【2019-2020春学期】数据库作业8:SQL练习5 - SELECT(嵌套查询EXISTS、集合查询、基于派生表的查询)
4.带有EXISTS谓词的子查询
EXISTS谓词
(1)存在量词
(2)带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值 “true” 或逻辑假值 “false” 。
<1>若内层查询结果非空,则外层的WHERE子句返回真值
<2>若内层查询结果为空,则外层的WHERE子句返回假值
(3)由EXISTS引出的子查询,其目标列表达式通常都用 * ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。
NOT EXISTS谓词(与EXISTS谓词相反)
(1)若内层查询结果非空,则外层的WHERE子句返回假值
(2)若内层查询结果为空,则外层的WHERE子句返回真值
例 3.60查询所有选修了1号课程的学生姓名。
思路分析:
(1)本查询涉及Student和SC关系
(2)在Student中依次取每个元组的Sno值,用此值去检查SC表
(3)若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno= ‘1’,则取此Student.Sname送入结果表
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ' 1 ');
例 3.61 查询没有选修1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
不同形式的查询间的替换
(1)一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
(2)所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
例 3.55 查询与“刘晨”在同一个系学习的学生。
可以用带EXISTS谓词的子查询替换:
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept AND
S2.Sname = '刘晨');
!!! 用EXISTS/NOT EXISTS实现全称量词(难点)
SQL语言中没有全称量词 (For all)
可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:
例 3.62 查询选修了全部课程的学生姓名。
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 EXISTS实现逻辑蕴涵(难点)
SQL语言中没有蕴涵逻辑运算
可以利用谓词演算将逻辑蕴涵谓词等价转换为:
(参考离散数学)
例 3.63 查询至少选修了学生201215122选修的全部课程的学生号码。
解题思路:
(1)用逻辑蕴涵表达:查询学号为x的学生,对所有的课程y,只要201215122学生选修了课程y,则x也选修了y。
(2)形式化表示:
<1>用p表示谓词 “学生201215122选修了课程y”
<2>用q表示谓词 “学生x选修了课程y”
<3>则上述查询为:
(3)等价变换:
(4)变换后语义:不存在这样的课程y,学生201215122选修了y,而学生x没有选。
用NOT EXISTS谓词表示:
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));
EXISTS的使用总结:
1、谓词EXISTS只会返回逻辑真值true或者逻辑假值false。
2、EXISTS若内层查询结果为非空,则外层WHERE子句返回真值;若为空,则返回假值;NOT EXISTS谓词相反。
3、一些EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换,但所有带有IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
大概步骤:先按照离散数学的方法将问题的形式化的表达出来,把带有全称量词的谓词转换为等价的带有存在量词的谓词,用谓词演算将逻辑蕴涵谓词等价转换(,)),理解转换后的语义,一般情况下否定部分用NOT EXISTS谓词,肯定部分用EXISTS谓词,根据逻辑嵌套。
三、集合查询
集合操作的种类
1、并-UNION
2、交-INTERSECT
3、差-EXCEPT
(1)参加集合操作的各查询结果的列数必须相同
(2)对应项的数据类型必须相同
例 3.64 查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
UNION:将多个查询结果合并起来时,系统自动去掉重复元组
UNION ALL:将多个查询结果合并起来时,保留重复元组
若为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.66 实际上就是查询计算机科学系中年龄不大于19岁的学生。
用连接查询解决该问题:
SELECT *
FROM Student
WHERE Sdept= 'CS' AND Sage<=19;
例 3.67 查询既选修了课程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;
例3.68 实际上是查询计算机科学系中年龄大于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
· 如果子查询中没有聚集函数,
· 派生表可以不指定属性列,
· 子查询SELECT子句后面的列名为其缺省属性。
改写 例3.60查询所有选修了1号课程的学生姓名,可以用如下查询完成:
SELECT Sname
FROM Student,
(SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1
WHERE Student.Sno=SC1.Sno;
就是在FROM子句里进行子查询,形成一个派生表(临时表)。若子查询中有聚集函数,要指定属性列,反之,不指定(但都要指定表名)。后续与无派生表时类似。
两种方法对比,其实大体意思类似只是执行顺序不同,可根据具体情况选择,令语句更加简洁明了。
SELECT总结:
SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名]
[ ,<表名或视图名> [别名]] …
|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
分析题目意思,按此格式书写即可。
心得体会:
我是真的不太会写心得体会。。。总之,重在理解,多实验,自己打代码有助于记忆和理解。比较绕的部分是NOT EXISTS的嵌套查询,可以通过纸笔,画执行步骤会更清晰易懂。最后,千万别拖作业,好累(当然我还是没有偷工减料),就是如果早写能从容一点。