多表关系
概述
项目开发中，在进行的数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表之间也存在的各种联系，基本分为三种：
一对多(多对一)
多对多
一对一
一对多
在多的一方建立外键，指向一的一方的主键
多对多
建立中间表，中间至少包括两个外键，分别关联双方主键
一对一
多用于单表拆分
联合查询
相关数据表如下：
1
2
3
4
5
6
7
8
9
| +-------+--------+
| id| content|
+-------+--------+
| 1| aa |
+-------+--------+
| 2| bb |
+-------+--------+
| 3| cc |
+-------+--------+
|
1
2
3
4
5
6
7
| +-------+--------+
| id| content|
+-------+--------+
| 1| aa |
+-------+--------+
| 2| cc |
+-------+--------+
|
1
2
3
4
5
6
7
8
9
| +-------+--------+
| id| content|
+-------+--------+
| 1| cc |
+-------+--------+
| 2| dd |
+-------+--------+
| 3| ee |
+-------+--------+
|
笛卡尔积
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| +-------+--------+-------+--------+
| id| content| id| content|
+-------+--------+-------+--------+
| 1| aa | 1| aa |
+-------+--------+-------+--------+
| 1| aa | 1| cc |
+-------+--------+-------+--------+
| 2| bb | 1| aa |
+-------+--------+-------+--------+
| 2| bb | 1| cc |
+-------+--------+-------+--------+
| 3| cc | 1| aa |
+-------+--------+-------+--------+
| 3| cc | 1| cc |
+-------+--------+-------+--------+
|
内连接（inner Join 或 Join）
内连接：仅显示两个表中匹配行，即两表中都有才显示。
SQL如下：
1
2
3
4
5
6
| SELECT A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContent
FROM A INNER JOIN B ON (A.id = B.id)
|
1
2
3
4
5
6
7
| +-------+--------+-------+--------+
| AID|AContent| BID|BContent|
+-------+--------+-------+--------+
| 1| aa | 1| aa |
+-------+--------+-------+--------+
| 2| bb | 2| cc |
+-------+--------+-------+--------+
|
1
2
3
4
5
6
7
8
| SELECT A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContent,
C.id AS CID,
C.content AS CContent
FROM A INNER JOIN B ON (A.id = B.id) INNER JOIN C ON (A.id = C.id)
|
1
2
3
4
5
6
7
| +-------+--------+-------+--------+-------+--------+
| AID|AContent| BID|BContent| CID|CContent|
+-------+--------+-------+--------+-------+--------+
| 1| aa | 1| aa | 1| cc |
+-------+--------+-------+--------+-------+--------+
| 1| aa | 1| aa | 1| dd |
+-------+--------+-------+--------+-------+--------+
|
外连接（outer Join）
左外连接（left outer Join 或 left Join）
左外连接：左表有就显示，不论右表。
1
2
3
4
5
| SELECT A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContent
FROM A LEFT JOIN B ON (A.id = B.id)
|
1
2
3
4
5
6
7
8
9
| +-------+--------+-------+--------+
| AID|AContent| BID|BContent|
+-------+--------+-------+--------+
| 1| aa | 1| aa |
+-------+--------+-------+--------+
| 2| bb | 2| cc |
+-------+--------+-------+--------+
| 3| cc | <null>| <null>|
+-------+--------+-------+--------+
|
右外连接（right outer Join 或 right Join）
右外连接：右表有就显示，不论左表。
1
2
3
4
5
| SELECT A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContent
FROM A RIGHT JOIN B ON (A.id = B.id)
|
1
2
3
4
5
6
7
| +-------+--------+-------+--------+
| AID|AContent| BID|BContent|
+-------+--------+-------+--------+
| 1| aa | 1| aa |
+-------+--------+-------+--------+
| 2| bb | 2| cc |
+-------+--------+-------+--------+
|
全外连接（full outer Join 或 full Join）
全外连接：左表/右表，有一个有就显示。
1
2
3
4
5
6
| SELECT
A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContent
FROM A FULL JOIN B ON (A.id = B.id);
|
1
2
3
4
5
6
7
8
9
| +-------+--------+-------+--------+
| AID|AContent| BID|BContent|
+-------+--------+-------+--------+
| 1| aa | 1| aa |
+-------+--------+-------+--------+
| 2| bb | 2| cc |
+-------+--------+-------+--------+
| 3| cc | <null>| <null>|
+-------+--------+-------+--------+
|
结果集链接 （union 和 union all）
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了
1
| SELECT * FROM A UNION SELECT * from B
|
1
2
3
4
5
6
7
8
9
10
11
| +-------+--------+
| id| content|
+-------+--------+
| 1| aa |
+-------+--------+
| 2| bb |
+-------+--------+
| 3| cc |
+-------+--------+
| 2| cc |
+-------+--------+
|
