MySQL经典面试练习题 (34题含答案)~面试BAT再也不怕了

前言：

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

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

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

一 . 新建数据库及数据表

1. 新建数据库test2

语句：CREATE database test2;

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

语句：

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最高薪水)，并插入如下数据。

语句：

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部门编号)，并插入如下数据。

语句：

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表之间的联结关系，以便做题时使用。

二 . 题目

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;

结果：

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;

结果：

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;

结果：

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;

结果：

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

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

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

语句：

select sal from emp order by sal desc limit 1;

结果：

思路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;

结果：

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

结果：

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

结果：

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;

结果：

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;

结果：

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

结果：

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

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

函数：极限函数limit

语句：

select * from emp order by sal desc limit 5;

结果：

思路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;

结果：

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

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

函数：极限函数limit

语句：

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

结果：

思路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;

结果：

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;

结果：

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;

结果：

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

1）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课程表数据

语句：

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成绩表数据

语句：

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表之间联结关系，便于做题时使用。

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='黎明'));

结果：

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;

结果：

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

结果：

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;

正确结果：

错误语句：

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.deptno
where a.hiredate<b.hiredate
order by dname;

结果：

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;

结果：

错误语句：

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;

结果：

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

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

函数：/

语句：

select * from emp where sal>(select sal from emp where ename='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;

结果：

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

结果：

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

结果：

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

结果：

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;

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

select distinct sal from emp where deptno=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);

结果：

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;

结果：

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;

结果：

错误语句：

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;

结果：

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;

结果：

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

思路：/

函数：/

语句：

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

结果：

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;

结果：

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;

结果：

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;

结果：

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

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

函数：

步骤1）

新建表格：

create table emp_new as select * from emp;

查验数据：

步骤2）

更新数据：

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

查验数据：

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