嵌套查询
在SQL语句中,一个select from where 语句称为一个查询块。将一个查询块嵌套在另一个查询快的where 子句或having短句中的查询称为嵌套查询
子查询不能使用order by语句
1.带有in的子查询
3.55 查询与刘晨在同一个系的学生
select sno,sname,sdept
from student
where sdept='cs';
子查询条件不依赖父查询,称为不相关子查询,否则为相关子查询
3.56 查询选修了课程名为信息系统的学生学号和姓名
select sno,sname
from student --根据学号找学生
where sno in (
select sno--根据课程号找学号
from sc
where cno in (
select cno
from course
where cname='信息系统'--先找课程号
)
);
2.带有比较运算符的子查询
3.57 找出每个学生超过他自己选修课平均成绩的课程号
select Sno , Cno
from SC x
where Grade >=
(
select avg(Grade)
from SC y
where y.Sno = x.Sno
);
3.带有any(some)或all谓词的子查询
[例3.58] 査询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
select Sname , Sage
from Student
where Sage < any
(
select Sage
from Student
where Sdept = 'CS'
)
and Sdept <> 'CS';
[例3.59] 査询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄
select Sname , Sage
from Student
where Sage < all
(
select Sage
from Student
where Sdept = 'CS'
)
and Sdept<> 'CS';;
[例3.60] 査询所有选修了 1号课程的学生姓名
select Sname
from Student
where exists
(
select *
from SC
where Sno = Student.Sno
and Cno = '1'
);
4.带有exist谓词的子查询
[例3.61] 査询没有选修1号课程的学生姓名
select Sname
from Student
where not exists
(
select *
from SC
where Sno = Student.Sno
and Cno = '1'
);
[例3.62] 査询选修了全部课程的学生姓名
select Sname
from Student
where not exists
(
select *
from Course
where not exists
(
select *
from SC
where Sno = Student.Sno
and Cno = Course.Cno
)
);