小白也能玩转的数据库进阶：用 “约束魔法“ 收拾数据

前几天帮学弟改代码，他对着屏幕哀嚎："哥，我明明给用户表设了主键，怎么还是插进两个 id=5 的用户啊？后台直接报错说数据重复！"

害，这就是没玩好 "约束" 的锅。数据库里的约束看着复杂，其实就是给数据定规矩 —— 就像学校里的校规：不许迟到、不许抄作业，数据也一样，得按规矩来才不会乱套。

今天就用最土的话，把这些 "约束魔法" 讲明白，保证看完你也能驯服调皮的数据～

🔢 自增约束：给数据装个 "自动排号机"

你有没有过这种经历？手动给数据编序号，编到 100 多号时手滑输成 "100"，结果后面全乱了 —— 我当年做学生表时就干过这蠢事，改到半夜两点。

后来才发现，数据库自带 "自动排号机"：AUTO_INCREMENT，堪称懒人福音！

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 学号自动生成，不用手动填
  name VARCHAR(50)
);

这玩意儿有多香？试试就知道：

• 插数据时不用管 id，它会自己 + 1：插第一条 id=1，第二条 id=2... 比超市取号机还靠谱
• 就算删了中间的记录（比如删了 id=3），新数据也不会补位，下一个还是 id=4（避免序号重复）
• 想 "插队" 也行：INSERT INTO students VALUES (100, '转学生')，后面的会从 101 接着排

👉 小技巧：给现有表加自增，得先确保字段是主键（不然数据库不知道给谁排号）：

ALTER TABLE students MODIFY id INT AUTO_INCREMENT;  -- 给学号开自动排号

🆔 唯一约束：给数据上 "防重名锁"

上次做用户注册功能，没给邮箱加限制，结果同一个邮箱注册了 8 个账号 —— 用户登录时直接懵了："我到底哪个账号是对的？"

这时候就得请出UNIQUE约束，相当于给数据装个 "查重雷达"，重复的一律拦在门外。

CREATE TABLE users (
  email VARCHAR(100) UNIQUE,  -- 邮箱不许重复，跟身份证似的
  nickname VARCHAR(50)
);

举个生活例子：
就像班级里不能有两个 "张三"（除非老师特别标注），UNIQUE 会盯着邮箱、手机号这些字段，只要出现重复值，就会报错："Duplicate entry"（翻译：这玩意儿已经有了，别塞了！）

👉 避坑点：给已有表加唯一约束前，先清掉重复数据！不然数据库会跟你急："里面都有俩张三了，你现在才说不让重名？"

-- 先删重复的，再加约束
DELETE FROM users WHERE email IN (
  SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
);
-- 再加唯一锁
ALTER TABLE users MODIFY email VARCHAR(100) UNIQUE;

🚫 非空约束：给数据设 "必填项"

之前做订单系统，没给收货地址加限制，结果有个用户没填地址就下单了 —— 数据库存了个 NULL，APP 上直接显示 "null"，被产品经理追着骂了一下午。

这就是NOT NULL的作用：强制某些字段必须填，就像快递单上的 "收件人" 和 "电话"，不填根本发不了货。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  address VARCHAR(200) NOT NULL  -- 地址必须填，不然下单失败！
);

偷偷说个冷知识：
如果一个字段同时满足NOT NULL和UNIQUE，数据库会偷偷把它当成 "准主键"—— 比如用户表的手机号，既不能空着，也不能重复，这不就是天然的唯一标识吗？

👉 小技巧：给已有字段加非空约束时，数据库会自动给现有 NULL 值 "填坑"：字符串填空串""，数字填 0，省得你手动改～

🛡️ 默认值约束：给数据留 "保底方案"

做电商系统时，每次上架新商品都要填 "状态"：在售、下架、预售... 手快的时候经常漏填，导致后台统计出错。

后来学乖了，给状态字段加个DEFAULT：新商品默认 "在售"，不用每次手动输，省事儿多了。

CREATE TABLE goods (
  name VARCHAR(100),
  status VARCHAR(20) DEFAULT '在售'  -- 没填状态？默认就是在售
);

这招还能玩组合技：
给 "优惠码" 字段加DEFAULT '无优惠' + UNIQUE，既避免空值重复，又不用每次手动填 —— 我做活动页时就这么干，少写了几十行代码～

ALTER TABLE orders MODIFY coupon VARCHAR(20) DEFAULT '无优惠' UNIQUE;

🔗 外键约束：给数据做 "血缘鉴定"

最开始做员工管理系统时，我犯过一个低级错误：部门表只有 "技术部" 和 "市场部"，但员工表居然出现了 "财务部" 的人 —— 统计部门人数时直接算错，被老板约谈。

这就是没加外键约束的锅：外键就像给数据办 "户口本"，员工表（孩子）的部门 id，必须能在部门表（家长）里找到对应的记录，不能乱认爹！

-- 先建"家长表"（部门表）
CREATE TABLE depts (
  dept_id INT PRIMARY KEY,  -- 部门编号（比如1=技术部，2=市场部）
  name VARCHAR(50)
);

-- 再建"孩子表"（员工表），加外键认亲
CREATE TABLE staff (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  -- 关键：员工的部门id必须在部门表里存在
  FOREIGN KEY (dept_id) REFERENCES depts(dept_id)
);

加了外键后：

• 想给员工表插个 dept_id=3？不行！部门表里没有 3 号部门（防乱认爹）
• 想删部门表的 "技术部"？得先把员工表里所有技术部的人转走（防家长跑了，孩子没人管）

👉 删外键要记三步：先查外键名（SHOW CREATE TABLE staff;），再用DROP FOREIGN KEY 外键名，别硬删～

⚙️ 数据库引擎：选对 "发动机" 才跑得快

之前朋友帮忙搭博客，用了 InnoDB 引擎，结果访问量一大就卡 —— 后来换成 MyISAM，速度直接翻倍。

数据库引擎就像汽车发动机：有的稳（适合载重要数据），有的快（适合轻量数据），得按需选。

引擎名	像什么车	特点	适合场景
InnoDB	带安全气囊的家用车	支持事务（稳），稍慢	银行转账、订单系统
MyISAM	赛车	不支持事务（快）	博客文章、商品列表

举个例子：

• 转账时必须用 InnoDB：A 转 B 100 元，得确保 A 扣钱和 B 到账同时成功（事务特性），不然钱可能凭空消失
• 博客文章用 MyISAM：查文章不用事务，快就完事了，卡一秒读者就跑了

-- 建表时指定引擎，博客表选MyISAM
CREATE TABLE articles (
  id INT PRIMARY KEY,
  content TEXT
) ENGINE=MyISAM;

🧮 聚合函数：给数据装 "计算器"

期末统计班级成绩时，手动算平均分、最高分能算到眼花 —— 其实用 SQL 的聚合函数，一行代码就搞定，比 Excel 还快。

-- 学生成绩表求统计
SELECT 
  AVG(score) AS 平均分,  -- 算平均（自动忽略NULL值，贴心）
  MAX(score) AS 最高分,  -- 找最高分
  COUNT(*) AS 参考人数   -- 算总人数
FROM exam;

我常用的两个 "组合技"：

1. 分组统计（按科目算平均分）：

-- 每个科目的平均分
SELECT subject, AVG(score) FROM exam GROUP BY subject;

1. 拼接字符串（给用户 id 加前缀）：

-- 把id=1变成"用户_1"
UPDATE users SET nickname = CONCAT('用户_', id);

🎯 实战：做个 "不翻车" 的成绩管理系统

需求：建一个班级成绩系统，要保证：

• 学生 id 自动生成（自增）
• 学生姓名必须填（非空）
• 成绩表必须关联存在的学生（外键）
• 统计各科平均分和学霸（聚合函数）

-- 1. 建学生表（自增id+非空姓名）
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL  -- 姓名必填，不然不让进系统
);

-- 2. 建成绩表（外键关联学生）
CREATE TABLE scores (
  id INT PRIMARY KEY,
  student_id INT,
  subject VARCHAR(20),
  score FLOAT,
  -- 成绩必须属于存在的学生
  FOREIGN KEY (student_id) REFERENCES students(id)
);

-- 3. 插数据（学生表id自动增，不用填）
INSERT INTO students (name) VALUES ('张三'), ('李四');
INSERT INTO scores VALUES 
(1, 1, '数学', 90),  -- 张三的数学成绩
(2, 2, '数学', 85);  -- 李四的数学成绩

-- 4. 统计各科平均分
SELECT subject, AVG(score) AS 平均分 FROM scores GROUP BY subject;

-- 5. 找数学学霸（分数高于平均分的）
SELECT * FROM scores 
WHERE subject='数学' AND score > (
  SELECT AVG(score) FROM scores WHERE subject='数学'
);

💡 避坑口诀（我踩过的坑，你别再踩）

加唯一，先查重（不然老数据重复会报错）
加非空，补默认（给现有 NULL 值找个落脚点）
外键前，主表定（先建主表再建从表，不然认不了亲）
引擎选，看事务（转账用 InnoDB，查数据用 MyISAM）

最后说句实在的：这些约束看着多，其实多用几次就熟了。我当年因为没加外键，让不存在的部门出现在报表里，被老板骂到想辞职；也因为忘了非空约束，让用户昵称显示 "null" 被产品经理怼 —— 都是踩坑踩出来的经验。