数据库笔记04
多表查询前提：一起查询的表之间是有关联的，它们之间肯定有关联字段。
1. 笛卡尔积的错误
1.1 笛卡尔积（交叉连接）
一个数学上的运算，假设我有两个聚集 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个聚集中元素个数的乘积数。https://i-blog.csdnimg.cn/direct/86e81c235c574cf6b290e48c87a8486d.png
在SQL92中，笛卡尔积也称为 交叉连接 ，英文是CROSS JOIN,在SQL99中也可以使用 CROSS JOIN 表示交叉连接，它可以将任意表举行连接，即使这些表没有关联。
1.2 问题的产生及办理
笛卡尔积的错误产生条件：
[*] 省略多表的连接（关联）条件
[*] 连接条件无效
[*] 所有表的所有行互相连接
为了避免笛卡尔积，可以在 WHERE 语句中加入连接条件（SQL92语法，SQL99之后有讲）
语法：
SELECTtable1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件 实例：
#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id; # 连接条件
[*] 在多个表中含有雷同的列时，必须指明字段来自哪个表。
SELECT employees.department_id,last_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
[*] 可以给表起别名，一旦起了别名在SELECT 和 WHERE 子句中必须使用别名，不能使用表的原名
SELECT last_name, department_name,emp.department_id
FROM employees emp, departments de
WHERE emp.department_id = de.department_id; 原因：在语句执行时，FROM 子句开始执行，此时表的别名会覆盖表的原名，以是起别名实际上是用别名替换原名。
建议：在每个查询字段都加上它对应的表名
[*] 连接n个表，至少 需要n-1 个连接条件
2. 查询分类
留意：这里都是以SQL92标准来实现多表查询
2.1 等值连接 vs 非等值连接
等值连接
连接条件为等于
SELECT t.工号,c.`任课教师编号`
FROM teacher t,course c
WHERE t.`工号`=c.`任课教师编号`; 非等值连接
连接条件不为等于 ，可以是大于、不等于、小于等条件
https://i-blog.csdnimg.cn/direct/1d0b20013d3e4712b65551c62715ca44.png
2.2 自连接 vs 非自连接
自连接
表中字段互相关联，如员工表中记录了员工以及她的上级，而她的上级的信息同样在员工表内。
SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHEREworker.manager_id = manager.employee_id ; CONCAT(str1,str2,...) 连接字符串的函数，string1, string2, ..., stringN 是你盼望连接的字符串，可以是文本值、列名或其他字符串表达式。
留意，这里给表取两个别名，本质上这两个表指的是同一张表，只是通过取差别别名的方式将其虚拟成两张表以代表差别的意义，便于举行查询。https://i-blog.csdnimg.cn/direct/b4133cc10a954d00a3b9050c8ab46a6e.png
非自连接
就是多张表之间的查询，前面所有的例子都黑白自连接
2.3 内连接 vs 外连接
[*] 内连接: 合并具有同一列的两个以上的表的行, 结果会集不包罗一个表与另一个表不匹配的行
[*] 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。如果要显示全部记录，则为满外连接，也称为全外连接
[*] 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
将主表的所有记录显示，没有匹配的行从表相应字段显示为NULL
在 SQL92 中采用（+）代表从表地点的位置。即左或右外连接中，(+) 表示哪个是从表。
Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
#左外连接
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; SQL92中没有全外连接
3. SQL99语法实现多表查询
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件 3.1 根本语法
使用JOIN...ON..来建立连接表和连接条件
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件 语法说明：
[*] 可以使用 ON 子句指定额外的连接条件。
[*] 这个连接条件是与其它条件分开的。
[*] ON 子句使语句具有更高的易读性。
[*] 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
3.2 内连接
SELECT 字段列表
FROM A表 INNER JOIN B表 # INNER可省略
ON 关联条件
WHERE 等其他子句; 示例：
https://i-blog.csdnimg.cn/direct/98981e4b4f4f4adc97ecdcb8c68a3c92.png
3.3 外连接
3.3.1 左外连接
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句; 示例：
https://i-blog.csdnimg.cn/direct/5b3d7588136c4bb08253a70b3cd512ac.png
3.3.2 右外连接
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句; 示例：
https://i-blog.csdnimg.cn/direct/cdb0043cab304a72b831eccd16a0de59.png
.3.3 满外连接(FULL OUTER JOIN)
[*] 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
[*] SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
[*] 需要留意的是，MySQL不支持FULL JOIN，但是可以用 UNION实现。
3.4 UNION
UNION 可以将多条SELECT 语句的结果组合成一个结果集，但合并的结果对应的列数和数据类型必须雷同，并且相互对应（因为会显示在同一张表上）
[*] 语法：
SELECT column,... FROM table1
UNION
SELECT column,... FROM table2 UNION
会举行去重操作，返回并集。https://i-blog.csdnimg.cn/direct/b52035e81d93461bab4f15ecfb182e00.png
UNION ALL
不举行去重操作，直接返回
https://i-blog.csdnimg.cn/direct/dc723c3328d74db0a26dc0503e3be742.png
执行UNION ALL 效率比UNION 语句高，在实际开发中，如果明确知道合并数据无重复结果或数据不存在重复数据时，尽量使用UNION ALL 语句
3.5 7种 SQL JOINS 的实现（重要）https://i-blog.csdnimg.cn/direct/7215060bde0641848ee528f5fd80a5fe.png
可以通过UNION 结合一些连接来实现全连接。好比使用左上图和右中图就可以实现左下图（全外连接）,也可以使用右上图和左中图，等等。
#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;
#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#左下图：满外连接
# 左中图 + 右上图A∪B
SELECT employee_id,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 employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右下图
#左中图 + 右中图A ∪B- A∩B 或者 (A -A∩B) ∪ （B - A∩B）
SELECT employee_id,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 employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
4. SQL99 语法新特性
4.1 自然连接
NATURAL JOIN ，会自动查询两张表中 所有雷同的字段 ，然后举行等值连接
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`;
# 或
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d; 它虽然轻便，但不够灵活，只使用于这一种环境
4.2 USING
USING 在自然连接根本上更加灵活，可以指定表中的同名字段举行等值连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id); 但可以看到，它仍有很大的范围性，只能指定同名字段并等值连接
5.留意
实际开发中我们要控制连接表的数量，多表查询黑白常耗费资源的，使查询效率下降严峻。
【欺压】凌驾三个表禁止 join。需要 join 的字段，数据类型保持绝对同等；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要留意表索引、SQL 性能。
泉源：阿里巴巴《Java开发手册》
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]