《空间数据库原理与方法》笔记/期末复习资料（3. 连接查询、嵌套查询、集合查询、基于派生表的查询）

连接查询：

前面的查询都是针对一个表进行的，若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系型数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。

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

当连接运算符为 = 时，称为等值连接，使用其他运算符称为非等值连接。

例题：查询每个学生及其选修课程的情况。

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;  /*将 Student与SC中同一学生的元组连接起来*/

在等值连接中把目标列中重复的属性列去掉则为自然连接。

例题：查询每个学生及其选修课程的情况，请用自然连接完成。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

本例题中由于 Sname , Ssex , Sage , Sdept , Cno 和 Grade 属性列在 Student 表和 SC 表中是唯一的，因此引用时可以去掉表名前缀，但是 Sno 在两个表中都出现了，因此引用时必须加上表名前缀。

一条 SQL 语句可以同时完成选择和连接查询，这时 WHERE 子句是由连接谓词和选择谓词组成的复合条件。

例题：查询选修 2 号课程且成绩在 90 以上的学生的学号和姓名。

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND       /*连接谓词*/
      Cno='2' AND Grade>90;  /*其他限制条件*/

2.自身连接：

连接操作不仅可以在两个表之间进行，也可以是一个表与其自身进行连接，称为表的自身连接。

例题：查询每一门课程的间接先修课（即先修课的先修课）。

首先为 Course 表取两个别名，一个是 FIRST，另一个是 SECOND。完成该查询的 SQL 语句为：

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

3.多表连接：

连接操作除了可以是两表连接、一个表与其自身连接，还可以是两个以上的表进行连接，后者通常称为“多表连接”。

例题：查询每个学生的学号、姓名、选修的课程名及成绩。

本查询涉及三个表，完成该查询的 SQL 语句如下：

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

嵌套查询：

在 SQL 语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块，将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 语句的条件中的查询称为嵌套查询。例如：

SELECT Sname       /*外层查询或父查询*/
FROM Student
WHERE Sno IN
      (SELECT Sno  /*内层查询或子查询*/
       FROM SC
       WHERE Cno='2';)

嵌套查询使用户可以用多个简单查询构成复杂的查询，从而增强 SQL 的查询能力，以层层嵌套的方式构造程序正是 SQL 中“结构化”的含义所在。

1.带有 IN 谓词的子查询：

在嵌套查询中，子查询的结果往往是一个集合，所以谓词 IN 是嵌套查询中最常使用的谓词。

例题：查询与“刘晨”在同一系学习的学生。

SELECT Sname,Sno,Sdept
FROM Student
WHERE Sdept IN
      (SELECT Sdept
       FROM Student
       WHERE Sname='刘晨');

本例题的查询也可以使用自身连接查询来完成：

SELECT S1.Sname,S1.Sno,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';

例题：查询选修了课程名为“信息系统”的学生学号和姓名。

SELECT Sno,Sname          /*③最后在Student表中找出学生学号和学生姓名*/
FROM Student
WHERE Sno IN
      (SELECT Sno         /*②然后在SC表中找出选修了“信息系统”课程的学生学号*/
       FROM SC
       WHERE Cno IN
             (SELECT Cno  /*①首先在Course表中找出“信息系统”课程的课程号*/
              FROM Course
              WHERE Cname='信息系统'
             )
      );

本查询同样可以用连接查询实现：

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
SC.Cno=Course.Cno AND
Course.Cname='信息系统';

子查询条件不依赖于父查询，则称为“不相关子查询”。如果子查询条件依赖于父查询，则这类子查询称为“相关子查询”，整个查询语句称为“相关嵌套查询”语句。

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

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接，当用户能够确切地知道内层查询返回值是单个值时，可以用 >、<、=、>=、<=、!= 或 <> 等比较运算符进行连接。

例题：找出每个学生超过他自己选修课程平均成绩的课程号。

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)  /*某学生的平均成绩*/
              FROM SC y
              WHERE y.Sno=x.Sno);

x 是表 SC 的别名，又称为元组变量，可以用来表示 SC 的一个元组。内层查询是求一个学生所有选修课的平均成绩，至于是哪个学生的平均成绩要看参数 x.Sno 的值，而该值与父查询是相关的，因此这类查询称为“相关子查询”。

3.带有 ANY (SOME) 或 ALL 谓词的子查询：

子查询返回单值时可以用比较运算符，但返回多值时要用 ANY (有的系统用 SOME ）或 ALL 谓词修饰符。而使用 ANY 或 ALL 谓词时则必须同时使用比较运算符。其语义如下所示：

ANY 和 ALL 谓词的语义

谓词修饰符	含义
>ANY	大于子查询结果中的某个值
>ALL	大于子查询结果中的所有值
<ANY	小于子查询结果中的某个值
<ALL	小于子查询结果中的所有值
>=ANY	大于等于子查询结果中的某个值
>=ALL	大于等于子查询结果中的任何一个值
<=ANY	小于等于子查询结果中的某个值
<=ALL	小于等于子查询结果中的任何一个值
=ANY	等于子查询结果中的某个值
=ALL	等于子查询结果中的所有值（通常没有实际意义）
!= (或<>) ANY	不等于子查询结果中的某个值
!= (或<>) ALL	不等于子查询结果中的任何一个值

例题：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
               FROM Student
               WHERE Sdept='CS')
AND Sdept<>'CS';  /*注意这里是父查询块中的条件*/

本查询也可以用聚集函数来实现，首先用子查询找出计算机科学系（CS）中最大年龄，然后在父查询中查询所有非计算机科学系且年龄小于计算机科学系中最大年龄的学生。SQL 语句如下：

SELECT Sname,Sage
FROM Student
WHERE Sage<
          (SELECT MAX(Sage)
           FROM Student
           WHERE Sdept='CS')
AND Sdept<>'CS';

例题：查询非计算机科学系中比计算机科学系所有学生年龄小的学生姓名和年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<ALL
           (SELECT Sage
            FROM Student
            WHERE Sdept='CS')
AND Sdept<>'CS';

本查询也可以用聚集函数来实现，SQL 语句如下：

SELECT Sage
FROM Student
WHERE Sage<
          (SELECT MIN(Sage)
           FROM Student
           WHERE Sdept='CS')
AND Sdept<>'CS';

ANY（或SOME）、ALL 谓词与聚集函数、IN 谓词的等价转换关系

	=	<> 或 !=	<	<=	>	>=
ANY	IN	--	<NAX	<=MAX	>MIN	>=MIN
ALL	--	NOT IN	<MIN	<=MIN	>MAX	>=MAX

4.带有 EXISTS 谓词的子查询：

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

例题：查询所有选修了 1 号课程的学生姓名。

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

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

由 EXISTS 引出的子查询，其目标列表达式通常都用 * ，因为带 EXISTS 的子查询只返回真值或假值，给出列名无实际意义。

例题：查询没有选修 1 号课程的学生姓名。

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

例题：查询选修了全部课程的学生姓名。

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 来实现带全称量词的查询。

例题：查询至少选修了学生 201215122 选修的全部课程的学生学号。

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 语句的查询结果是元组的集合，所以多个SELECT 语句的查询结果可以进行集合操作。集合操作主要包括并操作 UNION、交操作 INTERSECT 以及差操作 EXCEPT。

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

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

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

例题：查询选修了课程 1 或者选修了课程 2 的学生。

本例即查询选修课程 1 的学生集合与选修课程 2 的学生集合的并集。

SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';

例题：查询计算机科学系的学生与年龄不大于 19 岁的学生的交集。

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

这实际上就是查询计算机科学系中年龄不大于 19 岁的学生。

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

例题：查询既选修了课程 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');

例题：查询计算机科学系的学生与年龄不大于 19 岁的学生的差集。

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

就是等价于查询计算机科学系中年龄大于 19 的学生：

SELECT *
FROM Student
WHERE Sage>19 AND Sdept='CS';

基于派生表的查询：

子查询不仅可以出现在 WHERE 子句中，还可以出现在 FROM 子句中，这时子查询生成的临时派生表是父查询的查询对象。

例题：查询每个学生超过他自己选修课程平均成绩的课程号。

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 子句生成派生表时，AS 关键字可以省略，但是必须为派生关系指定一个别名。而对于基本表，别名则是可选择项。