操作目录
四.集合查询
多个 SELECT 语句的结果可以进行 集合操作 。 集合操作主要包括 并操作 UNION 、 交操作 INTERSECT 、 差操作 EXCEPT 。
【例3.64】 查询计算机科学系的学生 及 年龄不大于19岁的学生。
即为查询计算机科学系的全体学生 和 其他系年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 19;
查询结果:
【例3.65】 查询选修了课程1 或者 选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno = '1'
UNION
SELECT Sno
FROM SC
WHERE Cno = '2'
查询结果:
用完整的SC表作为对照:
【例3.66】 查询计算机科学系的学生 与 年龄不大于19岁的学生的交集。
即查询计算机科学系的年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept = 'CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage <= 19;
查询结果:
用完整的Student表作为对照:
【例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;
查询结果为:
就是查询计算机科学系中年龄大于19岁的学生
SELECT *
FROM Student
WHERE Sdept= 'CS' AND Sage>19;
结果同上。
五.基于派生表的查询
子查询 不仅可以出现在 WHERE 子句中,还可以出现在 FROM 子句中,这时子查询生成的 临时派生表 成为主查询的查询对象。
----------【例3.57】找出每个学生超过他自己选修课程平均成绩的课程号,可以用如下查询完成:
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
查询结果为:
若去掉最后一行 and SC.Grade >=Avg_sc.avg_grade
语句,则为找出每个学生选修课程的课程号。
SQL语句为:
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
查询结果为:
----------【例3.60】查询所有选修了1号课程的学生姓名,可以用如下查询完成:
SELECT Sname
FROM Student,
(SELECT Sno FROM SC WHERE Cno='1') AS SC1
WHERE Student.Sno=SC1.Sno;
通过 FROM 子句生成派生表时, AS 关键字可以省略,但 必须为派生关系指定一个别名,例该例题中的SC1不可省 。
故等价于: