嵌套查询
接着上一篇说
带有EXISTS谓词的查询
EXISTS:存在
带有EXISTS 谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
如果返回true,主查询会执行,返回false,主查询就不再执行。
因为带有EXISTS谓词的子查询不返回数据,指定列名没有多大意义,所以一般目标列表达式都用*代替
一旦找到第一个匹配的记录后(true),就马上停止查找。
先来个例子
查询所有选修了1号课程的学生的姓名
SELECT
Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1')
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200321121614822.png)
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= '1')
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200321142730596.png)
带有IN谓词,比较运算符,ANY|ALL谓词的子查询可以用带EXISTS谓词的子查询替换
例如:仍查询与 刘晨 在同系的学生的信息
SELECT
Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname = N'刘晨')
SELECT
Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname=N'刘晨')
SQL中没有全称量词,可以利用离散中的逻辑转化,将全称量词转换为存在量词
(
∀
x
)
P
≡
¬
(
∃
x
(
¬
P
)
)
(\forall x)P \equiv \neg (\exists x(\neg P))
(∀x)P≡¬(∃x(¬P))
来个例子:
1. 查询选修了全部课程的学生,可以转化一下,选修了全部课程的学生=没有课程是该学生没有选的
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE SC.Cno = Course.Cno
AND Student.Sno=SC.Sno)
)
SELECT Student.Sname,Student.Sno
FROM Student
WHERE Student.Sno=(
SELECT
SC.Sno
FROM SC
INNER JOIN Course ON SC.Cno=Course.Cno
GROUP BY Sno
HAVING COUNT(*)=(SELECT COUNT(*) FROM Course)
)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200321160630910.png)
能用EXISTS的时候,还是建议使用EXISTS,EXISTS的执行效率还是挺高的,并且可以对空值进行判断。
先将三张表的数据列出来
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200321162044899.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoZW5nX1ha,size_16,color_FFFFFF,t_70)
带有EXISTS关键词的执行过程
进入第一层:取Student表中的一个Sno值‘201215121’
进入第二层:取Course表中的一个Cno值‘1’
进入第三层:判断SC表中是否存在Sno=‘201215121’ 并且Cno=‘1’这样的元组,经过NOT EXISTS的取反,存在F,不存在就是T—— 结果F
返回第二层:取第二个Cno值‘2’
进入第三层:判断是否存在Sno=‘201215121’ 并且Cno=‘2’这样的元组,结果取反 —— 结果F
…
返回第二层:取最后一个Cno值‘7’
进入第三层:判断是否存在 Sno=‘201215121’ 并且Cno=‘7’这样的元组,结果取反 —— 结果F
得到第二层的结果是:F∨F∨F∨F∨F…∨F = F
返回第一层的结果:T(NOT EXISTS再次取反)
结果为T,所以将Sno='201215121’学生的学生姓名取出放入结果表中。
进入第一层:取第二个Sno值‘201215122’
再次重复以上的操作。
2. 使用EXISTS关键词实现逻辑蕴含
p
→
q
≡
¬
p
∨
q
p \rightarrow q \equiv \neg p \vee q
p→q≡¬p∨q
当年离散差点挂了,就不在这解释这是什么意思了。
例子:
查询至少选修了学号201215122选修的全部课程的学生学号
也就是查询学修的课程里包含学生201215122选修的课的学生学号
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 Sno = SCX.Sno
AND Cno = SCY.Cno)
)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200321165936780.png)
执行过程
在这里的SCX,SCY,SCZ都是SC表。
1.从SCX中取第一个Sno 201215121
2.进入第二层从SCY中,取SCY中第一个Sno,WHERE后 SCY.Sno=‘201215122’这个条件直接干掉SCY中一堆Sno取值为201215121的情况,全为F,当Sno为201215122时
3.进入第三层,从SCZ中取第一个元组的Sno,Cno,并根据WHERE后的条件进行判断,存在满足条件Sno = SCX.Sn AND Cno = SCY.Cno的,为T,返回再经NOT EXISTS作用,最终返回F
4.最终第二层的结果为F,返回经NOT EXISTS作用,最终返回T,SCX的第一个元组的SNO放入结果集中。
5.在从SCX中取第二个元组的Sno,再重复上述2、3、4执行过程
最后获得最终结果
集合查询
并—UNION|UNION ALL
交—INTERSECT
差—EXCEPT
参与集合操作的表的列数,数据类型必须相同
例题:
查询CS系中学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 19
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200321163633299.png)
UNION和UNION ALL都是讲查询结果合并起来,UNION会去掉重复的元组,而UNION ALL会保留重复的元组
查询CS系与年龄不大于19岁的学生的交集
SELECT *
FROM Student
WHERE Sdept = 'CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage <= 19
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200321163927724.png)
查询CS系学生与年龄不大于19岁学生的差集
SELECT *
FROM Student
WHERE Sdept = 'CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage <= 19
![在这里插入图片描述](https://img-blog.csdnimg.cn/2020032116405089.png)
基于派生表的查询
将一个查询结果集作为另一个查询源,及出现在FROM子句中,必须要为派生表起个别名
查询每个学生超出他选修课平均成绩的课程号
SELECT SC.Sno,Cno
FROM SC,(SELECT Sno,AVG(Grade) as avg_grade
FROM SC
GROUP BY Sno)
AS Avg_sc
WHERE SC.Sno = Avg_sc.Sno AND SC.Grade >= avg_grade
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200321165012184.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoZW5nX1ha,size_16,color_FFFFFF,t_70)
SELECT语句的一般形式
SELECT [ALL|DISTINCT]
<目标列表达式> [别名][,<目标列表达式> [别名]]...
FROM <表名或视图名> [别名]
[,<表名或视图名> [别名]]...
|(<SELECT语句>)[AS <别名>]
[WHERE<条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)