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

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

《第九章 连接查询和分组查询.docx》由会员分享，可在线阅读，更多相关《第九章 连接查询和分组查询.docx（22页珍藏版）》请在冰豆网上搜索。

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

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

本章工作任务

✓统计学生考试信息

✓从多个数据表中取得学生考试成绩单

本章技能目标

✓使用GROUPBY进行分组查询

✓掌握多表连接查询

本章简介

上一章介绍了模糊查询、聚合函数，解决了不确定具体条件情况下的查询及统计、计算方面的查询问题。

本章接着介绍常用的查询——分组查询、多表连接查询，从不同方面将数据进行分组汇总，从不同的数据表中查询出需要的数据。

预习作业

请按以下步骤预习本章内容：

1.带着以下任务，阅读、学习本章的相关资料

2.标注出本章看不懂或存在疑惑的部分

3.整理、记录学习中的问题

4.熟记本章的英文单词

请到中心听课前，完成下面的作业。

1.背诵英文单词

请在预习时找出下列单词在教材中的用法，了解它们的含义和发音，并填写于横线处。

（1）group:

（2）join:

（3）left:

（4）right:

（5）outer:

（6）client:

2.预习并回答以下问题

阅读本章内容，回答以下问题

（1）为什么要使用分组查询？

（2）多表连接查询有哪几种？

语法分别是什么？

3.预习并完成编程题

请使用SQL语言编写代码，完成以下功能：

从“成绩”表中查询每门课程的最高分。

4.记录预习的问题

记录学习中遇到的问题，填写于下面的横线上，以便集中探讨解决

——————————————————————————————————————

——————————————————————————————————————

——————————————————————————————————————

11.1分组查询

11.1.1使用GROUPBY进行分组查询

首先看一下学生成绩表，成绩表中存储了学生参加考试的成绩。

有时，可能需要统计不同课程的平均成绩，也就是说，首先需要对成绩表中的记录按照课程来分组，分组以后再针对每个组进行平均成绩计算。

这种情况应用很普遍。

例如，一个电器销售店，销售洗衣机、冰箱、电视等，月末时，就需要分类统计洗衣机销售总数、冰箱销售总数、电视销售总数。

这个时候就需要首先分类，将冰箱、洗衣机、电视分成三组，然后在每个组的基础上分别进行汇总和统计。

这实际上也就是分组查询的原理。

分组后的统计计算要利用前面学习过的聚合函数，如SUM（）、AVG（）等。

看一个具体例子，假设学生成绩表中有以下数据记录，如图11.1所示。

图11.1学生成绩表中的数据

从图中的数据可以看出，该成绩记录了几门课程的学生成绩，课程编号（SubjectId）分别是1、3。

此时，要统计不同课程的平均分数。

首先把相同的SubjectId都分为一组，这样就将数据分成了几组，然后针对每一组使用前面的聚合函数取平均值，如图11.2所示，这样就得到了每门课程的平均分数。

图11.2在分组的基础上分别统计

在编写SQL语句之前，先想想我们想要的输出结果是什么？

我们想要的输出结果应该首先是不同的课程，其次是每门课程的平均分。

那么，我们还能够在查询中输出显示这张表中学生编号的信息吗？

答案显然是不行了。

很明显，学生的编号与课程再也不是一对一的关系，因为课程已经被“分组”了，分组后的数量减少为三组，而学生没有被“分组”，依然保持原来的个数。

以上这种类型的查询，在MySQL中叫作分组查询，分组查询采用GROUPBY子句来实现。

采用分组查询实现的SQL语句如下。

SELECTSubjectId，AVG（StudentResult）AS课程平均成绩

FROMResult

GROUPBYSubjectId;

查询结果如图11.3所示。

图11.3分组查询的输出结果

下面再来看几个分组的例子。

（1）查询男女学生的人数各是多少。

分析：

首先按照性别列进行分组：

GROUPBYSex。

其次对每个组进行总数的统计，用到聚合函数COUNT（）

完整的SQL语句如下。

SELECTSex，COUNT（*）AS人数FROMStudent

GROUPBYSex

查询结果如图11.4所示。

图11.4查询男女学生的人数

（2）查询每个年级的总人数。

分析：

思路同前面的一样，按照年级进行分组即可。

完整的SQL语句如下。

SELECTGradeId，COUNT（*）AS年级人数FROMStudent

GROUPBYGradeId

查询结果如图11.5所示。

图11.5查询每个年级的总人数

（3）查询每个科目的平均分，并且按照由高到低的顺序排列显示。

对比：

思路同前面的一样，按照科目进行分组。

分数由高到低进行排序，需要用到ORDERBY子句，问题是这个ORDERBY子句放在哪个位置？

GROUPBY子句之前还是之后？

现在来仔细想一下，进行排序时，应该是对分完组后的平均分进行排序，这样想来应该放在GROUPBY子句之后，答案的确如此，应该放在GROUPBY子句之后。

完整的SQL语句如下。

SELECTSubjectId，AVG（StudentResult）AS课程平均成绩FROMResult

GROUPBYSubjectId

ORDERBYAVG（StudentResult）DESC

查询结果如图11.6所示。

图11.6查询每个科目的平均分并排序

11.1.2多列分组查询

分组查询有时候可能还要按照多个列来进行分组。

例如，学生信息表Student中记录了每个学生的信息，包括所属年级和性别等，如图11.7所示是表中的部分学生记录。

如果要统计每个学期的男女学生人数，则理论上先把每个学期分开，然后针对每个学期，把那男女学生人数各自统计，也就是需要按照两个列进行分组：

所属年级和性别。

SQL语句如下。

图11.7学生信息表

SELECTCOUNT（*）AS人数，GradeIdAS年级，SexAS性别FROMStudent

GROUPBYGradeId，Sex

ORDERBYGradeId

查询输出的结果如图11.8所示。

图11.8分组查询后的每个年级男女学生人数

不难理解，使用GROUPBY关键字时，在SELECT列表中可以指定的列是有限的，仅允许以下几项。

Ø被分组的列。

Ø为每个分组返回一个值的表达式，如聚合函数计算出的列。

11.1.3使用HAVING子句进行分组筛选

通过前面的学习，我们已经基本了解了分组查询的意义和原理，下面再来分析以下几个查询需求。

（1）查询年级总人数超过15的年级。

首先可以通过分组查询获取每个年级的总人数，对应的T-SQL语句如下。

SELECTCOUNT（*）AS人数，GradeIdAS年级FROMStudent

GROUPBYGradeId

查询结果如图11.9所示。

图11.9每个年级的总人数

但是还有一个条件：

人数超过15的年级。

这个时候，牵扯到分组统计后的条件限制，限制条件为COUNT（*）>15。

这时候使用WHERE子句是不能满足查询要求的，因为WHERE子句只能对没有分组统计前的数据行进行筛选。

对分组后的条件的筛选必须使用HAVING子句，简单地说，HAVING子句用来对分组后的数据进行筛选，将“组”看作“列”来限定条件。

以上需求的SQL语句如下。

SELECTCOUNT（*）AS人数，GradeIdAS年级FROMStudent

GROUPBYGradeId

HAVINGCOUNT（*）>15

查询结果如图11.10所示。

图11.10总人数超过15的年级

（2）查询分均分达到及格的课程信息。

在查询每个科目平均分的基础上，增加了一个条件：

平均分及格的科目。

这样按照科目进行分组后，使用AVG（StudentResult）>=60控制及格条件即可。

SQL语句如下。

SELECTSubjectIdAS课程编号，AVG（StudentResult）AS课程平均成绩

FROMResult

GROUPBYSubjectId

HAVINGAVG（StudentResult）>=60

查询结果如图11.11所示。

图11.11查询平均分及格的科目信息

HAVING和WHERE子句可以在同一个SELECT语句中一起使用，使用顺序应如图11.12所示。

图11.12WHERE、GROUPBY和HAVING的使用次序

提示：

在SELECT语句中，WHERE、GROUPBY、HAVING子句和聚合函数的执行次序如下：

WHERE子句从数据源中去掉不符合其搜索条件的数据；GROUPBY子句搜索数据行到各个组中，统计函数为各个组计算统计值；HAVING子句去掉不符合其组搜索条件的各组数据行。

（3）查询每门课程及格总人数和及格学生的平均分。

分析：

通过需求了解到所查询的信息，都是要求及格的统计，这样就首先从数据源中将不及格的学生信息进行滤除，然后对符合及格要求的数据再进行分组处理。

完整的SQL语句如下。

SELECTCOUNT（*）AS人数，AVG（StudentResult）AS平均分，SubjectIdAS课程

FROMResult

WHEREStudentResult>=60

GROUPBYSubjectId

查询结果如图11.13所示。

图11.13及格总人数和及格平均分

（4）查询每门课程及格总人数和及格平均分在80分以上的记录。

分析：

同上一个查询需求思路一致，只是加了一个对分组后数据进行筛选的条件：

及格平均分在80分以上，增加HAVING子句即可。

完整的SQL语句如下。

SELECTCOUNT（*）AS人数，AVG（StudentResult）AS平均分，SubjectIdAS课程

FROMResult

WHEREStudentResult>=60

GROUPBYSubjectId

HAVINGAVG（StudentResult）>=80

查询结果如图11.14所示。

图11.14及格总人数和及格平均分80分以上

（5）在按照部门分类的员工表中，查询“有两个及其以上员工的工资不低于2000的部门编号”。

分析：

利用WHERE子句首先滤除工资低于2000的记录，然后再按照部门进行分组，最后对分组后的记录进行条件限定。

完整的SQL语句如下。

SELECT部门编号，COUNT（*）FROM员工信息表

WHERE工资>=2000

GROUPBY部门编号

HAVINGCOUNT（*）>1

11.1.4技能训练

[上机练习1]

练习——使用分组查询学生相关信息

需求说明

Ø查询每个年级的总学时数，并按照升序排列。

Ø查询每个参加考试的学员的平均分。

Ø查询每门课程的平均分，并按照降序排列。

Ø查询每个学生参加的所有考试的总分，并按照降序排列。

[上机练习2]

指导——限定条件的分组查询

训练要点

ØGROUPBY子句。

ØHAVING子句。

Ø聚合函数。

需求说明

Ø查询每学期学时数超过50的课程数。

Ø查询每学期学生的平均年龄。

Ø查询北京地区的每学期学生人数。

Ø查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列。

Ø查询考试日期为2013年3月22日的课程的及格平均分。

Ø查询至少一次考试不及格的学生学号、不及格次数。

难点分析

Ø需求2中计算学生的年龄。

在表Student中存储的是学生的出生日期，由出生日期计算年龄，需要用到日期函数DATESTAMPDIFF（），与当前的日期比较，计算出天数差，然后天数差除以365，即可得学生的年龄。

参考如下SQL语句。

DATESTAMPDIFF（day,BornDate,NOW（））/365

查询结果如图11.15所示。

图11.15每学期学生的平均年龄

Ø需求6中至少一次不及格的信息，首先用WHERE进行不及格的条件限定，然后再根据学号来分别获取所需信息。

11.2多表连接查询

前面我们讲述过的所有查询，都是基于单个数据库标的查询，本节介绍涉及多个表的数据查询。

11.2.1多表连接查询的分类

在上面介绍的学生成绩查询中，我们每次显示的都是学生的编号信息，因为该表中只存储了学生的编号。

实际上最好显示学生的姓名，而姓名却存储在学生信息表中，像这种需要从多个表中选择或者比较数据项的情况，就需要使用多表连接查询。

多表连接查询实际上是通过各个表之间共同列的关联性来查询数据的，它是关系数据库查询最主要的特征。

以下是几种常用的连接查询方式：

内连接、外连接。

1.内连接查询

内连接查询是最典型、最常用的连接查询，它根据表中共同的列来进行匹配。

特别是两个表存在主外键关系时通常会使用内连接查询。

2.外连接查询

外连接查询是至少返回一个表中的所有记录，根据匹配条件有选择性地返回另一张表的记录。

外连接可以是左外连接、右外连接。

下面我们一一介绍这几种选择查询的具体含义和用法。

11.2.2内连接查询

内连接查询通常会使用“=”或“<>”等比较运算符来判断两列数据值是否相等，上面所说的根据学生号来判断学生姓名的连接就是一种内连接。

内连接使用INNERJOIN…ON关键字或WHERE子句来进行表之间的关联。

内连接查询可以通过两种方式实现。

1.在WHERE子句中指定连接条件

例如，查询学生姓名和成绩的SQL如下。

SELECTStudent.StudentName,Result.SubjectId,Result.StudentResult

FROMStudent,Result

WHEREStudent.StudentNo=Result.StudentNo

上面这种形式的查询，相当于FROM后面紧跟了两个表名，然后在字段列表中用“表名.列名”来区分别，再在WHERE条件子句中加以判断，要求学生编号信息相等。

2.在FROM子句中使用INNERJOIN…ON

上面的查询也可以通过以下的JOIN…ON子句来实现。

SELECTS.StudentName,C.SubjectId,C.StudentResult

FROMStudentASS

INNERJOINResultASCON（S.StudentNo=C.StudentNo）

在上面的内连接查询中：

ØINNERJOIN用来连接两个表。

ØINNER可以省略。

ØON用来设置条件。

ØAS指定标的“别名”。

如果查询的列名在用到的两个或多个表中不重复，则对这一列的引用不必用表名来限定。

查询结果如图11.16所示。

图11.16两表连接查询

再看以下SQL语句。

SELECTS.StudentName,C.SubjectId,C.StudentResult

FROMStudentASS

INNERJOINResultASCON（S.StudentNo=C.StudentNo）

WHEREC.StudentResult>=60ANDC.SubjectId=1

查询将返回科目编号为1的及格学生的姓名和分数。

WHERE子句用来限定查询条件。

查询结果如图11.17所示。

图11.17科目编号为1的及格学生信息

内连接查询通常不仅仅连接两个表，有时候还会涉及三个表或者更多表。

例如，除了学生信息表，学生成绩表之外，还存在课程表。

上面的查询不仅仅要显示学生姓名、分数，而且要通过课程编号来显示课程名称表中对应课程的名称，可以使用以下三表连接查询的SQL语句来实现。

SELECTS.StudentNameAS学生姓名，CS.SubjectNameAS课程名称，R.StudentResultAS考试成绩

FROMStudentASS

INNERJOINResultASRON（S.StudentNo=R.StudentNo）

INNERJOINSubjectASCSON（CS.SubjectId=R.SubjectId）

执行以上的SQL，查询结果如图11.18所示。

图11.18三表连接查询

注意以上的数据分别来自三个不同的数据表。

11.2.3技能训练

[上机练习3]

指导——两表内连接查询信息

训练要点

ØINNNERJOIN…ON的使用。

ØWHERE的使用。

需求说明

以下所有查询均使用INNERJOIN…ON和WHERE两种方式完成。

Ø查询学生姓名、所属年级名称及联系电话。

Ø查询年级编号为1的年级名称、科目名称及学时。

Ø查询参加科目编号为1的考试的学生姓名、分数、考试日期。

Ø查询学号为S1101007的学生参加的考试科目名称、分数、考试日期。

Ø查询参加考试的学生学号、所考科目名称、分数、考试日期。

难点分析

Ø需求2的年级名称可以从年级表Grade中获取，科目名称及学时从科目表Subject中获取，而科目表Subject中存在年级编号GradeId,通过GradeId连接这两张表即可获取所需信息，参考如下SQL语句。

SELECT……FROMSubjectASJ

INNERJOINGradeASGONJ.GradeId=G.GradeId

……

Ø需求2中还有一个限定条件：

年级编号为1，使用WHERE进行限定即可。

参考如下SQL语句。

SELECT……FROMSubjectASJ

INNERJOINGradeASGONJ.GradeId=G.GradeId

WHEREG.GradeId=1

[上机练习4]

练习——三表内连接查询信息

需求说明

以下所有查询均使用INNERJOIN…ON和WHERE两种方式完成。

Ø查询学生学号、姓名、考试科目名称及成绩。

Ø查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期。

Ø保存为“三表内连接查询信息.sql”文件。

11.2.4外连接查询

通过上面例子可以看出：

内连接查询的结果是从两个或两个以上表的组合中挑选出符合连接条件的数据，如果数据无法满足连接条件则将其忽略。

在内连接查询中，参与连接的表的地位是平等的。

与内连接查询相对的方式称为外连接查询。

在外连接查询中参与连接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中；对那些不符合连接条件的列，将被填上NULL值（空值）后再返回到结果集中。

1.左外连接查询

左外连接查询的结果集包括LEFTJOIN子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。

若左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列均为空值。

左外连接查询使用LEFTJOIN…ON或LEFTOUTERJOIN…ON关键字来进行表之间的关联。

例如，要统计所有学生的考试情况，要求显示所有参加考试学生的每次考试分数，没有参加考试的学生也要显示出来。

这时候，以学生信息表为主表（有时也叫左表）、学生成绩表为从表的左外连接查询如下SQL语句所示。

SELECTS.StudentName,R.SubjectId,R.StudentResult

FROMStudentASS

LEFTOUTERJOINResultASRONS.StudentNo=R.StudentNo

其中，从学生信息表中把每一条记录跟成绩表的记录进行数据匹配（匹配条件为S.StudentNo=R.StudentNo）。

若匹配成功，则返回到记录集（取S.StudentName,R.SubjectId,R.StudentResult的值）；若没有找到匹配的记录，则返回NULL值填充记录集。

有部分学生没有参加过任何科目的考试，所以成绩表中没有相关的考试记录，对应的科目编号和成绩以NULL（空值）填充，查询结果如图11.19所示。

图11.19左外连接查询

想一想：

如下SQL语句返回的结果是什么？

SELECTCS.SubjectName,R.StudentNo,R.StudentResult

FROMSubjectASCS

LEFTOUTERJOINResultASRONCS.SubjectId=R.SubjectId

2.右外连接查询

右外连接查询与左外连接查询类似，只不过要包含右表中所有匹配的行。

若右表中有的项在左表中没有对应的项，则以NULL值填充。

右外连接查询使用RIGHTJOIN…ON或RIGHTOUTERJOIN…ON关键字来进行表之间的关联。

例如在某数据库中，存在书籍表Titles和出版商表Publishers之间的右外连接将包括所有的出版商，在Titles表中没有书名的出版商（很久没出书了）也会被列出。

SELECTTitles.Title_id,Titles.Title,Publishers.Pub_name

FROMtitles

RIGHTOUTERJOINPublishersONTitles.Pub_id=Publishers.Pub_id

想一想

在数据库MySchool中，考虑到各表之间的关系，如下两条SQL语句返回的结果是否相同？

第一条语句如下。

SELECTCS.SubjectName,R.StudentNo,R.StudentResult

FROMSubjectASCS

RIGHTOUTERJOINResultASRONCS.SubjectId=R.SubjectId

第二条语句如下。

SELECTCS.SubjectName,R.StudentNo,R.StudentResult

FROMSubjectASCS

INNERJOINResultASRONCS.SubjectId=R.SubjectId

11.2.5技能训练

[上机练习5]

练习——使用外连接查询信息

需求说明

Ø查询所有科目的考试信息（某些科目可能还没有被考过），如图11.20所示。

图11.20所有科目考试情况

Ø查询从未考试的科目信息。

提示：

根据需求1的思路，使用外连接，在成绩表中没有的科目考试记录信息，即增加了WHERE条件，条件如下。

WHERE……ISNULLAND……ISNULL

Ø查询所有年级对应的学生信息（某些年级可能还没有学生就读），如图11.21所示。

图11.21所有年级对应的学生信息

Ø保存为“使用外连接查询信息.sql”文件。

想一想：

根据数据库MySchool各表之间的关系，以上查询需求使用左外连接还是右外连接，还是都可以实现？

如果都能够实现，请使用两种方法实现。

本章总结

Ø分组查询是针对表中不同的组分类统计的，GR