Mysql练习

1.求每个部门最高薪水的人员名称

解决思路：1 先求每个部门最高的薪水

SELECT 
    e.deptno,MAX(e.sal) as maxsal
from 
    emp e
GROUP BY
     e.deptno;
    

2. 将第一步得出来的结果，当成临时表

SELECT 
     e.deptno,ename,t.maxsal,e.sal
from 
      (SELECT 
            e.deptno,max(e.sal) as maxsal
       from 
            emp e
       GROUP BY
             e.deptno) t
JOIN   
       emp e
on    
       t.deptno = e.deptno
WHERE 
        t.maxsal = e.sal
ORDER BY           --做一个排序
        e.deptno;

2. 哪些人的薪水在部门平均薪水之上

1. 求出每个部门的平均薪水

SELECT
   e.deptno,avg(e.sal) as avgsal
from 
   emp e
GROUP BY
   e.deptno;

2. 查看哪些人的薪水大于平均水平

SELECT 
   e.deptno,e.ename,e.sal
from 
    (SELECT
   e.deptno,avg(e.sal) as avgsal
from 
   emp e
GROUP BY
   e.deptno) t
JOIN 
    emp e
ON 
    e.deptno = t.deptno
WHERE
     t.avgsal < e.sal

3.取得部门中（所有人的）平均薪水等级

1. 取得部门中所有人的平均薪水的等级

SELECT
   e.deptno,avg(e.sal) as avgsal
from 
   emp e
GROUP BY
   e.deptno;

2. 取得部门中所有人的平均的薪水等级

SELECT 
    t.deptno,t.avgsal,s.grade
from 
   (  SELECT
           e.deptno,avg(e.sal) as avgsal
      from 
           emp e
      GROUP BY
           e.deptno) t
JOIN 
   salgrade s
on 
   t.avgsal BETWEEN s.losal and hisal

4. 取得部门中所有人的平均的薪水等级

1. 求出每个人的薪水等级

SELECT 
    e.deptno,e.ename,s.grade
FROM
    emp e
join 
    salgrade s
on 
    e.sal BETWEEN s.losal and s.hisal;

2. 在临时表求平均的

SELECT 
    t.deptno,t.ename,AVG(t.grade) as avggrade
FROM
   (SELECT 
    e.deptno,e.ename,s.grade
FROM
    emp e
join 
    salgrade s
on 
    e.sal BETWEEN s.losal and s.hisal) t
GROUP BY
    t.deptno

5. 不用组函数（MAX）,求取最高薪水（俩种方案）

1. 用倒叙，找第一行

SELECT e.sal from emp e ORDER BY e.sal desc LIMIT 1

2. 建俩张表，交叉进行比较去掉最大的，在找出不在俩表生成的表中数，就是最大的数

SELECT a.sal FROM emp a WHERE a.sal not in (SELECT 
    a.sal 
from 
     emp a 
join 
      emp b
on 
     a.sal < b.sal);

6. 求取平均薪水最高的部门的部门编号

1. 求部门平均水平

SELECT
    e.deptno,avg(e.sal) as avgsal
FROM 
    emp e
GROUP BY
    e.deptno

2. 求出平均水平最大值，再进行筛选

SELECT 
   t.deptno 
FROM 
    (  SELECT
            e.deptno,avg(e.sal) as avgsal
       FROM 
            emp e
       GROUP BY
            e.deptno)t
HAVING
    MAX(avgsal)

7 . 求取平均薪水最高部门的部门名称

SELECT 
   d.dname
FROM 
    (  SELECT
            e.deptno,avg(e.sal) as avgsal
       FROM 
            emp e
       GROUP BY
            e.deptno)t
JOIN 
     dept d
ON   
     t.deptno = d.deptno
HAVING
    MAX(avgsal)

8.求平均薪水的等级最低的部门名称

1. 部门的平均薪水

SELECT
   e.deptno,avg(e.sal) AS avgsal
FROM
  emp e
group by
  e.deptno

2. 在一的基础上求最低的部门薪资等级

SELECT
   MIN(s.grade)
from 
   (SELECT
       e.deptno,avg(e.sal) AS avgsal
    FROM
       emp e
   group by
        e.deptno) t
join 
   salgrade s 
on 
    t.avgsal between s.losal and s.hisal 

3.连表就行求部门名称

SELECT 
   t.deptno,t.dname,s.grade
FROM
   (SELECT
            e.deptno,d.dname,avg(e.sal) as avgsal
       FROM 
            emp e
       JOIN 
            dept d
        on   
            e.deptno = d.deptno
        GROUP BY
             e.deptno,d.dname)t
JOIN 
   salgrade s
on 
   t.avgsal BETWEEN s.losal and s.hisal
WHERE
   s.grade = (SELECT
   MIN(s.grade)
from 
   (SELECT
       e.deptno,avg(e.sal) AS avgsal
    FROM
       emp e
   group by
        e.deptno) t
join 
   salgrade s 
on 
    t.avgsal between s.losal and s.hisal )

9. 求比普通员工的最高薪水高的经理名字

1. 找出mgr的人有哪些

SELECT  
     DISTINCT   --去重
     mgr 
FROM
    emp 

2. 找出最高薪水员工

SELECT 
    max(sal) as maxsal
FROM
    emp 
WHERE
    empno not in (SELECT  
     DISTINCT
     mgr 
FROM
    emp  
WHERE mgr is not null)
 

注 in 会自动忽略空值，not in不会自动忽略

SELECT ename from emp WHERE sal > (SELECT 
    max(sal) as maxsal
FROM
    emp 
WHERE
    empno not in (SELECT  
     DISTINCT
     mgr 
FROM
    emp  
WHERE mgr is not null)
 )

10 取薪水最高的前五名员工

SELECT * from emp 
ORDER BY 
   sal desc 
LIMIT 
   0,5;

11 取得薪水最高的第六到第十名员工

SELECT * from emp ORDER BY sal desc limit 5,5

12 取得最后入职的5名员工

SELECT * from emp order by hiredate desc limit 0,5

13求每个等级有多少员工

1.查询每个员工的薪水等级

SELECT
  e.ename,s.grade
FROM
  emp e
JOIN
  salgrade s
on 
  e.sal BETWEEN s.losal and s.hisal
ORDER BY
  s.grade

2. 将每个等级的员工计数

SELECT
   t.grade ,COUNT(t.ename) as countEmp
FROM 
   (SELECT
  e.ename,s.grade
FROM
  emp e
JOIN
  salgrade s
on 
  e.sal BETWEEN s.losal and s.hisal
ORDER BY
  s.grade) t
GROUP BY 
   t.grade