关于嵌套查询(子查询)
1.概述
- 子查询是在一个查询中嵌套另一个查询的查询语句。
- 内部查询从外部查询或数据库中提取数据,然后使用这些数据来执行内部查询。
- 出现在其他语句中的 select 语句,称为嵌套查询或子查询。
- 外部的查询语句,称为主查询或外查询。
2.分类
-
按结果集的行列数分类
- 标量子查询(子查询结果集只有一行一列)
- 列子查询(子查询结果集只有一列多行)
- 行子查询(子查询结果集有一行多列)
- 表子查询(子查询结果集一般为多行多列)
-
按子查询出现的位置分类
- select 后:标量子查询。
- from 后面:表子查询。
- where 或 having 后:标量子查询、行子查询、列子查询、表子查询。
- exists 后
-
按与父查询的关系分类
- 不相关子查询:子查询的条件不依赖于父查询。
- 相关子查询:子查询的条件依赖于父查询。
3.注意
- 子查询一般放在小括号内,并且一般放在条件的右侧。
- 标量子查询,一般搭配着单行操作符使用;列子查询,一般搭配着多行操作符使用。
- 子查询的
select
语句中不能使用order by
子句,因为其只能对最终结果进行排序。
where 或 having 后的子查询
1.标量子查询
-
子查询结果集只有一行一列
-
在
where
子句中过滤数据:使用标量子查询来比较列与单个值之间的关系 -
查询工资比名为Ellen高的员工的名字和工资
-
分步:查询Ellen的工资(子) -> 查询比其工资高的信息(父)
-
select first_name, salary from employees where salary > ( select salary from employees where first_name = 'Ellen' );
-
-
查询工资最少的员工的名字、工种和月薪
-
分步:查询最少工资(子)-> 查询工资等于最少工资的员工信息
-
select first_name, job_id, salary from employees where salary = ( select min(salary) from employees );
2.列子查询
-
子查询结果集只有一列多行
-
放在
where
语句中,用来限制主查询返回的结果集,可以用于比较、过滤和连接数据。 -
返回位置编号是 1400 和 1500 两个部门中的所有员工名字
-
分步:1400 和 1500 两个部门(子) -> 此两部门的员工信息(父)。
-
select first_name, department_id from employees where department_id in( select distinct department_id from departments where location_id in(1400, 1500) );
-
-
查询其它工种中比
it_prog
工种所有工资都低的员工的员工编号、名字、工种和月薪-
分步:it_prog公种所有员工的工资(子) -> 查询满足要求的员工信息()父。
-
select employee_id, first_name, job_id, salary from employees where salary < all( select distinct salary from employees where job_id = 'it_prog' ) and job_id != 'it_prog';
-
3.行子查询
-
子查询结果集有一行多列。
-
查询员工编号最小并且工资最高的员工信息(编号、姓名、工资)
-
分步:最小员工编号与最高工资(子) -> 员工信息(父)。
-
select employee_id, first_name, salary from employees where(employee_id, salary) = ( select min(employee_id), max(salary) from employees );
-
-
查询 employees 的部门编号和管理者编号在 departments 表中的员工名字,部门编号和管理者编号
-
分步:部门编号和管理者编号(子)-> 员工信息(父)
-
select first_name, department_id, manager_id from employees where (department_id, manager_id) in ( select department_id, manager_id from departments );
-
select 和 from 后的子查询
1. select
-
select
后面仅支持标量子查询。 -
查询每个部门信息和该部门员工个数
-
分步:部门员工个数(子)-> 部门信息(父)
-
select d.*, ( select count(*) from employees e where e.department_id = d.department_id ) as num from departments d;
-
-
查询 90 编号的部门员工人数占公司总人数的比例,带百分号,小数点后保留一位。
-
select concat(truncate(( select count(*) from employees where department_id = 90) / ( select count(*) from employees)*100,1), '%') as percent from dual;
-
2. from
-
from
后面支持表子查询。 -
子查询结果作为临时表,要求必须起别名。
-
查询部门编号、该部门的平均工资 average_salary 和工资等级,平均工资去掉小数部分
-
分步:部门编号、该部门的平均工资(子)-> 部门编号、该部门的平均工资和对应的工资等级(父)。
-
select s.*, g.grade_level from ( select department_id, truncate(avg(salary), 0) as average_salary from employees group by department_id ) as s join job_grades as g on s.average_salary between lowest_sal and highest_sal;
-
exit 后的子查询
1.语法
exists(query_statement)
- 返回结果为1或0。
2.示例
-
查询工资大于3000的员工编号
select employee_id from northwind.employees where salary > 30000; select exists( select employee_id from northwind.employees where salary > 30000 ) as 'exist salary > 30000';
-
查询有员工的部门名
select department_name from departments as d where exists ( select * from employees as e where d.department_id = e.department_id );
沉住气别惧怕黑暗,因为光亮就在不远。 ——痛仰《当太阳照常升起》