三、表设计：好表是成功的一半
3.1 数据类型详解
数值类型
-- DECIMAL 示例：总位数10，小数点后2位
price DECIMAL(10,2) -- 最大 99999999.99
为什么金额用 DECIMAL 不用 FLOAT？
-- FLOAT 的问题
SELECT 0.1 + 0.2; -- 可能得到 0.30000000000000004
-- DECIMAL 精确计算
SELECT 0.1 + 0.2; -- 0.3
字符串类型
-- CHAR vs VARCHAR
CHAR(10) -> 无论存"ab"还是"abcdefghij"，都占10字节
VARCHAR(10) -> "ab"占2+1字节，"abcdefghij"占10+1字节
日期时间类型
-- 常用日期函数
SELECT NOW(); -- 当前日期时间
SELECT CURDATE(); -- 当前日期
SELECT CURTIME(); -- 当前时间
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 7天后
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 天数差
SELECT YEAR(created_at) FROM users; -- 提取年份
3.2 约束（Constraints）
约束是数据库强制执行的规则，保证数据的完整性。
-- 1. NOT NULL：不能为空
email VARCHAR(100) NOT NULL
-- 2. UNIQUE：值唯一
username VARCHAR(50) UNIQUE
-- 3. PRIMARY KEY：主键（NOT NULL + UNIQUE + 索引）
id INT PRIMARY KEY
-- 4. FOREIGN KEY：外键（关联其他表）
user_id INT REFERENCES users(id)
-- 5. CHECK：检查条件
age INT CHECK (age >= 0 AND age <= 150)
-- 6. DEFAULT：默认值
status TINYINT DEFAULT 1
3.3 主键与外键
-- 创建带外键的表
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE -- 用户删除时，同时删除订单
ON UPDATE CASCADE -- 用户ID更新时，自动更新
);
-- ON DELETE 选项
-- CASCADE: 删除用户时，删除关联订单
-- SET NULL: 删除用户时，订单的user_id设为NULL
-- RESTRICT: 如果有关联订单，禁止删除用户
-- NO ACTION: 同 RESTRICT
3.4 索引基础
索引是提高查询速度的核心手段。
-- 创建索引
CREATE INDEX idx_username ON users(username);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 创建复合索引
CREATE INDEX idx_age_status ON users(age, status);
-- 查看索引
SHOW INDEX FROM users;
-- 删除索引
DROP INDEX idx_username ON users;
-- 主键本身就是索引
-- 外键会自动创建索引
3.5 三大范式（Normalization）
范式是表设计的规范，目的是减少数据冗余、避免更新异常。
第一范式（1NF）：列不可再分
-- ❌ 违反1NF（电话号码列包含多个值）
CREATE TABLE students (
id INT,
name VARCHAR(50),
phone_numbers VARCHAR(200) -- "13812345678,13987654321"
);
-- ✅ 符合1NF
CREATE TABLE students (
id INT,
name VARCHAR(50),
phone VARCHAR(20)
);
-- 或者拆分成多行
第二范式（2NF）：消除部分依赖
-- ❌ 违反2NF（订单详情表中，产品名称依赖产品ID，不是完全依赖订单ID）
CREATE TABLE order_details (
order_id INT,
product_id INT,
product_name VARCHAR(100), -- 部分依赖 product_id
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- ✅ 符合2NF（拆分成两张表）
CREATE TABLE order_details (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100)
);
第三范式（3NF）：消除传递依赖
-- ❌ 违反3NF（省份名依赖省份ID，省份ID依赖用户ID）
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
province_id INT,
province_name VARCHAR(20) -- 传递依赖：id → province_id → province_name
);
-- ✅ 符合3NF
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
province_id INT,
FOREIGN KEY (province_id) REFERENCES provinces(id)
);
CREATE TABLE provinces (
id INT PRIMARY KEY,
name VARCHAR(20)
);
3.6 范式 vs 反范式
-- 反范式设计示例：订单表冗余用户名
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
username VARCHAR(50), -- 冗余字段，避免 JOIN users 表
amount DECIMAL(10,2)
);
