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