MySQL多表SQL查询_网站建设

MySQL多表SQL查询

2025-07-22 09:59 点击:0

01.创建数据库表结构并插入数据

DROP DATAbase exam;CREATE DATAbase exam;USE exam;CREATE TABLE dept(deptno INT PRIMARY KEY,dname VARCHAR(50),loc VARCHAR(50));CREATE TABLE emp(empno INTPRIMARY KEY,ename VARCHAR(50),job VARCHAR(50),mgr INT,hiredate DATE,sal DECIMAL(7,2),COMM DECIMAL(7,2),deptno INT,ConSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno));CREATE TABLE salgrade(grade INTPRIMARY KEY,losal INT,hisal INT);CREATE TABLE stu(sid INTPRIMARY KEY,sname VARCHAR(50),age INT,gander VARCHAR(10),province VARCHAR(50),tuition INT);INSERT INTO dept VALUES (10, '教研部', '北京');INSERT INTO dept VALUES (20, '学工部', '上海');INSERT INTO dept VALUES (30, '销售部', '广州');INSERT INTO dept VALUES (40, '财务部', '武汉');INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);INSERT INTO salgrade VALUES (1, 7000, 12000);INSERT INTO salgrade VALUES (2, 12010, 14000);INSERT INTO salgrade VALUES (3, 14010, 20000);INSERT INTO salgrade VALUES (4, 20010, 30000);INSERT INTO salgrade VALUES (5, 30010, 99990);INSERT INTO `stu` VALUES ('1', '王永', '23', '男', '北京', '1500');INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500');INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '北京', '3500');INSERT INTO `stu` VALUES ('4', '宋永合', '25', '男', '北京', '1500');INSERT INTO `stu` VALUES ('5', '叙美丽', '23', '女', '北京', '1000');INSERT INTO `stu` VALUES ('6', '陈宁', '22', '女', '山东', '2500');INSERT INTO `stu` VALUES ('7', '王丽', '21', '女', '北京', '1600');INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500');INSERT INTO `stu` VALUES ('9', '张玲', '23', '女', '广州', '2500');INSERT INTO `stu` VALUES ('10', '啊历', '18', '男', '山西', '3500');INSERT INTO `stu` VALUES ('11', '王刚', '23', '男', '湖北', '4500');INSERT INTO `stu` VALUES ('12', '陈永', '24', '男', '北京', '1500');INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '辽宁', '2500');INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500');INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500');INSERT INTO `stu` VALUES ('16', '王小丽', '23', '女', '北京', '1000');INSERT INTO `stu` VALUES ('17', '唐宁', '22', '女', '山东', '2500');INSERT INTO `stu` VALUES ('18', '唐丽', '21', '女', '北京', '1600');INSERT INTO `stu` VALUES ('19', '啊永', '23', '男', '北京', '3500');INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '广州', '2500');INSERT INTO `stu` VALUES ('21', '叙刚', '18', '男', '山西', '3500');INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500');INSERT INTO `stu` VALUES ('23', '赵安', '23', '男', '北京', '1500');INSERT INTO `stu` VALUES ('24', '关雷', '25', '男', '辽宁', '2500');INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500');INSERT INTO `stu` VALUES ('26', '叙安国', '25', '男', '北京', '1500');INSERT INTO `stu` VALUES ('27', '陈浩难', '23', '女', '北京', '1000');INSERT INTO `stu` VALUES ('28', '陈明', '22', '女', '山东', '2500');INSERT INTO `stu` VALUES ('29', '孙丽', '21', '女', '北京', '1600');INSERT INTO `stu` VALUES ('30', '李治国', '23', '男', '北京', '3500');INSERT INTO `stu` VALUES ('31', '张娜', '23', '女', '广州', '2500');INSERT INTO `stu` VALUES ('32', '安强', '18', '男', '山西', '3500');INSERT INTO `stu` VALUES ('33', '王欢', '23', '男', '湖北', '4500');INSERT INTO `stu` VALUES ('34', '周天乐', '23', '男', '北京', '1500');INSERT INTO `stu` VALUES ('35', '关雷', '25', '男', '辽宁', '2500');INSERT INTO `stu` VALUES ('36', '吴强', '22', '男', '北京', '3500');INSERT INTO `stu` VALUES ('37', '吴合国', '25', '男', '北京', '1500');INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000');INSERT INTO `stu` VALUES ('39', '吴丽', '22', '女', '山东', '2500');INSERT INTO `stu` VALUES ('40', '冯含', '21', '女', '北京', '1600');INSERT INTO `stu` VALUES ('41', '陈冬', '23', '男', '北京', '3500');INSERT INTO `stu` VALUES ('42', '关玲', '23', '女', '广州', '2500');INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500');INSERT INTO `stu` VALUES ('44', '威刚', '23', '男', '湖北', '4500');INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500');INSERT INTO `stu` VALUES ('46', '张关雷', '25', '男', '辽宁', '2500');INSERT INTO `stu` VALUES ('47', '送小强', '22', '男', '北京', '3500');INSERT INTO `stu` VALUES ('48', '关动林', '25', '男', '北京', '1500');INSERT INTO `stu` VALUES ('49', '苏小哑', '23', '女', '北京', '1000');INSERT INTO `stu` VALUES ('50', '赵宁', '22', '女', '山东', '2500');INSERT INTO `stu` VALUES ('51', '陈丽', '21', '女', '北京', '1600');INSERT INTO `stu` VALUES ('52', '钱小刚', '23', '男', '北京', '3500');INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '广州', '2500');INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500');INSERT INTO `stu` VALUES ('55', '周制强', '23', '男', '湖北', '4500');

02.SQL多表查询

SELECT d.*,z.cnt FROM dept d,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z WHERE d.deptno=z.deptno;

# 答案

SELECTz.*,d.dname,d.locFROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) zWHERE z.deptno=d.deptno;

# 解答

SELECT e.* FROM emp e WHERE e.sal>(SELECT sal FROM emp WHERE ename='关羽');

# 答案

SELECT *FROM emp eWHERE e.sal > (SELECT sal FROM emp WHERE ename='关羽')

# 解答

SELECT e.*,m.ename FROM emp e, emp m WHERE e.mgr=m.empno;

SELECT e.*,IFNULL(m.ename,'BOSS') leader FROM emp eLEFT JOIN emp m ON e.mgr=m.empno

SELECT e.*,m.ename leader FROM emp eRIGHT JOIN emp m ON e.mgr=m.empno

# 答案

SELECT e.ename, IFNULL(m.ename, 'BOSS') AS leadFROM emp e LEFT JOIN emp m ON e.mgr=m.empno;

# 解答

SELECT e.empno,e.ename,d.dnameFROM emp e,emp m,dept dWHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.hiredateSELECT e.empno,e.ename,d.dnameFROM emp eLEFT JOIN emp m ON e.mgr=m.empnoLEFT JOIN dept d ON e.deptno=d.deptnoWHERE e.hiredateSELECT e.empno,e.ename,d.dnameFROM emp eRIGHT JOIN emp m ON e.mgr=m.empnoRIGHT JOIN dept d ON e.deptno=d.deptnoWHERE e.hiredate# 答案SELECT e.empno, e.ename, d.dnameFROM emp e LEFT JOIN emp m ON e.mgr=m.empno LEFT JOIN dept d ON e.deptno=d.deptnoWHERE e.hiredate# 解答SELECT d.dname,e.*FROM dept dLEFT JOIN emp e ON d.deptno=e.deptno

# 答案
SELECT e.*, d.dnameFROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;




# 解答

SELECT e.ename,e.job,d.dname,c.cntFROM emp e,dept d,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) cWHERE e.deptno=d.deptno AND e.deptno=c.deptno AND e.job='文员';


SELECT e.ename,e.job,d.dname,c.cntFROM emp eLEFT JOIN dept d ON e.deptno=d.deptnoLEFT JOIN (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) c ON e.deptno=c.deptnoWHERE e.job='文员';


SELECT e.ename,e.job,d.dname,c.cntFROM emp eRIGHT JOIN dept d ON e.deptno=d.deptnoRIGHT JOIN (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) c ON e.deptno=c.deptnoWHERE e.job='文员'ORDER BY c.cnt ASC;




# 解答

SELECT e.job,c.cnt FROM emp e, (SELECT job,COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) cWHERE e.job = c.job AND c.minsal>15000;


SELECT e.job,c.cntFROM emp eLEFT JOIN (SELECT job, COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) c ON e.job=c.jobWHERE c.minsal>15000;


SELECT DISTINCT e.job,c.cntFROM emp eRIGHT JOIN (SELECT job, COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) c ON e.job=c.jobWHERE c.minsal>15000;

# 答案
SELECT job, COUNT(*)FROM emp eGROUP BY jobHAVING MIN(sal) > 15000;

# 练习
SELECT job, COUNT(*)FROM emp eGROUP BY jobHAVING MIN(sal) > 15000;





# 解答

EXPLAIN SELECT e.enameFROM emp e,dept dWHERE e.deptno=d.deptno AND d.dname='销售部';


EXPLAIN SELECT e.enameFROM emp eLEFT OUTER JOIN dept d ON e.deptno=d.deptnoWHERE d.dname='销售部';


EXPLAIN SELECT e.enameFROM emp eRIGHT OUTER JOIN dept d ON e.deptno=d.deptnoWHERE d.dname='销售部';


EXPLAIN SELECT e.enameFROM emp eWHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');

# 答案
SELECT e.enameFROM emp eWHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');



# 解答

SELECT e.*,d.dname,m.ename,s.gradeFROM emp e,dept d,emp m,salgrade sWHERE e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal AND e.sal>(SELECT AVG(sal) avgsal FROM emp )ORDER BY s.grade;


SELECT e.*,d.dname,m.ename,s.gradeFROM emp eLEFT OUTER JOIN dept d ON e.deptno=d.deptnoLEFT OUTER JOIN emp m ON e.mgr=m.empnoLEFT OUTER JOIN salgrade s ON e.sal> s.losal AND e.sal(SELECT AVG(sal) avgsal FROM emp)ORDER BY s.grade;


SELECT e.*,d.dname,m.ename,s.gradeFROM emp eRIGHT JOIN dept d ON e.deptno=d.deptnoRIGHT OUTER JOIN emp m ON e.mgr=m.empnoRIGHT OUTER JOIN salgrade s ON e.sal> s.losal AND e.sal(SELECT AVG(sal) avgsal FROM emp)ORDER BY s.grade;


# 内连接、左外连接、右外连接在where条件中都包含了子查询

# 答案
SELECT e.*, d.dname, m.ename, s.gradeFROM emp e   NATURAL LEFT JOIN dept d  LEFT JOIN emp m ON m.empno=e.mgr  LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisalWHERE e.sal > (SELECT AVG(sal) FROM emp);




# 解答

SELECT e.*,d.dnameFROM emp e, dept dWHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');
SELECT e.*,d.dnameFROM emp eLEFT JOIN dept d ON e.deptno=d.deptnoWHERE e.job=(SELECT job FROM emp WHERE ename='庞统');


SELECT e.*,d.dnameFROM emp eRIGHT JOIN dept d ON e.deptno=d.deptnoWHERE e.job=(SELECT job FROM emp WHERE ename='庞统');


# 内连接、左外连接、右外连接在where条件部分都包含了子查询

# 答案
SELECT e.*, d.dnameFROM emp e, dept dWHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');




# 解答

SELECT e.ename,e.sal,d.dnameFROM emp e,dept dWHERE e.deptno=d.deptno AND e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)ORDER BY e.sal;


SELECT e.ename,e.sal,d.dnameFROM emp eLEFT JOIN dept d ON e.deptno=d.deptnoWHERE e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)ORDER BY e.sal;


SELECT e.ename,e.sal,d.dnameFROM emp eRIGHT JOIN dept d ON e.deptno=d.deptnoWHERE e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)ORDER BY e.sal;


# 内连接、左外连接、右外连接在where条件中都存在子查询

# 答案
SELECT e.ename, e.sal, d.dnameFROM emp e, dept dWHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)




# 解答

SELECT d.dname,c.cnt,c.avgsalFROM dept d,(SELECT deptno,COUNT(*) cnt,AVG(sal) avgsal FROM emp GROUP BY deptno) cWHERE d.deptno=c.deptno;


SELECT d.dname,c.cnt,c.avgsalFROM dept dLEFT JOIN (SELECT deptno,COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) c ON d.deptno=c.deptno


SELECT d.dname,c.cnt,c.avgsalFROM dept dRIGHT JOIN (SELECT deptno,COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) c ON d.deptno=c.deptno


# 内连接、左外连接、右外连接中在条件where部分都包含了子查询

# 答案
SELECT d.dname, e.cnt, e.avgsalFROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept dWHERE e.deptno=d.deptno;