MySql例题进阶

最新推荐文章于 2025-04-23 14:34:36 发布

原创最新推荐文章于 2025-04-23 14:34:36 发布 · 275 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

习题

博客主要围绕MySQL数据库展开，包含创建dept、empty、salgrade等表并插入数据，还给出一系列面试题的SQL解答，如取得各部门最高薪水人员、找出薪水在部门平均薪水之上的人等，涉及多种数据查询和统计操作。

–创建dept，empty，salgrade三张表并插入数据
– Table structure for dept

DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
deptno int(2) NOT NULL,
dname varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
loc varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (deptno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

– Records of dept

INSERT INTO dept VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO dept VALUES (20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept VALUES (30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept VALUES (40, ‘OPERATIONS’, ‘BOSTON’);

– Table structure for emp

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
empno int(4) NOT NULL,
ename varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
job varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
mgr int(4) NULL DEFAULT NULL,
hiredate date NULL DEFAULT NULL,
sal double(7, 2) NULL DEFAULT NULL,
comm double(7, 2) NULL DEFAULT NULL,
deptno int(2) NULL DEFAULT NULL,
PRIMARY KEY (empno) USING BTREE,
INDEX FK_DEPTNO(deptno) USING BTREE,
CONSTRAINT FK_DEPTNO FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

– Records of emp

INSERT INTO emp VALUES (7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-07’, 800.00, NULL, 20);
INSERT INTO emp VALUES (7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20’, 1600.00, 300.00, 30);
INSERT INTO emp VALUES (7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1250.00, 500.00, 30);
INSERT INTO emp VALUES (7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-02-04’, 2975.00, NULL, 20);
INSERT INTO emp VALUES (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1250.00, 1400.00, 30);
INSERT INTO emp VALUES (7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-01-05’, 2850.00, NULL, 30);
INSERT INTO emp VALUES (7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2450.00, NULL, 10);
INSERT INTO emp VALUES (7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-04-19’, 3000.00, NULL, 20);
INSERT INTO emp VALUES (7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981-11-17’, 5000.00, NULL, 10);
INSERT INTO emp VALUES (7844, ‘TUR%NER’, ‘SALESMAN’, 7698, ‘1981-09-08’, 1500.00, 0.00, 30);
INSERT INTO emp VALUES (7876, ‘ADAMS’, ‘CLERK’, 7788, ‘1987-05-23’, 1100.00, NULL, 20);
INSERT INTO emp VALUES (7900, ‘JAMES’, ‘CLERK’, 7698, ‘1981-03-12’, 950.00, NULL, 30);
INSERT INTO emp VALUES (7902, ‘FORD’, ‘ANALYST’, 7566, ‘1981-03-12’, 3000.00, NULL, 20);
INSERT INTO emp VALUES (7934, ‘MILLER’, ‘CLERK’, 7782, ‘1982-01-23’, 1300.00, NULL, 10);

– Table structure for salgrade

DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade (
grade int(11) NULL DEFAULT NULL,
losal int(11) NULL DEFAULT NULL,
hisal int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

– Records of salgrade

INSERT INTO salgrade VALUES (1, 700, 1200);
INSERT INTO salgrade VALUES (2, 1201, 1400);
INSERT INTO salgrade VALUES (3, 1401, 2000);
INSERT INTO salgrade VALUES (4, 2001, 3000);
INSERT INTO salgrade VALUES (5, 3001, 9999);

SET FOREIGN_KEY_CHECKS = 1;

– 15.1. 取得每个部门最高薪水的人员名称
SELECT e.ename,t.dname,t.maxsal FROM emp e JOIN (SELECT d.dname,MAX(sal) maxsal FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY dname) t ON e.sal=t.maxsal ORDER BY t.maxsal DESC;

– 15.2. 哪些人的薪水在部门平均薪水之上
SELECT e.ename,t.dname, e.sal, t.avgsal FROM emp e JOIN (SELECT d.dname,avg(sal) avgsal FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY dname) t
ON e.sal>t.avgsal ORDER BY t.avgsal DESC;

– 15.3.取得部门中（所有人的）平均薪水等级
SELECT t.dname ,t.avgsal,salgrade.grade FROM (SELECT d.dname,avg(sal) avgsal FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY dname) t JOIN salgrade ON t.avgsal BETWEEN salgrade.losal AND salgrade.hisal ORDER BY t.avgsal DESC;

– 15.4. 不准用组函数（MAX），取得最高薪水（给出两种解决方案）
SELECT MAX(sal) FROM emp ;

SELECT sal FROM emp ORDER BY sal DESC LIMIT 1;

SELECT sal FROM emp WHERE sal not in(SELECT DISTINCT e.sal FROM emp e JOIN emp t on e.sal<t.sal);

– 15.5. 取得平均薪水最高的部门的部门编号
SELECT t.deptno ,max(avgsal) FROM (SELECT e.deptno, d.dname,avg(sal) avgsal FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY dname) t;

– 15.6.**取得平均薪水最高的部门的部门名称
SELECT t.dname ,max(avgsal) FROM (SELECT e.deptno, d.dname,avg(sal) avgsal FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY dname) t;

– 15.7.***求平均薪水的等级最低的部门的部门名称
SELECT f.dname,min(f.fgrade) from (SELECT t.dname ,t.avgsal,salgrade.grade fgrade FROM (SELECT d.dname,avg(sal) avgsal FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY dname) t JOIN salgrade ON t.avgsal BETWEEN salgrade.losal AND salgrade.hisal) f;

– 15.8.取得比普通员工(员工代码没有在mgr上出现的)最高薪水还要高的经理人姓名
SELECT distinct ename,sal FROM emp WHERE sal> (SELECT MAX(sal) FROM emp WHERE empno not in (SELECT DISTINCT mgr FROM emp WHERE mgr is not NULL ) );

– 15.9. 取得薪水最高的前五名员工
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 0,5;

– 15.10. 取得薪水最高的第六到第十名员工
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5,6;

– 15.11. 取得最后入职的5名员工
SELECT * from emp ORDER BY hiredate DESC LIMIT 0,5;

– 15.12. 取得每个薪水等级有多少员工
SELECT s.grade,COUNT(s.grade) ‘人数’ FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal GROUP BY s.grade;

– 15.13. 面试题
– DROP TABLE IF EXISTS SC;
– CREATE TABLE SC (
– SNO VARCHAR ( 200 ),
– CNO VARCHAR ( 200 ),
– SCGRADE VARCHAR ( 200 )
– );

– DROP TABLE IF EXISTS S;
– CREATE TABLE S(
– SNO VARCHAR(200 ),
– SNAME VARCHAR(200)
– );

– DROP TABLE IF EXISTS C;
– CREATE TABLE C(
– CNO VARCHAR(200),
– CNAME VARCHAR(200),
– CTEACHER VARCHAR(200)
– );
– INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘1’, ‘诧文’, ‘张’);
– INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘2’, ‘政治’, ‘王’);
– INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘3’, ‘英诧’, ‘李’);
– INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘4’, ‘数学’, ‘赵’);
– INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘5’, ‘物理’, ‘黎明’);

– INSERT INTO S ( SNO, SNAME ) VALUES ( ‘1’, ‘学生1’);
– INSERT INTO S ( SNO, SNAME ) VALUES ( ‘2’, ‘学生2’);
– INSERT INTO S ( SNO, SNAME ) VALUES ( ‘3’, ‘学生3’);
– INSERT INTO S ( SNO, SNAME ) VALUES ( ‘4’, ‘学生4’);

– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘1’, ‘40’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘2’, ‘30’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘3’, ‘20’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘4’, ‘80’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘5’, ‘60’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘1’, ‘60’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘2’, ‘60’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘3’, ‘60’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘4’, ‘60’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘5’, ‘40’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘3’, ‘1’, ‘60’);
– INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘3’, ‘3’, ‘80’);
– COMMIT;

--  1，找出没选过“黎明”老师的所有学生姓名。
SELECT SNAME from s WHERE sno not in (
SELECT sno from sc WHERE cno=(SELECT cno FROM c WHERE CTEACHER='黎明')
);

--  2，列出2门以上（含2门）不及格学生姓名及平均成绩。
SELECT sname,avg(sc.SCGRADE) '平均成绩' FROM s JOIN ( 
SELECT sno,count(*) num FROM sc WHERE SCGRADE<60 GROUP BY sno HAVING num>=2 
) t
on s.sno=t.sno JOIN sc on s.SNO=sc.SNO;

--  3，即学过1号课程又学过2号课所有学生的姓名
SELECT sno,SNAME from s WHERE sno in (SELECT a.sno FROM sc a JOIN sc b on a.cno=1 AND b.cno=2);

-- 	15.13.2. 找出没选过“黎明”老师的所有学生姓名
SELECT SNAME from s WHERE sno not in (SELECT sno from sc WHERE cno=(SELECT cno FROM c WHERE CTEACHER='黎明'));

-- 15.13.3. 列出2门以上（含2门）丌及格学生姓名及平均成绩
SELECT sname,avg(sc.SCGRADE) '平均成绩' FROM s JOIN ( SELECT sno,count(*) num FROM sc WHERE SCGRADE<60 GROUP BY sno HAVING num>=2 ) t on s.sno=t.sno JOIN sc on s.SNO=sc.SNO;

-- 15.13.4. 即学过1号课程又学过2号课所有学生的姓名
SELECT sno,SNAME from s WHERE sno in (SELECT a.sno FROM sc a JOIN sc b on a.cno=1 AND b.cno=2);

– 15.14. 列出所有员工及领导的名字
SELECT a.ename ‘员工名’, e.ename ‘领导名’ FROM emp e RIGHT JOIN emp a ON e.empno=a.mgr;

– 15.15. 列出受雇日期早亍其直接上级的所有员工编号、姓名、部门名称
SELECT a.ename ‘员工名’ ,a.hiredate, e.ename ‘领导名’ ,e.hiredate FROM emp e RIGHT JOIN emp a ON e.empno=a.mgr WHERE a.hiredate<e.hiredate;

– 15.16. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dept.dname,emp.* FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno;

– 15.17. 列出至少有5个员工的所有部门
SELECT dept.dname, COUNT(dept.deptno) num FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno GROUP BY dept.deptno HAVING num>4;

– 15.18. 列出薪水比“SMITH”多的所有员工信息
SELECT * from emp WHERE sal>(SELECT sal FROM emp WHERE ename=‘SMITH’) ORDER BY sal;

– 15.19. 列出所有“CLERK”（办事员的姓名及其部门名，部门人数
SELECT e.ename,e.job,d.dname,t.num ‘部门人数’ FROM emp e JOIN dept d ON e.deptno=d.deptno JOIN (SELECT deptno,count(deptno) num FROM emp GROUP BY deptno) t on e.deptno=t.deptno
WHERE e.job=‘CLERK’;

– 15.20. 列出最低薪水大亍1500的各种工作及从事此工作的全部雇员人数
SELECT emp.job,emp.sal,d.num ‘人数’ from emp JOIN (SELECT job,count(job) num FROM emp GROUP BY job) d ON emp.job=d.job WHERE sal>1500;

– 15.21. 列出在部门“SALES”<销售部>工作的员工的姓名，假定丌知道销部门的部门编号
SELECT * from emp WHERE deptno in (SELECT deptno from dept WHERE dname=‘SALES’);

– 15.22. 列出薪金高亍公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级
SELECT a.ename ‘员工名’, e.ename ‘领导名’, a.sal, d.dname, s.grade FROM emp e RIGHT JOIN emp a ON e.empno = a.mgr JOIN dept d ON a.deptno = d.deptno JOIN salgrade s ON a.sal BETWEEN s.losal
AND s.hisal WHERE a.sal>(SELECT avg(sal) from emp);

– 15.23. 列出不与“SCOTT”从事相同工作的所有员工及部门名称
SELECT e.ename,d.dname FROM emp e JOIN dept d on e.deptno=d.deptno WHERE job !=(SELECT job FROM emp WHERE ename=‘SCOTT’);

– 15.24. 列出薪金等亍部门30中员工的薪金的其它员工的姓名和薪金
SELECT ename ,sal FROM emp WHERE sal not in (SELECT sal FROM emp WHERE deptno=30);

– 15.25. 列出薪金高亍在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
SELECT ename ,sal,d.dname FROM emp e JOIN dept d on e.deptno=d.deptno WHERE sal >(SELECT max(t.sal) maxsal FROM (SELECT sal FROM emp WHERE deptno=30) t);

– 15.26. **列出在每个部门工作的员工数量、平均工资和平均服务期限
SELECT d.dname,count(e.deptno) 员工数量, IFNULL(avg(sal),0) 平均工资, avg(IFNULL(TO_DAYS(NOW())-TO_DAYS(hiredate),0)) 平均服务天数 FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno
GROUP BY e.deptno;

– 15.27. 列出所有员工的姓名、部门名称和工资
SELECT e.ename ,d.dname ,e.sal FROM emp e JOIN dept d ON e.deptno=d.deptno;

– 15.28. 列出所有部门的详细信息和人数
SELECT d.*,count(ename) 部门人数 FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno GROUP BY d.deptno;

– 15.29. 列出各种工作的最低工资及从事此工作的雇员姓名
SELECT job,min(sal),ename FROM emp GROUP BY job;

– 15.30. 列出各个部门MANAGER的最低薪金
SELECT t.部门编号,MIN(t.sal) 最低薪金 FROM (SELECT distinct e.ename MANAGER , e.sal,a.deptno 部门编号 FROM emp e JOIN emp a ON e.empno=a.mgr) t GROUP BY t.部门编号;

– 15.31. 列出所有员工的年工资，按年薪从低到高掋序
SELECT ename,(IFNULL(sal,0)+IFNULL(comm,0))*12 年工资 from emp ORDER BY 年工资 ;

– 15.32. 求出员工领导的薪水超过3000的员工名称和领导名称
SELECT a.ename ‘员工名’, e.ename ‘领导名’ ,e.sal FROM emp e right JOIN emp a ON e.empno = a.mgr where e.sal>3000;

– 15.33. 求部门名称中带“S”字符的部门员工的工资合计、部门人数
SELECT sum(sal) 工资合计,count(deptno) 部门人数 from emp WHERE deptno in (SELECT deptno FROM dept WHERE dname LIKE ‘%s%’) GROUP BY deptno

– 15.34. 给任职日期超过30年的员工加薪10%
SELECT ename,sal,sal*1.1 加薪后 FROM emp WHERE ((TO_DAYS(NOW())-TO_DAYS(hiredate))/365-30)>0

标签

#MySql