6.4 SELECT高级查询
6.4 SELECT高级查询
本节将以school数据库（其关系如图6-37所示）为例，来介绍SELECT语句的高级查询，包括数据汇总、连接查询、子查询等。该school数据库中包含student、sc、course、tc和teacher等5张数据表。其中student、course、teacher分别记录了学生、课程、老师的信息，而sc中则记录了学生所选修的课程以及该课程的考试成绩，tc中则记录了教师所授的课程、课程的时间和地点、教学评估的得分。
图6-37 school关系图
6.4.1 数据汇总
1．聚合函数
数据库最大的一个特点就是将各种分散的数据按一定规律、条件进行分类汇总，并得出统计结果。Transact-SQL提供了聚合函数来完成这项工作，聚合函数对一组数据进行操作，并返回一个数值，常用的聚合函数如下。
· AVG(expr)：列值的平均值。该列只能包含数字数据。
· COUNT(expr)，COUNT(*)：列值的计数（如果将列名指定为expr）或是表或组中所有行的计数（如果指定*）。COUNT(expr) 忽略空值，但COUNT(*) 在计数中包含空值。
· MAX(expr)：列中最大的值（文本数据类型中按字母顺序排在最后的值）。忽略空值。
· MIN(expr)：列中最小的值（文本数据类型中按字母顺序排在最前的值）。忽略空值。
· SUM(expr)：列值的合计。该列只能包含数字数据。
聚合函数只用于SELECT语句的选择列表（如SELECT COUNT(*)）、COMPUTE和COMPUTE BY子句、HAVING子句。例如，若要统计课程号为1的课程的平均分，则可执行如下语句。
SELECT AVG(score)
FROM sc
WHERE cno=1
再如，若要查看课程号为2的课程的最高分，则可执行如下语句：
SELECT MAX(score)
FROM sc
WHERE cno=2
再如，若要查看课程号为3的课程及格的人数，则可执行如下SQL语句：
SELECT COUNT(*)
FROM sc
WHERE cno=3 AND score>=60
2．GROUP BY子句
如果要分类统计，一般需要GROUP BY子句，其基本格式如下：
GROUP BY <分组表达式>
其中分组表达式是分组的依据，表达式值相同的记录归为同一组，从而进行统计。例如，要统计各门课程的平均分，则可按课程进行分组，然后各课程分别进行统计，显示出课程序号和平均分，可以执行如下语句：
SELECT cno, AVG(score)
FROM sc
GROUP BY cno
3．HAVING子句
HAVING子句指定组或聚合的搜索条件，只能与SELECT语句一起使用，通常与GROUP BY连用。例如，要查询平均分低于60分的课程。
SELECT cno, AVG(score)
FROM sc
GROUP BY cno
HAVING AVG(score)<60
读者也许会发现，HAVING子句与WHERE子句功能类似，都可进行筛选，但应当注意两者的区别，HAVING只应用于作为一个整体的组，而WHERE子句应用于个别的行。而且，在聚合的情况下，WHERE用于聚合前的筛选，即仅聚合符合WHERE条件的行，从而减少聚合统计的时间，而HAVING用于聚合后的筛选，即仅显示符合HAVING条件的结果。例如，要查询课程序号小于100的课程中平均分不足60分的课程。
SELECT cno, AVG(score)
FROM sc
WHERE cno<100
GROUP BY cno
HAVING AVG(score)<60
上段语句中，先筛选出课程序号小于100的课程，然后再对这些课程进行聚合求平均分，最后，仅显示平均分低于60的课程。
4．COMPUTE BY子句
COMPUTE BY子句的作用是进行统计计算，它将生成统计作为附加的汇总列出现在结果集的最后，其基本格式如下：
COMPUTE [ <聚合函数名> ](<表达式>) [BY <分组表达式>]
当省略BY子句时，将计算所有记录的统计值。若要分组统计，则应带COMPUTE BY子句，必须和ORDER BY子句配合使用，而且COMPUTE BY中的分组表达式必须在ORDER BY中出现。例如，要计算各同学的平均分。
SELECT sno,score
FROM sc
ORDER BY sno
COMPUTE AVG(score) BY sno
6.4.2 连接查询
连接查询就是先将两个表或多个表连接成结果集，再执行查询，结果集中的记录来源于各表。若两个表相连接，这两个表的任意两条记录都可能连接成结果集中一条记录，而这两条记录是否属于结果集，取决于这两条记录是否满足连接条件。连接的基本语法如下：
<左表名> [<连接类型>] JOIN <右表名> ON <连接条件>
其中，连接类型有如下5种。
· INNER：内连接。这是默认类型，当左表中某记录根据连接条件在右表中没有匹配记录时，该记录被忽略。
· LEFT：左连接。当左表中某记录根据连接条件在右表中没有匹配记录时，该记录不被忽略，仍在结果集中产生一条记录。
· RIGHT：右连接。与左连接相似，当右表中某记录根据连接条件在左表中没有匹配记录时，该记录不被忽略，仍在结果集中产生一条记录。
· FULL：完全连接。当左表数据与右表不匹配，右表数据与左表也不相匹配时，两种数据都不丢失，都仍会在结果集中产生一条记录。
· CROSS：交叉连接。左表任意一条记录都与右表中任意一条记录连接，并在结果中产生记录。这类连接的结果集中的记录数为左表记录数与右表记录数的乘积。
如果左表中有100条记录，右表中有80条记录，根据连接条件，两表有60条记录相匹配，那么，如果两表是内连接，则结果集中仅有60条记录；如果是左连接，则有100条记录；如果是右连接，则有80条记录；如果是完全连接，则有（100+80-60=120）条记录；如果是交叉连接，则有（100×80=8000）条记录。
在有些表记录中包含空值时，而且又要查询所有信息时，连接查询就很有效。例如，要查看所有的学生和他们的数学成绩。
SELECT sname,score
FROM student LEFT JOIN sc ON student.sno=sc.sno JOIN course ON course
.cno=sc.cno
WHERE cname='数学'
6.4.3 子查询
子查询询是一个SELECT查询，它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其他子查询中，任何允许使用表达式的地方都可以使用子查询。
子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。
许多包含子查询的Transact-SQL语句都可以改为用连接表示。而其他一些问题只能由子查询提出。在Transact-SQL中，包括子查询的语句和不包括子查询但语义上等效的语句在性能方面通常没有区别。但是，在一些必须检查存在性的情况中，使用连接会产生更好的性能。否则，为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。所以在这些情况下，连接方式会产生更好的效果。
下面查询一个名叫“王五”的学生的成绩表，首先在子查询中查找到该学生的学号，再在外部查询中使用该学号查找各科成绩。
SELECT cno, score
FROM sc
WHERE sno=(
SELECT sno
FROM student
WHERE sname= '王五'
)
若子查询的结果不唯一，则可以使用“IN”运算符来取代等号。例如要查询不及格（成绩低于60分）的女生名单，可以执行以下语句：
SELECT sname
FROM student
WHERE sno IN(
SELECT sno
FROM sc
WHERE score<60
) AND ssex='女'
若要检测子查询是否返回结果，可以使用EXISTS运算符，其语法格式如下：
EXISTS <子查询>
如果子查询中返回了记录，则该表达式值为TRUE，反之则为FALSE。还可以使用NOT EXISTS，其返回值刚好与EXISTS相反。
例如，上述查询不及格的女生名单，也可以使用EXISTS运算符来实现，用户可以比较IN与EXISTS的不同：
SELECT sname
FROM student
WHERE EXISTS(
SELECT *
FROM sc
WHERE score<60 AND student.ssex='女' AND sno=student.sno
)
如果子查询返回的是单个列的记录，则可以使用ALL、ANY这两个运算符与“>”“<”“>=”等比较运算符结合使用，从而筛选出符合条件的记录。ALL或ANY运算符的语法格式如下：
<表达式> <比较运算符> {ANY | ALL} <子查询>
当“表达式”与“子查询”中每一个记录比较都为True时，则“<表达式> <比较运算符> ALL <子查询>”的值才为True，而当“表达式”与“子查询”中任何一条记录比较为True时，“<表达式> <比较运算符> ANY <子查询>”的值即为True。如果以“>”比较运算符为例，“> ALL”则表示大于子查询中的每一个值，即大于其中的最大值，“> ANY”则表示至少大于其中某一个值，即大于其中的最小值。
若要使用ANY或ALL运算符来查询不及格的女生名单，则可以执行如下语句：
SELECT sname
FROM student
WHERE sno = ANY(
SELECT sno
FROM sc
WHERE score<60
) AND ssex='女'
事实上，“= ANY”与“IN”是等价的，而“<> ALL”与“NOT IN”是等价的。