子查詢

定義:查詢中嵌套查詢就是子查詢

注意:子查詢必須用()括起來

子查詢的本質:

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.子查詢嵌套的層數越多,則效率越低

為什么相關子查詢的效率極其低下?

內查詢用到了外查詢的列,每次查詢行記錄時都會迭代表格中

每一行的行記錄,而這種迭代中產生的值都是動態(tài)生成的.

結論:

性能排序/優(yōu)先使用

關聯/分組查詢>無關子查詢>相關子查詢

練習

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. 查詢出各部門總薪資,平均薪資，總人數，顯示部門編號，部門名稱與部門總薪資（沒有員工的部門也需要統(tǒng)計）

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;