mysql判断日期在区间_MySQL经典面试练习题 (34题含答案)~面试BAT再也不怕了

前言：

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

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

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

一 . 新建数据库及数据表

1. 新建数据库test2

语句：CREATE database test2;

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

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经典面试练习题，新建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经典面试练习题，新建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表之间的联结关系，以便做题时使用。

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经典面试练习题：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经典面试练习题：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经典面试练习题：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经典面试练习题：3.2 取得部门中所有人的平均的“薪水等级”。

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

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

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

语句：

select sal from emp order by sal desc limit 1;

结果：

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经典面试练习题：4. 取得最高薪水。

思路3：从emp取2次sal分别视作临时表a、b；查询a表薪水

函数：比较结果涉及剔重，需用到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

结果：

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经典面试练习题：5. 取得平均薪水最高的部门的部门编号。

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

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

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

语句：

select d.dname,a.deptno,a.avg_salfrom 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))awhere d.deptno=a.deptno;

结果：

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

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

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

语句：

select d.dname,a.deptno,a.avg_sal,min(a.grade) as min_gradefrom 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) awhere d.deptno=a.deptno;

结果：

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

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

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

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

语句：

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

结果：

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

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

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

函数：极限函数limit

语句：

select * from emp order by sal desc limit 5;

结果：

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经典面试练习题：9. 取得薪水最高的前五名员工。

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

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

函数：极限函数limit

语句：

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

结果：

经典面试练习题：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;

结果：

经典面试练习题：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经典面试练习题：11. 取得最后入职的5名员工。

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

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

函数：计数函数count()

语句：

select grade,count(empno) as'员工数量'from (select e.*,s.grade      from emp e,salgrade swhere e.sal between s.losal and s.hisal) tgroup by gradeorder by grade;

结果：

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

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

1)student学生表数据：

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经典面试练习题，新建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经典面试练习题，新建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表之间联结关系，便于做题时使用。

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经典面试练习题：13.1 找出没选过”黎明”老师的所有学生姓名。

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

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

函数：/

语句：

select a.sname,a.c_num,b.avg_gradefrom (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)bwhere a.sno=b.sno;

结果：

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经典面试练习题：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经典面试练习题：14. 列出所有员工及领导的名字。

错误语句：

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

错误结果：

注：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.deptnowhere a.hiredate

结果：

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.deptnoorder 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经典面试练习题：16. 列出部门名称和这些部门的员工信息，*同时列出那些没有员工的部门。

错误语句：

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

错误结果：

说明：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经典面试练习题：17. 列出至少有5个员工的所有部门。

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

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

函数：/

语句：

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

结果：

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经典面试练习题：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经典面试练习题：21. 列出在部门"SALES"(销售部)工作的员工姓名，假定不知道销售部门的部门编号。

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

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

函数：left join

语句：

select d.dname,a.ename,b.ename as leadername,s.gradefrom 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.hisalwhere a.sal>(select avg(sal) as avgsal from emp);

结果：

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经典面试练习题：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经典面试练习题：24. 列出薪资等于部门30中员工的薪资的其他部门员工的姓名和薪资。

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

select distinct sal from emp where deptno=30;

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

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

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

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

函数：/

语句1：

select e.ename,e.sal,d.dnamefrom emp e join dept d on e.deptno=d.deptnowhere e.sal>(select max(sal) from emp where deptno=30);

语句2：

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

结果：

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经典面试练习题：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经典面试练习题：27. 列出*所有员工*的姓名、部门名称和工资。

错误语句：

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

错误结果：

说明：如果只用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经典面试练习题：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经典面试练习题：29. 列出各种工作的最低工资以及从事此工作的雇员姓名。

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

思路：/

函数：/

语句：

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

结果：

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经典面试练习题：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经典面试练习题：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经典面试练习题：33. 求部门名称中带s字符的部门员工的工资合计、部门人数。

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

思路：考察update用法。先以emp数据为基础新建表emp_new(不破坏emp数据)；然后更新emp_new的薪水*(1+10%)即1.1倍，服务年限的算法可参考题26。

函数：

步骤1)

新建表格：

create table emp_new as select * from emp;

查验数据：

MySQL经典面试练习题：34. 给任职日期超过30年的员工加薪10%。

步骤2)

更新数据：

update emp_new set sal=sal*1.1 where (datediff(curdate(),hiredate)/365)>30;

查验数据：

MySQL经典面试练习题：34. 给任职日期超过30年的员工加薪10%。

对比更新前后数据可知，所有员工入职入职年限均已超30年，所以每个人的薪资都增加了0.1倍。