文章目录
- 一、where或having后面
-
- 1.1 标量子查询
- 1.2 列子查询(多行子查询)
- 1.3 行子查询（结果集一行多列或多行多列）
- 二、select后面
- 三、from后面
- 四、exists后面(相关子查询)
子查询含义：
出现在其他语句的select语句,称为
子查询或内查询
外部的查询语句,称为
主查询或外查询
分类:
按子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:★★★
标量子查询(单行)√
列子查询 (多行)√
行子查询(用的少)
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集一列多行)
行子查询(结果集一行多列)
表子查询(结果集一般为多行多列)
一、where或having后面
支持：
1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多行多列）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般单行操作符使用
>,<,=,>=,<=,<>
列子查询,一般搭配多行操作符使用
in,any/sum,all
④子查询的执行优先于主查询的执行,主查询的条件用到了子查询的结果
1.1 标量子查询
案例1: 谁的工资比Abel高?
step1：查询Abel的工资
show databases;
use myemployees;
select salary from employees where last_name = 'Abel';
step2：查询员工信息,满足salary>step1的结果
select * from employees
where salary >(
select salary
from employees
where last_name = 'Abel'
); # where后面标量子查询(结果集是一个数据)，放在()内
案例2: 返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
step1：查询141号员工的job_id
select job_id
from employees
where employee_id=141;
step2：查询143号员工的salary
select salary
from employees
where employee_id=143;
step3：查询员工的姓名,job_id和工资,要求job_id=step1并且salary>step2
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
step1：查询公司的最低工资
select min(salary)
from employees;
step2：查询last_name,job_id,和salary,要求salary=step1结果
select last_name,job_id,salary
from employees
where salary=(
select min(salary)
from employees
);
案例4: 查询最低工资大于50号部门最低工资的部门id和其最低工资
step1：查询50号部门的最低工资
select min(salary)
from employees
where department_id=50;
step2：查询每个部门的最低工资
select min(salary),department_id
from employees
group by department_id;
step3：筛选step2满足min(salary)>step1
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
select min(salary)
from employees
where department_id=50
); # 子查询在having之后
非法使用子查询
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
select salary
from employees
where department_id=50
); # 不是一行一列
1.2 列子查询(多行子查询)
案例1: 返回location_id是1400或1700的部门中的所有员工姓名
step1:查询location_id是1400或1700的部门编号
select department_id
from departments
where location_id in (1400,1700); # 两行
step2:查询员工姓名,要求部门号是step1列表中的某一个
select last_name
from employees
where department_id in(
select distinct department_id
from departments
where location_id in (1400,1700)
); # 将in换成 =any 效果一样;not in换成<>all效果一样
案例2: 返回其它工种中比job_id为‘IT_PROG’工种部门任一工资低的员工的员工号、姓名、job_id以及salary
step1: 查询job_id为‘IT_PROG’部门任一工资
select distinct salary
from employees
where job_id='IT_PROG';
step2: 查询员工号、姓名、job_id以及salary,salary<any(step1)
select last_name,employee_id,job_id,salary
from employees
where salary < any (
select distinct salary
from employees
where job_id='IT_PROG'
)and job_id<>'IT_PROG'; # 工种不是IT_PROG且工资低于IT_PROG任一工资的员工
或者：
select last_name,employee_id,job_id,salary
from employees
where salary < (
select max(salary)
from employees
where job_id='IT_PROG'
)and job_id<>'IT_PROG'; # 去掉any，里面为max也行
案例3: 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
select last_name,employee_id,job_id,salary
from employees
where salary < all (
select distinct salary
from employees
where job_id='IT_PROG'
)and job_id<>'IT_PROG'; # 比IT_PROG部门所有（all）都低
或
select last_name,employee_id,job_id,salary
from employees
where salary < (
select min(salary)
from employees
where job_id='IT_PROG'
)and job_id<>'IT_PROG'; # 去掉all，里面为min也行
1.3 行子查询（结果集一行多列或多行多列）
案例： 查询员工编号最小且工资最高的员工信息
select *
from employees
where (employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);
传统做法:
s1:查询最小的员工编号
show databases;
use myemployees;
select min(employee_id)
from employees;
s2:查询最高工资
select max(salary)
from employees;
s3:查询员工信息
select *
from employees
where employee_id=(
select min(employee_id)
from employees
)and salary=(
select max(salary)
from employees
); # 不用and，使两个字段放在（）里亦可
二、select后面
仅仅支持标量子查询
案例1: 查询每个部门的员工个数
select d.*,(
select count(*)
from employees e
where e.department_id=d.department_id
) 个数 # 个数为标量
from departments d;
案例2: 查询员工号=102的部门
select (
select department_name
from departments d
inner join employees e
on d.department_id=e.department_id
where e.employee_id=102
) 部门名; # 里面是部门名
三、from后面
将子查询结果充当一张表,要求必须起别名
案例: 查询每个部门的平均工资的工资等级
s1：查询每个部门的平均工资
select avg(salary),department_id
from employees
group by department_id;
select * from job_grades;
s2:连接s1的结果集和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:不存在
案例1: 查询有员工的部门名
in(能使用exists就能用in)
select department_name
from departments d
where d.department_id in (
select department_id
from employees
); # 员工表中的部门名集合里是不是部门表中也有
== exists==
select department_name
from departments d
where exists(
select *
from employees e
where d.department_id=e.department_id
);
案例2: 查询没有女朋友的男神信息
not in
use girls;
select bo.*
from boys bo
where bo.id not in(
select boyfriend_id
from beauty
);
not exists
select bo.*
from boys bo
where not exists(
select boyfriend_id
from beauty b
where bo.id=b.boyfriend_id
);