MySQL数据库设计从入门到魔改 🛠️
🏗️ 数据库建筑师手册 在这里你将掌握：
- 数据库的诞生与毁灭 🍼💥
- 数据表的七十二变 🐒
- 约束条件的金钟罩 ⛑️
- 索引的加速秘籍 🚀
- 注释文学的艺术 📝
1. 数据库的生与死 💫
🧙 创世与湮灭
sql
-- 开天辟地：创建数据库
CREATE DATABASE 电商系统
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
-- 格式化警告：慎用！
DROP DATABASE 电商系统; -- 💀 数据灰飞烟灭2. 数据表基础三剑客 🗡️
sql
CREATE TABLE 学生档案 (
-- 🆔 身份标识（主键：唯一且非空）
学号 INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增主键',
-- 👤 基础信息（必须填写）
姓名 VARCHAR(20) NOT NULL COMMENT '不超过10个汉字',
-- ⚧️ 枚举约束（只能选这三个值）
性别 ENUM('男', '女', '其他') DEFAULT '其他' COMMENT '默认显示"其他"',
-- 🗓️ 日期类型（存储格式：YYYY-MM-DD）
入学日期 DATE NOT NULL COMMENT '格式示例：2023-09-01',
-- 📱 唯一约束（手机号不能重复）
手机号 CHAR(11) UNIQUE COMMENT '允许为空但不可重复'
) ENGINE=InnoDB COMMENT='学生基本信息档案';sql
/* 🔍 表结构体检报告 */
DESC 学生档案;
/* 📜 表的DNA检测报告 */
SHOW CREATE TABLE 学生档案;sql
-- 🧨 普通删除（表不存在会报错）
DROP TABLE 学生档案;
-- 🧯 安全删除（推荐写法）
DROP TABLE IF EXISTS 学生档案;🚨 主键生存法则
- 唯一性：就像身份证号，不能重复
- 非空性：必须填写，不能为NULL
- 简洁性：尽量用数字类型（查询更快）
- 自增技巧：
AUTO_INCREMENT让数据库自动分配ID
📱 唯一约束小剧场
sql
-- 正确插入
INSERT INTO 学生档案 (手机号) VALUES ('13800138000'); ✅
-- 错误示范：重复手机号
INSERT INTO 学生档案 (手机号) VALUES ('13800138000'); ❌
-- 报错：Duplicate entry '13800138000' for key '手机号'
-- 特殊技巧：允许NULL值
INSERT INTO 学生档案 (手机号) VALUES (NULL); ✅
INSERT INTO 学生档案 (手机号) VALUES (NULL); ✅
-- NULL不算重复，可以多次插入🧐 字段类型冷知识
| 字段类型 | 存储空间 | 使用场景 | 类比说明 |
|---|---|---|---|
| VARCHAR(20) | 动态分配 | 姓名、地址等变长文本 | 行李箱（按需使用空间） |
| CHAR(11) | 固定11字符 | 手机号、身份证等定长数据 | 固定尺寸的收纳盒 |
| ENUM | 1-2字节 | 有限选项的字段 | 选择题的选项 |
3. 数据类型收纳指南 📦
🧳 数据收纳三大件
数字保险箱
INT：存年龄、数量等整数（-21亿~21亿）DECIMAL(5,2)：精确存钱（总5位，小数2位）FLOAT：存科学数据，可能有微小误差
文字时光机
VARCHAR(100)：可变长度（省空间，最大65535）CHAR(11)：固定长度（适合手机号/身份证）TEXT：存小作文（最大65,535字符）
时间管理器
DATE：记录生日（'2023-08-20'）DATETIME：精确到秒（'2023-08-20 14:30:00'）TIMESTAMP：自动记录修改时间
4. 约束全家福 👨👩👧👦
🚧 数据交警的执法手册
sql
CREATE TABLE 员工信息 (
ID INT PRIMARY KEY AUTO_INCREMENT, -- 主键（唯一+非空）
姓名 VARCHAR(20) NOT NULL, -- 必填项
邮箱 VARCHAR(50) UNIQUE, -- 唯一值
部门ID INT DEFAULT 0, -- 默认值
入职年份 YEAR CHECK (入职年份 > 2000), -- 自定义检查
FOREIGN KEY (部门ID) REFERENCES 部门表(ID) -- 外键约束
);5. 表结构美容院 💅
🧑🔧 表结构整容指南
sql
-- 增发手术：添加新列
ALTER TABLE 用户表
ADD 个性签名 VARCHAR(100) AFTER 性别; -- 这里after是添加到性别后面
-- 瘦身计划：删除列
ALTER TABLE 商品表
DROP COLUMN 过期时间;
-- 改名换姓
ALTER TABLE 旧表名 RENAME TO 新表名;
-- 时间列整容（正确姿势）
ALTER TABLE users
MODIFY last_login
DATETIME
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP; -- ✅ 正确的时间函数，数据更新时自动更新
-- 微整形：修改列定义
ALTER TABLE 员工表
MODIFY 工资 DECIMAL(10,2) NOT NULL DEFAULT 5000;
-- 身份置换：主键修改
ALTER TABLE 订单表
DROP PRIMARY KEY,
ADD PRIMARY KEY (订单号, 用户ID);6. 索引加速器 🚄
💡 索引使用秘籍
sql
-- 创建普通索引
CREATE INDEX 姓名索引 ON 学生表(姓名);
-- 唯一索引（加速+唯一性）
CREATE UNIQUE INDEX 邮箱索引 ON 用户表(邮箱);
-- 联合索引（查询加速套餐）
CREATE INDEX 联合索引 ON 订单表(用户ID, 下单时间);
-- 查看索引清单
SHOW INDEX FROM 用户表;
-- 删除多余索引
DROP INDEX 姓名索引 ON 学生表;7. 注释文学 📝
🎨 代码即文档
sql
CREATE TABLE 图书信息 (
书号 INT PRIMARY KEY COMMENT '唯一标识符',
书名 VARCHAR(100) NOT NULL COMMENT '书籍全称',
价格 DECIMAL(6,2) COMMENT '人民币定价'
) COMMENT='图书馆藏书基本信息';8. 综合实战：电商系统设计 🛒
sql
-- 创建商品表
CREATE TABLE 商品 (
商品ID INT AUTO_INCREMENT PRIMARY KEY,
名称 VARCHAR(100) NOT NULL,
价格 DECIMAL(10,2) CHECK(价格 > 0),
库存 INT UNSIGNED DEFAULT 0,
上架时间 DATETIME DEFAULT CURRENT_TIMESTAMP,
分类ID INT,
FOREIGN KEY (分类ID) REFERENCES 分类表(ID)
) ENGINE=InnoDB COMMENT='商品基本信息';
-- 添加折扣率列
ALTER TABLE 商品
ADD 折扣率 DECIMAL(3,2) DEFAULT 1.00
AFTER 价格;
-- 创建商品名称索引
CREATE INDEX 商品名称索引 ON 商品(名称);🚨 新手避坑指南
- 主键陷阱：每个表必须有主键，但只能有一个
- 字符集迷途：推荐使用utf8mb4（支持emoji）
- 命名雷区：避免使用保留字（如order, group）
- 外键警告：需要关联表先存在，且引擎为InnoDB
- 修改风险：生产环境慎用ALTER TABLE（可能锁表）