数据库原理-数据查询 嵌套查询【二】_查询年龄不大于20的cs系学生

数据库原理-数据查询 嵌套查询【二】

带有ANY(SOME)或ALL谓词的子查询

在谓词逻辑中，还有存在量词和全称量词的概念，在SQL中并没有对应的表达，统一采用“谓词”来表达

方法一：引入ANY和ALL谓词，其对象为某个查询结果，表示其中任意一个值或者全部值

方法二：引入EXIST谓词，其对象也是某个查询结果，但表示这个查询结果是否为空，返回真值

使用ANY或ALL谓词时必须同时使用比较运算

语义为：

​

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

	SELECT Sname,Sage
	FROM Student
	WHERE Sage < ANY (SELECT Sage
					  FROM Student
					  WHERE Sdept='CS')
	AND Sdept <> 'CS';
1
2
3
4
5
6

执行过程：

1. 首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20，19）
2. 处理父查询，找所有不是CS系且年龄小于20或19的学生

用聚集函数实现

	SELECT Sname,Sage
	FROM Student
	WHERE Sage < (SELECT MAX(Sage)
				  FROM Student
				  WHERE Sdept='CS')
	AND Sdept <> 'CS';
1
2
3
4
5
6

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

方法一：用ALL谓词

	SELECT Sname,Sage
	FROM Student
	WHERE Sage < ALL
				(SELECT Sage
				FROM Student
				WHERE Sdept = 'CS')
	AND Sdept <> 'CS';
1
2
3
4
5
6
7

方法二：用聚集函数

	SELECT Sname,Sage
	FROM Student
	WHERE Sage < (SELECT MIN(Sage)
				  FROM Student
				  WHERE Sdept = 'CS')
	AND Sdept <> 'CS';
1
2
3
4
5
6

ANY(或SOME)，ALL谓词与聚集函数、IN谓词的等价转换关系

EXISTS谓词

• 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”
  • 若内层查询结果非空，则外层的WHERE子句返回真值
  • 若内层查询结果为空，则外层的WHERE子句返回假值
• 由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

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

思路分析：

• 本查询涉及Student和SC关系
• 在Student中依次取每个元组的Sno值，用此值去检查SC表
• 若SC中存在这样的元组，其Sno值等于此Student,Sno值，并且其Cno=‘1’,则取此Student.Sname送入结果表

	SELECT Sname
	FROM Student
	WHERE EXISTS
		  (SELECT *
		   FROM SC
		   WHERE Sno=Student.Sno AND Cno='1');
1
2
3
4
5
6

查询没有选修1号课程的学生姓名

	SELECT Sname
	FROM Student
	WHERE NOT EXISTS
		  (SELECT *
		   FROM SC
		   WHERE Sno=Student.Sno AND Cno='1');
1
2
3
4
5
6

用EXISTS代替其他谓词

• 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

用EXISTS/NOT EXISTS实现全称量词(难点)

用EXISTS实现逻辑蕴含

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

可以用带EXISTS谓词的相关子查询替换：

	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
7

查询选修了全部课程的学生姓名

	SELECT Sname
	FROM Student
	WHERE NOT EXISTS
		  (SELECT *
		   FROM Course
		   WHERE NOT EXISTS
		   		 (SELECT *
		   		  FROM SC
		   		  WHERE Sno=Student.Sno
		   		  	AND Con=Course.Cno
		   		 )
		   );
1
2
3
4
5
6
7
8
9
10
11
12

查询至少选修了学生201215122选修的全部课程的学生号码

	SELECT DISTINCT SCX.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
10
11