MySQL学习笔记（5）

女爷i 2022-09-16 15:28 85阅读 0赞

练习题目

> 1.  每个部门薪水最高的员工

mysql> select e.ename, t.* 
        -> from emp e 
        -> join (select deptno, max(sal) as maxsal from emp group by deptno) t 
        -> on e.deptno = t.deptno and e.sal = t.maxsal;
    +-------+--------+---------+
    | ename | deptno | maxsal  |
    +-------+--------+---------+
    | BLAKE |     30 | 2850.00 |
    | SCOTT |     20 | 3000.00 |
    | KING  |     10 | 5000.00 |
    | FORD  |     20 | 3000.00 |
    +-------+--------+---------+
    4 rows in set (0.00 sec)

> 1.  哪些人的工资在平均工资上

mysql> select e.ename, e.sal, t.* 
        -> from emp e 
        -> join (select deptno, avg(sal) as avgsal from emp group by deptno) t 
        -> on e.sal > avgsal and e.deptno = t.deptno;
    +-------+---------+--------+-------------+
    | ename | sal     | deptno | avgsal      |
    +-------+---------+--------+-------------+
    | ALLEN | 1600.00 |     30 | 1566.666667 |
    | JONES | 2975.00 |     20 | 2175.000000 |
    | BLAKE | 2850.00 |     30 | 1566.666667 |
    | SCOTT | 3000.00 |     20 | 2175.000000 |
    | KING  | 5000.00 |     10 | 2916.666667 |
    | FORD  | 3000.00 |     20 | 2175.000000 |
    +-------+---------+--------+-------------+
    6 rows in set (0.00 sec)

> 1.  每个部门平均的工资等级

mysql> select e.deptno, avg(grade)
        -> from emp e
        -> join salgrade s
        -> on e.sal between s.losal and s.hisal
        -> group by e.deptno;
    +--------+------------+
    | deptno | avg(grade) |
    +--------+------------+
    |     20 |     2.8000 |
    |     30 |     2.5000 |
    |     10 |     3.6667 |
    +--------+------------+
    3 rows in set (0.01 sec)

> 1.  查找工资最高的员工

mysql> select ename, sal 
        -> from emp 
        -> order by sal desc 
        -> limit 1;
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | KING  | 5000.00 |
    +-------+---------+
    1 row in set (0.00 sec)

> 1.  平均工资最高的部门

mysql> select d.dname, t.* 
        -> from dept d 
        -> join (select deptno, avg(sal) as avgsal from emp group by deptno) t 
        -> on d.deptno = t.deptno 
        -> order by avgsal desc 
        -> limit 1;
    +------------+--------+-------------+
    | dname      | deptno | avgsal      |
    +------------+--------+-------------+
    | ACCOUNTING |     10 | 2916.666667 |
    +------------+--------+-------------+
    1 row in set (0.00 sec)

> 1.  平均工资等级最低的部门

mysql> select d.dname, t.* 
        -> from dept d 
        -> join (select e.deptno, avg(s.grade) as avggrade from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno order by avggrade limit 1) t 
        -> on d.deptno = t.deptno;
    +-------+--------+----------+
    | dname | deptno | avggrade |
    +-------+--------+----------+
    | SALES |     30 |   2.5000 |
    +-------+--------+----------+
    1 row in set (0.00 sec)

> 1.  取得比普通员工（在mgr字段没有出现）的最高薪水还要高的领导

// 比普通员工最高工资还要高的一定是领导
    mysql> select empno, ename, sal 
        -> from emp 
        -> where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
    +-------+-------+---------+
    | empno | ename | sal     |
    +-------+-------+---------+
    |  7566 | JONES | 2975.00 |
    |  7698 | BLAKE | 2850.00 |
    |  7782 | CLARK | 2450.00 |
    |  7788 | SCOTT | 3000.00 |
    |  7839 | KING  | 5000.00 |
    |  7902 | FORD  | 3000.00 |
    +-------+-------+---------+
    6 rows in set (0.00 sec)

> 1.  取得工资最高的前五名

mysql> select ename, sal 
        -> from emp 
        -> order by sal desc 
        -> limit 5;
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | KING  | 5000.00 |
    | SCOTT | 3000.00 |
    | FORD  | 3000.00 |
    | JONES | 2975.00 |
    | BLAKE | 2850.00 |
    +-------+---------+
    5 rows in set (0.00 sec)

> 1.  取得工资最高的第6到第10名员工

mysql> select ename, sal 
        -> from emp 
        -> order by sal desc 
        -> limit 5, 5;
    +--------+---------+
    | ename  | sal     |
    +--------+---------+
    | CLARK  | 2450.00 |
    | ALLEN  | 1600.00 |
    | TURNER | 1500.00 |
    | MILLER | 1300.00 |
    | WARD   | 1250.00 |
    +--------+---------+
    5 rows in set (0.00 sec)

> 1.  查找最后入职的五名员工

> 1.  取得每个工资等级的员工数

mysql> select s.grade, count(*) 
        -> from emp e 
        -> join salgrade s 
        -> on e.sal >s.losal and e.sal <s.hisal 
        -> group by s.grade;
    +-------+----------+
    | grade | count(*) |
    +-------+----------+
    |     1 |        3 |
    |     3 |        2 |
    |     2 |        3 |
    |     4 |        3 |
    |     5 |        1 |
    +-------+----------+
    5 rows in set (0.00 sec)

> 1.  列出所有员工和领导的名字

> 1.  列出受雇日期早于上级的员工编号、姓名和部门名称

mysql> select e1.empno as '员工',e1.hiredate, e2.ename as '领导',e2.hiredate, e2.deptno, d.dname 
        -> from emp e1 
        -> join emp e2 
        -> on e1.mgr = e2.empno 
        -> join dept d 
        -> on e1.deptno = d.deptno 
        -> where e1.hiredate < e2.hiredate;
    +-------+------------+-------+------------+--------+------------+
    | 员工   | hiredate   | 领导  | hiredate   | deptno | dname      |
    +-------+------------+-------+------------+--------+------------+
    |  7369 | 1980-12-17 | FORD  | 1981-12-03 |     20 | RESEARCH   |
    |  7499 | 1981-02-20 | BLAKE | 1981-05-01 |     30 | SALES      |
    |  7521 | 1981-02-22 | BLAKE | 1981-05-01 |     30 | SALES      |
    |  7566 | 1981-04-02 | KING  | 1981-11-17 |     10 | RESEARCH   |
    |  7698 | 1981-05-01 | KING  | 1981-11-17 |     10 | SALES      |
    |  7782 | 1981-06-09 | KING  | 1981-11-17 |     10 | ACCOUNTING |
    +-------+------------+-------+------------+--------+------------+
    6 rows in set (0.00 sec)

> 1.  列出部门名称和部门员工的信息，同时列出没有员工的部门

mysql> select d.dname, e.* 
        -> from dept d 
        -> left join emp e 
        -> on d.deptno = e.deptno;
    +------------+-------+--------+-----------+------+------------+---------+---------+--------+
    | dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
    +------------+-------+--------+-----------+------+------------+---------+---------+--------+
    | ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    | ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    | ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    | RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    | RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
    | RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    | RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    | RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
    | SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    | SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
    | SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    | SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    | SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    | SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    | OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
    +------------+-------+--------+-----------+------+------------+---------+---------+--------+
    15 rows in set (0.00 sec)

> 1.  列出至少有5个员工的部门

mysql> select deptno 
        -> from emp 
        -> group by deptno 
        -> having count(*) >= 5;
    +--------+
    | deptno |
    +--------+
    |     20 |
    |     30 |
    +--------+
    2 rows in set (0.00 sec)

> 1.  列出所有岗位为“clerk”的的员工，以及部门名称和部门人数

> 1.  列出最低工资大于1500的岗位，以及岗位的人数

mysql> select job, count(*) as '人数' 
        -> from emp 
        -> group by job 
        -> having min(sal) > 1500;
    +-----------+------+
    | job       | 人数 |
    +-----------+------+
    | MANAGER   |    3 |
    | ANALYST   |    2 |
    | PRESIDENT |    1 |
    +-----------+------+
    3 rows in set (0.00 sec)

> 1.  列出岗位为“sales”的员工

> 1.  列出工资高于平均工资的员工，以及所在部门、上级领导和工资等级

mysql> select e.ename as '员工', ee.ename as '领导', e.sal, s.grade, e.deptno 
        -> from emp e 
        -> left join emp ee 
        -> on e.mgr = ee.empno 
        -> join salgrade s 
        -> on e.sal between s.losal and s.hisal 
        -> where e.sal > (select avg(sal) from emp);
    +-------+-------+---------+-------+--------+
    | 员工  | 领导  | sal     | grade | deptno |
    +-------+-------+---------+-------+--------+
    | FORD  | JONES | 3000.00 |     4 |     20 |
    | SCOTT | JONES | 3000.00 |     4 |     20 |
    | CLARK | KING  | 2450.00 |     4 |     10 |
    | BLAKE | KING  | 2850.00 |     4 |     30 |
    | JONES | KING  | 2975.00 |     4 |     20 |
    | KING  | NULL  | 5000.00 |     5 |     10 |
    +-------+-------+---------+-------+--------+
    6 rows in set (0.00 sec)

> 1.  列出与“scott”从事相同工作的员工及部门名称

mysql> select e.ename, d.dname 
        -> from emp e 
        -> join dept d 
        -> on e.deptno = d.deptno 
        -> where job = (select job from emp where ename = 'scott') and e.ename <> 'scott';
    +-------+----------+
    | ename | dname    |
    +-------+----------+
    | FORD  | RESEARCH |
    +-------+----------+
    1 row in set (0.00 sec)

> 1.  列出与部门30的员工的工资相等的其它部门的员工和工资

mysql> select ename, sal 
        -> from emp 
        -> where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;
    Empty set (0.00 sec)

> 1.  列出工资高于部门30的所有员工的工资的员工姓名、工资和部门名称

> 1.  列出每个部门的员工数量和平均工资

// 部门40没有人，采用count(*)结果为1，采用字段进行统计，忽略null值，结果为0
    mysql> select d.deptno, count(e.ename) as '员工数量', ifnull(avg(sal),0) as '平均工资' 
        -> from emp e 
        -> right join dept d 
        -> on e.deptno = d.deptno 
        -> group by d.deptno;
    +--------+----------+-------------+
    | deptno | 员工数量 | 平均工资    |
    +--------+----------+-------------+
    |     10 |        3 | 2916.666667 |
    |     20 |        5 | 2175.000000 |
    |     30 |        6 | 1566.666667 |
    |     40 |        0 |    0.000000 |
    +--------+----------+-------------+
    4 rows in set (0.00 sec)

> 1.  列出每个岗位的最低工资和最低工资的员工

> 1.  列出各个部门领导（岗位为manager）的最低工资

mysql> select deptno, min(sal) 
        -> from emp 
        -> where job = 'manager' group by deptno;
    +--------+----------+
    | deptno | min(sal) |
    +--------+----------+
    |     20 |  2975.00 |
    |     30 |  2850.00 |
    |     10 |  2450.00 |
    +--------+----------+
    3 rows in set (0.00 sec)

> 1.  列出员工给年薪，并从高到低进行排序

mysql> select ename, ((sal + ifnull(comm, 0)) * 12) as yearsal 
        -> from emp 
        -> order by yearsal desc;
    +--------+----------+
    | ename  | yearsal  |
    +--------+----------+
    | KING   | 60000.00 |
    | SCOTT  | 36000.00 |
    | FORD   | 36000.00 |
    | JONES  | 35700.00 |
    | BLAKE  | 34200.00 |
    | MARTIN | 31800.00 |
    | CLARK  | 29400.00 |
    | ALLEN  | 22800.00 |
    | WARD   | 21000.00 |
    | TURNER | 18000.00 |
    | MILLER | 15600.00 |
    | ADAMS  | 13200.00 |
    | JAMES  | 11400.00 |
    | SMITH  |  9600.00 |
    +--------+----------+
    14 rows in set (0.00 sec)

> 1.  列出员工领导的工资高于3000的员工和员工领导领导

mysql> select e1.ename as '员工', e2.ename as '领导', e2.sal 
        -> from emp e1 
        -> join emp e2 
        -> on e1.mgr = e2.empno 
        -> where e2.sal > 3000;
    +-------+------+---------+
    | 员工  | 领导 | sal     |
    +-------+------+---------+
    | JONES | KING | 5000.00 |
    | BLAKE | KING | 5000.00 |
    | CLARK | KING | 5000.00 |
    +-------+------+---------+
    3 rows in set (0.00 sec)

> 1.  列出部门名称中带“s”的部门员工工资总和和部门人数

mysql> select t.*, count(e.deptno), sum(ifnull(sal,0)) 
        -> from emp e 
        -> right join (select deptno, dname from dept where dname like '%s%') t 
        -> on e.deptno = t.deptno 
        -> group by t.deptno;
    +--------+------------+-----------------+--------------------+
    | deptno | dname      | count(e.deptno) | sum(ifnull(sal,0)) |
    +--------+------------+-----------------+--------------------+
    |     20 | RESEARCH   |               5 |           10875.00 |
    |     30 | SALES      |               6 |            9400.00 |
    |     40 | OPERATIONS |               0 |               0.00 |
    +--------+------------+-----------------+--------------------+
    3 rows in set (0.00 sec)