MySQL基础篇 | 经典三十四道练习题

✅作者简介：大家好我是@每天都要敲代码，希望一起努力，一起进步！

📃个人主页：@每天都要敲代码的个人主页

🔥系列专栏：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 = 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;