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.AVGSAL from emp e JOIN (select deptno, AVG(sal) AVGSAL from emp GROUP BY deptno) t on e.deptno = t.deptno and e.sal > t.AVGSAL ORDER BY t.deptno; – 3、查各部门平均薪水等级 – 先查部门平均薪水，再查等级 select t.deptno, t.AVGSAL, s.* from salgrade s JOIN (select e.deptno, AVG(e.sal) AVGSAL from emp e GROUP BY e.deptno) t on t.AVGSAL between s.losal and s.hisal ORDER 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.empno where 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、列出在每个部门工作的员工数量、平均工资和平均服务期限 SELECT d.deptno, count( e.deptno ), IFNULL( avg( e.sal ), 0 ), IFNULL(avg(( TO_DAYS( NOW()) - TO_DAYS( hiredate )) / 365 ) , 0) FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno GROUP BY d.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; 参考：