MySQL DQL操作之高级查询(子查询篇)

定义：子查询即对查询的结果集进行查询或者说查询的表也可以是一个查询结果集(示例2)，另外也包括最常用的where后加条件限制(限制的值是一个查询–示例1)

示例1：

数据准备：还是之前的查询的emp员工表和部门表：

SQL题目：#查询所有超过"SCOTT"用户工资并且非同部门的员工信息。

SQL代码：

select * from mktest.emp em1 where sal>(
  select sal from mktest.emp em2 
  where ename='Scott' and em1.deptno!=em2.deptno
);

分析：

上述是在where条件中对工资进行限制(超过"SCOTT"用户工资并且非同部门),并且两者通过子查询中的where条件限制了部门，从而满足需求。

结果：

empno	ename	job	mgr	hiredate	sal	comm	deptno
7839	KING	PRESIDENT	null	1981-11-17 00:00:00	5000.00	null	10

示例2：

数据准备：还是之前的查询的emp员工表和部门表以及薪水评级表：

薪水评级表salgrade：

SQL题目：#查询’SALES’部门平均薪水的等级。

SQL代码：

select grade from (
	select avg(sal) avg_sal from mktest.emp emp,mktest.dept dept
	where emp.deptno=dept.deptno and dept.dname like 'SALES%') ep,
mktest.salgrade salg
where avg_sal>=salg.losal and avg_sal<=salg.hisal;

分析：

上述是直接从子查询中进行查询，区别于上面示例1的where条件中的子查询形式，因为查询的是工作是SALES的部门的平均薪水，首先需要找出工作是SALES的部门，即销售员，通过模糊查询获得，然后是平均薪水，再根据平均薪水计算该平均薪水所对应的薪水登记。

结果：

grade
3

练习：

-- 以emp,dept和salgrade表为例完成以下练习:
-- 1.查询所有入职日期超过领导的员工信息。
select * from mktest.emp em1 where hiredate>(
  select hiredate from mktest.emp em2 where em2.empno=em1.mgr
  );

-- 2.查询所有超过"SCOTT"用户工资并且非同部门的员工信息。
select * from mktest.emp em1 where sal>(
  select sal from mktest.emp em2 
  where ename='Scott' and em1.deptno!=em2.deptno
);

-- 3.查询所有员工姓名,薪水,部门名称及薪水等级。
select emp.ename,emp.sal,dept.dname,salg.grade 
from mktest.emp emp,mktest.dept dept,mktest.salgrade salg
where emp.deptno=dept.deptno and emp.sal>=salg.losal 
	and emp.sal<=salg.hisal;

-- 4.查询年总收入超过20000的员工信息及其所在部门信息。
select  emp.*,dept.dname,dept.loc 
from (
	select * from mktest.emp where sal*12>20000
) emp,mktest.dept dept
where dept.deptno=emp.deptno;

-- 5.查询各部门薪水最高的员工信息
select em1.* from mktest.emp em1 where sal=(
  select sal from mktest.emp em2
  where em1.deptno=em2.deptno order by sal desc limit 1
);

-- 6.查询20号部门中员工薪水等级为3的员工信息。
select * from mktest.emp emp,mktest.salgrade salg
where deptno=20 and sal>=salg.losal and sal<=salg.hisal and grade=3;

-- 7.查询工资最高领导的姓名,薪水,所在部门及薪水等级
select ename,sal,dname,grade
from (
  select ename,sal,deptno from mktest.emp
  where sal=(
    select max(sal) from mktest.emp where empno in(
      select distinct mgr empno from mktest.emp where mgr is not null
    )
  ) and empno in(
      select distinct mgr empno from mktest.emp where mgr is not null
  )
) ep
left join mktest.dept dept
  on dept.deptno=ep.deptno
left join mktest.salgrade salg
  on ep.sal between salg.losal and salg.hisal;

-- 8.查询不是领导的员工信息。
select * from mktest.emp where empno not in(
	select distinct mgr from mktest.emp where mgr is not null
);

-- 9.查询'SALES'部门平均薪水的等级
select grade from (
	select avg(sal) avg_sal from mktest.emp emp,mktest.dept dept
	where emp.deptno=dept.deptno and dept.dname like 'SALES%') ep,
mktest.salgrade salg
where avg_sal>=salg.losal and avg_sal<=salg.hisal;

-- 10.查询每个部门员工总数的比例
select deptno,count(1)/(select count(1) from mktest.emp)*100 percent 
from mktest.emp group by deptno;