数据库作业8：SQL练习5 - SELECT（嵌套查询EXISTS、集合查询、基于派生表的查询）

Harriet ·

更新时间:2024-11-10

· 612 次阅读

3.4.3 嵌套查询
4.带有EXISTS谓词的子查询
EXISTS谓词代表存在量词，
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则外层的WHERE子句返回真值；若内层查询结果为空，则外层的WHERE子句返回假值。
NOT EXISTS谓词：
若内层查询结果非空，则外层的WHERE子句返回假值；若内层查询结果为空，则外层的WHERE子句返回真值。
由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

[例 3.60]查询所有选修了1号课程的学生姓名。
思路分析：本查询涉及Student和SC关系，在Student中依次取每个元组的Sno值，用此值去检查SC表。若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');

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换；
所有带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 = '刘晨');

SQL语言中没有全称量词 （For all），可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(∀x)P≡¬(∃x(¬P))( \forall x) P \equiv \neg(\exists x(\neg P))(∀x)P≡¬(∃x(¬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
             )
      );

在这里插入图片描述

SQL语言中没有蕴涵逻辑运算，可以利用谓词演算将逻辑蕴涵谓词等价转换为：p→q≡¬p∨qp \rightarrow q\equiv \neg p\vee qp→q≡¬p∨q
[例 3.63]查询至少选修了学生201215122选修的全部课程的学生号码。
解题思路：用逻辑蕴涵表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y。形式化表示：
用P表示谓词 “学生201215122选修了课程y”
用q表示谓词 “学生x选修了课程y”
则上述查询为：(∀y)p→q(\forall y)p\rightarrow q(∀y)p→q
等价变换：p→q≡¬p∨qp\rightarrow q\equiv \neg p\vee qp→q≡¬p∨q
变换后语义：不存在这样的课程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)
	   );

在这里插入图片描述

3.4.4 集合查询
参加集合操作的各查询结果的列数必须相同；对应项的数据类型必须相同。

并-UNION
UNION：将多个查询结果合并起来时，系统自动去掉重复元组。
UNION ALL：将多个查询结果合并起来时，保留重复元组。
[例 3.64] 查询计算机科学系的学生及年龄不大于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';

交-INTERSECT
[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生的交集。
实际上就是查询计算机科学系中年龄不大于19岁的学生。

SELECT * FROM Student WHERE Sdept='CS' 
INTERSECT
SELECT * FROM Student WHERE Sage<=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');

差-EXCEPT
[例 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;

3.4.5基于派生表的查询
子查询不仅可以出现在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

FROM 子句子查询生成一派生表Avg_sc，该表由avg_sno,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 子句生成派生表，AS关键字可省略，但必须为派生关系指定一个别名：

SELECT Sname
FROM Student,(SELECT Sno FROM SC WHERE Cno='1') SC1
WHERE  Student.Sno=SC1.Sno;

SELECT语句的一般格式：

 SELECT [ALL|DISTINCT]   [别名] [ , [别名]] …
 FROM   [别名] [ , [别名]] …
       |()[AS]
 [WHERE ]
 [GROUP BY  [HAVING]]
 [ORDER BY  [ASC|DESC]];

SELECT子句：
DISTINCT：消除取值重复的行。如果没有指定DISTINCT关键词，则缺省为ALL。
目标列表达式：指定要显示的属性列。查询全部列（*）。可以是算术表达式、字符串常量、函数等。

FROM子句：指定查询对象。
基于派生表的查询AS

WHERE子句：指定查询条件。逻辑运算符AND和 OR连接多个查询条件。比较大小、确定范围（BETWEEN AND）、确定集合 IN ()、字符匹配（[NOT] LIKE）。
连接查询：WHERE子句中用来连接两个表的条件，格式：[.] [.]。自身连接FROM必须使用“别名”。

GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语：只有满足指定条件的组才予以输出。
聚集函数只能用于SELECT子句和GROUP BY子句中的HAVING子句。如果未对查询结果分组，聚集函数将作用于整个查询结果。对查询结果分组后，聚集函数将分别作用于每个组。按指定的一列或多列值分组，值相等的为一组。

HAVING短语与WHERE子句的区别：作用对象不同
WHERE子句作用于基表或视图，从中选择满足条件的元组；
HAVING短语作用于组，从中选择满足条件的组。
嵌套查询：将查询块嵌套在WHERE子句或HAVING短语的条件中。（带IN谓词、带比较运算符、带有ANY或ALL谓词的子查询）

ORDER BY子句：只对最终查询结果按一个或多个属性列排序。缺省值为升序ASC；降序DESC。

作者：sjkylzy

sql练习 exists SQL select 数据数据库嵌套