子查询
定义:查询中嵌套查询就是子查询
注意:子查询必须用()括起来
子查询的本质:
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 !=30and salary >(select max(salary)from emp where deptno =30)查询出30部门最高薪资2000 比30部门薪资还要低的 select empno,ename,salary,deptno from emp where deptno !=30and salary <(select max(salary)from emp where deptno =30)select empno,ename,salary,deptno from emp where deptno !=30and 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.deptnoleft join emp m on e.mgr= m.empnowhere e.salary>(select min(salary)from emp where ename in('BLAKE','WARD'))-- 找出各个部门中大于他所在部门平均工资的员工名和工资分析： 部门编号 平均薪资 102000 【salary >2000】 203000 【salary >3000】 303500 【salary >3500】 select deptno,avg(salary) avg_sal from emp e groupby deptno -- ------------临时表---------------dno avg_sal 102916202175301566-- ---------------------------------select e.empno,e.ename,e.salary,e.deptno,temp.avg_salfrom emp e join(select deptno dno ,avg(salary) avg_sal from emp e groupby deptno)temp on e.deptno= temp.dnowhere e.salary> temp.avg_sal
