MySQL经典33题，DQL语句硬核专项练习！！！

话不多说，直接导入三张表： 员工表：em

部门表：dept

薪资等级表：salgrade

以下DQL语句是基于上面三张表进行的。

题目

• 1、取得每个部门最高薪水的人员名称
• 2、哪些人的薪水在部门的平均薪水之上
• 3、取得部门中（所有人的）平均的薪水等级
• 4、不准用组函数（Max），取得最高薪水
• 5、取得平均薪水最高的部门的部门编号
• 6、取得平均薪水最高的部门的部门名称
• 7、求平均薪水的等级最低的部门的部门名称
• 8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的 最高薪水还要高的领导人姓名
• 9、取得薪水最高的前五名员工
• 10、取得薪水最高的第六到第十名员工
• 11、取得最后入职的 5 名员工
• 12、取得每个薪水等级有多少员工
• 13、列出所有员工及领导的姓名
• 14、列出受雇日期早于其直接上级的所有员工的编号,姓名, 部门名称
• 15、列出部门名称和这些部门的员工信息,同时列出那些没有 员工的部门.
• 16、列出至少有 5 个员工的所有部门
• 17、列出薪金比"SMITH"多的所有员工信息.
• 18、列出所有"CLERK"(办事员)的姓名及其部门名称,部门 的人数.
• 19、列出最低薪金大于 1500 的各种工作及从事此工作的全 部雇员人数.
• 20、列出在部门"SALES"<销售部>工作的员工的姓名,假定 不知道销售部的部门编号.
• 21、列出薪金高于公司平均薪金的所有员工,所在部门,上级 领导,雇员的工资等级.
• 22、列出与"SCOTT"从事相同工作的所有员工及部门名称.
• 23、列出薪金等于部门 30 中员工的薪金的其他员工的姓名 和薪金.部门名称
• 24、列出薪金高于在部门 30 工作的所有员工的薪金的员工 姓名和薪金.部门名称.
• 25、列出在每个部门工作的员工数量,平均工资和平均服务期 限.
• 26、列出所有员工的姓名、部门名称和工资。
• 27、列出所有部门的详细信息和人数
• 28、列出各种工作的最低工资及从事此工作的雇员姓名
• 29、列出各个部门的 MANAGER(领导)的最低薪金
• 30、列出所有员工的年工资,按年薪从低到高排序
• 31、求出员工领导的薪水超过 3000 的员工名称与领导名称
• 32、求出部门名称中,带'S'字符的部门员工的工资合计、部门 人数.
• 33、给任职日期超过 30 年的员工加薪 10%.

1、取得每个部门最高薪水的人员名称

思路： 第一步：取得每个部门最高薪水（部门分组，求薪水最大值）

第二步：将第一步的表当作临时表与emp表做连接 连接条件e.deptno=t.deptno and e.sal=t.maxsal

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

思路： 第一步：找出部门的平均薪资

第二步：找出高于平均薪资的人

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

tip:

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

思路： 第一步：找出每个人的薪水等级

第二步：找出薪水等级的平均值

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

思路： 将薪水降序排列，用limit取第一个数

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

思路： 第一步：取得每个部门的平均薪水

第二步：找出以上结果中avgsal最大的值。

第三步：找出部门编号

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

方案一思路： 第一步：取得每个部门的平均薪水

第二步：取平均薪水最高的部门

第三步：找出部门名称

方案二：

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

tip:

平均薪水最低的对应的等级一定是最低的

思路： 第一步：平均薪水最低的

第二步：找出对应的等级

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

思路： 第一步：“普通员工的最高薪水”还要高的一定是领导！找出领导

第二步：不在上面的都是普通员工

第三步：找出高于1600的

9、取得薪水最高的前五名员工

思路： 将薪水降序排列limit取前五

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

思路： 将薪水降序排列limit取6到10名

11、取得最后入职的 5 名员工

思路： 将入职日期降序排，取前五

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

思路： 第一步：找出每个员工的薪水等级

第二步：进行分组

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

tip:

员工的领导编号等于领导的员工编号

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

思路： 将员工表看成一个员工表 a,领导表 b

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

思路： 员工表和部门表进行外连接（此处使用右外连接）

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

17、列出薪金比"SMITH"多的所有员工信息.

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

思路： 第一步：找出job是CLERK的所有的人名和部门名称:

第二步：每个部门的人数

第三步：将上边两个表做连接：

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

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

思路： 第一步找出销售部门编号：

第二步：知道销售部的部门编号找出员工姓名：

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

select a.ename '姓名',d.dname '部门', b.ename '上级领导',s.grade '工资等级'
from emp a
join dept d
on a.deptno=d.deptno and a.sal>(select avg(sal)
from emp)
left join emp b
on a.mgr=b.empno
join salgrade s
on a.sal between s.losal and hisal;

22、列出与"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';

23、列出薪金等于部门 30 中员工的薪金的其他员工的姓名 和薪金.部门名称

思路： 第一步：列出部门30的员工薪资

select distinct sal,deptno from emp where deptno=30;

第二步：找出薪资等于上表的员工信息

select e.sal,e.ename,d.dname
from emp e
join dept d
on e.deptno=d.deptno
where e.sal in (select distinct sal from emp where deptno=30) and e.deptno<> 30;

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

select e.sal,e.ename,d.dname
from emp e
join dept d
on e.deptno=d.deptno
where e.sal > (select max(sal) from emp where deptno=30) and e.deptno<> 30;

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

tip:

在mysql当中怎么计算两个日期的“年差”，差了多少年？ TimeStampDiff(间隔类型, 前一个日期, 后一个日期) timestampdiff(YEAR, hiredate, now()) 间隔类型： SECOND 秒， MINUTE 分钟， HOUR 小时， DAY 天， WEEK 星期 MONTH 月， QUARTER 季度， YEAR 年

select d.dname,d.deptno,count(e.ename) ecount,ifnull(avg(e.sal),0) avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())),0) as avgservicetime
from emp e
join dept d
on e.deptno=d.deptno
group by d.deptno;

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

select e.ename,d.dname,e.sal
from emp e
join dept d
on e.deptno=d.deptno;

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

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

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

思路： 第一步：按工作分组，找出各种工作的最低工资

select min(sal),job
from emp
group by job;

第二步：找出从事工作的雇员信息

select e.*
from emp e
join (select min(sal) minsal,job from emp group by job) t
on e.job=t.job and e.sal=t.minsal;

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

select job,deptno,min(sal)
from emp
where job='MANAGER'
group by deptno;

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

select ename,(sal+ifnull(comm,0)) *12 incone
from emp
order by incone asc;

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

select a.ename '员工',b.ename '领导'
from emp a
join emp b
on a.mgr=b.empno
where b.sal>3000;

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

select d.deptno,d.dname,d.loc,ifnull(sum(e.sal),0),count(e.ename)
from dept d
left join emp e
on d.deptno=e.deptno
where dname like'%S%'
group by d.deptno,d.dname,d.loc;

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

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

加薪之前：

加薪之后：

一起加油，一起努力，一起秃见成效