第11章、子查询

标签： MySQL是怎样使用的新版

多表查询的需求

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

  1. mysql> SELECT number FROM student_info WHERE name = '杜琦燕';
  2. +----------+
  3. | number   |
  4. +----------+
  5. | 20180102 |
  6. +----------+
  7. 1 row in set (0.00 sec)
  9. mysql> SELECT * FROM student_score WHERE number = 20180102;
  10. +----------+-----------------------------+-------+
  11. | number   | subject                     | score |
  12. +----------+-----------------------------+-------+
  13. | 20180102 | 母猪的产后护理              |   100 |
  14. | 20180102 | 论萨达姆的战争准备          |    98 |
  15. +----------+-----------------------------+-------+
  16. 2 rows in set (0.00 sec)
  18. mysql>

标量子查询

我们回过头再仔细看看上述的两条查询语句，第二条查询语句的搜索条件其实是用到了第一条查询语句的查询结果。为了书写简便，我们可以把这两条语句合并到一条语句中，从而减少了把第一条查询语句的结果复制粘贴到第二条查询语句中的步骤，就像这样：

  1. mysql> SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');
  2. +----------+-----------------------------+-------+
  3. | number   | subject                     | score |
  4. +----------+-----------------------------+-------+
  5. | 20180102 | 母猪的产后护理              |   100 |
  6. | 20180102 | 论萨达姆的战争准备          |    98 |
  7. +----------+-----------------------------+-------+
  8. 2 rows in set (0.01 sec)
  10. mysql>

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

小贴士： 事实上，所有的子查询都必须用小括号扩起来，否则是非法的。

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

  1. mysql> SELECT (SELECT number FROM student_info WHERE name = '杜琦燕') AS 学号;
  2. +----------+
  3. | 学号     |
  4. +----------+
  5. | 20180102 |
  6. +----------+
  7. 1 row in set (0.00 sec)
  9. mysql>

标量子查询单纯的代表一个值，由标量子查询作为的操作数组成的搜索条件只要符合表达语法就可以。比方说我们来查询学号大于'杜琦燕'的学号的学生成绩，可以这么写：

  1. mysql> SELECT * FROM student_score WHERE number > (SELECT number FROM student_info WHERE name = '杜琦燕');
  2. +----------+-----------------------------+-------+
  3. | number   | subject                     | score |
  4. +----------+-----------------------------+-------+
  5. | 20180103 | 母猪的产后护理              |    59 |
  6. | 20180103 | 论萨达姆的战争准备          |    61 |
  7. | 20180104 | 母猪的产后护理              |    55 |
  8. | 20180104 | 论萨达姆的战争准备          |    46 |
  9. +----------+-----------------------------+-------+
  10. 4 rows in set (0.00 sec)
  12. mysql>

这样结果集的记录中的学号都大于'杜琦燕'的学号。

列子查询

如果我们想查询'计算机科学与工程'专业的学生的成绩，我们需要先从student_info表中根据专业名称找到对应的学生学号，然后再通过学号到student_score表中找着对应的成绩信息，所以这个问题的解决方案就是书写下述两个查询语句：

  1. mysql> SELECT number FROM student_info WHERE major = '计算机科学与工程';
  2. +----------+
  3. | number   |
  4. +----------+
  5. | 20180101 |
  6. | 20180102 |
  7. +----------+
  8. 2 rows in set (0.00 sec)
  10. mysql> SELECT * FROM student_score WHERE number IN (20180101, 20180102);
  11. +----------+-----------------------------+-------+
  12. | number   | subject                     | score |
  13. +----------+-----------------------------+-------+
  14. | 20180101 | 母猪的产后护理              |    78 |
  15. | 20180101 | 论萨达姆的战争准备          |    88 |
  16. | 20180102 | 母猪的产后护理              |   100 |
  17. | 20180102 | 论萨达姆的战争准备          |    98 |
  18. +----------+-----------------------------+-------+
  19. 4 rows in set (0.00 sec)
  21. mysql>

第二条查询语句的搜索条件也是用到了第一条查询语句的查询结果，我们自然可以想到把第一条查询语句作为内层查询，把第二条查询语句作为外层查询来将这两个查询语句合并为一个查询语句，就像这样：

  1. mysql> SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');
  2. +----------+-----------------------------+-------+
  3. | number   | subject                     | score |
  4. +----------+-----------------------------+-------+
  5. | 20180101 | 母猪的产后护理              |    78 |
  6. | 20180101 | 论萨达姆的战争准备          |    88 |
  7. | 20180102 | 母猪的产后护理              |   100 |
  8. | 20180102 | 论萨达姆的战争准备          |    98 |
  9. +----------+-----------------------------+-------+
  10. 4 rows in set (0.00 sec)
  12. mysql>

很显然第一条查询语句的结果集中并不是一个单独的值，而是一个列（本例中第一条查询语句的结果集中该列包含2个值，分别是：20180101和20180102），所以它对应的子查询也被称之为列子查询。因为列子查询得到的结果是多个值，相当于一个列表。我们前边的章节中说过，IN和NOT IN操作符正好是用来匹配列表的，上边使用的例子是使用IN操作符和子查询的结果组成表达式来作为外层查询的搜索条件的。NOT IN和IN的操作符的使用方式是一样的，只不过语义不同罢了，我们就不赘述了。

行子查询

有列子查询，大家肯定就好奇有没有行子查询。哈哈，当然有了，只要子查询的结果集中最多只包含一条记录，而且这条记录中有超过一个列的数据（如果该条记录只包含一个列的话，该子查询就成了标量子查询），那么这个子查询就可以被称之为行子查询，比如这样：

  1. mysql> SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '母猪的产后护理' FROM student_info LIMIT 1);
  2. +----------+-----------------------+-------+
  3. | number   | subject               | score |
  4. +----------+-----------------------+-------+
  5. | 20180104 | 母猪的产后护理        |    55 |
  6. +----------+-----------------------+-------+
  7. 1 row in set (0.01 sec)
  9. mysql>

该子查询的查询列表是number, '母猪的产后护理'，其中number是列名，'母猪的产后护理'是一个常数。我们在子查询语句中加了LIMIT 1这个子句，意味着子查询最多只能返回一条记录，所以该子查询就可以被看作一个行子查询。

小贴士： 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1子句来限制记录数量。

另外，我们之前在唠叨表达式的时候操作数都是单一的一个值，不过由于上述的子查询执行后产生的结果集是一个行（包含2个列），所以用作等值比较的另一个操作数也得是2个值，本例中就是(number, subject)（注意，这两个值必须用小括号()扩住，否则会产生歧义）。它表达的语义就是：先获取到子查询的执行结果，然后再执行外层查询，如果student_score中记录的number等于子查询结果中的number列并且subject列等于子查询结果中的'母猪的产后护理'，那么就将该记录加入到结果集。

表子查询

如果子查询结果集中包含多行多列，那么这个子查询也可以被称之为表子查询，比如这样：

  1. mysql> SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '母猪的产后护理' FROM student_info WHERE major = '计算机科学与工程');
  2. +----------+-----------------------+-------+
  3. | number   | subject               | score |
  4. +----------+-----------------------+-------+
  5. | 20180101 | 母猪的产后护理        |    78 |
  6. | 20180102 | 母猪的产后护理        |   100 |
  7. +----------+-----------------------+-------+
  8. 2 rows in set (0.00 sec)
  10. mysql>

在这个例子中的子查询执行之后的结果集中包含多行多列，所以可以被看作是一个表子查询。

EXISTS和NOT EXISTS子查询

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

操作符 示例 描述
EXISTS EXISTS (SELECT ...) 当子查询结果集不是空集时表达式为真
NOT EXISTS NOT EXISTS (SELECT ...) 当子查询结果集是空集时表达式为真

我们来举个例子：

  1. mysql> SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 20180108);
  2. Empty set (0.00 sec)
  4. mysql>

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

不相关子查询和相关子查询

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

  1. mysql> SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');
  2. +----------+-----------------------------+-------+
  3. | number   | subject                     | score |
  4. +----------+-----------------------------+-------+
  5. | 20180102 | 母猪的产后护理              |   100 |
  6. | 20180102 | 论萨达姆的战争准备          |    98 |
  7. +----------+-----------------------------+-------+
  8. 2 rows in set (0.00 sec)
  10. mysql>

子查询中只用到了student_info表而没有使用到student_score表，它可以单独运行并产生结果，这就是一种典型的不相关子查询。

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

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

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

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

  • 我们要查询学生的一些基本信息。

  • 这些学生必须符合这样的条件：必须有成绩记录保存在student_score表中。

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

  • 先执行外层查询获得到student_info表的第一条记录，发现它的number值是20180101。把20180101当作参数传入到子查询，此时子查询的意思是判断student_score表的number字段是否有20180101这个值存在，子查询的结果是该值存在，所以整个EXISTS表达式的值为TRUE，那么student_info表的第一条记录可以被加入到结果集。

  • 再执行外层查询获得到student_info表的第二条记录，发现它的number值是20180102，与上边的步骤相同，student_info表的第二条记录也可以被加入到结果集。

  • 与上边类似，student_info表的第三条记录也可以被加入到结果集。

  • 与上边类似，student_info表的第四条记录也可以被加入到结果集。

  • 再执行外层查询获得到student_info表的第五条记录，发现它的number值是20180105，把20180105当作参数传入到它的子查询，此时子查询的意思是判断student_score表的number字段是否有20180105这个值存在，子查询的结果是该值不存在，所以整个EXISTS表达式的值为FALSE，那么student_info表的第五条记录就不被加入结果集中。

  • 与上一步骤类似，student_info表的第六条记录也不被加入结果集中。

  • student_info表没有更多的记录了，结束查询。

所以最后的查询结果是上边展示的4条记录。如果你觉得相关子查询还是有点儿绕的话，那就返回去再重新看几遍这个查询的执行过程。

对同一个表的子查询

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

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

很抱歉，报错了。为啥呢？因为聚集函数是用来对分组做数据统计的（如果没有GROUP BY语句那么意味着只有一个分组），而WHERE子句是以记录为单位来执行过滤操作的，在WHERE子句执行完成之后才会得到分组，也就是说：聚集函数不能放到WHERE子句中！！！ 如果我们想实现上边的需求，就需要搞一个student_score表的副本，就相当于有了两个student_score表，在一个表上使用聚集函数统计，统计完了之后拿着统计结果再到另一个表中进行过滤，这个过程可以这么写：

  1. mysql>  SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理');
  2. +----------+-----------------------+-------+
  3. | number   | subject               | score |
  4. +----------+-----------------------+-------+
  5. | 20180101 | 母猪的产后护理        |    78 |
  6. | 20180102 | 母猪的产后护理        |   100 |
  7. +----------+-----------------------+-------+
  8. 2 rows in set (0.01 sec)
  10. mysql>

我们使用子查询先统计出了'母猪的产后护理'这门课的平均分，然后再到外层查询中使用这个平均分组成的表达式来作为搜索条件去查找大于平均分的记录。