mysql经典试题共34题

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