该文章只是个人按照题目写的SQL语句，可能某个语法很冗余或有更优的方案…

环境声明

系统: Windows10家庭中文版
硬件: 16G内存、8核CPU
集成环境: 小皮面板8.1.1.3
Mysql版本: 5.7.26
强烈推荐学习视频: B站-Java酱
数据来源: 动力节点

环境部署

导入数据

数据库文件下载完成后，创建一个数据库，并将该文件导入到数据库中

-- 创建数据库
create database test;
use test

-- 导入数据
source 数据库文件路径

-- 导入成功后
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept           |
| emp            |
| salgrade       |
+----------------+

dept表

-- dept表结构
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

-- dept表数据
部门编号    部门名称       地址
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

salgrade表

-- salgrade表结构
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |
| LOSAL | int(11) | YES  |     | NULL    |       |
| HISAL | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

-- salgrade表数据
薪资等级   最低    最高
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

emp表

-- emp表结构
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

-- emp表数据
员工编号  员工姓名  员工工作  上级编号   入职时间     工资       xx      部门编号   
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

第一题

题目

取得每个部门最高薪资的人员名称

-- 效果
+-------+--------+---------+
| ename | deptno | max_sal |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

实现

-- 先把每个部门的最高薪资与部门编号取出 (通过部门编号分组)
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+

-- 再将emp表与上面查询结果做内连接
-- 条件: a表的部门编号=b表的部门编号，a表的员工工资=b表的最大工资
select
	a.ename,b.*
from
	emp a
join
	(select deptno,max(sal) max_sal from emp group by deptno) b
on
	a.deptno=b.deptno and a.sal=b.max_sal;

第二题

题目

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

-- 效果
+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

实现

-- 先把每个部门的平均薪资与部门编号取出 (通过部门编号分组)
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

-- 再将emp表与上面查询结果做内连接
-- 条件: a表的员工工资>b表的平均工资，a表的部门编号=b表的部门编号
select
	a.ename,a.sal
from
	emp a
join
	(select deptno,avg(sal) avg_sal from emp group by deptno) b
on
	a.sal>b.avg_sal and a.deptno=b.deptno;

第三题

题目

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

-- 效果
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)

实现

-- 先把每个部门的员工名、部门编号、员工薪资等级取出
-- 将emp表与salgrade表做内连接
mysql> select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal>=s.losal and e.sal <= s.hisal;
+--------+--------+-------+
| ename  | deptno | grade |
+--------+--------+-------+
| SMITH  |     20 |     1 |
| ALLEN  |     30 |     3 |
| WARD   |     30 |     2 |
| JONES  |     20 |     4 |
| MARTIN |     30 |     2 |
| BLAKE  |     30 |     4 |
| CLARK  |     10 |     4 |
| SCOTT  |     20 |     4 |
| KING   |     10 |     5 |
| TURNER |     30 |     3 |
| ADAMS  |     20 |     1 |
| JAMES  |     30 |     1 |
| FORD   |     20 |     4 |
| MILLER |     10 |     2 |
+--------+--------+-------+
14 rows in set (0.00 sec)


-- 再将上表的部门编号做分组，取出部门编号与薪资平均等级
select 
	e.deptno,avg(s.grade)
from
	emp e 
join 
	salgrade s 
on
	e.sal>=s.losal and e.sal <= s.hisal 
group by 
	e.deptno;

第四题

题目

不准用分组函数（Max），取得最高薪水（给出两种解决方案）

-- 效果
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

实现

-- 方法1: 对sal进行降序排序后，取第一个值
select 
	sal 
from
	emp
order by
	sal
desc limit 1;

-- 方法2: 对表进行自连接，获取到小于最大值的全部值，再将结果与emp表对比，不存在就拿出来
select
	sal
from
	emp
where
	sal not in
	(select a.sal from emp a join emp b on a.sal<b.sal);

第五题

题目

取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

-- 效果
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

实现

-- 方法1: 获取平均工资后进行降序排序，取第一个值就是最大值
select e.deptno,avg(e.sal) avg_sal from emp e group by deptno order by avg_sal desc limit 1;

-- 方法2
-- 先获取到每个部门的平均工资
select deptno,avg(sal) avg_sal from emp group by deptno;
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

-- 再取平均工资中的最大值
select max(t.avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno) t;
+----------------+
| max(t.avg_sal) |
+----------------+
|    2916.666667 |
+----------------+

-- 再通过分组查到每个部门的平均工资，再与上面获取到的最大值进行对比
select
	deptno,avg(sal) avgsal
from
	emp
group by
	deptno 
having
	avgsal=(select max(t.avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno) t);

第六题

题目

取得平均薪水最高的部门的部门名称

-- 效果
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+

实现

-- 方法1: 
-- 先获取平均工资后进行降序排序，取第一个值就是最大值
-- 再使用上面结果与dept表进行内连接
select 
	d.dname 
from 
	dept d 
join 
	(select e.deptno,avg(e.sal) avg_sal from emp e group by deptno order by avg_sal desc limit 1) t 
on 
d.deptno=t.deptno;

-- 方法2: 以部门名字分组
select
	d.dname,avg(e.sal) avg_sal
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
group by
	d.dname
order by 
	avg_sal desc
limit 1;

第七题

题目

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

-- 效果
+-------+
| dname |
+-------+
| SALES |
+-------+

实现

-- 方法1: 
-- 先获取平均工资后进行升序排序，取第一个值就是最小值
-- 再使用上面结果与dept表进行内连接
select 
	d.dname
from 
	dept d 
join 
	(select e.deptno,avg(e.sal) avg_sal from emp e group by deptno order by avg_sal limit 1) t 
on 
d.deptno=t.deptno;

-- 方法2: 以部门名称分组
select
	d.dname,avg(e.sal) avg_sal
from
	emp e
join
	dept d
on
	d.deptno=e.deptno
group by
	d.dname
order by
	avg_sal
limit
	1;

第八题

题目

取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名

-- 效果
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

实现

-- 先找出哪些人是领导 (员工代码在mgr字段出现的)(distinct去重)
select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+

-- 再找出普通员工中最高薪资 (员工代码不在mgr字段出现的就是普通员工)
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
+---------+
| max_sal |
+---------+
| 1600.00 |
+---------+

-- 再将普通员工的最高薪资与领导薪资对比
select
	ename
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     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

实现

1 2	-- 按照工资降序排序后取得前五个值 select ename,sal from emp order by sal desc limit 0,5;

第十题

题目

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

-- 效果
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
+--------+---------+

实现

1 2	-- 按照工资降序排序后取得6~10的值 select ename,sal from emp order by sal desc limit 5,5;

第十一题

题目

取得最后入职的 5 名员工

-- 效果
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+

实现

1 2	-- 按照入职时间降序排序后取前五个值 select ename,hiredate from emp order by hiredate desc limit 0,5;

第十二题

题目

取得每个薪水等级有多少员工

-- 效果
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

实现

-- 通过内连接获取到每个员工的薪资等级，再通过对薪资等级的分组统计获得数据
select 
	s.grade grade,count(*) 
from 
	emp e 
join
	salgrade s 
on 
	e.sal >= s.losal and e.sal<=s.hisal 
group by 
	grade;

第十三题

题目

1
2

-- 效果

实现

--

第十四题

题目

列出所有员工及领导的姓名

-- 效果
+--------+--------+
| 员工   | 领导   |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
14 rows in set (0.00 sec)

实现

1 2	-- 使用自连接，将员工的mgr与领导的empno匹配，获取到结果 select a.ename 员工,b.ename 领导 from emp a left join emp b on a.mgr=b.empno;

第十五题

题目

列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

-- 效果
+--------------+-----------+------------+
| 员工编号     | 员工名    | 部门       |
+--------------+-----------+------------+
|         7369 | SMITH     | RESEARCH   |
|         7499 | ALLEN     | SALES      |
|         7521 | WARD      | SALES      |
|         7566 | JONES     | RESEARCH   |
|         7698 | BLAKE     | SALES      |
|         7782 | CLARK     | ACCOUNTING |
+--------------+-----------+------------+

实现

-- 获取员工与对应领导的入职时间
select a.empno 员工编号,a.hiredate 员工入职时间 ,b.ename 领导,b.hiredate 领导入职时间 from emp a join emp b on a.mgr=b.empno;
+--------------+--------------------+--------+--------------------+
| 员工编号     | 员工入职时间       | 领导   | 领导入职时间       |
+--------------+--------------------+--------+--------------------+
|         7369 | 1980-12-17         | FORD   | 1981-12-03         |
|         7499 | 1981-02-20         | BLAKE  | 1981-05-01         |
|         7521 | 1981-02-22         | BLAKE  | 1981-05-01         |
|         7566 | 1981-04-02         | KING   | 1981-11-17         |
|         7654 | 1981-09-28         | BLAKE  | 1981-05-01         |
|         7698 | 1981-05-01         | KING   | 1981-11-17         |
|         7782 | 1981-06-09         | KING   | 1981-11-17         |
|         7788 | 1987-04-19         | JONES  | 1981-04-02         |
|         7844 | 1981-09-08         | BLAKE  | 1981-05-01         |
|         7876 | 1987-05-23         | SCOTT  | 1987-04-19         |
|         7900 | 1981-12-03         | BLAKE  | 1981-05-01         |
|         7902 | 1981-12-03         | JONES  | 1981-04-02         |
|         7934 | 1982-01-23         | CLARK  | 1981-06-09         |
+--------------+--------------------+--------+--------------------+
13 rows in set (0.00 sec)

-- 使用自连接将员工与对应领导的入职时间取出来
-- 再通过员工表的deptno与dept表的deptno做为内连接的条件
-- 最后将员工入职时间与领导入职时间做对比
select 
	a.empno 员工编号,a.ename 员工名,d.dname 部门 
from 
	emp a 
join 
	emp b 
on 
	a.mgr=b.empno 
join 
	dept d 
on 
	a.deptno=d.deptno 
where 
	a.hiredate<b.hiredate;

第十六题

题目

列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

-- 效果
+------------+--------+
| dname      | ename  |
+------------+--------+
| RESEARCH   | SMITH  |
| SALES      | ALLEN  |
| SALES      | WARD   |
| RESEARCH   | JONES  |
| SALES      | MARTIN |
| SALES      | BLAKE  |
| ACCOUNTING | CLARK  |
| RESEARCH   | SCOTT  |
| ACCOUNTING | KING   |
| SALES      | TURNER |
| RESEARCH   | ADAMS  |
| SALES      | JAMES  |
| RESEARCH   | FORD   |
| ACCOUNTING | MILLER |
| OPERATIONS | NULL   |
+------------+--------+
15 rows in set (0.00 sec)

实现

-- 使用外连接获取，条件为部门名称相等
select
	d.dname,e.ename
from
	emp e
right join 
	dept d
on
	d.deptno=e.deptno;

第十七题

题目

列出至少有 5 个员工的所有部门

-- 效果
+----------+-----+
| dname    | num |
+----------+-----+
| RESEARCH |   5 |
| SALES    |   6 |
+----------+-----+

实现

-- 先按照员工进行分组查询，将员工人数大于等于5的部门编号取出
select deptno,count(*) num from emp group by deptno having num>=5;
+--------+-----+
| deptno | num |
+--------+-----+
|     20 |   5 |
|     30 |   6 |
+--------+-----+

-- 再将取到的表设置别名为t，再将t表与dept表做内连接
select
	d.dname,t.num
from
	dept d
join
	(select deptno,count(*) num from emp group by deptno having num>=5) t
on
	t.deptno=d.deptno;

第十八题

题目

列出薪金比”SMITH”多的所有员工信息

-- 效果
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

实现

-- 使用子查询获取到SMITH的工资，将查询结果作为where条件对比
select
	*
from
	emp
where
	sal>(select sal from emp where ename='SMITH');

第十九题

题目

列出所有”CLERK”(办事员)的姓名及其部门名称,部门的人数.

-- 效果
+--------+------------+-----+
| ename  | dname      | num |
+--------+------------+-----+
| SMITH  | RESEARCH   |   5 |
| ADAMS  | RESEARCH   |   5 |
| JAMES  | SALES      |   6 |
| MILLER | ACCOUNTING |   3 |
+--------+------------+-----+
4 rows in set (0.00 sec)

实现

-- 先获取办事员的姓名、部门名、部门编号
-- 将emp与dept表做内连接，条件为deptno相等，再使用where设置emp表的job字段为CLERK
select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno=d.deptno where e.job='CLERK';
+--------+------------+--------+
| ename  | dname      | deptno |
+--------+------------+--------+
| SMITH  | RESEARCH   |     20 |
| ADAMS  | RESEARCH   |     20 |
| JAMES  | SALES      |     30 |
| MILLER | ACCOUNTING |     10 |
+--------+------------+--------+

-- 再获取每个部门的人数
select deptno,count(*) num from emp group by deptno;
+--------+-----+
| deptno | num |
+--------+-----+
|     10 |   3 |
|     20 |   5 |
|     30 |   6 |
+--------+-----+

-- 再将上面两个查询结果做内连接
select
	t1.ename,t1.dname,t2.num
from
	(select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno=d.deptno where e.job='CLERK') t1
join
	(select deptno,count(*) num from emp group by deptno) t2
on t1.deptno=t2.deptno;

第二十题

题目

列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

-- 效果
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

实现

-- 用job做分组，再将每个工作的员工最小工资做比较
select
	job,count(*)
from
	emp
group by
	job
having
	min(sal)>1500;

第二一题

题目

列出在部门”SALES”<销售部>工作的员工的姓名,假定不知道销售部的部门编号

-- 效果
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)

实现

-- 先获取到SALES的部门编号
select deptno from dept where dname='SALES';
+--------+
| deptno |
+--------+
|     30 |
+--------+

-- 再将上面的查询结果作为where条件
select
	ename
from
	emp
where
	deptno=(select deptno from dept where dname='SALES');


-- 或者使用内连接(不推荐)
select
	e.ename
from
	emp e
join
	(select deptno from dept where dname='SALES') t
on e.deptno=t.deptno;

第二二题

题目

列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级

-- 效果(注意KING是没有上级领导的)
+--------+------------+--------+--------+
| 员工   | 部门       | 领导   | 等级   |
+--------+------------+--------+--------+
| JONES  | RESEARCH   | KING   |      4 |
| BLAKE  | SALES      | KING   |      4 |
| CLARK  | ACCOUNTING | KING   |      4 |
| SCOTT  | RESEARCH   | JONES  |      4 |
| FORD   | RESEARCH   | JONES  |      4 |
| KING   | ACCOUNTING | NULL   |      5 |
+--------+------------+--------+--------+
6 rows in set (0.00 sec)

实现

-- 先将员工与对应的部门名取出
-- 再将取出的内容与emp表做左自连接，得到员工与领导的对应信息
-- 再将取出的内容与salgrade表做薪资等级运算(左连接)
-- 再使用where语句过滤大于平均薪资的员工信息
select
	e.ename 员工,d.dname 部门,l.ename 领导,s.grade 等级
from
	emp e
left join
	dept d
on
	e.deptno=d.deptno
left join
	emp l
on
	l.empno=e.mgr
left join
	salgrade s
on
	e.sal >= s.losal and e.sal <= s.hisal
where
	e.sal>(select avg(sal) from emp);

第二三题

题目

列出与”SCOTT”从事相同工作的所有员工及部门名称

-- 效果
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

实现

-- 先将emp表与dept表做内连接
-- 再设置where条件为job等于SCOTT的JOB名
-- 再过滤掉SCOTT本身
select
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
where
	e.job=(select job from emp where ename='SCOTT')
and
	e.ename!='SCOTT';

第二四题

题目

列出薪金等于部门 30 中员工的薪金，的其他员工的姓名和薪金

1 2	-- 效果(没结果) Empty set (0.00 sec)

实现

-- 先获取到部门编号为30的工资
mysql> select distinct sal from emp where deptno=30;
+---------+
| sal     |
+---------+
| 1760.00 |
| 1375.00 |
| 3135.00 |
| 1650.00 |
| 1045.00 |
+---------+

-- 再将部门编号非30的员工姓名、工资取出，使用in来判断是否包含在里面
select
	ename,sal
from
	emp
where
	sal in (select distinct sal from emp where deptno=30)
and
	deptno!=30;

第二五题

题目

列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名称

-- 效果
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+
4 rows in set (0.00 sec)

实现

-- 先算出30部门的员工最高薪资
mysql> select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+

-- 再将emp表与dept表做内连接
-- where条件为员工工资大于30部门的最高员工工资
select
	e.ename,e.sal,d.dname
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
where
	e.sal>(select max(sal) from emp where deptno=30);

第二六题

题目

列出在每个部门工作的员工数量,平均工资和平均服务期限

-- 效果
+--------+----------------+-------------+----------+
| deptno | count(e.ename) | avg_sal     | avg_dete |
+--------+----------------+-------------+----------+
|     10 |              3 | 3208.333333 |  40.0000 |
|     20 |              5 | 2392.500000 |  37.8000 |
|     30 |              6 | 1723.333333 |  40.3333 |
|     40 |              0 |    0.000000 |   0.0000 |
+--------+----------------+-------------+----------+
4 rows in set (0.00 sec)

实现

-- 将emp表与dept表做外连接，条件为deptno，再将结果以d.depeno做分组
-- 使用dept表因为有的部门没有员工，但也要显示在查询结果中
select
	d.deptno,count(e.ename),ifnull(avg(e.sal),0) avg_sal,ifnull(avg(timestampdiff(YEAR,e.hiredate,now())),0) avg_dete
from
	emp e
right join
	dept d
on
	d.deptno=e.deptno
group by
	d.deptno;

第二七题

题目

列出所有员工的姓名、部门名称和工资

-- 效果
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| SMITH  | RESEARCH   |  800.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| JONES  | RESEARCH   | 2975.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| CLARK  | ACCOUNTING | 2450.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| KING   | ACCOUNTING | 5000.00 |
| TURNER | SALES      | 1500.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| JAMES  | SALES      |  950.00 |
| FORD   | RESEARCH   | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)

实现

-- emp表与dept表做内连接，条件为deptno
select
	e.ename,d.dname,e.sal
from
	emp e
join
	dept d
on
	e.deptno=d.deptno;

第二八题

题目

列出所有部门的详细信息和人数

-- 效果
+--------+------------+----------+----------------+
| deptno | dname      | loc      | count(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+
4 rows in set (0.00 sec)

实现

-- 先获取到dept的数据，再与emp表做左连接
-- 再将查询结果做分组

select
	d.*,count(e.ename)
from
	dept d
left join
	emp e
on
	d.deptno=e.deptno
group by
	d.deptno,d.dname,d.loc;

第二九题

题目

列出各种工作的最低工资及从事此工作的雇员姓名

-- 效果
+--------+-----------+---------+
| ename  | job       | min_sal |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+
7 rows in set (0.00 sec)

实现

-- 先获取到每个工作的最低工资
select
	job,min(sal) min_sal
from
	emp
group by
	job;
+-----------+---------+
| job       | min_sal |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+

-- 再将上面查询结果与emp表做自连接
-- 条件为job=job and min_sal=sal
select
	e.ename,t.*
from
	emp e
join
	(select job,min(sal) min_sal from emp group by job) t
on
	e.job=t.job
and
	e.sal=t.min_sal;

第三十题

题目

列出各个部门的 MANAGER(领导)的最低薪金

-- 效果
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

实现

-- 先把job为MANAGER的部门编号与最小工资取出
-- 再以部门编号进行分组
select
	deptno,min(sal)
from
	emp
where
	job='MANAGER'
group by
	deptno;

第三一题

题目

列出所有员工的年工资,按年薪从低到高排序

-- 效果
+--------+----------+
| ename  | sal      |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| WARD   | 15000.00 |
| MARTIN | 15000.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| ALLEN  | 19200.00 |
| CLARK  | 29400.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)

实现

-- 将工资*12，再进行排序
select
	ename,sal*12 sal
from
	emp
order by
	sal;

第三二题

题目

求出员工领导的薪水超过 3000 的员工名称与领导名称

-- 效果
+--------+--------+
| 员工   | 领导   |
+--------+--------+
| JONES  | KING   |
| BLAKE  | KING   |
| CLARK  | KING   |
+--------+--------+
3 rows in set (0.00 sec)

实现

-- 使用自连接查出员工与领导的关系
-- 再找出工资大于3000的领导
select
	a.ename 员工,b.ename 领导,b.sal
from
	emp a
join
	emp b
on
	a.mgr=b.empno
where
	b.sal>3000;

第三三题

题目

求出部门名称中,带’S’字符的部门、员工的工资合计、部门人数

-- 效果
+------------+----------+----------------+
| dname      | sum(sal) | count(e.ename) |
+------------+----------+----------------+
| OPERATIONS |     NULL |              0 |
| RESEARCH   | 10875.00 |              5 |
| SALES      |  9400.00 |              6 |
+------------+----------+----------------+
3 rows in set (0.00 sec)

实现

-- 先查出与员工对应的部门名
-- 再过滤出包含S的部门
-- 再对部门进行分组
select
	d.dname,sum(sal),count(e.ename)
from
	emp e
right join
	dept d
on
	e.deptno=d.deptno
where
	d.dname like "%S%"
group by
	d.dname;

第三四题

题目

给任职日期超过 30 年的员工加薪 10%

-- 效果
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  880.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1760.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1375.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 3272.50 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1375.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 3135.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2695.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3300.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5500.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1650.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1210.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 1045.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3300.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1430.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

实现

-- 获取时间差
-- 语法: timestampdiff(YEAR,时间1,时间2) -- 返回两个时间之间的年差
select timestampdiff(YEAR,hiredate,now()) 任职时间 from emp;
+--------------+
| 任职时间     |
+--------------+
|           41 |
|           41 |
|           41 |
|           40 |
|           40 |
|           40 |
|           40 |
|           34 |
|           40 |
|           40 |
|           34 |
|           40 |
|           40 |
|           40 |
+--------------+

-- 再将上面的条件设为update的where条件
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

30;
```