目录

一、多表操作的基本模版展示

外键约束模版

操作关联表模版

连接查询模版

子查询模版

二、简单案例展示

（一）两张表情况

（二）三张表情况

（三）多表情况（三张以上）

三、注意事项

一、多表操作的基本模版展示

结合具体的例子，给出多表操作的通用模版，并分析每个模版的作用。

外键约束模版

CREATE TABLE table1 (

  id INT PRIMARY KEY,

  ...

) ENGINE=InnoDB;



CREATE TABLE table2 (

  id INT PRIMARY KEY,

  table1_id INT,

  ...

  FOREIGN KEY (table1_id) REFERENCES table1(id)

) ENGINE=InnoDB;

外键约束模版中，我们可以定义一个外键，将一个表的某个字段设置为另一个表的主键。这样，当我们在更新或删除一张表的记录时，会自动对应更新或删除另一张表中的相关记录。在创建表时，我们需要注意两点：

  • 必须将引擎设置为 InnoDB，才能使用外键约束。
  • 外键所在表（table2）的字段必须是另一张表（table1）的主键。

操作关联表模版

SELECT t1.column1, t2.column2

FROM table1 t1, table2 t2

WHERE t1.id = t2.table1_id;

操作关联表模版中，我们需要用到 JOIN 关键字，将两张表的记录连接起来。常见的 JOIN 类型有 INNER JOIN、LEFT JOIN、RIGHT JOIN 等。上面的模版使用的是 INNER JOIN，它只返回两张表中都存在的记录。在实际应用中，我们需要根据具体的业务需求来选择合适的 JOIN 类型。

连接查询模版

SELECT t1.column1, t2.column2

FROM table1 t1

JOIN table2 t2 ON t1.id = t2.table1_id;

连接查询模版和操作关联表模版类似，但是它使用了 ON 关键字来指定连接条件，更加清晰明了。在实际应用中，我们可以根据实际情况选择操作关联表模版或连接查询模版。

子查询模版

SELECT column1, column2, ...

FROM table1

WHERE column1 IN (SELECT column1 FROM table2 WHERE ...);

子查询模版中，我们在 WHERE 子句中使用了一个子查询来获取 table2 表中符合条件的数据，并将其作为查询条件之一。子查询可以用在 SELECT、INSERT、UPDATE 和 DELETE 等语句中，是一种非常强大的查询手段。

二、简单案例展示

（一）两张表情况

假设我们有两个表，一个是用户表（user），另一个是订单表（order），其中订单表中包含了用户id的外键（user_id）。

我们需要查询出所有已完成的订单及对应的用户名和手机号。可以使用连接查询来实现：

SELECT o.order_id, u.user_name, u.phone_number

FROM `order` o

INNER JOIN `user` u ON o.user_id = u.user_id

WHERE o.order_status = 'completed';

在上面的查询中，我们使用了 INNER JOIN 将订单表和用户表连接起来，连接条件是订单表的 user_id 字段与用户表的 user_id 字段相等。在 SELECT 子句中，我们指定了要查询的字段，注意使用了别名来区分两个表中相同字段名的情况。在 WHERE 子句中，我们过滤出订单状态为已完成的记录。

如果我们需要查询某个用户的订单及对应的用户名和手机号，可以使用子查询来实现：

SELECT o.order_id, u.user_name, u.phone_number

FROM `order` o

INNER JOIN (

    SELECT user_id, user_name, phone_number

    FROM `user`

    WHERE user_id = 123

) u ON o.user_id = u.user_id

WHERE o.order_status = 'completed';

在上面的查询中，我们使用了子查询来获取指定用户的用户名和手机号，并将其命名为 u 表。在连接查询中，我们使用了 u 表来代替用户表，以此来获取指定用户的订单信息。

现在我们想查询每个用户的订单总数，并按照订单总数从高到低进行排序。我们可以使用以下SQL查询：

SELECT u.name, COUNT(o.id) as order_count 

FROM users u 

LEFT JOIN orders o ON u.id = o.user_id 

GROUP BY u.id 

ORDER BY order_count DESC;

在这个查询中，我们使用了LEFT JOIN将users表和orders表关联起来。然后我们使用了COUNT函数来计算每个用户的订单数量，并将其重命名为order_count。最后，我们使用GROUP BY对用户进行分组，并使用ORDER BY按订单总数从高到低排序。

这个查询可以帮助我们了解哪些用户下单最频繁，并根据这些数据做出更明智的业务决策。

（二）三张表情况

假设我们有三个表，一个是订单表（orders），另一个是商品表（products），第三个是订单商品关联表（order_products），用来记录每个订单中包含了哪些商品，其结构如下：

orders：

Field

Type

Null

Key

Default

Extra

order_id

int

NO

PRI

NULL

auto_increment

user_id

int

NO


NULL


order_date

datetime

NO


NULL


status

varchar(20)

NO


NULL


products：

Field

Type

Null

Key

Default

Extra

product_id

int

NO

PRI

NULL

auto_increment

name

varchar(50)

NO


NULL


description

text

NO


NULL


price

decimal(10,2)

NO


NULL


order_products：

Field

Type

Null

Key

Default

Extra

order_id

int

NO

MUL

NULL


product_id

int

NO

MUL

NULL


quantity

int

NO


NULL


unit_price

decimal(10,2)

NO


NULL


现在我们需要查询用户最近一个月的订单中，包含哪些商品，以及每个商品的销售数量、单价和总价。可以使用以下 SQL 语句来实现：

SELECT op.product_id, p.name, op.unit_price, SUM(op.quantity) AS sales_volume, SUM(op.quantity * op.unit_price) AS sales_amount

FROM orders o

INNER JOIN order_products op ON o.order_id = op.order_id

INNER JOIN products p ON op.product_id = p.product_id

WHERE o.user_id = 123

AND o.order_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)

GROUP BY op.product_id, p.name, op.unit_price;

在上面的查询中，我们使用了三个表的连接查询。在 WHERE 子句中，我们过滤出了用户最近一个月的订单记录，并指定了用户id为123。在 SELECT 子句中，我们指定了要查询的字段，其中 SUM 函数用来计算每个商品的销售数量和销售金额，GROUP BY 子句用来分组计算。

注意，在使用 GROUP BY 子句时，必须将所有非聚合字段都列出来，否则会报错。在本例中，我们需要将商品名称和单价列出来。

（三）多表情况（三张以上）

当涉及到5个或更多的表时，通常会使用更高级的查询技术，例如子查询和嵌套查询。以下是一个类似的示例，假设我们有6个表：users、orders、order_items、products、categories和suppliers，每个表的结构如下：

  • users: id, name, email, password
  • orders: id, user_id, created_at
  • order_items: id, order_id, product_id, quantity, price
  • products: id, name, category_id, supplier_id
  • categories: id, name
  • suppliers: id, name, email, phone

现在，我们想要查找所有已下订单的用户的姓名、订单号、订单创建时间、订单中的产品名称、产品所属类别、产品供应商名称、产品数量和价格。

这个查询涉及了6个表，我们需要使用多个JOIN子句来将它们连接起来，同时使用子查询和嵌套查询来过滤结果。

SELECT 

  u.name AS user_name,

  o.id AS order_id,

  o.created_at AS order_created_at,

  p.name AS product_name,

  c.name AS category_name,

  s.name AS supplier_name,

  oi.quantity AS product_quantity,

  oi.price AS product_price

FROM users u

JOIN orders o ON u.id = o.user_id

JOIN order_items oi ON o.id = oi.order_id

JOIN products p ON oi.product_id = p.id

JOIN categories c ON p.category_id = c.id

JOIN suppliers s ON p.supplier_id = s.id

WHERE o.created_at BETWEEN '2022-01-01' AND '2022-12-31'

  AND u.id IN (SELECT user_id FROM orders WHERE created_at BETWEEN '2022-01-01' AND '2022-12-31')

ORDER BY u.name, o.created_at;

这个查询使用了多个JOIN子句，将6个表连接起来。我们还使用了一个子查询来获取在指定时间范围内下单的用户的ID，并使用IN运算符将其与外部查询中的用户ID进行匹配。我们还使用了WHERE子句来过滤指定时间范围内下单的订单，并使用ORDER BY将结果按用户姓名和订单创建时间进行排序。

当涉及到多个表时，保持代码的可读性和可维护性非常重要。使用良好的命名约定和注释，以及遵循最佳实践和代码风格指南，可以使代码更易于理解和维护。

三、注意事项

在进行多表操作时，需要注意以下几点：

  1. 多表连接可能会产生笛卡尔积，需要注意去重或限制返回结果数量。
  2. 在进行关联查询时，尽量使用索引字段作为关联条件，以提高查询效率。
  3. 多表查询涉及到数据的读写，为了保证数据的一致性，应该使用事务进行处理。
  4. 在多表关联查询中，使用子查询时要注意子查询的效率，尽量使用连接查询代替子查询。
  5. 多表操作可能会涉及到数据的修改和删除，因此需要谨慎处理，避免误操作导致数据的丢失。
  6. 在进行多表查询时，应该选择适当的查询方式和语句结构，以提高查询效率。同时，还需要根据实际情况进行调整和优化。