数据库笔记04

多表查询

前提：一起查询的表之间是有关联的，它们之间肯定有关联字段。
1. 笛卡尔积的错误

1.1 笛卡尔积（交叉连接）

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

在SQL92中，笛卡尔积也称为 交叉连接 ，英文是CROSS JOIN,在SQL99中也可以使用 CROSS JOIN 表示交叉连接，它可以将任意表举行连接，即使这些表没有关联。
1.2 问题的产生及办理

笛卡尔积的错误产生条件：

• 省略多表的连接（关联）条件
  
• 连接条件无效
  
• 所有表的所有行互相连接
  

为了避免笛卡尔积，可以在 WHERE 语句中加入连接条件（SQL92语法，SQL99之后有讲）
语法：

1. SELECT  table1.column, table2.column
2. FROM    table1, table2
3. WHERE   table1.column1 = table2.column2;  #连接条件

复制代码

实例：

1. #案例：查询员工的姓名及其部门名称
2. SELECT last_name, department_name
3. FROM employees, departments
4. WHERE employees.department_id = departments.department_id; # 连接条件

复制代码

• 在多个表中含有雷同的列时，必须指明字段来自哪个表。
  

1. SELECT employees.department_id,last_name
2. FROM employees,departments
3. WHERE employees.department_id = departments.department_id;

复制代码

• 可以给表起别名，一旦起了别名在SELECT 和 WHERE 子句中必须使用别名，不能使用表的原名
  
  1. SELECT last_name, department_name,emp.department_id
  2. FROM employees emp, departments de
  3. WHERE emp.department_id = de.department_id;

  复制代码
  原因：在语句执行时，FROM 子句开始执行，此时表的别名会覆盖表的原名，以是起别名实际上是用别名替换原名。
  建议：在每个查询字段都加上它对应的表名
  
• 连接n个表，至少 需要n-1 个连接条件
  

2. 查询分类

留意：这里都是以SQL92标准来实现多表查询
2.1 等值连接 vs 非等值连接

等值连接

连接条件为等于

1. SELECT t.工号,c.`任课教师编号`
2. FROM teacher t,course c
3. WHERE t.`工号`=c.`任课教师编号`;

复制代码

非等值连接

连接条件不为等于 ，可以是大于、不等于、小于等条件

2.2 自连接 vs 非自连接

自连接

表中字段互相关联，如员工表中记录了员工以及她的上级，而她的上级的信息同样在员工表内。

1. SELECT CONCAT(worker.last_name ,' works for '
2.       , manager.last_name)
3. FROM   employees worker, employees manager
4. WHERE  worker.manager_id = manager.employee_id ;

复制代码

  CONCAT(str1,str2,...) 连接字符串的函数，string1, string2, ..., stringN 是你盼望连接的字符串，可以是文本值、列名或其他字符串表达式。
  留意，这里给表取两个别名，本质上这两个表指的是同一张表，只是通过取差别别名的方式将其虚拟成两张表以代表差别的意义，便于举行查询。

非自连接

就是多张表之间的查询，前面所有的例子都黑白自连接
2.3 内连接 vs 外连接

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

在 SQL92 中采用（+）代表从表地点的位置。即左或右外连接中，(+) 表示哪个是从表。
Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。

1. #左外连接
2. SELECT last_name,department_name
3. FROM employees ,departments
4. WHERE employees.department_id = departments.department_id(+);
5. ​
6. #右外连接
7. SELECT last_name,department_name
8. FROM employees ,departments
9. WHERE employees.department_id(+) = departments.department_id;

复制代码

SQL92中没有全外连接
3. SQL99语法实现多表查询

1. SELECT table1.column, table2.column,table3.column
2. FROM table1
3.    JOIN table2 ON table1 和 table2 的连接条件
4.        JOIN table3 ON table2 和 table3 的连接条件

复制代码

3.1 根本语法

使用JOIN...ON..来建立连接表和连接条件

1. SELECT table1.column, table2.column,table3.column
2. FROM table1
3.    JOIN table2 ON table1 和 table2 的连接条件
4.        JOIN table3 ON table2 和 table3 的连接条件

复制代码

语法说明：

• 可以使用 ON 子句指定额外的连接条件。
  
• 这个连接条件是与其它条件分开的。
  
• ON 子句使语句具有更高的易读性。
  
• 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
  

3.2 内连接

1. SELECT 字段列表
2. FROM A表 INNER JOIN B表 # INNER可省略
3. ON 关联条件
4. WHERE 等其他子句;

复制代码

示例：

3.3 外连接

3.3.1 左外连接

1. #实现查询结果是A
2. SELECT 字段列表
3. FROM A表 LEFT JOIN B表
4. ON 关联条件
5. WHERE 等其他子句;

复制代码

示例：

3.3.2 右外连接

1. #实现查询结果是B
2. SELECT 字段列表
3. FROM A表 RIGHT JOIN B表
4. ON 关联条件
5. WHERE 等其他子句;

复制代码

示例：

.3.3 满外连接(FULL OUTER JOIN)

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

3.4 UNION

UNION 可以将多条SELECT 语句的结果组合成一个结果集，但合并的结果对应的列数和数据类型必须雷同，并且相互对应（因为会显示在同一张表上）

• 语法：
  
  1. SELECT column,... FROM table1
  2. UNION [ALL]
  3. SELECT column,... FROM table2

  复制代码
  UNION
  会举行去重操作，返回并集。
  
  
  

UNION ALL
不举行去重操作，直接返回

   执行UNION ALL 效率比UNION 语句高，在实际开发中，如果明确知道合并数据无重复结果或数据不存在重复数据时，尽量使用UNION ALL 语句
  3.5 7种 SQL JOINS 的实现（重要）

可以通过UNION 结合一些连接来实现全连接。好比使用左上图和右中图就可以实现左下图（全外连接）,也可以使用右上图和左中图，等等。

1. #中图：内连接 A∩B
2. SELECT employee_id,last_name,department_name
3. FROM employees e JOIN departments d
4. ON e.`department_id` = d.`department_id`;
5. ​
6. #左上图：左外连接
7. SELECT employee_id,last_name,department_name
8. FROM employees e LEFT JOIN departments d
9. ON e.`department_id` = d.`department_id`;
10. ​
11. #右上图：右外连接
12. SELECT employee_id,last_name,department_name
13. FROM employees e RIGHT JOIN departments d
14. ON e.`department_id` = d.`department_id`;
15. ​
16. #左中图：A - A∩B
17. SELECT employee_id,last_name,department_name
18. FROM employees e LEFT JOIN departments d
19. ON e.`department_id` = d.`department_id`
20. WHERE d.`department_id` IS NULL;
21. ​
22. #右中图：B-A∩B
23. SELECT employee_id,last_name,department_name
24. FROM employees e RIGHT JOIN departments d
25. ON e.`department_id` = d.`department_id`
26. WHERE e.`department_id` IS NULL;
27. ​
28. #左下图：满外连接
29. # 左中图 + 右上图  A∪B
30. SELECT employee_id,last_name,department_name
31. FROM employees e LEFT JOIN departments d
32. ON e.`department_id` = d.`department_id`
33. WHERE d.`department_id` IS NULL
34. UNION ALL  #没有去重操作，效率高
35. SELECT employee_id,last_name,department_name
36. FROM employees e RIGHT JOIN departments d
37. ON e.`department_id` = d.`department_id`;
38. ​
39. #右下图
40. #左中图 + 右中图  A ∪B- A∩B 或者 (A -  A∩B) ∪ （B - A∩B）
41. SELECT employee_id,last_name,department_name
42. FROM employees e LEFT JOIN departments d
43. ON e.`department_id` = d.`department_id`
44. WHERE d.`department_id` IS NULL
45. UNION ALL
46. SELECT employee_id,last_name,department_name
47. FROM employees e RIGHT JOIN departments d
48. ON e.`department_id` = d.`department_id`
49. WHERE e.`department_id` IS NULL

复制代码

4. SQL99 语法新特性

4.1 自然连接

NATURAL JOIN ，会自动查询两张表中 所有雷同的字段 ，然后举行等值连接

1. SELECT employee_id,last_name,department_name
2. FROM employees e JOIN departments d
3. ON e.`department_id` = d.`department_id`
4. AND e.`manager_id` = d.`manager_id`;
5. # 或
6. SELECT employee_id,last_name,department_name
7. FROM employees e NATURAL JOIN departments d;

复制代码

它虽然轻便，但不够灵活，只使用于这一种环境
4.2 USING

USING 在自然连接根本上更加灵活，可以指定表中的同名字段举行等值连接

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

复制代码

但可以看到，它仍有很大的范围性，只能指定同名字段并等值连接
5.留意

实际开发中我们要控制连接表的数量，多表查询黑白常耗费资源的，使查询效率下降严峻。
   【欺压】凌驾三个表禁止 join。需要 join 的字段，数据类型保持绝对同等；多表关联查询时， 保证被关联的字段需要有索引。
  说明：即使双表 join 也要留意表索引、SQL 性能。
  泉源：阿里巴巴《Java开发手册》

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。