在 MYSQL 中的一个查询中执行多个连接

Mehdi Acheli 2023年1月30日 2022年1月13日
  1. 在 MYSQL 中在一个查询中执行多个连接 - 查询构造
  2. 在 MYSQL 中的一个查询中执行多个连接 - 三表连接与自然连接
  3. 在 MYSQL 中的一个查询中执行多个连接 - 使用 ON 关键字的三表连接
  4. 在 MYSQL 中的一个查询中执行多个连接 - WHERE 块内的三表连接
  5. 在 MYSQL 中的一个查询中执行多个连接 - 外部连接案例
在 MYSQL 中的一个查询中执行多个连接

你有没有想过如何在 MySQL 的一个查询中包含多个连接?你来对地方了。请记住,连接允许我们访问其他表中的信息。该信息单独包含以避免冗余。让我们考虑以下示例。让我们从创建三个表开始。

  • client(client_id, client_name) 定义了一个由 client_id 标识并命名为 client_name 的客户。
CREATE TABLE client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(255)
);
  • product(product_id, product_name, unit_price, supplier_cost) 代表商店中由 product_id 标识并命名为 product_nameunit_price 出售的产品。从供应商处购买一单位产品的成本由 supplier_cost 给出:
CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    unit_price INT,
    supplier_cost INT
);
  • product_order(order_id, product_id, client_id, quantity) 表示由 order_id 标识的订单,引用客户 client_id 购买的产品 product_id,数量为 quantity
CREATE TABLE product_order (
    order_id INT PRIMARY KEY,
    product_id INT NOT NULL,
    client_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES product(product_id),
    FOREIGN KEY (client_id) REFERENCES client(client_id)
);

如你所见,它非常简约,但它完全可以胜任。花点时间注意没有多余的信息。产品名称不在 product_order 表中。如果是这样的话,每次购买时都会重复出现该产品的名称。

我们在这里的工作是返还为每个客户实现的利润。从业务的角度来看,可以提出更复杂和有用的查询,但我们只是展示了多表连接。你可以使用以下值填充数据库以测试查询。

INSERT INTO client VALUES (1, 'John');
INSERT INTO client VALUES (2, 'Mehdi');
INSERT INTO client VALUES (3, 'Ali');
INSERT INTO product VALUES (1, 'laptop', 500, 250);
INSERT INTO product VALUES (2, 'tablet', 600, 550);
INSERT INTO product_order VALUES (1, 1, 1, 3);
INSERT INTO product_order VALUES (2, 1, 1, 3);
INSERT INTO product_order VALUES (3, 2, 2, 6);

在 MYSQL 中在一个查询中执行多个连接 - 查询构造

与订单相关的利润按以下方式计算:

$$profit = quantity * (unit\_price - supplier\_cost)$$

如你所见,对于我们的目标查询,我们需要三个值。在 product_order 中找到数量,在 product 中找到单价和供应商成本,最后,在 client 中找到客户名称。因此,需要三表连接。我们在每次查询后给出查询结果。

在 MYSQL 中的一个查询中执行多个连接 - 三表连接与自然连接

根据设计,不同表中的外键与引用的主键具有相同的名称。我们可以通过以下方式使用自然连接来链接三个表。

SELECT client_name, SUM(quantity * (unit_price - supplier_cost)) AS profit
FROM product_order 
     NATURAL JOIN product
     NATURAL JOIN client
GROUP BY client_id;

输出:

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

在 MYSQL 中的一个查询中执行多个连接 - 使用 ON 关键字的三表连接

还有另一种可能来实现我们的目标。我们可以使用 ON 关键字,如下所示:

SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order 
     JOIN product
        ON product_order.product_id = product.product_id 
     JOIN client
        ON product_order.client_id = client.client_id
GROUP BY client.client_id;

输出:

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

在 MYSQL 中的一个查询中执行多个连接 - WHERE 块内的三表连接

最后,执行连接的条件可以合并到 WHERE 块本身中。

SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order 
     JOIN product
     JOIN client
WHERE product_order.product_id = product.product_id 
      AND product_order.client_id = client.client_id
GROUP BY client.client_id;

输出:

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

在 MYSQL 中的一个查询中执行多个连接 - 外部连接案例

回想一下,连接是在属性之间相等的条件下执行的。如果表的某些行中不存在这样的相等性,则合并的行将不会包含在结果连接中(称为内部连接,这是默认的连接)。这可能有问题。特别是,对于上述查询,存在于数据库中但从未购买过任何产品的客户不会出现在结果中。那是因为它们在 product_order 表中没有关联的行,如下图所示。当使用 Web 应用程序时,某些客户创建了帐户但尚未购买任何东西时,可能会出现这种情况。一种解决方案是使用 LEFT OUTER JOIN,其中没有先前订单的客户与 NULL product_order 属性相关联。最后的查询是:

SELECT client_name, SUM(IFNULL(quantity, 0) * (IFNULL(unit_price, 0) - IFNULL(supplier_cost, 0))) AS profit
FROM client
     LEFT OUTER JOIN product_order
         ON product_order.client_id = client.client_id
     LEFT OUTER JOIN product
         ON product.product_id = product_order.product_id
GROUP BY client.client_id;

输出:

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |
| Ali         | 0      |

如上所述,如果当前客户没有订单,product_order 表属性设置为 NULL,包括数量 - product 表属性相同。如果我们希望这些客户的利润值为零,我们可以使用 IFNULL 函数将 NULL 数量值转换为零。unit_pricesupply_cost 相同。可以使用除 0 之外的任何其他默认值。有关 IFNULL 函数的详细信息,请参阅 https://www.w3schools.com/sql/func_mysql_ifnull.asp

我们在下图中给出了内部连接与外部连接的比较的说明。

MySQL 中的多连接

相关文章 - MySQL Join