说明

子查询就是一个 SQL 查询的结果作为另一个 SQL 查询语句语法的一部分
image-20220418163010381

子查询结果分类

  1. 单行单列—— <、>、=、…
  2. 多行单列—— in、not in
  3. 多行多列—— 将查询结果作为一张表，使用as取别名后关联查询

    测试数据准备

    一个部门有多个员工，一个员工只能属于一个部门，关系由员工维护 ``` — 创建部门表 1 CREATE TABLE dept ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) );

INSERT INTO dept (NAME) VALUES (‘开发部’),(‘市场部’),(‘财务部’);

— 创建员工表 n CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), gender CHAR(1), — 性别 salary DOUBLE, — 工资 join_date DATE, — 入职日期 dept_id INT, foreign key(dept_id) references dept(id) );

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘孙悟空’,’男’,7200,’2013-02-24’,1); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘猪八戒’,’男’,3600,’2010-12-02’,2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘唐僧’,’男’,9000,’2008-08-08’,2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘白骨精’,’女’,5000,’2015-10-07’,3); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘蜘蛛精’,’女’,4500,’2011-03-14’,1);

  1. emp表<br />![image-20220418163515085](https://cdn.nlark.com/yuque/0/2022/png/280648/1650603208759-ae648600-78ff-4a18-a3b4-f0c88f3d1f52.png)<br />dept表<br />![image-20220418163542025](https://cdn.nlark.com/yuque/0/2022/png/280648/1650603208841-572ab2d7-fbfd-45e8-b2eb-7f16442917a5.png)
  2. ## 单行单列
  3. 例子<br />查询工资最高的员工是谁？

select

  • from emp where salary = ( select max(salary) from emp );
效果<br />![image-20220418164034995](https://cdn.nlark.com/yuque/0/2022/png/280648/1650603208914-ee65ca57-ddff-494b-aea6-105645baa42e.png)<br />例子<br />查询工资小于平均工资的员工有哪些

select

  • from emp where salary < ( select avg(salary) from emp );
效果<br />![image-20220418164137413](https://cdn.nlark.com/yuque/0/2022/png/280648/1650603208992-59fb6ee2-0113-4598-a8da-4d58815a438d.png)
## 多行单列
例子<br />查询工资大于5000的员工，来自于哪些部门的名字

select dept.name from dept where id in ( select dept_id from emp where salary > 5000 );

效果<br />![image-20220418164415745](https://cdn.nlark.com/yuque/0/2022/png/280648/1650603209074-30184f56-0ac7-49d0-883b-8c05b6b5fbb0.png)<br />例子<br />查询开发部与财务部所有的员工信息

select

  • from emp where dept_id in ( select id from dept where name in(‘开发部’, ‘财务部’) );
效果<br />![image-20220418164536463](https://cdn.nlark.com/yuque/0/2022/png/280648/1650603209153-399d93ba-3d5e-4184-9f8a-93c273847a65.png)
## 多行多列
例子<br />查询出2011年以后入职的员工信息，包括部门名称

select my_emp., dept.name from dept, ( select emp. from emp where join_date > ‘2011-01-01’ ) as my_emp where dept.id = my_emp.dept_id;

``` 效果
image-20220418165242388