MySQL 中连接表

本章我们将连接 MySQL 中的表,关系数据库的真正力量和收益来自于连接表。 SQL JOIN子句合并数据库中两个或多个表中的记录。 基本上有两种连接类型:INNEROUTER

在本教程的这一部分中,我们将使用CustomersReservations表。

mysql> SELECT * FROM Customers;
+------------+-------------+
| CustomerId | Name        |
+------------+-------------+
|          1 | Paul Novak  |
|          2 | Terry Neils |
|          3 | Jack Fonda  |
|          4 | Tom Willis  |
+------------+-------------+

这些是Customers表中的值。

mysql> SELECT * FROM Reservations;
+----+------------+------------+
| Id | CustomerId | Day        |
+----+------------+------------+
|  1 |          1 | 2009-11-22 |
|  2 |          2 | 2009-11-28 |
|  3 |          2 | 2009-11-29 |
|  4 |          1 | 2009-11-29 |
|  5 |          3 | 2009-12-02 |
+----+------------+------------+

这些是Reservations表中的值。

CustomersReservations都有CustomerId列。 它是关系列。 如果两个表中的名称相同,则可以使用USING (CustomerId) syntax。 如果名称不同,则说我们有CustomerIdCId,我们将使用ON Customers.CustomerId = Reservations.CId语法。

内连接

内连接是最常见的连接类型。 这也是默认的连接。 内连接仅从数据库表中选择具有匹配值的记录。 我们有 内接头 的三种类型:INNER JOINNATURAL INNER JOINCROSS INNER JOININNER关键字可以省略。

内连接

mysql> SELECT Name, Day FROM Customers AS C JOIN Reservations 
    -> AS R ON C.CustomerId=R.CustomerId;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+
5 rows in set (0.00 sec)

在此SELECT语句中,我们选择了所有已进行预订的客户。 保罗·诺瓦克(Paul Novak)和特里·尼尔斯(Terry Neils)提出了两项​​保留。 杰克·方达(Jack Fonda)制造了一个。 汤姆·威利斯(Tom Willis)失踪了,他尚未做出任何保留。 请注意,我们省略了INNER关键字。

该语句等效于以下语句:

mysql> SELECT Name, Day FROM Customers, Reservations
    -> WHERE Customers.CustomerId=Reservations.CustomerId;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+

我们得到相同的数据。

交叉内连接

CROSS INNER JOIN将一个表中的所有记录与另一表中的所有记录组合在一起。 这种连接几乎没有实用价值。 它也称为记录的笛卡尔积。

mysql> SELECT Name, Day FROM Customers CROSS JOIN Reservations;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Paul Novak  | 2009-11-28 |
| Paul Novak  | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Paul Novak  | 2009-12-02 |
| Terry Neils | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Terry Neils | 2009-11-29 |
| Terry Neils | 2009-12-02 |
| Jack Fonda  | 2009-11-22 |
...

使用以下 SQL 语句可以达到相同的结果:

SELECT Name, Day FROM Customers, Reservations;

外连接

外部连接 不需要两个连接表中的每个记录都具有匹配的记录。 外部连接有三种类型。 左外部连接,右外部连接和完全外部连接。 在创建教程时,MySQL 不支持完全外部连接。

如前所述,内连接是最常见的连接。 外部连接对于查找孤立记录可能很有用。 如果一个人没有预订,是顾客吗? 如果我们无法与客户匹配,预订是否有效?

左外连接

即使与右表不匹配,LEFT OUTER JOIN也将从左表返回所有值。 在这样的行中,将有NULL值。 换句话说,左外部连接返回左表中的所有值,以及右表中的匹配值。 注意,可以省略OUTER关键字。

mysql> SELECT Name, Day FROM Customers LEFT JOIN Reservations
    -> ON Customers.CustomerId=Reservations.CustomerId;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Paul Novak  | 2009-11-29 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
| Tom Willis  | NULL       |
+-------------+------------+

在这里,我们有所有预订的客户,还有一位没有预订的客户。 他的行中有NULL值。

我们可以使用USING关键字获得相同的结果。 这是因为关系列在两个表中具有相同的名称。 SQL 语句将不再那么冗长。

mysql> SELECT Name, Day FROM Customers LEFT JOIN Reservations
    -> USING (CustomerId);
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Paul Novak  | 2009-11-29 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
| Tom Willis  | NULL       |
+-------------+------------+

结果相同,但 SQL 语句较短。

右外连接

RIGHT OUTER JOINRIGHT JOIN相同。 它提供了两个表中所有记录的匹配以及正确表的所有可能性。 右侧孤立记录在左侧显示NULL

mysql> SELECT Name, Day FROM Customers RIGHT JOIN
    -> Reservations USING (CustomerId);
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+

这是两个表的正确连接的输出。 右侧表的所有记录(预订)在左侧均具有匹配的记录(客户)。

自然连接

自然连接将两个具有相同名称的表中的所有列链接在一​​起。 在ustomersReservations表中,有一个名为CustomerId的列。

自然内连接

NATURAL INNER JOIN自动使用所有匹配的列名进行连接。 在我们的表中,两个表中都有一个名为CustomerId的列。

mysql> SELECT Name, Day FROM Customers NATURAL JOIN Reservations;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+

我们得到相同的数据。 SQL 语句不太冗长。

自然左外连接

NATURAL LEFT OUTER JOIN提供表中所有匹配的记录以及左表中的所有其他记录。 它会自动使用所有匹配的列名进行连接。

mysql> SELECT Name, Day FROM Customers 
    -> NATURAL LEFT JOIN Reservations;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Paul Novak  | 2009-11-29 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
| Tom Willis  | NULL       |
+-------------+------------+

结果相同,但击键次数更少。

自然右外连接

NATURAL RIGHT OUTER JOIN提供表中的所有匹配记录以及右表中的所有其他记录。 它会自动使用匹配的列名进行连接。

mysql> SELECT Name, Day FROM Customers
    -> NATURAL RIGHT JOIN Reservations;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+

快速回顾

接下来,我们将创建两个小表来回顾我们在这里学到的东西。

mysql> CREATE TABLE AA(A INTEGER);
mysql> CREATE TABLE BB(B INTEGER);
mysql> INSERT INTO AA VALUES(1);
mysql> INSERT INTO AA VALUES(2);
mysql> INSERT INTO AA VALUES(3);
mysql> INSERT INTO AA VALUES(4);
mysql> INSERT INTO BB VALUES(3);
mysql> INSERT INTO BB VALUES(4);
mysql> INSERT INTO BB VALUES(5);
mysql> INSERT INTO BB VALUES(6);

mysql> SELECT * FROM AA;
+------+
| A    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

mysql> SELECT * FROM BB;
+------+
| B    |
+------+
|    3 |
|    4 |
|    5 |
|    6 |
+------+

我们创建了两个表,并填充了数值数据。 表AA具有两个唯一编号(1、2),表BB也具有两个唯一编号(5、6)。 他们共享两个数字(3、4)。

内连接

mysql> SELECT * FROM AA JOIN BB ON A = B;
+------+------+
| A    | B    |
+------+------+
|    3 |    3 |
|    4 |    4 |
+------+------+

这是两个表上的INNER JOIN。 我们仅从两个表中获得匹配的值。

左外连接

mysql> SELECT * FROM AA LEFT JOIN BB ON A = B;
+------+------+
| A    | B    |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 |    3 |
|    4 |    4 |
+------+------+

这是两个表上的LEFT OUTER JOIN。 我们获得匹配的值以及左表中没有数学记录的值。

右外连接

mysql> SELECT * FROM AA RIGHT JOIN BB ON A = B;
+------+------+
| A    | B    |
+------+------+
|    3 |    3 |
|    4 |    4 |
| NULL |    5 |
| NULL |    6 |
+------+------+

这是两个表上的RIGHT OUTER JOIN。 我们得到匹配的值,再加上右表中没有匹配记录的值。

赞(0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址