一、带有EXISTS谓词的子查询
EXISTS谓词:存在量词
带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
①、若内层查询结果非空,则外层的WHERE子句返回真值
②、若内层查询结果为空,则外层的WHERE子句返回假值
由EXISTS引出的子查询,其目标列表达式通常都用 * ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。
NOT EXISTS谓词:
①、若内层查询结果非空,则外层的WHERE子句返回假值
②、若内层查询结果为空,则外层的WHERE子句返回真值
(同EXISTS谓词相反)
[例 3.60]查询所有选修了1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ' 1 ');
这段代码的意思是,在SC表中是否存在选修了课程号为1的学生学号,如果存在,则在Student表中找到与该学号对应的学生姓名。
[例 3.61] 查询没有选修1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
如果存在选修了1 号课的学生学号,因为是NOT EXISTS,所以返回假值;如果没有选修1 号课,返回真值,同时显示出与没有选修1号课的学生学号对应的学生姓名。
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换,就是说,EXSTS 更高级一些。
[例 3.62] 查询选修了全部课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno= Student.Sno
AND Cno= Course.Cno
)
);
这段代码一层一层来分析,先看第一个学生是否选修了全部的课程,假设全部课程从1到5,如果选修了1 号课,最内层返回F,没有选修返回T,以此类推,如果这5 轮中都返回F,那么说明该学生选修了全部课程,然后是第二名学生…分析方法相同。
用EXISTS/NOT EXISTS实现逻辑蕴涵(难点)
[例 3.63]查询至少选修了学生201215122选修的全部课程的学生号码。
(该题目的意思是学生201215122选的课,某学生都选了,而且还可能选了别的课,也就是说,不存在这样的课程y,学生201215122选修了y,而学生x没有选。)
用NOT EXISTS谓词表示:
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno = ' 201215122 ' AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno));
在SCX表中选中第一个学号假定为201215011,在SCY中选择201215122选修的地门课的学号,最内层的意思是,如果201215122选的课程201215011也选了,那么返回F,反之返回T,以此类推,将所得结果取并集如果均为F,那么201215011符合条件,如果有一个T,则不符合条件。
二、集合查询
集合操作的种类
并-UNION
交-INTERSECT
差-EXCEPT
参加集合操作的各查询结果的列数必须相同
对应项的数据类型必须相同
[例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
以上二者是或的关系,满足其中之一即可。
UNION:将多个查询结果合并起来时,系统自动去掉重复元组
UNION ALL:将多个查询结果合并起来时,保留重复元组
[例 3.65] 查询选修了课程1或者选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno=' 1 '
UNION
SELECT Sno
FROM SC
WHERE Cno= ' 2 ';
[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生 的交集。
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19
[例 3.67]查询既选修了课程1又选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno=' 1 '
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2 ';
[例 3.68] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage <=19;
三、基于派生表的查询
子查询不仅可以出现在WHERE子句中,
还可以出现在FROM子句中,
这时子查询生成的临时派生表成为主查询的查询对象。
[例3.57]找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade)
FROM SC
GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno
and SC.Grade >=Avg_sc.avg_grade
将每个学生的平均成绩放在一个新形成的临时表中,重新命名,然后再把SC表中的每个学生的成绩和平均成绩进行比较,如果比平均成绩大,符合条件显示出来该同学的学号以及该门课的课程号。
如果子查询中没有聚集函数,派生表可以不指定属性列,子查询SELECT子句后面的列名为其缺省属性。
[例]查询所有选修了1号课程的学生姓名,可以用如下查询完成:
SELECT Sname
FROM Student,
(SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1
WHERE Student.Sno=SC1.Sno;
通过对嵌套查询(带有EXISTS)、 集合查询、派生表的查询等操作,集合查询比较简单,带有EXISTS的查询比较难理解、可以先从整体理解,再从细节理解,派生表相当于一个中介,相对容易理解,总的来说,还需要多多练习。