MySQL 中的表连接

た入场券 2022-05-29 10:11 219阅读 0赞

表连接使用JOIN关键字将一个或者多个表按照彼此间的关系连接为一个结果集。  
表连接类型：被主流数据库系统支持的有交叉连接（CROSS JOIN）、内连接（INNER JOIN）、外连接（OUTTER JOIN），另外在有的数据库系统中还支持联合连接（UNION JOIN）。

首先创建需要的数据表：  
CREATE TABLE T\_Customer (FId INT NOT NULL ,FName VARCHAR(20) NOT NULL ,  
FAge INT,PRIMARY KEY (FId))  
CREATE TABLE T\_OrderType (FId INT NOT NULL ,FName VARCHAR(20) NOT NULL,  
PRIMARY KEY (FId))  
CREATE TABLE T\_Order (FId INT NOT NULL ,FNumber VARCHAR(20) NOT NULL ,  
FPrice DECIMAL(10,2),FCustomerId INT,FTypeId INT,PRIMARY KEY (FId))  
其中：  
表T\_Customer保存的是客户信息，FId 为主键、FName 为客户姓名、FAge 为客户年龄；表T\_OrderType 保存的是订单类型，FId为主键、FName为类型名；表T\_Order 为保存的是订单信息，FId为主键、FNumber 为订单号、FPrice为价格、FCustomerId为客户的主键。

再想数据表中添加数据：  
INSERT INTO T\_Customer(FId,FName,FAge)  
VALUES(1,’TOM’,21);  
INSERT INTO T\_Customer(FId,FName,FAge)  
VALUES(2,’MIKE’,24);  
INSERT INTO T\_Customer(FId,FName,FAge)  
VALUES(3,’JACK’,30);  
INSERT INTO T\_Customer(FId,FName,FAge)  
VALUES(4,’TOM’,25);  
INSERT INTO T\_Customer(FId,FName,FAge)  
VALUES(5,’LINDA’,NULL);  
INSERT INTO T\_OrderType(FId,FName)  
VALUES(1,’MarketOrder’);  
INSERT INTO T\_OrderType(FId,FName)  
VALUES(2,’LimitOrder’);  
INSERT INTO T\_OrderType(FId,FName)  
VALUES(3,’Stop Order’);  
INSERT INTO T\_OrderType(FId,FName)  
VALUES(4,’StopLimit Order’);  
INSERT INTO T\_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)  
VALUES(1,’K001′,100,1,1);  
INSERT INTO T\_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)  
VALUES(2,’K002′,200,1,1);  
INSERT INTO T\_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)  
VALUES(3,’T003′,300,1,2);  
INSERT INTO T\_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)  
VALUES(4,’N002′,100,2,2);  
INSERT INTO T\_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)  
VALUES(5,’N003′,500,3,4);  
INSERT INTO T\_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)  
VALUES(6,’T001′,300,4,3);  
INSERT INTO T\_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)  
VALUES(7,’T002′,100,NULL,1);

一、内连接（INNER JOIN）  
内连接组合两张表，并且基于两张表中的关联关系来连接它们。  
使用内连接需要指定表中哪些字段组成关联关系，并且需要指定基于什么条件进行连接。  
在大多数数据库系统中，INNER JOIN 中的 INNER 是可选的，INNER JOIN 是默认的连接方式。  
内连接的语法：  
INNER JOIN table\_name  
ON condition  
其中table\_name 为被关联的表名，condition 为进行连接时的条件。

检索所有的客户姓名为 TOM 的客户的订单号以及订单价格：  
SELECT FNumber,FPrice  
FROM T\_Order INNER JOIN T\_Customer  
ON FCustomerId= T\_Customer.FId  
WHERE T\_Customer.FName=’TOM’  
首先列出了组成结果集所需要的列名，而后则是在 FROM 关键字后指定需要的表，在 INNER JOIN 关键字后指明要被连接的表，而在ON 关键字后则指定了进行连接时所使用的条件。  
由于 T\_Customer 和 T\_Order 表中都有名称为 FId 的列，所以在ON 关键字后的条件中使用 FId 字段的时候必须显示的指明这里使用 FId 字段属于哪个表。  
同样如果在SELECT语句后的字段列表中也不能存在有歧义的字段。例如：  
SELECT FID 时必须为 FId 字段显式的指定所属的表：  
SELECT T\_Order.FId,FNumber,FPrice  
FROM T\_Order INNER JOIN T\_Customer  
ON FCustomerId= T\_Customer.FId  
WHERE T\_Customer.FName=’TOM’

为了避免列名歧义并且提高可读性，使用表连接的时候显式所有的列所属的表。  
如：  
SELECT T\_Order.FId,T\_Order.FNumber,T\_Order.FPrice  
FROM T\_Order INNER JOIN T\_Customer  
ON T\_Order.FCustomerId= T\_Customer.FId  
WHERE T\_Customer.FName=’TOM’

指定列所属的表后，就可以很轻松的引用同名的字段了，  
检索所有的订单以及它们对应的客户的相关信息：  
SELECT T\_Order.FId,T\_Order.FNumber,T\_Order.FPrice,  
T\_Customer.FId,T\_Customer.FName,T\_Customer.FAge  
FROM T\_Order INNER JOIN T\_Customer  
ON T\_Order.FCustomerId= T\_Customer.FId

使用列别名来简化代码：  
SELECT o.FId,o.FNumber,o.FPrice,  
c.FId,c.FName,c .FAge  
FROM T\_Order o JOIN T\_Customer c  
ON o.FCustomerId= c.FId

同时连接 T\_Customer 和 T\_OrderType 两张表才能检索到所需要的信息：  
INNER JOIN T\_OrderType  
ON T\_Order.FTypeId= T\_OrderType.FId

二、不等值连接  
等值连接是最常用的连接，因为它指定的连接条件是一个表中的一个字段必须等于另一个表中的一个字段。  
还存在不等值连接，也就是在连接的条件中可以使用小于（<）、大于（>）、不等于（<>）等运算符，而且还可以使用 LIKE、BETWEEN AND 等运算符，甚至还可以使用函数。

检索价格小于每个客户的年龄的五倍值的订单列表：  
SELECT T\_Order.FNumber,T\_Order.FPrice,  
T\_Customer.FName,T\_Customer.FAge  
FROM T\_Order  
INNER JOIN T\_Customer  
ON T\_Order.FPrice< T\_Customer.FAge\*5

不等值连接产生了大量的查询结果，因为它是对被连接的两张表做了笛卡尔运算，  
如果只想查看与客户对应的订单，那么就要在不等值连接后添加等值连接匹配条件：  
SELECT T\_Order.FNumber,T\_Order.FPrice,  
T\_Customer.FName,T\_Customer.FAge  
FROM T\_Order  
INNER JOIN T\_Customer  
ON T\_Order.FPrice< T\_Customer.FAge\*5  
and T\_Order.FCustomerId=T\_Customer.FId

三、交叉连接  
交叉连接不存在 ON 子句。  
交叉连接会将涉及到的所有表中的所有记录都包含在结果集中。  
可以采用两种方式来定义交叉连接：隐式（被所有数据库支持）的和显式的。

1、隐式的连接只要在 SELECT 语句的 FROM 语句后将要进行交叉连接的表名列出即可  
将 T\_Customer 表和 T\_Order 做交叉连接：  
SELECT T\_Customer.FId, T\_Customer.FName, T\_Customer.FAge,  
T\_Order.FId, T\_Order.FNumber, T\_Order.FPrice  
FROM T\_Customer, T\_Order

在交叉连接中同样可以对表使用别名：  
SELECT c.FId, c.FName, c.FAge,  
o.FId, o.FNumber, o.FPrice  
FROM T\_Customer c, T\_Order o

2、交叉连接的显式定义方式为使用 CROSS JOIN 关键字，其语法与 INNER JOIN 类似。  
将 T\_Customer 表和 T\_Order 做交叉连接：  
SELECT T\_Customer.FId, T\_Customer.FName, T\_Customer.FAge,  
T\_Order.FId, T\_Order.FNumber, T\_Order.FPrice  
FROM T\_Customer  
CROSS JOIN T\_Order

三、自连接  
表与其自身相连接，这样连接就被称为自连接。  
自连接并不是独立于交叉连接、内连接、外连接等这些连接方式之外的另外一种连接方式，而只是这些连接方式的一种特例，  
也就是交叉连接、内连接、外连接等连接方式中只要参与连接的表是同一张表，那么它们就可以被称为自连接。  
其主要用途就是检索一张表内部的匹配情况。

检索与另外一个订单的订单类型一样的所有订单的列表：  
SELECT o1.FNumber,o1.FPrice,o1.FTypeId,  
o2.FNumber,o2.FPrice,o2.FTypeId  
FROM T\_Order o1  
INNER JOIN T\_Order o2  
ON o1.FTypeId=o2.FTypeId and o1.FId<o2.FId  
这里为 T\_Order 表取了两个别名 o1 和 o2，并且在引用表中列的时候也明确的指定了列属于那个表下的。  
使用别名以后我们可以将这两个别名看作结构相同、数据相同的两个不同的表。  
ON 子句末端添加的新条件 “and o1.FId<o2.FId” 检查了别名为 o1 的表的主键不等于名为o2的表的主键，因为主键是唯一的，所以这样就可以确保得到的是一个不同的订单，从而不包含同一张订单。  
注：数据库系统把 “A匹配B” 与 “B匹配A” 看成了两个不同的匹配，所以需要指定 o1.FId

五、外部连接  
内部连接要求组成连接的两个表必须具有匹配的记录，有时我们要求无法匹配的 NULL 值也要显示到结果集中，内部连接无法做到。  
外部连接主要就是用来解决空值匹配问题。

外部连接的语法与内部连接几乎是一样的，主要区别就是对于空值的处理。  
外部连接不需要两个表具有匹配记录，这样可以指定某个表中的记录总是放到结果集中。  
根据哪个表中的记录总是放到结果集中，外部连接分为三种类型：

*  左外部连接（LEFT OUTER JOIN）：还返回左表中不符合连接条件的数据；
 *  右外部连接（RIGHT OUTER JOIN）：还返回右表中不符合连接条件的数据；
 *  和全外部连接（FULLOUTER JOIN）：还返回左表中不符合连接条件的数据以及右表中不符合连接条件的数据，它其实是左外部连接和左外部连接的合集。

三者的共同点是都返回符合连接条件的数据，这一点是和内部连接是一样的，不同点在于它们对不符合连接条件的数据处理。

这里的左表和右表是相对于 JOIN 关键字来说的，位于 JOIN 关键字左侧的表被称为左表，位于 JOIN 关键字右侧的表被称为右表。  
如：  
SELECT o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
FROM T\_Order o  
INNER JOIN T\_Customer c  
ON o.FCustomerId=c.FId  
这里的 T\_Order 就是左表，而 T\_Customer 则是右表。

1、左外部连接  
在左外部连接中，左表中所有的记录都会被放到结果集中，无论是否在右表中存在匹配记录。

查询每张订单的订单号、价格、对应的客户姓名以及客户年龄，如果没有对应的客户，则在客户信息处显示空格：  
SELECT o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
FROM T\_Order o  
LEFT OUTER JOIN T\_Customer c  
ON o.FCustomerId=c.FId

虽然左外部连接包含左表中的所有记录，但是它只提供出示的结果集，WHERE语句仍然会改变最终的结果集。  
为上面的 SQL 语句添加一个WHERE子句，使得结果中不包含价格小于 150 元的订单：  
SELECT o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
FROM T\_Order o  
LEFT OUTER JOIN T\_Customer c  
ON o.FCustomerId=c.FId  
WHERE o.FPrice>=150  
尽管左外部连接返回了 T\_Order 表中的所有记录，但是由于 WHERE 语句的过滤，包括订单号为 T002 在内的所有价格小于 150 元的订单全部被排除在了结果集之外。

2、右外部连接  
在右外部连接中不管是否成功匹配连接条件都会返回右表中的所有记录。

使用右外部连接查询每张订单的信息以及对应的客户信息：  
SELECT o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
FROM T\_Order o  
RIGHT OUTER JOIN T\_Customer c  
ON o.FCustomerId=c.FId

3、全外部连接  
几乎所有的数据库系统都支持左外部连接和右外部连接，但是全外部连接则不是所有数据库系统都支持，  
特别是最常使用的 MYSQL 不支持全外部连接。  
全外部连接是左外部连接和右外部连接的合集：  
即使在右表中不存在匹配连接条件的数据，左表中的所有记录也将被放到结果集中，  
同样即使在左表中不存在匹配连接条件的数据，右表中的所有记录也将被放到结果集中。

使用全外部连接查询每张订单的信息以及对应的客户信息：  
SELECT o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
FROM T\_Order o  
FULL OUTER JOIN T\_Customer c  
ON o.FCustomerId=c.FId

虽然在 MYSQL 中不支持全外部连接，不过由于全外部连接是左外部连接和右外部连接的合集，  
所以可以使用左外部连接和右外部连接来模拟实现全外部连接：  
使用左外部连接和右外部连接分别进行匹配查询，然后使用UNION运算符来取两个查询结果集的合集。

在 MYSQL 中执行下面的 SQL 来实现 T\_Order 表和 T\_Customer 表的全外部连接：  
SELECT o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
FROM T\_Order o  
LEFT OUTER JOIN T\_Customer c  
ON o.FCustomerId=c.FId  
UNION  
SELECT o.FNumber,o.FPrice,o.FCustomerId,  
c.FName,c.FAge  
FROM T\_Order o  
RIGHT OUTER JOIN T\_Customer c  
ON o.FCustomerId=c.FId