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