本文共 2787 字，大约阅读时间需要 9 分钟。

数据查询

三、嵌套查询

一个SELECT-FROM-WHERE是一个查询块，将一个查询块嵌套在另一个查询块的WHERE或者HAVING短语的条件中的查询称为嵌套查询（nested query）

1、带有IN的谓词子查询

SELECT Sname FROM studentWHERE Sno IN(SELECT Sno FROM SC WHERE Cno='1');

子查询不能使用ORDER BY 子句，ORDER BY只能对最终的查询结果排序

SELECT * FROM Student;SELECT S1.*,S2.* FROM Student S1,Student S2 WHERE S1.Sdept = S2.Sdept;SELECT S1.*,S2.* FROM Student S1,Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname='李勇 ';

SELECT Sno,Sname,Cname FROM Student,Course WHERE Sno IN(SELECT Sno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname='信息系统'));SELECT Sno,Sname,Cname FROM Student,Course;--从多个表中查询相当于连接，如果不限制条件会出问题

SELECT Sno,Sname FROM Student WHERE IN(SELECT Sno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname='信息系统'));

上述查询可以用连接查询实现：

SELECT Student.Sno,Sname FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname='信息系统';

相关嵌套查询&&不相关嵌套查询

1. 子查询的查询条件不依赖于父查询的，称为不相关子查询（先执行子查询，子查询的结果用于建立其父查询的查找条件）
2. 子查询的查询条件依赖于父查询的，称为相关子查询，整个查询语句称为相关嵌套查询

2、带有比较运算符的谓词子查询

SELECT Sno,Cno FROM SC X WHERE Grade >= (SELECT AVG(Grade) FROM SC Y WHERE X.Cno=Y.Cno);

3、带有ANY/SOME/ALL的谓词子查询

查询比CS系中任意一个学生年龄小的学生姓名和年龄

SELECT Sname,Sage FROM StudentWHERE Sage < ANY(SELECT Sage FROM Student WHERE Sdept='CS')AND Sdept<>'CS'

用聚集函数实现

SELECT Sname,Sage FROM StudentWHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept='CS')AND Sdept<>'CS'

查询比CS系中所有学生年龄小的学生姓名和年龄

SELECT Sname,Sage FROM StudentWHERE Sage < ALL(SELECT Sage FROM Student WHERE Sdept='CS')AND Sdept<>'CS'

SELECT Sname,Sage FROM StudentWHERE Sage < (SELECT MIN(Sage) FROM Student WHERE Sdept='CS')AND Sdept<>'CS'

4、带有EXISTS的谓词子查询

1）EXISTS

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值‘true’或者‘false’

SELECT SnameFROM StudentWHERE EXISTS(SELECT * FROM SC WHERE SC.Sno=Student.Sno AND Cno='1');--EXISTS引出的子查询一般都用*，其目标列无意义

2)NOT EXISTS

SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT * FROM SC WHERE SC.Sno=Student.Sno AND Cno='1');

3）全称量词

--查询选修了全部课程的学生--没有一门课他是没有选修的SELECT Sname FROM StudentWHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC Sno=Student.Sno AND Cno=Course.Cno) )

5、集合查询

集合操作主要包括并操作UNION、交操作INTERSECT、和差操作EXCEPT

--UNION进行并操作，系统会自动去掉重复元组，要保留重复元组则用UNION ALLSELECT * FROM Student WHERE Sdept='CS'UNIONSELECT * FROM Student WHERE Sage<19;

查询既选修了课程1又选修了课程2

SELECT * FROM SC WHERE Cno='1' INTERSECT(SELECT * FROM SC WHERE Cno='2' )

6基于派生表的查询

通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名

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;SELECT * 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;SELECT * 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;

转载地址：http://vlsh.baihongyu.com/