查询结果
[例25] 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT *
FROM Student
ORDER BY Sdept，Sage DESC； 使用聚集函数
五类主要聚集函数
计数
COUNT（[DISTINCT|ALL] *） COUNT（[DISTINCT|ALL] <列名>） 计算总和
SUM（[DISTINCT|ALL] <列名>）
计算平均值
AVG（[DISTINCT|ALL] <列名>） 求最大值
MAX（[DISTINCT|ALL] <列名>）
求最小值
MIN（[DISTINCT|ALL] <列名>）
DISTINCT短语：在计算时要取消指定列中的重复值 ALL短语：不取消重复值 ALL为缺省值
[例26] 查询学生总人数。 SELECT COUNT(DISTINCT *) FROM Student；
[例27] 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC；
[例28] 计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC
WHERE Cno= ' 1 '；
[例29] 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC
WHERE Cno= ' 1 '； 对查询结果分组
使用GROUP BY子句分组 细化聚集函数的作用对象 未对查询结果分组：聚集函数将作用于整个查询结果对查询结果分组后：聚集函数将分别作用于每个组
使用GROUP BY子句分组
[例30] 求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC
GROUP BY Cno；结果
Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48
使用HAVING短语筛选最终输出结果
[例31] 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC
GROUP BY Sno
HAVING COUNT(*) >3；
[例32] 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。 SELECT Sno, COUNT(Cno)
FROM SC WHERE Grade>=90 GROUP BY Sno
HAVING COUNT(*)>=3;
只有满足HAVING短语指定条件的组才输出。HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。连接查询
同时涉及多个表的查询称为连接查询 用来连接两个表的条件称为连接条件或连接谓词
一般格式：[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>
比较运算符：=、>、<、>=、<=、!=
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
连接字段：连接谓词中的列名称为连接字段，连接条件中的各连接字段类型必须是可比的
SQL中连接查询的主要类型：不带连接谓词，等值连接(含自然连接)，非等值连接查询，自身连接查询，外连接查询，复合条件连接查询
不带连接谓词：不带连接谓词的连接，很少使用 例： SELECT Student.* , SC.* FROM Student, SC 等值与非等值连接查询
等值连接、自然连接、非等值连接
[例32] 查询每个学生及其选修课程的情况。 SELECT Student.*, SC.*
FROM Student,SC
WHERE Student.Sno = SC.Sno; 等值连接
连接运算符为 = 的连接操作
[<表名1>.]<列名1> = [<表名2>.]<列名2> 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。 连接操作的执行过程 嵌套循环法(NESTED-LOOP) 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕
等值与非等值连接查询
[例32] 查询每个学生及其选修课程的情况。 SELECT Student.*, SC.* FROM Student,SC
WHERE Student.Sno = SC.Sno;
等值连接 假设Student表、SC表分别有下列数据：
自然连接：等值连接的一种特殊情况，把目标列中重复的属性列去掉。
[例33] 对[例32]用自然连接完成。 SELECT Student.Sno，Sname，Ssex， Sage，Sdept，Cno，Grade FROM Student，SC
WHERE Student.Sno = SC.Sno； 非等值连接查询
连接运算符 不是 = 的连接操作
[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>
比较运算符：>、<、>=、<=、!= 此外连接谓词还可以是以下形式：
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
自身连接：一个表与其自身进行连接，称为表的自身连接；需要给表起别名以示区别；由于所有属性名都是同名属性，因此必须使用别名前缀
[例34] 查询每一门课的间接先修课（即先修课的先修课）
[例34] 查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cno，SECOND.Cpno
FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；
外连接——外连接与普通连接的区别：普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
[例 33] 查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作
SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade
FROM Student LEFT OUT JOIN SC ON （ Student.Sno = SC.Sno ）；
非主体表有一“万能”的虚行，该行全部由空值组成；虚行可以和主体表中所有不满足连接条件的元组进行连接；由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值部分都是空值 左外连接:左外连接列出左边关系中的所有元组 右外连接:右外连接列出右边关系中的所有元组
复合条件连接:
WHERE子句中含多个连接条件时，称
为复合条件连接
[例35]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。
SELECT Student.Sno, student.Sname FROM Student, SC
WHERE Student.Sno = SC.Sno AND /* 连接谓词*/
SC.Cno= ' 2 ' AND /* 其他限定条件 */ SC.Grade > 90； /* 其他限定条件 */ 多表连接
[例36] 查询每个学生的学号、姓名、选修的课程名及成绩。
SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno；
嵌套查询
嵌套查询概述:一个SELECT-FROM-WHERE语句称为一个查询块;将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 SELECT Sname
外层查询/父查询
FROM Student WHERE Sno IN
(SELECT Sno 内层查询/子查询 FROM SC
WHERE Cno= ' 2 '）；
子查询的限制:不能使用ORDER BY子句，该子句只能对最终查询结果排序。层层嵌套方式反映了SQL语言的结构化。可以由简单查询构造复杂查询，增强SQL查询能力。嵌套查询分类:不相关子查询——子查询的
查询条件不依赖于父查询；相关子查询——子查询的查询条件依赖于父查询 嵌套查询求解方法
不相关子查询：由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
相关子查询：首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止
引出子查询的谓词：带有IN谓词的子查询；带有比较运算符的子查询；带有ANY或ALL谓词的子查询；带有EXISTS谓词的子查询
带有IN谓词的子查询
例37] 查询与?刘晨?在同一个系学习的学生。 此查询要求可以分步来完成
① 确定?刘晨?所在系名
SELECT Sdept
FROM Student WHERE Sname= ‘ 刘晨 ’； 结果为： Sdept IS
② 查找所有在IS系学习的学生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= ' IS '； 结果为：Sno Sname Sdept
95001 刘晨 IS 95004 张立 IS 构造嵌套查询
将第一步查询嵌入到第二步查询的条件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student
WHERE Sname= ‘ 刘晨 ’)； 此查询为不相关子查询。即子查询的结果用来建立父查询的查询条件。求解方法是由里向外。DBMS求解该查询时也是分步去做的。
用自身连接完成本查询要求？
SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = ‘刘晨’； 父查询和子查询中的表均可以定义别名 SELECT Sno，Sname，Sdept FROM Student S1
WHERE S1.Sdept IN SELECT Sdept
FROM Student S2
WHERE S2.Sname= ‘ 刘晨 ’)； 三层嵌套查询例子
[例38]查询选修了课程名为?信息系统?的学生学号和姓名。
SELECT Sno，Sname ③ 最后在Student关系中 FROM Student 取出Sno和Sname
WHERE Sno IN
(SELECT Sno ② 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号 WHERE Cno IN
(SELECT Cno ① 首先在Course关系中找出?信
FROM Course 息系统?的课程号，结果为3号
WHERE Cname= ‘信息系统’ ) ); 用连接查询改造 用连接查询
SELECT Sno，Sname
FROM Student，SC，Course
WHERE Student.Sno = SC.Sno AND
SC.Cno = Course.Cno AND Course.Cname=‘信息系统’；
带有比较运算符的子查询：当能确切知道内层查询返回单值时，可不用关键字IN，而用比较运算符（>，<，=，>=，<=，!=或< >）。
[例37] 查询与?刘晨?在同一个系学习的学生。 例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例37]可以 用 = 代替IN ：
SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student
WHERE Sname= ‘ 刘晨 ’)； 子查询一定要跟在比较符之后 错误的例子：
SELECT Sno，Sname，Sdept FROM Student
WHERE ( SELECT Sdept
FROM Student
WHERE Sname= ‘ 刘晨 ’) = Sdept；
带有ANY或ALL谓词的子查询
谓词语义ANY：任意一个值 ALL：所有值 需要配合使用比较运算符 > ANY 大于子查询结果中的某个值 > ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值 < ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值 >= ALL
大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值 <= ALL 小于等于子查询结果中的所有值 = ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际
意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值 [例39] 查询其他系中比信息系某一学生年龄小的学生姓名和年龄。
SELECT Sname，Sage FROM Student
WHERE Sage < ANY (SELECT Sage FROM Student
WHERE Sdept= ' IS ') AND Sdept <> ' IS ' ;
/* 父查询块中的条件 */ 结果 Sname Sage 王敏 18
执行过程：1. DBMS执行此查询时，首先处理子查询，找出IS系中所有学生的年龄，构成一个集合(19，18) 2. 处理父查询，找所有不是IS系且年龄小于 19 或18的学生
[例39']：用聚集函数实现[例39] SELECT Sname，Sage FROM Student WHERE Sage <
(SELECT MAX(Sage) FROM Student
WHERE Sdept= ' IS ') AND Sdept <> ' IS ’;
[例40] 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。 方法一：用ALL谓词 SELECT Sname，Sage FROM Student WHERE Sage < ALL
(SELECT Sage FROM Student
WHERE Sdept= ' IS ') AND Sdept <> ' IS ’; 方法二：用聚集函数 SELECT Sname，Sage FROM Student
WHERE Sage <
(SELECT MIN(Sage) FROM Student
WHERE Sdept= ' IS ')
AND Sdept <>' IS ’;
ANY和ALL谓词有时可以用聚集函数实现ANY与ALL与聚集函数的对应关系
用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数 带有EXISTS谓词的子查询
1. EXISTS谓词——带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值；若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 2. NOT EXISTS谓词
[例41] 查询所有选修了1号课程的学生姓名 SELECT Sname FROM Student
WHERE EXISTS (SELECT *
FROM SC /*相关子查询*/ WHERE Sno=Student.Sno AND
Cno= ‘1’)；
思路分析：本查询涉及Student 和 SC关系。在Student中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其 Cno= '1'，则取此Student.Sname送入结果关系。 改造：用连接运算进行查询 SELECT Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND SC.Cno= '1';
[例42] 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student
WHERE NOT EXISTS (SELECT * FROM SC
WHERE Sno = Student.Sno
AND Cno='1')；
3. 不同形式的查询间的替换
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换；所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
[例37]查询与?刘晨?在同一个系学习的学生。可以用带EXISTS谓词的子查询替换： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS （SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept AND S2.Sname = ‘刘晨’）； 集合查询
标准SQL直接支持的集合操作种类：并操作(UNION) 一般商用数据库支持的集合操作种类；并操作(UNION)；交操作(INTERSECT)；差操作(EXCEPT) 并操作
形式<查询块> UNION <查询块>
参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同
[例45] 查询计算机科学系的学生及年龄不大于19岁的学生。
[例46] 查询选修了课程1或者选修了课程2的学生。
[例47] 设数据库中有一教师表Teacher(Tno, Tname,...)。查询学校中所有师生的姓名。 SELECT Sname FROM Student