嵌套查询
先导概念
查询块:一个select语句为一个查询块
嵌套查询:将一个查询块嵌套在一个另一个查询块中where子句中的查询叫做嵌套查询。
嵌套查询的种类:
不相关子查询:子查询里的条件不依赖于父查询,从里到外依次查询,子查询的结果作为上一次查询的条件。
相关子查询:子查询里的条件依赖于父查询。父查询中列名就像是数组名,里面有很多元素,父查询的过程就像for循环遍历数组一样对每一个元素进行判断,如果符合条件就放入查询结果。而判断的条件就在where的子查询中。子查询一般用sno = s.sno 来确定遍历的索引,s,sno就像for循环中的i一样。
子查询的限制
不能使用ORDERBY语句
嵌套查询的谓词
in 比较运算符 any all exists
带有谓词in的子查询
eg:查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
( SELECT Sdept
FROM Student
WHERE Sname=' 刘晨 ' );
带有比较运算符的子查询
只能在内层查询返回单值的情况下使用(毕竟比较只能单个数之间比较)
eg:查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
( SELECT Sdept
FROM Student
WHERE Sname=' 刘晨 ' );
eg: 找出每个学生超过他选修课程平均成绩 的课程号
不难写出以下代码
SELECT Sno,Cno
FROM SC x
WHERE Grade >
那么怎么算出这位学生的平均成绩呢,直接用 avg(grade)肯定不行,因为其中包含了其他的学生的成绩。
所以这里就用到相关子查询了,在子查询中的where部分写下sno= x.sno,这样子查询的条件就会依赖于父查询。sno=x.sno就像一个遍历一个数组一样,算出每个学生(数组)的平均成绩 。
SELECT Sno,Cno
FROM SC x
WHERE Grade >
( SELECT AVG(Grade)
FROM SC y
WHERE y.Sno = x.SNO );
带有any或all谓词的子查询
一般与比较运算符搭配,扩展比较运算符的功能,不用局限于内层查询只能返回一个值。
<any : 小于子查询的结果中某一值
<all : 小于子查询的结果中所有值
带有EXISTS谓词的子查询
exists不返回任何实际数据,只返回逻辑真值,TRUE和FALSE
eg:查询所有没有选修1号课程的学生姓名
思路是查询student表中的学生,如果在课程表中这个学生选修了1号课程,条件就为false,结果中不会有这个学生。这个查询就很想if语句,如果成立就将其放入查询结果,如果不成立,就不放入查询结果。
SELECT Sname
FROM Student S
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S.Sno = Sno AND Cno=1);