✅作者简介：大家好我是@每天都要敲代码，希望一起努力，一起进步！
📃个人主页：@每天都要敲代码的个人主页
🔥系列专栏：MySQL专栏
目录
1. 取得每个部门最高薪水的人员名称
2. 哪些人的薪水在部门的平均薪水之上
3. 取得部门中（所有人的）平均的薪水等级
4. 用不同的方法，求最高薪水
5. 取得平均薪水最高的部门的部门编号
6. 取得平均薪水最高的部门的部门名称
7. 求平均薪水的等级最低的部门的部门名称
8. 取得比普通员工的最高薪水还要高的领导人姓名
9. 取得薪水最高的前五名员工
10. 取得薪水最高的第六到第十名员工
11. 得最后入职的 5 名员工
12. 取得每个薪水等级有多少员工
13. 面试题：
14. 列出所有员工及领导的姓名
15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
16. 列出部门名称和这些部门的员工信息, 同时也要列出那些没有员工的部门
17. 列出至少有 5 个员工的所有部门
18. 列出薪金比"SMITH" 多的所有员工信息
19. 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
20. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
21. 列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号
22. 列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级
23. 列出与"SCOTT" 从事相同工作的所有员工及部门名称
24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称
26. 列出在每个部门工作的员工数量, 平均工资和平均服务期限
27. 列出所有员工的姓名、部门名称和工资
28. 列出所有部门的详细信息和人数
29. 列出各种工作的最低工资及从事此工作的雇员姓名
30. 列出各个部门的 MANAGER( 领导) 的最低薪金
31. 列出所有员工的 年工资, 按 年薪从低到高排序
32. 求出员工领导的薪水超过3000的员工名称与领导
33. 求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
34. 给任职日期超过 30 年的员工加薪 10%
1. 取得每个部门最高薪水的人员名称
第一步：先按照部门分组，找到每个部门的最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
-- 养成好习惯，给max(sal)定义一个别名，便于操作；不定义下面直接t.max(sal)会报错 
第二步：将以上的查询结果当做一张临时表t，t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal
select e.ename,t.*
from (select deptno,max(sal) as maxsal from emp group by deptno) t
join emp e
on e.deptno = t.deptno and e.sal = t.maxsal;
2. 哪些人的薪水在部门的平均薪水之上
第一步：按照部门分组，求出每个部门的平均薪水
select deptno,avg(sal) as avgsal
from emp
group by deptno;
第二步：将以上查询结果当做t表，t和emp表连接；条件：部门编号相同，并且emp的sal大于t表的avgsal
select e.ename,e.sal,t.*
from (select deptno,avg(sal) as avgsal from emp group by deptno) t
join emp e
on e.deptno = t.deptno and sal > t.avgsal;
3. 取得部门中（所有人的）平均的薪水等级
第一步：先获得每个人的薪水等级
select e.ename,e.sal,e.deptno,s.grade
from emp e
join salgrade s
on e.sal between s.losal and hisal;
第二步：根据以上结果进行分组，然后求平均值
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;
4. 用不同的方法，求最高薪水
第一种方法：max组函数
select max(sal) maxsal from emp;
第二种方法：先降序排，然后使用limit取第一个
select sal from emp order by sal desc limit 1;
第三种方法：使用自连接
第一步：先使用自连接求出一个范围数据，这堆数据里不包括除最大值，其它都包括
select distinct a.sal
from emp a
join emp b
on a.sal < b.sal;
-- 最大值5000不小于任何值，不会被列出来；其它数据都会被列出来 
第二步：使用子查询
select sal
from emp
where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal );
第四种方法：常识思维
select sal from emp where mgr is null;
-- 工资最高，肯定是老板，没有上级领导
5. 取得平均薪水最高的部门的部门编号
第一种方法：降序排，然后limit截取第一个
第一步：先求出每个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
第二步： 降序选第一个
select deptno,avg(sal) avgsal
from emp
group by deptno
order by avgsal desc
limit 1;
第二种方法： 使用max
第一步：先求出每个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
第二步：找出以上结果中avgsal最大的值
select max(t.avgsal) maxavgsal
from (select deptno,avg(sal) avgsal from emp group by deptno) t
第三步：联合使用，第一步和第二步结合，显示平均薪水中最大的值
select deptno,avg(sal) avgsal
from emp
group by deptno
having avgsal = (select max(t.avgsal) maxavgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t);
6. 取得平均薪水最高的部门的部门名称
第一步：先求出每个部门的平均薪水，用部门名称dname分组
select d.dname,avg(e.sal) avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname ; --根据部门名称分类
第二步： 降序选第一个
select d.dname,avg(e.sal) avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname -- 根据姓名进行排序
order by avgsal desc
limit 1;
7. 求平均薪水的等级最低的部门的部门名称
第一步：找出每个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
第二步：找出部门的平均薪水等级
select t.*,s.grade
from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
第三步：选出最低的薪水
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
第四步：根据最低薪水得出部门名称；有可能不止一个数据，所以不能直接先升序排然后limit
select t.*,s.grade
from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join salgrade s
on t.avgsal between s.losal and s.hisal
where e.sal = (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1);8. 取得比普通员工的最高薪水还要高的领导人姓名
第一步：找出普通员工的最高薪资
-- 编号没有出现在mgr中的一定就是普通员工
select max(sal)
from emp
where empno not in(select distinct mgr from emp where mgr is not null);
-- not in 后面一定要手动排除null，不要最后结果是null
第二步：找出高于1600的
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));
9. 取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5;
10. 取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5;
11. 得最后入职的 5 名员工
select ename,hiredate from emp order by hiredate desc limit 5;
-- 日期也可以降序，升序
12. 取得每个薪水等级有多少员工
第一步：找出每个员工的薪水等级
select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and hisal;
第二步：分组count
select s.grade,count(*)
from emp e
join salgrade s
on e.sal between s.losal and hisal
group by s.grade;
13. 面试题：
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
（1）找出没选过“黎明”老师的所有学生姓名。
（2）列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
（3）即学过 1 号课程又学过 2 号课所有学生的姓名。
（1）找出没选过“黎明”老师的所有学生姓名
第一步：先根据姓名找出“黎明”老师的课号cno
select cno from c where cteacher=“黎明”;第二步：根据课号cno，找出选的学生的学号sno
select sno from sc where cno = (select cno from c where cteacher=“黎明”);第三步：根据学号sno找出学生姓名
select sname from s where sno not in (select sno from sc where cno = (select cno from c where cteacher=“黎明”));（2）列出 2 门以上（含2 门）不及格学生姓名及平均成绩
select s.sname,avg(sc.scgrade) avggrade
from s
right join sc
on s.sno = sc.sno
where sc.scgrade < 60
group by s.sname
having count(s.sname) >= 2;（3）即学过 1 号课程又学过 2 号课所有学生的姓名
第一步：选出学过1号课程的学生
select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 1;第一步：选出学过2号课程的学生
select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 2;第三步：选出即学过 1 号课程又学过 2 号课所有学生的姓名
select t1.sname from (select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 1) t1
join (select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 2) t2 on t1.sname = t2.sname;14. 列出所有员工及领导的姓名
-- 使用自连接
select a.ename '员工', b.ename '领导'
from emp a
left join emp b
on a.mgr = b.empno; 
15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select a.empno,a.ename '员工',a.deptno,a.hiredate,b.empno,b.ename '领导',b.deptno,b.hiredate,d.dname
from emp a
join emp b
on a.mgr = b.empno --直接上级
join dept d
on a.deptno = d.deptno --根据条件输出对应的d.name
where a.hiredate < b.hiredate;
16. 列出部门名称和这些部门的员工信息, 同时也要列出那些没有员工的部门
select e.*,d.dname from emp e right join dept d on e.deptno = d.deptno; 
17. 列出至少有 5 个员工的所有部门
select deptno from emp group by deptno having count(*)>=5;
18. 列出薪金比"SMITH" 多的所有员工信息
第一步：先查出smith的薪资
select e.sal from emp e where e.ename = 'smith';
第二步：找出薪资大于800的
select ename,sal from emp where sal > (select e.sal from emp e where e.ename = 'smith');
19. 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
第一步：找出clerk(办事员)的姓名和部门名称
select e.ename,d.dname,d.deptno
from emp e
join dept d
on e.deptno = d.deptno
where job = 'CLERK';
第二步：分组，统计每个部门的人数
select deptno,count(*) as deptcount
from emp e
group by deptno; 
第三步：两张表进行链接
select t1.*,t2.deptcount
from (select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno
where job = 'CLERK') t1
join (select deptno,count(*) as deptcount from emp e group by deptno) t2
on t1.deptno = t2.deptno;
20. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
select job,count(*) from emp group by job having min(sal) > 1500;
21. 列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号
第一步：利用部门sales得到部门编号
select deptno from dept where dname = 'sales';
第二步：在通过部门编号得到员工姓名
select ename from emp where deptno = (select deptno from dept where dname = 'sales');
22. 列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级
第一步：找出公司平均薪金
select avg(sal) from emp;
第二步：找出所有工资>2073.214286的员工
select e1.ename '员工',d.dname,e2.ename '领导',s.grade
from emp e1
join dept d
on e1.deptno = d.deptno
left join emp e2
on e1.mgr = e2.deptno
join salgrade s
on e1.sal between s.losal and hisal
where e1.sal > (select avg(sal) from emp);
23. 列出与"SCOTT" 从事相同工作的所有员工及部门名称
第一步：找出scott从事的工作
select job from emp where ename = 'SCOTT';
第二步： 找出相同工作的所有员工及部门名称
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 ename <> 'SCOTT'; --排除本身自己
24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
第一步：找出部门30的薪资集合
select distinct sal from emp where deptno = 30;
第二步：找出薪资属于上述集合，但部门不是30的
select ename,sal
from emp
where sal in (select distinct sal from emp where deptno = 30)
and deptno <> 30; 
25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称
第一步：找出30部门的最高薪资
select max(sal) from emp where deptno = 30;
第二步：找出薪资>2850.00的员工
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);
26. 列出在每个部门工作的员工数量, 平均工资和平均服务期限
注：没有员工的部门，使用ifnull函数，部门人数是0
第一步：找出员工数量和平均工资
select d.dname,count(e.ename),ifnull(avg(e.sal),0)
from emp e
right join dept d
on e.deptno = d.deptno
group by d.dname;
第二步：加上计算平均服务期限
在mysql当中怎么计算两个日期的“年差”，差了多少年？
TimeStampDiff(间隔类型, 前一个日期, 后一个日期)间隔类型：
SECOND 秒，
MINUTE 分钟，
HOUR 小时，
DAY 天，
WEEK 星期
MONTH 月，
QUARTER 季度，
YEAR 年
select d.dname,count(e.ename),ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from emp e
right join dept d
on e.deptno = d.deptno
group by d.dname;
27. 列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal
from emp e
join dept d
on e.deptno = d.deptno; 
28. 列出所有部门的详细信息和人数
select d.* ,count(e.ename)
from emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno,d.dname,d.loc;
29. 列出各种工作的最低工资及从事此工作的雇员姓名
第一步：找出各种工作的最低工资
select job,min(sal) as minsal
from emp
group by job;
第二步：把上述结果当成t表，进行表连接
select e.ename,t.*
from emp e
join (select job,min(sal) as minsal from emp group by job) t
on e.job = t.job and e.sal = t.minsal;
30. 列出各个部门的 MANAGER( 领导) 的最低薪金
select deptno,min(sal)
from emp
where job = 'MANAGER'
group by deptno;
31. 列出所有员工的 年工资, 按 年薪从低到高排序
select ename,(sal+ifnull(comm,0)) * 12 as yearsal
from emp
order by yearsal asc;
32. 求出员工领导的薪水超过3000的员工名称与领导
select a.ename as '员工', b.ename as '领导'
from emp a
join emp b
on a.mgr = b.empno
where b.sal > 3000;
33. 求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
select d.deptno,d.dname,ifnull(sum(e.sal),0) as sumsal,count(e.ename)
from emp e
right join dept d
on e.deptno = d.deptno
where d.dname like '%S%'
group by d.deptno,d.dname;
文章来源：https://www.toymoban.com/news/detail-789654.html
34. 给任职日期超过 30 年的员工加薪 10%
update emp set sal = sal*1.1 where (timestampdiff(YEAR,hiredate,now()) >30 );
select * from emp;
文章来源地址https://www.toymoban.com/news/detail-789654.html
到了这里，关于MySQL基础篇 | 经典三十四道练习题的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
