子查询

子查询

• 在mysql中,子查询分为两大类

• 不相关子查询

  • 单行不相关子查询:子查询的返回结果只有一个
  • 多行不相关子查询:子查询的返回结果是多个

• 相关子查询

• 不相关子查询的特点:

  • 先执行子查询，再执行外查询；
  • 子查询可以独立运行，称为不相关子查询。
  • 根据子查询的结果行数，可以分为单行子查询和多行子查询。

• 相关子查询的特点:

  • 语句简单
  • 功能强大
  • 但是语句可读性相较于不相关子查询来说较差

不相关子查询-单行子查询

-- 查询所有比"CLARK"工资高的员工的信息
-- step1先查出"CLARK"的工资
select sal from emp where ename = "CLARK"; -- 结果为2450
-- step2查询所有比"CLARK"工资高的员工的信息
select * from emp where sal > 2450;
-- step1与step2合并  子查询
select * from emp where sal > (select sal from emp where ename = "CLARK");

-- 单行子查询：
-- 查询工资高于平均工资的雇员名字和工资。
select ename, sal
from emp
where sal > (select avg(sal) from emp);

-- 查询和CLARK同一部门且比他工资低的雇员名字和工资。
select ename, sal
from emp
where deptno = (select deptno from emp where ename = "CLARK") and sal < (select sal from emp where ename = "CLARK");

-- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
select * 
from emp
where job = (select job from emp where ename = "SCOTT") 
and 
hiredate < (select hiredate from emp where ename = "SCOTT");

不相关子查询-多行子查询

-- 多行子查询：
-- 【1】查询【部门20中职务同部门10的雇员一样的】雇员信息。
-- 查询雇员信息
select * from emp;
-- 查询部门20中的雇员信息
select * from emp where deptno = 20;
-- 部门10的雇员的职务：
select job from emp where deptno = 10;
-- 查询部门20中职务同部门10的雇员一样的雇员信息。
-- 方式1
select * from emp 
where deptno = 20
and job in (select job from emp where deptno = 10);
-- 方式2
select * from emp 
where deptno = 20
and job = any(select job from emp where deptno = 10);


-- 【2】查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
-- 查询雇员的编号、名字和工资
select empno,ename,sal from emp;
-- “SALESMAN”的工资：
select sal from emp where job = "SALESMAN";
-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
-- 多行子查询：
select empno,ename,sal 
from emp
where sal > all(select sal from emp where job = "SALESMAN");
-- 单行子查询
select empno,ename,sal 
from emp
where sal > (select max(sal) from emp where job = "SALESMAN");


-- 【3】查询工资低于任意一个“CLERK”的工资的雇员信息。  
-- 查询雇员信息
select * from emp;
-- 查询工资低于任意一个“CLERK”的工资的雇员信息
-- 多行子查询
select * 
from emp
where sal < any(select sal from emp where job = "CLERK");
-- 单行子查询
select * 
from emp 
where sal < (select max(sal) from emp where job = "CLERK");

相关查询

-- 【1】查询最高工资的员工  （不相关子查询）
select * from emp where sal = (select max(sal) from emp);
-- 【2】查询本部门最高工资的员工   （相关子查询）
-- 方法1：通过不相关子查询实现：
select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
union
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
union
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30);

-- 缺点：语句比较多，具体到底有多少个部分未知
-- 方法2： 相关子查询
select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno;

-- 【3】查询工资高于其所在岗位的平均工资的那些员工  （相关子查询）
-- 不相关子查询：
select * from emp where job = "SALESMAN" and sal >= (select avg(sal) from emp where job = "SALESMAN")
union
select * from emp where job = "MANAGER" and sal >= (select avg(sal) from emp where job = "MANAGER")
union
select * from emp where job = "CLERK" and sal >= (select avg(sal) from emp where job = "CLERK")
union
select * from emp where job = "ANALYST" and sal >= (select avg(sal) from emp where job = "ANALYST")
union
select * from emp where job = "PRESIDENT" and sal >= (select avg(sal) from emp where job = "PRESIDENT");


-- 相关子查询
select * from emp e where sal >= (select avg(sal) from emp where job = e.job);