MySQL数据库基础练习系列目标

很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了，但是不接触项目根本部知道需求，我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息，让大家更好的深入项目进行实战式的练习，可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。

数据库环境

MySQL版本：5.7.31-log

数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。

字符集：utf8

排序规则：utf8_general_ci

使用工具：Navicat Premium 15，可以在下面的连接中下载

https://download.csdn.net/download/feng8403000/89403778  

项目名称与项目简介

订单管理系统是一个用于跟踪、处理和管理订单的系统。它通常涉及客户信息管理、产品信息管理、订单生成、订单状态更新、支付处理等功能。以下是一个简化版的订单管理系统描述及其涉及的数据库表结构：

1. 客户信息表：存储客户的基本信息，如姓名、性别、联系方式等。

2. 产品信息表：存储产品的详细信息，如产品名称、价格、库存量等。

3. 订单信息表：存储订单的基本信息，如订单号、客户ID、订单总金额等。

4. 订单明细表：存储订单中每个产品的详细信息，如产品ID、数量、单价等。

5. 支付信息表：存储订单的支付信息，如支付方式、支付状态等。

6. 物流信息表：存储订单的物流信息，如发货状态、物流公司、运单号等。

数据库DDL（注意创建顺序）

为了直接运行DDL语句并创建表，我们需要确保在创建含有外键约束的表之前，相关的被引用表（即外键指向的表）已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建，否则就会出现没有外键关系导致的创建异常。

-- 首先创建客户信息表，因为订单需要关联客户
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,           -- 客户ID，自增主键
    name VARCHAR(100) NOT NULL,                           -- 客户姓名，不能为空
    gender ENUM('男', '女') NOT NULL,                    -- 客户性别，枚举类型，不能为空
    contact_number VARCHAR(20) NOT NULL,                  -- 联系电话，不能为空
    email VARCHAR(100)                                     -- 邮箱地址，可以为空
);


-- 接着创建产品信息表，因为订单明细需要关联产品
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,           -- 产品ID，自增主键
    product_name VARCHAR(100) NOT NULL,                  -- 产品名称，不能为空
    price DECIMAL(10, 2) NOT NULL,                       -- 产品价格，不能为空
    stock_quantity INT NOT NULL                          -- 库存数量，不能为空
);


-- 然后创建订单信息表，因为订单明细和支付、物流信息都需要关联订单
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,             -- 订单ID，自增主键
    customer_id INT NOT NULL,                            -- 客户ID，不能为空，外键引用
    order_date DATE NOT NULL,                           -- 订单日期，不能为空
    total_amount DECIMAL(10, 2) NOT NULL,               -- 订单总金额，不能为空
    status ENUM('待支付', '已支付', '已发货', '已完成', '已取消') NOT NULL DEFAULT '待支付', -- 订单状态，默认为待支付
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) -- 外键约束，确保客户ID的有效性
);


-- 创建订单明细表，关联订单和产品
CREATE TABLE order_details (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,           -- 订单明细ID，自增主键
    order_id INT NOT NULL,                              -- 订单ID，不能为空，外键引用
    product_id INT NOT NULL,                            -- 产品ID，不能为空，外键引用
    quantity INT NOT NULL,                              -- 购买数量，不能为空
    unit_price DECIMAL(10, 2) NOT NULL,                -- 单价，不能为空
    FOREIGN KEY (order_id) REFERENCES orders(order_id),  -- 外键约束，确保订单ID的有效性
    FOREIGN KEY (product_id) REFERENCES products(product_id) -- 外键约束，确保产品ID的有效性
);


-- 创建支付信息表，关联订单
CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,         -- 支付ID，自增主键
    order_id INT NOT NULL,                             -- 订单ID，不能为空，外键引用
    payment_method VARCHAR(50) NOT NULL,               -- 支付方式，不能为空
    payment_status ENUM('未支付', '支付成功', '支付失败') NOT NULL, -- 支付状态，不能为空
    payment_date DATE,                                -- 支付日期，可以为空
    FOREIGN KEY (order_id) REFERENCES orders(order_id) -- 外键约束，确保订单ID的有效性
);


-- 最后创建物流信息表，关联订单
CREATE TABLE shipping_details (
    shipping_id INT PRIMARY KEY AUTO_INCREMENT,       -- 物流ID，自增主键
    order_id INT NOT NULL,                           -- 订单ID，不能为空，外键引用
    shipping_status ENUM('未发货', '已发货', '已签收') NOT NULL, -- 物流状态，不能为空
    tracking_number VARCHAR(50),                     -- 物流单号，可以为空
    shipping_company VARCHAR(100),                   -- 物流公司，可以为空
    FOREIGN KEY (order_id) REFERENCES orders(order_id) -- 外键约束，确保订单ID的有效性
);

插入数据DML（注意插入数据顺序）

插入数据的时候也要注意主外键关系，如果没有外检的情况下是没有办法插入从表数据的。

INSERT INTO customers (name, gender, contact_number, email) VALUES
('张三', '男', '13800138000', 'zhangsan@example.com'),
('李四', '女', '13900139000', 'lisi@example.com'),
('王五', '男', '13700137000', 'wangwu@example.com'),
('赵六', '女', '13600136000', 'zhaoliu@example.com'),
('孙七', '男', '13500135000', 'sunqi@example.com');

INSERT INTO products (product_name, price, stock_quantity) VALUES
('智能手机', 2999.00, 50),
('笔记本电脑', 5999.00, 30),
('运动鞋', 399.00, 80),
('T恤', 99.00, 100),
('耳机', 199.00, 60);

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-04-01', 3198.00),
(2, '2023-04-02', 5999.00),
(3, '2023-04-03', 798.00),
(4, '2023-04-04', 1194.00),
(5, '2023-04-05', 398.00);

INSERT INTO order_details (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 2999.00),
(1, 5, 2, 99.00),
(2, 2, 1, 5999.00),
(3, 3, 2, 399.00),
(4, 4, 1, 99.00),
(4, 5, 1, 199.00),
(5, 3, 1, 399.00);

INSERT INTO payments (order_id, payment_method, payment_status, payment_date) VALUES
(1, '支付宝', '支付成功', '2023-04-01'),
(2, '微信支付', '未支付', NULL),
(3, '银行卡', '支付成功', '2023-04-03'),
(4, '支付宝', '支付失败', '2023-04-04'),
(5, '余额支付', '支付成功', '2023-04-05');

INSERT INTO shipping_details (order_id, shipping_status, tracking_number, shipping_company) VALUES
(1, '已发货', '1234567890AB', '顺丰速运'),
(2, '未发货', NULL, NULL),
(3, '已签收', '0987654321CD', '中通快递'),
(4, '已发货', 'ABCDEFG12345', '圆通速递'),
(5, '未发货', NULL, NULL);

遵循的数据库三范式

数据库建表的三范式（3NF，Third Normal Form）是关系型数据库设计的基本原则，用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的，每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式：

第一范式（1NF, First Normal Form）

定义：

1. 列不可分割，即数据库表的每一列都是不可分割的原子数据项。

2. 每一列都是不可再分的最小数据单元（也称为最小的原子单元）。

解释：

• 在第一范式中，主要关注的是列的原子性。也就是说，表中的每一列都应该只包含一个值，而不能包含集合、数组或其他复合数据类型。

• 例如，如果有一个“地址”列，它包含了街道、城市、省份和国家等信息，那么这就违反了第一范式。应该将这个“地址”列拆分成多个独立的列，如“街道”、“城市”、“省份”和“国家”。

第二范式（2NF, Second Normal Form）

定义：

1. 满足1NF。

2. 非主键列必须完全依赖于主键，而不能只依赖于主键的一部分（针对复合主键而言）。

解释：

• 第二范式建立在第一范式的基础上，主要关注于主键与非主键列之间的依赖关系。

• 在第二范式中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

• 如果表中的某一列只与复合主键的一部分有关，那么它就不应该存在于这个表中，而应该被分离出去形成另外一张新表。

第三范式（3NF, Third Normal Form）

定义：

1. 满足2NF。

2. 非主键列必须直接依赖于主键，不能存在传递依赖。即非主键列必须直接依赖于整个主键，而不能依赖于主键的一部分。

解释：

• 第三范式是在第二范式的基础上进一步细化的。它主要关注于消除传递依赖，即非主键列不应该依赖于主键的某一部分，而应该直接依赖于整个主键。

• 如果存在传递依赖，那么应该考虑将这个非主键列分离出去，形成新的表，并通过主键或外键与原表进行关联。