老杜MySQL 34道题目详解

gurenchang

已于 2022-07-27 16:48:42 修改

阅读量1.7k

点赞数 4

文章标签： mysql sql 数据库

于 2022-07-27 16:40:34 首次发布

本文链接：https://blog.csdn.net/gurenchang/article/details/126002936

版权

1.每一个部门的最高工资人员名称

解析：

（1）按照部门编号进行分组求出每一个部门的工资最大值；

（2）把（1）的结果当成一个临时表与emp表进行连接，连接条件是部门号与最高薪资均相等。

代码参考：

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

结果截图：

2.高于每个部门的平均薪资的人

解析：

（1）按照部门编号进行分组求出每一个部门的工资平均值；

（2）把（1）的结果当成一个临时表与emp表进行连接，连接条件是部门号与大于平均薪资

代码参考：

select
	e.ename,
	t.* 
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.每一个部门平均薪水的等级

解析:

（1）按照部门编号进行分组求出每一个部门的工资平均值；

(2)把（1）的结果当成一个临时表与salgrade表进行连接，连接条件是t表的薪资在salgrade表的最高薪资与最低薪资之间

代码参考：

select 
t.*,s.*
from 
salgrade grade
join
(select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t
on
t.avgsal between losal and hisal;

结果截图：

3.1每一个部门的平均的薪水等级

解析：

（1）将emp表与salgrade表进行连接，条件是e表的薪资在salgrade表的最高薪资与最低薪资之间；

（2）按照部门进行分组，查找部门号、薪资等级

代码参考：

select e.deptno,avg(s.grade) as avggrade
from
emp e
join
salgrade s  
on
e.sal between s.losal and s.hisal
group by
e.deptno;

结果截图：

4.1不使用Max找出工资最高值

解析：

（1）将薪资按照降进行排序；

（2）使用limit进行截取

代码参考：

select sal 
from emp
order by
sal desc
limit 1;

结果解图：

4.2不使用Max找出工资最高值

解析：

（1）进行自连接，连接条件是a表的薪资大于b表的薪资，同时进行去重；

（2）在emp表里面查找不在（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.取得平均薪水最高部门的部门编号

解析：

（1）按照部门编号进行分组；

（2）获取每一个部门的平均薪资；

（3）对每一个部门的平均薪资按照降序进行排列；

（4）使用limit截取第一个，即为最高薪资

代码参考

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

结果截图：

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

解析：

（1）按照部门编号进行分组；

（2）获取每一个部门的平均薪资；

（3）对每一个部门的平均薪资按照降序进行排列；

（4）使用limit截取第一个，即为最高薪资

（5）将（1）-（4）的结果作为一个临时表与dept表进行连接，连接条件是两个表的部门编号一致

代码参考：

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

结果截图：

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

解析：

（1）按照部分分组求出每一个部门的平均薪资；
（2）将1当成一张临时表，与薪资等级表进行连接，求出各个部门的薪资等级，对薪资等级进行升序排序，截取第一个
（3）再将2当成一张临时表，与部门名称表格进行连接

代码参考：

select d.dname
from dept d
join
(select s.grade,t.deptno
from salgrade s
join
(select avg(sal) as avgsal,deptno from emp group by deptno) t
on t.avgsal between losal and hisal
order by
s.grade asc
limit 1) a
on
a.deptno = d.deptno;

结果截图：

9.每个部门的最高工资

解析：

（1）将emp表按照部门标号进行分组，求出每一部门的最高薪资；

（2）将（1）的结果作为一张临时表与emp表进行表连接，连接条件是两个表格的部门编号相等与员工的薪资与最高薪资相等

代码参考：

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

结果截图：

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

解析：

（1）将emp表中的薪资按照降序进行排序；

（2）使用limit截取前5个

代码参考：

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

结果截图：

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

解析:

（1）将emp表中的薪资按照降序进行排序；

（2）使用limit截取第6-10个

代码参考：

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

结果截图：

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

解析：

（1）将emp表中的入职日期按照降序进行排序；

（2）使用limit截取前5个

代码参考：

select hiredate,ename
from emp
order by hiredate desc
limit 5;

结果截图：

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

解析：

将emp表进行自连接，连接条件是a表的领导编号与b表的员工编号相等

代码参考：

select a.ename,e.ename as sel
from emp e
right join
emp a
on
a.mgr = e.empno;

结果截图:

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

解析：

（1）将emp表自连接，连接条件是a表的领导编号与e表的员工编号相等与员工的入职日期早于自己直接上级；

（2）继续连接dept表，连接条件是a表的员工编号与d表的员工编号相等

代码参考：

select a.empno,a.ename ,d.dname
from emp e
join
emp a
on
a.mgr = e.empno and a.HIREDATE < e.HIREDATE
join 
dept d
on
a.deptno = d.deptno;

结果截图：

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

解析：

（1）将emp表按照部门进行分组，使用having求出多于5个员工的部门；

（2）将dept表与（1）得到的临时表进行连接，连接条件是两者的部门编号相等

代码参考：

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

结果截图:

18.列出薪金比SMITH多的所有员工信息

解析：

（1）从emp表里面取出SMITH的薪资；

（2）将emp与（1）所得到的临时表进行连接，连接条件是emp表的薪资大于SMITH的薪资

代码参考：

select e.*
from emp e
join
(select sal
from emp
where
ename = 'SMITH') a
on
e.sal > a.sal;

结果截图:

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

解析:

(1)将emp表按照部门编号进行排序，求出每一个部门的人数；

(2)将(1)得到的临时表与dept表进行连接，连接条件是两者的部门编号相等；

(3)将以（1）-（2）得到的表格作为表格b

(4)在emp表中查找job等于clerk的员工以及他的部门编号；

（5）将dept表与（4）表格进行连接，连接条件是两者的部门编号相等：
（6）将（4）-（5）得到的作为表格a；

（7）将表格a与表格b进行连接，条件是员工的名字相同

代码参考：

SELECT
	a.ename,
	a.dname,
	b.personCount 
FROM
	(
	SELECT
		d.dname,
		t.personCount 
	FROM
		dept d
		JOIN ( SELECT count(*) AS personCount, deptno FROM emp GROUP BY deptno ) t ON d.deptno = t.deptno
	) b
	JOIN (
	SELECT
		t.ename,
		d.dname 
	FROM
		dept d
	JOIN ( SELECT ename, deptno FROM emp WHERE job = 'clerk' ) t ON t.deptno = d.deptno 
	) a ON a.dname = b.dname;

结果截图：

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

解析：

(1)将emp表按照部门编号进行排序，求出每一个部门的人数；

(2)将(1)得到的临时表与dept表进行连接，连接条件是两者的部门编号相等；

(3)将以（1）-（2）得到的表格作为表格a

(4)将emp表按照工作进行分组，查出各个部门中最低薪资都>1500的工作；

（5）将(4)的结果作为表格b

（6）将表格a,b进行连接，连接条件是两者的部门编号相等

代码参考：

select t.job,a.c
from (select d.deptno,t.c
from dept d
join
(select count(*) as c,deptno from emp group by deptno) t
on
d.deptno = t.deptno) a
join
(select e.job,min(e.sal) as minsal,e.deptno
from emp e 
group by
e.job
having minsal >1500) t
on
a.deptno = t.deptno;

结果截图：

21.列出在部门SALES销售部工作的员工的姓名,假定不知道销售部的部门编号

解析：

（1）在dept表中查询dname='SALES'的部门编号；

（2）在emp表中查找deptno等于（1）中查询的值

代码参考：

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

结果截图：

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

解析：

（1）在emp表中查找所有薪资的平均值，将结果作为一个临时表a

(2)将emp表将a继续连接，连接条件是e表的薪资大于a表的薪资；

（3）继续将emp表与dept表进行连接，连接条件是e表与d表的部门编号相同；

（4）继续将emp表与salgrade表进行连接，连接条件是e表的sal在s表的losal与hisal之间；

（5）继续将emp表进行左连接，连接条件是e表的mgr=empno

代码参考：

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

结果截图:

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

解析：

（1）将emp表与dept表连接，连接条件是两个表格的部门编号相同；

(2)还要满足工作与“SCOLL”相同同时要将"SCOLL"去掉

代码参考：

select e.ename,d.dname
from 
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'analyst' and e.ename != 'SCOTT';

结果截图：

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

解析：

（1）将emp表中部门编号等于30的薪资查找出来；

（2）将emp表与（1）所得到的临时表连接起来，连接条件是emp表的薪资等于临时表的薪资同时emp表的部门编号不等于30

代码参考：

select e.ename,e.sal
from emp e
join
(select sal
from emp 
where
deptno = 30) t
on
e.sal = t.sal and e.deptno <> 30;

结果截图：

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

解析：

（1）在emp表中查找部门编号等于30的最大薪资；

（2）将emp表与（1）所得的临时表进行连接，连接条件是emp表的薪资大于由（1）所得的最大薪资；

（3）继续将emp表与dept表进行连接，连接条件是两个的部门编号相同

代码参考：

select distinct e.ename,e.sal,d.dname
from emp e
join
(select max(sal) as maxsal
from emp 
where
deptno = 30) t
on
e.sal > t.maxsal
join
dept d
on
d.deptno = e.deptno;

结果截图：

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

解析：将emp表与dept表进行连接，连接条件是两个表格的部门编号相同

代码参考：

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

结果截图：

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

解析：

（1）将emp表按照部门编号进行分组，记录每一个部门的人数；

（2）将emp表与由（1）得到临时表a进行连接，连接条件是两者的部门编号相同

代码参考：

select d.*,a.personCount
from dept d
left join
(select count(deptno) as personCount,deptno
from
emp
group by
deptno) a
on
a.deptno = d.deptno;

结果截图：

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

解析：

（1）将emp表按照job进行分组求出每一个job的最低西薪资；

(2) 将emp表与由（1）得到的临时表t进行连接，连接的条件是两者的job相同与t.minsal与sal相同

代码参考：

select 
t.minsal,e.ename,e.job
from 
emp e
join
(select min(sal) as minsal,ename,job
from emp
group by job) t
on
e.job = t.job and e.sal = t.minsal;

结果截图：

30.列出各个部门的 MANAGER( 领导) 的最低薪金

解析：

（1）将emp表中所有的'MANAGER'全部选取出来；

（2）然后按照部门进行分组；

（3）使用min()函数取出最小值

代码参考：

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

结果截图：

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

解析：

（1）在emp表中将薪资乘以12求出年薪；

(2)然后对年薪进行升序排序

代码参考：

select ename,(sal*12) as annalsal
from emp
order by
annalsal asc;

结果截图：

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

解析：

（1）将emp进行子连接，连接条件是e表的empno与a的mgr相同；

（2）同时还需要满足e.sal>3000

代码参考：

select a.ename,e.ename as manager
from emp e
join
emp a
on
e.empno = a.mgr
where e.sal >3000;

结果截图:

33.求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数

解析：

（1）将emp表根据模糊查询进行查找；

（2）将emp表按照部门进行分组后对薪资进行求和；

（3）将（1）-（2）结果作为临时表a;

（4）将emp按照部门进行分类，计算每一个部门的人数，将此表作为临时表b；
（5）将表a,b进行连接，连接条件是两者的部门编号相同

代码参考：

select a.sumsal,b.count
from
(select sum(sal) sumsal,deptno
from emp
where ename like '%s%'
group by deptno) a
join
(select count(deptno) count,deptno
from emp
group by deptno) b
on
a.deptno = b.deptno;

结果截图：