mysql的复习笔记

1、取得每个部门最高薪水的人员名称
第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值)
mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
第二步：将以上的查询结果当做一张临时表t
t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal
select 
    e.ename, t.*
from 
    emp e
join
    (select deptno,max(sal) as maxsal from emp group by deptno) t
on
    t.deptno = e.deptno and t.maxsal = e.sal;

+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

2、哪些人的薪水在部门的平均薪水之上
第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
第二步：将以上查询结果当做t表，t和emp表连接
条件：部门编号相同，并且emp的sal大于t表的avgsal
select 
    t.*, e.ename, e.sal
from
    emp e
join
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
on
    e.deptno = t.deptno and e.sal > t.avgsal;
+--------+-------------+-------+---------+
| deptno | avgsal      | ename | sal     |
+--------+-------------+-------+---------+
|     30 | 1566.666667 | ALLEN | 1600.00 |
|     20 | 2175.000000 | JONES | 2975.00 |
|     30 | 1566.666667 | BLAKE | 2850.00 |
|     20 | 2175.000000 | SCOTT | 3000.00 |
|     10 | 2916.666667 | KING  | 5000.00 |
|     20 | 2175.000000 | FORD  | 3000.00 |
+--------+-------------+-------+---------+

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

    平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

    平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

    第一步：找出每个人的薪水等级
    emp e和salgrade s表连接。
    连接条件：e.sal between s.losal and s.hisal
    
    select 
        e.ename,e.sal,e.deptno,s.grade
    from
        emp e
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal;

    +--------+---------+--------+-------+
    | ename  | sal     | deptno | grade |
    +--------+---------+--------+-------+
    | CLARK  | 2450.00 |     10 |     4 |
    | KING   | 5000.00 |     10 |     5 |
    | MILLER | 1300.00 |     10 |     2 |

    | SMITH  |  800.00 |     20 |     1 |
    | ADAMS  | 1100.00 |     20 |     1 |
    | SCOTT  | 3000.00 |     20 |     4 |
    | FORD   | 3000.00 |     20 |     4 |
    | JONES  | 2975.00 |     20 |     4 |

    | MARTIN | 1250.00 |     30 |     2 |
    | TURNER | 1500.00 |     30 |     3 |
    | BLAKE  | 2850.00 |     30 |     4 |
    | ALLEN  | 1600.00 |     30 |     3 |
    | JAMES  |  950.00 |     30 |     1 |
    | WARD   | 1250.00 |     30 |     2 |
    +--------+---------+--------+-------+

    第二步：基于以上的结果继续按照deptno分组，求grade的平均值。
    select 
        e.deptno,avg(s.grade)
    from
        emp e
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal
    group by
        e.deptno;

    +--------+--------------+
    | deptno | avg(s.grade) |
    +--------+--------------+
    |     10 |       3.6667 |
    |     20 |       2.8000 |
    |     30 |       2.5000 |
    +--------+--------------+

4、不准用组函数（Max ），取得最高薪水

第一种：sal降序，limit 1
select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

第二种方案：select max(sal) from emp;

第三种方案：表的自连接
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

从a表中找出比b表中元素小的，只有5000不能被找出来，因为5000最大，因此not in就表示最大值5000
select 
    distinct a.sal 
from 
    emp a 
join 
    emp b 
on 
    a.sal < b.sal

a表、b表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

5、取得平均薪水最高的部门的部门编号

第一种方案：降序取第一个。

    第一步：找出每个部门的平均薪水
        select deptno,avg(sal) as avgsal from emp group by deptno;
        +--------+-------------+
        | deptno | avgsal      |
        +--------+-------------+
        |     10 | 2916.666667 |
        |     20 | 2175.000000 |
        |     30 | 1566.666667 |
        +--------+-------------+
    第二步：降序选第一个。
        select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
        +--------+-------------+
        | deptno | avgsal      |
        +--------+-------------+
        |     10 | 2916.666667 |
        +--------+-------------+

第二种方案：max

    第一步：找出每个部门的平均薪水

    select deptno,avg(sal) as avgsal from emp group by deptno;
    +--------+-------------+
    | deptno | avgsal      |
    +--------+-------------+
    |     10 | 2916.666667 |
    |     20 | 2175.000000 |
    |     30 | 1566.666667 |<