第一幕：综合实战——快递驿站数据库设计

前两幕学了建库建表和增删改查，这一幕把技能全部串起来——从零设计一个校园快递驿站管理系统的数据库。

业务需求

一个快递驿站需要管理：货架、包裹、投诉。核心流程：

快递到达 → 上架存放 → 通知取件 → 学生取件 → 超期/投诉处理

表结构设计

拿到需求先想清楚三件事：几张表？什么关系？每个字段什么类型？

shelves（货架表）           packages（包裹表）           complaints（投诉表）
┌──────────────┐    ┌─────────────────────┐    ┌─────────────────────┐
│ shelf_id  PK │◀───│ shelf_id  FK        │    │ complaint_id  PK    │
│ shelf_code   │    │ package_id  PK      │◀───│ package_id  FK      │
│ area         │    │ tracking_no  UNIQUE │    │ complaint_type      │
│ capacity     │    │ recipient_name      │    │ description         │
│ current_count│    │ recipient_phone     │    │ status              │
└──────────────┘    │ courier_company     │    │ created_at          │
                    │ status              │    └─────────────────────┘
                    │ arrival_time        │
                    │ pickup_time         │
                    │ pickup_code         │
                    └─────────────────────┘

关系：
  shelves 1 ──── N packages    一个货架放多个包裹
  packages 1 ──── N complaints  一个包裹可能被投诉多次

建表顺序

先建被引用的表（shelves），再建引用者（packages），最后建二级引用者（complaints）。否则外键找不到目标表，报错 ERROR 1215: Cannot add foreign key constraint。

完整建库建表 SQL

CREATE DATABASE express_station CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE express_station;

CREATE TABLE shelves (
    shelf_id INT AUTO_INCREMENT PRIMARY KEY,
    shelf_code VARCHAR(20) NOT NULL UNIQUE,
    area VARCHAR(20) NOT NULL,
    capacity INT NOT NULL,
    current_count INT NOT NULL DEFAULT 0
);

CREATE TABLE packages (
    package_id INT AUTO_INCREMENT PRIMARY KEY,
    tracking_no VARCHAR(50) NOT NULL UNIQUE,
    recipient_name VARCHAR(50) NOT NULL,
    recipient_phone VARCHAR(20) NOT NULL,
    shelf_id INT,
    courier_company VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT '待取件',
    arrival_time DATETIME NOT NULL,
    pickup_time DATETIME DEFAULT NULL,
    pickup_code VARCHAR(20) NOT NULL,
    FOREIGN KEY (shelf_id) REFERENCES shelves(shelf_id) ON DELETE SET NULL
);

CREATE TABLE complaints (
    complaint_id INT AUTO_INCREMENT PRIMARY KEY,
    package_id INT NOT NULL,
    complaint_type VARCHAR(50) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT '待处理',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (package_id) REFERENCES packages(package_id) ON DELETE CASCADE
);

自查清单

检查项
建库时设置了 utf8mb4 字符编码？
tracking_no 加了 UNIQUE 约束？
shelf_code 加了 UNIQUE 约束？
status 字段有 DEFAULT 值？
pickup_time 允许为 NULL？
两个外键都写对了？

日常运营事务

学生取件（先查再改）

-- 1. 先查
SELECT * FROM packages WHERE tracking_no = 'ZT2026001';
-- 确认状态是"待取件"

-- 2. 更新包裹状态
UPDATE packages SET status = '已取件', pickup_time = NOW()
WHERE tracking_no = 'ZT2026001';

-- 3. 更新货架数量
UPDATE shelves SET current_count = current_count - 1
WHERE shelf_id = (SELECT shelf_id FROM packages WHERE tracking_no = 'ZT2026001');

批量处理超期包裹

-- 先查：哪些超期超过 5 天
SELECT tracking_no, recipient_name, DATEDIFF(NOW(), arrival_time) AS 超期天数
FROM packages
WHERE status = '待取件' AND DATEDIFF(NOW(), arrival_time) > 5;

-- 批量更新
UPDATE packages SET status = '已退回'
WHERE status = '待取件' AND DATEDIFF(NOW(), arrival_time) > 5;

尝试删除有包裹的货架——踩坑体验

DELETE FROM shelves WHERE shelf_id = 6;
-- ❌ ERROR 1451: Cannot delete or update a parent row:
--    a foreign key constraint fails

外键阻止了破坏数据完整性的操作——必须先移走包裹才能删货架。这就是外键的保护作用。

15 个常用查询场景

#	需求	关键 SQL
1	全部包裹概览	SELECT * FROM packages
2	待取件包裹，早到早排	WHERE status = '待取件' ORDER BY arrival_time ASC
3	顺丰的包裹	WHERE courier_company = '顺丰'
4	待取件且超过 3 天	WHERE status = '待取件' AND DATEDIFF(NOW(), arrival_time) > 3
5	查取件码对应的包裹	WHERE pickup_code = '502847'
6	某人的全部包裹	WHERE recipient_name = '张三'
7	手机号 138000 开头	WHERE recipient_phone LIKE '138000%'
8	已取件包裹取件耗时	TIMESTAMPDIFF(HOUR, arrival_time, pickup_time)
9	有哪些快递公司	SELECT DISTINCT courier_company FROM packages
10	货架使用统计	SELECT *, capacity - current_count AS 剩余 FROM shelves ORDER BY 剩余 ASC
11	待取件 + 顺丰或京东	WHERE status = '待取件' AND courier_company IN ('顺丰', '京东')
12	未处理的投诉	WHERE status = '待处理' ORDER BY created_at DESC
13	编号 5 到 10 的包裹	WHERE package_id BETWEEN 5 AND 10
14	生成取件通知	SELECT CONCAT('【快递通知】收件人', recipient_name, '，您的', courier_company, '快递已到达，取件码：', pickup_code) FROM packages WHERE status = '待取件'
15	NULL 替换显示	SELECT COALESCE(pickup_time, '尚未取件') FROM packages

小结

从一段业务描述到三张有主键外键的表，再到 15 个查询全部跑通——这就是数据库课的核心能力：把真实需求翻译成表结构，再翻译成 SQL。

关键习惯：建表先建被引用者、"先查再改"、外键是用来保护你的不是限制你的。