08【子查询】

---

上一篇：07【连接查询】

下一篇：09【多表查询案例】

目录：【MySQL零基础系列教程】

---

---

08【子查询】

• 子查询的概念：

1. 一个查询语句结果做为另一个查询语句的条件
2. 查询语句有嵌套，里面的查询称为子查询，外面的查询称为父查询
3. 子查询要使用括号括起来

准备数据：

drop table if exists emp;

-- 创建员工表
create table emp (
	id int primary key auto_increment,
	name varchar(10),
	addr varchar(30), 
	age int, 
	sex char(1), 
	dept_id int,
	foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);

INSERT INTO `emp` VALUES (1, '小明', '南昌', 24, '男', 1);
INSERT INTO `emp` VALUES (2, '小红', '九江', 20, '女', 1);
INSERT INTO `emp` VALUES (3, '小兰', '抚州', 19, '女', 2);
INSERT INTO `emp` VALUES (4, '小龙', '宜春', 18, '男', 2);
INSERT INTO `emp` VALUES (5, '小军', '赣州', 23, '男', 3);
INSERT INTO `emp` VALUES (6, '小聪', '吉安', 23, '男', 3);
INSERT INTO `emp` VALUES (7, '小陈', '上饶', 25, '男', 2);
INSERT INTO `emp` VALUES (8, '小李', '鹰潭', 28, '男', 3);
INSERT INTO `emp` VALUES (9, '小孙', '萍乡', 20, '男', 1);
INSERT INTO `emp` VALUES (10, '小王', '景德镇', 24, '男', 1);
INSERT INTO `emp` VALUES (11, '小赵', '新余', 29, '女', 3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

8.1 查询的结果

• 1）查询是单行单列

select max(age) from emp;
1

• 2）查询是多行单列

select id from emp where dept_id=1;
1

• 3）查询是多行多列的情况

select * from emp where dept_id=1;
1

8.1.1 结果为单行单列

如果子查询的结果是一个值，父查询使用比较运算符：> 、 <、=、<>

select * from 表名 where 字段名 = (子查询);
1

需求：查询年龄最高的员工是谁？

-- 1. 查询年龄大的员工的详细信息
select max(age) from emp;

-- 2. 根据最高年龄到员工表查询到对应的员工信息
select * from emp where age = 26;

-- 使用子查询
select * from emp where age = (select max(age) from emp);
1
2
3
4
5
6
7
8

需求：查询年龄大于"小明"年龄的员工

-- 1. 首先查询小明的年龄是多少
select age from emp where name='小明';

-- 2. 查询大于这个年龄的员工
select * from emp where age > (select age from emp where name='小明');
1
2
3
4
5
6

8.1.2 结果为多行单列

子查询结果是多行单列的时候，子查询的结果相当于一个集合或数组。父查询要使用in/any/all这些关键字

select * from 表名 where 字段名 IN/ANY/ALL(子查询) 
1

需求1：查询年龄大于23的员工的部门信息

-- 1. 首先查询年龄大于23岁的员工所在的部门id
select dept_id from emp where age > 23;

-- 2. 再查询在这些部门id中部门的名字

-- ERROR 1242 (21000): Subquery returns more than 1 row 子查询返回了多行数据
select * from dept where id = (select dept_id from emp where age > 23);

-- 采用in 取结果集中的数据 in (1,2,3)
select * from dept where id in (select dept_id from emp where age > 23);
1
2
3
4
5
6
7
8
9
10

结果如下：

需求2：查询年龄大于1号部门所有员工的人

-- 1. 查询1号部门所有员工的年龄，得到多行单列
select age from emp where dept_id=1;

-- 2. 当结果集返回多行时不能使用比较运算符
-- ERROR 1242 (21000): Subquery returns more than 1 row
select * from emp where age > (select age from emp where dept_id=1);

select * from emp where age > all (select age from emp where dept_id=1);

-- 比1号部门任意一个大就行
select * from emp where age > any (select age from emp where dept_id=1);
1
2
3
4
5
6
7
8
9
10
11

8.1.3 子查询结果为多行多列

查询结果为多行多列的时候，可以当做一张虚拟表

如果子查询的结果是多行多列，父查询可以将这个查询结果做为一个虚拟表，进行第2次查询。不是放在where后面，而是放在from的后面。

select 列名 from 表, (子查询的结果) 别名 where 条件
1

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

需求：查询出年龄大于23岁的员工信息和部门名称

-- 1. 在员工表中查询年龄大于23岁的员工
select * from emp where age > 23;

-- 2.查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
select * from dept d, emp e where d.id = e.dept_id; -- 隐式内连接

select e.*,d.name 部门名称 from dept d, (select * from emp where age > 23) e where d.id = e.dept_id; -- 隐式内连接

-- 也可以使用表连接
select e.*,d.name 部门名称 from dept d, emp e where d.id = e.dept_id and e.age > 23;
1
2
3
4
5
6
7
8
9
10

8.2 子查询小结

1. 单行单列：父查询使用比较运算符(>，<，=)

2. 多行单列：父查询使用关键字：in/any/all，只要是单列的情况放在where后面。

3. 多行多列：父查询放在from后面做为虚拟表并起别名再次进行查询