MySQL练习题

练习题所使用的sql文件：链接：https://pan.baidu.com/s/1nAIDG_KcV6UOrdHxGsEACA 
提取码：6s72 

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

第一步：取得每个部门的最高薪水

select deptno,max(sal) from emp group by deptno;

第二步：将以上结果当做临时表t，与表emp连接，查询姓名。条件为部门号和薪水相同

select 
	e.ename, t.*
from 
	emp e
join
	(select deptno,max(sal) as maxsal from emp group by deptno) t
on
	t.deptno = e.deptno and t.maxsal = e.sal;

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

第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：将以上查询结果当做临时表t，与emp表进行连接

条件：部门号相同且工资大于部门的平均薪水

select 
	t.*, e.ename, e.sal
from
	emp e
join
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
on
	e.deptno = t.deptno and e.sal > t.avgsal;

3.找出部门中所有人的平均的薪水等级

注意：

        平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

        平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

第一步：找出每个人的薪水等级，emp和salgrade连接

条件：e.sal between s.losal and s.hisal

select 
		e.ename,e.sal,e.deptno,s.grade
	from
		emp e
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal;

第二步：根据deptno分组，计算出平均薪水等级

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 ），取得最高薪水

第一种：sal降序，limit 1

select ename,sal from emp order by sal desc limit 1;

第二种方案：表的自连接

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

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

第一种方案：找出每个部门的平均薪水，降序取第一个。

select 
    deptno,avg(sal) as avgsal 
from 
    emp 
group by 
    deptno 
order by 
    avgsal 
desc 
    limit 1;

第二种方案：max

        第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

        第二步：找出以上结果中avgsal最大的值。

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

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

select 
	d.dname,avg(e.sal) as avgsal 
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
group by 
	d.dname
order by 
	avgsal desc 
limit 
	1;

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

注意：

        平均薪水是800
        平均薪水是900
        那么他俩都是1级别。

第一步：找出每个部门的平均薪水以及部门名称

select 
    d.dname,avg(sal) as avgsal 
from 
    emp e 
join 
    dept d 
on 
    e.deptno = d.deptno 
group by 
    d.dname

第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.hisal

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 
    grade 
from 
    salgrade 
where 
    (select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1) 
between 
    losal 
and 
    hisal;

第四步：把第二三步连接在一起查询

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
	s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);

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

第一步：找到领导编号

select distinct mgr from emp where mgr is not null;

第二步：找到普通员工的最高工资（not in在使用的时候，后面小括号中记得排除NULL。）

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

 第三步：找到工资高于普通员工最高工资的领导的工资

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 
	s.grade ,count(*)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	s.grade;

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

select 
	a.ename '员工', b.ename '领导'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;

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

select 
	a.ename '员工', a.hiredate, b.ename '领导', b.hiredate, d.dname
from
	emp a
join
	emp b
on
	a.mgr = b.empno
join
	dept d
on
	a.deptno = d.deptno
where
	 a.hiredate < b.hiredate;

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

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

 17、列出至少有五个员工的部门的名称
 

第一步：找出至少有五个员工的部门的编号
 

select 
	deptno
from
	emp
group by
	deptno
having
	count(*) >= 5;

第二步：把以上结果作为表t，联立表dept进行查询

select
    t.deptno,d.dname
from
    dept d
join 
    (select deptno from emp group by deptno having count(*) >= 5) t
on 
    d.deptno = t.deptno;
    

 18、列出薪资比SMITH高的员工的所有信息

第一步：找到SMITH的工资

select sal from emp where ename = 'SMITH';

 第二步：找到比他高的

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

19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

第一步：找到所有工作是办事员的人的姓名以及部门编号和部门名称

select 
	e.ename,e.job,d.dname,d.deptno
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
where 
	e.job = 'CLERK';

 第二步：找出每个部门的人数

select deptno, count(*) from emp group by deptno;

第三步：联立以上两个结果

select 
	t1.*,t2.deptcount
from
	(select 
		e.ename,e.job,d.dname,d.deptno
	from 
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	where 
		e.job = 'CLERK') t1
join
	(select deptno, count(*) as deptcount from emp 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”工作的员工的名字，假设不知道销售部的部门编号

select 
    e.ename,d.dname 
from
    emp e
join
    dept d
on 
    e.deptno = d.deptno
where 
    d.dname = 'SALES';

 22、找出工资高于公司平均工资的所有员工，所再部门名称，上级领导的名称以及工资等级

select 
	e.ename '员工',d.dname,l.ename '领导',s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
left join
	emp l
on
	e.mgr = l.empno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
where
	e.sal > (select avg(sal) from emp);

 23、计算每个员工的年薪（工资加津贴）

select ename,(sal+comm)*12 as yearsal from emp;

output：

 +--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+

有很多为空值，这是为什么呢，这是因为许多人没有津贴，只要有NULL参与运算，结果一定是NULL。

正确写法：

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

24、找出与“SCOTT”从事相同工作的所有员工及部门名称。 

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

25、找出工资不等于  部门10中员工的工资  的其他员工的姓名和工资 

select 
	ename,sal 
from 
	emp 
where 
	sal not in(select distinct sal from emp where deptno = 10) 
and 
	deptno <> 10;

26、 找出工资高于  在部门30工作的所有员工的工资   的员工的工资和姓名

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

 27、找出在每个部门工作的员工的数量，平均工资和平均服务期限。没有员工的部门其值全部为0。

select 
	d.deptno, count(e.ename) ecount,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.deptno;

在mysql当中怎么计算两个日期的“年差”，差了多少年？
    TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
    timestampdiff(YEAR, hiredate, now())

    间隔类型：
        SECOND   秒，
        MINUTE   分钟，
        HOUR   小时，
        DAY   天，
        WEEK   星期
        MONTH   月，
        QUARTER   季度，
        YEAR   年

28、 找到所有员工的姓名，部门名称和工资。

select 
	e.ename,d.dname,e.sal
from
	emp e
join 
	dept d
on
	e.deptno = d.deptno;

29、找出所有部门的详细信息和人数。 

select 
    d.*,count(e.ename)
from 
    emp e
right join 
    dept d
on 
    e.deptno = d.deptno
group by 
    deptno;

30、找出各种工作的最低工资以及从事此工作的员工名称

第一步：找出各种工作的最低工资 

select job,min(sal) from emp group by job;

 第二步：联立

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;

31、 找出各个部门领导的最低工资

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

32、列出所有员工的年薪，从低到高排序

select 
	ename,(sal + ifnull(comm,0)) * 12 as yearsal
from
	emp
order by
	yearsal asc;

33、找出  员工的领导  的薪水超过3000的员工名称与其领导。 

select 
	a.ename '员工',b.ename '领导'
from
	emp a
join
	emp b
on
	a.mgr = b.empno
where
	b.sal > 3000;

34、找到部门名称中带‘S’字符的部门员工的工资总数以及部门人数。 

select 
	d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	d.dname like '%S%'
group by
	d.deptno,d.dname,d.loc;

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

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

 本篇文章是为了总结一下某节点MySQL学习视频，方便以后巩固复习，一共有三十四道练习题，每天都会做几道题然后记录到这里。原视频地址为：

MySQL基础入门到精通视频教程-MySQL数据库实战（内含MySQL34道面试题）_哔哩哔哩_bilibili