MySQL基础完结篇【第七篇】| 34道练习题
✅作者简介：大家好我是@每天都要敲代码，一位材料转码农的选手，希望一起努力，一起进步！
📃个人主页：@每天都要敲代码的个人主页
🔥系列专栏：MySQL专栏
💬推荐一款模拟面试、刷题神器，从基础到大厂面试题 👉点击跳转刷题网站进行注册学习
目录
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) tjoin emp eon 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) tjoin emp eon e.deptno = t.deptno and sal > t.avgsal;
3. 取得部门中（所有人的）平均的薪水等级
第一步：先获得每个人的薪水等级
select e.ename,e.sal,e.deptno,s.gradefrom emp ejoin salgrade son e.sal between s.losal and hisal;
第二步：根据以上结果进行分组，然后求平均值
select e.deptno,avg(s.grade)from emp ejoin salgrade son e.sal between s.losal and s.hisalgroup by e.deptno;
4. 用不同的方法，求最高薪水
第一种方法：max组函数
select max(sal) maxsal from emp;
第二种方法：先降序排，然后使用limit取第一个
select sal from emp order by sal desc limit 1;
第三种方法：使用自连接
（1）先使用自连接求出一个范围数据，这堆数据里不包括除最大值，其它都包括
select distinct a.sal from emp ajoin emp bon a.sal < b.sal;-- 最大值5000不小于任何值，不会被列出来；其它数据都会被列出来 
（2）使用子查询
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) maxavgsalfrom (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 ejoin dept don e.deptno = d.deptnogroup by d.dname ; --根据部门名称分类
第二步： 降序选第一个
select d.dname,avg(e.sal) avgsal from emp ejoin dept don e.deptno = d.deptnogroup 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) tjoin salgrade son 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) tjoin salgrade son t.avgsal between s.losal and s.hisalwhere 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 ejoin salgrade s on e.sal between s.losal and hisal;
第二步：分组count
select s.grade,count(*)from emp ejoin salgrade s on e.sal between s.losal and hisalgroup 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 = 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 aleft join emp bon a.mgr = b.empno; 
15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select a.empno,a.ename '员工',a.deptno,a.hiredate,b.empno,b.ename '领导',b.deptno,b.hiredate,d.dnamefrom emp ajoin emp bon a.mgr = b.empno --直接上级join dept don a.deptno = d.deptno --根据条件输出对应的d.namewhere 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.deptnofrom emp ejoin dept don e.deptno = d.deptnowhere job = 'CLERK';
第二步：分组，统计每个部门的人数
select deptno,count(*) as deptcountfrom emp egroup by deptno; 
第三步：两张表进行链接
select t1.*,t2.deptcountfrom (select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptnowhere job = 'CLERK') t1join (select deptno,count(*) as deptcount from emp e group by deptno) t2on 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.gradefrom emp e1join dept don e1.deptno = d.deptnoleft join emp e2on e1.mgr = e2.deptnojoin salgrade son e1.sal between s.losal and hisalwhere e1.sal > (select avg(sal) from emp);
23. 列出与"SCOTT" 从事相同工作的所有员工及部门名称
第一步：找出scott从事的工作
select job from emp where ename = 'SCOTT';
第二步： 找出相同工作的所有员工及部门名称
select e.ename,d.dnamefrom emp ejoin dept don e.deptno = d.deptnowhere 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 ejoin dept don e.deptno = d.deptnowhere 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 eright join dept don e.deptno = d.deptnogroup 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 avgservicetimefrom emp eright join dept don e.deptno = d.deptnogroup by d.dname;
27. 列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.salfrom emp ejoin dept don e.deptno = d.deptno; 
28. 列出所有部门的详细信息和人数
select d.* ,count(e.ename)from emp eright join dept don e.deptno = d.deptnogroup by d.deptno,d.dname,d.loc;
29. 列出各种工作的最低工资及从事此工作的雇员姓名
第一步：找出各种工作的最低工资
select job,min(sal) as minsal from empgroup by job;
第二步：把上述结果当成t表，进行表连接
select e.ename,t.*from emp ejoin (select job,min(sal) as minsal from emp group by job) ton e.job = t.job and e.sal = t.minsal;
30. 列出各个部门的 MANAGER( 领导) 的最低薪金
select deptno,min(sal)from empwhere 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 ajoin emp bon a.mgr = b.empnowhere b.sal > 3000;
33. 求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
select d.deptno,d.dname,ifnull(sum(e.sal),0) as sumsal,count(e.ename)from emp eright join dept don e.deptno = d.deptnowhere d.dname like '%S%'group by d.deptno,d.dname;
34. 给任职日期超过 30 年的员工加薪 10%
update emp set sal = sal*1.1 where (timestampdiff(YEAR,hiredate,now()) >30 );select * from emp;
结束语
今天的分享就到这里啦！快快通过下方链接注册加入刷题大军吧！各种大厂面试真题在等你哦！
💬刷题神器，从基础到大厂面试题👉点击跳转刷题网站进行注册学习
