mysql数据结构面试题（MySQL经典面试练习题）

忙族 2022-12-16 21:29:03 940

mysql数据结构面试题（MySQL经典面试练习题）MySQL经典面试练习题，新建dept部门表2.1 新建部门表dept(deptno部门编号，dname部门名称，loc部门)，并插入如下数据。一 . 新建数据库及数据表1. 新建数据库test2语句：CREATE database test2;

前言：

这是最近梳理的第2套MySQL经典面试练习题，部分复杂或特殊题目标注了思路和对应的考察函数or用法知识点，分享给正在学习MySQL或正在刷SQL面试题的同学们哈~

码字截图不易，若文章对您的学习或面试起到了帮助，欢迎点赞收藏转发哈，赠人玫瑰手有余香~

若学习过程中发现某个题目的语句有更优化的写法，也欢迎留言或私信交流，互相学习共同提升~

一 . 新建数据库及数据表

1. 新建数据库test2

语句：CREATE database test2;

2.1 新建部门表dept(deptno部门编号，dname部门名称，loc部门)，并插入如下数据。

mysql数据结构面试题（MySQL经典面试练习题）(1)

MySQL经典面试练习题，新建dept部门表

语句：

create table dept(deptno int(10) primary key dname varchar(20) loc varchar(20)); insert into dept(deptno dname loc) values(10 'ACCOUNTING' 'NEW YORK'); insert into dept(deptno dname loc) values(20 'RESEARCHING' 'DALLAS'); insert into dept(deptno dname loc) values(30 'SALES' 'CHICAGO'); insert into dept(deptno dname loc) values(40 'OPERATIONS' 'BOSTON');

2.2 新建薪水等级表salgrade(grade等级，losal最低薪水，hisal最高薪水)，并插入如下数据。

mysql数据结构面试题（MySQL经典面试练习题）(2)

MySQL经典面试练习题，新建salgrade薪水等级表

语句：

create table salgrade(grade int(10) losal int(10) hisal int(10)); insert into salgrade(grade losal hisal) values(1 700 1200); insert into salgrade(grade losal hisal) values(2 1201 1400); insert into salgrade(grade losal hisal) values(3 1401 2000); insert into salgrade(grade losal hisal) values(4 2001 3000); insert into salgrade(grade losal hisal) values(5 3001 5000);

2.3 新建雇员表emp(empno雇员编号、ename雇员姓名、job岗位、magr领导编号、hiredate入职日期、sal薪水、comm补助、deptno部门编号)，并插入如下数据。

mysql数据结构面试题（MySQL经典面试练习题）(3)

MySQL经典面试练习题，新建emp雇员表

语句：

create table emp(empno int(10) primary key ename varchar(10) job varchar(10) mgr int(10) hiredate date sal double(10 2) comm double(10 2) deptno int(10)); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7369 'SIMITH' 'CLERK' 7902 '1980-12-17' 800 null 20); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7499 'ALLEN' 'SALESMAN' 7698 '1981-02-20' 1600 300 30); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7521 'WARD' 'SALESMAN' 7698 '1981-02-22' 1250 500 30); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7566 'JONES' 'MANAGER' 7839 '1981-04-02' 2975 null 20); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7654 'MARTIN' 'SALESMAN' 7698 '1981-09-28' 1250 1400 30); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7698 'BLAKE' 'MANAGER' 7839 '1981-05-01' 2850 null 30); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7782 'CLARK' 'MANAGER' 7839 '1981-06-09' 2450 null 10); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7788 'SCOTT' 'ANALYST' 7566 '1987-04-19' 3000 null 20); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7839 'KING' 'PRESIDENT' null '1981-11-17' 5000 null 10); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7844 'TURNER' 'SALESMAN' 7698 '1981-09-08' 1500 null 30); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7876 'ADAMS' 'CLERK' 7788 '1987-05-23' 1100 null 20); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7900 'JAMES' 'CLERK' 7698 '1981-12-03' 950 null 30); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7902 'FORD' 'ANALYST' 7566 '1981-12-03' 3000 null 20); insert into emp(empno ename job mgr hiredate sal comm deptno) values(7934 'MILLER' 'CLERK' 7782 '1982-01-23' 1300 null 10);

3. 观察一下3表之间的联结关系，以便做题时使用。

mysql数据结构面试题（MySQL经典面试练习题）(4)

dept部门表、emp雇员表、salgrade薪水等级表 3表联结关系

二 . 题目

1. 取得每个部门最高薪水的人员名称。

思路：先查询每个部门部门编号、最高薪水；将结果视作临时表t与emp雇员表进行联合查询出部门编号、最高薪水、人员姓名（条件：2表部门编号、薪水均相等）。

函数：求最高薪水，要用到max()函数。

语句：

select t.deptno e.ename t.max_sal e.sal from emp e (select deptno max(sal) as max_sal from emp group by deptno)t where e.deptno=t.deptno and e.sal=t.max_sal order by t.deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(5)

MySQL经典面试练习题：1. 取得每个部门最高薪水的人员名称。

2. 哪些人的薪水在部门平均薪水之上。

思路：先查询每个部门的部门编、平均薪水；将结果视作临时表t与emp雇员表进行联合查询出部门编号、薪水、人员姓名（条件：2表部门编号相等，但t表平均薪水＜emp表薪水）。

函数：求平均薪水，要用到avg()函数。

语句：

select e.deptno e.ename e.sal t.avg_sal from emp e (select deptno avg(sal) as avg_sal from emp group by deptno)t where e.deptno=t.deptno and e.sal>t.avg_sal order by e.deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(6)

MySQL经典面试练习题：2. 哪些人的薪水在部门平均薪水之上。

3. 取得部门中所有人的平均薪水等级。

思路：此题具有迷惑性，容易发生理解歧义，实际可以理解成2个问题:

3.1 取得部门中所有人的“平均薪水”的等级。

思路：先取部门所有人的平均薪水；将查询结果视作临时表t，与salgrade薪水等级表联合查询出部门编号、平均薪水、平均薪水对应等级。

函数：求平均薪水需使用到avg()；另平均薪水等级需根据高低薪水区间去判断，需使用区间查询函数between()and()。

语句：

select t.deptno t.avg_sal s.grade from salgrade s (select deptno avg(sal) as avg_sal from emp group by deptno)t where t.avg_sal between s.losal and s.hisal order by t.deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(7)

MySQL经典面试练习题：3.1 取得部门中所有人的“平均薪水”的等级。

3.2 取得部门中所有人的平均的“薪水等级”。

思路：先部门中所有人的薪水等级；将查询结果视作临时表t，从中查询出部门编号、平均“薪水等级”。

函数：同上，涉及区间查询查询函数between()and()。

语句：

select deptno avg(grade) as avg_grade from (select e.deptno e.empno e.ename s.grade from emp e salgrade s where e.sal between s.losal and s.hisal)t group by deptno order by deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(8)

MySQL经典面试练习题：3.2 取得部门中所有人的平均的“薪水等级”。

4. 不准用组函数（max），取得最高薪水（给出2种解决方案）。

思路1：对薪水进行从高到低降序排序，取第一条数据即为最高薪水。

函数：涉及对记录进行限制截取，要使用limit极限函数。

语句：

select sal from emp order by sal desc limit 1;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(9)

MySQL经典面试练习题：4. 取得最高薪水。

思路2：先对对薪水进行降序排位；将查询结果视作临时表t，取其中排位结果为1的即为最高薪水。

函数：排位函数rank() over(partition by * order by *)，其中partition by 限定排位分区字段，order by限定排位字段，此处不需要限定排位分区，直接对sal进行排位即可。

语句：

select t.sal t.rank_sal from (select sal rank() over(order by sal desc) as rank_sal from emp)t where t.rank_sal=1;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(10)

MySQL经典面试练习题：4. 取得最高薪水。

思路3：从emp取2次sal分别视作临时表a、b；查询a表薪水<b表薪水的结果并剔重（2表中最大薪水只可能等同，故此结果一定不包含最大值），再反向查询emp表中不在此结果的sal即可。

函数：比较结果涉及剔重，需用到distinck取唯一值函数;not in().

语句：

select sal from emp where sal not in ( select distinct a.sal from (select sal from emp) a (select sal from emp)b where a.sal<b.sal);

结果：

mysql数据结构面试题（MySQL经典面试练习题）(11)

MySQL经典面试练习题：4. 取得最高薪水。

5. 取得平均薪水最高的部门的部门编号。

思路：先取每个部门的平均薪水；将结果视作临时表t并从中取平均薪水最大值；以此最大值为过滤条件再从emp中取对应的部门编号。

函数：取最大值函数max()、取平均值函数avg()。

语句：

select deptno avg(sal) as avg_sal from emp group by deptno having avg_sal=(select max(avg_sal) from (select deptno avg(sal) as avg_sal from emp group by deptno)t);

结果：

mysql数据结构面试题（MySQL经典面试练习题）(12)

MySQL经典面试练习题：5. 取得平均薪水最高的部门的部门编号。

6. 取得平均薪水最高的部门的部门名称。

思路：沿用题5查询结果，将其视作临时表a与dept部门表进行联合查询取对应部门名称即可，关联条件为deptno。

函数：取最大值函数max()、取平均值函数avg()。

语句：

select d.dname a.deptno a.avg_sal from dept d (select deptno avg(sal) as avg_sal from emp group by deptno having avg_sal=(select max(avg_sal) from (select deptno avg(sal) as avg_sal from emp group by deptno) t))a where d.deptno=a.deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(13)

7. 求平均薪水的等级最低的部门的部门名称。

思路：先取每个部门的平均薪水；将结果视作临时表t与salgrade关联查询平均薪水对应等级；最后再与dept表关联查询等级中最低的部门名称。

函数：取最小值函数min()、取平均值函数avg()。

语句：

select d.dname a.deptno a.avg_sal min(a.grade) as min_grade from dept d (select t.deptno t.avg_sal s.grade from salgrade s (select deptno avg(sal) as avg_sal from emp group by deptno) t where t.avg_sal between s.losal and s.hisal) a where d.deptno=a.deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(14)

MySQL经典面试练习题：7. 求平均薪水的等级最低的部门的部门名称。

8. 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名。

思路：先取得普通员工的最高薪水（员工代码不在mgr中）；再取emp表中薪水大于此最高薪水的人员姓名，即为题目要求的经理人姓名。

函数：反向思考，先取mgr并剔重，需限定mgr不为空，需使用is not null；员工编号不在此结果集中的即为普通员工，需用到not in。

语句：

select ename from emp where sal>(select max(sal) as max_sal from emp where empno not in (select distinct mgr from emp where mgr is not null));

结果：

mysql数据结构面试题（MySQL经典面试练习题）(15)

MySQL经典面试练习题：8. 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名。

9. 取得薪水最高的前五名员工。

思路1：参考题4思路，对薪水进行降序排序，截取前5条记录即可

函数：极限函数limit

语句：

select * from emp order by sal desc limit 5;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(16)

MySQL经典面试练习题：9. 取得薪水最高的前五名员工。

思路2：先对对薪水进行降序排位后加上序号；将查询结果视作临时表t并取其中排位结果≤5的即可。

函数：此处使用row_number()函数，另可自行查询学习SQL四大排序函数：row_number()、rank()、dense_rank、NTILE()。

语句：

select * from (select * row_number() over(order by sal desc) as rank_sal from emp)t where t.rank_sal<=5;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(17)

MySQL经典面试练习题：9. 取得薪水最高的前五名员工。

10. 取得薪水最高的第六到第十名员工。

思路1：思路同题4，对薪水进行从高到低倒序排序，其中第6到第10的记录。

函数：极限函数limit

语句：

select * from emp order by sal desc limit 5 5;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(18)

经典面试练习题：10. 取得薪水最高的第六到第十名员工。

思路2：先对对薪水进行倒序排位后加上序号，将查询结果视作临时表，取其中序号结果介于6和10的记录。

函数：此处使用row_number()函数，另可自行查询学习SQL四大排序函数：row_number()、rank()、dense_rank、NTILE()。

语句：

select t.* from (select * row_number() over(order by sal desc) as rank_sal from emp)t where t.rank_sal between 6 and 10;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(19)

经典面试练习题：10. 取得薪水最高的第六到第十名员工。

11. 取得最后入职的5名员工。

思路：对入职时间降序排序，截取前5条记录即可。

函数：极限函数limit；limit后可跟随1个数字代表直接截取几条记录，如也可以跟随2个数字代表从第几位开始截取，截多少条记录。

语句1：

select * from emp order by hiredate desc limit 5;

语句2：

select * from emp order by hiredate desc limit 0 5;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(20)

MySQL经典面试练习题：11. 取得最后入职的5名员工。

12. 取得每个薪水等级有多少员工。

思路：先查询每个员工的薪水及等级信息；将查询结果视作临时表并对t表中薪水等级进行分组计数。

函数：计数函数count()

语句：

select grade count(empno) as'员工数量' from (select e.* s.grade from emp e salgrade s where e.sal between s.losal and s.hisal) t group by grade order by grade;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(21)

MySQL经典面试练习题：12. 取得每个薪水等级有多少员工。

13.分别根据如下数据创建对应表并插入对应数据。

1）student学生表数据：

mysql数据结构面试题（MySQL经典面试练习题）(22)

MySQL经典面试练习题，新建student学生表

语句：

create table student(sno int(20) primary key sname varchar(20)); insert into student(sno sname) values(1 '张三'); insert into student(sno sname) values(2 '李四'); insert into student(sno sname) values(3 '王五'); insert into student(sno sname) values(4 '赵六');

2）course课程表数据

mysql数据结构面试题（MySQL经典面试练习题）(23)

MySQL经典面试练习题，新建course课程表

语句：

create table course(cno int(20) primary key cname varchar(20) cteacher varchar(20)); insert into course(cno cname cteacher) values(1 'java' '刘德华'); insert into course(cno cname cteacher) values(2 'C ' '郭富城'); insert into course(cno cname cteacher) values(3 'C#' '张学友'); insert into course(cno cname cteacher) values(4 'mysql' '梁朝伟'); insert into course(cno cname cteacher) values(5 'oracle' '黎明');

3）sc成绩表数据

mysql数据结构面试题（MySQL经典面试练习题）(24)

MySQL经典面试练习题，新建sc成绩表

语句：

create table sc(sno int(20) cno int(20) scgrade int(20) primary key(sno cno)); insert into sc(sno cno scgrade) values(1 1 50); insert into sc(sno cno scgrade) values(1 2 50); insert into sc(sno cno scgrade) values(1 3 50); insert into sc(sno cno scgrade) values(2 2 80); insert into sc(sno cno scgrade) values(2 3 70); insert into sc(sno cno scgrade) values(2 4 59); insert into sc(sno cno scgrade) values(3 1 60); insert into sc(sno cno scgrade) values(3 2 61); insert into sc(sno cno scgrade) values(3 3 99); insert into sc(sno cno scgrade) values(3 4 100); insert into sc(sno cno scgrade) values(3 5 52); insert into sc(sno cno scgrade) values(4 3 82); insert into sc(sno cno scgrade) values(4 4 99); insert into sc(sno cno scgrade) values(4 5 40);

4）观察3表之间联结关系，便于做题时使用。

mysql数据结构面试题（MySQL经典面试练习题）(25)

course课程表、sc成绩表、student学生表 3表联结关系

13.1 找出没选过”黎明”老师的所有学生姓名。

思路：反向思考，先从course表取“黎明”老师的授课编号，再从sc表取学习了该课程的学生编号，最后从student表中取学生编号不在此结果集中的学生姓名。

函数：/

语句：

select sname from student where sno not in (select sno from sc where cno in (select cno from course where cteacher='黎明'));

结果：

mysql数据结构面试题（MySQL经典面试练习题）(26)

MySQL经典面试练习题：13.1 找出没选过”黎明”老师的所有学生姓名。

13.2 列出2门以上（含2门）不及格学生姓名及平均分。

思路：先从sc和student表联合查询学生编号、学生姓名、选课科目（分数不及格且不及格科目≥2）视作临时表a；再从sc取学生编号、平均成绩作为临时表b；再将a/b表关联查询即可。

函数：/

语句：

select a.sname a.c_num b.avg_grade from (select sc.sno s.sname count(sc.cno) as c_num from sc join student s on sc.sno=s.sno where scgrade<60 group by sc.sno s.sname having c_num>=2)a (select sno avg(scgrade) as avg_grade from sc group by sno)b where a.sno=b.sno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(27)

MySQL经典面试练习题：13.2 列出2门以上（含2门）不及格学生姓名及平均分。

13.3 既学过1号课程也学过2号课程的学生。

思路：从sc和student关联表中取学生姓名，但同时过滤课程编号为1且课程编号为2，语法上无法直接写出，所以换一种写法：过滤条件为课程编号为1且学号存在于学了课程2的学号中。

函数：/

语句：

select s.sname from sc student s where sc.sno=s.sno and sc.cno='1' and sc.sno in (select sno from sc where cno='2');

结果：

mysql数据结构面试题（MySQL经典面试练习题）(28)

MySQL经典面试练习题：13.3 既学过1号课程也学过2号课程的学生。

14. 列出所有员工及领导的名字。

思路：将emp当做2张表，1张为员工表a，1张为领导表b，对2张表进行联结查询，联结条件a表领导编号等于b表员工编号。

函数：题干要求列出所有员工，所以领导为空的king也需要显示出来，则以员工表为基础左联结领导表，需用到left join左联结函数。

正确语句：

select a.ename b.ename as leadername from emp a left join emp b on a.mgr=b.empno;

正确结果：

mysql数据结构面试题（MySQL经典面试练习题）(29)

MySQL经典面试练习题：14. 列出所有员工及领导的名字。

错误语句：

select a.ename b.ename as leadername from emp a join emp b on a.mgr = b.empno;

错误结果：

mysql数据结构面试题（MySQL经典面试练习题）(30)

注：join为inner join简写，只查询关联表的交集部分，会把员工表a中的“KING”员工过滤掉，不符合题干要求。应以员工表a为主，左联结领导表b，取员工全集。

15. 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称。

思路：将emp视作员工表a、领导表b，与部门表dept进行三表联结，联结条件：a表领导编号=b表员工编号&a表部门编号=dept表部门编号，过滤条件：a表入职日期＜b表入职日期。

函数：join

语句：

select a.empno a.ename from emp a join emp b on a.mgr=b.empno join dept d on a.deptno=d.deptno where a.hiredate<b.hiredate order by dname;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(31)

MySQL经典面试练习题：15. 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称。

16. 列出部门名称和这些部门的员工信息，*同时列出那些没有员工的部门。

思路：题干要求列出没有员工的部门，所以需要以dept中的部门为主，左联结emp表进行查询（或emp右联结dept也可以）。

函数：left join right join

语句1：

select d.dname e.* from dept d left join emp e on d.deptno=e.deptno order by d.dname;

语句2：

select d.dname e.* from emp e right join dept d on e.deptno = d.deptno order by d.dname;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(32)

MySQL经典面试练习题：16. 列出部门名称和这些部门的员工信息，*同时列出那些没有员工的部门。

错误语句：

select d.dname e.* from dept d join emp e on d.deptno=e.deptno order by d.dname;

错误结果：

mysql数据结构面试题（MySQL经典面试练习题）(33)

说明：join为inner join简写，只查询关联表的交集部分，会把dept表中的“OPERATIONS”部门过滤掉，不符合题干要求。应以dept中的部门为主，左联结emp表进行查询，取部门全集。

17. 列出至少有5个员工的所有部门。

思路：从emp中查询部门编号、部门人数，再加入过滤条件部门人数≥5。

函数：计数函数count()

语句：

select deptno count(empno) as '部门人数' from emp group by deptno having count(empno)<=5;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(34)

MySQL经典面试练习题：17. 列出至少有5个员工的所有部门。

18. 列出薪水比"SIMITH"多的所有员工信息。

思路：先查询"SIMITH"的薪水，然后查询emp中的所有员工信息，过滤条件为薪水大于"SIMITH"的薪水。

函数：/

语句：

select * from emp where sal>(select sal from emp where ename='SIMITH');

结果：

mysql数据结构面试题（MySQL经典面试练习题）(35)

MySQL经典面试练习题：18. 列出薪水比"SIMITH"多的所有员工信息。

19. 列出所有"CLERK"（办事员）的姓名及其部门名称，部门人数。

思路：先从emp、dept关联查询姓名、部门编号、部门名称（条件job等于"CLERK"）；再从emp中查询部门编号、部门人数；最后对2个查询表进行联合查询。

函数：/

语句：

select a.ename a.dname b.emp_num from (select e.ename e.deptno d.dname from emp e dept d where e.deptno=d.deptno and job='CLERK')a (select deptno count(empno) as emp_num from emp group by deptno)b where a.deptno=b.deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(36)

MySQL经典面试练习题：19. 列出所有"CLERK"（办事员）的姓名及其部门名称，部门人数。

20. 列出最低薪水大于1500的各种工作以及从事此工作的全部雇员人数。

思路：先从emp中查询每种工作的名称、最低薪水(过滤条件:最低薪水＞1500)；再与emp进行关联查询工作名称、雇员人数。

函数：/

语句：

select e.job t.min_sal count(e.empno) as emp_num from emp e (select job min(sal) as min_sal from emp group by job having min_sal>1500)t where e.job=t.job group by t.job;

结果：

21. 列出在部门"SALES"（销售部）工作的员工姓名，假定不知道销售部门的部门编号。

思路：先从dept查询部门名称为"SALES"的部门编号；再从emp表中查询对应此部门编号的员工姓名。

函数：/

语句：

select ename from emp where deptno=(select deptno from dept where dname='SALES');

结果：

mysql数据结构面试题（MySQL经典面试练习题）(37)

MySQL经典面试练习题：21. 列出在部门"SALES"（销售部）工作的员工姓名，假定不知道销售部门的部门编号。

22. 列出薪资高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级。

思路：先从emp查询公司平均薪水；再以此平均薪水为过滤条件，对emp、salgrade、dept进行多表关联查询。

函数：left join

语句：

select d.dname a.ename b.ename as leadername s.grade from emp a join dept d on a.deptno=d.deptno left join emp b on a.mgr=b.empno join salgrade s on a.sal between s.losal and s.hisal where a.sal>(select avg(sal) as avgsal from emp);

结果：

mysql数据结构面试题（MySQL经典面试练习题）(38)

MySQL经典面试练习题：22. 列出薪资高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级。

23. 列出与"SCOTT"从事相同工作的所有员工及部门名称。

思路：先从emp查询"SCOTT"从事的工作；再以此结果为过滤条件从emp、dept中关联查询员工信息和部门名称。

函数：/

语句：

select e.ename e.job d.dname from emp e dept d where e.deptno=d.deptno and e.job=(select job from emp where ename='SCOTT');

结果：

mysql数据结构面试题（MySQL经典面试练习题）(39)

MySQL经典面试练习题：23. 列出与"SCOTT"从事相同工作的所有员工及部门名称。

24. 列出薪资等于部门30中员工的薪资的其他部门员工的姓名和薪资。

思路：先查询部门30的员工薪资；再以此薪资和部门30为过滤条件，从emp中查询员工姓名、薪资。

函数：/

语句：

select ename sal from emp where deptno!=30 and sal in(select distinct sal from emp where deptno=30);

结果：

Empty set (0.00 sec)

验证：

1）查看部门不等于30的人员的薪资信息：

select distinct sal from emp where deptno!=30;

mysql数据结构面试题（MySQL经典面试练习题）(40)

MySQL经典面试练习题：24. 列出薪资等于部门30中员工的薪资的其他部门员工的姓名和薪资。

2）查看部门等于30的人员的薪资信息（薪资剔重）

select distinct sal from emp where deptno=30;

mysql数据结构面试题（MySQL经典面试练习题）(41)

MySQL经典面试练习题：24. 列出薪资等于部门30中员工的薪资的其他部门员工的姓名和薪资。

对比可见部门非30的人员薪资与部门为30的人员薪资没有交集，故此题结果为空集是正确的。

25. 列出薪资高于在部门30工作的所有员工的薪资的员工姓名、薪资和部门名称。

思路：先查询部门30的最高员工薪资；再以此为过滤条件对emp、dept 2表进行联合查询。

函数：/

语句1：

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);

语句2：

select e.ename e.sal d.dname from emp e dept d where e.deptno=d.deptno and e.sal>(select max(sal) from emp where deptno=30);

结果：

mysql数据结构面试题（MySQL经典面试练习题）(42)

MySQL经典面试练习题：25. 列出薪资高于在部门30工作的所有员工的薪资的员工姓名、薪资和部门名称。

26. 列出在每个部门工作的员工数量，平均薪资和平均服务期限。

思路：员工数量、平均薪资好取，取服务期限则需先获取当前日期，然后计算当前日期与入职日期的差值，差值除以365则为服务年限，套用avg()函数即可算平均服务年限。

函数：可以直接用datediff()计算当前日期天数和入职日期天数差，也可以用to_days()分别计算当前日期天数和入职日期天数后相减获取天数差；

另获取当前日期可以用curdate()也可以用now()，差异在于now获取“年月日时分秒”，curdate只获取“年月日”。

语句1：

select deptno count(empno) as '员工数量' avg(sal) as '平均薪资' avg(datediff(curdate() hiredate)/365) as '平均服务年限' from emp group by deptno order by deptno;

语句2：

select deptno count(empno) as '员工数量' avg(sal) as '平均薪资' avg((to_days(curdate())-to_days(hiredate))/365) as '平均服务年限' from emp group by deptno order by deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(43)

MySQL经典面试练习题：26. 列出在每个部门工作的员工数量，平均薪资和平均服务期限。

27. 列出*所有员工*的姓名、部门名称和工资。

思路：题干要求所有员工、所有部门，故需以dept表中部门信息为基数，右匹配emp表的相关信息。

函数：right join

语句：

select e.ename d.dname e.sal from emp e right join dept d on e.deptno = d.deptno order by e.ename;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(44)

MySQL经典面试练习题：27. 列出*所有员工*的姓名、部门名称和工资。

错误语句：

select e.ename d.dname e.sal from emp e join dept d where e.deptno=d.deptno order by e.ename;

错误结果：

mysql数据结构面试题（MySQL经典面试练习题）(45)

说明：如果只用join进行表联结，则dept中部门OPERATIONS未提现信息，虽然该部门并无人员，但不符合题干要求“所有”，故应以dept表为基础去右联结emp。

28. 列出所有部门的详细信息和人数。

思路：题干要求所有部门，所以需要以dept表中部门信息为基数，左匹配emp表的相关信息。

函数：left join

语句：

select d.* count(e.empno) as dmp_num from dept d left join emp e on d.deptno=e.deptno group by e.deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(46)

MySQL经典面试练习题：28. 列出所有部门的详细信息和人数。

29. 列出各种工作的最低工资以及从事此工作的雇员姓名。

思路：先从emp查询工作名称、最低工资；将此结果与emp表再进行关联查询，关联条件为2表中的工作名称及薪水分别相等。

函数：/

语句：

select e.ename e.job t.min_sal from emp e (select job min(sal) as min_sal from emp group by job)t where e.job=t.job and e.sal=t.min_sal;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(47)

MySQL经典面试练习题：29. 列出各种工作的最低工资以及从事此工作的雇员姓名。

30. 列出各个部门manager的最低薪资。

思路：/

函数：/

语句：

select deptno min(sal) as min_sal from emp where job='manager' group by deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(48)

MySQL经典面试练习题：30. 列出各个部门manager的最低薪资。

31. 列出所有员工的年工资，按年薪从低到高排序。

思路：年薪为基本工资sal 补助comm之和乘以12

函数：通过观察emp表得知comm存在null空置情况，而空置无法直接参与数学运算，所以相加之前需要先对comm进行空置处理，引入ifnull()函数。

语句：

select empno ename (sal ifnull (comm 0))*12 as year_sal from emp order by year_sal;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(49)

MySQL经典面试练习题：31. 列出所有员工的年工资，按年薪从低到高排序。

32. 求出员工领导的薪水超过3000的员工名称和领导名称。

思路：将emp当做2张表，1张员工表，1张领导表，员工表中的deptno包含领导表中的mgr

函数：/

语句1：

select a.ename b.ename as leader_name b.sal as leader_sal from emp a emp b where a.mgr=b.empno and b.sal>3000;

语句2：

select a.ename b.ename as leader_name b.sal as leader_sal from emp a join emp b on a.mgr = b.empno where b.sal > 3000;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(50)

MySQL经典面试练习题：32. 求出员工领导的薪水超过3000的员工名称和领导名称。

33. 求部门名称中带s字符的部门员工的工资合计、部门人数。

思路：/

函数：考察通配符的使用 like'%*%'

语句1：

select e.deptno d.dname sum(sal) as total_sal count(e.empno) as emp_num from emp e dept d where e.deptno=d.deptno and d.dname like'%s%' group by e.deptno;

语句2：

select e.deptno d.dname sum(sal) as total_sal count(e.empno) as emp_num from emp e join dept d on e.deptno=d.deptno where d.dname like'%s%' group by e.deptno;

结果：

mysql数据结构面试题（MySQL经典面试练习题）(51)