MySQL入门系列：查询简介（五）之子查询

叁歲伎倆 2022-01-19 03:01 334阅读 0赞

### 使用子查询 ###

#### 多表查询的需求 ####

上边介绍的查询语句都是作用于单个表的，但是有时候会有从多个表中查询数据的需求，比如我们想查一下名叫`'杜琦燕'`的学生的各科成绩该怎么办呢？我们只能先从`student_info`表中根据名称找到对应的学生学号，然后再通过学号到`student_score`表中找着对应的成绩信息，所以我们只能写成这样的两个查询：

mysql> SELECT number FROM student_info WHERE name = '杜琦燕';
    +----------+
    | number   |
    +----------+
    | 20180102 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> SELECT * FROM student_score WHERE number = 20180102;
    +----------+-----------------------------+-------+
    | number   | subject                     | score |
    +----------+-----------------------------+-------+
    | 20180102 | 母猪的产后护理              |   100 |
    | 20180102 | 论萨达姆的战争准备          |    98 |
    +----------+-----------------------------+-------+
    2 rows in set (0.00 sec)
    
    mysql>
    复制代码

#### 标量子查询 ####

我们回过头再仔细看看这两条查询语句，第二条查询语句的搜索条件其实是用到了第一条查询语句的查询结果，所以为了方便，我们可以把它们合并到一条语句中，就像这样：

mysql> SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');
    +----------+-----------------------------+-------+
    | number   | subject                     | score |
    +----------+-----------------------------+-------+
    | 20180102 | 母猪的产后护理              |   100 |
    | 20180102 | 论萨达姆的战争准备          |    98 |
    +----------+-----------------------------+-------+
    2 rows in set (0.01 sec)
    
    mysql>
    复制代码

我们把第二条查询语句用小括号`()`扩起来直接放到了第一条的搜索条件处，这样就起到了合并两条查询语句的作用。小括号中的查询语句也被称为`子查询`或者`内层查询`，使用`内层查询`的结果作为搜索条件的查询称为`外层查询`。如果你在一个查询语句中需要用到更多的表的话，那么在一个子查询中可以继续嵌套另一个子查询，在执行查询时，将按照从内到外的顺序依次执行这些查询。

在这个例子中的子查询的结果只有一个值(也就是`'杜琦燕'`的学号)，这种子查询称之为`标量子查询`。正因为`标量子查询`单纯的代表一个值，所以它可以作为表达式的操作数来参与运算，它除了用在外层查询的搜索条件以外，也可以作为查询对象放在查询列表处，比如这样：

mysql> SELECT (SELECT number FROM student_info WHERE name = '杜琦燕') AS 学号;
    +----------+
    | 学号     |
    +----------+
    | 20180102 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql>
    复制代码

因为`标量子查询`单纯的代表一个值，所以外层查询的搜索条件想怎么写就怎么写喽，只要符合布尔表达式的语法就可以，比方说我们来查询学号大于`'杜琦燕'`的学号的学生成绩，可以这么写：

mysql> SELECT * FROM student_score WHERE number > (SELECT number FROM student_info WHERE name = '杜琦燕');
    +----------+-----------------------------+-------+
    | number   | subject                     | score |
    +----------+-----------------------------+-------+
    | 20180103 | 母猪的产后护理              |    59 |
    | 20180103 | 论萨达姆的战争准备          |    61 |
    | 20180104 | 母猪的产后护理              |    55 |
    | 20180104 | 论萨达姆的战争准备          |    46 |
    +----------+-----------------------------+-------+
    4 rows in set (0.00 sec)
    
    mysql>
    复制代码

这样查出来的成绩记录中的学号都是大于`'杜琦燕'`的学号。

#### IN和NOT IN子查询 ####

有时候子查询的结果并不是单纯的一个值，而是多个值，比如我们想查询`'计算机科学与工程'`专业的学生的成绩，那我们可以拆成下边两个查询：

mysql> SELECT number FROM student_info WHERE major = '计算机科学与工程';
    +----------+
    | number   |
    +----------+
    | 20180101 |
    | 20180102 |
    +----------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT * FROM student_score WHERE number IN (20180101, 20180102);
    +----------+-----------------------------+-------+
    | number   | subject                     | score |
    +----------+-----------------------------+-------+
    | 20180101 | 母猪的产后护理              |    78 |
    | 20180101 | 论萨达姆的战争准备          |    88 |
    | 20180102 | 母猪的产后护理              |   100 |
    | 20180102 | 论萨达姆的战争准备          |    98 |
    +----------+-----------------------------+-------+
    4 rows in set (0.00 sec)
    
    mysql>
    复制代码

很显然对于子查询结果是多个值的情况，需要使用`IN`这种多值匹配操作符，所以我们可以把上边两个语句合并成这样：

mysql> SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');
    +----------+-----------------------------+-------+
    | number   | subject                     | score |
    +----------+-----------------------------+-------+
    | 20180101 | 母猪的产后护理              |    78 |
    | 20180101 | 论萨达姆的战争准备          |    88 |
    | 20180102 | 母猪的产后护理              |   100 |
    | 20180102 | 论萨达姆的战争准备          |    98 |
    +----------+-----------------------------+-------+
    4 rows in set (0.00 sec)
    
    mysql>
    复制代码

`NOT IN`和`IN`的使用差不多，就不赘述了。

##### 多列子查询 #####

上边例子中出现的子查询的结果集里只有一个列，其实子查询的结果集也可以有多个列的，只不过在子查询的查询列表和外层查询`WHERE`子句的搜索条件要匹配！比如我们可以这么写：

mysql> SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '母猪的产后护理' FROM student_info WHERE major = '计算机科学与工程');
    +----------+-----------------------+-------+
    | number   | subject               | score |
    +----------+-----------------------+-------+
    | 20180101 | 母猪的产后护理        |    78 |
    | 20180102 | 母猪的产后护理        |   100 |
    +----------+-----------------------+-------+
    2 rows in set (0.00 sec)
    
    mysql>
    复制代码

在这个例子中的子查询的查询列表是`number, '母猪的产后护理'`，`number`是列名，`'母猪的产后护理'`是一个常数，所以在外层查询的`WHERE`子句的搜索条件里也需要两个表达式，例子中使用的是\`\`(number, subject)\`。需要注意的是，WHERE子句的搜索条件中多个表达式需要用小括号()括住。

#### EXISTS和NOT EXISTS子查询 ####

有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果是不是为空，这时可以用到下边这两个操作符：

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">操作符</th> 
   <th style="text-align:center;">示例</th> 
   <th style="text-align:center;">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;"><code>EXISTS</code></td> 
   <td style="text-align:center;"><code>EXISTS (SELECT ...)</code></td> 
   <td style="text-align:center;">当子查询有结果时表达式为真</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;"><code>NOT EXISTS</code></td> 
   <td style="text-align:center;"><code>NOT EXISTS (SELECT ...)</code></td> 
   <td style="text-align:center;">当子查询没有结果时表达式为真</td> 
  </tr> 
 </tbody> 
</table>

我们来举个例子：

mysql> SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 20180108);
    Empty set (0.00 sec)
    
    mysql>
    复制代码

这个例子的子查询的意思是在`student_info`表中查找学号为`20180108`的学生信息，很显然并没有学号为`20180108`的学生，所以子查询没有结果，`EXISTS`表达式的结果为`FALSE`，所以外层查询也就不查了，直接返回了一个`Empty set`，表示没有结果。你可以自己试一下`NOT EXISTS`的使用。

#### 相关子查询 ####

前边介绍的子查询和外层查询都没有依赖关系，也就是说可以独立运行完子查询得到结果之后，再拿结果作为外层查询的搜索条件去执行外层查询，这种子查询称为`不相关子查询`，比如下边这个查询：

子查询中只用到了`student_info`表而没有使用到`student_score`表，这就是一种典型的`不相关子查询`。

而有时候我们需要在子查询的语句中引用到外层查询的值，这样的话子查询就不能当作一个独立的语句去执行，这种子查询方式称为`相关子查询`。比方说我们想查看一些学生的基本信息，但是前提是这些学生有成绩记录，那可以这么写：

mysql> SELECT number, name, id_number, major FROM student_info WHERE EXISTS (SELECT * FROM student_score WHERE student_score.number = student_info.number);
    +----------+-----------+--------------------+--------------------------+
    | number   | name      | id_number          | major                    |
    +----------+-----------+--------------------+--------------------------+
    | 20180101 | 杜子腾    | 158177199901044792 | 计算机科学与工程         |
    | 20180102 | 杜琦燕    | 151008199801178529 | 计算机科学与工程         |
    | 20180103 | 范统      | 17156319980116959X | 软件工程                 |
    | 20180104 | 史珍香    | 141992199701078600 | 软件工程                 |
    +----------+-----------+--------------------+--------------------------+
    4 rows in set (0.00 sec)
    
    mysql>
    复制代码

首先需要注意的是，`student_info`和`student_score`表里都有`number`列，所以在子查询的`WHERE`语句中书写`number = number`会造成二义性，也就是让服务器懵逼，不知道这个`number`列到底是哪个表的，所以为了区分，在列名前边加上了表名，并用点`.`连接起来，这种显式的将列所属的表名书写出来的名称称为该列的`全限定名`。所以上边子查询的`WHERE`语句中用了列的全限定名：`student_score.number = student_info.number`。

这条查询语句可以分成这么两部分来理解

*  我们要查询学生的一些基本信息。
 *  这些学生必须符合这样的条件：`必须在student_score表中有记录`。

所以这个例子中的`相关子查询`的查询过程是这样的：

1.  先执行外层查询获得到`student_info`表的第一条记录，发现它的`number`值是`20180101`。把`20180101`当作参数传入到它的子查询，此时子查询的使意思判断`student_score`表的`number`字段是否有`20180101`这个值存在，子查询的结果是该值存在，所以整个`EXISTS`表达式的值为`TRUE`，那么`student_info`表的第一条记录可以被加入到结果集。
2.  再执行外层查询获得到`student_info`表的第二条记录，发现它的`number`值是`20180102`，与上边的步骤相同，`student_info`表的第二条记录也可以被加入到结果集。
3.  与上边类似，`student_info`表的第三条记录也可以被加入到结果集。
4.  与上边类似，`student_info`表的第四条记录也可以被加入到结果集。
5.  再执行外层查询获得到`student_info`表的第五条记录，发现它的`number`值是`20180105`，把`20180105`当作参数传入到它的子查询，此时子查询的使意思判断`student_score`表的`number`字段是否有`20180105`这个值存在，子查询的结果是该值不存在，所以整个`EXISTS`表达式的值为`FALSE`，那么`student_info`表的第五条记录就不被加入结果集中。
6.  与上边类似，`student_info`表的第六条记录也不被加入结果集中。
7.  `student_info`表没有更多的记录了，结束查询。

所以最后的查询结果是上边的4条记录。如果你觉得`相关子查询`还是有点儿绕的话，那就返回去再重新看几遍这个查询过程。需要注意的是，相关子查询通常都用在`EXISTS`和`NOT EXISTS`子查询里，用于匹配在某个表里查找在另一个表里有匹配行或者没有匹配行的行。

#### 对同一个表的子查询 ####

其实不只是在查询多表的时候会用到子查询，在单个表中有时也会用到子查询。比方说我们想看看在`student_score`表的`'母猪的产后护理'`这门课的成绩中，有哪些超过了平均分的记录，脑子中第一印象是这么写：

mysql> SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > AVG(score);
    ERROR 1111 (HY000): Invalid use of group function
    mysql>
    复制代码

需要特别特别特别注意的是：聚集函数不能放到WHERE子句中！！！因为`WHERE`子句是针对每一条记录来进行过滤的，而聚集函数只能用于过滤分组。所以如果我们想实现上边的需求，就需要搞一个`student_score`表的副本，就相当于有了两个`student_score`表，在一个表上使用聚集函数统计，统计完了之后拿着统计结果再到另一个表中进行过滤，这个过程可以这么写：

mysql>  SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理');
    +----------+-----------------------+-------+
    | number   | subject               | score |
    +----------+-----------------------+-------+
    | 20180101 | 母猪的产后护理        |    78 |
    | 20180102 | 母猪的产后护理        |   100 |
    +----------+-----------------------+-------+
    2 rows in set (0.01 sec)
    
    mysql>
    复制代码

我们使用子查询先统计出了`'母猪的产后护理'`这门课的平均分，然后再到外层查询中使用这个平均分作为过滤条件来查找大于平均分的记录。这样看上去student\_score表就像是有了一个副本一样～。

#### 子查询注意事项 ####

1.  标量子查询的结果只有一个值，如果结果有多个值的话会报错，可以在查询语句末尾使用`LIMIT 1`来保证只有一条结果。
2.  子查询的查询列表和外层查询WHERE子句的搜索条件要匹配！如果外层查询的`WHERE`子句中的表达式个数大于1个，需要用小括号`()`扩起来。
3.  在引用的列可能出现二义性时，必须使用列的全限定名(也就是用一个点`.`分隔的表名和列名)来注明该列所属的表。
4.  `相关子查询`是涉及外部查询的子查询，一般用在`EXISTS`和`NOT EXISTS`子查询里。

## 小册 ##

本系列专栏都是MySQL入门知识，想看进阶知识可以到小册中查看：[《MySQL是怎样运行的：从根儿上理解MySQL》的链接][MySQL_MySQL] 。小册的内容主要是从小白的角度出发，用比较通俗的语言讲解关于MySQL进阶核的一些核心概念，比如记录、索引、页面、表空间、查询优化、事务和锁等，总共的字数大约是三四十万字，配有上百幅原创插图。主要是想降低普通程序员学习MySQL进阶的难度，让学习曲线更平滑一点～

转载于:https://juejin.im/post/5c886284f265da2db30591b1

[MySQL_MySQL]: https://juejin.im/book/5bffcbc9f265da614b11b731?referrer=5bff96c6e51d45452f2d6f95