数据库SQL入门阅读：数据库关系

今天，我们继续进入 sql 和关系数据库系统的世界。在本系列的第三部分中，我们将学习如何使用多个相互关联的表。首先，我们将回顾一些核心概念，然后开始使用 SQL 中的 JOIN 查询。

您还可以通过查看Envato Market 上的SQL 脚本、应用程序和插件来查看 SQL 数据库的运行情况。

跟上来

• 面向初学者的 SQL：第 1 部分

• 面向初学者的 SQL：第 2 部分

介绍

创建数据库时，常识要求我们为不同类型的实体使用单独的表。一些例子是：客户、订单、项目、消息等......但我们还需要在这些表之间建立关系。例如，客户下订单，订单包含项目。这些关系需要在数据库中表示。此外，当使用 SQL 获取数据时，我们需要使用某些类型的 JOIN 查询来获取我们需要的内容。

有几种类型的数据库关系。今天我们将介绍以下内容：

• 一对一关系

• 一对多和多对一关系

• 多对多关系

• 自引用关系

从具有关系的多个表中选择数据时，我们将使用 JOIN 查询。JOIN 有几种类型，我们将了解以下内容：

• 交叉连接

• 自然连接

• 内连接

• 左（外）连接

• 右（外）连接

我们还将了解 ON 子句和 USING 子句。

一对一关系

假设您有一张供客户使用的桌子：

我们可以将客户地址信息放在一个单独的表中：

现在我们在客户表和地址表之间建立了关系。如果每个地址只能属于一个客户，这种关系就是“一对一”。请记住，这种关系并不常见。在大多数情况下，我们包含地址和客户的初始表格可以正常工作。

请注意，现在 Customers 表中有一个名为“address_id”的字段，它引用了 Address 表中的匹配记录。这称为“外键”，用于各种数据库关系。我们将在本文后面讨论这个主题。

我们可以像这样可视化客户和地址记录之间的关系：

请注意，关系的存在可以是可选的，例如拥有没有相关地址记录的客户记录。

一对多和多对一关系

这是最常用的关系类型。考虑一个电子商务网站，具有以下内容：

• 客户可以下很多订单。

• 订单可以包含许多项目。

• 项目可以有多种语言的描述。

在这些情况下，我们需要创建“一对多”关系。这是一个例子：

每个客户可能有零个、一个或多个订单。但是一个订单只能属于一个客户。

多对多关系

在某些情况下，您可能需要关系双方的多个实例。例如，每个订单可以包含多个项目。每个项目也可以是多个订单。

对于这些关系，我们需要创建一个额外的表：

Items_Orders 表只有一个目的，那就是在商品和订单之间创建“多对多”关系。

这是我们如何可视化这种关系的方法：

如果您想在图表中包含 items_orders 记录，它可能如下所示：

自引用关系

这在表需要与自身建立关系时使用。例如，假设您有一个推荐计划。客户可以将其他客户推荐到您的购物网站。该表可能如下所示：

客户 102 和 103 由客户 101 推荐。

这实际上也可以类似于“一对多”关系，因为一个客户可以推荐多个客户。它也可以像树形结构一样可视化：

一位客户可能会推荐零位、一位或多位客户。每个客户只能由一位客户推荐，或者根本没有。

如果您想创建一个自引用的“多对多”关系，您将需要一个额外的表，就像我们在上一节中讨论的那样。

外键

到目前为止，我们只了解了一些概念。现在是时候使用 SQL 将它们变为现实了。对于这一部分，我们需要了解什么是外键。

在上面的关系示例中，我们总是有这些“****_id”字段引用另一个表中的列。在此示例中，Orders 表中的 customer_id 列是外键列：

对于 mysql 这样的数据库，有两种方法可以创建外键列：

显式定义外键

让我们创建一个简单的客户表：

CREATE TABLE customers (   
customer_id INT AUTO_INCREMENT PRIMARY KEY,    
customer_name VARCHAR(100));

现在订单表，其中将包含一个外键：

CREATE TABLE orders (    
order_id INT AUTO_INCREMENT PRIMARY KEY,    
customer_id INT,    
amount DOUBLE,    
FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

两列（customers.customer_id 和 orders.customer_id）应该是完全相同的数据结构。例如，如果一个是 INT，那么另一个不应该是 BIGINT。

请注意，在 MySQL 中，只有 InnoDB 引擎完全支持外键。但是其他存储引擎仍然允许您指定它们而不会给出任何错误。除非您为其指定另一个索引，否则外键列也会自动建立索引。

没有明确声明

无需明确将 customer_id 列声明为外键即可创建相同的订单表：

CREATE TABLE orders (    
order_id INT AUTO_INCREMENT PRIMARY KEY,    
customer_id INT,    
amount DOUBLE,    
INDEX (customer_id));

使用 JOIN 查询检索数据时，您仍然可以将此列视为外键，即使数据库引擎不知道该关系。

SELECT * FROM ordersJOIN customers USING(customer_id)

我们将在本文中进一步了解 JOIN 查询。

可视化关系

我目前最喜欢的用于设计数据库和可视化外键关系的软件是MySQL Workbench。

设计数据库后，您可以导出 SQL 并在服务器上运行它。这对于更大和更复杂的数据库设计非常方便。

加入查询

要从具有关系的数据库中检索数据，我们经常需要使用 JOIN 查询。

在我们开始之前，让我们创建表格和一些示例数据来使用。

CREATE TABLE customers (    
customer_id INT AUTO_INCREMENT PRIMARY KEY,    
customer_name VARCHAR(100)); CREATE TABLE orders (    
order_id INT AUTO_INCREMENT PRIMARY KEY,    
customer_id INT,    
amount DOUBLE,    
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)); 
INSERT INTO `customers` 
(`customer_id`, `customer_name`) 
VALUES(1, 'Adam'),(2, 'Andy'),(3, 'Joe'),(4, 'Sandy'); 
INSERT INTO `orders` 
(`order_id`, `customer_id`, `amount`) 
VALUES(1, 1, 19.99),(2, 1, 35.15),(3, 3, 17.56),(4, 4, 12.34);

我们有 4 个客户。一个客户有两个订单，两个客户每个有一个订单，一个客户没有订单。现在让我们看看我们可以在这些表上运行的不同类型的 JOIN 查询。

交叉连接

这是未指定条件时的默认 JOIN 查询类型。

结果是表格的所谓“笛卡尔积”。这意味着第一个表的每一行都与第二个表的每一行匹配。由于每个表有 4 行，我们最终得到 16 行的结果。

可以选择将 JOIN 关键字替换为逗号。

当然，这种结果通常是没有用的。那么让我们看看其他的连接类型。

自然连接

使用这种 JOIN 查询，表需要有一个匹配的列名。在我们的例子中，两个表都有 customer_id 列。因此，只有当该列的值在两条记录上匹配时，MySQL 才会加入记录。

如您所见，customer_id 列这次只显示一次，因为数据库引擎将此视为公共列。我们可以看到 Adam 下的两个订单，以及 Joe 和 Sandy 下的另外两个订单。最后，我们得到了一些有用的信息。

内部联接

当指定连接条件时，将执行内部连接。在这种情况下，最好让两个表上的 customer_id 字段匹配。结果应该类似于自然连接。

结果是一样的，只是有一点点不同。customer_id 列重复两次，每个表一次。原因是，我们只是要求数据库匹配这两列的值。但它实际上并没有意识到它们代表的是相同的信息。

让我们在查询中添加更多条件。

这次我们只收到超过 15 美元的订单。

ON 条款

在继续讨论其他连接类型之前，我们需要查看 ON 子句。这对于将 JOIN 条件放在单独的子句中很有用。

现在我们可以区分 JOIN 条件和 WHERE 子句条件。但是在功能上也有细微的差别。我们将在 LEFT JOIN 示例中看到这一点。

使用条款

USING 子句类似于 ON 子句，但更短。如果一个列在两个表上的名称相同，我们可以在这里指定它。

事实上，这很像 NATURAL JOIN，因此连接列 (customer_id) 在结果中不会重复两次。

左（外）连接

左连接是外连接的一种。在这些查询中，如果从第二个表中没有找到匹配项，则仍然显示来自第一个表的记录。

尽管安迪没有命令，但他的记录仍在显示。第二个表的列下的值设置为 NULL。

这对于查找没有关系的记录也很有用。例如，我们可以搜索没有下过任何订单的客户。

我们所做的只是寻找 order_id 的 NULL 值。

另请注意，OUTER 关键字是可选的。您可以只使用 LEFT JOIN 而不是 LEFT OUTER JOIN。

条件句

现在让我们看一个带有条件的查询。

那么安迪和桑迪怎么了？LEFT JOIN 应该返回没有匹配订单的客户。问题是 WHERE 子句阻止了这些结果。为了得到它们，我们也可以尝试包含 NULL 条件。

我们得到了安迪，但没有桑迪。但这看起来仍然不对。为了得到我们想要的，我们需要使用 ON 子句。

现在我们得到了每个人，所有超过 15 美元的订单。正如我之前所说，ON 子句的功能有时与 WHERE 子句略有不同。在像这样的外部联接中，即使行与 ON 子句条件不匹配，也会包含行。

右（外）连接

RIGHT OUTER JOIN 的工作原理完全相同，但表的顺序颠倒了。

这次我们没有 NULL 结果，因为每个订单都有匹配的客户记录。我们可以更改表的顺序并获得与 LEFT OUTER JOIN 相同的结果。

现在我们有了这些 NULL 值，因为客户表位于连接的右侧。

结论

感谢您阅读这篇文章。我希望你喜欢它！请留下您的意见和问题，祝您有美好的一天！