MySQL数据库基础练习系列22、快递查询系统

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

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

数据库环境

MySQL版本：5.7.31-log

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

字符集：utf8

排序规则：utf8_general_ci

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

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

项目名称与项目简介

快递查询系统是一个为用户提供快递信息查询服务的平台。该系统能够记录快递的寄送信息、查询状态、历史记录等，并提供用户管理功能。通过该系统，用户可以方便地查询自己寄送或接收的快递的实时状态，如运输中、已签收等。系统管理员可以管理用户信息，并监控整个快递查询流程。

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

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

-- 用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID，主键，自增',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，唯一标识',
    password VARCHAR(255) NOT NULL COMMENT '用户密码，加密存储',
    email VARCHAR(100) COMMENT '用户邮箱',
    phone VARCHAR(20) COMMENT '用户手机号',
    gender ENUM('男', '女') NOT NULL COMMENT '用户性别，枚举类型',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间'
);


-- 快递公司表
CREATE TABLE courier_companies (
    company_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '快递公司ID，主键，自增',
    company_name VARCHAR(100) NOT NULL UNIQUE COMMENT '快递公司名称，唯一标识',
    contact_info VARCHAR(255) COMMENT '快递公司联系方式或地址'
);


-- 快递单表
CREATE TABLE express_orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '快递单ID，主键，自增',
    user_id INT NOT NULL COMMENT '用户ID，外键，关联users表的user_id',
    company_id INT NOT NULL COMMENT '快递公司ID，外键，关联courier_companies表的company_id',
    tracking_number VARCHAR(50) NOT NULL UNIQUE COMMENT '快递单号，唯一标识',
    sender_name VARCHAR(100) COMMENT '寄件人姓名',
    receiver_name VARCHAR(100) COMMENT '收件人姓名',
    send_address VARCHAR(255) COMMENT '寄件地址',
    receive_address VARCHAR(255) COMMENT '收件地址',
    status VARCHAR(50) NOT NULL COMMENT '快递状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '快递单创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '快递单更新时间',
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (company_id) REFERENCES courier_companies(company_id)
);


-- 快递状态表
CREATE TABLE express_statuses (
    status_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '快递状态ID，主键，自增',
    status_name VARCHAR(50) NOT NULL UNIQUE COMMENT '快递状态名称，唯一标识'
);


-- 快递状态历史记录表
CREATE TABLE express_status_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '历史记录ID，主键，自增',
    order_id INT NOT NULL COMMENT '快递单ID，外键，关联express_orders表的order_id',
    status_id INT NOT NULL COMMENT '快递状态ID，外键，关联express_statuses表的status_id',
    status_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '状态变更时间',
    FOREIGN KEY (order_id) REFERENCES express_orders(order_id),
    FOREIGN KEY (status_id) REFERENCES express_statuses(status_id)
);


-- 管理员表
CREATE TABLE administrators (
    admin_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '管理员ID，主键，自增',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '管理员用户名，唯一标识',
    password VARCHAR(255) NOT NULL COMMENT '管理员密码，加密存储',
    email VARCHAR(100) COMMENT '管理员邮箱',
    phone VARCHAR(20) COMMENT '管理员手机号',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '管理员创建时间'
);


-- 系统日志表
CREATE TABLE system_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID，主键，自增',
    admin_id INT NOT NULL COMMENT '管理员ID，外键，关联administrators表的admin_id',
    operation VARCHAR(100) NOT NULL COMMENT '操作名称',
    target VARCHAR(100) COMMENT '操作目标',
    details TEXT COMMENT '操作详情',
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '日志记录时间',
    FOREIGN KEY (admin_id) REFERENCES administrators(admin_id)
);

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

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

-- 插入快递状态
INSERT INTO express_statuses (status_name) VALUES ('待揽收'), ('运输中'), ('已签收'), ('已退回');


-- 插入管理员
INSERT INTO administrators (username, password, email, phone) VALUES 
('admin', '123', 'admin@example.com', '1234567890'),
('operator', '123', 'operator@example.com', '0987654321');

-- 插入用户（以三国人物为例）
INSERT INTO users (username, password, email, phone, gender) VALUES 
('诸葛亮', '123', 'zhugeliang@example.com', '13800138000', '男'),
('刘备', '123', 'liubei@example.com', '13900139000', '男'),
('曹操', '123', 'caocao@example.com', '13700137000', '男'),
('孙权', '123', 'sunquan@example.com', '13600136000', '男');

-- 插入快递公司
INSERT INTO courier_companies (company_name, contact_info) VALUES 
('顺丰速运', '4008111111'),
('中通快递', '4008270270'),
('圆通速递', '95554');

-- 插入快递单
INSERT INTO express_orders (user_id, company_id, tracking_number, sender_name, receiver_name, send_address, receive_address, status) VALUES 
(1, 1, '123456789012', '诸葛亮', '张三', '蜀国 成都', '魏国 许昌', '待揽收'),
(2, 2, '234567890123', '刘备', '李四', '蜀国 荆州', '吴国 建业', '运输中'),
(3, 3, '345678901234', '曹操', '王五', '魏国 洛阳', '蜀国 成都', '已签收'),
(1, 2, '456789012345', '诸葛亮', '赵六', '蜀国 成都', '吴国 柴桑', '已退回'),
(2, 1, '567890123456', '刘备', '孙七', '蜀国 汉中', '魏国 邺城', '待揽收'),
(3, 3, '678901234567', '曹操', '周八', '魏国 许昌', '蜀国 绵竹', '运输中');

-- 插入快递状态历史记录（以部分订单的状态变更为例）
INSERT INTO express_status_history (order_id, status_id, status_time) VALUES 
(1, 2, NOW()), -- 假设第一个订单的状态从待揽收变为了运输中
(2, 3, NOW()), -- 假设第二个订单的状态从运输中变为了已签收
(3, 4, NOW()), -- 假设第三个订单的状态从已签收变为了已退回
(4, 2, NOW()); -- 假设第四个订单的状态从待揽收变为了运输中（续上一条记录）

遵循的数据库三范式

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

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

定义：

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

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

解释：

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

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

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

定义：

1. 满足1NF。

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

解释：

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

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

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

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

定义：

1. 满足2NF。

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

解释：

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

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