SQL多表查询

基本格式 

select *  from emp, dept, (select * from emp) where group by having order by limit

MySQL关键字执行的优先级

from 表 where 条件 group by 字段 形成虚拟的表及字段，聚合及字段添加 having 筛选数据 distinct 去重 order by 字段 asc,desc limit 限制条数 select 罗列记录 先根据where 约束条件 将数据从表中加载到内存，所以where的条件作用在数据之前， 然后根据字段进行分组，在内存中形成一张虚拟的表包含字段，如果此时有聚合函数则先执行聚合函数， 并将聚合函数字段也加到虚拟的表中，接着进行having记录筛选，筛选完成后对数据进行去重，排序， 限制等操作后进行显示。

 

多表查询

  1. 分类：     * 合并结果集(了解)     * 连接查询     * 子查询

  2.合并结果集   * 要求被合并的表中，列的类型和列数相同   * UNION，去除重复行   * UNION ALL，不去除重复行

SELECT * FROM cd UNION ALL SELECT * FROM ab;

连接查询   1. 分类     * 内连接     * 外连接       > 左外连接       > 右外连接       > 全外连接(MySQL不支持)     * 自然连接（属于一种简化方式）

  2. 内连接

    /* 方言：*/ 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     /* 内连接查询出的所有记录都满足条件。*/

  3. 外连接    

/* 左外：左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为NULL*/ SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx     /* 左外自然：*/ SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx     /* 右外：右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表不满足条件的记录，其左表部分都为NULL*/ SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx     /* 右外自然：*/ SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx     /* 全链接：可以使用UNION来完成全链接 */

子查询：查询中有查询（查看select关键字的个数！）   1. 出现的位置：     * where后作为条件存在     * from后作为表存在(多行多列)

  2. 条件

    /* (***)单行单列：*/ SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)     /* (**)多行单列：*/ SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)     /* (*)单行多列：*/ SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)     /* (***)多行多列：*/ SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

练习题

dept（部门表）：

emp（员工表）： 

salgrade（工资等级表） ：

 

/*查询每个部门的人数*/ SELECT d.deptno,d.dname,COUNT(e.empno) FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno GROUP BY d.deptno,d.dname; /*1. 查出至少有两个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/ SELECT d.deptno,d.dname,d.loc,p.num num FROM dept d,(SELECT deptno,COUNT(empno) num FROM emp GROUP BY deptno HAVING COUNT(empno) > 2) p WHERE d.deptno = p.deptno; /*2. 列出薪金比关羽高的所有员工。*/ SELECT * FROM emp WHERE sal + IFNULL(comm,0) > (SELECT sal + IFNULL(comm,0) FROM emp WHERE ename = '关羽'); /*3. 列出所有员工的姓名及其直接上级的姓名。 emp表中存在自身关联，即empno和mgr的关系。我们需要让emp和emp表连接查询。 因为要求是查询所有员工的姓名，所以不能用内连接， 因为曾阿牛是BOSS，没有上级，内连接是查询不到它的*/ SELECT e.ename,IFNULL(m.ename,'BOSS') mgr FROM emp e LEFT JOIN emp m ON e.mgr = m.empno; /*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/ /*方法1：内连接*/ SELECT e.empno,e.ename,d.dname FROM emp e INNER JOIN emp m ON e.mgr = m.empno AND e.hiredate < m.hiredate LEFT JOIN dept d ON e.deptno = d.deptno; /*方法2：左连接*/ SELECT e.empno, e.ename, d.dname FROM emp e LEFT JOIN emp m ON e.mgr=m.empno LEFT JOIN dept d ON e.deptno=d.deptno WHERE e.hiredate<m.hiredate; /*5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/ SELECT d.dname,e.* FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno; /*6. 列出所有文员的姓名及其部门名称，部门的人数。*/ SELECT e.ename,d.dname,t.num FROM dept d,emp e, (SELECT deptno,COUNT(1) num FROM emp GROUP BY(deptno)) t WHERE e.job = '文员' AND e.deptno = d.deptno AND t.deptno = d.deptno; /*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/ SELECT job,COUNT(empno) FROM emp GROUP BY job HAVING MIN(sal) > 15000; /*8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/ SELECT e.ename FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = '销售部'; SELECT e.ename FROM emp e WHERE e.deptno = (SELECT deptno FROM dept WHERE dname = '销售部'); /*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/ SELECT e.*, d.dname, m.ename, s.grade FROM emp e NATURAL LEFT JOIN dept d LEFT JOIN emp m ON e.mgr = m.empno LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND hisal WHERE e.sal > (SELECT AVG(sal) FROM emp); /*10.列出与庞统从事相同工作的所有员工及部门名称。*/ SELECT e.*, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno AND e.job = (SELECT job FROM emp WHERE ename = '庞统'); /*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/ SELECT e.ename,e.sal, d.dname FROM emp e, dept d WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE deptno = '30') AND e.deptno = d.deptno 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,COUNT(1) num,AVG(sal) avgsal FROM dept d, emp e WHERE d.deptno = e.deptno GROUP BY d.deptno SELECT d.dname, t.num, t.avgsal FROM (SELECT deptno,COUNT(1) num,AVG(sal) avgsal FROM emp GROUP BY deptno) t, dept d WHERE t.deptno = d.deptno