PostgreSQL 关系与连接 - 外键、INNER JOIN、OUTER JOIN 详解

介绍

在这个实验（Lab）中，你将探索 PostgreSQL 中的关系（relationships）和连接（joins）。你将学习如何创建带有外键约束（foreign key constraints）的表，以确保数据的完整性（data integrity）。

你将首先创建两个表，customers 和 orders，并在它们之间建立外键关系。然后，你将把数据插入到这些表中，确保数据符合定义的关系。最后，你将学习如何使用 INNER JOIN 检索数据，并比较 LEFT、RIGHT 和 FULL OUTER JOIN 操作的结果，以了解它们如何处理不同的数据关系。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/col_int("Add Integer Column") postgresql/PostgreSQLGroup -.-> postgresql/col_text("Add Text Column") postgresql/PostgreSQLGroup -.-> postgresql/col_date("Add Date Column") postgresql/PostgreSQLGroup -.-> postgresql/key_primary("Set Primary Key") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/func_call("Call Stored Function") subgraph Lab Skills postgresql/table_init -.-> lab-550959{{"PostgreSQL 关系与连接"}} postgresql/col_int -.-> lab-550959{{"PostgreSQL 关系与连接"}} postgresql/col_text -.-> lab-550959{{"PostgreSQL 关系与连接"}} postgresql/col_date -.-> lab-550959{{"PostgreSQL 关系与连接"}} postgresql/key_primary -.-> lab-550959{{"PostgreSQL 关系与连接"}} postgresql/row_add -.-> lab-550959{{"PostgreSQL 关系与连接"}} postgresql/data_all -.-> lab-550959{{"PostgreSQL 关系与连接"}} postgresql/data_where -.-> lab-550959{{"PostgreSQL 关系与连接"}} postgresql/func_call -.-> lab-550959{{"PostgreSQL 关系与连接"}} end

创建带有外键约束的表

在此步骤中，你将创建两个表，customers 和 orders，并在它们之间建立外键约束（foreign key constraint）。此约束确保表之间的关系得到维护，防止输入无效数据。

理解外键（Foreign Keys）

外键是一个表中的一列，它引用另一个表的主键（primary key）。它在两个表之间建立链接。包含外键的表称为「子」表（"child" table），包含主键的表称为「父」表（"parent" table）。

步骤 1：连接到 PostgreSQL

在你的 LabEx VM 中打开一个终端。使用 psql 命令连接到 PostgreSQL 数据库：

sudo -u postgres psql

现在你应该看到 PostgreSQL 提示符 (postgres=#)。

步骤 2：创建 customers 表

创建具有以下列的 customers 表：

customer_id：每个客户的唯一标识符（主键）。
first_name：客户的名字。
last_name：客户的姓氏。
email：客户的电子邮件地址（必须是唯一的）。

在 psql shell 中执行以下 SQL 命令：

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

此命令创建 customers 表。SERIAL 关键字自动为 customer_id 生成一个数字序列，使其自动递增。PRIMARY KEY 将 customer_id 指定为主键。NOT NULL 确保 first_name 和 last_name 列不能为空，UNIQUE 确保每个电子邮件地址都是唯一的。

步骤 3：创建带有外键的 orders 表

创建具有以下列的 orders 表：

order_id：每个订单的唯一标识符（主键）。
customer_id：下订单的客户的 ID（外键，引用 customers）。
order_date：下订单的日期。
total_amount：订单的总金额。

在 psql shell 中执行以下 SQL 命令：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

此命令创建 orders 表。customer_id 列被定义为使用 REFERENCES 关键字的外键。REFERENCES customers(customer_id) 指定 orders 表中的 customer_id 列引用 customers 表中的 customer_id 列。这建立了外键关系。

步骤 4：验证表创建

通过列出数据库中的表来验证表是否已成功创建。在 psql shell 中执行以下命令：

\dt

你应该看到列出的 customers 和 orders 表。

步骤 5：描述表

要查看表的结构，请使用 \d 命令，后跟表名。例如，要描述 customers 表，请执行：

\d customers

这将显示为你 customers 表定义的列、数据类型和约束。类似地，你可以描述 orders 表：

\d orders

这将显示 customer_id 列上的外键约束。

你现在已经成功创建了两个带有外键约束的表。

插入数据并强制引用完整性

在此步骤中，你将把数据插入到 customers 和 orders 表中，确保引用完整性（referential integrity）得到维护。这意味着你不能为一个不存在于 customers 表中的客户添加订单。

理解引用完整性

引用完整性确保表之间的关系保持一致。在我们的例子中，这意味着 orders 表中的 customer_id 必须存在于 customers 表中。

步骤 1：将数据插入到 customers 表中

使用以下 SQL 命令在 psql shell 中将数据插入到 customers 表中：

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]'),
('David', 'Lee', '[email protected]');

此命令将三个客户添加到 customers 表中。customer_id 会自动生成。

步骤 2：验证数据是否插入到 customers 表中

通过查询 customers 表来验证数据是否已正确插入：

SELECT * FROM customers;

你应该看到你刚刚插入的三个客户，以及他们自动生成的 customer_id 值。输出应类似于这样：

 customer_id | first_name | last_name |         email
-------------+------------+-----------+------------------------
           1 | John       | Doe       | [email protected]
           2 | Jane       | Smith     | [email protected]
           3 | David      | Lee       | [email protected]
(3 rows)

步骤 3：将数据插入到 orders 表中

将数据插入到 orders 表中，引用 customers 表中的 customer_id 值：

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-11-01', 100.00),
(2, '2023-11-05', 250.50),
(1, '2023-11-10', 75.25),
(3, '2023-11-15', 120.00);

此命令将四个订单添加到 orders 表中。每个订单都与 customers 表中的 customer_id 相关联。

步骤 4：验证数据是否插入到 orders 表中

通过查询 orders 表来验证数据是否已正确插入：

SELECT * FROM orders;

你应该看到你刚刚插入的四个订单。输出应类似于这样：

 order_id | customer_id | order_date | total_amount
----------+-------------+------------+--------------
        1 |           1 | 2023-11-01 |       100.00
        2 |           2 | 2023-11-05 |       250.50
        3 |           1 | 2023-11-10 |        75.25
        4 |           3 | 2023-11-15 |       120.00
(4 rows)

步骤 5：尝试插入无效数据（演示引用完整性）

为了演示引用完整性约束，请尝试插入一个 customer_id 在 customers 表中不存在的订单：

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(4, '2023-11-20', 50.00);

你应该看到类似于这样的错误消息：

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(4) is not present in table "customers".

此错误消息确认外键约束正在工作。数据库阻止插入订单，因为 customer_id 4 在 customers 表中不存在。

你现在已经成功地将数据插入到 customers 和 orders 表中，确保了引用完整性。

使用 INNER JOIN 查询数据

在此步骤中，你将学习如何使用 PostgreSQL 中的 INNER JOIN 子句从多个表中检索数据。INNER JOIN 基于相关的列组合来自两个或多个表的行。

理解 INNER JOIN

INNER JOIN 仅返回被连接的两个表中都存在匹配的行。如果没有匹配项，则该行将从结果中排除。

步骤 1：连接到 PostgreSQL

确保你已使用 psql 命令连接到 PostgreSQL 数据库：

sudo -u postgres psql

步骤 2：执行 INNER JOIN 查询

在 psql shell 中执行以下 SQL 查询：

SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

让我们分解这个查询：

SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount：这指定了你要从 orders 和 customers 表中检索的列。使用表名作为前缀（例如，orders.order_id）可以明确每个列来自哪个表。
FROM orders：这指定了你要从中查询的第一个表。
INNER JOIN customers ON orders.customer_id = customers.customer_id：这指定了你要连接的第二个表（customers）和连接条件（orders.customer_id = customers.customer_id）。ON 子句指定 orders 表中的 customer_id 必须与 customers 表中的 customer_id 匹配，才能包含这些行。

步骤 3：分析结果

该查询将返回一个结果集，其中包含每个订单的订单 ID、客户的名字、订单日期和总金额。输出应类似于这样：

 order_id | first_name | order_date | total_amount
----------+------------+------------+--------------
        1 | John       | 2023-11-01 |       100.00
        3 | John       | 2023-11-10 |        75.25
        2 | Jane       | 2023-11-05 |       250.50
        4 | David      | 2023-11-15 |       120.00
(4 rows)

该查询已成功基于 customer_id 连接了 orders 和 customers 表，并检索了所请求的信息。仅包含 customers 表中具有相应客户的订单。

步骤 4：使用别名（可选）

对于更复杂的查询，你可以使用别名来使查询更具可读性。可以使用别名重写先前的查询：

SELECT o.order_id, c.first_name, o.order_date, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

在此查询中，o 是 orders 的别名，c 是 customers 的别名。结果将是相同的，但是查询更简洁。

你现在已经成功地使用 INNER JOIN 子句从多个表中查询了数据。

比较 LEFT、RIGHT 和 FULL OUTER JOIN 的结果

在此步骤中，你将探索和比较 PostgreSQL 中 LEFT OUTER JOIN、RIGHT OUTER JOIN 和 FULL OUTER JOIN 的结果。这些连接从一个或两个表中检索所有行，即使在另一个表中没有匹配的值。

理解 OUTER JOIN

LEFT OUTER JOIN（或 LEFT JOIN）： 返回左表中的所有行以及右表中匹配的行。如果右表中没有匹配项，则右表的列将返回 NULL 值。
RIGHT OUTER JOIN（或 RIGHT JOIN）： 返回右表中的所有行以及左表中匹配的行。如果左表中没有匹配项，则左表的列将返回 NULL 值。
FULL OUTER JOIN（或 FULL JOIN）： 返回两个表中的所有行。如果一个表中没有匹配项，则另一个表的列将返回 NULL 值。

步骤 1：连接到 PostgreSQL

确保你已使用 psql 命令连接到 PostgreSQL 数据库：

sudo -u postgres psql

步骤 2：插入一个没有订单的新客户

将一个没有下任何订单的新客户插入到 customers 表中：

INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Brown', '[email protected]');

步骤 3：验证新客户

验证新客户已添加到 customers 表中：

SELECT * FROM customers;

你应该在结果中看到 Alice Brown，以及一个新的 customer_id（可能是 4）。

步骤 4：执行 LEFT OUTER JOIN

执行以下 SQL 查询以在 customers 和 orders 表之间执行 LEFT OUTER JOIN：

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;

此查询返回所有客户，以及他们下的任何订单。如果客户没有下任何订单，则 order_id 和 order_date 列将包含 NULL 值。输出应类似于这样：

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 John       |        3 | 2023-11-10
 Jane       |        2 | 2023-11-05
 David      |        4 | 2023-11-15
 Alice      |          |
(5 rows)

请注意，即使 Alice Brown 没有下任何订单，她也被包括在内。她的 order_id 和 order_date 列为 NULL。

步骤 5：执行 RIGHT OUTER JOIN

执行以下 SQL 查询以在 customers 和 orders 表之间执行 RIGHT OUTER JOIN：

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id;

此查询返回所有订单，以及下达每个订单的客户的名字。由于每个订单都有相应的客户，因此在这种情况下，结果将与 INNER JOIN 相同。输出应类似于这样：

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 Jane       |        2 | 2023-11-05
 John       |        3 | 2023-11-10
 David      |        4 | 2023-11-15
(4 rows)

步骤 6：执行 FULL OUTER JOIN

执行以下 SQL 查询以在 customers 和 orders 表之间执行 FULL OUTER JOIN：

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

此查询返回所有客户和所有订单。如果客户没有下任何订单，则 order_id 和 order_date 列将包含 NULL 值。如果订单没有相应的客户（由于外键约束，这在当前设置中是不可能的），则 first_name 列将包含 NULL 值。输出应类似于这样：

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 John       |        3 | 2023-11-10
 Jane       |        2 | 2023-11-05
 David      |        4 | 2023-11-15
 Alice      |          |
(5 rows)

请注意，Alice Brown 包含在内，order_id 和 order_date 的值为 NULL。

步骤 7：理解差异

LEFT OUTER JOIN 包括 customers 表中的所有行，即使没有匹配的订单。
RIGHT OUTER JOIN 包括 orders 表中的所有行。在我们的例子中，它的行为类似于 INNER JOIN，因为所有订单都有相应的客户。
FULL OUTER JOIN 包括两个表中的所有行。

你现在已经探索和比较了 PostgreSQL 中 LEFT OUTER JOIN、RIGHT OUTER JOIN 和 FULL OUTER JOIN 的结果。

总结

在这个实验中，你学习了如何在 PostgreSQL 中创建具有外键约束的表，以维护数据的完整性。你创建了 customers 和 orders 表，并使用外键在它们之间建立了关系。然后，你将数据插入到这些表中，确保强制执行外键约束。最后，你探索了不同类型的 JOIN 操作（INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN 和 FULL OUTER JOIN），以从相关表中检索数据，并了解它们如何处理不同的数据关系。