我只想卷死各位，或者被各位卷死，在此特别感谢康师傅的MySQL教程

85:43:12

MySQL数据库入门到大牛，mysql安装到优化，百科全书级，全网天花板

612.5万 19.4万

一个案例引发的多表查询

案例说明

为了减少查询表时的数据冗余，我们经常会把数据拆成多个表来存储

EMPLOYEES表 DEPARTMENTS表 LOCATIONS表
employee_id department_id location_id
first_name department_name street_address
last_name manager_id postal_code
email location_id city
phone_nunber state_province
job_id country_id
salary
commission_pct
manager_id
department_id

现在我们想查询员工的department_name，从以上两个表中获取数据：

1
2
3
4
5
6
7
-- 错误的实现方式：每个员工都与每个部门匹配了一遍。
SELECT last_name,department_name
FROM employees,departments;         --查询出2889条数据

SELECT * FROM employees;            --查询出107条数据

SELECT * FROM departments;          --查询出27调数据，而 27 * 107 = 2889

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。
错误的原因：缺少了多表的连接条件

笛卡尔积的理解

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

案例分析与问题解决

  • 笛卡尔积的错误会在下面条件下产生：
    1. 省略多个表的连接条件（或关联条件）
    2. 连接条件（或关联条件）无效
    3. 所有表中的所有行互相连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 多表查询的正确方式：需要有连接条件(使多个表之间能够相联系起来)
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

/*
如果查询语句出现了多个表中都存在的字段，则必须指明此字段所在的表。
建议：从SQL优化的角度，建议多表查询时，每个字段前都指明其所在的表 
*/
SELECT employees.last_name,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

/*
上面那种写法的可读性较差，所以我们还可以在 FROM 中给表起别名，在 SELECT 和 WHERE 中使用表的别名。
注意：如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
原因：在SQL中，FROM语句最先执行，一旦执行就会把我们自定义表的别名覆盖了表的原名，故不能再使用原名。
*/
SELECT e.last_name,d.department_id,d.department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;

小结：如果有n个表实现多表的查询，则至少需要n-1个连接条件，否则会出现笛卡尔积的错误。

练习：查询员工的employee_id，last_name，department_name，city

1
2
3
4
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

多表查询分类讲解

等值连接 vs 非等值连接

1
2
3
4
5
6
7
/*
非等值连接的例子：查询员工`last_name`，`salary`，`grade_level`
员工不同的salary对应着不同的grade_level
*/
SELECT e.last_name, e.salary, j.grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

自连接 vs 非自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询

练习：查询员工id,员工姓名及其管理者的id和姓名

1
2
3
SELECT emp.employee_id,emp.last_name,mng.employee_id,mng.last_name
FROM employees emp,employees mng
WHERE emp.`manager_id` = mng.`employee_id`;

内连接 vs 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

  • 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  • 如果是右外连接，则连接条件中右边的表也称为 主表，左边的表称为 从表 。

SQL92：使用(+)创建连接

  • 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
  • Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
  • 在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。
    1
    2
    3
    4
    5
    6
    7
    8
    -- 左外连接
    SELECT last_name,department_name
    FROM employees ,departments
    WHERE employees.department_id = departments.department_id(+);
    -- 右外连接
    SELECT last_name,department_name
    FROM employees ,departments
    WHERE employees.department_id(+) = departments.department_id;

SQL99语法实现多表查询

基本语法

  • 使用JOIN…ON子句创建连接的语法结构
    1
    2
    3
    4
    5
    6
    SELECT table1.field, table2.field,table3.field
    FROM table1
    JOIN table2 
    ON table1 和 table2 的连接条件
    JOIN table3 
    ON table2 和 table3 的连接条件

内连接( INNER JOIN )的实现

1
2
3
4
5
6
7
8
9
10
11
12
-- SQL99语法实现内连接：INNER JOIN  ... ON  (INNER可以省略)
SELECT last_name,department_name
FROM employees e 
JOIN departments d
ON e.`department_id` = d.`department_id`;

SELECT last_name,department_name,city
FROM employees e 
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;

外连接( OUTER JOIN )的实现

左外连接：LEFT OUTER JOIN …ON (OUTER可以省略)

1
2
3
4
5
-- 练习：查询所有的员工的last_name,department_name信息
SELECT last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

右外连接：RIGHT OUTER JOIN … ON (OUTER可以省略)

1
2
3
4
SELECT last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

1
2
3
4
SELECT last_name,department_name
FROM departments d 
FULL OUTER JOIN employees e
ON e.`department_id` = d.`department_id`;

UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。 语法格式： 
1
2
3
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
  • UNION：返回两个查询的结果集的并集，去除重复记录。
  • UNION ALL：返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

7种SQL JOINS的实现

中图就是普通的内连接

1
2
3
4
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`;

左上图是左外连接

1
2
3
4
SELECT last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

1
2
3
4
SELECT last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

左中图就是在左连接的情况下，剔除内连接的情况

1
2
3
4
5
SELECT last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

右中图就是在右连接的情况下，剔除内连接的情况

1
2
3
4
5
SELECT last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

左下图也就是满外连接，可以由左上图与右中图合成，或者右上图和左中图合成

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
UNION ALL
SELECT last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
-- 或
SELECT last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
UNION ALL
SELECT last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

可以由左中图和右中图合成

1
2
3
4
5
6
7
8
9
10
11
SELECT last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;
UNION ALL
SELECT last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

SQL99语法新特性

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

在SQL92标准中：

1
2
3
4
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

1
2
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。比如：

1
2
3
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

与自然连接不同的是，USING指明了具体的相同的字段名称，你需要在 USING
的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接。它与下
面的 SQL 查询结果是相同的：

1
2
3
4
5
6
7
8
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

SELECT e.employee_id,e.last_name,d.department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id;

章节小结

表连接的约束条件可以有三种方式：WHERE, ON, USING

  • WHERE：适用于所有关联查询
  • ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

注意：我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
来源：阿里巴巴《Java开发手册》

课后练习

  1. 显示所有员工的姓名，部门号和部门名称。

    1
    2
    3
    4
    SELECT e.last_name,e.department_id,d.department_name
    FROM employees e
    LEFT JOIN departments d
    ON e.department_id = d.department_id;

  2. 查询90号部门员工的job_id和90号部门的location_id

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT job_id,location_id
    FROM employees e
    JOIN departments d
    ON e.`department_id` = d.`department_id`
    WHERE e.`department_id` = 90;
    -- 或
    SELECT job_id,location_id
    FROM employees e,departments d
    WHERE e.`department_id` = d.`department_id`
    AND e.`department_id` = 90;

  3. 选择所有有奖金的员工的 last_name , department_name , location_id , city

    1
    2
    3
    4
    5
    6
    7
    SELECT e.last_name,d.department_name,d.location_id,l.city
    FROM employees e
    LEFT JOIN `departments` d
    ON e.`department_id` = d.`department_id`
    JOIN locations l
    ON d.`location_id` = l.`location_id`
    WHERE e.`commission_pct` IS NOT NULL;

  4. 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT e.last_name,e.job_id,d.department_id,d.department_name
    FROM employees e,departments d,locations l
    WHERE e.`department_id` = d.`department_id`
    AND d.`location_id` = l.`location_id`
    AND l.`city` = 'Toronto';
    -- 或
    SELECT e.last_name,e.job_id,d.department_id,d.department_name
    FROM employees e
    JOIN departments d
    ON e.`department_id` = d.`department_id`
    JOIN locations l
    ON d.`location_id` = l.`location_id`
    WHERE l.`city` = 'Toronto';

  5. 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT d.department_name,street_address,last_name,job_title,salary
    FROM employees e
    JOIN departments d
    ON e.`department_id` = d.`department_id`
    JOIN jobs j
    ON e.`job_id` = j.`job_id`
    JOIN locations l
    ON l.`location_id` = d.`location_id`
    WHERE department_name = 'Executive';

  6. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

    1
    2
    3
    4
    SELECT emp.last_name,emp.employee_id,mng.last_name,mng.employee_id
    FROM employees emp
    LEFT JOIN employees mng
    ON emp.manager_id = mng.employee_id;

  7. 查询哪些部门没有员工

    1
    2
    3
    4
    5
    SELECT department_name
    FROM departments d
    LEFT JOIN employees e
    ON d.`department_id` = e.`department_id`
    WHERE e.`department_id` IS NULL;

  8. 查询哪个城市没有部门

    1
    2
    3
    4
    5
    SELECT city
    FROM locations l
    LEFT JOIN departments d
    ON d.`location_id` = l.location_id
    WHERE d.`location_id` IS NULL;

  9. 查询部门名为 Sales 或 IT 的员工信息

    1
    2
    3
    4
    5
    6
    SELECT *
    FROM employees e
    JOIN departments d
    ON e.`department_id` = d.`department_id`
    WHERE d.`department_name` = 'Sales'
    OR d.`department_name` = 'IT';

建表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);

  1. 所有有门派的人员信息（ A、B两表共有）

    1
    2
    3
    4
    SELECT * 
    FROM t_emp a
    JOIN t_dept b
    ON a.`deptId` = b.`id`;

  2. 列出所有用户，并显示其机构信息（A的全集）

    1
    2
    3
    4
    SELECT * 
    FROM t_emp a
    LEFT JOIN t_dept b
    ON a.deptId = b.id;

  3. 列出所有门派（B的全集）

    1
    2
    SELECT * 
    FROM t_dept;

  4. 所有不入门派的人员(A的独有)

    1
    2
    3
    4
    5
    SELECT * 
    FROM t_emp a
    LEFT JOIN t_dept b
    ON a.`deptId` = b.`id`
    WHERE b.`id` IS NULL;

  5. 所有没人入的门派 (B的独有)

    1
    2
    3
    4
    5
    SELECT * 
    FROM t_dept b
    LEFT JOIN t_emp a
    ON b.id = a.deptId
    WHERE a.deptId IS NULL;

  6. 列出所有人员和机构的对照关系(AB全有)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT * 
    FROM t_emp a
    LEFT JOIN t_dept b
    ON a.deptId = b.id
    UNION ALL
    SELECT *
    FROM t_dept b LEFT JOIN t_emp a
    ON a.deptId = b.id
    WHERE a.deptId IS NULL;

  7. 列出所有没入派的人员和没人入的门派（A的独有+B的独有）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT * 
    FROM t_emp a
    LEFT JOIN t_dept b
    ON a.`deptId` = b.`id`
    WHERE b.`id` IS NULL
    UNION 
    SELECT * 
    FROM t_dept b
    LEFT JOIN t_emp a
    ON b.id = a.deptId
    WHERE a.deptId IS NULL;