7 个 join
多表查询
SQL中多表查询关键字及其排列顺序：
select *
from emp, dept, (select * from emp)
where
group by
having
order by
limit连接查询
一次查询多张表。所以在使用列元素的时候要带上表名这个前缀表名.列名，如果表名比较长可以给表名起别名。
内连接
- 方言：
SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx; - 标准(推荐使用)：
SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx; - 自然：
SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2;
内连接查询出的所有记录都满足条件。
外连接
- 左外：
SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;- 左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为NULL
- 左外自然：
SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx; - 右外：
SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;- 右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表不满足条件的记录，其左表部分都为NULL
- 右外自然：
SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
示例
/* 方言 */
SELECT e.name, e.sal, e.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno;
/* 标准 */
SELECT e.name, e.sal, e.dname
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno;
/* 相较于方言，把','换成'JOIN'，把'WHERE'换成'ON' */
/* 自然 */
SELECT e.name, e.sal, e.dname
FROM emp e INNER JOIN dept d;
/* 没ON了，它自动判断相等了 */
/* 外连接 */
SELECT e.name, e.sal, e.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;子查询
子查询就是查询中有查询，子查询可以出现的位置：
WHERE后作为条件存在FROM后作为表存在
按子查询的结果形式分类：
-
单行单列：
SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)/* 查询雇员中工资最高的员工 */ SELECT * FROM emp e WHERE sal=(SELECT MAX(sal) FROM emp) -
多行单列：
SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)/* 查询雇员中工资比所有经理都高的员工 */ SELECT * FROM emp e WHERE sal > ALL (SELECT sal FROM emp WHERE job='经理') -
单行多列：
FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)/* 查询雇员中工作，部门，工资和殷天正一样的员工 */ SELECT * FROM emp e WHERE (job, deptno, sal) IN (SELECT job, deptno, sal FROM emp WHERE ename='殷天正') -
多行多列：
SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件/* 查询部门编号为30的雇员的员工编号和姓名 */ SELECT e.empno, e.ename FROM (SELECT * FROM emp WHERE deptno=30) e
合并结果集
语法：UNION ALL或者UNION。
- 要求被合并的表中，列的类型和列数相同
UNION：会去除两个表中重复的行UNION ALL：不去除重复行
SELECT * FROM ab
UNION ALL
SELECT * FROM cd;多表查询练习题
/* 1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/
SELECT z.deptno 部门编号, d.dname 部门名称, d.loc 部门位置, z.cnt 部门人数
FROM (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno HAVING COUNT(*) >= 1) z, dept d
WHERE z.deptno=d.deptno
/* 2. 列出薪金比关羽高的所有员工。*/
SELECT *
FROM emp e
WHERE e.sal > (SELECT sal FROM emp WHERE ename='关羽')
/* 3. 列出所有员工的姓名及其直接上级的姓名。*/
SELECT e.ename 员工姓名, IFNULL(m.ename, '老板') 上级姓名
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno
/* 4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
SELECT e.empno 员工编号, e.ename 姓名, d.dname 部门名称
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno
WHERE e.hiredate>m.hiredate
/* 5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/
/* 因为以部门为主，所以是 RIGHT OUTER JOIN */
SELECT e.*, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;
/* 6. 列出所有文员的姓名及其部门名称，部门的人数。*/
SELECT e.ename 姓名, d.dname 部门名称, z.cnt 部门人数
FROM emp e, dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z
WHERE e.job='文员' AND e.deptno=d.deptno AND z.deptno=e.deptno
/* 7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/
SELECT job, COUNT(*)
FROM emp
GROUP BY job
HAVING MIN(sal) > 15000
/* 8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/
SELECT ename
FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE dname='销售部')
/* 9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/
SELECT e.*, d.dname '部门名称', m.ename '上级领导', g.grade '工资等级'
FROM emp e
LEFT OUTER JOIN dept d ON e.deptno=d.deptno
LEFT OUTER JOIN emp m ON e.mgr=m.empno
LEFT OUTER JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp)
/* 10.列出与庞统从事相同工作的所有员工及部门名称。*/
SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.job=(SELECT job FROM emp WHERE ename='庞统') AND e.deptno=d.deptno AND e.ename != '庞统'
/* 11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.sal > ALL (SELECT sal FROM emp WHERE deptno=30) AND e.deptno=d.deptno
/* 12.列出每个部门的员工数量、平均工资。*/
SELECT d.dname '部门', c.cnt '员工数量', c.avg_sal '平均工资'
FROM dept d, (SELECT deptno, COUNT(*) cnt, AVG(sal) avg_sal FROM emp GROUP BY deptno) c
WHERE d.deptno=c.deptno