在本教程中,您将学习各种MySQL连接语句来查询来自多个表中的数据。
MySQL连接语句简介
关系数据库由多个相关表组成,这些表使用已知为外键列的常用列链接在一起。 因此,从业务角度来看,每个表中的数据是不完整的。
例如,在示例数据库(yiibaidb)中,使用orderNumber列链接的orders和orderdetails表。
orders和orderdetails表的 ER 图如下所示 -
要获取完整的订单数据,需要从订单(orders)和订单详细(orderdetails)表中查询数据。
这就是为什么要使用连接表了。
MySQL连接是一种基于表之间的公共列的值来链接来自一个(自连接)或更多表的数据的方法。
MySQL支持以下类型的连接:
要连接表,可以对相应类型的连接使用CROSS JOIN,INNER JOIN,LEFT JOIN或RIGHT JOIN子句。 在SELECT语句中的FROM子句之后使用了连接子句。
请注意,MySQL不支持完全外部连接。
为了方便您了解每种类型的连接,我们将使用具有以下结构的两个表:t1和t2表:
USE testdb;
CREATE TABLE t1 (
id INT PRIMARY KEY,
pattern VARCHAR(50) NOT NULL
);
CREATE TABLE t2 (
id VARCHAR(50) PRIMARY KEY,
pattern VARCHAR(50) NOT NULL
);
t1和t2表中都有pattern列,此列也是这两个表之间的公共列。执行以下查询语句将数据插入到t1和t2表中:
INSERT INTO t1(id, pattern)
VALUES(1,'Divot'),
(2,'Brick'),
(3,'Grid');
INSERT INTO t2(id, pattern)
VALUES('A','Brick'),
('B','Grid'),
('C','Diamond');
现在两个表中的数据如下所示 -
2. MySQL交叉连接(CROSS JOIN)
CROSS JOIN生成来自多个表的行的笛卡尔乘积。假设您使用CROSS JOIN来连接t1和t2表,结果集将包括t1表中的行与t2表中的行的组合。
要执行交叉连接(最后得到迪卡尔乘积),请使用CROSS JOIN子句,如以下语句所示:
SELECT
t1.id, t2.id
FROM
t1
CROSS JOIN t2;
执行上面语句,得到以下结果 -
mysql> SELECT
t1.id, t2.id
FROM
t1
CROSS JOIN t2;
+----+----+
| id | id |
+----+----+
| 1 | A |
| 2 | A |
| 3 | A |
| 1 | B |
| 2 | B |
| 3 | B |
| 1 | C |
| 2 | C |
| 3 | C |
+----+----+
9 rows in set
如您所见,t1表中的每一行与t2表中的行结合形成笛卡尔乘积。
下图显示了t1和t2表之间的CROSS JOIN连接 -
3. MySQL内连接(INNER JOIN)
要形成一个INNER JOIN连接子句,需要一个称为连接谓词的条件。 INNER JOIN需要两个连接的表中的行具有匹配的列值。 INNER JOIN通过组合基于连接谓词的两个连接表的列值来创建结果集。
要连接两个表,INNER JOIN将第一个表中的每一行与第二个表中的每一行进行比较,以找到满足连接谓词的行对。每当通过匹配非NULL值来满足连接谓词时,两个表中每个匹配的行对的列值将包含在结果集中(可以简单地理解为两个表的交集)。
以下语句使用INNER JOIN子句来连接t1和t2表:
SELECT
t1.id, t2.id
FROM
t1
INNER JOIN
t2 ON t1.pattern = t2.pattern;
在上面语句中,以下表达式是连接谓词:
t1.pattern = t2.pattern
这意味着t1和t2表中的行必须在pattern列中具有相同的值才能包含在结果中。
以下查询的结果可以说明:
+----+----+
| id | id |
+----+----+
| 2 | A |
| 3 | B |
+----+----+
下图显示了t1和t2表之间的INNER JOIN:
在此图中,两个表中的行必须具有相同pattern列值,才能包含在结果集中。
4. MySQL左连接(LEFT JOIN)
类似于INNER JOIN,LEFT JOIN也需要连接谓词。当使用LEFT JOIN连接两个表时,介绍了左表和右表的概念。
与INNER JOIN不同,LEFT JOIN返回左表中的所有行,包括满足连接谓词的行。 对于不匹配连接谓词的行,右表中的列将使用NULL值显示在结果集中。
以下语句使用LEFT JOIN子句来连接t1和t2表:
SELECT
t1.id, t2.id
FROM
t1
LEFT JOIN
t2 ON t1.pattern = t2.pattern
ORDER BY t1.id;
执行上面查询,得到以下结果 -
+----+------+
| id | id |
+----+------+
| 1 | NULL |
| 2 | A |
| 3 | B |
+----+------+
3 rows in set
如上所见,t1表中的所有行都包含在结果集中。 对于t2表(右表)中没有任何匹配t1表(左表)中的行,在t2表中的列使用NULL显示。
下图显示了在t1和t2表上使用LEFT JOIN子句:
在此图中,以下行具有相同的pattern:(2和A),(3和B)。 t1表中ID为1的行在t2表中没有匹配的行,因此,在结果集中的t2表的列使用NULL代替显示。
5. MySQL右连接(RIGHT JOIN)
右连接(RIGHT JOIN)类似于右连接(LEFT JOIN),除了表的处理是相反的。使用RIGHT JOIN,右表格(t2)中的每一行将显示在结果集中。 对于右表中没有左表(t1)中的匹配行的行,左表(t1)中的列会显示NULL。
以下语句使用RIGHT JOIN连接t1和t2表:
SELECT
t1.id, t2.id
FROM
t1
RIGHT JOIN
t2 on t1.pattern = t2.pattern
ORDER BY t2.id;
执行上面是查询语句,得到下面结果 -
+------+----+
| id | id |
+------+----+
| 2 | A |
| 3 | B |
| NULL | C |
+------+----+
3 rows in set
在此结果中,来自右表(t2)的所有行都显示在结果集中。对于左表(t1)中没有匹配右表(t2)中的行,则左表(t1)的列将使用NULL代替显示。
下图显示了t1和t2表之间的右连接(RIGHT JOIN):
在本教程中,您已经学习了各种MySQL连接语句,包括交叉连接,内部连接,左连接和右连接,以从两个或多个表查询数据。
¥ 我要打赏
纠错/补充
收藏
加QQ群啦,易百教程官方技术学习群
注意:建议每个人选自己的技术方向加群,同一个QQ最多限加 3 个群。