作业
数据准备:
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS salgrade;
CREATE TABLE dept
(DEPTNO int(2) not null ,
DNAME VARCHAR(14) ,
LOC VARCHAR(13),
primary key (DEPTNO)
);
CREATE TABLE emp
(EMPNO int(4) not null ,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT(4),
HIREDATE DATE DEFAULT NULL,
SAL DOUBLE(7,2),
COMM DOUBLE(7,2),
primary key (EMPNO),
DEPTNO INT(2)
)
;
CREATE TABLE salgrade
( GRADE INT,
LOSAL INT,
HISAL INT );
INSERT INTO dept ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO dept ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO dept ( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
commit;
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902, '1980-12-17'
, 800, NULL, 20);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20'
, 1600, 300, 30);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, 'WARD', 'SALESMAN', 7698, '1981-02-22'
, 1250, 500, 30);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839, '1981-04-02'
, 2975, NULL, 20);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28'
, 1250, 1400, 30);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01'
, 2850, NULL, 30);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839, '1981-06-09'
, 2450, NULL, 10);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19'
, 3000, NULL, 20);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL, '1981-11-17'
, 5000, NULL, 10);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08'
, 1500, 0, 30);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788, '1987-05-23'
, 1100, NULL, 20);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, 'JAMES', 'CLERK', 7698, '1981-12-03'
, 950, NULL, 30);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566, '1981-12-03'
, 3000, NULL, 20);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782, '1982-01-23'
, 1300, NULL, 10);
commit;
INSERT INTO salgrade ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO salgrade ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO salgrade ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO salgrade ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO salgrade ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
commit;
1、取得每个部门最高薪水的人员名称
-
“每个部门”,说明要用到分组查询;我们可以很简单地得到每个部门的最高薪水:
select max(sal), deptno
mysql> select deptno, max(sal) -> from emp -> group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+ 3 rows in set (0.00 sec)
-
已经找到了每个部门的最高薪水,接下来就只需要找到对应的员工名称 ename,把上表作为临时 t 表,那么条件就是:
t.depo = emp.deptno and t.max(sal) = emp.sal
mysql> SELECT -> e.ename, e.sal, e.deptno -> FROM -> emp e,(SELECT deptno, max(sal) as maxsal FROM emp GROUP BY deptno) t -> WHERE -> t.maxsal = e.sal and t.deptno = e.deptno; +-------+---------+--------+ | ename | sal | deptno | +-------+---------+--------+ | BLAKE | 2850.00 | 30 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | +-------+---------+--------+ 4 rows in set (0.00 sec)
2、哪些人的薪水在部门的平均薪水之上
-
同样,可以很简单地查到每个部门的平均薪水:
mysql> SELECT -> deptno, AVG(sal) -> FROM -> emp -> GROUP BY -> deptno; +--------+-------------+ | deptno | AVG(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
-
把上表当做一个临时表 t,在 emp 表中查找出薪资大于部门平均水平的员工名即可:
mysql> SELECT -> e.ename, e.sal, e.deptno -> FROM -> emp e,(SELECT deptno, AVG(sal) as avgsal FROM emp GROUP BY deptno) t -> WHERE -> t.avgsal < e.sal and t.deptno = e.deptno; +-------+---------+--------+ | ename | sal | deptno | +-------+---------+--------+ | ALLEN | 1600.00 | 30 | | JONES | 2975.00 | 20 | | BLAKE | 2850.00 | 30 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | +-------+---------+--------+ 6 rows in set (0.00 sec)
3、取得部门中(所有人的)平均的薪水等级
-
与 salgrade 表连接,按照部门进行分组,查询每个部门里边人员的薪资等级
e.sal between s.losal and s.hisal
:mysql> 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 | +--------+---------+--------+-------+ | SMITH | 800.00 | 20 | 1 | | ALLEN | 1600.00 | 30 | 3 | | WARD | 1250.00 | 30 | 2 | | JONES | 2975.00 | 20 | 4 | | MARTIN | 1250.00 | 30 | 2 | | BLAKE | 2850.00 | 30 | 4 | | CLARK | 2450.00 | 10 | 4 | | SCOTT | 3000.00 | 20 | 4 | | KING | 5000.00 | 10 | 5 | | TURNER | 1500.00 | 30 | 3 | | ADAMS | 1100.00 | 20 | 1 | | JAMES | 950.00 | 30 | 1 | | FORD | 3000.00 | 20 | 4 | | MILLER | 1300.00 | 10 | 2 | +--------+---------+--------+-------+ 14 rows in set (0.00 sec)
-
按照 deptno 进行分组,计算每组里边的平均 grade:
mysql> SELECT -> e.deptno, AVG(s.grade) -> FROM -> emp e -> INNER 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 | +--------+--------------+ 3 rows in set (0.00 sec)
4、不准用组函数( Max),取得最高薪水(给出两种解决方案)
-
第一种方法:按照 sal 排序,取第一个:
mysql> SELECT -> sal -> FROM -> emp -> ORDER BY -> sal desc -> LIMIT -> 1; +---------+ | sal | +---------+ | 5000.00 | +---------+ 1 row in set (0.00 sec)
-
表的自连接
mysql> 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 | +---------+ 1 row in set (0.00 sec)
5、取得平均薪水最高的部门的部门编号(至少给出两种解决方案)
第一种方案:降序取第一个
-
先要求得每个部门的平均薪水
mysql> SELECT -> deptno, AVG(sal) -> FROM -> emp -> GROUP BY -> deptno; +--------+-------------+ | deptno | AVG(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
-
在进行排序,找到最高平均薪水最高的部门编号:
mysql> SELECT -> t.deptno -> FROM -> (SELECT deptno, AVG(sal) as avgsal FROM emp GROUP BY deptno) t -> ORDER BY -> t.avgsal desc -> LIMIT -> 1; +--------+ | deptno | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)
第二种方案:max
-
同样,先要求得每个部门的平均薪水
mysql> SELECT -> deptno, AVG(sal) -> FROM -> emp -> GROUP BY -> deptno; +--------+-------------+ | deptno | AVG(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
-
使用 max
mysql> SELECT -> MAX(t.avgsal) -> FROM -> (SELECT deptno, AVG(sal) as avgsal FROM emp GROUP BY deptno) t; +---------------+ | MAX(t.avgsal) | +---------------+ | 2916.666667 | +---------------+ 1 row in set (0.00 sec)
-
分组选择出部门号
mysql> SELECT -> deptno -> FROM -> emp -> GROUP BY -> deptno -> HAVING -> avg(sal) = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t); +--------+ | deptno | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)
6、取得平均薪水最高的部门的部门名称
-
先要求得每个部门的平均薪水
mysql> SELECT -> deptno, AVG(sal) -> FROM -> emp -> GROUP BY -> deptno; +--------+-------------+ | deptno | AVG(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
-
由于要查询的是部门名称,位于 dept 表,因此要进行表的等值连接;在进行排序,找到最高平均薪水最高的部门名称:
mysql> SELECT -> d.dname -> FROM -> dept d -> INNER JOIN -> (SELECT deptno, AVG(sal) as avgsal FROM emp GROUP BY deptno) t -> ON -> d.deptno = t.deptno -> LIMIT -> 1; +------------+ | dname | +------------+ | ACCOUNTING | +------------+ 1 row in set (0.00 sec)
7、求平均薪水的等级最低的部门的部门名称
-
先要求得每个部门的平均薪水等级
mysql> 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 | +--------+--------------+ 3 rows in set (0.00 sec)
-
把上表看成一个临时表,需要查到部门名称,需要表连接 dept 表:
mysql> SELECT -> d.dname -> FROM -> dept d -> INNER JOIN -> (select -> e.deptno, AVG(s.grade) as avgsal -> from -> emp e -> join -> salgrade s -> on -> e.sal between s.losal and s.hisal -> GROUP BY -> e.deptno) t -> ON -> d.deptno = t.deptno -> ORDER BY -> avgsal -> LIMIT -> 1; +-------+ | dname | +-------+ | SALES | +-------+ 1 row in set (0.00 sec)
8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名
-
找出普通员工(员工代码没有在 mgr 字段上出现的),可以反向思维,找出在 mgr 字段里边的 empno,那么不在此字段里边的就是普通员工:
-- 1.找出在 mgr 里边的员工 mysql> SELECT DISTINCT -> mgr -> FROM -> emp -> WHERE -> mgr is not NULL; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | 7788 | | 7782 | +------+ 6 rows in set (0.00 sec) -- 2.找出不再 mgr 里边的员工的最高薪水 mysql> SELECT -> MAX(sal) -> FROM -> emp -> WHERE -> empno NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr is not NULL); +----------+ | MAX(sal) | +----------+ | 1600.00 | +----------+ 1 row in set (0.00 sec)
-
找出高于 1600 的员工姓名即可
mysql> SELECT -> 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)); +-------+---------+ | ename | sal | +-------+---------+ | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ 6 rows in set (0.00 sec)
9、取得薪水最高的前五名员工
按照 sal 降序排序,选择前 5 个即可:
mysql> SELECT
-> ename, sal
-> FROM
-> emp
-> ORDER BY
-> sal desc
-> LIMIT
-> 5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
10、取得薪水最高的第六到第十名员工
同样是进行降序排序,这次需要注意的是 limit 后面的数字:
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)
11、取得最后入职的 5 名员工
日期 date 格式也可以升序降序:
mysql> SELECT
-> ename, hiredate
-> FROM
-> emp
-> ORDER BY
-> hiredate desc
-> LIMIT
-> 5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)
12、取得每个薪水等级有多少员工
-
找出每个员工的薪资等级
mysql> select -> e.ename, e.sal, s.grade -> from -> emp e -> join -> salgrade s -> on -> e.sal between s.losal and s.hisal; +--------+---------+-------+ | ename | sal | grade | +--------+---------+-------+ | SMITH | 800.00 | 1 | | ALLEN | 1600.00 | 3 | | WARD | 1250.00 | 2 | | JONES | 2975.00 | 4 | | MARTIN | 1250.00 | 2 | | BLAKE | 2850.00 | 4 | | CLARK | 2450.00 | 4 | | SCOTT | 3000.00 | 4 | | KING | 5000.00 | 5 | | TURNER | 1500.00 | 3 | | ADAMS | 1100.00 | 1 | | JAMES | 950.00 | 1 | | FORD | 3000.00 | 4 | | MILLER | 1300.00 | 2 | +--------+---------+-------+ 14 rows in set (0.00 sec)
-
按照 grade 进行分组,并统计 count
mysql> select -> s.grade, COUNT(*) -> from -> emp e -> join -> salgrade s -> on -> e.sal between s.losal and s.hisal -> GROUP BY -> s.grade; +-------+----------+ | grade | COUNT(*) | +-------+----------+ | 1 | 3 | | 2 | 3 | | 3 | 2 | | 4 | 5 | | 5 | 1 | +-------+----------+ 5 rows in set (0.00 sec)