欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【2019-2020春学期】数据库作业8:SQL练习5 - SELECT(嵌套查询EXISTS、集合查询、基于派生表的查询)

程序员文章站 2024-03-21 17:40:58
...

4.带有EXISTS谓词的子查询

EXISTS谓词
(1)存在量词 \exists

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

【2019-2020春学期】数据库作业8:SQL练习5 - SELECT(嵌套查询EXISTS、集合查询、基于派生表的查询)
例 3.61 查询没有选修1号课程的学生姓名。

    SELECT Sname
    FROM     Student
    WHERE NOT EXISTS
                  (SELECT *
                   FROM SC
                   WHERE Sno = Student.Sno AND Cno='1');

【2019-2020春学期】数据库作业8:SQL练习5 - SELECT(嵌套查询EXISTS、集合查询、基于派生表的查询)
不同形式的查询间的替换
(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语言中没有全称量词 \forall(For all)
可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:
(x)P¬(x(¬P))(\forall x)P \equiv \neg (\exists x(\neg P))

例 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语言中没有蕴涵逻辑运算
可以利用谓词演算将逻辑蕴涵谓词等价转换为:
pq¬pqp \rightarrow q \equiv \neg p \vee q
(参考离散数学)

例 3.63 查询至少选修了学生201215122选修的全部课程的学生号码。
解题思路
(1)用逻辑蕴涵表达:查询学号为x的学生,对所有的课程y,只要201215122学生选修了课程y,则x也选修了y。
(2)形式化表示
<1>用p表示谓词 “学生201215122选修了课程y”
<2>用q表示谓词 “学生x选修了课程y”
<3>则上述查询为: ypq(\forall y)p \rightarrow q
(3)等价变换:ypq¬y(p¬q)(\forall y)p \rightarrow q \equiv \neg \exists y (p∧ \neg q)
(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));

【2019-2020春学期】数据库作业8:SQL练习5 - SELECT(嵌套查询EXISTS、集合查询、基于派生表的查询)

EXISTS的使用总结:
1、谓词EXISTS只会返回逻辑真值true或者逻辑假值false。
2、EXISTS若内层查询结果为非空,则外层WHERE子句返回真值;若为空,则返回假值;NOT EXISTS谓词相反。
3、一些EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换,但所有带有IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

大概步骤:先按照离散数学的方法将问题的形式化的表达出来,把带有全称量词的谓词转换为等价的带有存在量词的谓词,用谓词演算将逻辑蕴涵谓词等价转换((x)P¬(x(¬P))(\forall x)P \equiv \neg (\exists x(\neg P))pq¬pqp \rightarrow q \equiv \neg p \vee q)),理解转换后的语义,一般情况下否定部分用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;

【2019-2020春学期】数据库作业8:SQL练习5 - SELECT(嵌套查询EXISTS、集合查询、基于派生表的查询)
UNION:将多个查询结果合并起来时,系统自动去掉重复元组
UNION ALL:将多个查询结果合并起来时,保留重复元组

若为UNION ALL,结果如下:
【2019-2020春学期】数据库作业8:SQL练习5 - SELECT(嵌套查询EXISTS、集合查询、基于派生表的查询)

例 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的嵌套查询,可以通过纸笔,画执行步骤会更清晰易懂。最后,千万别拖作业,好累(当然我还是没有偷工减料),就是如果早写能从容一点。

相关标签: sqlserver