持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第7天，点击查看活动详情
数据库原理-数据查询 嵌套查询【二】
带有ANY(SOME)或ALL谓词的子查询
在谓词逻辑中，还有存在量词和全称量词的概念，在SQL中并没有对应的表达，统一采用“谓词”来表达
方法一：引入ANY和ALL谓词，其对象为某个查询结果，表示其中任意一个值或者全部值
方法二：引入EXIST谓词，其对象也是某个查询结果，但表示这个查询结果是否为空，返回真值
使用ANY或ALL谓词时必须同时使用比较运算
语义为：
| >ANY | 大于子查询结果中的某个值 |
|---|---|
| >ALL | 大于子查询结果中的所有值 |
| <ANY | 小于子查询结果中的某个值 |
| <ALL | 小于子查询结果中的所有值 |
| >=ANY | 大于等于子查询结果中的某个值 |
| >=ALL | 大于等于子查询结果中的所有值 |
| <=ANY | 小于等于子查询结果中的某个值 |
| <=ALL | 小于等于子查询结果中的所有值 |
| =ANY | 等于子查询结果中的某个值 |
| =ALL | 等于子查询结果中的所有值 |
| !=(或<>)ANY | 不等于子查询结果中的某个值 |
| !=(或<>)ALL | 不等于子查询结果中的任何一个值 |
查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept='CS')
AND Sdept <> 'CS';
执行过程：
- 首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20，19）
- 处理父查询，找所有不是CS系且年龄小于20或19的学生
用聚集函数实现
SELECT Sname,Sage
FROM Student
WHERE Sage < (SELECT MAX(Sage)
FROM Student
WHERE Sdept='CS')
AND Sdept <> 'CS';
查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄
方法一：用ALL谓词
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept = 'CS')
AND Sdept <> 'CS';
方法二：用聚集函数
SELECT Sname,Sage
FROM Student
WHERE Sage < (SELECT MIN(Sage)
FROM Student
WHERE Sdept = 'CS')
AND Sdept <> 'CS';
ANY(或SOME)，ALL谓词与聚集函数、IN谓词的等价转换关系
| = | <>或!= | < | <= | >= | ||
|---|---|---|---|---|---|---|
| ANY | IN | -- | <MAX | <=MAX | >MIN | >=MIN |
| ALL | -- | NOT IN | <MIN | <=MIN | >MAX | >=MAX |
EXISTS谓词
-
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”
- 若内层查询结果非空，则外层的WHERE子句返回真值
- 若内层查询结果为空，则外层的WHERE子句返回假值
-
由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
查询所有选修了1号课程的学生姓名
思路分析：
- 本查询涉及Student和SC关系
- 在Student中依次取每个元组的Sno值，用此值去检查SC表
- 若SC中存在这样的元组，其Sno值等于此Student,Sno值，并且其Cno='1',则取此Student.Sname送入结果表
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
查询没有选修1号课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
用EXISTS代替其他谓词
- 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
用EXISTS/NOT EXISTS实现全称量词(难点)
用EXISTS实现逻辑蕴含
查询与“刘晨”在同一个系学习的学生
可以用带EXISTS谓词的相关子查询替换：
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept AND
S2.Sname = '刘晨')；
查询选修了全部课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Con=Course.Cno
)
);
查询至少选修了学生201215122选修的全部课程的学生号码
SELECT DISTINCT SCX.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));
\
