在实际应用场景中,我们经常需要处理包含多种数据实体及其之间复杂关系的业务逻辑,例如电商平台的用户、商品、订单,社交网络的用户、帖子、评论等等。如果将所有数据都堆砌在一张表中,不仅会造成数据冗余、难以维护,还会严重影响查询效率。
为了解决这些问题,我们需要采用多表设计,即将数据分散到多个逻辑关联的表中,并通过建立表与表之间的关系,来保证数据的一致性和完整性,提高数据库的性能和可扩展性。
一、关系模型与关系类型
关系模型是数据库设计的核心概念,它将现实世界中的事物抽象为实体,并将实体之间的联系抽象为关系,最终以二维表的结构来组织和存储数据。
在关系模型中,常见的实体关系类型包括:
-
一对一 (1:1) 关系: 一个实体最多只与另一个实体关联,例如用户与其身份证信息。
-
一对多 (1:n) 关系: 一个实体可以与多个其他实体关联,例如一个用户可以拥有多条订单。
-
多对多 (m:n) 关系: 多个实体可以与多个其他实体关联,例如一个学生可以选择多门课程,一门课程也可以被多个学生选择。
二、MySQL中的五种基本约束
约束是数据库用来维护数据完整性和一致性的重要机制,它限制了数据表中可以存储的数据类型和范围。MySQL提供了五种基本约束,分别是:
-
主键约束 (PRIMARY KEY):
-
唯一标识数据表中的每一条记录。
-
不能为空值 (NOT NULL)。
-
一个表只能有一个主键。
-
通常使用自增整数类型 (INT UNSIGNED AUTO_INCREMENT)。
CREATE TABLE users ( user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) NOT NULL );
-
-
外键约束 (FOREIGN KEY):
-
用于建立和维护不同数据表之间的关系。
-
关联的字段必须拥有相同的数据类型和长度。
-
外键字段的值必须在关联表的主键字段中存在,或者为 NULL。
CREATE TABLE orders ( order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, order_date DATE NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id) );
-
-
唯一约束 (UNIQUE):
-
保证字段值的唯一性,不允许重复值。
-
可以为空值 (NULL),但只能有一个空值。
-
一个表可以有多个唯一约束。
CREATE TABLE products ( product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(255) NOT NULL, sku VARCHAR(255) NOT NULL UNIQUE );
-
-
非空约束 (NOT NULL):
-
确保字段值不能为空值 (NULL)。
CREATE TABLE customers ( customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(255) NOT NULL, phone VARCHAR(20) );
-
-
默认值约束 (DEFAULT):
-
当插入新记录时,如果没有为该字段指定值,则使用默认值。
CREATE TABLE articles ( article_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, status VARCHAR(20) DEFAULT 'draft' );
-
三、多表设计实战案例:电商平台数据库
假设我们要设计一个简单的电商平台数据库,包含用户、商品、订单三个核心实体,以及它们之间的关系。
1. 确定实体和关系
-
用户: 可以注册、登录、浏览商品、下单、查看订单等。
-
商品: 包含名称、价格、库存、描述等信息。
-
订单: 记录用户的购买信息,包括下单时间、商品、数量、总价等。
粗略的实体关系图(E-R)如下:
[用户] 1:n [订单]
n:m [商品]
-
用户与订单之间是一对多关系,一个用户可以有多个订单,一个订单只能属于一个用户。
-
商品与订单之间是多对多关系,一个订单可以包含多个商品,一个商品可以出现在多个订单中。
2. 创建数据表
根据实体关系图,我们可以创建以下数据表:
用户表 (users)
字段名 | 数据类型 | 约束 | 说明 |
user_id | INT UNSIGNED | PRIMARY KEY AUTO_INCREMENT | 用户ID |
username | VARCHAR(255) | NOT NULL UNIQUE | 用户名 |
password | VARCHAR(255) | NOT NULL | 密码 |
VARCHAR(255) | NOT NULL UNIQUE | 邮箱 | |
address | VARCHAR(255) | 地址 |
商品表 (products)
字段名 | 数据类型 | 约束 | 说明 |
product_id | INT UNSIGNED | PRIMARY KEY AUTO_INCREMENT | 商品ID |
product_name | VARCHAR(255) | NOT NULL | 商品名称 |
price | DECIMAL(10,2) | NOT NULL | 价格 |
stock | INT UNSIGNED | NOT NULL | 库存 |
description | TEXT | 商品描述 |
订单表 (orders)
字段名 | 数据类型 | 约束 | 说明 |
order_id | INT UNSIGNED | PRIMARY KEY AUTO_INCREMENT | 订单ID |
user_id | INT UNSIGNED | NOT NULL | 用户ID |
order_date | TIMESTAMP | NOT NULL DEFAULT CURRENT_TIMESTAMP | 下单时间 |
total_amount | DECIMAL(10,2) | NOT NULL | 总金额 |
订单详情表 (order_items)
字段名 | 数据类型 | 约束 | 说明 |
item_id | INT UNSIGNED | PRIMARY KEY AUTO_INCREMENT | 订单详情ID |
order_id | INT UNSIGNED | NOT NULL | 订单ID |
product_id | INT UNSIGNED | NOT NULL | 商品ID |
quantity | INT UNSIGNED | NOT NULL | 数量 |
3. 建立外键关系
-- 在订单表添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(user_id);
-- 在订单详情表添加外键约束
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_orders
FOREIGN KEY (order_id) REFERENCES orders(order_id),
ADD CONSTRAINT fk_order_items_products
FOREIGN KEY (product_id) REFERENCES products(product_id);
4. 多对多关系的实现
我们注意到,商品和订单之间是多对多关系,需要创建一个中间表来关联它们。这个中间表通常包含两个外键,分别指向商品表和订单表的主键。
示例数据:
假设用户表 (users) 中已经存在一个用户,其 user_id 为 1。现在,该用户购买了两个商品,分别是 product_id 为 1 的 "T 恤" 和 product_id 为 2 的 "牛仔裤",数量分别为 2 件和 1 件。
首先,我们需要在 orders 表中插入一条新的订单记录:
INSERT INTO orders (user_id, total_amount) VALUES (1, 150.00);
-- 假设总金额为 150.00 元
执行完上述语句后,新插入的订单记录会自动获得一个自增的 order_id,假设为 101。
接下来,我们需要在 order_items 表中插入两条订单详情记录,分别对应购买的 "T 恤" 和 "牛仔裤":
INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 1, 2);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 2, 1);
通过以上步骤,我们就成功地将用户购买商品的信息存储到了数据库中,并利用多表设计和外键约束,保证了数据的一致性和完整性。
希望通过本文的学习,让各位看官可以掌握MySQL 多表设计原则,并将其应用到实际项目中,感谢各位看官的观看,下期见,谢谢~
标签:用户,id,订单,详解,KEY,MySQL,之多表,NULL,order From: https://blog.csdn.net/weixin_64178283/article/details/142622259