5.7 进阶7：子查询

2024-03-29 12:54:40

5.7 进阶7：子查询

5.7.1 子查询的含义

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

5.7.2 子查询的分类

按子查询出现的位置：
- select后面
  - 仅仅支持标量子查询
- from后面
  - 支持表子查询
- where或having后面
  - 标量子查询
  - 列子查询
  - 行子查询
- exists后面（相关子查询）
  - 表子查询
按结果集的行列数不同
- 标量子查询：结果集只有一行一列
- 列子查询：结果集只有一列多行
- 行子查询：结果集可以有多行多列，一行多列
- 表子查询：结果集一般为多行多列

5.7.3 where或having后面

标量子查询：单行子查询
列子查询：多行子查询
行子查询

5.7.4 子查询的特点

子查询都放在小括号内
子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
子查询优先于主查询执行，主查询使用了子查询的执行结果
单行子查询
- 结果集只有一行一列
- 一般搭配单行操作符使用：：> < = <> >= <=
- 非法使用子查询的情况：
  - 子查询的结果为一组值
  - 子查询的结果为空标量子查询
- 案例1到案例4
多行子查询
- 结果集有多行
- 一般搭配多行操作符使用：any/some、all、in、not in
  - in/not in：属于子查询结果中的任意一个就行
  - any和all往往可以用其他查询代替

5.7.5 案例

案例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和工资

# 1查询141号员工的job_id
select job_id
from employees
where employee_id = 141
# 2查询143号员工的salary
select salary
from employees
where employee_id = 143

# 3查询员工的姓名 job_id和工资，要求job_id=1且salary=2
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

# 1查询公司的最低工资
select min(salary)
from employees

# 2查询last_name，job_id和salary，要求salary=1
select last_name,job_id, salary
from employees
where salary = (
    select min(salary)
    from employees
);

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

# 1查询50号部门的最低工资
select min(salary)
from employees
where department_id = 50

# 2查询所有部门的最低工资
select min(salary),department_id
from employees
group by department_id

# 3筛选2，满足min(salary)大于1的结果
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
	select min(salary)
    from employees
    where department_id = 50
);

案例5：返回location_id是1400或1700的部门中的所有员工姓名

select distinct department_id
from departments
where location_id in (1400,1700)

# 查询员工姓名，要求部门号是1列表中的某一个
select last_name
from emplopyees
where department_id in (
    select distinct department_id
    from departments
    where location_id in (1400,1700)
)

案例6：返回其他工种中比job_id为IT_PROG部门任一工资低的员工的员工号、姓名、job_id以及salary

# 查询job_id为“IT_PROG"部门的最低工资
select salary
from employees
where job_id = "IT_PROG"

# 查询员工号
select employee_id, last_name,job_id,salary
from employees
where salary < any(
    select salary
    from employees
    where job_id = "IT_PROG"
                  )
and job_id <>"IT_PROG";

5.7.5.1 where后面的子查询

查询员工编号最小且工资最高的员工信息

# 查询最小编号
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
);

5.7.5.2 select后面

select后面查询结果只能是一行一列的
查询每个部门的员工个数

select d.*,(
	select count(*)
    from employees e
    where e.department_id = d.department_id
)个数
from departments d;

查询员工号=102的部门名

select (
	select department_name
    from departments d
    inner join employees e
    on d.department_id = e.department_id
    where e.employee_id = 102
)部门名;

5.7.5.3 from后面

子查询的结果集当成一个表来用
查询每个部门的平均工资的工资等级

# 查询每个部门的平均工资
select avg(salary),department_id
from employees
group by department_id;

select *
from job_grades

# 2链接1的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
select ag_dep.*,j.grade_level
from (
	select avg(salary) ag,department_id
	from employees
	group by department_id
) ag_dep
inner join job_grades j
on ag_dep.ag between lowest_sal and highest_sal;

5.7.5.4 exists后面

关注结果是否存在，如果有的话结果为1，没有的话，结果为0select exists(select employee_id from employees where salary = 30000) 是否存在;
查询有员工的部门名

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
);

查询一下没有女朋友的男神信息

# in的方式
select bo.*
from boys bo
where bo.id not in (
	select boyfriend_id
    from beauty
);

# exist的方式
select bo.*
from boys bo
where not exists (
	select boyfriend_id
    from beauty b
    where bo.id = b.boyfriend_id
);

码农公寓

5.7 进阶7：子查询

5.7.1 子查询的含义

5.7.2 子查询的分类

5.7.3 where或having后面

5.7.4 子查询的特点

5.7.5 案例

5.7.5.1 where后面的子查询

5.7.5.2 select后面

5.7.5.3 from后面

5.7.5.4 exists后面

相关文章