MySQL入门（9）- 34道练习题（内含bjpowernode.sql数据）

关注

VIP免费原创最新推荐文章于 2026-04-16 09:38:52 发布 1.2w 阅读 AI 写同款· GEO 优化 ›

MySQL学习笔记-B站动力节点 MySQL 1、sql、DB、DBMS分别是什么，他们之间的关系？ DB: DataBase（数据库，数据库实际上在硬盘上以文件的形式存在） DBMS: DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer...） SQL: 结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。 SQL属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。 SQL语句在执行的时阅读详情

34道练习题（内含bjpowernode.sql数据）

此系列记录PN视频学习笔记

* 本博客涉及到的数据为bjpowernode.sql，数据见文末。

* 该数据有三张表：部门表dept（deptno部门编号、dname部门名称、loc位置）、

员工表emp（empno工号、ename员工姓名、job职位、mgr直属领导工号、

hiredate入职日期、sal月薪、comm补贴、deptno部门编号）、

薪水等级表salgrade（grade等级、losal区间下限、hisal区间上限）

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

mysql> select t.deptno,e.ename,t.msal from emp e

-> join (selectdeptno,max(sal) msal from emp group by deptno) t

-> on e.sal =t.msal;

+--------+-------+---------+

| deptno | ename | msal |

+--------+-------+---------+

| 30 | BLAKE |2850.00 |

| 20 | SCOTT |3000.00 |

| 10 | KING | 5000.00 |

| 20 | FORD | 3000.00 |

+--------+-------+---------+

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

思路：先求平均薪水构成表a2；联立emp e表，和a2表

mysql>select a.ename,a.sal,a.deptno

-> from emp a

-> join (select deptno,avg(sal) asalfrom emp group by deptno) b

-> on a.sal > b.asal and a.deptno =b.deptno;

mysql>create table a2 as select deptno,avg(sal) asal from emp group by deptno;

mysql>select e.ename,e.sal,e.deptno,a2.asal from emp e,a2 where e.sal > a2.asaland e.deptno = a2.deptno;

+-------+---------+--------+-------------+

+-------+---------+--------+-------------+

| ALLEN | 1600.00 | 30 | 1566.666667 |

| JONES | 2975.00 | 20 | 2175.000000 |

| BLAKE | 2850.00 | 30 | 1566.666667 |

| SCOTT | 3000.00 | 20 | 2175.000000 |

| KING | 5000.00 | 10 | 2916.666667 |

| FORD | 3000.00 | 20 | 2175.000000 |

+-------+---------+--------+-------------+

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

思路：计算每个人的薪水等级；然后根据部门编号分组

select e.ename,e.deptno,s.gradefrom emp e

join salgrades on e.sal between s.losal and s.hisal

group by e.deptno；

mysql>select e.deptno,avg(s.grade) agrade from emp e

-> join salgrade s on e.sal betweens.losal and s.hisal

-> group by e.deptno;

+--------+--------+

| deptno |agrade |

+--------+--------+

| 10 | 3.6667 |

| 20 | 2.8000 |

| 30 | 2.5000 |

+--------+--------+

4、求最高薪水（给出两种解决方案）

法一：mysql>select ename, max(sal) from emp;

+--------+----------+

| ename | max(sal) |

+--------+----------+

| SIMITH | 5000.00 |

+--------+----------+

法二：mysql>select ename,sal from emp order by sal desc limit 1;

+-------+---------+

| ename | sal |

+-------+---------+

| KING | 5000.00 |

+-------+---------+

法三：mysql>select ename,sal from emp where sal not in(select distinct a.sal from emp ajoin emp b on a.sal < b.sal);

+-------+---------+

| ename | sal |

+-------+---------+

| KING | 5000.00 |

+-------+---------+

5、取得平均薪水最高的部门和部门编号（至少给出两种解决方案）

mysql>create table a5 as select deptno,avg(sal) asal from emp group by deptno;

法一：mysql> select deptno,max(asal) masal from a5;

+--------+-------------+

| deptno | masal |

+--------+-------------+

| 10| 2916.666667 |

+--------+-------------+

select deptno,avg(sal) asal from emp group by deptno havingavg(sal) = (select max(t.asal) from (select deptno,avg(sal) asal from emp groupby deptno having avg(sal)) t);

法二：mysql>select deptno, asal from a5 order by asal desc limit 1;

+--------+-------------+

| deptno | asal |

+--------+-------------+

| 10| 2916.666667 |

+--------+-------------+

select deptno,avg(sal) asal from emp group by deptno having avg(sal) =(select avg(sal) from emp group by deptno order by avg(sal) desc limit 1);

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

mysql>create table a6 as select deptno,avg(sal) asal from emp group by deptno;

mysql>select d.dname, max(a6.asal) from dept d,a6 where d.deptno = a6.deptno;

+------------+--------------+

|dname | max(a6.asal) |

+------------+--------------+

|ACCOUNTING | 2916.666667 |

+------------+--------------+

select d.dname,avg(e.sal)asal from emp e join dept d on e.deptno = d.deptno group by d.dname havingavg(e.sal) = (select avg(sal) from emp group by deptno order by avg(sal) desclimit 1);

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

mysql>select d.dname, avg(s.grade) agrade from emp e join dept d on e.deptno =d.deptno join salgrade s on e.sal between s.losal and s.hisal group by d.dnameorder by agrade asc limit 1;

+-------+--------+

| dname |agrade |

+-------+--------+

| SALES |2.5000 |

+-------+--------+

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

mysql>select * from emp where empno not in(select distinct mgr from emp);

Empty set(0.00 sec)

无法查询到结果，原因是not in语句不会自动忽略空值，需要程序员手动排除NULL，即在语句后添加‘where mgr is not null’（这里KING没有mgr），但是in可以自动忽略空值

mysql>select * from emp where empno not in(select distinct mgr from emp where mgr isnot null);

+-------+--------+----------+------+------------+---------+---------+--------+

+-------+--------+----------+------+------------+---------+---------+--------+

| 7369 | SIMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |

| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |

| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |

| 7654 | MARTIN | SALEDMAN | 7698 | 1981-09-28| 1250.00 | 1400.00 | 30 |

| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08| 1500.00 | NULL | 30 |

| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 |1100.00 | NULL | 20 |

| 7900 | JAMES | CLERK | 7698 | 1981-12-03| 950.00 | NULL | 30 |

| 7934 | MILLER | CLERK |7782 | 1982-01-23 | 1300.00 | NULL| 10 |

+-------+--------+----------+------+------------+---------+---------+--------+

mysql>select * from emp where empno in(select distinct mgr from emp);

+-------+-------+-----------+------+------------+---------+------+--------+

+-------+-------+-----------+------+------------+---------+------+--------+

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 |3000.00 | NULL | 20 |

| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |

| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |

| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |

+-------+-------+-----------+------+------------+---------+------+--------+

selectb.ename,b.sal

from (selectmax(sal) msal from emp where empno not in(select distinct mgr from emp wheremgr is not null)) a

join (select* from emp where empno in(select distinct mgr from emp) b

on a.msal< b.sal;

+-------+---------+

| ename |sal |

+-------+---------+

| JONES |2975.00 |

| BLAKE | 2850.00|

| CLARK |2450.00 |

| SCOTT |3000.00 |

| KING | 5000.00 |

| FORD | 3000.00 |

+-------+---------+

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

mysql> select *

最低0.47元/天解锁文章

标签

#MYSQL入门 #练习题 #select #update #create

bjpowernode.sql 动力节点 sql bjpowernode.sql 立即下载

MySQL入门（9）- 34道练习题（内含bjpowernode.sql数据）

34道练习题（内含bjpowernode.sql数据）

相关推荐