集合查询和基于派生表的查询

四、集合查询

select 语句的查询结果是元组的集合，所以多个 select 语句的结果可进行集合操作
集合操作主要包括 union ，交操作 intersect 和差操作 except

注意：参加集合操作的各查询结果的列数必须相同；对应的数据类型也必须相同

• 例1：查询计算机科学系的学生及年龄不大于19岁的学生

select*
form Student
where Sdept=‘CS’
union
select*
from Student
where Sage<=19

本查询实际上是求计算机科学系的所有学生与年龄不大于 19 岁的学生的并集，使用 union 将多个查询结果合并起来时，系统会自动去掉重复元组，如果要保留重复元组则用 union all 操作符

• 例2：查询选修了课程1或者选修了课程2的学生

本例即查询选修课程1的学生集合与选修课程2的学生集合的并集

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

• 例3：查询计算机系的学生与年龄不大于19岁的学生的交集

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

这实际上就是查询计算机科学系中年龄不大于19岁的学生

select *
from Student
where Sdept=‘CS’ and Sage<19;

• 例4：查询既选修了课程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');

• 例5：查询计算机科学系的学生与年龄不大于19岁的学生的差集

select*
from Student
where Sdept='CS'
except 
sxelect*
from Student
where Sage<=19

也就是查询计算机可粗恶习中年龄大于19岁的学生

select*
from Student
where Sdept='CS' and Sage>19 

五、基于派生表的查询

子查询不仅可以出现在 where 子句中，还可以出现在 from 子句中，这时子查询生成的临时派生表（derived table）成为主查询的查询对象

• 例1：找出每个学生超过他自己选修课程平均成绩的课程号

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;

这里 from 子句中的子查询将生成一个派生表 Avg_sc 。
该表由 avg_sno 和 avg_grade 两个属性组成，记录了每个学生的学号及平均成绩。
主查询将 SC 表与 Avg_sc 按学号相等进行连接，选出选修课成绩大于其平均成绩的课程号

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询 select 子句后面的列名为其默认属性。

• 例2：查询所有选修了1号课程的学生姓名（使用派生表的方式）

select Sname
from Student,(select Sno from SC where Cno='1') as SC1
where Student.Sno=SC1.Sno;

需要说明的是，通过 form 子句生成派生表时，as关键字可以省略，但必须为派生关系指定一个别名；而对于基本表，别名时可选择项