数据库作业8：SELECT(嵌套查询、exists、集合查询、基于派生表的查询)

［例3.61］ 査询所有选修了 1号课程的学生姓名。
        本査询涉及Student和SC表。可以在Student中依次取每个元组的Sno值，用此值去检査SC表。若SC中存在这样的元组，其Sno值等于Student.Sno值，并且其Cno = T,则取此Student.Sname送入结果表。代码如下：

select Sname 
	from Student
	where exists
		(
			select *
			from SC
			where Sno = Student.Sno
			and Cno = '1'
		);

        运行结果如下：


        使用存在量词seists后,若内层査询结果非空，则外层的where子句返回真值，否则返回假值。
        由EXISTS引出的子査询，其目标列表达式通常都用*,因为带exists的子査询只
返回真值或假值，给出列名无实际意义。

［例3.61］ 査询没有选修1号课程的学生姓名。

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

［例3.62］ 査询选修了全部课程的学生姓名。
        SQL中没有全称量词(fbrall),但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
$(\forall x)P\equiv (\exists x(\neg P))$
        由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：査询这样的学生，没有一门课程是他不选修的。代码如下：

select Sname 
	from Student
	where not exists
		(
			select *
			from Course
			where not exists	
				(
					select *
					from SC
					where Sno = Student.Sno
					and Cno = Course.Cno
				)
		);

        从而用exists / not exsits来实现带全称量词的査询。
［例3.63］ 査询至少选修了学生201215122选修的全部课程的学生号码。
        本查询可以用逻辑蕴涵来表达:査询学号为x的学生，对所有的课程y,只要201215122学生选修了课程y,则x也选修了 y。形式化表示如下：

• 用p表示谓词“学生201215122选修了课程y”
• 用q表示谓词“学生x选修了课程y” ，则上述査询为
  $\forall y(p\to q)$
• SQL语言中没有蕴涵(implication)逻辑运算，但是可以利用谓词演算将一个逻辑蕴涵的谓词等价转换为
  $p\to q\equiv \neg p\vee q$
• 该査询可以转换为如下等价形式：
  $(\forall y)p\to q\equiv \neg (\exists y(\neg (\neg p\vee q)))\equiv \neg \exists y(p\wedge \neg q)$
• 它所表达的语义为：不存在这样的课程y,学生201215122选修了 y,而学生x没有选。用SQL表示如下：

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
				)
		);

集合查询：
        select语句的査询结果是元组的集合，所以多个select语句的结果可进行集合操作。集合操作主要包括并操作union、交操intersect和差操作except.
［例3.64］ 査询计算机科学系的学生及年龄不大于19岁的学生。

select *
	from Student
	where Sdept = 'CS'
	union
	select *
	from Student
	where Sage <= 19;

        使用union将多个査询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用union all操作符。

［例3.65］査询选修了课程1或者选修了课程2的学生。

select Sno 
	from SC
	where Cno = '1'
	union
	select Sno 
	from SC
	where Cno = '2';

[例 3.66] 査询计算机科学系的学生与年龄不大于19岁的学生的交集。

select *
	from Student
	where Sdept = 'CS'
	intersect
	select *
	from Student
	where Sage <= 19;

［例3.67］ 査询既选修了课程1又选修了课程2的学生。就是査询选修课程1的学生集合与选修课程2的学生集合的交集。

select Sno
	from SC
	where Cno = '1'
	intersect
	select Sno
	from SC
	where Cno = '2';
--或者
select Sno 
	from SC 
	where Cno = '1'
	and Sno in
	(
		select Sno 
		from SC
		where Cno = '2'
	);

［例3.68］ 査询计算机科学系的学生与年龄不大于19岁的学生的差集。

select *
	from Student
	where Sdept = 'CS'
	except
	select *
	from Student
	where Sage <= 19;
--或者
select *
	from Student
	where Sdept = 'CS'
	and Sage > 19;

基于派生表的查询：
        子査询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子査询生成的临时派生表成为主査询的査询对象。例如：
［例3.57］ 找出每个学生超过他自己的选修课程平均成绩的课程号

--原来的方法：
select Sno,Cno
from SC x
where Grade>=
	(select avg(Grade)
		from SC y
		where y.Sno=x.Sno
	);
--新方法：
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子句后面的列名为其默认属性。例如:
［例3.60］ 査询所有选修了1号课程的学生姓名。

--原来的方法
select Sname
	from Student
	where exists
		(
			SELECT *
			FROM SC
			WHERE Sno=Student.Sno 
			and Cno='1'
		);
--新方法：
select Sname
	from Student , (select Sno from SC where Cno = '1')
		as SC1
	where Student.Sno = SC1.Sno;

两种方法的不同之处：

1. 原方法的from后面添加的是表名，新方法from中要为派生关系指定一个别名 。
2. 原方法的子查询出现在where子句中，新方法子查询在from子句中。

总结：

1. 子句的返回值是一个bool值 ，有exists的谓词的子查询返回 true 或 false .
2. 使用 exists 时候，若内层查询结果为非空，则外层的 where 子句返回真值，反之则返回假值。
3. exists内部的子查询语句(select … from…)， 我将其称为exists的内查询语句，内查询语句返回一个结果集。
4. 运行时候先看语句的第一个词，当第一个词是 select 关键字的时候，跳到 from 关键字，然后通过 from 关键字找到表名并把表装入内存。接着是找 where 关键字，如果找不到则返回到 select 找字段解析，如果找到where，则分析其中的条件，完成后再回到 select 分析字段。