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

 

---

• SELECT:【例3.60】~【例3.68】；EXISTS 的使用总结。
• 基于派生表的查询：改写3.57 ，改写3.60；两种方法对比。
• SELECT 总结

1. 在SQLserver上运行，观察运行效果，并把代码写到作业中。

2. 写出自己的理解/收获/心得体会（部分比较复杂的例题，建议增加测试方法和测试数据，举一反三）。

REF：教材3.4“数据查询”的部分例题

REF：建表&插入数据

作业原地址：作业

 

目录

 

4.带有EXISTS谓词的子查询

集合查询

基于派生表的查询

SELECT总结

总结

 

---

---

 

4.带有EXISTS谓词的子查询

 

EXISTS代表存在量词∃ 。

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

 

【例3.60】查询所有选修了1号课程的学生姓名。

 

步骤：在Student中依次取每个元组的Sno值，去检查SC表。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且Cno='1'，则取此值送入结果表。

 

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

 

注：

使用EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。

使用NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。

 

【例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谓词的子查询等价替换。

例如：

 

SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
      (SELECT *
        FRMO Student S2
        WHERE S2.Sdept = S1.Sdept AND
              S2.Sname = '刘晨');

 

【例3.62】查询选修了全部课程的学生姓名。

 

SQL中没有全称量词（for all）但是可以替换为：(∀x)P≡¬(∃x(¬P))

 

则此例：选修全部课程 → 没有一门是他不选修的

 

 

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

 

【例3.63】查询至少选修了学生201215122选修的全部课程的学生号码。

本查询可用逻辑蕴涵表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y。

形式化表示如下：
用p表示谓词 “学生201215122选修了课程y”
用q表示谓词 “学生x选修了课程y”
则上述查询为: (∀y)p→q
等价变换：(∀y)p→q≡¬(∃y(p∧¬q)))≡ ¬(∃y(¬(¬p∨q)))≡¬∃y(p∧¬q)
它所表达的语义：不存在这样的课程y，学生201215122选修了y，而学生x没有选。

 

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

 

• 集合查询

 

多个SELECT语句的结果可以进行集合操作。

集合操作主要包括并操作UNINO、交操作INTERSECT和差操作EXCEPT。

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

 

【例3.61】查询计算机科学系的学生及年龄不大于19岁的学生。

 

SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

 

注：该题即求并集，使用UNINO将多个查询结果合并起来，系统会自动去掉重复的元组。若要保留重复元组则使用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;

 

实际上就是查询计算机科学系中年龄不大于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;

 

也就是查询计算机科学系中年龄大于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

主查询将SC表与Avg_sc按学号相等进行连接，选出选修课成绩大于其平均成绩的课程号。

 

原代码：

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade) 
	  		  FROM SC y
      		  WHERE y.Sno=x.Sno);

 

【例3.60】查询所有选修了1号课程的学生名字。

 

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

 

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

 

原代码：

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

 

注：通过FROM子句生成派生表时，AS关键字可以忽略，但必须为派生类关系指定一个别名。

 

• SELECT总结

 

一般格式：

SELECT [ALL|DISTINCT]  <目标列表达式> [别名] [ ,<目标列表达式> [别名]]…
FROM <表名或视图名> [别名]  [ ,<表名或视图名> [别名]]… |(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

 

数据查询

一、单表查询
  1.选择表中的若干列
     ①查询指定列
     ②查询全部列
     ③查询经过计算的值
  2.选择表中的若干元组
     ①消除取值重复的行
     ②查询满足条件的元组（比较大小、确定范围、确定集合、字符匹配、涉及空值的查询、多重条件查询。）
     ③ORDER BY子句
     ④聚集函数
     ⑤GROUP BY子句

二、连接查询

  1.等值与非等值连接查询

  2.自身连接

  3.外连接

  4.多表连接

三、嵌套查询
  1.带有IN谓词的子查询

  2.带有比较运算符的子查询

  3.带有ANY（SOME）或ALL谓词的子查询

  4.带有EXISTS谓词的子查询

四、集合查询

UNION、INTERSECT、EXCEPT

五、基于派生表的查询

 

---

总结

  还真说对了，这部分真的和离散一样。当初学离散的时候就没怎么明白，现在怕不是又要吃亏。加把劲补习吧。

  出了离散这种内容外，别的还好，代码也好理解。还可以吧

 

知识点：SQL中没有全称量词，需要替换：(∀x)P≡¬(∃x(¬P))

---

 

上期传送阵：【数据库】作业7——SQL练习4 - SELECT（连接查询、嵌套查询）

下期传送阵：————

 

---

 

！！！备注！！！————忘记发截图了（之后补上）

 

---

完成时间：1h+

          以上

                                         ————（2020.3.22）