子查询
文章目录
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类
按子查询出现的位置:
select后面:
只支持标量子查询
from后面:
支持表子查询
where或having后面:※
>标量子查询(单行)√
>
>列子查询(多行)√
>
>行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
一. where或having后面
1.标量子查询(单行子查询)
2.列子查询(多行子查询)
3.行子查询(多列多行)
特点:①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配着单行操作符使用
<> <= >= = <>
列子查询,一般搭配着多行操作符使用
in,any,some,all
④子查询的执行优先于主查询的执行,主查询的条件用到了子查询的结果
案例1:谁的工资被Abel高?
①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name=“Abel”;
②查询员工的信息,满足salary>①结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name=“Abel”
);
案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,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
);
案例3:返回公司工资最少的员工的last_name,job_id和salary
①查询公司的最低工资
SELECT MIN(salary)
FROM employees
②查询last_name,job_id和salary,要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
)
案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50
②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
③筛选②,满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
)
非法使用标量子查询
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id=50
)
里面需要的是单个,但出现多个,sql语法报错
[Err] 1242 - Subquery returns more than 1 row
多行子查询
返回多行,使用多行比较操作符
| 操作符 | 含义 |
|---|---|
| IN/NOT IN | 等于列表中任意一个 |
| ANY | SOME | 和子查询返回某一个值比较 |
| ALL | 和子查询返回的所有值比较 |
any相当于min,all是相当于max
案例一:返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门编号
SELECT distinct department_id
FROM departments
WHERE location_id IN(1400,1700);
②查询员工姓名,要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT distinct department_id
FROM departments
WHERE location_id IN(1400,1700)
);
二,select后面的子查询
案例一:查询每个部门的员工个数
SELECT d.,(
SELECT COUNT()
FROM employees e
WHERE e.department_id=d.department_id
) 个数
FROM departments d;
三,from后面
案例:查询每个部门的平均工资的工资等级
①查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;
select * FROM job_grades
②连接①的结果集合job_grades表,筛选条件平均工资between lowest_sal and highest_sal
SELECT ag_dep.*,g.grade_level
FROM
(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
注意: 子查询的结果要求必须起别名
四.exists后面(相关子查询)
语法: exists(完整的查询语句),结果是1或0
SELECT EXISTS
(
SELECT employee_id
FROM employees WHERE salary=30000
)