本文主要是介绍MySql数据库查询(DQL)语言—子查询，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
什么是子查询
出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询。
子查询分类
一、按子查询出现的位置分类
- select后面：仅支持标量子查询。
- from后面：支持表子查询。
- where或having后面：支持标量子查询、列子查询、行子查询。
- exists(相关子查询)后面：支持表子查询。
二、按结果集的行列数不同分类
- 标量子查询（结果集只有一行一列）
- 行子查询（结果集只有一行多列）
- 列子查询（结果集只有一列多行）
- 表子查询（结果集一般为多行多列 ）
子查询在where或having后面使用
一、在where或having后面使用特点
-
支持标量子查询、列子查询、行子查询
-
特点：
1.子查询放在小括号内；
2.子查询一般放在条件的右侧；
3.标量子查询一般搭配着单行操作符使用>，< ，>= ，<=，=，<>；
4.列子查询一般搭配着多行操作符使用，in，any/some，all；
5.子查询的执行优先于主查询，因为主查询查询条件是子查询的结果集。
二、在where后面使用标量子查询（单行单列） -
谁的工资比 Abel高？
分析：1.首先查询Abel的工资；2.查询员工的信息，满足salary大于1的结果。
select * from employees where salary > (select salary from employees where last_name = "Abel"); -
使用多个子查询
描述：返回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); -
结合分组函数
描述：返回公司工资最少的员工的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后面使用标量子查询（单行单列）
- 查询最低工资大于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);
三、非法使用标量子查询
- 例如>，< ，>= ，<=，=，<>操作符后面跟的是标量子查询，也就是单行单列，如果跟多行或多列，则会出错。
错误示例：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;子查询这个结果返回的是多行，所以会报错。
四、使用列子查询（多行子查询，一列多行）
-
返回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)); -
返回其工种中比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"; -
返回其工种中比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";
五、使用行子查询（单行多列）
- 查询员工编号最小并且工资最高的员工信息。
分析：
第一步：查询员工最小编号；
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后面使用
- 查询每个部门的员工个数。
select d.*, (select count(*) from employees e where e.department_id = d.department_id) as 员工个数 from departments d; - 总结：select后面使用仅仅支持标量子查询结果，也就是返回的是一行一列，不然报错。
子查询在from后面使用
- 查询每个部门的平均工资的工资等级。
分析：将子查询结果充当一张表，要求必须起别名；
第一步：查询每个部门的平均工资；
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后面使用(相关子查询)
-
语法。
select exists(select employee_id from employees);
表示子查询的结果是否存在，存在则结果为1，不存在则为0。 -
查询有员工的部门名。
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);
子查询经典案例
一、 查询工资最低的员工名称和工资。
- 先查询最低工资，结果集为单行单列，标量子查
select min(salary) from employees; - 将第一步最低工资的结果集为查询条件，查询员工信息。
select last_name, salary from employees where salary = (select min(salary) from employees);
二、 查询平均工资最低的部门信息。
- 先查询最低平均工资的部门ID。
select department_id from employees group by department_id order by avg(salary) limit 1; - 再查询平均工资等于1的部门信息。
select * from departments where department_id = (select department_id from employees group by department_id order by avg(salary) limit 1);
三、 查询平均工资最低的部门信息和平均工资。
- 先查询最低平均工资的部门ID和平均工资。
select avg(salary) ag_sal,department_id from employees group by department_id order by avg(salary) limit 1; - 使用表子查询，再把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信息。
- 先查询最高的平均工资的job_id。
select job_id from employees group by job_id order by avg(salary) desc limit 1; - 再根据1中的结果查询job信息。
select * from jobs where job_id = (select job_id from employees group by job_id order by avg(salary) desc limit 1);
五、 查询部门平均工资高于公司平均工资的部门有哪些。
- 先查询部门平均工资。
select avg(salary), department_id from employees group by department_id; - 再查询公司平均工资。
select avg(salary) from employees; - 由1筛选2，查出部门平均工资比公司平均工资高的部门。
select avg(salary), department_id from employees group by department_id having avg(salary) > (select avg(salary) from employees);
六、 查询出部门中所有manager的详细信息。
- 先查询所有的manager的manager_id,并去掉重复的。
select distinct manager_id from employees; - 再查询员工id等于1中结果集的信息。
select * from employees where employee_id in (select distinct manager_id from employees);
七、查询平均工资最高的部门的manager的信息。
- 先查询平均工资最高的部门编号；
select department_id from employees group by department_id order by avg(salary) desc limit 1; - 再查询部门编号=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);
这篇关于MySql数据库查询(DQL)语言—子查询的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
