MySQL基础完结篇【第七篇】| 34道练习题

✅作者简介：大家好我是@每天都要敲代码，一位材料转码农的选手，希望一起努力，一起进步！
📃个人主页：@每天都要敲代码的个人主页
🔥系列专栏：MySQL专栏
💬推荐一款模拟面试、刷题神器，从基础到大厂面试题 👉点击跳转刷题网站进行注册学习

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;

第三种方法：使用自连接

（1）先使用自连接求出一个范围数据，这堆数据里不包括除最大值，其它都包括

select distinct a.sal 
from emp a
join emp b
on 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) 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;

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

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

结束语

今天的分享就到这里啦！快快通过下方链接注册加入刷题大军吧！各种大厂面试真题在等你哦！
💬刷题神器，从基础到大厂面试题👉点击跳转刷题网站进行注册学习