首页 > 数据库 >MySQL 之多表设计详解

MySQL 之多表设计详解

时间:2024-09-28 21:53:31浏览次数:11  
标签:用户 id 订单 详解 KEY MySQL 之多表 NULL order

在实际应用场景中,我们经常需要处理包含多种数据实体及其之间复杂关系的业务逻辑,例如电商平台的用户、商品、订单,社交网络的用户、帖子、评论等等。如果将所有数据都堆砌在一张表中,不仅会造成数据冗余、难以维护,还会严重影响查询效率。

为了解决这些问题,我们需要采用多表设计,即将数据分散到多个逻辑关联的表中,并通过建立表与表之间的关系,来保证数据的一致性和完整性,提高数据库的性能和可扩展性。

一、关系模型与关系类型

关系模型是数据库设计的核心概念,它将现实世界中的事物抽象为实体,并将实体之间的联系抽象为关系,最终以二维表的结构来组织和存储数据。

在关系模型中,常见的实体关系类型包括:

  • 一对一 (1:1) 关系: 一个实体最多只与另一个实体关联,例如用户与其身份证信息。

  • 一对多 (1:n) 关系: 一个实体可以与多个其他实体关联,例如一个用户可以拥有多条订单。

  • 多对多 (m:n) 关系: 多个实体可以与多个其他实体关联,例如一个学生可以选择多门课程,一门课程也可以被多个学生选择。

二、MySQL中的五种基本约束

约束是数据库用来维护数据完整性和一致性的重要机制,它限制了数据表中可以存储的数据类型和范围。MySQL提供了五种基本约束,分别是:

  1. 主键约束 (PRIMARY KEY):

    • 唯一标识数据表中的每一条记录。

    • 不能为空值 (NOT NULL)。

    • 一个表只能有一个主键。

    • 通常使用自增整数类型 (INT UNSIGNED AUTO_INCREMENT)。

    CREATE TABLE users (
        user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(255) NOT NULL
    );
  2. 外键约束 (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)
    );
  3. 唯一约束 (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
    );
  4. 非空约束 (NOT NULL):

    • 确保字段值不能为空值 (NULL)。

    CREATE TABLE customers (
        customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        customer_name VARCHAR(255) NOT NULL,
        phone VARCHAR(20)
    );
  5. 默认值约束 (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_idINT UNSIGNEDPRIMARY KEY AUTO_INCREMENT用户ID
usernameVARCHAR(255)NOT NULL UNIQUE用户名
passwordVARCHAR(255)NOT NULL密码
emailVARCHAR(255)NOT NULL UNIQUE邮箱
addressVARCHAR(255)地址

商品表 (products)

字段名数据类型约束说明
product_idINT UNSIGNEDPRIMARY KEY AUTO_INCREMENT商品ID
product_nameVARCHAR(255)NOT NULL商品名称
priceDECIMAL(10,2)NOT NULL价格
stockINT UNSIGNEDNOT NULL库存
descriptionTEXT商品描述

订单表 (orders)

字段名数据类型约束说明
order_idINT UNSIGNEDPRIMARY KEY AUTO_INCREMENT订单ID
user_idINT UNSIGNEDNOT NULL用户ID
order_dateTIMESTAMPNOT NULL DEFAULT CURRENT_TIMESTAMP下单时间
total_amountDECIMAL(10,2)NOT NULL总金额

订单详情表 (order_items)

字段名数据类型约束说明
item_idINT UNSIGNEDPRIMARY KEY AUTO_INCREMENT订单详情ID
order_idINT UNSIGNEDNOT NULL订单ID
product_idINT UNSIGNEDNOT NULL商品ID
quantityINT UNSIGNEDNOT 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

相关文章

  • MySQL 数据类型详解
    在MySQL数据库中,数据类型的选择对性能和存储效率有着至关重要的影响。选择正确的数据类型可以最大限度地利用资源,并提高查询速度。一、数值类型详解数值类型用于存储数字数据,根据数据范围和精度需求,MySQL提供多种整数和浮点数类型。1.整数类型类型大小(字节)范围性能用途......
  • Android页面跳转与返回机制详解
    在Android开发中,页面跳转是实现应用功能交互的重要手段之一。本文将从Activity之间的跳转、Activity与Fragment之间的跳转、Fragment之间的跳转以及页面返回的问题四个方面进行详细解析。一、Activity之间的跳转Activity是Android应用的基本构建块,代表了一个用户界面的单一屏幕......
  • MySQL安全加固
    安装mysql下载地址:http://dev.mysql.com/get/Downloads/MySQL-5.6/mysq1-5.6.33-linux-glibc2.5x86_64.tar.gz解压:tar-zxvfmysql-5.6.33-linux-glibc2.5-x86_64.tar.gz复制解压后的mysql目录:cp-rmysql-5.6.33-linux-glibc2.5-x8664/usr/local/mysql添加用户组groupaddmysql......
  • MySQL 中的 FOREIGN KEY 约束:确保数据完整性的关键
    在MySQL数据库中,FOREIGNKEY(外键)约束是一种非常重要的机制,它可以帮助我们确保数据的完整性和一致性。那么,FOREIGNKEY约束究竟是什么呢?让我们一起来深入了解一下。一、什么是FOREIGNKEY约束?FOREIGNKEY约束是一种用于建立两个表之间关系的约束。它通过在一个表中定义一个......
  • MySQL 性能剖析全攻略
    在使用MySQL数据库的过程中,性能问题往往是让开发者和管理员头疼的难题。为了有效地解决这些问题,我们需要对MySQL进行性能剖析。那么,如何在MySQL中进行性能剖析呢?本文将为你详细介绍。一、为什么要进行性能剖析?MySQL数据库在运行过程中,可能会出现各种性能问题,如查询速度慢......
  • 一文详解几种常见本地大模型个人知识库工具部署、微调及对比选型
    前言近年来,大模型在AI领域崭露头角,成为技术创新的重要驱动力。从AlphaGo的胜利到GPT系列的推出,大模型展现出了强大的语言生成、理解和多任务处理能力,预示着智能化转型的新阶段。然而,要将大模型的潜力转化为实际生产力,需要克服理论到实践的鸿沟,实现从实验室到现实世界的落地......
  • 【HTML|第1期】HTML5视频(Video)元素详解:从起源到应用
    日期:2024年9月9日作者:Commas签名:(ง•_•)ง积跬步以致千里,积小流以成江海……注释:如果您觉在这里插入代码片得有所帮助,帮忙点个赞,也可以关注我,我们一起成长;如果有不对的地方,还望各位大佬不吝赐教,谢谢^-^1.01365=37.7834;0.99365=0.02551.02365=1377.4083;0.......
  • MySQL数据库基础
    一.知识框架一开始学数据库,我们要先大致了解一下整个体系是怎么样的,搭建起一个知识体系,方便记忆和学习。什么是数据库,由名可知,就是保存用户数据的程序就是数据库,那MySQL又是什么呢,其实MySQL就是一个应用软件,通过这个应用软件就可以创建数据库,能够对数据库进行增删查改等一系......
  • python 操作RabbitMq详解
    一、简介:RabbitMq 是实现了高级消息队列协议(AMQP)的开源消息代理中间件。消息队列是一种应用程序对应用程序的通行方式,应用程序通过写消息,将消息传递于队列,由另一应用程序读取完成通信。而作为中间件的RabbitMq无疑是目前最流行的消息队列之一。    RabbitMq......
  • mysql 锁 记录
     表级别锁1.表锁locktablesxxread. 线程只能读locktablesxxwrite其他线程无法读写  2.MDL锁(元数据锁 MDL是为了对表执行CRUD操作时,防止其他线程对这个表结构做了变更,反之亦然注意 MDL是在事务提交后才会释放,因此长事务可能导致修改表操作阻塞  ......