MySql数据库查询(DQL)语言—子查询

什么是子查询

出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询。

子查询分类

一、按子查询出现的位置分类

1. select后面：仅支持标量子查询。
2. from后面：支持表子查询。
3. where或having后面：支持标量子查询、列子查询、行子查询。
4. exists(相关子查询)后面：支持表子查询。

二、按结果集的行列数不同分类

1. 标量子查询（结果集只有一行一列）
2. 行子查询（结果集只有一行多列）
3. 列子查询（结果集只有一列多行）
4. 表子查询（结果集一般为多行多列 ）

子查询在where或having后面使用

一、在where或having后面使用特点

1. 支持标量子查询、列子查询、行子查询

2. 特点：
   1.子查询放在小括号内；
   2.子查询一般放在条件的右侧；
   3.标量子查询一般搭配着单行操作符使用>，< ，>= ，<=，=，<>；
   4.列子查询一般搭配着多行操作符使用，in，any/some，all；
   5.子查询的执行优先于主查询，因为主查询查询条件是子查询的结果集。
   二、在where后面使用标量子查询（单行单列）

3. 谁的工资比 Abel高？
   分析：1.首先查询Abel的工资；2.查询员工的信息，满足salary大于1的结果。
   select * from employees where salary > (select salary from employees where last_name = "Abel");

4. 使用多个子查询
   描述：返回job_id与员工号为141的员工相同，salary比143号员工多的员工姓名，job_id和工资。
   分析：
   第一步：查询job_id与141号员工相同的job_id;
   select job_id from employees where employee_id = 141; // 返回单行单列结果集
   第二步：查询143号员工的salary；
   select salary from employees where employee_id = 143;
   第三步：查询员工的姓名，job_id和工资，要求job_id=第一步结果，salary>第二步结果集；
   select last_name, job_id, salary from employees where job_id = (select job_id from employees where employee_id = 141) and salary > (select salary from employees where employee_id = 143);

5. 结合分组函数
   描述：返回公司工资最少的员工的last_name, job_id和salary。
   分析：
   第一步：查询公司最低工资；
   select min(salary) from employees;
   第二步：查询员工salary = 第一步的信息；
   select last_name, job_id,salary from employees where salary = (select min(salary) from employees);

二、在having后面使用标量子查询（单行单列）

1. 查询最低工资大于50号部门最低工资的部门id和其最低工资。
   分析：
   第一步：查询50号部门的最低工资；
   select min(salary) from employees where department_id = 50;
   第二步：查询每个部门的最低工资。
   select department_id,min(salary) from employees group by department_id;
   第三步：从第二步结果集中筛选出min(salary) > 第一步结果集；
   select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50);

三、非法使用标量子查询

1. 例如>，< ，>= ，<=，=，<>操作符后面跟的是标量子查询，也就是单行单列，如果跟多行或多列，则会出错。
   错误示例：select department_id,min(salary) from employees group by department_id having min(salary) > (select salary from employees where department_id = 50);
   分析：select salary from employees where department_id = 50;子查询这个结果返回的是多行，所以会报错。

四、使用列子查询（多行子查询，一列多行）

1. 返回location_id是1400或1700的部门中的所有员工姓名。
   分析：
   第一步：查询location_id是1400或1700的部门编号；
   select distinct department_id from departments where location_id in (1400,1700); // 返回单列多行
   第二步：查询员工姓名，要求部门号是第一步结果集中的任意一个；
   select last_name, department_id from employees where department_id in (select department_id from departments where location_id in (1400,1700));

2. 返回其工种中比job_id为IT_PROG工种任一工资低的员工的员工号、姓名、job_id和salary。
   分析：
   第一步：查询job_id为IT_PROG部门的工资。
   select distinct salary from employees where job_id ='IT_PROG';
   第二步：查询其他工种工资比第一步中的工资任意低的员工的员工号、姓名、job_id和salary。
   select employee_id, last_name, job_id, salary from employees where salary < any(select distinct salary from employees where job_id ='IT_PROG') and job_id <> "IT_PROG";
   或
   select employee_id, last_name, job_id, salary from employees where salary < (select max(salary) from employees where job_id ='IT_PROG') and job_id <> "IT_PROG";

3. 返回其工种中比job_id为IT_PROG工种所有的工资都低的员工的员工号、姓名、job_id和salary。
   select employee_id, last_name, job_id, salary from employees where salary < all(select salary from employees where job_id ='IT_PROG') and job_id <> "IT_PROG";
   或
   select employee_id, last_name, job_id, salary from employees where salary < (select min(salary) from employees where job_id ='IT_PROG') and job_id <> "IT_PROG";

五、使用行子查询（单行多列）

1. 查询员工编号最小并且工资最高的员工信息。
   分析：
   第一步：查询员工最小编号；
   select min(employee_id) from employees;
   第二步：查询员工最高工资；
   select max(salary) from employees;
   第三步：查询满足前两个条件的员工信息；
   select * from employees where employee_id = (select min(employee_id) from employees) and salary = (select max(salary) from employees);
   使用行子查询写法：
   select * from employees where (employee_id, salary) = (select min(employee_id), max(salary) from employees);

子查询在select后面使用

1. 查询每个部门的员工个数。
   select d.*, (select count(*) from employees e where e.department_id = d.department_id) as 员工个数 from departments d;
2. 总结：select后面使用仅仅支持标量子查询结果，也就是返回的是一行一列，不然报错。

子查询在from后面使用

1. 查询每个部门的平均工资的工资等级。
   分析：将子查询结果充当一张表，要求必须起别名；
   第一步：查询每个部门的平均工资；
   select avg(salary),department_id from employees group by department_id;
   第二步：连接第一步的结果集和job_grades表，筛选条件平均工资在lowest_sal和highest_sal之间；
   select avg_dep.*, g.grade_level from (select avg(salary) as avg_salary,department_id from employees group by department_id) avg_dep inner join job_grades g on avg_dep.avg_salary between g.lowerst_sal and highest_sal;

子查询在exists后面使用(相关子查询)

1. 语法。
   select exists(select employee_id from employees);
   表示子查询的结果是否存在，存在则结果为1，不存在则为0。

2. 查询有员工的部门名。
   select department_name from departments d where exists(select * from employees e where d.department_id = e.department_id);
   或
   select department_name from departments d where d. department_id in (select department_id from employees);

子查询经典案例

一、 查询工资最低的员工名称和工资。

1. 先查询最低工资，结果集为单行单列，标量子查
   select min(salary) from employees;
2. 将第一步最低工资的结果集为查询条件，查询员工信息。
   select last_name, salary from employees where salary = (select min(salary) from employees);

二、 查询平均工资最低的部门信息。

1. 先查询最低平均工资的部门ID。
   select department_id from employees group by department_id order by avg(salary) limit 1;
2. 再查询平均工资等于1的部门信息。
   select * from departments where department_id = (select department_id from employees group by department_id order by avg(salary) limit 1);

三、 查询平均工资最低的部门信息和平均工资。

1. 先查询最低平均工资的部门ID和平均工资。
   select avg(salary) ag_sal,department_id from employees group by department_id order by avg(salary) limit 1;
2. 使用表子查询，再把1的结果集当成一张虚拟表，跟部门表连接，将平均工资当成虚拟表的虚拟字段，查询出来。
   select d.*, ag_sal from departments d inner join (select avg(salary) ag_sal, department_id from employees group by department_id order by avg(salary) limit 1) avg_de on d.department_id = avg_de.department_id;

四、 查询平均工资最高的job信息。

1. 先查询最高的平均工资的job_id。
   select job_id from employees group by job_id order by avg(salary) desc limit 1;
2. 再根据1中的结果查询job信息。
   select * from jobs where job_id = (select job_id from employees group by job_id order by avg(salary) desc limit 1);

五、 查询部门平均工资高于公司平均工资的部门有哪些。

1. 先查询部门平均工资。
   select avg(salary), department_id from employees group by department_id;
2. 再查询公司平均工资。
   select avg(salary) from employees;
3. 由1筛选2，查出部门平均工资比公司平均工资高的部门。
   select avg(salary), department_id from employees group by department_id having avg(salary) > (select avg(salary) from employees);

六、 查询出部门中所有manager的详细信息。

1. 先查询所有的manager的manager_id,并去掉重复的。
   select distinct manager_id from employees;
2. 再查询员工id等于1中结果集的信息。
   select * from employees where employee_id in (select distinct manager_id from employees);

七、查询平均工资最高的部门的manager的信息。

1. 先查询平均工资最高的部门编号；
   select department_id from employees group by department_id order by avg(salary) desc limit 1;
2. 再查询部门编号=1结果集的manager的信息；
   select last_name from employees e inner join departments d on d.manager_id = e.employee_id where d.department_id = (select department_id from employees group by department_id order by avg(salary) desc limit 1);