细说 MySQL 的三种表关联设计

发布时间：2025-11-05 05:20:53 来源：创站工坊 作者：域名

一、细说通过关联表（N-N）

正常两张表进行关联，种表我们可以采用中间表的关联方式，这是设计最灵活的方式，它可以直接将两张表的细说数据根据某个字段直接关联起来。

下面是种表一个简单的例子来解释这个概念： 假设我们有两个表：students（学生）和 courses（课程）。一个学生可以选修多门课程，关联同时一门课程也可以被多个学生选修。设计这就是细说一个典型的多对多关系。

为了表示学生和课程之间的种表多对多关系，我们可以使用一个中间表 student_courses：

在这个中间表中，关联每一行都表示一个学生和一门课程之间的设计关联。例如，细说第一行表示 Alice（学生ID为1）选修了 Math（课程ID为1）。种表 通过查询这个中间表，关联我们可以轻松地获取某个学生选修的所有课程，或者获取选修了某门课程的所有学生。 这种使用中间表的方式非常灵活，因为它允许我们轻松地添加、删除或修改学生和课程之间的关联，亿华云而不需要修改原始的 students 或 courses 表。

二、主从设计（1-N）

除了上面那种方式，还有一种主从设计，就是一张主表，一张明细表（或者叫做从表）。

主从设计或称为父子表设计是数据库中常见的另一种表关联方式。在这种设计中，主表通常存储主要实体的信息，而明细表或从表则存储与主表实体相关的详细或子项信息。这种设计常用于一对多关系，即一个主表记录对应多个明细表记录。 以下是一个主从设计的例子：

order_id

customer_id

order_date

total_amount

1

101

2023-04-01

100.00

2

102

2023-04-02

150.00

item_id

order_id

product_id

quantity

unit_price

1

1

1001

2

50.00

2

1

1002

1

20.00

3

2

1003

3

50.00

在这个例子中：

通过 order_id 字段，order_items 表与 orders 表建立了关联。这样，我们可以轻松地查询某个订单的所有项，或者查询某个产品的所有订单项。 主从设计的优点是：

需要注意的是，在设计数据库时，应根据实际业务需求和数据关系来选择合适的表关联方式。有时，b2b信息网可能需要结合使用中间表、主从设计或其他设计模式来满足复杂的业务需求。

除了上面说的主从设计，还有一些情况，就是两张表并非主从关系，但是也有一定的逻辑关联性。比如一个手机生产订单，我们要根据这个订单生成一个多个工单，分为原料采购工单，组装工单，包装工单等。这种也是一对多的关系，但并非主从关系，针对这种情况，我们需要做关联设计。

我们可以为手机订单表和工单表创建相应的数据库表结构，并模拟一些基础数据。以下是使用SQL语言创建表和插入数据的示例：

这里，我们为work_orders表的sSrcSlaveId字段设置了外键约束，以确保它引用的是phone_orders表中存在的sid。使用ON DELETE CASCADE选项意味着当删除一个手机订单时，与该订单相关联的所有工单也会被自动删除。

首先，向手机订单表中插入一些数据：

然后，向工单表中插入与手机订单相关联的数据：

这里的sid字段在两张表中都是唯一的，但在各自的表中可以重复。对于work_orders表，sSrcSlaveId字段对应于phone_orders表的sid，用于表示工单与哪个手机订单相关联。 手机订单的总数量为0，我们一般需要在生成工单的时候，去回填订单表的数量字段，这是很常见的需求。 尝试写sql如下：

基于您提供的SQL更新语句，这条语句的目的是更新phone_orders表中sid为1的记录，将其phone_quantity字段设置为与该订单相关联的所有工单的产品数量之和。

首先，我们来分析这条SQL语句的各个部分：

这个子查询从work_orders表中选取sSrcSlaveId（即源单号，对应于phone_orders表的sid）和每个源单号对应的所有工单的产品数量之和（通过SUM(dProductPQty)计算）。结果集包含两列：sSrcSlaveId和计算后的产品数量dProductPQty。

这里使用了JOIN操作来连接phone_orders表（别名为A）和子查询的结果集（别名为B）。连接条件是A.sid = B.sSrcSlaveId，即phone_orders表的唯一键sid与子查询结果集中的sSrcSlaveId相匹配。

此部分将phone_orders表（别名为A）中的phone_quantity字段更新为子查询结果集（别名为B）中对应的dProductPQty值。

这个条件限制了更新的范围，只更新phone_orders表中sid为1的记录。

这条SQL语句的作用是：找出所有与phone_orders表中sid为1的订单相关联的工单，计算这些工单的产品数量之和，然后将phone_orders表中sid为1的记录的phone_quantity字段更新为这个总和。

执行后得到结果：

上面的例子，如果我们换成left join，并且去查询A.sid = 3会发生什么？

相关文章