MySQL基础笔记（三）复杂查询

「爱情、让人受尽委屈。」 2022-08-01 16:36 235阅读 0赞

所谓复杂查询，指涉及多个表、具有嵌套等复杂结构的查询。这里简要介绍典型的几种复杂查询格式。

# 一、连接查询 #

**连接**是区别关系与非关系系统的最重要的标志。通过连接运算符可以实现多个表查询。连接查询主要包括内连接、外连接等。

假设有`Student`和`Grade`两个表如下：

+-----+--------+-------+     +-----+------------+--------+
    | sID | sName  | sDept |     | gID | gCourse    | gScore |
    +-----+--------+-------+     +-----+------------+--------+
    |   1 | Paul   | CS    |     |   1 | Math       |     87 |
    |   2 | Oliver | MS    |     |   2 | English    |     95 |
    |   3 | Jack   | SE    |     |   3 | Physics    |     76 |
    |   4 | Robin  | CS    |     |   7 | Philosophy |     76 |
    +-----+--------+-------+     +-----+------------+--------+

## 1.1 内连接 ##

**内连接（INNER JOIN）**使用比较运算符进行表间列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。

当比较操作符是`=`时，称为等值连接：

SELECT * FROM Student INNER JOIN Grade ON Student.sID = Grade.gID;

等价于

SELECT * FROM Student,Grade WHERE Student.sID = Grade.gID;

结果如下：

+-----+--------+-------+-----+---------+--------+
    | sID | sName  | sDept | gID | gCourse | gScore |
    +-----+--------+-------+-----+---------+--------+
    |   1 | Paul   | CS    |   1 | Math    |     87 |
    |   2 | Oliver | MS    |   2 | English |     95 |
    |   3 | Jack   | SE    |   3 | Physics |     76 |
    +-----+--------+-------+-----+---------+--------+

可以看出，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

## 1.2 外连接 ##

与内连接不同的是，外连接返回的查询结果集中不仅包含符合连接条件的行，而且还包括左表（左连接）、右表（右连接）或两个表（全外连接）中的所有数据行。

### 1.2.1 左连接 ###

**LEFT JOIN（左连接）**，即`LEFT OUTER JOIN`，返回左表的全部记录，即使右表中没有对应匹配记录。

SELECT * FROM Student LEFT JOIN Grade ON Student.sID = Grade.gID;

结果如下：

+-----+--------+-------+------+---------+--------+
    | sID | sName  | sDept | gID  | gCourse | gScore |
    +-----+--------+-------+------+---------+--------+
    |   1 | Paul   | CS    |    1 | Math    |     87 |
    |   2 | Oliver | MS    |    2 | English |     95 |
    |   3 | Jack   | SE    |    3 | Physics |     76 |
    |   4 | Robin  | CS    | NULL | NULL    |   NULL |
    +-----+--------+-------+------+---------+--------+

### 1.2.2 右连接 ###

**RIGHT JOIN（右连接）**，即`RIGHT OUTER JOIN`，返回右表的全部记录，即使左表中没有对应匹配记录。

SELECT * FROM Student RIGHT JOIN Grade ON Student.sID = Grade.gID;

结果如下：

+------+--------+-------+-----+------------+--------+
    | sID  | sName  | sDept | gID | gCourse    | gScore |
    +------+--------+-------+-----+------------+--------+
    |    1 | Paul   | CS    |   1 | Math       |     87 |
    |    2 | Oliver | MS    |   2 | English    |     95 |
    |    3 | Jack   | SE    |   3 | Physics    |     76 |
    | NULL | NULL   | NULL  |   7 | Philosophy |     76 |
    +------+--------+-------+-----+------------+--------+

### 1.2.3 全连接 ###

**FULL JOIN（全连接）**，即`FULL OUTER JOIN`，返回左表、右表的全部记录，即使没有对应的匹配记录。

\*\*注意：\*\*MySQL不支持`FULL JOIN`，不过可以通过`UNION`关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟。

SELECT * FROM Student LEFT JOIN Grade ON Student.sID = Grade.gID
    UNION
    SELECT * FROM Student RIGHT JOIN Grade ON Student.sID = Grade.gID;

结果如下：

+------+--------+-------+------+------------+--------+
    | sID  | sName  | sDept | gID  | gCourse    | gScore |
    +------+--------+-------+------+------------+--------+
    |    1 | Paul   | CS    |    1 | Math       |     87 |
    |    2 | Oliver | MS    |    2 | English    |     95 |
    |    3 | Jack   | SE    |    3 | Physics    |     76 |
    |    4 | Robin  | CS    | NULL | NULL       |   NULL |
    | NULL | NULL   | NULL  |    7 | Philosophy |     76 |
    +------+--------+-------+------+------------+--------+

> 另外，如果在一个连接查询中涉及到的两个表是同一个表，这种查询称为**自连接查询**。为了防止产生二义性，自连接查询中通常对表使用别名。

# 二、子查询 #

子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件。

子查询中常用的操作符有`ANY`、`SOME`、`ALL`、`EXISTS`、`IN`，也可以使用比较运算符。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。

## 2.1 在条件表达式中产生标量的子查询 ##

SELECT *
    FROM score
    WHERE id = (SELECT event_id
                FROM event
                WHERE date='2015-07-01'
                AND type='Q');

所谓**标量**，就是单个属性的一个原子值。当子查询出现在 WHERE 子句中的**比较运算符**（= ,>, >= ,< , <= ,<>）的右边，其输出结果应该只有一个才对。很容易理解，如果返回多条结果，就无法进行比较，系统就会报错。

又如：

SELECT * FROM teacher WHERE birth = MIN(birth);  /*错误*/

这个查询是错的！因为MySQL不允许在子句里面使用统计函数，所以改用子查询：

SELECT *
    FROM teacher
    WHERE birth = (SELECT MIN(birth)
                   FROM teacher);

## 2.2 在条件表达式中产生集合的子查询 ##

如果子查询的输出是一个**结果集合**，可以通过 ANY、ALL、IN 进行比较。

### 2.2.1 ANY与SOME ###

`ANY`和`SOME`关键字是同义词，表示满足其中任一条件。它们允许创建一个表达式对子查询的返回结果集进行比较：

SELECT num1
    FROM t1
    WHERE num1 > ANY(SELECT num2 
                     FROM t2);

上面的子查询返回 t2 的 num2 列，然后将 t1 中的 num1 值与之进行比较，只要大于 num2 的任何一个值，即为符合查询条件的结果。

等价于：

SELECT num1
    FROM t1
    WHERE num1 > SOME(SELECT num2 
                      FROM t2);

### 2.2.2 ALL ###

与`ANY/SOME`不同，使用`ALL`时需要同时满足所有内层查询的条件。

SELECT num1
    FROM t1
    WHERE num1 > ALL(SELECT num2 
                     FROM t2);

上面的子查询还是返回 t2 的 num2 列，然后将 t1 中的 num1 值与之进行比较。但是只有大于所有 num2 值的 num1 才是符合查询条件的结果。

### 2.2.3 IN ###

`IN`关键字后接一个子查询，若在子查询结果集中，返回true，否则返回false。与之相对的是`NOT IN`。

SELECT num1
    FROM t1
    WHERE num1 IN (SELECT num2 
                   FROM t2);

## 2.3 在条件表达式中测试空/非空的子查询 ##

**`EXISTS`**关键字后接一个任意的子查询，系统对子查询进行运算以判断它是否返回行。

*  若至少返回一行，那么 EXISTS 的结果为 true，此时外层查询语句将进行查询；
 *  若没有返回任何行，那么 EXISTS 的结果为 false，此时外层语句将不进行查询。

SELECT sName
    FROM Student
    WHERE EXISTS (SELECT * 
                  FROM Grade 
                  WHERE gScore < 60);

`EXISTS`和`NOT EXISTS`的结果只取决于是否会返回行，而不取决于这些行的内容。

## 2.4 关联子查询 ##

一般的子查询只计算一次，其结果用于外层查询。但关联子查询需要计算多次。

子查询中使用了主查询中的某些字段，主查询每扫描一行都要执行一次子查询，这种子查询称为**关联子查询（Correlated Subquery）**。

SELECT sName
    FROM Student
    WHERE '450' NOT IN (SELECT courseID
                        FROM Course
                        WHERE sID = Student.sID);

上面的子查询中使用了 Student 表的 sID 字段。对于 Student 表中每一个 sID 都会执行一次子查询。

## 2.5 FROM子句中的子查询 ##

子查询可以用括号括起来作为一个关系，从而出现在 FROM 列表中。由于子查询的结果关系没有正式的名字，故必须给它取一个别名。

SELECT *
    FROM Grade,
        (SELECT * FROM Student WHERE sDept='CS')x
    WHERE x.sID=Grade.gID;

x 就是子查询的结果关系的别名。

# 三、合并查询结果 #

利用`UNION`或`UNION ALL`关键字，可以将多个 SELECT 语句的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。

*  **UNION**：合并查询结果时，删除重复的记录，返回的行都是唯一的。
 *  **UNION ALL**：合并查询结果时，不删除重复行。

## 3.1 UNION ALL ##

SELECT * FROM Student 
    UNION ALL
    SELECT * FROM Student;

结果如下：

+-----+--------+-------+
    | sID | sName  | sDept |
    +-----+--------+-------+
    |   1 | Paul   | CS    |
    |   2 | Oliver | MS    |
    |   3 | Jack   | SE    |
    |   4 | Robin  | CS    |
    |   1 | Paul   | CS    |
    |   2 | Oliver | MS    |
    |   3 | Jack   | SE    |
    |   4 | Robin  | CS    |
    +-----+--------+-------+

## 3.2 UNION ##

SELECT * FROM Student 
    UNION
    SELECT * FROM Student;

结果如下：

+-----+--------+-------+
    | sID | sName  | sDept |
    +-----+--------+-------+
    |   1 | Paul   | CS    |
    |   2 | Oliver | MS    |
    |   3 | Jack   | SE    |
    |   4 | Robin  | CS    |
    +-----+--------+-------+

个人站点：[http://songlee24.github.com][http_songlee24.github.com]

[http_songlee24.github.com]: http://songlee24.github.io/