mysql经典试题共34题

server/2025/3/14 16:24:00/

1、准备数据


--  drop
drop table if exists dept;
drop table if exists emp; 
drop table if exists salgrade;--  CREATE
CREATE TABLE `dept` (`deptno` int NOT NULL COMMENT '部门编号',`dname` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '部门名称',`loc` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '部门位置',PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `emp` (`empno` int NOT NULL COMMENT '员工编号',`ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '员工名字',`job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '员工岗位',`mgr` int DEFAULT NULL COMMENT '上级领导编号',`hiredate` date DEFAULT NULL COMMENT '入职日期',`sal` double(7,2) DEFAULT NULL COMMENT '月薪',`comm` double(7,2) DEFAULT NULL COMMENT '补助',`deptno` int DEFAULT NULL COMMENT '部门编号',PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `salgrade` (`grade` int DEFAULT NULL COMMENT '薪水等级',`losal` int DEFAULT NULL COMMENT '最低薪水',`hisal` int DEFAULT NULL COMMENT '最高薪水'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;--  INSERT
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');INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7369, 'SIMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, NULL, 30);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 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、试题与解答

-- 经典面试题
-- 表：dept（部门）、emp（员工）、salgrade（薪水等级）-- 1、取得每个部门最高薪水的人员名称
-- (1)取得每个部门最高薪水select deptno, max(sal) MAXSAL from emp GROUP BY deptno;-- (2)条件：部门名称和薪水select t.deptno, e.ename, t.MAXSAL from (select deptno, max(sal) MAXSAL from emp GROUP BY deptno) t LEFT JOIN emp e ON e.deptno = t.deptno and e.sal = t.MAXSAL ORDER BY t.deptno;-- 2、哪些人的薪水在部门平均薪水之上
-- (1) 部门平均薪水select deptno, AVG(sal) from emp GROUP BY deptno;
-- (2) 查询哪些人 条件：部门编号和薪水select e.ename, e.sal, t.deptno, t.AVGSALfrom emp e JOIN (select deptno, AVG(sal) AVGSAL from emp GROUP BY deptno) t on e.deptno = t.deptno and e.sal > t.AVGSALORDER BY t.deptno;-- 3、查各部门平均薪水等级
-- 先查部门平均薪水，再查等级select t.deptno, t.AVGSAL, s.*from salgrade sJOIN (select e.deptno, AVG(e.sal) AVGSAL from emp e GROUP BY e.deptno) ton t.AVGSAL between s.losal and s.hisalORDER BY t.deptno; -- 4、不准用组函数，取得最高薪水（给出两种解决方案）
-- 5、取得平均薪水最高的部门编号（至少两个方案）
-- 6、取得平均薪水最高的部门的部门名称
-- (1)先查每个部门的平均薪水
select deptno, AVG(sal) AVGSAL from emp GROUP BY deptno ORDER BY AVGSAL desc LIMIT 0,1;
-- (2)查薪水最高的部门
select d.deptno, d.dname, t.AVGSAL from dept d,(select deptno, AVG(sal) AVGSAL from emp GROUP BY deptno ORDER BY AVGSAL desc LIMIT 1) t
where d.deptno = t.deptno;
-- 7、求平均薪水等级最高的部门的名称
-- (1)每个部门的平均薪水
select e.deptno, d.dname, AVG(e.sal) AVGSAL from emp e, dept d where e.deptno = d.deptno GROUP BY e.deptno, d.dname;
-- (2) 每个部门的薪水等级
select t.deptno, t.dname, s.grade
from (select e.deptno, d.dname, AVG(e.sal) AVGSAL from emp e, dept d where e.deptno = d.deptno GROUP BY e.deptno, d.dname) t, salgrade s 
where t.AVGSAL between s.losal and s.hisal;
-- (3)查最高的部门薪水等级
select max(s.grade) maxgrade from salgrade s 
JOIN (select e.deptno, d.dname, AVG(e.sal) AVGSAL from emp e, dept d where e.deptno = d.deptno GROUP BY e.deptno, d.dname) t 
on t.AVGSAL between s.losal and s.hisal;
-- (4)薪水等级最高的部门的名称（第二个条件与第三个条件即：最高等级有几个部门）
select t1.*,t2.maxgrade
from (select t.deptno, t.dname, s.grade from (select e.deptno, d.dname, AVG(e.sal) AVGSAL from emp e, dept d where e.deptno = d.deptno GROUP BY e.deptno, d.dname) t, 			salgrade s where t.AVGSAL between s.losal and s.hisal) t1 
JOIN (select max(s.grade) maxgrade from salgrade s JOIN (select e.deptno, d.dname, AVG(e.sal) AVGSAL from emp e, dept d where e.deptno = d.deptno GROUP BY e.deptno, d.dname) t on t.AVGSAL between s.losal and s.hisal) t2 
on t1.grade = t2.maxgrade;
-- 8、取得比普通员工的最高薪水还要高的经理人姓名
select COUNT(1) from emp;
-- (1) 查到所有经理人
select DISTINCT mgr from emp where mgr is not NULL;
-- (2) 查所有的员工(即：非经理人、非king)的最大工资
select max(sal) from emp where empno not in (select DISTINCT mgr from emp where mgr is not NULL);
-- (3) 取得经理人姓名
select e.* from emp e JOIN (select max(sal) maxsal from emp where empno not in (select DISTINCT mgr from emp where mgr is not NULL)) t on e.sal > t.maxsal;
-- 9、取得薪水最高的前五名员工
-- 10、取得薪水最高的第六到第十名员工
select * from emp ORDER BY sal desc;
/*
SELECT * FROM table_name LIMIT offset, row_count;
offset：表示跳过的行数，从0开始计数。
row_count：表示返回的行数。
如果只指定一个参数，LIMIT 默认从第0行开始返回指定的行数。
*/
select * from emp ORDER BY sal desc limit 5,5;
-- 11、取得最后入职的五名员工
-- 12、取得每个薪水等级有多少员工
select s.grade,count(1) from emp e,salgrade s where e.sal between s.losal and s.hisal GROUP BY s.grade;
-- 13、学生、课程、学生课程表   -- 略
-- 14、列出所有员工及领导的名字
select e1.empno "员工编号",e1.ename "员工姓名", e1.mgr "经理编号", e2.ename "经理姓名" from emp e1 
LEFT JOIN (select * from emp) e2 on e1.mgr = e2.empno;
-- 15、列出受雇佣日期早于其直接上级的所有员工编号、姓名、部门名称
-- (1) 找出受雇佣日期早于其直接上级的所有员工编号、姓名
select e1.empno, e1.ename,e1.deptno from emp e1 LEFT JOIN emp e2 on e1.mgr = e2.empno where e1.hiredate < e2.hiredate;
-- (2)找出部门名称（与dept表连接）
select t.empno, t.ename, d.dname from dept d 
JOIN (select e1.empno, e1.ename,e1.deptno from emp e1 LEFT JOIN emp e2 on e1.mgr = e2.empnowhere e1.hiredate < e2.hiredate) t 
on d.deptno = t.deptno;
-- 16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select e.*,d.dname from emp e RIGHT JOIN dept d on e.deptno = d.deptno;
-- 17、列出至少有5个员工的所有部门(having对分组后的数据过滤：查询每组的记录总数)
select d.dname, count(1) count from emp e JOIN dept d on e.deptno = d.deptno GROUP BY d.dname HAVING count < 5;
-- 18、列出薪水比"SIMITH"多的所有员工信息
select e.* from emp e where e.sal > (select sal FROM emp where ename='SIMITH');
-- 19、列出所有"CLERK"（办事员）的姓名及其部门名称、部门人数
select e.ename,d.deptno,d.dname from (select * from emp where job = 'CLERK') e JOIN dept d on e.deptno = d.deptno;select deptno, count(1) from emp GROUP BY deptno; select e.*,t.deptnum from (select e.ename,d.deptno,d.dname from (select * from emp where job = 'CLERK') e JOIN dept d on e.deptno = d.deptno) e left JOIN (select deptno, count(1) deptnum from emp GROUP BY deptno) t on e.deptno = t.deptno;
-- 20、列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
-- WHERE子句用于在查询时对数据行进行过滤，它在分组操作之前执行。因此，WHERE子句中不能直接使用分组后的聚合结果。
-- HAVING子句的作用是在分组后对分组结果进行过滤
select job, min(sal) minsal, count(job) total from emp GROUP BY job HAVING minsal > 1500;
-- 21、列出在部门"SALES"《销售部》工作的员工的姓名，假定不知道销售部门的部门编号
select deptno,dname from dept where dname = 'SALES';
select e.ename from emp e where e.deptno = (select deptno from dept where dname = 'SALES');
-- 22、列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级
select e.*,d.dname,e2.ename "上级领导", s.grade from emp e 
JOIN dept d on e.deptno = d.deptno
LEFT JOIN emp e2 on e.mgr = e2.empno
JOIN salgrade s on e.sal between s.losal and s.hisal
where e.sal > (select avg(sal) from emp);
-- 23、列出与"SCOTT"从事相同工作的所有员工及部门名称
select e.job,d.dname from emp e JOIN dept d on e.deptno = d.deptno and e.ename='SCOTT';
select e.*, t.dname from emp e JOIN (select e.job,d.dname from emp e JOIN dept d on e.deptno = d.deptno and e.ename='SCOTT') t 
on e.job = t.job;
-- 24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
select sal from emp where deptno=30;
-- 25、列出薪金高于在部门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);
-- 26、列出在每个部门工作的员工数量、平均工资和平均服务期限
SELECTd.deptno,count( e.deptno ),IFNULL( avg( e.sal ), 0 ),IFNULL(avg(( TO_DAYS( NOW()) - TO_DAYS( hiredate )) / 365 ) , 0)
FROMemp eRIGHT JOIN dept d ON e.deptno = d.deptno 
GROUP BYd.deptno;
-- 27、列出所有员工的姓名、部门名称和工资
-- 28、列出所有部门的详细信息和人数
select deptno,count(empno) from emp GROUP BY deptno;
select d.*,IFNULL(t.total,0) from dept d left JOIN (select deptno,count(empno) total from emp GROUP BY deptno) t on d.deptno = t.deptno;
-- 29、列出各种工作的最低工资及从事此工作的雇员姓名
select job,min(sal) minsal from emp GROUP BY job;
select e.job,e.minsal,e2.ename from (select job,min(sal) minsal from emp GROUP BY job) e 
JOIN emp e2 on e.minsal = e2.sal; 
-- 30、列出每个部门MANAGER的最低薪资
select * from emp where job="MANAGER";
select min(sal) minsal,deptno from emp where job="MANAGER" group by deptno;
-- 31、列出所有员工的年工资，按年薪从低到高排序
select empno,ename,sal,comm,(sal + IFNULL(comm, 0)) * 12 AS "年薪" from emp ORDER BY 年薪;
-- 32、求出员工领导的薪水超过3000的员工名称和领导名称
select e1.empno,e1.ename,e1.sal, e2.empno "领导编号",e2.sal "领导薪水",e2.ename "领导姓名" 
from emp e1 JOIN emp e2 on e1.mgr = e2.empno and e2.sal > 3000;
-- 33、求部门名称中带"S"字符的部门员工的工资合计、部门人数
select deptno from dept where dname like "%S%";
select t.deptno,IFNULL(sum(e.sal),0),count(e.empno) from emp e RIGHT JOIN (select deptno from dept where dname like "%S%") t on e.deptno = t.deptno GROUP BY t.deptno;
-- 34、给任职日期超过30年的员工加薪10%
select empno,sal from emp where ((TO_DAYS(NOW()) - TO_DAYS(hiredate))/365) > 30;
drop table if EXISTS emp_bak;
create table emp_bak as select * from emp;
update emp_bak set sal = (sal*1.1) where (TO_DAYS(NOW()) - TO_DAYS(hiredate))/365 > 30;

参考：SQL经典训练试题_哔哩哔哩_bilibili


http://www.ppmy.cn/server/174926.html

相关文章

【H2O2 | 软件开发】Axios发送Http请求

【H2O2 | 软件开发】Axios发送Http请求

目录 前言 开篇语 准备工作 正文 概念 封装工具包 示例 结束语 前言 开篇语 本系列为短篇&#xff0c;每次讲述少量知识点&#xff0c;无需一次性灌输太多的新知识点。该主题文章主要是围绕前端、全栈开发相关面试常见问题撰写的&#xff0c;希望对诸位有所帮助。 如…
阅读更多...
概率论的基本知识

概率论的基本知识

逆概率还不懂&#xff0c;改天再想想。 联合概率 联合概率&#xff08;Joint Probability&#xff09; 是概率论中的一个重要概念&#xff0c;用于描述多个随机变量同时取某些值的概率。联合概率可以帮助我们理解多个变量之间的关系。
阅读更多...
Python编程中常见的10个案例

Python编程中常见的10个案例

文章目录 1. Hello, World!2. 计算斐波那契数列3. 文件读写4. 列表推导式5. 异常处理6. 函数定义与调用7. 类和对象8. 使用模块9. 网络请求10. 数据可视化总结 1. Hello, World! 这是学习任何编程语言时的第一个程序。 代码示例 print("Hello, World!")2. 计算斐波…
阅读更多...
请简述一下String、StringBuffer和“equals”与“==”、“hashCode”的区别和使用场景

请简述一下String、StringBuffer和“equals”与“==”、“hashCode”的区别和使用场景

在 Java 中&#xff0c;String 和 StringBuffer 是两种不同的字符串处理类&#xff0c;而 equals() 方法和 操作符、hashCode() 方法在字符串比较和哈希表操作中有不同的用途和语义。以下是它们的区别和使用场景的详细说明&#xff1a; 1. String 和 StringBuffer 的区别 1.1…
阅读更多...
Vue.js 基础入门：从零开始构建你的第一个 Vue 应用

Vue.js 基础入门：从零开始构建你的第一个 Vue 应用

Vue.js 是一个轻量级、易上手的渐进式 JavaScript 框架&#xff0c;广泛用于构建现代化的用户界面。无论你是前端新手还是有一定经验的开发者&#xff0c;Vue.js 都能帮助你快速构建高效、可维护的 Web 应用。本文将带你从零开始学习 Vue.js 的基础知识&#xff0c;并完成一个简…
阅读更多...
AI自动化编程初探

AI自动化编程初探

先说vscodeclinemodelscope方案&#xff0c;后面体验trae或者cursor再写写其它的。vscode和trae方案目前来说是免费的&#xff0c;cursor要用claud需要付费&#xff0c;而且不便宜&#xff0c;当然效果可能是最好的。 vscode方案&#xff0c;我的经验是最好在ubuntu上&#xff…
阅读更多...
工业物联网平台系列技术介绍-分类、聚类、逻辑回归

工业物联网平台系列技术介绍-分类、聚类、逻辑回归

前言 工业物联网平台在运行过程中&#xff0c;设备运行数据、传感器数据等如潮水般涌现&#xff0c;这些数据类型丰富&#xff0c;涵盖设备的实时状态、性能参数、环境监测数值等。面对如此规模与复杂度的数据&#xff0c;亟需高效的数据处理与分析手段。分类、聚类、逻辑回归…
阅读更多...
MongoDB 介绍与部署

MongoDB 介绍与部署

MongoDB 的 Schema-less&#xff08;无模式&#xff09;特性 MongoDB 不需要提前定义表结构&#xff08;Schema&#xff09;&#xff0c;可以存储不同结构的文档&#xff08;JSON/BSON&#xff09;&#xff0c;这与传统的 SQL 关系型数据库&#xff08;RDBMS&#xff09;有很大…
阅读更多...
最新文章

Copyright @ 2022~2023