【数据库系统原理作业】七、数据查询中的嵌套查询_查询至少选修了学生201215122选修的全部课程的学生

嵌套查询：
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
例如：
select Sname
from Student
where Sno IN (select Sno
			  from SC
			  where Cno='2');
-------------------------------------------------
SQl语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询
但子查询中不能使用ORDER BY子句
1
2
3
4
5
6
7
8
9
10
11
12

一、带有IN谓词的子查询

例1.查询与“刘晨”在同一个系学习的学生。
方法一：不相关子查询

select Sno,Sname,Sdept
from Student
where Sdept in(
				select Sdept
				from Student
				where Sname='刘晨');
1
2
3
4
5
6

方法二：相关子查询

select S1.Sno,S1.Sname,S1.Sdept
from Student S1,Student S2
where S1.Sdept=S2.Sdept and
	S2.Sname='刘晨';
1
2
3
4

例2.查询选修了课程名为“信息系统”的学生学号和姓名
方法一:不相关子查询

select Sno,Sname
from Student
where Sno in(select Sno
			 from SC
			 where Cno in(select Cno
			 			  from Course
			 			  where Cname='信息系统'));
1
2
3
4
5
6
7

方法二:连接查询

select Sno,Sname
from Student,SC,Course
where Student.Sno =SC.Sno and
	  SC.Cno = Course.Cno and
	  Course.Cname='信息系统';	
1
2
3
4
5

二、带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符>,<,=,>=,<=,!=

例.找出每个学生超过他选修课程平均成绩的课程号。

select Sno,Cno
from SC
where Grade >= (select AVG(Grade)
				from SC y
				where y.Sno=x.Sno);
1
2
3
4
5

三、带有ANY(SOME)或ALll谓词的子查询

例：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

select Sname,Sage
from Student
where Sage < any(select Sage
				 from Student
				 where Sdept='CS')
				 and Sdept <> 'CS';
1
2
3
4
5
6

或用聚集函数:

select Sname,Sage
from Student
where Sage < (select MAX(Sage)
			  from Student
			  where Sdept='CS')
			  and Sdept <> 'CS';
1
2
3
4
5
6

例2.查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

select Sname,Sage
from Student
where Sage < ALL(select Sage
			 from Student
			 where Sdept='CS')
			 and Sdept<>'CS';
1
2
3
4
5
6

或用聚集函数:

select Sname,Sage
from Student
where Sage < (select MIN(Sage)
			 from Student
			 where Sdept='CS')
			 and Sdept<>'CS';
1
2
3
4
5
6

四、带有EXIST谓词的子查询

EXISTS谓词
	存在量词（左右翻转的E）
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值"true"或逻辑假值"false"
1.若内层查询结果为非空，外层where句子返回真值
2.若内层查询结果为空,则外层的where子句返回假值
--NOT EXISTS与EXISTS相反
由于EXISTS引出的子查询，其目标列表表达式通常都用*，
因为带EXISTS的子查询值返回真值或假值，给出列名无实际意义。
1
2
3
4
5
6
7
8

例1.查询所有选修了1号课程的学生姓名。

select Sname
from Student
where exists(
		select *
		from SC
		where Sno=Student.Sno and Cno='1');
1
2
3
4
5
6

执行结果:

例2.查询没有选修1号课程的学生姓名。

select Sname
from Student
where not exists(select*
				 from SC
				 where Sno=Student.Sno and Cno='1');
1
2
3
4
5

执行结果：

例3.查询与“刘晨”在同一个系学习的学生。

select Sno,Sname,Sdept
from Student S1
where exists(select *
			 from Student S2
			 where S2.Sdept = S1.Sdept and
			 S2.Sname = '刘晨');
1
2
3
4
5
6

执行结果：

用EXISTS/NOT EXISTS实现全程量词
SQL语言中没有全称量词
可以通过下面的式子将全称量词转换为否定和存在量词

例4.查询选修了全部课程的学生姓名。

select Sname
from Student
where not exists(select *
				 from Course
				 where not exists(select *
				 				  from SC
				 				  where Sno=Student.Sno
				 				  and Cno=Course.CNo
				 				)
				 );
1
2
3
4
5
6
7
8
9
10

我的表里没有全选的就不输出结果了

例5.查询至少选修了学生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 SCZ.Sno=SCX.Sno and
				 			SCZ.CNO=SCY.CNO));
1
2
3
4
5
6
7
8
9

执行结果：