5.7 进阶7:子查询

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
);
上一篇:【DQL】总结(一):基础查询


下一篇:SQL练习题一