MySQL数据库原理与应用(微课版)ch07数据查询

第7章

数据查询本章概述数据查询是数据库操作中最常用也是最重要的操作，是指从数据库中获取所需要的数据。在MySQL中使用SELECT语句来查询数据。通过SELECT语句查询数据，有不同的查询方式。不同的查询方式可以获得不同的数据，开发人员可以根据需求选择不同的查询方式。本章学习目标掌握SELECT查询的语法结构，通过SELECT语句查询所有字段、指定字段和指定数据的操作；掌握按条件查询数据的方法；掌握常用的高级查询方式，例如，对查询结果排序、分组查询、使用LIMIT限制查询；掌握在查询过程中使用聚合函数进一步对查询结果进行加工；了解连接查询、子查询的操作，以及合并查询结果；掌握在查询过程中定义表和字段的别名；掌握常用的正则表达式在查询中的应用。目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询09目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询09基本查询语句7.1.1SELECT语句SELECT语句是最常用的查询语句，它的使用方式有简单的，也有复杂的。SELECT语句的基本语法如下。SELECTselectionlist//要查询的内容，选择哪些列FROM数据表名//指定数据表WHEREprimary_constraint//查询时需要满足的条件，行必须满足的条件GROUPBYgrouping_columns//如何对结果进行分组ORDERBYsortingcloumns//如何对结果进行排序HAVINGsecondary_constraint//查询时满足的第二条件LIMITcount//限定输出的查询结果基本查询语句7.1.1SELECT语句-使用SELECT语句查询单个数据表使用SELECT语句从某个数据表查询数据时，首先要确定要查询的列。“*”代表所有的列。例如，查询db_library数据库的tb_admin表中的所有数据，代码如下。USEdb_library;SELECT*FROMtb_admin;查询结果如图所示。基本查询语句7.1.1SELECT语句-查询数据表中的指定列查询数据表中的多列，只要在SELECT后面指定要查询的列名即可，多列之间用“,”分隔。例如，查询tb_admin表中的admin_id和username列数据：SELECTadmin_id,usernameFROMtb_admin;查询结果如图所示。基本查询语句7.1.1SELECT语句-从一个或多个表中获取数据确定所要查询的数据在哪个表中，在对多个表进行查询时，同样使用“,”对多个表进行分隔。例如，从tb_book表和tb_category表中查询出tb_book.book_id、tb_book.bookname、tb_category.cat_name和tb_book.price字段的值：SELECTtb_book.book_id,tb_book.bookname,tb_category.cat_name,tb_book.pricefromtb_category,tb_bookWHEREtb_book.cat_id=tb_category.cat_id;查询结果如图所示。基本查询语句7.1.2查询所有字段确定所要查询的数据在哪个表中，在对多个表进行查询时，同样使用“,”对多个表进行分隔。例如，从tb_book表和tb_category表中查询出tb_book.book_id、tb_book.bookname、tb_category.cat_name和tb_book.price字段的值：SELECTtb_book.book_id,tb_book.bookname,tb_category.cat_name,tb_book.pricefromtb_category,tb_bookWHEREtb_book.cat_id=tb_category.cat_id;查询结果如图所示。基本查询语句7.1.3查询指定字段查询指定字段的数据可以使用下面的语法格式：SELECT字段名FROM表名;如果查询多个字段，可以使用“,”对字段进行分隔。例如，查询图书信息表tb_book中图书的名称、作者、价格、图书唯一识别码：SELECTbookname,author,price,ISBNFROMtb_book;基本查询语句7.1.4查询指定数据如果要从很多记录中查询出指定的记录，则需要设定查询的条件。设定查询条件应用的是WHERE子句，该子句可以实现很多复杂的条件查询。例如，查询名称为Romero的管理员：SELECT*FROMtb_adminWHEREusername='Romero';目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询09按条件查询7.2.1带关系运算符的查询带关系运算符的查询，也就是将MySQL支持的关系运算符引入数据库查询中。例如，从图书管理数据库db_library的图书信息表tb_book中，查询定价price大于40元的图书。语句如下：SELECTbookname,author,priceFROMtb_bookWHEREprice>40;按条件查询7.2.2带IN关键字的查询关键字IN可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足查询条件，返回该字段所属记录；如果不在集合中，则不满足查询条件，不返回该记录。语法格式如下。SELECT*FROM表名WHERE条件[NOT]IN(元素1,元素2,…,元素n);例如：SELECTbook_id,bookname,authorFROMtb_bookWHEREauthorIN('明日科技','刘丹冰');按条件查询7.2.3带BETWEENAND关键字的查询关键字BETWEENAND可以判断某个字段的值是否在指定的范围内。如果字段的值在指定范围内，则满足查询条件，该记录将被查询出来；如果不在指定范围内，则不满足查询条件。其语法如下。SELECT*FROM表名WHERE条件[NOT]BETWEEN取值1AND取值2;例如：SELECTbookname,author,priceFROMtb_bookWHEREpriceBETWEEN30AND40;按条件查询7.2.4空值查询ISNULL关键字可以用来判断字段的值是否为空值(NULL)。例如，为图书信息表tb_book添加test字段，然后插入若干数据值，语句如下：ALTERTABLEtb_bookADDtestVARCHAR(50)NULL;按条件查询7.2.5用关键字DISTINCT去除结果中的重复行使用DISTINCT关键字可以去除查询结果中的重复记录，语法格式如下。SELECTDISTINCT字段名FROM表名;例如，要查看tb_book表中有哪些书架上有书，语句如下：SELECTDISTINCTbookcaseFROMtb_book;按条件查询7.2.6带LIKE关键字的查询LIKE属于较常用的比较运算符，可用于实现模糊查询。它有两种通配符：“%”和下画线“_”。“%”可以匹配一个或多个字符，可以代表任意长度的字符串，长度可以为0。例如，“明%技”表示以“明”开头、以“技”结尾的任意长度的字符串。该字符串可以代表明日科技、明日编程科技、明日图书科技等字符串。“_”只匹配一个字符。例如，m_n表示以m开头、以n结尾的3个字符。中间的“_”可以是任意一个字符。按条件查询7.2.7带AND关键字的多条件查询AND关键字可以用来联合多个条件进行查询。使用AND关键字时，只有同时满足所有查询条件的记录才会被查询出来；如果不满足查询条件中的一个，那么这样的记录将被排除。AND关键字的语法格式如下。SELECT*FROM数据表名WHERE条件1AND条件2[…AND条件表达式n];判断输入的管理员账号和密码是否存在：SELECTbook_id,bookname,authorFROMtb_bookWHEREbooknameLIKE'%GO%'ANDauthor='刘丹冰';按条件查询7.2.8带OR关键字的多条件查询使用OR关键字时，只要满足查询条件中的一个，那么此记录就会被查询出来。例如，根据作者查询图书信息：SELECTbook_id,bookname,authorFROMtb_bookWHEREauthor='明日科技'ORauthor='刘丹冰';目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询097.3高级查询 使用ORDERBY关键字可以对查询的结果进行升序(ASC)或降序(DESC)排列。ORDERBY字段名[ASC|DESC];ASC表示按升序排列，DESC表示按降序排列。例如：SELECTbook_id,bookname,priceFROMtb_bookORDERBYpriceDESC;7.3.1对查询结果排序7.3高级查询 GROUPBY子句可以将数据划分到不同的组中，实现对记录进行分组查询。例如：使用GROUPBY关键字对tb_book表中bookcase字段进行分组查询，语句如下。SELECTbookcase,COUNT(*)FROMtb_bookGROUPBYbookcase;此外，还可以按多个字段进行分组。7.3.2分组查询7.3高级查询 LIMIT子句可以对查询结果的记录条数进行限定，控制输出的行数。例如：SELECTbook_id,bookname,priceFROMtb_bookORDERBYpriceDESCLIMIT3;7.3.3使用LIMIT限制查询结果数量目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询09聚合函数查询返回选择集合中所有行的数目，包含NULL值的行：SELECTCOUNT(*)FROMtb_book;COUNT函数聚合函数查询SUM()函数可以求出表中某个数值类型字段取值的总和。例如：使用SUM()函数统计tb_book表中总金额字段(total)的总和，语句如下。SELECTSUM(total)FROMtb_book;SUM函数聚合函数查询AVG()函数可以求出表中某个数值类型字段取值的平均值。例如：使用AVG()函数求tb_book表中图书价格(price)字段值的平均值，语句如下。SELECTAVG(price)FROMtb_book;AVG函数聚合函数查询MAX()函数可以求出表中某个数值类型字段取值的最大值。例如，使用MAX()函数查询tb_book表中price字段值的最大值，语句如下。SELECTMAX(price)FROMtb_book;MAX函数聚合函数查询使用MIN()函数查询tb_book表中price字段值的最小值。查询语句如下。SELECTMIN(price)FROMtb_book;MIN函数目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询09连接查询内连接(INNERJOIN)是SQL中的一种连接类型，用于将两个或多个表中的记录根据某个条件进行匹配，并返回匹配的记录。内连接可以分为几种类型：等值连接。在连接条件中使用等于号(=)运算符比较被连接列的列值。非等值连接。在连接条件中使用除等于运算符以外的其他比较运算符，如大于、小于等。自连接。将一张表看作两张表，通过别名区分，然后进行连接查询。内连接查询连接查询【例7-22】使用内连接查询图书的借阅信息。代码如下。SELECTtb_borrow.book_id,tb_book.bookname,tb_borrow.borrowTime,tb_borrow.backTime,tb_borrow.ifbackFROMtb_book,tb_borrowWHEREtb_borrow.book_id=tb_book.book_id;内连接查询连接查询外连接（OuterJoin）是一种SQLJOIN操作，它允许从一个表中选择所有的记录，而无论在另一个表中是否有匹配的记录。如果记录在另一个表中没有匹配，那么结果集中的值为NULL。在左外连接查询时，查询左表所有数据，以及两张表交集部分的数据。语法格式如下：SELECT字段列表FROM表1LEFTOUTERJOIN表2ON条件…;右外连接(RIGHTJOIN)和左外连接恰好相反。右外连接包括右边的所有数据，以及两张表交集部分数据。表中不符合条件的数据，在相应列中填充NULL值。外连接查询目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询09子查询IN运算符可以检测结果集中是否存在某个特定的值，如果检测成功，则执行外部的查询。SELECTbook_id,bookname,authorFROMtb_bookWHEREbook_idIN(SELECTbook_idFROMtb_borrow);带IN关键字的子查询子查询从归还表tb_back中查询图书编号book_id等于1002的管理员(operator)，然后查询tb_admin表中姓名username为该管理员的信息，代码如下。SELECT*FROMtb_adminWHEREusername=(SELECToperatorFROMtb_backWHEREbook_id=1002);带比较运算符的子查询子查询应用带EXISTS关键字的子查询实现查询已经被借阅的图书的信息，代码如下。SELECTbook_id,bookname,authorFROMtb_bookWHEREEXISTS(SELECT*FROMtb_borrowWHEREtb_borrow.book_id=tb_book.book_id);带EXISTS关键字的子查询子查询。使用ANY关键字时，只要满足内层查询语句返回的结果中的任意一个，就可以通过该条件来执行外层查询语句。语法格式如下。列名比较运算符ANY(子查询)例如，从图书信息表tb_book中查询出非最低价格的全部图书信息，主要是通过带ANY关键字的子查询实现查询非最低图书价格的图书信息，示例代码如下。SELECTbook_id,bookname,author,priceFROMtb_bookWHEREprice>ANY(SELECTMin(price)FROMtb_book);带ANY关键字的子查询子查询使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。语法格式如下。列名比较运算符ALL(子查询)例如，查询比图书编号1001价格高的全部图书信息，主要是通过带ALL关键字的子查询实现，示例代码如下。SELECTbook_id,bookname,author,priceFROMtb_bookWHEREprice>ALL(SELECTpriceFROMtb_bookWHEREbook_id=1001);带ALL关键字的子查询目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询09合并查询结果使用UNION关键字可以将多个结果集合并到一起，并且会去除相同记录。假设有一个与归还表tb_back结构相同的数据表tb_back_copy1，查询结果如图所示。7.7.1使用UNION关键字合并查询结果下面使用UNION关键字合并两个表的查询结果，语句如下。SELECT*FROMtb_backUNIONSELECT*FROMtb_back_copy1;查询结果如图所示。结果显示，所有结果被合并，重复值被去除。7.7.1使用UNION关键字合并查询结果UNIONALL关键字的使用方法类似于UNION关键字，也是将多个结果集合并到一起，但是该关键字不会去除相同记录。下面使用UNIONALL关键字合并查询结果，但是不去除重复值，代码如下。SELECT*FROMtb_backUNIONALLSELECT*FROMtb_back_copy1;7.7.2使用UNIONALL关键字目录01020304高级查询按条件查询聚合函数查询基本查询语句05连接查询06子查询07合并查询结果定义表和字段的表明08使用正则表达式查询09定义表和字段的别名使用左连接查询实现查询图书的完整信息，并为图书信息表tb_book指定别名为b