13.2.13 子查询

发表于 更新于 分类于 ， ， Waline： 本文字数： 6.6k 阅读时长 ≈ 6 分钟

13.2.13 子查询

下面是本文用到的测试数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50508
Source Host           : localhost:3306
Source Database       : select_test

Target Server Type    : MYSQL
Target Server Version : 50508
File Encoding         : 65001

Date: 2019-07-02 22:42:04
*/

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `emp_table`;
CREATE TABLE `emp_table` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(255) DEFAULT NULL,
  `manager_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`emp_id`),
  KEY `manager_id` (`manager_id`),
  CONSTRAINT `emp_table_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `emp_table` (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `emp_table` VALUES ('1', '唐僧', null);
INSERT INTO `emp_table` VALUES ('2', '孙悟空', '1');
INSERT INTO `emp_table` VALUES ('3', '猪八戒', '1');
INSERT INTO `emp_table` VALUES ('4', '沙僧', '1');

DROP TABLE IF EXISTS `student_table`;
CREATE TABLE `student_table` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `java_teacher` (`java_teacher`),
  CONSTRAINT `student_table_ibfk_1` FOREIGN KEY (`java_teacher`) REFERENCES `teacher_table` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `student_table` VALUES ('1', '张三', '1');
INSERT INTO `student_table` VALUES ('2', '张三', '1');
INSERT INTO `student_table` VALUES ('3', '李四', '1');
INSERT INTO `student_table` VALUES ('4', '王五', '2');
INSERT INTO `student_table` VALUES ('5', '_王五', '2');
INSERT INTO `student_table` VALUES ('6', null, '2');
INSERT INTO `student_table` VALUES ('7', '赵六', '3');

DROP TABLE IF EXISTS `teacher_table`;
CREATE TABLE `teacher_table` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `teacher_table` VALUES ('1', 'Yeeku');
INSERT INTO `teacher_table` VALUES ('2', 'Leegang');
INSERT INTO `teacher_table` VALUES ('3', 'Martine');

子查询就是指在査询语句中嵌套另一个査询,子査询可以支持多层嵌套。对于一个普通的查询语句而言,子查询可以出现在两个位置:

  • 出现在from语句后,此时子查询的结果可以看成一个数据表,这种用法也被称为行内视图,因为该子查询的实质就是一个临时视图。
  • 出现在where条件后作为过滤条件的值。
    使用子查询时要注意如下几点。
  • 子查询要用括号括起来。
  • 把子查询当成数据表时(出现在from之后),可以为该子查询起别名,尤其是作为前缀来限定数据列时,必须给子查询起别名。

对于把子查询当做数据表来用,只是把之前的表名变成子查询(也可为子查询起别名),其他部分与普通查询没有任何区别。下面的SQL语句示范了把子查询当成数据表的用法。

1
2
3
select * 
from (select * from student_table) t
where t.java_teacher>1;

运行效果:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * 
from (select * from student_table) t
where t.java_teacher>1;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
4 rows in set

把子查询当成数据表的用法更准确地说是当成视图,可以把上面的SQL语句理解成在执行查询时创建了一个临时视图,该视图名为t,所以这种临时创建的视图也被称为行内视图。
还有一种情形:把子查询当成where条件中的值,如果子查询返回单行、单列值,则被当成一个标量值使用,也就可以使用单行记录比较运算符。例如如下SQL语句:

1
2
3
4
5
6
select * from student_table
where java_teacher > 
(
    select teacher_id from teacher_table
    where teacher_name = 'Yeeku'
);

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

mysql> select * from student_table
where java_teacher > 
(
    select teacher_id from teacher_table
    where teacher_name = 'Yeeku'
);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
4 rows in set

mysql>

上面查询语句中的子査询将返回一个单行、单列的值(该值就是1),如果把上面查询语句的括号部分换成1,那么这条语句就再简单不过了,相同功能的查询结果如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from student_table
where java_teacher > 1;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
4 rows in set

mysql>

放在Where子句的子查询会把单行,单列的返回值当成标量处理.

如果子査询返回多个值,则需要使用in、any和all等关键字,in可以单独使用,与前面介绍比较运算符时所讲的in完全一样,此时可以把子查询返回的多个值当成一个值列表。如下SQL语句所示:

1
2
3
4
5
6
select *
from student_table
where student_id in
(
    select teacher_id from teacher_table
);

运行效果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select *
from student_table
where student_id in
(
    select teacher_id from teacher_table
);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
+------------+--------------+--------------+
3 rows in set
mysql>

上面查询语句中的子查询将返回多个值,这多个值将被当成一个值列表,只要student_id与该值列表中的任意一个值相等,就可以选出这条记录。
any和all可以与>、≥、<、<、<>、=等运算符结合使用,与any结合使用分别表示大于、大于等于、小于、小于等于、不等于、等于其中任意一个值;
与叫all结合使用分别表示大于、大于等于、小于、小于等于、不等于、等于全部值。从上面介绍中可以看出,=any的作用与in的作用相同。如下SQL语句使用=any来代替上面的in:

1
2
3
4
5
select * from student_table
where student_id = any
(
    select teacher_id from teacher_table
);

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from student_table
where student_id = any
(
    select teacher_id from teacher_table
);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
+------------+--------------+--------------+
3 rows in set
  • <ANY只要小于值列表中的最大值即可,
  • >ANY只要大于值列表中的最小值即可。
  • <all要求小于值列表中的最小值,
  • >all要求大于值列表中的最大值
    下面的SQL语句选出student_table表中student_id大于teacher_table表中所有teacher_id的记录。
    1
    2
    3
    4
    5
    6
    select *
    from student_table
    where student_id >
    all (
     select teacher_id from teacher_table
    );
    还有一种子查询可以返回多行、多列,此时where子句中应该有对应的数据列,并使用圆括号将多个数据列组合起来。如下SQL语句所示:
    1
    2
    3
    select * from student_table
    where (student_id,student_name) = any
    (select teacher_id,teacher_name from teacher_table);