✅作者简介：大家好我是@每天都要敲代码，希望一起努力，一起进步！
📃个人主页：@每天都要敲代码的个人主页
🔥系列专栏：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)  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;

第三种方法：使用自连接

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

select distinct a.sal from emp ajoin emp bon 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) 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 < 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 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;

来源地址：https://blog.csdn.net/m0_61933976/article/details/126356250