第5章 数据库高级查询

26
示例
UNION操作一般用在要从不同的表中查询语义相 同的列，并合并查询结果的情况。 设有作者表（authors）和出版商表（ publishers），其中都有城市（city）列。 查询作者和出版商所在的全部城市。 SELECT city as 城市 FROM authors UNION SELECT city FROM publishers
数据库技术实践 （2008版）
何玉洁
第5章 高级查询
5.2 5.3 5.3 5.4 相关子查询 其他形式子查询 查询结果的并、交、差运算 其他一些查询功能
2/76
5.2 相关子查询
在条件子句中的相关子查询 在SELECT列表中的相关子查询 EXISTS形式的子查询
3/76
基本概念
一些说明
所有的SELECT语句列表中列的个数必须相 同，而且对应列的语义应该相同。 各SELECT语句中每个列的数据类型必须兼 容。 合并后的结果采用第一个SELECT语句的列 标题。 如果要对查询的结果进行排序，则ORDER BY子句写在最后一个查询语句之后。
24
示例
例1 将对计算机系学生的查询结果与信息 管理系学生的查询结果合并为一个结果集。 SELECT Sno,Sname,Sage,Sdept FROM Student WHERE Sdept = '计算机系系' UNION SELECT Sno,Sname,Sage,Sdept FROM Student WHERE Sdept = '信息管理系'
19/76
5.3 查询结果的并、交、差运算
SELECT语句的查询结果是元组的集合，所 以多个SELECT语句的结果可进行集合操作。 集合操作主要（交） MINUS（差）
20
5.3.1 并运算
并运算可将两个或多个查询语句的结果 集合并为一个结果集，这个运算可以使 用 UNION 运算符实现。 UNION是一个特殊的运算符，通过它可 以实现让两个或更多的查询产生单一的 结果集。
21
并操作示例
22
UNION语法格式
SELECT语句1 UNION [ ALL ] SELECT语句2 UNION [ ALL ] … … SELECT语句n
ALL表示在结果集中包含所有查询语句产生的全部 记录，包括重复的记录。如果没有指定ALL，则系 统默认是删除合并后结果集中的重复记录。
23
25
示例
例2 查询要求同例1，但将查询结果按年龄从小到 大的顺序进行排序，并将结果列名按中文显示
SELECT Sno 学号,Sname 姓名,Sage 年龄,Sdept 所在系
FROM Student WHERE Sdept = '计算机系' UNION SELECT Sno, Sname, Sage, Sdept FROM Student WHERE Sdept = '信息管理系' ORDER BY Sage ASC
18/76
示例
查询至少选了C001和C002两门课程的学生的姓名 、所在系、所选的课程号和课程名。 SELECT Sname,Dept,o,Cname FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON o = o WHERE S.Sno IN ( SELECT T1.Sno FROM (SELECT * FROM SC WHERE Cno = 'C001') AS T1 JOIN (SELECT * FROM SC WHERE Cno = 'c002') T2 ON T1.Sno=T2.Sno)
27
5.3.2 交运算
返回同时在两个集合中出现的记录。 其语法格式为： SELECT语句1 INTERSECT SELECT语句2 INTERSECT … … SELECT语句n
28
示例
例3 查询同时出现在t1表和t2表中的记 录。
SELECT * from t1 INTERSECT SELECT * from t2
8/76
HAVING 子句中的相关子查询
例21 查询有最高学分超过本学期平均学 分1.5倍的学期。 SELECT Semester FROM Course c1 GROUP BY Semester HAVING MAX(Credit) >= ALL ( SELECT 1.5 * AVG(Credit) FROM Course c2 WHERE c1.Semester = c2.Semester )
SELECT Sname, Dept FROM Student WHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'C001' )
14/76
其他形式子查询：替代表达式的子查询
指在SELECT的选择列表中，嵌入一个只返 回一个标量值的SELECT语句，这个查询语 句通常都是一个聚合函数。 例30 查询选了C001课程的学生学号、考试 成绩以及该门课程的平均成绩。
16/76
示例
例31 查询选了C001课程的学生学号、考 试成绩、该门课程的平均成绩以及每个学 生与平均成绩的差。
SELECT Sno,Grade, (SELECT AVG(Grade) FROM SC WHERE Cno = 'C001') AS AvgGrade, Grade - (SELECT AVG(Grade) FROM SC WHERE Cno = 'C001') AS AvgGradeDiff
17/76
示例
例32 查询至少选了C001和C002两门课程 的学生学号。
SELECT T1.Sno FROM (SELECT * FROM SC WHERE Cno = 'C001') AS T1 JOIN (SELECT * FROM SC WHERE Cno = 'c002') AS T2 ON T1.Sno=T2.Sno
29
示例
例4 查询同时出现在t1、t2和t3表的记 录。
SELECT * from t1 INTERSECT SELECT * from t2 INTERSECT SELECT * from t3
30
示例
例5 查询李勇和刘晨所选的相同课程,列出课程名 和学分。（即同时被李勇和刘晨选的课程） SELECT Cname,Credit FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON o = o WHERE Sname = '李勇' INTERSECT SELECT Cname,Credit FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON o = o WHERE Sname = '刘晨'
SELECT Sno,Grade, (SELECT AVG(Grade) FROM SC WHERE Cno = 'C001') AS AvgGrade FROM SC WHERE Cno = 'C001'
15/76
其他形式子查询：派生表
有时也称为内联视图,是将子查询做为一 个表来处理，这个由子查询产生的新表 就称之为“派生表”，这很类似于临时 表。 可以在查询语句中用派生表来建立与其 它表的连接关系，在生成派生表后，在 查询语句中对派生表的操作与普通表一 样。
9/76
2 在SELECT列表中的相关子查询
子查询也可以用在SELECT语句的查询列 表中。 当所要查询的信息与查询中的其他信息 完全不同时，经常使用这种形式的子查 询。 比如，需要一个字段的聚合结果，但又 不希望这个结果影响其他的字段。
10/76
示例
例22 查询学生姓名、所在系和该学生选的 课程门数。
相关子查询与嵌套子查询的不同之处在于 信息传递是双方向的。 在相关子查询中，内层查询利用外层查询 提供的信息执行,然后将内层查询的结果 返回给外层查询，外层查询再利用这个结 果判断当前数据是否是满足要求的数据。
4/76
1 在条件子句中的相关子查询
相关子查询也可以写在WHERE子句中，或 者是HAVING子句中。它可以通过IN、比 较运算符和EXISTS关键词与外层查询关 联。
SELECT Sname,Dept, (SELECT COUNT(*) FROM SC WHERE Sno = Student.Sno ) AS CountCno FROM Student
11/76
示例
例23 查询课程名、开课学期及选该门课的学生 人数、平均成绩。不包括没人选的课程。 SELECT Cname 课程名,Semester 开课学期, ( SELECT COUNT(*) FROM SC WHERE Cno = o ) AS 选课人数, ( SELECT AVG(Grade) FROM SC WHERE Cno = o ) AS 平均成绩 FROM Course WHERE Cno IN ( SELECT Cno FROM SC )
6/76
示例
例19 查询每门课程考试成绩最高的两个学 生的学号以及相应的课程号和成绩。不包括 没考试的课程。 SELECT Sno,Cno,Grade FROM SC SC1 WHERE Sno IN ( SELECT TOP 2 WITH TIES Sno FROM SC SC2 WHERE o = o ORDER BY Grade desc ) AND Grade IS NOT NULL ORDER BY Cno ASC, Grade DESC
7/76