2017.11.15
六:嵌套查询
嵌套查询概述:
一个select-from-where 语句称为一个查询块
将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询
例1:查询选修了2号课程得学生姓名:
内连接:select sname from student,sc where student.sno=sc.sno and cno=2
或:select sname from student where sno in --外层查询/父查询
(select sno from sc where cno=2) --内层查询/子查询
引出子查询得谓词:
带有in谓词的子查询
带有比较运算符的子查询
带有any或all谓词的子查询
带有exists谓词的子查询
1.带有in谓词的子查询
例2:查询与'刘晨'在同一个专业学习的学生:(涉及2张表)
select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘晨')
查询与'刘晨'在同一个专业学习的学生但是不包括'刘晨':
select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘晨') and sname<>'刘晨'
例3:查询选修了课程名为'信息系统'的学生学号和姓名:(涉及3张表)
select sno,sname from student where sno in --3.最后在student关系中取出sno和sname
( select sno from sc where cno in --2.然后在sc中找出选修了课程的学号和姓名
(select cno from course where cname='信息系统')); --1.首先在course关系中找出'信息系统'的课程号
select sno,sname from student,sc,course where student.sno=sc.sno....
2.带有比较运算符的子查询
例4:查询与'刘晨'在同一个专业学习的学生:
select sno,sname,sdept from student where sdept = (select sdept from student where sname='刘晨')
3.带有any或all谓词的子查询:
>any >min
>all
<any <max
<all
>=any >=min
>=all
<=any <=max
<=all
=any
=all
!=(<>)any 不存在
!=(<>)all not in
例5:查询其他专业中比计算机任意一个(其中某一个)学生年龄小的学生姓名和年龄
select sname,sage from student where sage<any(select sage from student where sdept='计算机') and sdept<>'计算机';
例6:查询其他专业中比计算机任意一个(其中某一个)学生年龄小的学生姓名和年龄(用聚合函数):
select sname,sage from student where sage<(select (max)sage from student where sdept='计算机') and sdept<>'计算机';
例7:查询...
4.带有exists谓词的子查询:
1.exists谓词:
不返回任何数据,只产生逻辑真值'true'或逻辑假值'false'
若内层查询结果非空,则返回真值
若内层查询结果为空,则返回假值
由exists引出的子查询,其目标列表达式通常都用*,因为带exists的子查询只返回真或假值,给出列名五实际意义
2.not exists谓词:
不返回结果集为真
例8:查询所有选修了1号课程的学生姓名:
select sname from studnet where sno in (select sno from sc where cno=1)
select sname from studnet where exists(select * from sc where sc.sno=student.sno and cno='1' )
例9:查询没有选修1号课程的学生姓名:
select sname from studnet where not exists(select * from sc where sc.sno=student.sno and cno='1')
查询与'刘晨'在同一个专业学习的学生:
select sno,sname,sdept from studnet s1 where exists (select * from student s2 where s2.sdept=s1.sdept and sname='刘晨')
查询选修了全部课程的学生姓名:
法1:select sname from student where not exists
(select * from course where not exists
(select * from sc where sc.sno=studnet.sno and sc.sno=course.cno))
法2:select sname from student where sno in
(select sno from sc group by sno having count(*)=(select count(*) from course))
练习:
use students
go
--1、用嵌套查询所有比“王敏”大的学生的姓名和年龄。
select sname,sage from student where sage >all (select sage from student where sname='王敏')
--2、用嵌套查询选修了3号课程的学生姓名。
select sname from student where sno in (select sno from sc where cno=3)
--3、用嵌套查询选修了课程名为“数学”的学生学号和姓名。
select sno,sname from student where sno in (select sno from sc where sdept='数学')
--4、查询其他系中比电子系任意一个学生年龄小的学生姓名和年龄。
select sname,sage from student where sage<any (select sage from student where sdept='电子') and sdept<>'电子'
--5、查询其他系中比电子系所有学生年龄都小的学生姓名及年龄。
-select sname,sage from student where sage <all(select sage from student where sdept='电子') and sdept<>'电子';
select sname,sage from student where sage <(select MIN(sage) from student where sdept='电子') and sdept<>'电子';
--6、用EXISTS嵌套查询所有选修了1号课程的学生姓名。
select sname from student where exists(select * from sc where sc.sno=student.sno and cno=1)
--7、查询至少选修了一门课程的学生姓名。
--select sname from student where not exists(select * from sc where sc.sno=student.sno and sdept=null)
select sname from student where sno in (select sno from sc)
--8、查询选修了两门及以上课程的学生姓名。
select sname from student where sno in (select sno from sc group by sc.sno having count(*)>=2 )
--Select sname from student a join sc b on a.sno=b.sno where having count(b.sno)>=2
--9、查询没有选修任何课程的学生姓名。
select sname from student where not exists(select * from sc where student.sno=sc.sno)
--或select sname from student where not in (select sno from sc )
--10、查询至少被选修了二次的课程号及课程名称。
select cno,cname from course where cno in (select cno from sc group by cno having count(*)>=2 )
--11、查询选修了全部课程的学生姓名。
select sname from student where not exists
(select * from course where not exists
(select * from sc where sc.sno=student.sno and sc.sno=course.cno))
--12、查询所有学生都选修了的课程信息。
select cno,cname from course where not exists
(select * from student where not exists
(select * from sc where sc.sno=student.sno and sc.cno=course.cno))