MySQL经典练习题+解题思路（一）

​

个人主页：BoBooY的CSDN博客_Java领域博主 前言：在上一期中我们讲解了MySQL的入门知识点，但理论总还是要通过实践来印证，学了再多不练也是白费，这一期我们讲解MySQL的一些经典查询例题帮助大家巩固已学知识点，如在做题过程中有知识点的遗忘，可以参考往期文章： MySQL入门知识点（上）：https://blog.csdn.net/qq_58233406/article/details/127143537 MySQL入门知识点（下）：https://blog.csdn.net/qq_58233406/article/details/127144532

文章目录

• MySQL练习题（一）
  • 导入练习数据
  • 1、取得每个部门最高薪水的人员名称
  • 2、哪些人的薪水在部门的平均薪水之上
  • 3、取得部门中（所有人的）平均的薪水等级
  • 4、不准用组函数（Max ），取得最高薪水
  • 5、取得平均薪水最高的部门的部门编号
  • 6、取得平均薪水最高的部门的部门名称
  • 7、求平均薪水的等级最低的部门的部门名称
  • 8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名(*)

MySQL练习题（一）

导入练习数据

代码语言：javascript复制

CREATE DATABASE `mysqlpractice`
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', '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, 0, 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); 
commit;
 
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, 9999); 
commit;

• 导入成功后，有如下三张表：

员工表

部门表

工资等级表

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

（1）先按照部门编号分组，取得每个部门的最高薪水。

代码语言：javascript复制

select 
	e.ename,e.deptno,max(e.sal) '最高薪水'
from
	emp e
group by 
	e.deptno;

（2）再将查询结果当做一张临时表，与emp表进行表连接（因为有的最高薪水是相同的人，需要将他们都显示出来）

代码语言：javascript复制

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

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

（1）先找出每个部门的平均薪水

代码语言：javascript复制

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

（2）找出每个部门比各自部门平均薪水高的人

条件：比平均薪水高，部门号相同

代码语言：javascript复制

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

3、取得部门中（所有人的）平均的薪水等级

（1）先找出每个人的薪资等级

代码语言：javascript复制

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

（2）再按部门分组，求得每个部门的平均薪资水平

代码语言：javascript复制

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

4、不准用组函数（Max ），取得最高薪水

• 方式一：使用limit分页

（1）将每个人的薪水进行降序排列，然后使用limit分页取第一个人的薪水

代码语言：javascript复制

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

（2）将结果当做一个临时表，与emp进行内连接 条件为emp表中的薪水 = 临时表中的薪水

代码语言：javascript复制

select 
	e.ename,e.sal
from
	emp e
join 
	(select 
		e.ename as ename,e.sal as sal
	from
		emp e
	order by
		e.sal desc
	limit 0,1
	) t
on
	e.sal = t.sal;

• 方式二：使用表的自连接

（1）将emp表自连接，找出 emp a表中所有比 emp b表中薪资小的薪水生成一个结果

代码语言：javascript复制

select 
	distinct a.sal
from
	emp a
join 
	emp b
on 
	a.sal < b.sal;

（2）再将emp中薪水不在这个结果中薪水找出来就是最高薪资

代码语言：javascript复制

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

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

（1）取得每个部门的平均薪水

代码语言：javascript复制

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

（2）将结果当做一张临时表进行取最高

代码语言：javascript复制

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

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

（1）按部门分组求得每个组的平均薪水

代码语言：javascript复制

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

（2）将结果与dept表进行内连接

代码语言：javascript复制

select 
	d.dname,t.deptno,max(t.avgsal) 
from 
	(select deptno ,avg(sal) avgsal from emp group by deptno) t
join 
	dept d
on 
	t.deptno = d.deptno;

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

（1）按部门分组求得每个组的平均薪水

代码语言：javascript复制

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

（2）将结果与salgrade表进行内连接 求得每个部门的部门等级,并取最低等级的部门

代码语言：javascript复制

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

（3）将结果与部门表进行内连接求得平均薪资水平最低的部门名称

代码语言：javascript复制

select
	d.dname,t2.deptno,t2.avgsal,t2.mingrade
from (
	select 
		t.deptno deptno,min(s.grade) mingrade,t.avgsal avgsal
	from 
		(select deptno ,avg(sal) avgsal from emp group by deptno) t
		join 
			salgrade s
		on 
			t.avgsal between s.losal and s.hisal
) t2
join
	dept d
on
	t2.deptno = d.deptno;

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

（1）找出所有是领导身份的员工代码

代码语言：javascript复制

select distinct mgr from emp where mgr is not null;

（2）不在上面结果的员工都是普通员工，取工资最高的

代码语言：javascript复制

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

（3）找出比这个最高工资大的员工的姓名和编号

代码语言：javascript复制

select 
	e.ename,e.empno
from 
	emp e
where(
	e.sal > (select 
				max(sal) 
			from 
				emp 
			where 
             	empno not in(select distinct mgr from emp where mgr is not null))
);

如果本篇文章的内容你都掌握了，那么我们开始练习下篇文章的内容吧：

MySQL经典练习题 解题思路（二）：https://blog.csdn.net/qq_58233406/article/details/127162943

MySQL经典练习题 解题思路（三）：https://blog.csdn.net/qq_58233406/article/details/127165622

MySQL经典练习题 解题思路（四）：https://blog.csdn.net/qq_58233406/article/details/127167682

云数据库SQLServer 数据库 sql https 网络安全

0 人点赞