mysql学习笔记（七）

• 子查询

    sql允许多层嵌套查询，子查询即嵌套在其他查询中的查询。
    子查询要用括号括起来
    将子查询放在比较运算符的右边（增强可读性）
  

• 分类：单行子查询，多行子查询。

    --有哪些人的薪水在雇员平均薪水之上。（单行子查询）
      1.先求平均薪水：
      select avg(e.sal) from emp e;
      2.把所有人的薪水与平均薪水比较，找出大于平均薪水的员工：
      select e.ename,e.sal from emp e where e.sal > (select avg(e.sal) from emp e);
    --查询雇员中哪些人是经理（多行子查询，子查询返回结果是多行，用in实现）
      1. 先查询所有人的经理编号：
      select distinct e.mgr from emp e;
      2.在雇员表中过滤这些编号即可。
      select * from emp e where e.empno in(select distinct e.mgr from emp e);
    --找出部门编号为20的所有员工中收入最高的职员。some  all实现
      select * from emp e where sal >=all(select sal from emp where deptno =20) and deptno = 20;
    --在from子句中使用子查询。求每个部门平均薪水的等级。
      1.先求部门的平均薪水:
      select avg(e.sal),e.dept from emp e group by e.deptno;
      2.跟薪水等级表做关联，求出平均薪水的等级。
      select t.deptno ,sg.grade from salgrade sg join(select e.deptno,avg(e.sal) from e group by e.deptno) t on t.vsal between sg.losal and sg.hisal;
    --求平均薪水最高的部门的部门编号
      1.先求部门的平均薪水
      select e.deptno,avg(e.sal) from emp e group by e.deptno;
      2.再求平均薪水最高的部门。
      select max(t.vsal) from (select e.deptno,avg(e.sal) vsal from emp e group by e.deptno)t;
      3.再求部门编号：
      select t.deptno from (select e.deptno,avg(e.sal) vsal from emp e group by e.deptno) t where t.vsal = 
      (select max(t.vsal) from (select e.deptno,avg(e.sal) vsal from emp e group by e.deptno) t);
  

• 限制输出

  limit是MySQL中用来做限制输出的，但是Oracle不支持。
  MySQL用法：求薪水最高的前5名雇员
    select * from emp order by sal desc limit 5;
  求指定名次的排名。如求5到10名的雇员名称。
    select * from emp order by sal desc limit(5,10);
  

• 分页查询来实现限制输出：

  在Oracle中，如果需要使用限制输出和分页的功能，必须使用rownum，但是rownum不能直接使用，需要嵌套使用。
  --求薪水最高的前5名雇员。
    select * from(select * from emp e order by e.sal desc )t1 where rownum <=5;
  --求薪水最高的第6-10名雇员
    错误解法：查不到值，因为rownum动态变化。
      select * from (select * from emp e order by e.sal desc) t1 where rownum<=10;
    正确解法：
      1.先求出rownum前10名。
      select t1.* ,rownum from (select * from emp e order by e.sal desc) t1 where rownum <=10;
      2.再取出6-10名。
      select * from (select t1.*,rownum rn from (select * from emp e order by e.sal desc) t1 where rownum <=10) t where t.rn>5 and t.rn <=10;
    注意：
      使用rownum的时候必须再外层添加嵌套，此时才能将rownum作为其中的一个列，然后进行限制输出。
  

• 多行合并成一行时，如果有一行有值，其他行是空，可以用max。

  select ss.name,
            max(decode(ss.subject,'语文'，ss.score))语文，
            max(decode(ss.subject,'数学'，ss.score))数学，
            max(decode(ss.subject,'英语'，ss.score))英语，
  from student_score ss group by ss.name;//必须带着groupby，分组时不能查询分组之外的字段。