单元5 数据查询 - 副本

1、单元五单元五数据查询项目知识要点与目标项目知识要点与目标项目知识要点项目知识要点知识能力目标知识能力目标学时学时5.1 5.1 任务任务1 1 单表查询单表查询5.1.1 SELECT5.1.1 SELECT语句定义语句定义5.1.2 5.1.2 选择指定的列选择指定的列5.1.3 5.1.3 聚合函数聚合函数5.1.4 WHERE5.1.4 WHERE子句子句5.2 5.2 任务任务2 2 多表查询多表查询5.2.1 FROM5.2.1 FROM子句子句5.2.2 5.2.2 多表连接多表连接5.3 5.3 任务任务3 3 排序与分类汇总排序与分类汇总5.3.1 GROUP BY5.3.1

2、GROUP BY子句子句5.3.2 HAVING5.3.2 HAVING子句子句5.3.3 ORDER BY5.3.3 ORDER BY子句子句5.4 5.4 综合实例综合实例 PetStorePetStore数据查询数据查询【情境情境】：PetstorePetstore数据查询数据查询【职业岗位职业岗位】：操作员、程序员：操作员、程序员【典型工作任务典型工作任务】：查询记录、多：查询记录、多表查询、子查询表查询、子查询【技能要求技能要求】：1 1、使用、使用MySQLMySQL命令完成多功能数据命令完成多功能数据查询；查询；2 2、使用图形工具完成多功能数据、使用图形工具完成多功能数据查询查

3、询【知识要求知识要求】：1 1、简单查询、分类汇总、简单查询、分类汇总2 2、联接和嵌套查询、联接和嵌套查询3 3、数据操作的、数据操作的SQLSQL语句语句 8 8项目实训五项目实训五“员工管理系统员工管理系统”数据库查询操作数据库查询操作8 8任务一任务一 单表查询单表查询 使用数据库和表的主要目的是存储数据以便在需要时进行检使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过索、统计或组织输出，通过SQLSQL语句的查询可以从表或视图中迅语句的查询可以从表或视图中迅速方便地检索数据。速方便地检索数据。一、一、SELECTSELECT语句语句 语法格式：语法格式：

4、SELECT ALL | DISTINCT 输出列表达式输出列表达式, . FROM 表名表名1 , 表名表名2 /*FROM子句子句*/ WHERE 条件条件 /*WHERE子句子句*/ GROUP BY 列名列名 | 表达式表达式 | 列编号列编号 ASC | DESC, . /* GROUP BY 子句子句*/ HAVING 条件条件 /* HAVING 子句子句*/ ORDER BY 列名列名 | 表达式表达式 | 列编号列编号 ASC | DESC , . /*ORDER BY子句子句*/ LIMIT 偏移量偏移量, 行数行数|行数行数OFFSET偏移量偏移量 /*LIMIT子句子句

5、*/ 顺序严格地排序。例如，一个顺序严格地排序。例如，一个HAVINGHAVING子句必须位于子句必须位于GROUP BYGROUP BY子句之后，子句之后，并位于并位于ORDER BYORDER BY子句之前。子句之前。 1.1. 选择指定的列选择指定的列使用使用SELECTSELECT语句选择一个表中的某些列的语法格式如下语句选择一个表中的某些列的语法格式如下： SELECT 字段列表字段列表 FROM 表名称表名称各列名之各列名之间要以逗号分隔。间要以逗号分隔。【例例5.15.1】查询查询BookstoreBookstore数据库的数据库的MembersMembers表中各会员的会员姓名

6、、表中各会员的会员姓名、联系电话和注册时间。联系电话和注册时间。 USE Bookstore; SELECT 会员姓名,联系电话,注册时间 FROM Members;说明：当在说明：当在SELECTSELECT语句指定列的位置上使用语句指定列的位置上使用* *号时，表示选择表的号时，表示选择表的所有列，如要显示所有列，如要显示MembersMembers表中所有列，不必将所有字段名一一列出，表中所有列，不必将所有字段名一一列出，使用使用 SELECT * FROM Members;列别名列别名2. 2. 定义列别名定义列别名 当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题当希望查

7、询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列名之后使用时，可以在列名之后使用ASAS子句来更改查询结果的列别名。子句来更改查询结果的列别名。语法格式为：语法格式为： SELECT column_name AS column_alias【例例5.25.2】 查询查询BookBook表中图书类别为计算机的图书书名、作者和出版表中图书类别为计算机的图书书名、作者和出版社，结果中各列的标题分别指定为社，结果中各列的标题分别指定为namename、autherauther和和publisherpublisher。 SELECT 书名 AS name, 作者 AS auther, 出版

8、社 AS publisher FROM Book WHERE 图书类别= 计算机;注意：注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。当自定义的列标题中含有空格时，必须使用引号将标题括起来。 SELECT 学号 AS Student number, 姓名 AS Student name, 总学分 AS mark FROM XS WHERE 专业名= 计算机;替换查询结果中的数据替换查询结果中的数据3. 3. 替换查询结果中的数据替换查询结果中的数据在对表进行查询时，有时对所查询的某些列希望得到的是一种概念在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据

9、。例如查询而不是具体的数据。例如查询XSXS表的总学分，所希望知道的是学习的总表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。体情况，这时，就可以用等级来替换总学分的具体数字。要替换查询结果中的数据，则要使用查询中的要替换查询结果中的数据，则要使用查询中的CASECASE表达式，格式为：表达式，格式为：CASE WHEN 条件条件1 THEN 表达式表达式1 WHEN 条件条件2 THEN 表达式表达式2 ELSE 表达式表达式END【例例5.35.3】 查询查询BookBook表中图书编号、书名和数量，对其库存数量按表中图书编号、书名和数量，对其库存数量按

10、以下规则进行替换：若数量为空值，替换为以下规则进行替换：若数量为空值，替换为“尚未进货尚未进货”；若数量小于；若数量小于5 5，替换为，替换为“需进货需进货”；若数量在；若数量在5-505-50之间，替换为之间，替换为“库存正常库存正常”；若；若总学分大于总学分大于5050，替换为，替换为“库存积压库存积压”。列标题更改为。列标题更改为“库存库存”。 SELECT 图书编号, 书名, CASE WHEN 数量 IS NULL THEN 尚未进货 WHEN 数量 =5 and 数量5; MAXMAX和和MINMIN（2 2）MAXMAX和和MINMINMAXMAX和和MINMIN分别用于求表达式

11、中所有值项的最大值与最小值，语法格分别用于求表达式中所有值项的最大值与最小值，语法格式为：式为：MAX / MIN ( ALL | DISTINCT expression )其中，其中，expressionexpression是常量、列、函数或表达式，其数据类型可以是是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期数字、字符和时间日期 类型。类型。【例例5.95.9】 求订购了图书编号为求订购了图书编号为ISBN 8-5006-6625-XISBN 8-5006-6625-X的订单的最高的订单的最高订购册数和最低订购册数。订购册数和最低订购册数。 SELECT MAX(订购册数)

12、, MIN(订购册数) FROM Sell WHERE 图书编号 = ISBN 8-5006-6625-X;注意：注意：当给定列上只有空值或检索出的中间结果为空时，当给定列上只有空值或检索出的中间结果为空时，MAXMAX和和MINMIN函数的值也为空。函数的值也为空。SUMSUM函数和函数和AVGAVG函数函数（3 3）SUMSUM函数和函数和AVGAVG函数函数SUMSUM和和AVGAVG分别用于求表达式中所有值项的总和与平均值，语法格式为：分别用于求表达式中所有值项的总和与平均值，语法格式为：SUM / AVG ( ALL | DISTINCT expression )其中，其中，expr

13、essionexpression是常量、列、函数或表达式，其数据类型只能是数值型。是常量、列、函数或表达式，其数据类型只能是数值型。【例例5.105.10】 求订购了图书编号为求订购了图书编号为ISBN 8-5006-6625-XISBN 8-5006-6625-X图书的订购总册数。图书的订购总册数。 SELECT SUM(订购册数) AS 订购总册数 FROM Sell WHERE 图书编号 = ISBN 8-5006-6625-X; 【例例5.115.11】 求订购图书编号为求订购图书编号为ISBN 8-5006-6625-XISBN 8-5006-6625-X图书的订单平均册数。图书的订

14、单平均册数。 SELECT AVG(订购册数) AS 每笔订单平均册数 FROM Sell WHERE 图书编号 = ISBN 8-5006-6625-X; 对对YGGLYGGL数据库完成以下查询：数据库完成以下查询：1 1、计算员工总数、计算员工总数2 2、计算、计算salarysalary表中员工月收入的平均数表中员工月收入的平均数3 3、计算所有员工的总支出、计算所有员工的总支出4 4、显示最高收入和最低收入的员工的员工号、显示最高收入和最低收入的员工的员工号WHEREWHERE子句必须紧跟子句必须紧跟FROMFROM子句之后，在子句之后，在WHEREWHERE子句中，使用一个条件子句中

15、，使用一个条件从从FROMFROM子句的中间结果中选取行。其基本格式为：子句的中间结果中选取行。其基本格式为： WHERE 列列 运算符运算符 值值操操 作作 符符描描 述述= =等于等于不等于不等于 大于大于 =大于等于大于等于=小于等于小于等于BETWEENBETWEEN在某个范围内在某个范围内LIKELIKE搜索某种模式搜索某种模式比较运算比较运算WHEREWHERE子句会根据条件对子句会根据条件对FROMFROM子句的中间结果中的行一行一行地进行子句的中间结果中的行一行一行地进行判断，当条件为判断，当条件为TRUETRUE的时候，一行就被包含到的时候，一行就被包含到WHEREWHERE

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

17、大于等于）、（大于等于）、（相等或都等于空）、（相等或都等于空）、（不等于）、（不等于）、!=!=（不等于）。（不等于）。当两个表达式值均不为空值（当两个表达式值均不为空值（NULLNULL）时，除了）时，除了“”运算符，其他比运算符，其他比较运算返回逻辑值较运算返回逻辑值TRUETRUE（真）或（真）或FALSEFALSE（假）；而当两个表达式值中有一（假）；而当两个表达式值中有一个为空值或都为空值时，将返回个为空值或都为空值时，将返回UNKNOWNUNKNOWN。【例例5.125.12】 查询查询BookstoreBookstore数据库数据库BookBook表中书名为表中书名为“网页程序

18、设计网页程序设计”的记录。的记录。 SELECT 书名,单价 FROM Book WHERE 书名=网页程序设计; 【例例5.135.13】 查询查询BookBook表中单价大于表中单价大于3030的图书情况。的图书情况。 SELECT * FROM Book WHERE 单价30; MySQLMySQL有一个特殊的等于运算符有一个特殊的等于运算符“”，当两个表达式彼此相等或，当两个表达式彼此相等或都等于空值时，它的值为都等于空值时，它的值为TRUETRUE，其中有一个空值或都是非空值但不相等，其中有一个空值或都是非空值但不相等，这个条件就是这个条件就是FALSEFALSE。没有。没有UNKN

19、OWNUNKNOWN的情况。的情况。【例例5.145.14】 查询查询SellSell表中还未收货的订单情况。表中还未收货的订单情况。 SELECT 订单号,是否收货 FROM Sell WHERE 是否收货NULL; 比较运算举例比较运算举例逻辑运算逻辑运算通过逻辑运算符（通过逻辑运算符（ANDAND、OROR、XORXOR和和NOTNOT）组成更为复杂的查询条件。）组成更为复杂的查询条件。逻辑运算操作的结果是逻辑运算操作的结果是“1 1”或或“0 0”，分别表示，分别表示“truetrue”或或“falsefalse”。符号符号1 1符号符号2 2说明说明示例示例说明说明notnot! !

20、非运算非运算!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, not null;或： select (1 or 0), (0

21、 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.155.15】 查询查询SellSell表中已收货且已结清的订单情况。表中已收货且已结清的订单情况。 SELECT 订单号,是否收货,是否结清 FROM Sell WHERE 是否收货=已收货 AND 是否结清=已结清; 【例例5.165.16】

22、查询查询BookBook表中清华大学出版社和北京大学出版社出版的价格大于表中清华大学出版社和北京大学出版社出版的价格大于2525元的图书。元的图书。SELECT 书名,出版社,单价 FROM Book WHERE (出版社=清华大学出版社 OR 出版社=北京大学出版社 ) AND 单价=25;或： SELECT书名,出版社,单价 FROM Book WHERE (出版社=清华大学出版社 AND 单价=25) OR (出版社=北京大学出版社 AND 单价=25); 【思考题思考题】以下语句能否得到正确结果？为什么？以下语句能否得到正确结果？为什么？ SELECT 书名,出版社,单价 FROM B

23、ook WHERE 出版社=清华大学出版社 OR 出版社=北京大学出版社 AND 单价=25;WHERE子句举例子句举例对对YGGLYGGL数据库完成一下查询：数据库完成一下查询：1 1、显示月收入高于、显示月收入高于20002000元的员工号码元的员工号码2 2、查询、查询19701970年以后出生的员工的姓名和地址年以后出生的员工的姓名和地址3 3、显示女雇员的地址和电话，列标题要求用中文、显示女雇员的地址和电话，列标题要求用中文“地址地址”、“电电话话”表示表示LIKELIKE运算符运算符2. 2. 模式匹配（模式匹配（LIKELIKE运算符）运算符）LIKELIKE运算符用于指出一个字

24、符串是否与指定的字符串相匹配，其运运算符用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是算对象可以是charchar、varcharvarchar、texttext、datetimedatetime等类型的数据，返回逻辑等类型的数据，返回逻辑值值TRUETRUE或或FALSEFALSE。使用使用LIKELIKE进行模式匹配时，常使用特殊符号进行模式匹配时，常使用特殊符号_ _和和% %，可进行模糊查询。，可进行模糊查询。“% %”代表代表0 0个或多个字符，个或多个字符，“_ _”代表单个字符。由于代表单个字符。由于MySQLMySQL默认不区分大默认不区分大小写，要区分大小写时需

25、要更换字符集的校对规则。小写，要区分大小写时需要更换字符集的校对规则。【例例5.175.17】 查询查询MembersMembers表中姓表中姓“张张”的会员的身份证号、姓名及的会员的身份证号、姓名及注册时间。注册时间。 SELECT 身份证号,会员姓名, 注册时间 FROM Members WHERE 会员姓名 LIKE 张%; 【例例5.185.18】 查询查询BookBook表中图书编号倒数第二位为表中图书编号倒数第二位为T T的图书的图书编的图书的图书编号和书名。号和书名。 SELECT 图书编号, 书名 FROM Book WHERE 图书编号 LIKE %T_; 例例5.195.1

26、9】 查询查询BookBook表中书名中包含下画线的图书。表中书名中包含下画线的图书。 SELECT 图书编号,书名 FROM Book WHERE 书名 LIKE %#_% ESCAPE #; 范围比较范围比较3. 3. 范围比较范围比较 用于范围比较的关键字有两个：用于范围比较的关键字有两个：BETWEENBETWEEN和和ININ。当要查询的条件是某个值的范围时，可以使用当要查询的条件是某个值的范围时，可以使用BETWEENBETWEEN关键字。关键字。BETWEENBETWEEN关键字指出查询范围，格式为：关键字指出查询范围，格式为：expression NOT BETWEEN exp

27、ression1 AND expression2当不使用当不使用NOTNOT时，若表达式时，若表达式expressionexpression的值在表达式的值在表达式expression1expression1与与expression2expression2之间（包括这两个值），则返回之间（包括这两个值），则返回TRUETRUE，否则返回，否则返回FALSEFALSE；使；使用用NOTNOT时，返回值刚好相反。时，返回值刚好相反。注意：注意：expression1expression1的值不能大于的值不能大于expression2expression2的值。的值。【例例5.205.20】 查询查

28、询BookBook表中表中20102010年出版的图书的情况。年出版的图书的情况。 SELECT * FROM Book WHERE 出版时间 BETWEEN 2010-1-1 AND 2010-12-31; 使用使用ININ关键字可以指定一个值表，值表中列出所有可能的值，当与关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回值表中的任一个匹配时，即返回TRUETRUE，否则返回，否则返回FALSEFALSE。使用使用ININ关键字指定值表的格式为：关键字指定值表的格式为：expression IN ( expression ,n)【例例5.215.21】 查询查

29、询BookBook表中表中“高等教育出版社高等教育出版社”、“北京大学出版社北京大学出版社”和和“人民邮电出版社人民邮电出版社”出版的图书的情况。出版的图书的情况。 SELECT * FROM Book WHERE 出版社 IN ( 高等教育出版社, 北京大学出版社, 人民邮电出版社); 说明：说明：ININ关键字最主要的作用是表达子查询。关键字最主要的作用是表达子查询。范围比较举例范围比较举例空值比较空值比较4. 4. 空值比较空值比较当需要判定一个表达式的值是否为空值时，使用当需要判定一个表达式的值是否为空值时，使用IS NULLIS NULL关键字，格关键字，格式为：式为：express

30、ion IS NOT NULL当不使用当不使用NOTNOT时，若表达式时，若表达式expressionexpression的值为空值，返回的值为空值，返回TRUETRUE，否则，否则返回返回FALSEFALSE；当使用；当使用NOTNOT时，结果刚好相反。时，结果刚好相反。【例例5.225.22】 查询查询SellSell表中尚未发货的订单记录。表中尚未发货的订单记录。 SELECT * FROM Sell WHERE 是否发货 IS NULL;本例即查找总学分为空的学生，结果为空。本例即查找总学分为空的学生，结果为空。对对YGGL数据库完成一下查询：数据库完成一下查询：1、显示月收入高于、显

31、示月收入高于2000元的员工号码元的员工号码2、查询、查询1970年以后出生的员工的姓名和地址年以后出生的员工的姓名和地址3、 查询支出在查询支出在50-150之间的员工信息之间的员工信息4、查询财务部、研发部、市场部的员工信息、查询财务部、研发部、市场部的员工信息5、显示工作年限三年以上（含、显示工作年限三年以上（含3年）、学历在本科以上（含本科）年）、学历在本科以上（含本科）的男性员工的信息的男性员工的信息6、查找员工号码中倒数第二个数字为、查找员工号码中倒数第二个数字为0的姓名、地址和学历的姓名、地址和学历7 查找地址中包含查找地址中包含中山路中山路 的员工的信息的员工的信息 任务二任务

32、二 多表查询多表查询 前面介绍了使用前面介绍了使用SELECTSELECT子句选择列，本小节讨论子句选择列，本小节讨论SELECTSELECT查询的对象查询的对象（即数据源）的构成形式。（即数据源）的构成形式。SELECTSELECT的查询对象由的查询对象由FROMFROM子句指定，其格子句指定，其格式为：式为：FROM 表名表名1 AS 别名别名1 , 表名表名2 AS 别名别名2 /*查询表查询表*/ | JOIN子句子句 /*连接表连接表*/ 引用一个表：引用一个表：可以用两种方式引用一个表，第一种方式是使用可以用两种方式引用一个表，第一种方式是使用USEUSE语句让一个数语句让一个数据

33、库成为当前数据库，在这种情况下，如果在据库成为当前数据库，在这种情况下，如果在FROMFROM子句中指定表名，子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是表所属数据库的名字。例如，假设当前数据库是db1db1，现在要显示数据，现在要显示数据库库db2db2里的表里的表tbtb的内容，使用如下语句：的内容，使用如下语句：SELECT SELECT * * FROM db2.tb; FROM db2.tb;当然，在当然，在SELECTSELECT关键字后指定列名的时

34、候也可以在列名前带上所属关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。的，就没有必要去特别指定。【例例5.235.23】 从从MembersMembers表中检索出所有客户的信息，并使用表别名表中检索出所有客户的信息，并使用表别名UsersUsers。 SELECT * FROM Members AS Users; 引用多个表：引用多个表：如果要在不同表中查询数据，则必须在如果要在不同表中查询数据，则必须在FROMFROM子句中指定多个表。指子句中指定

35、多个表。指定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的连接。例如，在连接。例如，在XSCJXSCJ数据库中需要查找选修了离散数学课程的学生的姓数据库中需要查找选修了离散数学课程的学生的姓名和成绩，就需要将名和成绩，就需要将XSXS、KCKC和和XS_KCXS_KC三个表进行连接，才能查找到结果。三个表进行连接，才能查找到结果。【例例5.245.24】 查找查找BookstoreBookstore数据库中客户订购的图书书名，订购册数据库中客户订购的图书书名，订购册数和订购时间。数和订购时间。 SELECT Book.

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

37、每个表中行数之积。接可能得到的行数为每个表中行数之积。如表如表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和和B B全连接后得到如下全连接后

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

39、B B进行等值连接（进行等值连接（T1=T3 T1=T3 ），则如下表所示，只有两行。），则如下表所示，只有两行。【例例5.245.24】 查找查找BookstoreBookstore数据库中客户订购的图书书名，订购册数据库中客户订购的图书书名，订购册数和订购时间。数和订购时间。 SELECT Book.书名, Sell.订购册数, Sell.订购时间 FROM Book, Sell WHERE Book.图书编号=Sell.图书编号; JOINJOIN连接连接2. JOIN2. JOIN连接连接 第二种方式是使用第二种方式是使用JOINJOIN关键字的连接，语法格式如下：关键字的连接，语法格

40、式如下： table_reference INNER JOIN table_reference ON conditional_expr| USING (column_list)（1 1）内连接：指定了）内连接：指定了INNER关键字的连接是内连接。关键字的连接是内连接。【例例5.255.25】 要实现例要实现例4.234.23中的结果，可以使用以下语句：中的结果，可以使用以下语句：SELECT Book.书名, Sell.订购册数, Sell.订购时间 FROM Book inner join Sell ON Book.图书编号=Sell.图书编号; 该语句根据该语句根据ONON关键字后面的连

41、接条件，合并两个表，返回满足条件的行关键字后面的连接条件，合并两个表，返回满足条件的行。内连接是系统默认的，可以省略内连接是系统默认的，可以省略INNERINNER关键字。关键字。使用内连接后，使用内连接后，FROMFROM子句中子句中ONON条件主要用来连接表，其他并不属于条件主要用来连接表，其他并不属于连接表的条件可以使用连接表的条件可以使用WHEREWHERE子句来指定。子句来指定。【例例 5.265.26】 用用JOINJOIN关键字表达下列查询：查找购买了关键字表达下列查询：查找购买了“网页程序网页程序设计设计”且订购数量大于且订购数量大于5 5本的图书信息。本的图书信息。 SELE

42、CT 书名,订购册数 FROM Book JOIN Sell ON Book.图书编号 = Sell. 图书编号 WHERE 书名 = 网页程序设计 AND 订购册数5; JOINJOIN连接举例连接举例内连接还可以用于多个表的连接。内连接还可以用于多个表的连接。【例例5.275.27】 用用JOINJOIN关键字表达下列查询：查找购买了关键字表达下列查询：查找购买了“网页程序设网页程序设计计”且订购数量大于且订购数量大于5 5本的图书和会员姓名和订购册数。本的图书和会员姓名和订购册数。SELECT Book.图书编号, 会员姓名, 书名, 订购册数 FROM Sell JOIN Book O

43、N Book. 图书编号= Sell.图书编号 JOIN Members ON Sell.身份证号 = Members.身份证号 WHERE 书名 = 网页程序设计 AND 订购册数5 ; 多表连接举例多表连接举例作为特例，可以将一个表与它自身进行连接，称为自连接。若要在作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。为表指定两个别名，且对所有列的引用均要用别名限定。【例例5.285.28】 查找查找BookSt

44、oreBookStore数据库中订单不同、图书编号相同的图数据库中订单不同、图书编号相同的图书的订单号、图书编号和订购册数。书的订单号、图书编号和订购册数。 SELECT a.订单号,a.图书编号,a.订购册数 FROM Sell AS a JOIN Sell AS b ON a. 图书编号=b. 图书编号 where a. 订单号!=b. 订单号;自表连接自表连接 如果要连接的表中有列名相同，并且连接的条件就是列名相等，那如果要连接的表中有列名相同，并且连接的条件就是列名相等，那么么ONON条件也可以换成条件也可以换成USINGUSING子句。子句。USINGUSING（column_lis

45、tcolumn_list）子句用于为一）子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中系列的列进行命名。这些列必须同时在两个表中存在。其中column_listcolumn_list为两表中相同的列名。为两表中相同的列名。 【例例5.295.29】 查找查找MembersMembers表中所有订购过图书的会员姓名。表中所有订购过图书的会员姓名。 SELECT Distinct 会员姓名 FROM Members JOIN Sell USING (身份证号); 查询的结果为查询的结果为SellSell表中所有出现的身份证号对应的会员姓名。表中所有出现的身份证号对应的会员姓名。

46、例例5.295.29的语句与下列语句等价：的语句与下列语句等价： SELECT Distinct 会员姓名 FROM Members JOIN Sell ON Members.身份证号=Sell.身份证号 ;USINGUSING子句子句外连接外连接 指定了指定了OUTEROUTER关键字的连接为外连接。关键字的连接为外连接。外连接包括：外连接包括： 左外连接（左外连接（LEFT OUTER JOINLEFT OUTER JOIN）：）： 结果表中除了匹配行外，还包括左表有的但右表中不匹配的结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为行，对于这样的

47、行，从右表被选择的列设置为NULLNULL。 右外连接（右外连接（RIGHT OUTER JOINRIGHT OUTER JOIN）：）： 结果表中除了匹配行外，还包括右表有的但左表中不匹配的结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为行，对于这样的行，从左表被选择的列设置为NULLNULL。 外连接举例外连接举例【例例5.305.30】 查找所有图书的图书编号、数量及订购了图书的会员身查找所有图书的图书编号、数量及订购了图书的会员身份证号，若从未订购过，也要包括其情况。份证号，若从未订购过，也要包括其情况。 SELECT Book.图书编号,B

48、ook.数量,身份证号 FROM Book LEFT OUTER JOIN Sell ON Book.图书编号= Sell.图书编号;说明： 【例例5.315.31】 查找订购了图书的会员的订单号、图书编号和订购册数查找订购了图书的会员的订单号、图书编号和订购册数以及所有会员的会员姓名。以及所有会员的会员姓名。 SELECT 订单号,图书编号,订购册数, Members.会员姓名 FROM Sell RIGHT JOIN Members ON Members.身份证号= Sell.身份证号; 子查询子查询 在查询条件中，可以使用另一个查询的结果作为条件的一部分，在查询条件中，可以使用另一个查询

49、的结果作为条件的一部分，例如，判定列值是否与某个查询的结果集中的值相等，作为查询条例如，判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。件一部分的查询称为子查询。SQLSQL标准允许标准允许SELECTSELECT多层嵌套使用，多层嵌套使用，用来表示复杂的查询。子查询除了可以用在用来表示复杂的查询。子查询除了可以用在SELECTSELECT语句中，还可以语句中，还可以用在用在INSERTINSERT、UPDATEUPDATE及及DELETEDELETE语句中。语句中。 子查询通常与子查询通常与ININ、EXISTEXIST谓词及比较运算符结合使用。谓词及比较运算符

50、结合使用。（1 1）ININ子查询子查询 IN IN子查询用于进行一个给定值是否在子查询结果集中的判断，子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：格式为：expressionexpression NOT IN NOT IN ( subquery )( subquery )其中，其中，subquerysubquery是子查询。当表达式是子查询。当表达式expressionexpression与子查询与子查询subquerysubquery的结果表中的某个值相等时，的结果表中的某个值相等时，ININ谓词返回谓词返回TRUETRUE，否则返回，否则返回FALSEFALSE；若使用了

51、若使用了NOTNOT，则返回的值刚好相反。，则返回的值刚好相反。子查询举例子查询举例【例例5.325.32】 查找在查找在BookstoreBookstore数据库中张三的订单信息。数据库中张三的订单信息。 SELECT *FROM Sell WHERE 身份证号 IN( SELECT 身份证号 FROM Members WHERE 会员姓名 = 张三 );说明：在执行包含子查询的说明：在执行包含子查询的SELECTSELECT语句时，系统先执行子查询，产语句时，系统先执行子查询，产生一个结果表，再执行查询。生一个结果表，再执行查询。 本例中，先执行子查询：本例中，先执行子查询： SELECT

52、身份证号 FROM Members WHERE会员姓名= 张三 得到一个只含有身份证号列的表。再执行外查询，若得到一个只含有身份证号列的表。再执行外查询，若SellSell表中某行表中某行的身份证号列值等于子查询结果表中的任一个值，则该行就被选择。的身份证号列值等于子查询结果表中的任一个值，则该行就被选择。 子查询嵌套举例子查询嵌套举例ININ子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。的子查询。【例例5.335.33】 查找购买了除查找购买了除“网页程序设计网页程序设计”以外图书的会员信息。以外图书的会员信息。要查找会

53、员信息，先要知道会员的身份证号，而要知道购买了除要查找会员信息，先要知道会员的身份证号，而要知道购买了除“网页程序设计网页程序设计”以外图书的会员，可以按图书编号在以外图书的会员，可以按图书编号在SellSell表中表中查到，但是查到，但是“网页程序设计网页程序设计”的图书编号要通过查找的图书编号要通过查找BookBook才可以才可以获得。获得。SELECT * FROM Members WHERE 身份证号 IN (SELECT 身份证号 FROM Sell WHERE 图书编号 NOT IN ( SELECT 图书编号 FROM Book WHERE 书名=网页程序设计); 多表查询多表查

54、询对对YGGLYGGL数据库完成多表查询：数据库完成多表查询：1 1、查询每个雇员的基本情况和薪水情况、查询每个雇员的基本情况和薪水情况2 2、查询、查询“王林王林”的基本情况和所工作的部门名称的基本情况和所工作的部门名称3 3、查询月收入在、查询月收入在2000300020003000元的员工姓名和支出元的员工姓名和支出4 4、查询研发部在、查询研发部在19701970年以前出生的员工姓名和薪水情况年以前出生的员工姓名和薪水情况5 5、使用子查询查找工资收入大于、使用子查询查找工资收入大于20002000元的员工的基本信息元的员工的基本信息6 6、查找在财务部工作的员工的基本信息、查找在财务

55、部工作的员工的基本信息7 7、查找住在、查找住在“中山路中山路”的员工的工作部门名称的员工的工作部门名称 GROUP BYGROUP BY子句主要用于根据字段对行分组。例如，根据学生所子句主要用于根据字段对行分组。例如，根据学生所学的专业对学的专业对XSXS表中的所有行分组，结果是每个专业的学生成为一组。表中的所有行分组，结果是每个专业的学生成为一组。GROUP BYGROUP BY子句的语法格式如下：子句的语法格式如下：GROUP BY 列名列名 |表达式表达式 | 列编号列编号 ASC | DESC, . WITH ROLLUPGROUP BYGROUP BY子句后通常包含列名或表达式。子

56、句后通常包含列名或表达式。MySQLMySQL对对GROUP BYGROUP BY子句子句进行了扩展，可以在列的后面指定进行了扩展，可以在列的后面指定ASCASC（升序）或（升序）或DESCDESC（降序）。（降序）。GROUP BYGROUP BY可以根据一个或多个列进行分组，也可以根据表达式进行可以根据一个或多个列进行分组，也可以根据表达式进行分组，经常和聚合函数一起使用。分组，经常和聚合函数一起使用。【例【例5.385.38】 输出输出BookBook表中图书类别名。表中图书类别名。 SELECT 图书类别 FROM Book GROUP BY 图书类别; 【例【例5.395.39】 按

57、图书类别统计按图书类别统计BookBook表中各类图书的库存数。表中各类图书的库存数。 SELECT 图书类别,COUNT(*) AS 库存数 FROM Book GROUP BY 图书类别; 【例【例5.405.40】 按图书编号分类统计其订单数和订单的平均订购册数。按图书编号分类统计其订单数和订单的平均订购册数。 SELECT 图书编号, AVG(订购册数) AS 订购册数 , COUNT(订单号) AS 订单数 FROM Sell GROUP BY 图书编号; 使用带使用带ROLLUPROLLUP操作符的操作符的GROUP BYGROUP BY子句，可指定在结果集内不仅子句，可指定在结果

58、集内不仅包含由包含由 GROUP BY GROUP BY 提供的正常行，还包含汇总行。提供的正常行，还包含汇总行。【例例5.415.41】按图书类别、出版社分类统计按图书类别、出版社分类统计BookBook表中各类图书的库存表中各类图书的库存数。数。 SELECT 图书类别, 出版社, Sum(数量) AS 库存数 FROM Book GROUP BY 图书类别, 出版社; 请将执行结果与以下语句比较：请将执行结果与以下语句比较： SELECT 图书类别, 出版社, Sum(数量) AS 库存数 FROM Book GROUP BY 图书类别, 出版社 WITH ROLLUP;使用使用HAVI

59、NGHAVING子句的目的与子句的目的与WHEREWHERE子句类似，不同的是子句类似，不同的是WHEREWHERE子句子句是用来在是用来在FROMFROM子句之后选择行，而子句之后选择行，而HAVINGHAVING子句用来在子句用来在GROUP BYGROUP BY子子句后选择行。例如，查找句后选择行。例如，查找XSCJXSCJ数据库中平均成绩在数据库中平均成绩在8585分以上的学分以上的学生，就是在生，就是在XS_KCXS_KC表上按学号分组后筛选出符合平均成绩大于等于表上按学号分组后筛选出符合平均成绩大于等于8585的学生。的学生。语法格式：语法格式： HAVING 条件条件其中，其中，

60、where_definitionwhere_definition是选择条件，条件的定义和是选择条件，条件的定义和WHEREWHERE子子句中的条件类似，不过句中的条件类似，不过HAVINGHAVING子句中的条件可以包含聚合函数，子句中的条件可以包含聚合函数，而而WHEREWHERE子句中则不可以。子句中则不可以。SQLSQL标准要求标准要求HAVINGHAVING必须引用必须引用GROUP BYGROUP BY子句中的列或用于聚合子句中的列或用于聚合函数中的列。不过，函数中的列。不过，MySQLMySQL支持对此工作性质的扩展，并允许支持对此工作性质的扩展，并允许HAVINGHAVING引用