细说 MySQL 的三种表关联设计

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

一、通过关联表(N-N)

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

下面是一个简单的例子来解释这个概念: 假设我们有两个表:students(学生)和 courses(课程)。一个学生可以选修多门课程,同时一门课程也可以被多个学生选修。这就是一个典型的多对多关系。

1.students 表

2.courses 表

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

3.student_courses 表

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

二、主从设计(1-N)

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

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

1.主表:orders(订单)

order_id

customer_id

order_date

total_amount

1

101

2023-04-01

100.00

2

102

2023-04-02

150.00

2.明细表:order_items(订单项)

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

在这个例子中:

  • orders 表是主表,它存储了订单的基本信息,如订单ID、客户ID、订单日期和总金额。
  • order_items 表是明细表或从表,它存储了每个订单的详细项,如订单项ID、所属的订单ID、产品ID、数量和单价。

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

  • 结构清晰:主表和明细表各司其职,主表存储总体信息,明细表存储详细信息。
  • 灵活扩展:如果需要添加更多的与主表相关的详细信息,可以在明细表中添加更多字段,而不会影响主表的结构。
  • 易于维护:由于主表和明细表是分离的,所以对其中一个表的修改不会影响到另一个表。

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

三、关联设计(1-N)

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

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

  1. 创建手机订单表 (phone_orders)
CREATE TABLE phone_orders (  
    sid INT PRIMARY KEY NOT NULL,  
    phone_name VARCHAR(100) NOT NULL,  
    phone_quantity INT NOT NULL  
);
  1. 创建工单表 (work_orders)

CREATE TABLE work_orders (  
    sid INT PRIMARY KEY NOT NULL,  
    sSrcSlaveId INT NOT NULL, -- 源单号,即手机订单表的sid  
    dProductPQty INT NOT NULL, -- 产品数量  
    FOREIGN KEY (sSrcSlaveId) REFERENCES phone_orders(sid) ON DELETE CASCADE  
);

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

3. 模拟基础数据

首先,向手机订单表中插入一些数据:

INSERT INTO phone_orders (sid, phone_name, phone_quantity) VALUES  
(1, 'iPhone 13', 0),  
(2, 'Galaxy S22', 0),  
(3, 'Pixel 6', 0);

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

INSERT INTO work_orders (sid, sSrcSlaveId, dProductPQty) VALUES  
(1, 1, 20), -- 对应phone_orders中sid为1的订单,产品数量为20  
(2, 1, 30), -- 同一个订单的另一个工单,产品数量为30  
(3, 2, 50), -- 对应phone_orders中sid为2的订单,产品数量为50

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

update phone_orders A join (
 SELECT sSrcSlaveId,SUM(dProductPQty) dProductPQty from work_orders GROUP BY  sSrcSlaveId
) B on A.sid = B.sSrcSlaveId
set A.phone_quantity = B.dProductPQty
where  A.sid = 1;

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

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

4.子查询:

SELECT sSrcSlaveId, SUM(dProductPQty) dProductPQty 
FROM work_orders 
GROUP BY sSrcSlaveId

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

5.JOIN操作:

UPDATE phone_orders A 
JOIN (
    ...子查询...
) B 
ON A.sid = B.sSrcSlaveId

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

6.SET操作:

SET A.phone_quantity = B.dProductPQty

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

7.WHERE条件:

WHERE A.sid = 1

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

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

执行后得到结果:

思考题

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

update phone_orders A left join (
 SELECT sSrcSlaveId,SUM(dProductPQty) dProductPQty from work_orders GROUP BY  sSrcSlaveId
) B on A.sid = B.sSrcSlaveId
set A.phone_quantity = B.dProductPQty
where  A.sid = 3;
责任编辑:赵宁宁 来源: java小白翻身
相关推荐

2010-11-23 10:11:23

mysql建表乱码

2015-09-14 09:31:44

结对设计

2022-05-27 11:33:02

前端代码设计模式

2017-12-29 08:26:28

存储引擎MySQL

2021-08-10 15:44:37

PostgreSQL表分区分区表

2010-11-22 17:00:10

MySQL建表语句

2017-07-03 18:24:39

MySQL数据冗余

2010-09-30 11:20:35

DB2表静默状态

2022-06-16 08:24:59

设计模式代码前端

2010-10-15 10:37:27

MySQL创建关联表

2022-03-15 11:31:17

MySQL日志格式

2010-11-10 13:28:06

SQL Server删

2011-01-18 15:35:59

jQueryJavaScriptweb

2010-06-13 16:04:14

MySQL三种安装方式

2010-05-11 14:08:50

MySQL数字类型

2010-10-09 11:36:30

MySQL字符集

2010-05-25 18:50:22

MySQL安装

2010-09-06 10:04:31

CSS样式表

2011-06-28 14:02:49

表分区

2010-10-13 11:19:11

MySQL数据文件
点赞
收藏

51CTO技术栈公众号