MySQL数据库（22）：子查询 sub query
【摘要】
子查询 sub query
1、基本概念
1.1、子查询
嵌套查询下层的程序模块，当一个查询是另一个查询的条件时，称之为子查询
一条select语句中，嵌入了另一条select语句
1.2、主查...
子查询 sub query
1、基本概念
1.1、子查询
嵌套查询下层的程序模块，当一个查询是另一个查询的条件时，称之为子查询
一条select语句中，嵌入了另一条select语句
1.2、主查询
主要的查询对象，第一条select语句，确定所获取的数据目标（数据源）
1.3、子查询和主查询的关系
- 子查询是嵌入到主查询中的
- 子查询辅助主查询，要么作为条件，要么作为数据源
- 子查询可以独立存在，是一条完整的select语句
1.4、子查询的分类
1、按功能分
- 标量子查询：子查询返回的结果是一个数据（一行一列）
- 列子查询：返回一列（一列多行）
- 行子查询：返回一行（一行多列）
- 表子查询：返回多行多列
- exists子查询 返回1或者0（类似布尔操作）
2、按位置分
- where子查询
- from子查询
2、标量子查询
查询结果是一个数据（一行一列）
2.1、基本语法
-- 子查询得到的结果只有一个值
select * from 数据源 where 条件判断 =/<> (select 字段名 form 数据源 where 条件判断);
- 1
- 2
2.2、示例
-- 知道学生的id，查询所在班级名字
-- 主查询：班级，子查询：班级id
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
mysql> select * from my_class;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 3 | 三班 |
| 2 | 二班 |
+----+--------+
select class_id from my_student where id = 1;
+----------+
| class_id |
+----------+
| 1 |
+----------+
select * from my_class where id = (
select class_id from my_student where id = 1
);
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
+----+--------+
- 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
3、列子查询
列子查询得到的结果是一列数据，一列多行
3.1、基本语法
主查询 where 条件 in (列子查询)
- 1
3.2、示例
-- 获取有学生的班级名字
-- 1、找到学生表中的所有班级id
-- 2、找出班级表中对应的名字
select distinct(class_id) from my_student;
+----------+
| class_id |
+----------+
| 1 |
| 2 |
+----------+
select name from my_class where id in (
select distinct(class_id) from my_student
);
+--------+
| name |
+--------+
| 一班 |
| 二班 |
+--------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
4、行子查询
行子查询返回的结果是一行多列
- 字段元素：一个字段对应的值
- 行元素：多个字段合起来作为一个元素参与运算
4.1、基本语法
主查询 where 条件 [(构造一个行元素)] = (行子查询);
- 1
4.2、示例
获取班级年龄最大，且班级号最大的学生
1、求年龄最大
2、求班级号最大
3、求出学生
-- 错误示例
select * from my_student having age = max(age) and class_id = max(class_id);
-- 1、having在group by之后，代表group by执行了一次，聚合函数使用
-- 2、group by 一旦执行，结果就是只返回一行记录，第一行
select max(age), max(class_id) from my_student;
+----------+---------------+
| max(age) | max(class_id) |
+----------+---------------+
| 22 | 2 |
+----------+---------------+
select * from my_student where (age, class_id) = (
select max(age), max(class_id) from my_student
);
Empty set (0.01 sec)
select * from my_student where (age, class_id) = (
select max(age), min(class_id) from my_student
);
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 5 | 关羽 | 1 | 22 | 2 |
+----+--------+----------+------+--------+
- 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
总结：
标量子查询、列子查询、行子查询都属于where子查询
5、表子查询
表子查询返回结果是多行多列，与行子查询相似
行子查询需要行元素，表子查询没有
- 行子查询用于where条件判断，属于where子查询
- 表子查询用于from数据源，属于from子查询
5.1、基本语法
select 字段表 from (表子查询) as 别名 [where] [group by] [having] [order by] [limit]
- 1
5.2、示例
获取每个班级年龄最大的学生
-- 错误示例
select * from my_student group by class_id having age = max(age);
将每个班年龄最大的学生排在最前面 order by
针对结果进行group by 保留每组第一条数据
--
select * from (
select * from my_student order by age desc
) as t group by t.class_id;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 3 | 王五 | 2 | 20 | 2 |
+----+--------+----------+------+--------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
6、exists子查询
返回结果只有0或者1，1代表成立，0代表不成立
6.1、基本语法
where exists (查询语句)
-- 永远为真
where 1;
- 1
- 2
- 3
- 4
6.2、示例
-- 查询有学生的所有班级
select * from my_class as c where exists (
select id from my_student as s where s.class_id = c.id
);
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 2 | 二班 |
+----+--------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
7、子查询中的特定关键字
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
mysql> select id from my_class;
+----+
| id |
+----+
| 1 |
| 3 |
| 2 |
+----+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
7.1、in
主查询 where 条件 in (列子查询)
select * from my_student where class_id in (select id from my_class);
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
7.2、any
-- 查询结果中有任意一个匹配即可，等价于in
主查询 where 条件 any (列子查询)
select * from my_student where class_id = any (select id from my_class);
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
-- 不等于任意一个
主查询 where 条件 any <> (列子查询)
select * from my_student where class_id <> any (select id from my_class);
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
- 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
7.3、some
与any完全一样
7.4、all
-- 等于其中所有
=all(列子查询)
select * from my_student where class_id = all (select id from my_class);
Empty set (0.00 sec)
select * from my_class where id = all (select class_id from my_student);
Empty set (0.00 sec)
-- 不等于其中所有
<>all(列子查询)
select * from my_student where class_id <> all (select id from my_class);
Empty set (0.00 sec)
select * from my_class where id <> all (select class_id from my_student);
+----+--------+
| id | name |
+----+--------+
| 3 | 三班 |
+----+--------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
7.5、值为null
如果值为null,不参与匹配
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | NULL | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
mysql> select * from my_student where class_id = any (select id from my_class);
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
mysql> select * from my_student where class_id <> any (select id from my_class);
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
- 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
文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。
原文链接：pengshiyu.blog.csdn.net/article/details/125156301
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）