子查询
定义:查询中嵌套查询就是子查询
注意:子查询必须用()括起来
子查询的本质:
a. 内联视图
b. 把子查询的结果作为外部查询的条件
找出工资大于Mark的员工名字和工资
分析：
1.查询出Mark的工资是多少
select salary from s_emp where first_name='Mark';//1450
2.查询出高于1450工资的人
select first_name,salary from s_emp where salary>1450;
整合成子查询
select first_name,salary
from s_emp
where salary>(
select salary
from s_emp
where first_name='Mark'
);
子查询的特点:
1.子查询很灵活,可以解决很多其他查询方式不能解决的问题
2.子查询效率很低,其中相关子查询效率最低
3.子查询嵌套的层数越多,则效率越低
为什么相关子查询的效率极其低下?
内查询用到了外查询的列,每次查询行记录时都会迭代表格中
每一行的行记录,而这种迭代中产生的值都是动态生成的.
结论:
性能排序/优先使用
关联/分组查询>无关子查询>相关子查询
练习
1. 找出工资比'BLAKE'多的员工
select *
from emp
where salary > (select salary from emp where ename ='BLAKE');
2. 列出薪金高于公司平均薪金的所有员工，所在部门
select empno,ename,salary,deptno
from emp
where salary > (select avg(salary) from emp);
3. 查询出工资最低的员工的姓名，工作，工资
select ename,job,salary
from emp
where salary = (select min(salary) from emp);
4. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
select e.ename, e.salary, d.dname
from emp e join dept d
on e.deptno = d.deptno
where d.deptno !=30 and salary > (select max(salary) from emp where deptno = 30);
或者
select e.ename, e.salary, d.dname
from emp e join dept d
on e.deptno = d.deptno
where d.deptno !=30 and salary > all (select salary from emp where deptno = 30);
5.查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资
select avg(salary)
from emp
where job in (
select job from emp where ename in('MARTIN','SMITH')
);
6. 列出薪金比“BLAKE”或“WARD”多的所有员工的编号、姓名、部门名称、其领导姓名。
select e.empno 员工的编号,e.ename 员工姓名,d.dname 部门名称,m.ename 领导姓名
from emp e join dept d
on e.deptno = d.deptno
left join emp m
on e.mgr = m.empno
where e.salary > any ( select salary from emp where ename in ('BLAKE','WARD') );
select *
from emp
where salary > ( select min( salary) from emp where ename in ('BLAKE','WARD) );
7. 找出各个部门中大于他所在部门平均工资的员工名和工资
select em.empno '员工编号',em.ename '员工姓名',em.salary '员工薪资',tm.avg_sal '所在部门的平均薪资'
from emp em join (
select * from (
select d.deptno ,avg(e.salary) avg_sal
from emp e join dept d
on e.deptno = d.deptno
group by d.deptno )t
)tm
on em.deptno = tm.deptno
where em.salary > tm.avg_sal
query_result1.png
8. 查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入
select e.empno,e.ename , e.salary+ifnull(e.comm,0)
from emp e join emp m
on e.mgr = m.empno
where ( e.salary + ifnull(e.comm,0) ) > ( m.salary + ifnull(m.comm,0));
9. 得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置
select *
from (select d.deptno dno, d.dname dname ,d.loc loc , sum(salary) s
from emp e join dept d
on e.deptno = d.deptno
group by d.deptno) temp
having s = min(s);
select *
from (select d.deptno dno, d.dname dname ,d.loc loc , sum(salary) s
from emp e join dept d
on e.deptno = d.deptno
group by d.deptno) temp
order by s
limit 0,1; -----------limit startNo, length
10. 查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置
select e.ename,d.dname,d.loc
from emp join dept d
on e.deptno = d.deptno
where e.deptno in(10,20)
order by salary
limit 2,3;
11. 以职位分组，找出平均工资最高的两种职位
select job,avg(salary)
from emp
group by job
order by avg(salary) desc
limit 2
12. 查询出各部门总薪资,平均薪资，总人数，显示部门编号，部门名称与部门总薪资（没有员工的部门也需要统计）
select sum(salary),avg(salary),count(empno),d.deptno,dname
from emp e right join dept d
on e.deptno = d.deptno
group by d.deptno;
