第五章数据查询

1、第五章第五章数据查询项目知识要点与目标项目知识要点与目标项目知识要点项目知识要点知识能力目标知识能力目标学时学时任务任务1 1 单表查询单表查询1 select1 select语句定义语句定义2 2 选择指定的列选择指定的列3 where3 where子句子句任务任务2 2 多表查询多表查询1 from1 from子句子句2 2 多表连接多表连接任务任务3 3 排序与分类汇总排序与分类汇总1 1 聚合函数聚合函数2 group by2 group by子句子句3 having3 having子句子句4 order by4 order by子句子句【典型工作任务典型工作任务】 单表查询、多表查询

2、、子查询单表查询、多表查询、子查询【技能要求技能要求】1 1 使用使用mysqlmysql命令完成多功能数据命令完成多功能数据查询；查询；2 2 使用图形工具完成多功能数据查使用图形工具完成多功能数据查询询【知识要求知识要求】1 1 简单查询、分类汇总简单查询、分类汇总2 2 联接和嵌套查询联接和嵌套查询3 3 数据查询的数据查询的sqlsql语句语句 6 6项目实训项目实训“员工管理系统员工管理系统”数据库查询操作数据库查询操作6 6单表查询单表查询 使用数据库和表的主要目的是存储数据以便在需要时进行检使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过索、统计或组织

3、输出，通过sqlsql语句的查询可以从表或视图中迅语句的查询可以从表或视图中迅速方便地检索数据。速方便地检索数据。一、一、selectselect语句语句 语法格式：语法格式： select all | distinct 输出列表达式输出列表达式, . from 表名表名1 , 表名表名2 /*from子句子句*/ where 条件条件 /*where子句子句*/ group by 列名列名 | 表达式表达式 | 列编号列编号 asc | desc, . /* group by 子句子句*/ having 条件条件 /* having 子句子句*/ order by 列名列名 | 表达式表达式

4、 | 列编号列编号 asc | desc , . /*order by子句子句*/ limit 偏移量偏移量, 行数行数|行数行数offset偏移量偏移量 /*limit子句子句*/ 顺序严格地排序。例如，一个顺序严格地排序。例如，一个havinghaving子句必须位于子句必须位于group bygroup by子句之后，子句之后，并位于并位于order byorder by子句之前。子句之前。 选择指定的列选择指定的列使用使用selectselect语句选择一个表中的某些列的语法格式如下语句选择一个表中的某些列的语法格式如下： select 字段列表字段列表 from 表名表名各列名之各列

5、名之间要以逗号分隔。间要以逗号分隔。【例例5.15.1】查询查询bookstorebookstore数据库的数据库的membersmembers表中各会员的会员姓名、表中各会员的会员姓名、联系电话和注册时间。联系电话和注册时间。 use bookstore; select 会员姓名,联系电话,注册时间 from members;说明：当在说明：当在selectselect语句指定列的位置上使用语句指定列的位置上使用* *号时，表示选择表的号时，表示选择表的所有列，如要显示所有列，如要显示membersmembers表中所有列，不必将所有字段名一一列出，表中所有列，不必将所有字段名一一列出，使用

6、使用 select * from members;列别名列别名2. 2. 定义列别名定义列别名 当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列名之后使用时，可以在列名之后使用asas子句来更改查询结果的列别名。子句来更改查询结果的列别名。语法格式为：语法格式为： select字段列表字段列表 as 别名别名【例例5.25.2】 查询查询bookbook表中图书类别为计算机的图书书名、作者和出版表中图书类别为计算机的图书书名、作者和出版社，结果中各列的标题分别指定为社，结果中各列的标题分别指定为namename

7、、autherauther和和publisherpublisher。 select 书名 as name, 作者 as auther, 出版社 as publisher from book where 图书类别= 计算机;注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。 select 图书编号 as book number, 书名 as book name, from book where 图书类别= 计算机;替换查询结果中的数据替换查询结果中的数据3. 3. 替换查询结果中的数据替换查询结果中的数据在对表进行查询时，

8、有时对所查询的某些列希望得到的是一种概念在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询而不是具体的数据。例如查询xsxs表的总学分，所希望知道的是学习的总表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。体情况，这时，就可以用等级来替换总学分的具体数字。要替换查询结果中的数据，则要使用查询中的要替换查询结果中的数据，则要使用查询中的casecase表达式，格式为：表达式，格式为：case when 条件条件1 then 表达式表达式1 when 条件条件2 then 表达式表达式2 else 表达式表达式end【例例5.3

9、5.3】 查询查询bookbook表中图书编号、书名和数量，对其库存数量按表中图书编号、书名和数量，对其库存数量按以下规则进行替换：若数量为空值，替换为以下规则进行替换：若数量为空值，替换为“尚未进货尚未进货”；若数量小于；若数量小于5 5，替换为，替换为“需进货需进货”；若数量在；若数量在5-505-50之间，替换为之间，替换为“库存正常库存正常”；若；若总学分大于总学分大于5050，替换为，替换为“库存积压库存积压”。列标题更改为。列标题更改为“库存库存”。 select 图书编号, 书名, case when 数量 is null then 尚未进货 when 数量 =5 and 数量=

10、50 then 库存正常 else 库存积压 end as 库存 from book; 替换查询结果举例替换查询结果举例计算列值计算列值4. 4. 计算列值计算列值使用使用selectselect对列进行查询时，在结果中可以输出对列值计算后的值，对列进行查询时，在结果中可以输出对列值计算后的值，即即selectselect子句可使用表达式作为结果，格式为：子句可使用表达式作为结果，格式为：select 表达式表达式1 , 表达式表达式2 【例例5.45.4】 对对sellsell表已发货的记录计算订购金额（订购金额表已发货的记录计算订购金额（订购金额= =订购册订购册数数* *订购单价），并显

11、示图书编号和订购金额。订购单价），并显示图书编号和订购金额。 select 图书编号, 订购册数*订购单价 as 订购金额 from sell where 是否发货= 已发货; 消除结果集中的重复行消除结果集中的重复行5. 5. 消除结果集中的重复行消除结果集中的重复行 对表只选择其某些列时，可能会出现重复行。例如，若对对表只选择其某些列时，可能会出现重复行。例如，若对xscjxscj数数据库的据库的xsxs表只选择专业名和总学分，则出现多行重复的情况。可以使表只选择专业名和总学分，则出现多行重复的情况。可以使用用distinctdistinct或或distinctrowdistinctrow

12、关键字消除结果集中的重复行，其格式是：关键字消除结果集中的重复行，其格式是：select distinct字段列表字段列表其含义是对结果集中的重复行只选择一个，保证行的唯一性。其含义是对结果集中的重复行只选择一个，保证行的唯一性。【例例5.55.5】 对对bookbook表只选择图书类别和出版社，消除结果集中的表只选择图书类别和出版社，消除结果集中的重复行。重复行。 select distinct 图书类别, 出版社 from book; wherewhere子句必须紧跟子句必须紧跟fromfrom子句之后，在子句之后，在wherewhere子句中，使用一个条件子句中，使用一个条件从从from

13、from子句的中间结果中选取行。其基本格式为：子句的中间结果中选取行。其基本格式为： where 列名列名 运算符运算符 值值操操 作作 符符描描 述述= =等于等于不等于不等于 大于大于 =大于等于大于等于=小于等于小于等于betweenbetween在某个范围内在某个范围内likelike搜索某种模式搜索某种模式比较运算比较运算wherewhere子句会根据条件对子句会根据条件对fromfrom子句的中间结果中的行一行一行地进行子句的中间结果中的行一行一行地进行判断，当条件为判断，当条件为truetrue的时候，一行就被包含到的时候，一行就被包含到wherewhere子句的中间结果中。子句

14、的中间结果中。在在sqlsql中，返回逻辑值（中，返回逻辑值（truetrue或或falsefalse）的运算符或关键字都可称为谓）的运算符或关键字都可称为谓词。词。判定运算包括比较运算、模式匹配、范围比较、空值比较和子查询。判定运算包括比较运算、模式匹配、范围比较、空值比较和子查询。1. 1. 比较运算比较运算比较运算符用于比较（除比较运算符用于比较（除texttext和和blobblob类型外类型外) ) 两个表达式值，两个表达式值，mysqlmysql支持的比较运算符有：支持的比较运算符有：= =（等于）、（等于）、 （小于）、（小于）、= （大（大于）、于）、=（大于等于）、（大于等于

15、）、（相等或都等于空）、（相等或都等于空）、（不等于）、（不等于）、!=!=（不等于）。（不等于）。当两个表达式值均不为空值（当两个表达式值均不为空值（nullnull）时，除了）时，除了“”运算符，其他比运算符，其他比较运算返回逻辑值较运算返回逻辑值truetrue（真）或（真）或falsefalse（假）；而当两个表达式值中有一（假）；而当两个表达式值中有一个为空值或都为空值时，将返回个为空值或都为空值时，将返回unknownunknown。【例例5.65.6】 查询查询bookstorebookstore数据库数据库bookbook表中书名为表中书名为“网页程序设计网页程序设计”的记录。

16、的记录。 select 书名,单价 from book where 书名=网页程序设计; 【例例5.75.7】 查询查询bookbook表中单价大于表中单价大于3030的图书情况。的图书情况。 select * from book where 单价30; 比较运算举例比较运算举例逻辑运算逻辑运算通过逻辑运算符（通过逻辑运算符（andand、oror、xorxor和和notnot）组成更为复杂的查询条件。）组成更为复杂的查询条件。逻辑运算操作的结果是逻辑运算操作的结果是“1 1”或或“0 0”，分别表示，分别表示“truetrue”或或“falsefalse”。符号符号1 1符号符号2 2说明说

17、明示例示例说明说明notnot! !非运算非运算!x!x如果x是true，那么示例的结果是false；如果x是false，那么示例的结果是true。oror|或运算或运算x | yx | y如果x或y任一是true，那么示例的结果是true，否则示例的结果是false。andand&与运算与运算x & yx & y如果x和y都是true，那么示例结果是true，否则示例的结果是false。xorxor 异或运算异或运算x yx y如果x和y不相同，那么示例结果是true，否则示例的结果是false。逻辑运算举例逻辑运算举例非：select not 0, not 1, n

18、ot null;或： select (1 or 0), (0 or 0), (1 or null), (1 or 1), (null or null);与： select (1 and 1), (0 and 1), (3 and 1), (1 and null);异或： select (1 xor 1), (0 xor 0), (1 xor 0), (0 xor 1), (null xor 1); 【例例5.85.8】 查询查询sellsell表中已收货且已结清的订单情况。表中已收货且已结清的订单情况。 select 订单号,是否收货,是否结清 from sell where 是否收货=已收货

19、 and 是否结清=已结清; 【例例5.95.9】 查询查询bookbook表中清华大学出版社和北京大学出版社出版的价格大于表中清华大学出版社和北京大学出版社出版的价格大于2525元的图书。元的图书。select 书名,出版社,单价 from book where (出版社=清华大学出版社 or 出版社=北京大学出版社 ) and 单价=25;或： select书名,出版社,单价 from book where (出版社=清华大学出版社 and 单价=25) or (出版社=北京大学出版社 and 单价=25); 【思考题思考题】以下语句能否得到正确结果？为什么？以下语句能否得到正确结果？为什

20、么？ select 书名,出版社,单价 from book where 出版社=清华大学出版社 or 出版社=北京大学出版社 and 单价=25;where子句举例子句举例likelike运算符运算符2. 2. 模式匹配（模式匹配（likelike运算符）运算符）likelike运算符用于指出一个字符串是否与指定的字符串相匹配，其运运算符用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是算对象可以是charchar、varcharvarchar、texttext、datetimedatetime等类型的数据，返回逻辑等类型的数据，返回逻辑值值truetrue或或falsefalse。

21、使用使用likelike进行模式匹配时，常使用特殊符号进行模式匹配时，常使用特殊符号_ _和和% %，可进行模糊查询。，可进行模糊查询。“% %”代表代表0 0个或多个字符，个或多个字符，“_ _”代表单个字符。由于代表单个字符。由于mysqlmysql默认不区分大默认不区分大小写，要区分大小写时需要更换字符集的校对规则。小写，要区分大小写时需要更换字符集的校对规则。【例例5.105.10】 查询查询membersmembers表中姓表中姓“张张”的会员的身份证号、姓名及的会员的身份证号、姓名及注册时间。注册时间。 select 用户号,会员姓名, 注册时间 from members wher

22、e 会员姓名 like 张%; 【例例5.115.11】 查询查询bookbook表中图书编号倒数第二位为表中图书编号倒数第二位为t t的图书的图书编的图书的图书编号和书名。号和书名。 select 图书编号, 书名 from book where 图书编号 like %t_; 例例【 5.12 5.12】 查询查询bookbook表中书名中包含下画线的图书。表中书名中包含下画线的图书。 select 图书编号,书名 from book where 书名 like %#_% escape #; 范围比较范围比较3. 3. 范围比较范围比较 用于范围比较的关键字有两个：用于范围比较的关键字有两个

23、：betweenbetween和和inin。当要查询的条件是某个值的范围时，可以使用当要查询的条件是某个值的范围时，可以使用betweenbetween关键字。关键字。betweenbetween关键字指出查询范围，格式为：关键字指出查询范围，格式为：表达式表达式 not between 表达式表达式1 and 表达式表达式2当不使用当不使用notnot时，若表达式时，若表达式expressionexpression的值在表达式的值在表达式expression1expression1与与expression2expression2之间（包括这两个值），则返回之间（包括这两个值），则返回true

24、true，否则返回，否则返回falsefalse；使；使用用notnot时，返回值刚好相反。时，返回值刚好相反。注意：注意：expression1expression1的值不能大于的值不能大于expression2expression2的值。的值。【例例5.135.13】 查询查询bookbook表中表中20102010年出版的图书的情况。年出版的图书的情况。 select * from book where 出版时间 between 2010-1-1 and 2010-12-31; 使用使用inin关键字可以指定一个值表，值表中列出所有可能的值，当与关键字可以指定一个值表，值表中列出所有可能

25、的值，当与值表中的任一个匹配时，即返回值表中的任一个匹配时，即返回truetrue，否则返回，否则返回falsefalse。使用使用inin关键字指定值表的格式为：关键字指定值表的格式为：表达式表达式 in (表达式表达式1 ,n)【例例5.145.14】 查询查询bookbook表中表中“高等教育出版社高等教育出版社”、“北京大学出版社北京大学出版社”和和“人民邮电出版社人民邮电出版社”出版的图书的情况。出版的图书的情况。 select * from book where 出版社 in ( 高等教育出版社, 北京大学出版社, 人民邮电出版社); 说明：说明：inin关键字最主要的作用是表达子

26、查询。关键字最主要的作用是表达子查询。范围比较举例范围比较举例空值比较空值比较4. 4. 空值比较空值比较当需要判定一个表达式的值是否为空值时，使用当需要判定一个表达式的值是否为空值时，使用is nullis null关键字，格关键字，格式为：式为：表达式表达式 is not null当不使用当不使用notnot时，若表达式时，若表达式expressionexpression的值为空值，返回的值为空值，返回truetrue，否则，否则返回返回falsefalse；当使用；当使用notnot时，结果刚好相反。时，结果刚好相反。【例例5.155.15】 查询查询sellsell表中尚未发货的订单记

27、录。表中尚未发货的订单记录。 select * from sell where 是否发货 is null;本例即查找总学分为空的学生，结果为空。本例即查找总学分为空的学生，结果为空。mysqlmysql有一个特殊的等于运算符有一个特殊的等于运算符“”，当两个表达式彼此相等或都，当两个表达式彼此相等或都等于空值时，它的值为等于空值时，它的值为truetrue，其中有一个空值或都是非空值但不相等，其中有一个空值或都是非空值但不相等，这个条件就是这个条件就是falsefalse。【例例5.165.16】 查询查询sellsell表中还未收货的订单情况。表中还未收货的订单情况。 select * fr

28、om sell where 是否收货null; 多表查询多表查询 前面介绍了使用前面介绍了使用selectselect子句选择列，本小节讨论子句选择列，本小节讨论selectselect查询的对象查询的对象（即数据源）的构成形式。（即数据源）的构成形式。selectselect的查询对象由的查询对象由fromfrom子句指定，其格子句指定，其格式为：式为：from 表名表名1 as 别名别名1 , 表名表名2 as 别名别名2 /*查询表查询表*/ | join子句子句 /*连接表连接表*/ 引用一个表：引用一个表：可以用两种方式引用一个表，第一种方式是使用可以用两种方式引用一个表，第一种方式

29、是使用useuse语句让一个数语句让一个数据库成为当前数据库，在这种情况下，如果在据库成为当前数据库，在这种情况下，如果在fromfrom子句中指定表名，子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是表所属数据库的名字。例如，假设当前数据库是db1db1，现在要显示数据，现在要显示数据库库db2db2里的表里的表tbtb的内容，使用如下语句：的内容，使用如下语句：select select * * from db2.tb; from db2.tb;当然，在当然，在

30、selectselect关键字后指定列名的时候也可以在列名前带上所属关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。的，就没有必要去特别指定。【例例5.175.17】 从从membersmembers表中检索出所有客户的信息，并使用表别名表中检索出所有客户的信息，并使用表别名usersusers。 select * from members as users; 引用多个表：引用多个表：如果要在不同表中查询数据，则必须在如果要在不同表中查询数据，则必须在f

31、romfrom子句中指定多个表。指子句中指定多个表。指定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的连接。例如，在连接。例如，在xscjxscj数据库中需要查找选修了离散数学课程的学生的姓数据库中需要查找选修了离散数学课程的学生的姓名和成绩，就需要将名和成绩，就需要将xsxs、kckc和和xs_kcxs_kc三个表进行连接，才能查找到结果。三个表进行连接，才能查找到结果。【例例5.185.18】 查找查找bookstorebookstore数据库中客户订购的图书书名，订购册数据库中客户订购的图书书名，订购册数和订购时

32、间。数和订购时间。 select book.书名, sell.订购册数, sell.订购时间 from book, sell where book.图书编号=sell.图书编号; 全连接全连接当数据查询涉及到多张表格时，要将多张表格的数据连接起来组成当数据查询涉及到多张表格时，要将多张表格的数据连接起来组成一张表格，连接的方式有多种。一张表格，连接的方式有多种。全连接全连接全连接产生的新表是每个表的每行都与其他表中的每行交叉以产生全连接产生的新表是每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积。全连所有可能的组合，列包含了所有表中出现的列，也就

33、是笛卡儿积。全连接可能得到的行数为每个表中行数之积。接可能得到的行数为每个表中行数之积。如表如表a a有有3 3行，表行，表b b有有2 2行，表行，表a a和和b b全连接后得到全连接后得到6 6行（行（3x2=6)3x2=6)的表的表全连接举例全连接举例t1t1t2t2t3t3t4t4t5t51 1a a1 13 3m m6 6f f2 20 0n n2 2b bt1t1t2t2t3t3t4t4t5t51 1a a1 13 3m m6 6f f1 13 3m m2 2b b1 13 3m m1 1a a2 20 0n n6 6f f2 20 0n n2 2b b2 20 0m m表表a a

34、和和b b全连接后得到如下全连接后得到如下6 6行（行（3x2=6)3x2=6)的表：的表：表表a a表表b b等值连接等值连接t1t2t3t4t51a13m2b20nfromfrom子句各个表用逗号分隔，这样就指定了全连接。全连接潜在地子句各个表用逗号分隔，这样就指定了全连接。全连接潜在地产生数量非常大的行，因为可能得到的行数为每个表中行数之积。在这产生数量非常大的行，因为可能得到的行数为每个表中行数之积。在这样的情形下，通常要使用样的情形下，通常要使用wherewhere子句设定条件来将结果集减少为易于管子句设定条件来将结果集减少为易于管理的大小，这样的连接即为等值连接。理的大小，这样的连

35、接即为等值连接。若表若表a a和和b b进行等值连接（进行等值连接（t1=t3 t1=t3 ），则如下表所示，只有两行。），则如下表所示，只有两行。【例例5.195.19】 查找查找bookstorebookstore数据库中客户订购的图书书名，订购册数据库中客户订购的图书书名，订购册数和订购时间。数和订购时间。 select book.书名, sell.订购册数, sell.订购时间 from book, sell where book.图书编号=sell.图书编号; joinjoin连接连接2. join2. join连接连接 第二种方式是使用第二种方式是使用joinjoin关键字的连接，

36、语法格式如下：关键字的连接，语法格式如下： 表名表名1 inner join 表名表名2 on 条件条件 | using (列名列名)（1 1）内连接：指定了）内连接：指定了inner关键字的连接是内连接。关键字的连接是内连接。【例例5.205.20】 要实现例要实现例4.234.23中的结果，可以使用以下语句：中的结果，可以使用以下语句：select book.书名, sell.订购册数, sell.订购时间 from book inner join sell on book.图书编号=sell.图书编号; 该语句根据该语句根据onon关键字后面的连接条件，合并两个表，返回满足条件的行关键字

37、后面的连接条件，合并两个表，返回满足条件的行。内连接是系统默认的，可以省略内连接是系统默认的，可以省略innerinner关键字。关键字。使用内连接后，使用内连接后，fromfrom子句中子句中onon条件主要用来连接表，其他并不属于条件主要用来连接表，其他并不属于连接表的条件可以使用连接表的条件可以使用wherewhere子句来指定。子句来指定。【例例 5.265.26】 用用joinjoin关键字表达下列查询：查找购买了关键字表达下列查询：查找购买了“网页程序网页程序设计设计” ” 图书信息。图书信息。 select 书名,订购册数 from book join sell on book.

38、图书编号 = sell. 图书编号 where 书名 = 网页程序设计 ; joinjoin连接举例连接举例【例例5.215.21】 用用joinjoin关键字表达下列查询：查找订购数量大于关键字表达下列查询：查找订购数量大于5 5本图本图书和会员姓名和订购册数。书和会员姓名和订购册数。select 会员姓名,订购册数 from sell join members on sell.用户号 = members.用户号 where 订购册数5 ; 多表连接举例多表连接举例内连接还可以用于多个表的连接。内连接还可以用于多个表的连接。【例例5.225.22】 用用joinjoin关键字表达下列查询：查

39、找购买了关键字表达下列查询：查找购买了“网页程序设网页程序设计计”且订购数量大于且订购数量大于5 5本的图书和会员姓名和订购册数。本的图书和会员姓名和订购册数。select book.图书编号, 会员姓名, 书名, 订购册数 from sell join book on book. 图书编号= sell.图书编号 join members on sell.身份证号 = members.身份证号 where 书名 = 网页程序设计 and 订购册数5 ; 多表连接举例多表连接举例作为特例，可以将一个表与它自身进行连接，称为自连接。若要在作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一

40、个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。为表指定两个别名，且对所有列的引用均要用别名限定。【例例5.235.23】 查找查找bookstorebookstore数据库中订单不同、图书编号相同的图数据库中订单不同、图书编号相同的图书的订单号、图书编号和订购册数。书的订单号、图书编号和订购册数。 select a.订单号,a.图书编号,a.订购册数 from sell as a join sell as b on a. 图书编号=b. 图书编号 where a. 订单

41、号!=b. 订单号;自表连接自表连接 如果要连接的表中有列名相同，并且连接的条件就是列名相等，那如果要连接的表中有列名相同，并且连接的条件就是列名相等，那么么onon条件也可以换成条件也可以换成usingusing子句。子句。usingusing（column_listcolumn_list）子句用于为一）子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中系列的列进行命名。这些列必须同时在两个表中存在。其中column_listcolumn_list为两表中相同的列名。为两表中相同的列名。 【例例5.245.24】 查找查找membersmembers表中所有订购过图书的会员姓名

42、。表中所有订购过图书的会员姓名。 select distinct 会员姓名 from members join sell using (用户号); 查询的结果为查询的结果为sellsell表中所有出现的身份证号对应的会员姓名。表中所有出现的身份证号对应的会员姓名。 例例5.245.24的语句与下列语句等价：的语句与下列语句等价： select distinct 会员姓名 from members join sell on members.用户号=sell.用户号;usingusing子句子句多表查询多表查询对对ygglyggl数据库完成多表查询：数据库完成多表查询：1 1、查询每个雇员的基本情

43、况和薪水情况、查询每个雇员的基本情况和薪水情况2 2、查询、查询“王林王林”的基本情况和所工作的部门名称的基本情况和所工作的部门名称3 3、查询月收入在、查询月收入在2000300020003000元的员工姓名和支出元的员工姓名和支出4 4、查询研发部在、查询研发部在19701970年以前出生的员工姓名和薪水情况年以前出生的员工姓名和薪水情况外连接外连接 指定了指定了outerouter关键字的连接为外连接。关键字的连接为外连接。外连接包括：外连接包括： 左外连接（左外连接（left outer joinleft outer join）：）： 结果表中除了匹配行外，还包括左表有的但右表中不匹配

44、的结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为行，对于这样的行，从右表被选择的列设置为nullnull。 右外连接（右外连接（right outer joinright outer join）：）： 结果表中除了匹配行外，还包括右表有的但左表中不匹配的结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为行，对于这样的行，从左表被选择的列设置为nullnull。 左外连接举例左外连接举例t1t1t2t2t3t3t4t4t5t51 1a a1 13 3m m6 6f f2 20 0n n2 2b bt1t1t2

45、t2t3t3t4t4t5t51 1a a1 13 3m m6 6f fnullnullnullnullnullnull2 2b b2 20 0n nfrom a left join b on t1=t3from a left join b on t1=t3：表表a a表表b b外连接举例外连接举例【例例5.255.25】 查找所有图书的图书编号、数量及订购了图书的会员身查找所有图书的图书编号、数量及订购了图书的会员身份证号，若从未订购过，也要包括其情况。份证号，若从未订购过，也要包括其情况。 select book.图书编号,book.数量,身份证号 from book left outer

46、join sell on book.图书编号= sell.图书编号; 【例例5.265.26】 查找订购了图书的会员的订单号、图书编号和订购册数查找订购了图书的会员的订单号、图书编号和订购册数以及所有会员的会员姓名。以及所有会员的会员姓名。 select 订单号,图书编号,订购册数, members.会员姓名 from sell right join members on members.用户号= sell.用户号; 子查询子查询 在查询条件中，可以使用另一个查询的结果作为条件的一部分，在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如，判定列值是否与某个查询的结果集中的值相等，作为

47、查询条例如，判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。件一部分的查询称为子查询。sqlsql标准允许标准允许selectselect多层嵌套使用，多层嵌套使用，用来表示复杂的查询。子查询除了可以用在用来表示复杂的查询。子查询除了可以用在selectselect语句中，还可以语句中，还可以用在用在insertinsert、updateupdate及及deletedelete语句中。语句中。 子查询通常与子查询通常与inin、existexist谓词及比较运算符结合使用。谓词及比较运算符结合使用。（1 1）inin子查询子查询 in in子查询用于进行一个给定值

48、是否在子查询结果集中的判断，子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：格式为：expressionexpression not in not in ( subquery )( subquery )其中，其中，subquerysubquery是子查询。当表达式是子查询。当表达式expressionexpression与子查询与子查询subquerysubquery的结果表中的某个值相等时，的结果表中的某个值相等时，inin谓词返回谓词返回truetrue，否则返回，否则返回falsefalse；若使用了若使用了notnot，则返回的值刚好相反。，则返回的值刚好相反。子查询举例子

49、查询举例【例例5.275.27】 查找在查找在bookstorebookstore数据库中张三的订单信息。数据库中张三的订单信息。 select *from sell where 用户号 in( select 用户号 from members where 会员姓名 = 张三 );说明：在执行包含子查询的说明：在执行包含子查询的selectselect语句时，系统先执行子查询，产语句时，系统先执行子查询，产生一个结果表，再执行查询。生一个结果表，再执行查询。 本例中，先执行子查询：本例中，先执行子查询： select 用户号 from members where 会员姓名= 张三 得到一个只含有

50、身份证号列的表。再执行外查询，若得到一个只含有身份证号列的表。再执行外查询，若sellsell表中某行表中某行的身份证号列值等于子查询结果表中的任一个值，则该行就被选择。的身份证号列值等于子查询结果表中的任一个值，则该行就被选择。 子查询嵌套举例子查询嵌套举例inin子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。的子查询。【例例5.285.28】 查找购买了除查找购买了除“网页程序设计网页程序设计”以外图书的会员信息。以外图书的会员信息。要查找会员信息，先要知道会员的身份证号，而要知道购买了除要查找会员信息，先要知道会员的

51、身份证号，而要知道购买了除“网页程序设计网页程序设计”以外图书的会员，可以按图书编号在以外图书的会员，可以按图书编号在sellsell表中表中查到，但是查到，但是“网页程序设计网页程序设计”的图书编号要通过查找的图书编号要通过查找bookbook才可以才可以获得。获得。select * from members where 用户号 in (select 用户号 from sell where 图书编号 not in ( select 图书编号 from book where 书名=网页程序设计); 比较子查询比较子查询这种子查询可以认为是这种子查询可以认为是inin子查询的扩展，它使表达式的值

52、与子查询的结子查询的扩展，它使表达式的值与子查询的结果进行比较运算。其格式如下：果进行比较运算。其格式如下：表达式表达式 | | = | != | all | some | any | | = | != | all | some | any ( ( 子查询子查询 ) )【例【例5.295.29】 查找查找bookbook表中所有比表中所有比“网页设计网页设计”类图书价格都高的图书基类图书价格都高的图书基本信息。本信息。 select 图书编号,图书类别,单价 from book where 单价all (select 单价 from book where 图书类别=网页设计 );比较子查询比较

53、子查询【例5.30】 查找sell表中订购册数不低于编号为 tp7/301-135 的任何一个订单的订购册数的订单信息。select 图书编号,订购册数 from sell where 订购册数some (select 订购册数 from sell where 图书编号 =tp7/301-135 );外连接与子查询外连接与子查询一、对一、对ygglyggl数据库完成多表查询：数据库完成多表查询：1 1、使用左外连接显示员工表中员工基本信息和所在部门的部门名称、使用左外连接显示员工表中员工基本信息和所在部门的部门名称二、使用子查询完成：二、使用子查询完成：2 2、查找工资收入大于、查找工资收入大

54、于20002000元的员工的基本信息元的员工的基本信息3 3、查找在财务部工作的员工的基本信息、查找在财务部工作的员工的基本信息4 4、查找住在、查找住在“中山中山”的员工的工作部门名称的员工的工作部门名称selectselect子句的表达式中还可以包含所谓的聚合函数。聚合函数常子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。常用于对一组值进行计算，然后返回单个值。函函 数数 名名说说 明明countcount求组中项数，返回求组中项数，返回intint类型整数类型整数maxmax求最大值求最大值minmin求最小值求最小值sumsum返回表达式中所有

55、值的和返回表达式中所有值的和avgavg求组中值的平均值求组中值的平均值（1 1）countcount函数函数聚合函数中最经常使用的是聚合函数中最经常使用的是count()count()函数，用于统计组中满足条件函数，用于统计组中满足条件的行数或总行数，返回的行数或总行数，返回selectselect语句检索到的行中非语句检索到的行中非nullnull值的数目，若值的数目，若找不到匹配的行，则返回找不到匹配的行，则返回0 0。 语法格式为：语法格式为：count ( all | distinct 表达式表达式 | * )其中，其中，表达式1是一个表达式，其数据类型是除是一个表达式，其数据类型是

56、除blobblob或或texttext之外之外的任何类型。的任何类型。allall表示对所有值进行运算，表示对所有值进行运算，distinctdistinct表示去除重复值，表示去除重复值，默认为默认为allall。使用。使用count(count(* *) )时将返回检索行的总数目，不论其是否包时将返回检索行的总数目，不论其是否包含含 nullnull值。值。【例例5.315.31】 求会员总人数。求会员总人数。 select count(*) as 会员数 from members; 【例例5.325.32】 统计已结清的订单数。统计已结清的订单数。 select count(是否结清)

57、as 已结清的订单数 from sell;注意：注意：这里这里count(count(是否结清是否结清) )只统计是否结清列中不为只统计是否结清列中不为nullnull的行。的行。【例例5.335.33】 统计订购册数在统计订购册数在5 5以上的订单数。以上的订单数。 select count(订购册数) as 订购册数在5以上的订单数 from sell where 订购册数5; maxmax和和minmin（2 2）maxmax和和minminmaxmax和和minmin分别用于求表达式中所有值项的最大值与最小值，语法格分别用于求表达式中所有值项的最大值与最小值，语法格式为：式为：max

58、/ min ( all | distinct 表达式 )其中，其中，expressionexpression是常量、列、函数或表达式，其数据类型可以是是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期数字、字符和时间日期 类型。类型。【例例5.355.35】 求订购了图书编号为求订购了图书编号为tp7/301-135的订单的最高订购的订单的最高订购册数和最低订购册数。册数和最低订购册数。 select max(订购册数), min(订购册数) from sell where 图书编号 = tp7/301-135 ;注意：注意：当给定列上只有空值或检索出的中间结果为空时，当给定列上只

59、有空值或检索出的中间结果为空时，maxmax和和minmin函数的值也为空。函数的值也为空。sumsum函数和函数和avgavg函数函数（3 3）sumsum函数和函数和avgavg函数函数sumsum和和avgavg分别用于求表达式中所有值项的总和与平均值，语法格式为：分别用于求表达式中所有值项的总和与平均值，语法格式为：sum / avg ( all | distinct 表达式 )其中，其中，expressionexpression是常量、列、函数或表达式，其数据类型只能是数值型。是常量、列、函数或表达式，其数据类型只能是数值型。【例例5.365.36】 求订购了图书编号为求订购了图书编

60、号为tp7/301-135图书的订购总册数。图书的订购总册数。 select sum(订购册数) as 订购总册数 from sell where 图书编号 = tp7/301-135 ; 【例例5.375.37】 求订购图书编号为求订购图书编号为tp7/301-135图书的订单平均册数。图书的订单平均册数。 select avg(订购册数) as 每笔订单平均册数 from sell where 图书编号 = tp7/301-135; group bygroup by子句主要用于根据字段对行分组。例如，根据学生所子句主要用于根据字段对行分组。例如，根据学生所学的专业对学的专业对xsxs表中的所有行分组，结果是每个