第九章 连接查询和分组查询.docx

1、第九章 连接查询和分组查询第九章 连接查询和分组查询本章工作任务 统计学生考试信息 从多个数据表中取得学生考试成绩单本章技能目标 使用GROUP BY进行分组查询 掌握多表连接查询本章简介上一章介绍了模糊查询、聚合函数，解决了不确定具体条件情况下的查询及统计、计算方面的查询问题。本章接着介绍常用的查询分组查询、多表连接查询，从不同方面将数据进行分组汇总，从不同的数据表中查询出需要的数据。预习作业请按以下步骤预习本章内容：1. 带着以下任务，阅读、学习本章的相关资料2. 标注出本章看不懂或存在疑惑的部分3. 整理、记录学习中的问题4. 熟记本章的英文单词请到中心听课前，完成下面的作业。1. 背诵

2、英文单词请在预习时找出下列单词在教材中的用法，了解它们的含义和发音，并填写于横线处。(1) group: (2) join: (3) left: (4) right: (5) outer: (6) client: 2. 预习并回答以下问题阅读本章内容，回答以下问题(1) 为什么要使用分组查询？(2) 多表连接查询有哪几种？语法分别是什么？3. 预习并完成编程题请使用SQL语言编写代码，完成以下功能：从“成绩”表中查询每门课程的最高分。4. 记录预习的问题记录学习中遇到的问题，填写于下面的横线上，以便集中探讨解决11.1 分组查询11.1.1 使用GROUP BY 进行分组查询 首先看一下学生成

3、绩表，成绩表中存储了学生参加考试的成绩。有时，可能需要统计不同课程的平均成绩，也就是说，首先需要对成绩表中的记录按照课程来分组，分组以后再针对每个组进行平均成绩计算。 这种情况应用很普遍。例如，一个电器销售店，销售洗衣机、冰箱、电视等，月末时，就需要分类统计洗衣机销售总数、冰箱销售总数、电视销售总数。这个时候就需要首先分类，将冰箱、洗衣机、电视分成三组，然后在每个组的基础上分别进行汇总和统计。这实际上也就是分组查询的原理。分组后的统计计算要利用前面学习过的聚合函数，如SUM( )、AVG( )等。 看一个具体例子，假设学生成绩表中有以下数据记录，如图11.1所示。图11.1 学生成绩表中的数据

4、 从图中的数据可以看出，该成绩记录了几门课程的学生成绩，课程编号(SubjectId)分别是1、3。此时，要统计不同课程的平均分数。首先把相同的SubjectId都分为一组，这样就将数据分成了几组，然后针对每一组使用前面的聚合函数取平均值，如图11.2所示，这样就得到了每门课程的平均分数。图11.2 在分组的基础上分别统计 在编写SQL语句之前，先想想我们想要的输出结果是什么？我们想要的输出结果应该首先是不同的课程，其次是每门课程的平均分。那么，我们还能够在查询中输出显示这张表中学生编号的信息吗？答案显然是不行了。很明显，学生的编号与课程再也不是一对一的关系，因为课程已经被“分组”了，分组后的

5、数量减少为三组，而学生没有被“分组”，依然保持原来的个数。 以上这种类型的查询，在MySQL中叫作分组查询，分组查询采用GROUP BY子句来实现。采用分组查询实现的SQL语句如下。 SELECT SubjectId，AVG(StudentResult) AS 课程平均成绩 FROM Result GROUP BY SubjectId; 查询结果如图11.3所示。图11.3 分组查询的输出结果 下面再来看几个分组的例子。 （1）查询男女学生的人数各是多少。 分析： 首先按照性别列进行分组：GROUP BY Sex。 其次对每个组进行总数的统计，用到聚合函数COUNT( ) 完整的SQL语句如下

6、。 SELECT Sex， COUNT(*) AS 人数 FROM Student GROUP BY Sex 查询结果如图11.4所示。图11.4 查询男女学生的人数 （2）查询每个年级的总人数。 分析： 思路同前面的一样，按照年级进行分组即可。 完整的SQL语句如下。 SELECT GradeId， COUNT(*) AS 年级人数 FROM Student GROUP BY GradeId 查询结果如图11.5所示。图11.5 查询每个年级的总人数 （3）查询每个科目的平均分，并且按照由高到低的顺序排列显示。 对比： 思路同前面的一样，按照科目进行分组。 分数由高到低进行排序，需要用到OR

7、DER BY子句，问题是这个ORDER BY 子句放在哪个位置？GROUP BY 子句之前还是之后？现在来仔细想一下，进行排序时，应该是对分完组后的平均分进行排序，这样想来应该放在GROUP BY 子句之后，答案的确如此，应该放在GROUP BY子句之后。 完整的SQL语句如下。 SELECT SubjectId ，AVG(StudentResult) AS 课程平均成绩 FROM Result GROUP BY SubjectId ORDER BY AVG(StudentResult) DESC 查询结果如图11.6所示。图11.6 查询每个科目的平均分并排序11.1.2 多列分组查询 分组

8、查询有时候可能还要按照多个列来进行分组。例如，学生信息表Student中记录了每个学生的信息，包括所属年级和性别等，如图11.7所示是表中的部分学生记录。 如果要统计每个学期的男女学生人数，则理论上先把每个学期分开，然后针对每个学期，把那男女学生人数各自统计，也就是需要按照两个列进行分组：所属年级和性别。SQL语句如下。图11.7 学生信息表 SELECT COUNT(*) AS 人数，GradeId AS 年级，Sex AS 性别 FROM Student GROUP BY GradeId，Sex ORDER BY GradeId 查询输出的结果如图11.8所示。图11.8 分组查询后的每个

9、年级男女学生人数 不难理解，使用GROUP BY 关键字时，在SELECT 列表中可以指定的列是有限的，仅允许以下几项。 被分组的列。 为每个分组返回一个值的表达式，如聚合函数计算出的列。11.1.3 使用HAVING子句进行分组筛选 通过前面的学习，我们已经基本了解了分组查询的意义和原理，下面再来分析以下几个查询需求。（1） 查询年级总人数超过15的年级。首先可以通过分组查询获取每个年级的总人数，对应的T-SQL语句如下。 SELECT COUNT(*) AS 人数，GradeId AS 年级 FROM Student GROUP BY GradeId查询结果如图11.9所示。图11.9 每

10、个年级的总人数但是还有一个条件：人数超过15的年级。这个时候，牵扯到分组统计后的条件限制，限制条件为COUNT(*)15。这时候使用WHERE子句是不能满足查询要求的，因为WHERE子句只能对没有分组统计前的数据行进行筛选。对分组后的条件的筛选必须使用HAVING子句，简单地说，HAVING子句用来对分组后的数据进行筛选，将“组”看作“列”来限定条件。以上需求的SQL语句如下。 SELECT COUNT(*) AS 人数，GradeId AS 年级 FROM Student GROUP BY GradeId HAVING COUNT(*)15查询结果如图11.10所示。图11.10 总人数超过

11、15的年级（2） 查询分均分达到及格的课程信息。在查询每个科目平均分的基础上，增加了一个条件：平均分及格的科目。这样按照科目进行分组后，使用AVG(StudentResult)=60控制及格条件即可。SQL语句如下。 SELECT SubjectId AS 课程编号，AVG(StudentResult) AS 课程平均成绩 FROM Result GROUP BY SubjectId HAVING AVG(StudentResult) =60查询结果如图11.11所示。图11.11 查询平均分及格的科目信息HAVING和WHERE子句可以在同一个SELECT语句中一起使用，使用顺序应如图11.

12、12所示。图11.12 WHERE、GROUP BY和HAVING的使用次序提示：在SELECT语句中，WHERE、GROUP BY、HAVING子句和聚合函数的执行次序如下：WHERE子句从数据源中去掉不符合其搜索条件的数据；GROUP BY子句搜索数据行到各个组中，统计函数为各个组计算统计值；HAVING子句去掉不符合其组搜索条件的各组数据行。（3） 查询每门课程及格总人数和及格学生的平均分。分析：通过需求了解到所查询的信息，都是要求及格的统计，这样就首先从数据源中将不及格的学生信息进行滤除，然后对符合及格要求的数据再进行分组处理。完整的SQL语句如下。 SELECT COUNT(*) A

13、S人数，AVG(StudentResult) AS 平均分，SubjectId AS 课程 FROM Result WHERE StudentResult = 60 GROUP BY SubjectId查询结果如图11.13所示。图11.13 及格总人数和及格平均分（4） 查询每门课程及格总人数和及格平均分在80分以上的记录。分析：同上一个查询需求思路一致，只是加了一个对分组后数据进行筛选的条件：及格平均分在80分以上，增加HAVING子句即可。完整的SQL语句如下。 SELECT COUNT(*) AS 人数，AVG(StudentResult) AS 平均分，SubjectId AS 课程

14、 FROM Result WHERE StudentResult =60 GROUP BY SubjectId HAVING AVG(StudentResult) =80查询结果如图11.14所示。图11.14 及格总人数和及格平均分80分以上（5） 在按照部门分类的员工表中，查询“有两个及其以上员工的工资不低于2000的部门编号”。分析： 利用WHERE子句首先滤除工资低于2000的记录，然后再按照部门进行分组，最后对分组后的记录进行条件限定。 完整的SQL语句如下。 SELECT 部门编号，COUNT(*) FROM 员工信息表 WHERE 工资 = 2000 GROUP BY 部门编号

15、HAVING COUNT(*) 111.1.4 技能训练 上机练习1 练习使用分组查询学生相关信息 需求说明 查询每个年级的总学时数，并按照升序排列。 查询每个参加考试的学员的平均分。 查询每门课程的平均分，并按照降序排列。 查询每个学生参加的所有考试的总分，并按照降序排列。上机练习2 指导限定条件的分组查询 训练要点 GROUP BY 子句。 HAVING 子句。 聚合函数。需求说明 查询每学期学时数超过50的课程数。 查询每学期学生的平均年龄。 查询北京地区的每学期学生人数。 查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列。 查询考试日期为2013年3月22日的课程的及格平

16、均分。 查询至少一次考试不及格的学生学号、不及格次数。难点分析 需求2中计算学生的年龄。在表Student中存储的是学生的出生日期，由出生日期计算年龄，需要用到日期函数DATESTAMPDIFF()，与当前的日期比较，计算出天数差，然后天数差除以365，即可得学生的年龄。参考如下SQL语句。DATESTAMPDIFF(day,BornDate,NOW()/365查询结果如图11.15所示。图11.15 每学期学生的平均年龄 需求6中至少一次不及格的信息，首先用WHERE进行不及格的条件限定，然后再根据学号来分别获取所需信息。11.2 多表连接查询 前面我们讲述过的所有查询，都是基于单个数据库标

17、的查询，本节介绍涉及多个表的数据查询。11.2.1 多表连接查询的分类 在上面介绍的学生成绩查询中，我们每次显示的都是学生的编号信息，因为该表中只存储了学生的编号。实际上最好显示学生的姓名，而姓名却存储在学生信息表中，像这种需要从多个表中选择或者比较数据项的情况，就需要使用多表连接查询。 多表连接查询实际上是通过各个表之间共同列的关联性来查询数据的，它是关系数据库查询最主要的特征。 以下是几种常用的连接查询方式：内连接、外连接。1. 内连接查询内连接查询是最典型、最常用的连接查询，它根据表中共同的列来进行匹配。特别是两个表存在主外键关系时通常会使用内连接查询。2. 外连接查询外连接查询是至少返

18、回一个表中的所有记录，根据匹配条件有选择性地返回另一张表的记录。外连接可以是左外连接、右外连接。下面我们一一介绍这几种选择查询的具体含义和用法。11.2.2 内连接查询 内连接查询通常会使用“=”或“”等比较运算符来判断两列数据值是否相等，上面所说的根据学生号来判断学生姓名的连接就是一种内连接。 内连接使用INNER JOINON关键字或WHERE子句来进行表之间的关联。内连接查询可以通过两种方式实现。1. 在WHERE子句中指定连接条件例如，查询学生姓名和成绩的SQL如下。 SELECT Student.StudentName, Result.SubjectId, Result.Studen

19、tResult FROM Student,Result WHERE Student.StudentNo=Result.StudentNo上面这种形式的查询，相当于FROM后面紧跟了两个表名，然后在字段列表中用“表名.列名”来区分别，再在WHERE条件子句中加以判断，要求学生编号信息相等。2. 在FROM子句中使用INNER JOINON上面的查询也可以通过以下的JOINON子句来实现。 SELECT S.StudentName,C.SubjectId,C.StudentResult FROM Student AS S INNER JOIN Result AS C ON(S.StudentNo

20、= C.StudentNo)在上面的内连接查询中： INNER JOIN 用来连接两个表。 INNER 可以省略。 ON 用来设置条件。 AS指定标的“别名”。如果查询的列名在用到的两个或多个表中不重复，则对这一列的引用不必用表名来限定。查询结果如图11.16所示。图11.16 两表连接查询再看以下SQL语句。 SELECT S.StudentName,C.SubjectId,C.StudentResult FROM Student AS S INNER JOIN Result AS C ON (S.StudentNo = C.StudentNo) WHERE C.StudentResult=

21、60 AND C.SubjectId=1查询将返回科目编号为1的及格学生的姓名和分数。WHERE子句用来限定查询条件。查询结果如图11.17所示。图11.17 科目编号为1的及格学生信息内连接查询通常不仅仅连接两个表，有时候还会涉及三个表或者更多表。例如，除了学生信息表，学生成绩表之外，还存在课程表。上面的查询不仅仅要显示学生姓名、分数，而且要通过课程编号来显示课程名称表中对应课程的名称，可以使用以下三表连接查询的SQL语句来实现。SELECT S.StudentName AS 学生姓名，CS.SubjectName AS 课程名称，R.StudentResult AS 考试成绩FROM St

22、udent AS SINNER JOIN Result AS R ON (S.StudentNo = R.StudentNo)INNER JOIN Subject AS CS ON (CS.SubjectId = R.SubjectId) 执行以上的SQL，查询结果如图11.18所示。图11.18 三表连接查询 注意以上的数据分别来自三个不同的数据表。11.2.3 技能训练上机练习3 指导两表内连接查询信息 训练要点 INNNER JOINON的使用。 WHERE的使用。需求说明 以下所有查询均使用INNER JOINON和WHERE两种方式完成。 查询学生姓名、所属年级名称及联系电话。 查询

23、年级编号为1的年级名称、科目名称及学时。 查询参加科目编号为1的考试的学生姓名、分数、考试日期。 查询学号为S1101007的学生参加的考试科目名称、分数、考试日期。 查询参加考试的学生学号、所考科目名称、分数、考试日期。难点分析 需求2的年级名称可以从年级表Grade中获取，科目名称及学时从科目表Subject中获取，而科目表Subject中存在年级编号GradeId,通过GradeId连接这两张表即可获取所需信息，参考如下SQL语句。SELECT FROM Subject AS JINNER JOIN Grade AS G ON J.GradeId = G.GradeId 需求2中还有一个

24、限定条件：年级编号为1，使用WHERE进行限定即可。参考如下SQL语句。SELECT FROM Subject AS JINNER JOIN Grade AS G ON J.GradeId = G.GradeIdWHERE G.GradeId=1上机练习4 练习三表内连接查询信息需求说明 以下所有查询均使用INNER JOINON和WHERE两种方式完成。 查询学生学号、姓名、考试科目名称及成绩。 查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期。 保存为“三表内连接查询信息.sql”文件。11.2.4 外连接查询 通过上面例子可以看出：内连接查询的结果是从两个或两个以上表的组合

25、中挑选出符合连接条件的数据，如果数据无法满足连接条件则将其忽略。在内连接查询中，参与连接的表的地位是平等的。 与内连接查询相对的方式称为外连接查询。在外连接查询中参与连接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中；对那些不符合连接条件的列，将被填上NULL值（空值）后再返回到结果集中。1. 左外连接查询左外连接查询的结果集包括LEFT JOIN子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。若左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列均为空值。左外连接查询使用LEFT JOINON或LEFT OUTER JOINON

26、关键字来进行表之间的关联。例如，要统计所有学生的考试情况，要求显示所有参加考试学生的每次考试分数，没有参加考试的学生也要显示出来。这时候，以学生信息表为主表（有时也叫左表）、学生成绩表为从表的左外连接查询如下SQL语句所示。 SELECT S.StudentName,R.SubjectId,R.StudentResult FROM Student AS S LEFT OUTER JOIN Result AS R ON S.StudentNo = R.StudentNo其中，从学生信息表中把每一条记录跟成绩表的记录进行数据匹配（匹配条件为S.StudentNo=R.StudentNo）。若匹配成

27、功，则返回到记录集（取S.StudentName,R.SubjectId,R.StudentResult的值）；若没有找到匹配的记录，则返回NULL值填充记录集。有部分学生没有参加过任何科目的考试，所以成绩表中没有相关的考试记录，对应的科目编号和成绩以NULL（空值）填充，查询结果如图11.19所示。图11.19 左外连接查询想一想：如下SQL语句返回的结果是什么？ SELECT CS.SubjectName,R.StudentNo,R.StudentResult FROM Subject AS CS LEFT OUTER JOIN Result AS R ON CS.SubjectId =

28、R.SubjectId2. 右外连接查询右外连接查询与左外连接查询类似，只不过要包含右表中所有匹配的行。若右表中有的项在左表中没有对应的项，则以NULL值填充。右外连接查询使用RIGHT JOINON 或 RIGHT OUTER JOINON关键字来进行表之间的关联。例如在某数据库中，存在书籍表Titles和出版商表Publishers之间的右外连接将包括所有的出版商，在Titles表中没有书名的出版商（很久没出书了）也会被列出。SELECT Titles.Title_id,Titles.Title,Publishers.Pub_nameFROM titlesRIGHT OUTER JOIN

29、Publishers ON Titles.Pub_id = Publishers.Pub_id想一想在数据库MySchool中，考虑到各表之间的关系，如下两条SQL语句返回的结果是否相同？第一条语句如下。 SELECT CS.SubjectName,R.StudentNo,R.StudentResult FROM Subject AS CS RIGHT OUTER JOIN Result AS R ON CS.SubjectId=R.SubjectId第二条语句如下。 SELECT CS.SubjectName,R.StudentNo,R.StudentResult FROM Subject

30、AS CS INNER JOIN Result AS R ON CS.SubjectId=R.SubjectId11.2.5 技能训练上机练习5 练习使用外连接查询信息需求说明 查询所有科目的考试信息（某些科目可能还没有被考过），如图11.20所示。图11.20 所有科目考试情况 查询从未考试的科目信息。提示：根据需求1的思路，使用外连接，在成绩表中没有的科目考试记录信息，即增加了WHERE条件，条件如下。WHERE IS NULL AND IS NULL 查询所有年级对应的学生信息（某些年级可能还没有学生就读），如图11.21所示。图11.21 所有年级对应的学生信息 保存为“使用外连接查询信息.sql”文件。想一想：根据数据库MySchool各表之间的关系，以上查询需求使用左外连接还是右外连接，还是都可以实现？如果都能够实现，请使用两种方法实现。本章总结 分组查询是针对表中不同的组分类统计的，GR