数据库系统概论 ---- 第三章 -- 关系数据库标准语言SQL( 3.4 数据查询 )（四）

5. 内连接

内连接：合并具有相同属性列的两个或两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行，即只保存满足连接条件的元组。

使用 [INNER] JOIN …ON 的方式实现多表的内连接查询。

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

SELECT s.Sno, Sname, Cname, Grade
FROM Student s
    INNER JOIN SC ON s.Sno = SC.Sno
    JOIN Course c ON c.Con = SC.Cno;

3.4.3 嵌套查询

在SQL语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

例如：

SELECT Sname
FROM Student
WHERE Sno IN (
    SELECT Sno
    FROM SC
    WHERE Cno = '2'
    );

本例中，下层查询块SELECT Sno FROM SC WHERE Cno=2'是嵌套在上层查询块SELECT Sname FROM Student WHERE Sno IN的WHERE条件中的。

上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。

SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。

子查询的SELECT 语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

1．带有IN谓词的子查询

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

子查询的查询条件不依赖于父查询，称为不相关子查询。

嵌套查询可以由里向外处理，即先执行子查询，子查询的结果用于建立其父查询的查找条件。

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

SELECT Sname
FROM Student
# 查询结果确定为一个数值在用等号
WHERE Sdept = (
    SELECT Sdept
    FROM Student
    WHERE Sname = '刘晨'
    );

用连接完成

SELECT s1.Sname
FROM Student s1
    INNER JOIN Student s2 ON s1.Sdept = s2.Sdept AND s2.Sname = '刘晨';

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

SELECT Sno, Sname
FROM Student
WHERE Sno IN (
    SELECT Sno
    FROM SC
    WHERE Cno = (
        SELECT Cno
        FROM Course
        WHERE Cname = '信息系统'
        )
    );

使用连接实现

SELECT s.Sno, Sname
FROM Student s
    INNER JOIN SC ON s.Sno = SC.Sno
    INNER JOIN Course c ON c.Cname = '信息系统' AND c.Con = SC.Cno;

子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。

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

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

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

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

SELECT sc1.Sno, sc1.Cno
FROM SC sc1
WHERE Grade > (
    SELECT avg(sc2.Grade)
    FROM SC sc2
    WHERE sc2.Sno = sc1.Sno
    );

该查询的子查询的查询条件依赖于父查询，为相关子查询。

在该查询中，从上到下遍历sc1这个表，都会到sc2中求一次与sc1中当前行的学号相等的平均成绩，然后进行比较判断是否为需要查询的结果。

求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关,因此必须反复求值。

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

子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME)或ALL 谓词修饰符。而使用ANY或ALL 谓词时则必须同时使用比较运算符。

其语义如下所示：

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

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

用聚集函数实现：

SELECT Sname, Sage
FROM Student
WHERE Sdept != 'CS' AND
      # 比计算机系中的任意一个学生的年龄小
      # 所有只要小于最大年龄即满足条件
      Sage < (
          SELECT MAX(Sage)
          FROM Student
          WHERE Sdept = 'CS'
        ) ;

用聚集函数实现子查询通常比直接用ANY 或ALL查询效率要高。

4. 带有EXISTS 谓词的子查询

EXISTS 代表存在量词:。

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

可以利用EXISTS来判断x∈S、S包含于R、S=R、S∩R非空等是否成立。

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

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

mysql中EXISTS是一个函数

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

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

与EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的 WHERE子句返回真值，否则返回假值。

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

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

一些带EXISTS或NOT EXISTS 谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和 ALL谓词的子查询都能用带EXISTS 谓词的子查询等价替换。

SQL中没有全称量词，但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

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

没有一门课程是他不选修的。

SQL 语言中没有蕴涵逻辑运算，但是可以利用谓词演算将一个逻辑蕴涵的谓词等价转换为

3.4.4 集合查询

SELECT语句的查询结果是元组的集合，所以多个 SELECT 语句的结果可进行集合操作。

集合操作主要包括：

• 并操作 UNION
• 交操作 INTERSECT(mysql不支持)
• 差操作 EXCEPT(mysql不支持)

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

使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用UNION ALL操作符。

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

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

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

可以使用如下语句进行代替：

SELECT DISTINCT Sno
FROM SC
WHERE Cno = '1' OR Cno = '2';
SELECT Sno
FROM SC
WHERE Cno = '1' OR Cno = '2';

效率会比并集的效率高，因为并集查询会遍历两次表，从中选出满足条件的元组进行合并。

mysql中求交集可以使用AND实现

mysql中求差，可以使用NOT IN

3.4.5 基于派生表的查询

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

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

通过 FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。

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

SELECT SC.Sno,Cno
FROM SC, (
    SELECT Sno, AVG(Grade) avg
    FROM SC
    GROUP BY Sno
) AS avg_sc
WHERE SC.Sno = avg_sc.Sno AND
        SC.Grade>avg_sc.avg;

3.4.6 SELECT 语句的一般格式

SELECT 语句的一般格式：

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

1. 目标列表达式的可选格式

*
<表名>.*
COUNT( [DISTINCT|ALL] * )
[<表名>.]<属性列名表达式> [,[<表名>.]<属性列名表达式>]...

其中，<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算(+，一，*，/)组成的运算公式。

2. 聚集函数的一般格式

3. WHERE 字句的条件表达式的可选格式