子查询

定义:查询中嵌套查询就是子查询

注意:子查询必须用()括起来

子查询的本质:

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)  );

9. 查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入

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));

10. 得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置

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和部门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;

以职位分组，找出平均工资最高的两种职位

select job,avg(salary)
from emp 
group by job 
order by avg(salary) desc
limit 0,2

查询出各部门总薪资,平均薪资，总人数，显示部门编号，部门名称与部门总薪资（没有员工的部门也需要统计）

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;

分页

MySQL

 3050 个产品  
页面 ，每页显示20条数据  
多少页？ 3050/20  
显示第1页数据： 1-20  
显示第2页数据：21-40

显示第n页数据： 20 * (n-1) + 1 -  20 * n

根据员工薪资从高到低，显示第5页员工信息
select * from emp 
order by salary desc 
limit 81 ,20

oracle

  第3页：11 - 15     

  select * from (
  select rownum r, t.* from (
   select   empno,ename,sal 
   from emp  
   order by sal desc)t
   ) tm
   where tm.r >=11 and tm.r <=15

课堂练习

--  ---------------子查询 sub query -------------

-- 1.  查询出薪资最高的员工的编号，姓名，薪资
select empno,ename,salary
from emp
where salary = (select max(salary) from emp)

-- 2. 查询比 ‘SCOTT’ 薪资还要高的员工的编号，姓名，薪资
select empno ,ename,salary
from emp 
where salary > (
select salary from emp 
where ename = 'SCOTT'
)
-- 3. 列出薪金高于公司平均薪金的所有员工，所在部门 
select empno,deptno,salary
from emp 
where salary > (
select avg(salary)
from emp 
)
-- 4. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
select empno,ename,salary,deptno
from emp
where deptno !=30 and salary > (select max(salary) from emp where deptno = 30)

查询出30部门最高薪资2000

比30部门薪资还要低的
select empno,ename,salary,deptno
from emp
where deptno !=30 and salary < (select max(salary) from emp where deptno = 30)

select empno,ename,salary,deptno
from emp
where deptno !=30 and salary <all (select salary from emp where deptno = 30)

--  列出薪资比“BLAKE”或“WARD”多的所有员工的编号、姓名、薪资
select empno,ename,salary
from emp
where salary >( select min(salary) from emp where ename in('BLAKE','WARD'))
-- 比任何一个都要高
select empno,ename,salary
from emp
where salary >any( select salary from emp where ename in('BLAKE','WARD'))
-- 三表查询
select e.empno,e.ename,e.salary,d.dname,m.ename 'manager_name'
from emp e join dept d
on e.deptno = d.deptno
left join emp m 
on e.mgr = m.empno
where e.salary >( select min(salary) from emp where ename in('BLAKE','WARD'))

-- 找出各个部门中大于他所在部门平均工资的员工名和工资
分析： 部门编号  平均薪资
           10       2000 【salary > 2000】
                     20       3000 【salary > 3000】
                     30       3500 【salary > 3500】
select deptno,avg(salary) avg_sal
from emp e
group by deptno
-- ------------临时表---------------
dno  avg_sal
10   2916
20   2175
30   1566
-- ---------------------------------
select e.empno,e.ename,e.salary,e.deptno,temp.avg_sal
from emp e join (select deptno dno ,avg(salary) avg_sal
                                from emp e 
                                group by deptno)temp
on e.deptno = temp.dno
where e.salary > temp.avg_sal

Mysql

七、SQL子查询

子查询

练习

分页

MySQL

oracle

课堂练习