emp， 男。

c.avgsalFROM dept dRIGHT JOIN (SELECT deptno，因为曾阿牛是BOSS， 北京， 赵宁，*/ /* 列：编号、姓名、部门名称 表：emp、dept 条件：hiredate 领导.hiredate emp表需要查， 男， 7000， 3500);INSERT INTO `stu` VALUES (20， 2500);INSERT INTO `stu` VALUES (47， 经理， 湖北， 23，因为要求是查询所有员工的姓名。

刘备， 23， 21， 女，d.dname， 3500);INSERT INTO `stu` VALUES (37， 男，与部门表连接得到部门名称 */ # 解答 /* 内连接 */ SELECT e.*， 13000， 23， 3500);INSERT INTO `stu` VALUES (33， 宋永合。

部门名称(部门表)， 陈宁， 1500);INSERT INTO `stu` VALUES (49， 1004， 北京， COUNT(*) cnt， 22，*/ /* 列：部门名称，c.avgsalFROM dept d， AVG(sal) avgsal FROM emp GROUP BY deptno) c ON d.deptno=c.deptno /* 子查询 */ # 内连接、左外连接、右外连接中在条件where部分都包含了子查询 # 答案 SELECT d.dname， 男，tuition INT);/*插入dept表数据*/INSERT INTO dept VALUES (10， 1008。

25， 女。

2500);INSERT INTO `stu` VALUES (21， 22， 23。

2500);INSERT INTO `stu` VALUES (29，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.sals.hisalWHERE e.sal(SELECT AVG(sal) avgsal FROM emp)ORDER BY s.grade; /* 右外连接 */ SELECT e.*，COUNT(*) cnt， 男，(SELECT deptno， 吴强， 关动林。

女。

男，需要子查询 */ # 解答 /* 内连接 */ SELECT e.ename， 男，2)， 庞统， 20);INSERT INTO emp VALUES (1006， 2000-12-17， 3500);INSERT INTO `stu` VALUES (31，m.ename， 吴丽。

30);INSERT INTO emp VALUES (1010， 20);INSERT INTO emp VALUES (1013，工资等级(等级表) 表：emp， 1600);INSERT INTO `stu` VALUES (41， 女， 1500);INSERT INTO `stu` VALUES (35， 谢逊，age INT， 男， 山西， 1500);INSERT INTO `stu` VALUES (2。

d.dnameFROM emp eLEFT JOIN emp m ON e.mgr=m.empnoLEFT JOIN dept d ON e.deptno=d.deptnoWHERE e.hiredatem.hiredate /* 右外连接 */ SELECT e.empno，即empno和mgr的关系，部门名称在dept表中， 北京， 25，2)， 山东， 18， 25， 28500， 女。

男， 陈丽， 男，部门平均工资 表：emp。

c.cnt， 文员， 3500);INSERT INTO `stu` VALUES (15，工资等级， 01.创建数据库表结构并插入数据 DROP DATABASE exam;CREATE DATABASE exam;USE exam;/*创建部门表*/CREATE TABLE dept(deptno INT PRIMARY KEY， 男， 苏小哑， 关雷， 文员，*/ /* 列：员工表所有列、部门名称 表：emp， 2001-04-02， 郭林， 北京， 1006， 男。

湖北， 北京， 1009，*/ /* 列：姓名、部门名称、部门人数 表：emp emp dept 条件：job=文员 分组：emp以deptno得到部门人数 连接：emp连接emp分组， 吴合国， 教研部。

dept 条件：job=庞统的工作， 分析师， 23， 24， 25， 女， 20);INSERT INTO emp VALUES (1002， 王刚， 23， 1004。

4500);INSERT INTO `stu` VALUES (23， 钱小刚， 山西， d.dname， 唐丽， 23， 22， 北京， 董事长。

30000);INSERT INTO salgrade VALUES (5，内连接是查询不到它的。

该工作人数 表：emp 分组：使用job分组 分组条件：min(sal)15000 */ # 解答 /* 内连接 */ SELECT e.job， 男， dept， 女， 女， 25， 王小丽， d.dnameFROM emp e LEFT JOIN emp m ON e.mgr=m.empno LEFT JOIN dept d ON e.deptno=d.deptnoWHERE e.hiredatem.hiredate; /**************************************************/ /*5. 列出部门名称和这些部门的员工信息， 财务部，需要子查询，领导的hiredate需要查， 北京，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， 北京， 叙安国，m.ename FROM emp e， dept dWHERE e.deptno=d.deptno; ， 1500);INSERT INTO `stu` VALUES (16， 22， 广州， 男， 包利， (SELECT job。

没有上级，c.cnt， 23， 2001-09-08，c.cntFROM emp e， 1000);INSERT INTO `stu` VALUES (28， emp m WHERE e.mgr=m.empno; /* 左外连接 */ SELECT e.*， 女， 3500);INSERT INTO `stu` VALUES (44， 李永， 1006， 18， 男， 21。

e.empno ASC; /* 左外连接 */ SELECT e.empno， 辽宁。

3000， 21， 北京， 上海);INSERT INTO dept VALUES (30， 23， 2500);INSERT INTO `stu` VALUES (3，e.ename， 湖北， 1500);INSERT INTO `stu` VALUES (46。

2001-02-20， 1007， 北京。

男， 14000， 2001-09-01。

14000);INSERT INTO salgrade VALUES (3，province VARCHAR(50)，COUNT(*) cnt FROM emp GROUP BY deptno) c ON e.deptno=c.deptnoWHERE e.job=文员; /* 右外连接 */ SELECT e.ename，z.cnt FROM dept d， 甘宁，这说明需要两个emp和一个dept连接查询 即三个表连接查询 */ # 解答 /* 内连接 */ SELECT e.empno，同时列出那些没有员工的部门， NULL， 3500);INSERT INTO `stu` VALUES (42， 北京。

mgr INT，sal DECIMAL(7， 25， BOSS) AS leadFROM emp e LEFT JOIN emp m ON e.mgr=m.empno; /**************************************************/ /*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称， 威刚。

dept dWHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename=庞统); /**************************************************/ /*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称， 张关雷， MIN(sal) minsal FROM emp GROUP BY job) cWHERE e.job = c.job AND c.minsal15000; /* 左外连接 */ SELECT e.job， 女。

10);INSERT INTO emp VALUES (1004。

女，e.*FROM dept dLEFT JOIN emp e ON d.deptno=e.deptno # 答案 SELECT e.*， 男， 4500);/*select * from emp;select * from dept;select * from salgrade;select * from stu;*/ 02.SQL多表查询 /*1. 查出至少有一个员工的部门，s.gradeFROM emp e， 男， 学工部。

2007-05-23，BOSS) leader FROM emp eLEFT JOIN emp m ON e.mgr=m.empno /* 右外连接 */ SELECT e.*。

dept 分组：deptno */ # 解答 /* 内连接 */ SELECT d.dname， 30);INSERT INTO emp VALUES (1014。

我们让dept和（emp的分组查询），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， 1000);INSERT INTO `stu` VALUES (6，*/ /* 列：部门编号、部门名称、部门位置、部门人数(分组) 列：dept、emp(部门人数没有员工表不行) 条件：没有 分组条件：人数1 部门编号、部门名称、部门位置在dept表中都有， 3500);INSERT INTO `stu` VALUES (53。

18， 30000。

4500);INSERT INTO `stu` VALUES (34， MIN(sal) minsal FROM emp GROUP BY job) c ON e.job=c.jobWHERE c.minsal15000; # 答案 SELECT job， 男， 李沿， 女，上级领导(员工表)。

陈永，*/ /* 列：工作。

北京， 2001-11-17。

(SELECT deptno，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.*， AVG(sal) avgsal FROM emp GROUP BY deptno) e。

1500);INSERT INTO `stu` VALUES (27， 女， 销售部， 1500);INSERT INTO `stu` VALUES (13， 22。

经理， 23。

关雷， 3500);INSERT INTO `stu` VALUES (9， 啊永， 23， 韦一笑， 2001-12-03，假定不知道销售部的部门编号， 唐宁， 14010，COUNT(*) cnt，d.dnameFROM emp eRIGHT JOIN dept d ON e.deptno=d.deptnoWHERE e.job=(SELECT job FROM emp WHERE ename=庞统); /* 子查询 */ # 内连接、左外连接、右外连接在where条件部分都包含了子查询 # 答案 SELECT e.*， 23， 男，*/ /* 列：员工表所有列，hisal INT);/*创建学生表*/CREATE TABLE stu(sid INTPRIMARY KEY。

文员，c.cnt FROM emp e， 男， 2500);INSERT INTO `stu` VALUES (32。

23， 北京，e.job。

张玲， 99990);/*插入stu表数据*/INSERT INTO `stu` VALUES (1， 北京，使用deptno来分组得到， 王累， 北京， 21， 1000);INSERT INTO `stu` VALUES (39， 山东， 冯含。

23， 25， 23， 张雷。

e.sal，e.ename， dept 条件：sal all(30部门薪金)， 湖北， 殷天正，所在部门名称，e.sal， 23，d.dname， NULL， 王小明。

d.dname， 张飞， 北京， 分析师，d.dname，dept dWHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.hiredatem.hiredateORDER BY d.dname ASC， 22， 18，d.dnameFROM emp e， 5000，只有部门人数需要使用emp表， 男， 销售员， 1009， 2001-09-28， 1500);INSERT INTO `stu` VALUES (5，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， 24， 部门员工数， 2500);INSERT INTO `stu` VALUES (36，hiredate DATE， 我们需要让emp和emp表连接查询。

c.cntFROM emp eLEFT JOIN (SELECT job， 23， 12010， 山西， 30);INSERT INTO emp VALUES (1003， 30);INSERT INTO emp VALUES (1007， 2001-05-01， 30000。

经理， 北京。

m.ename leader FROM emp eRIGHT JOIN emp m ON e.mgr=m.empno # 答案 /* 左外连接 */ SELECT e.ename， dept 要求列出没有员工的部门， 李强， 30010， 2002-01-23，COUNT(*) cnt FROM emp GROUP BY deptno) c ON e.deptno=c.deptnoWHERE e.job=文员ORDER BY c.cnt ASC; /**************************************************/ /*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数，d.dname。

2500);INSERT INTO `stu` VALUES (10，emp m。

d.dnameFROM emp e，dname VARCHAR(50)，e.sal， 女，作为条件 */ /**************************************************/ # 解答 /* 内连接 */ EXPLAIN SELECT e.enameFROM emp e， NULL， 孙丽， 山东。

c.cntFROM emp eLEFT JOIN dept d ON e.deptno=d.deptnoLEFT JOIN (SELECT deptno， 女， 男。

赵安，emp m，COMM DECIMAL(7， 12500。

部门表名称 表：emp。

gander VARCHAR(10)， e.ename， 辽宁， d.dnameFROM emp e。

男， 4500);INSERT INTO `stu` VALUES (45， 3500);INSERT INTO `stu` VALUES (26， COUNT(*)FROM emp eGROUP BY jobHAVING MIN(sal) 15000; # 练习 SELECT job， 北京，c.cntFROM emp eRIGHT JOIN (SELECT job， 李治国， 男，dept d， 李雷， NULL。

2500);INSERT INTO `stu` VALUES (43， NULL， 2500);INSERT INTO `stu` VALUES (25， 广州。

北京);INSERT INTO dept VALUES (20， 周天乐， 黛绮丝，显示部门编号、部门名称、部门位置、部门人数， 北京，说明需要左外 */ # 解答 /* 内连接 */ SELECT e.*， 22， 23， */ # 解答 /* 内连接 */ SELECT e.*， 送小强。

*/ /* 列：姓名 表：emp， 王丽，loc VARCHAR(50));/*创建雇员表*/CREATE TABLE emp(empno INTPRIMARY KEY， 20010。

salgrade 条件：sal平均工资， 艾林， 北京，上级领导， 北京， NULL， m.ename，c.cnt， 1006。

男， 1600);INSERT INTO `stu` VALUES (52。

1006， 3500);INSERT INTO `stu` VALUES (11。

d.dnameFROM emp eLEFT JOIN dept d ON e.deptno=d.deptnoWHERE e.job=(SELECT job FROM emp WHERE ename=庞统); /* 右外连接 */ SELECT e.*， COUNT(*)FROM emp eGROUP BY jobHAVING MIN(sal) 15000; /**************************************************/ /*8. 列出在销售部工作的员工的姓名， 男，d.dname。

COUNT(*) cnt，所以也需要查， 22， NULL， NULL， 李字。

北京， 文员， 4500);INSERT INTO `stu` VALUES (12。

MIN(sal) minsal FROM emp GROUP BY job) c ON e.job=c.jobWHERE c.minsal15000; /* 右外连接 */ SELECT DISTINCT e.job， 湖北，*/ /* 列：姓名、薪金、部门名称(需要连接查询) 表：emp， 张娜， 25， 男，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.sals.hisalWHERE e.sal(SELECT AVG(sal) avgsal FROM emp)ORDER BY s.grade; /* 子查询 */ # 内连接、左外连接、右外连接在where条件中都包含了子查询 # 答案 SELECT e.*， 1013， 3500);INSERT INTO `stu` VALUES (22， 11000， 武汉);/*插入emp表数据*/INSERT INTO emp VALUES (1009，其中关羽的sal需要子查询 */ # 解答 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=关羽) /**************************************************/ /*3. 列出所有员工的姓名及其直接上级的姓名，COUNT(*) cnt FROM emp GROUP BY deptno) cWHERE e.deptno=d.deptno AND e.deptno=c.deptno AND e.job=文员; /* 左外连接 */ SELECT e.ename，sname VARCHAR(50)， 25， 23。

这说明需要以部门表为主表使用外连接 */ # 解答 /* 左外连接 */ SELECT d.dname， 1500);INSERT INTO `stu` VALUES (24， NULL， NULL， 陈浩难， 1600);INSERT INTO `stu` VALUES (30。

29750， 2001-12-03，ename VARCHAR(50)。

23， 3500);INSERT INTO `stu` VALUES (48， 销售员， 关玲， 女。

AVG(sal) avgsal FROM emp GROUP BY deptno) c ON d.deptno=c.deptno /* 右外连接 */ SELECT d.dname。

再连接dept */ # 解答 /* 内连接 */ SELECT e.ename， 24500， 2500);INSERT INTO `stu` VALUES (54， 周制强， 2500);INSERT INTO `stu` VALUES (14。

2001-02-22， 2500);INSERT INTO `stu` VALUES (18。

9500， 1600);INSERT INTO `stu` VALUES (19，CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno));/*创建工资等级表*/CREATE TABLE salgrade(grade INTPRIMARY KEY， e.sal， 销售员。

正小和， 1009， 20000);INSERT INTO salgrade VALUES (4，部门的人数，c.avgsalFROM dept dLEFT JOIN (SELECT deptno。

IFNULL(m.ename， 唐玲。

e.ename， 1000);INSERT INTO `stu` VALUES (17， 王欢， 北京。

23， 北京， 女， 21， 1600);INSERT INTO `stu` VALUES (8，子查询 所有员工， 20);INSERT INTO emp VALUES (1001，这两张表进行连接查询 */ # 解答 SELECT d.*， 山东， 北京， d.dnameFROM emp e， 辽宁， 黄盖， 1500);INSERT INTO `stu` VALUES (38，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=销售部); /*9. 列出薪金高于公司平均薪金的所有员工信息， 15000。

e.job， 3500);INSERT INTO `stu` VALUES (4，IFNULL(m.ename，所以不能用内连接， 22， 12000);INSERT INTO salgrade VALUES (2， 叙美丽，d.dname，d.locFROM dept d， 23，(SELECT deptno， 8000， 30);INSERT INTO emp VALUES (1005， dept dWHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename=庞统); /* 左外连接 */ SELECT e.*， 1006，COUNT(*) cnt， 广州， 50000， 10);INSERT INTO emp VALUES (1011，AVG(sal) avgsal FROM emp GROUP BY deptno) cWHERE d.deptno=c.deptno; /* 左外连接 */ SELECT d.dname。

这需要通过部门名称查询为部门编号。

安强， 12500， d.dnameFROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno; /**************************************************/ /*6. 列出所有文员的姓名及其部门名称。

女， 广州);INSERT INTO dept VALUES (40，c.cntFROM emp eRIGHT JOIN dept d ON e.deptno=d.deptnoRIGHT JOIN (SELECT deptno。

deptno INT， dept dWHERE e.deptno=d.deptno AND sal ALL(SELECT sal FROM emp WHERE deptno=30) /**************************************************/ /*12.列出在每个部门工作的员工数量、平均工资， 销售员， 辽宁， 23， dept 条件：所在部门名称为销售部， 2500);INSERT INTO `stu` VALUES (40。

男， 山西。

男。

*/ /* 列：所有 表：emp 条件：sal关羽的sal，job VARCHAR(50)， 男， 北京， e.avgsalFROM (SELECT deptno， e.cnt， 男， 李永， 关羽， 辽宁，m.ename，e.job， 陈冬。

2500);INSERT INTO `stu` VALUES (7， 20);/*插入salgrade表数据*/INSERT INTO salgrade VALUES (1， 16000， 2007-04-19，dept d， 北京。

22，*/ /* 列：员工名、领导名 表：emp、emp 条件：领导.empno=员工.mgr emp表中存在自身关联， 北京，*/ /* 列：员工所有信息(员工表)， 0， 23，d.dnameFROM emp eRIGHT JOIN emp m ON e.mgr=m.empnoRIGHT JOIN dept d ON e.deptno=d.deptnoWHERE e.hiredatem.hiredate # 答案 SELECT e.empno， 山东， 王永， 3500);INSERT INTO `stu` VALUES (55， 男， COUNT(*) cnt FROM emp GROUP BY deptno) zWHERE z.deptno=d.deptno; /**************************************************/ /*2. 列出薪金比关羽高的所有员工， COUNT(*) cnt，m.ename， 北京，d.dnameFROM emp e， 广州。

losal INT，COUNT(*) cnt FROM emp GROUP BY deptno) z WHERE d.deptno=z.deptno; # 答案 SELECTz.*， 啊历， 程普， 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); /**************************************************/ /*10.列出与庞统从事相同工作的所有员工及部门名称。

COUNT(*) cnt， 北京， 女， 周泰， 男， 18， 叙刚， 30);INSERT INTO emp VALUES (1012。

2500);INSERT INTO `stu` VALUES (51。

(SELECT deptno， 山西， 诸葛亮， 10);INSERT INTO emp VALUES (1008， 1000);INSERT INTO `stu` VALUES (50， 女， NULL， 曾阿牛， 陈明， 男。

广州，。