做MySQL开发这些年面试新人最怕听到一句话就是索引我熟、事务我懂但一问他三大范式立刻支支吾吾写SQL时count(*)和count(1)用得飞起却不知道聚合函数在分组和NULL面前还藏着一堆坑。数据库约束、三大范式、聚合函数这三块恰恰是MySQL基础里最容易被跳过又最影响实战能力的内容。约束管的是数据能不能进表范式管的是表该怎么拆聚合函数管的是数据怎么汇总统计三者环环相扣。这篇把三个知识点串成一条完整的入门链路配合建表语句和统计查询案例一起讲适合刚学完MySQL增删改查、准备做课程设计或应付面试的初学者。我会把每个概念的为什么也拆开说清楚免得你死记硬背概念遇到具体表结构照样不会设计。1. 约束把数据不守规矩的苗头掐死在表结构里数据库里的数据如果不加约束就像公司没有规章制度谁都能乱来用户表里出现两个相同的手机号、订单表里挂一个不存在的用户编号、价格字段填成负数。约束就是加在表结构上的规则让数据库在写入数据时自己把关而不是靠应用层一层层if判断。1.1 主键约束一张表的法定身份证主键约束的作用是唯一标识一行记录它同时满足非空和唯一两个条件。你可以把它理解为人的身份证号一张表里不可能两个人有同一个号码也不可能有人没有号码。建表时最常见的写法是CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) );这里的AUTO_INCREMENT是自增配合主键使用保证每次插入新记录时id自动加1不需要手动指定。很多人纠结一个问题要不要用自增id做主键我的经验是绝大多数业务表都建议加一个独立的自增主键因为业务字段比如手机号、邮箱一旦作为主键将来改手机号就很麻烦而且自增id在聚簇索引中插入时顺序友好不容易产生页分裂和索引碎片。主键还有一些进阶写法值得注意-- 联合主键多个字段共同构成唯一标识 CREATE TABLE order_item ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id) );联合主键在实际业务里非常常见尤其是明细表和中间表。这里有个容易踩的坑联合主键的字段顺序会影响索引的查询效率。联合主键本身就是一个联合索引查询时如果能用到最左前缀效率就高如果只查product_id不查order_id这个索引就用不上。1.2 外键约束让表与表之间开始讲信用外键约束是表与表之间的一种引用关系。它规定子表中的外键字段值必须在父表的主键列中存在或者为空。用大白话说订单表里的user_id不能随便写必须是user表里真实存在的id。实际项目中外键的争议一直很大。互联网公司的大流量场景普遍禁用物理外键理由很直接——外键约束会强制数据库在执行插入、更新时去检查关联表这在高并发下会带来明显的性能损耗而且MySQL的外键检查在分库分表后会彻底失效。但在教学、课程设计、小型管理系统里物理外键能让数据一致性有保障写起来也省心。MySQL里创建外键的语法CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, order_no VARCHAR(50), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user(id) );外键约束还附带一些行为规则最常用的两个是ON DELETE CASCADE和ON DELETE SET NULLCASCADE删父表记录时自动删掉子表里引用它的记录。适合订单明细跟订单同生死的关系。SET NULL删父表记录时子表外键字段自动置空。适合删除部门但保留员工把部门id清空的场景。RESTRICT默认有子表记录引用时不允许删除父表记录。我在教学时建议初学者尽量手动模拟外键关系也就是逻辑外键比如在应用层先查父表再插子表。这样既能理解外键的含义又不会被数据库的物理约束卡住尤其当你以后要面对的是分布式系统时逻辑外键是必然选择。1.3 唯一、非空、默认、检查把规则写在表上而不是靠应用层除了主键和外键还有一批轻量级约束它们单独看很简单但组合起来威力很大。**唯一约束UNIQUE**保证一列或一组列的值不重复。典型场景是手机号、身份证号。要注意唯一约束允许有多个NULL值因为MySQL认为NULL ! NULL。这个特性在业务上需要特别留意比如注册时邀请码字段允许为空但隐含的业务逻辑是同一个邀请码不能被多个人使用如果有两行都是NULLMySQL不会拦。**非空约束NOT NULL**是最容易被忽略但最值得重视的约束。很多人图省事把所有列都允许NULL结果查询时到处碰壁——统计函数、字符串拼接、where判断都要额外处理NULL。我的习惯是凡是业务上必须有值的列一律NOT NULL可以没有值的列也尽量用默认值兜底。**默认约束DEFAULT**指定不填时的默认值。比如创建时间默认当前时间create_time DATETIME DEFAULT CURRENT_TIMESTAMP这个在MySQL 8.0里很方便5.7也支持。**检查约束CHECK**在MySQL 8.0.16之前是名义存在写了也不生效从8.0.16开始才开始真正校验。比如price DECIMAL(10,2) CHECK (price 0)如果你还在用5.7千万别指望CHECK帮你拦数据只能在应用层做校验。1.4 约束不是越多越好性能与维护成本的取舍我刚带项目时也犯过约束控的毛病觉得约束越全越安全结果一张用户表加了五六个唯一索引、三个外键插入效率明显变慢而且每次业务方要求修改业务规则光改约束就要拖很久。约束的正确打开方式是分级管理核心数据一致性靠主键、唯一约束、NOT NULL守住外键能不用就不用枚举范围校验比如性别、状态优先用ENUM或TINYINT加默认值复杂的业务规则比如库存不能为负、优惠券过期状态放在应用层做事务控制而不是堆在表结构里。简单说约束是数据库的第一道防线但不能是唯一一道防线。2. 三大范式从大而全到小而专的表设计进化范式是关系数据库设计的一套理论简单理解就是如何把一张大表拆成多张小表。三大范式分别是第一范式1NF、第二范式2NF、第三范式3NF。很多人觉得这些概念抽象其实它们解决的都是一类很实际的问题数据冗余、更新异常、插入异常。2.1 第一范式列不可再分拒绝复合属性第一范式的要求非常朴素每一列都必须是不可分割的原子值。说白了一个字段里不能塞多个值。反例非常典型比如你设计一个订单表字段是商品列表值写成手机x2耳机x1充电器x3。这看起来方便但当你需要统计耳机卖了多少个时就要在字符串里做切片解析痛苦至极。正确做法是拆成两张表订单表和订单明细表一个商品占一行。第一范式是所有数据库表设计的底线它不允许一个列里有张飞、关羽、刘备这种逗号分隔的多值。关系数据库是针对行和列的二维表结构不是为了存列表而设计的。在设计表时我判断是否满足1NF的标准很简单假设我要对这个字段做统计是否需要先用字符串函数拆分如果需要就不符合1NF。2.2 第二范式消除部分依赖别让非主键列看人下菜第二范式建立在第一范式基础上核心要求是非主键列必须完全依赖于主键不能只依赖联合主键的一部分。一句话部分依赖指的就是表用了联合主键但某个非主键列只看主键中的某一列就能确定。举个例子你有一张选课成绩表主键是(student_id, course_id)里面有course_name课程名、student_name学生姓名、score分数。score完全依赖于(student_id, course_id)因为分数就是某个学生某门课的成绩。student_name只依赖student_id跟你选哪门课没关系——这就是部分依赖。course_name只依赖course_id也是部分依赖。如果不拆分后果很明显同一个学生选了10门课student_name就要重复存10次数据冗余。而且如果学生还没选课他就没法被记录在表中这叫插入异常。解决方法是拆成三张表学生表、课程表、选课成绩表。这就是第二范式做的事情——把联合主键拆开让每一列都只依赖完整的业务主键。实际工作中部分依赖的识别很考验人因为很多人不会刻意去想这个字段到底依赖哪个键。我的技巧是先问自己这张表的每一行记录代表的实体是什么再想每一列描述的到底是这个实体本身还是别的实体。如果描述的是别的实体就该拆出去。2.3 第三范式消除传递依赖别让由他来变成由他的他来第三范式的要求是非主键列不能依赖于另一个非主键列。换句话说非主键列之间不能存在传递关系。举个典型例子订单表里有customer_id和customer_name还有customer_address。customer_id依赖主键order_id没问题但customer_name和customer_address其实是依赖customer_id的——它们是客户的信息不是订单的信息。这就是传递依赖order_id → customer_id → customer_address。结果是一个客户下10个订单他的姓名和地址就要重复存10次客户搬家了你又要更新10条历史订单里的地址。拆解方案是把客户信息单独拆成客户表订单表里只保留customer_id作为外键。第三范式在真实开发中经常被有意违反这就是冗余字段的来源。比如在订单表里直接冗余一个user_name换来的是查询时少关联一张表减少了join开销。这在读多写少的互联网系统里很常见。范式的价值在于指导你发现问题而不是禁止你使用冗余。关键分辨点是冗余字段是否高频改动如果频繁更新就不适合冗余。2.4 范式学完要解构实际项目中怎么妥协很多初学者学完三大范式就开始把业务表拆得七零八落什么都想满足第三范式。但生产环境里完全满足范式化的数据库往往是学术正确性能灾难。我上过的真实教训电商订单列表页老板要看用户昵称于是我把订单表和用户表分开每次查询都要join一次。用户量涨到几百万后join变慢不得不把user_name冗余回订单表用同步脚本维护一致性。说白了范式是理想模型反范式是业务妥协。设计表时先按范式拆分理清实体边界再根据查询场景做有限度的冗余。这个顺序不能颠倒否则你连实体边界都分不清上来就胡乱加冗余字段只会更乱。三大范式的另一种理解方式第一范式管列第二范式管主键和非主键的关系第三范式管非主键和非主键的关系。有了这个框架面试官怎么问都不慌。3. 聚合函数一行一行看数据太慢直接汇总才有意义约束和范式解决的是表结构怎么设计聚合函数解决的是数据怎么统计。MySQL的聚合函数就是在一组数据上进行计算并返回单一值的函数。最常见的五个是COUNT、SUM、AVG、MAX、MIN。它们也是报表统计的基础。3.1 五大聚合函数COUNT、SUM、AVG、MAX、MIN的用法与边界先看一个最简单的统计需求订单表里总共有多少单、总金额多少、平均金额多少、最大单多少、最小单多少。SELECT COUNT(*) AS order_count, SUM(total_amount) AS total_amount, AVG(total_amount) AS avg_amount, MAX(total_amount) AS max_amount, MIN(total_amount) AS min_amount FROM orders;这段SQL体现了聚合函数的基本特征多行输入单行输出。这里的边界要分清楚COUNT(*)统计行数包含NULL行。COUNT(column)统计该列非NULL值的个数。COUNT(DISTINCT column)统计该列去重后的非NULL值个数。实际开发里统计用户数我总会被问用count(*)还是count(1)。在MySQL 5.7和8.0中两者的性能差异几乎可以忽略重点是你统计的语义对不对。count(1)在优化器里会被转换成count(*)一样的效果不需要纠结。SUM和AVG在遇到NULL时也有自己的行为不参与计算。比如10行数据有2行是NULLSUM只累加8行AVG也是除以8而不是除以10。这在求平均分时尤其容易产生认知偏差必须先明确业务口径。3.2 GROUP BY分组统计的真正威力聚合函数单独用只能得到全局汇总配合GROUP BY才能体现价值。GROUP BY的作用是把行按某个字段分组然后对每组分别聚合。举个例子统计每个用户的订单总额SELECT user_id, SUM(total_amount) AS total_amount FROM orders GROUP BY user_id;再复杂一点按月统计订单量SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_count, SUM(total_amount) AS total_amount FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m);这里有个非常重要的问题SELECT列表里只能出现分组字段和聚合函数。分组字段是user_id你最多再选DATE_FORMAT后的月份但你不能去selectorder_no因为同一组里有多个order_no数据库不知道该显示哪一个。在MySQL 5.7默认配置下你select非分组字段也不会报错它会随机取一行。这个随机在数据量大的时候容易引发难以排查的Bug。MySQL 8.0默认开启ONLY_FULL_GROUP_BY会直接报错强制你遵循规范。这点后文会详细讲。分组统计还有一个延伸用法GROUP BY多个字段分组维度就变成了多个字段组合成一组。比如统计每个用户每个月的订单总额SELECT user_id, DATE_FORMAT(create_time, %Y-%m) AS month, SUM(total_amount) FROM orders GROUP BY user_id, DATE_FORMAT(create_time, %Y-%m);3.3 HAVING vs WHERE过滤顺序的隐形坑分组之后还想过滤怎么办比如只想要订单总额超过1000的用户。刚接触SQL的人最容易写错的位置是把聚合条件放在WHERE里SELECT user_id, SUM(total_amount) AS total FROM orders WHERE SUM(total_amount) 1000 GROUP BY user_id;这条SQL会报错因为WHERE是在分组前对原始行进行过滤而聚合函数SUM这时候还没开始计算where根本不知道total_amount。正确写法是使用HAVINGSELECT user_id, SUM(total_amount) AS total FROM orders GROUP BY user_id HAVING total 1000;我把WHERE和HAVING的区别总结成一句话WHERE过滤数据行HAVING过滤分组。执行逻辑上WHERE在GROUP BY之前执行HAVING在GROUP BY之后执行。实际开发中还有个性能技巧能用WHERE筛掉的数据绝不要留到HAVING里。比如统计已支付订单中每个用户的总金额应该先WHERE status paid再分组而不是先分组再HAVING status。因为分组计算是有开销的先过滤能大幅减少参与分组的数据量。3.4 NULL天生自成一派聚合函数与NULL的相处之道聚合函数遇上NULL是基础中最容易被忽视的高级话题。第一COUNT(column)会忽略NULLCOUNT()不会。假设一张表的email列有5行数据其中3行是NULLCOUNT(email)返回2COUNT()返回5。这个差异在做基数校验时非常危险比如你想查有多少用户填了邮箱就必须用COUNT(email)。第二SUM、AVG、MAX、MIN都会忽略NULL行只对非NULL值计算。AVG(NVL(val,0))和AVG(val)的结果完全不一样前者把NULL当0算后者直接不算NULL。业务上平均工资到底是只算有工资的人还是把没工资的也按0算这就是SQL写法和业务口径的关系你必须在写之前想清楚。第三NULL与比较运算的结果永远是NULL不是假也不是真。所以WHERE amount NULL永远查不到数据必须用IS NULL。这个坑在入门阶段出现的频率极高我见过不少新同事排查半天最后发现是等于NULL写成了等号。聚合函数还经常和CASE WHEN组合使用实现条件统计。比如统计订单表中的男性用户数和女性用户数SELECT COUNT(CASE WHEN gender male THEN 1 END) AS male_count, COUNT(CASE WHEN gender female THEN 1 END) AS female_count FROM users;这里没有用ELSE 0COUNT只会统计非NULL的CASE结果等于只统计满足条件的行。这种写法比多个子查询干净得多值得收藏。4. 综合案例一张订单表把约束、范式、聚合函数串起来理论学完不落地等于白学我用一个完整的电商订单场景把前面三章内容串成一条流程先按范式和约束设计表再写统计报表SQL。4.1 先设计表约束 范式落地假设我们要做一个简单的电商系统涉及的数据实体有用户、商品、订单、订单明细。按第三范式的思路四个实体拆成四张表。用户表CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL UNIQUE, nickname VARCHAR(50) NOT NULL, gender TINYINT NOT NULL DEFAULT 0 COMMENT 0未知 1男 2女, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );商品表CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL CHECK (price 0), stock INT NOT NULL DEFAULT 0 );订单主表CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL UNIQUE, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 0 COMMENT 0未支付 1已支付 2已取消, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_id (user_id), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user (id) );订单明细表CREATE TABLE order_item ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL DEFAULT 1, price DECIMAL(10,2) NOT NULL COMMENT 下单时的快照价格, CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders (id) );这里每个字段的设置都能解释出理由phone用了NOT NULL UNIQUE因为手机号是登录凭证必须唯一order_no加UNIQUE防止并发下生成重复订单号order_item.price是商品价格快照不是实时关联product表的价格。这是数据一致性设计里很重要的一点——订单一旦生成价格就不能跟着商品调价变否则财务对账会出大问题外键用于教学场景没问题生产环境考虑去掉用COMMENT给字段加注释是数据库设计的良好习惯时间长了你会回来感谢自己的。范式层面订单明细表的主键是自增id所有非主键列完全依赖于id符合第二范式订单表中没有冗余用户昵称和商品名称符合第三范式。4.2 再出报表聚合函数查询统计表设计好之后统计需求就变得非常清爽。需求一统计每个用户的订单数和总消费金额只统计已支付订单。SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM orders WHERE status 1 GROUP BY user_id ORDER BY total_spent DESC;需求二找出消费总额超过1000的用户以及他们的平均客单价。SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent, AVG(total_amount) AS avg_order_amount FROM orders WHERE status 1 GROUP BY user_id HAVING total_spent 1000 ORDER BY total_spent DESC;需求三统计销量前3的商品。此时要join明细表和商品表SELECT p.product_name, SUM(oi.quantity) AS sold_quantity FROM order_item oi JOIN product p ON oi.product_id p.id GROUP BY p.id, p.product_name ORDER BY sold_quantity DESC LIMIT 3;这个SQL里GROUP BY同时写了p.id和p.product_name是为了满足ONLY_FULL_GROUP_BY的要求——product_name是id的函数依赖按理说MySQL 8.0也允许但我习惯写全避免在5.7和8.0之间切换时遇到玄学报错。4.3 常见报错与调试思路新手做综合案例时最常碰到的几个报错我直接列出来第一Column xxx must appear in the GROUP BY clause or be used in an aggregate function。这是ONLY_FULL_GROUP_BY报错说明你select了非分组字段。解决办法要么把字段加进GROUP BY要么改成MIN(xxx)、MAX(xxx)等聚合写法。第二Invalid use of group function。一般是聚合函数写到了WHERE或ON子句里比如WHERE COUNT(*) 1。记住WHERE不认识聚合函数HAVING才认识。第三外键插入失败Cannot add or update a child row。说明你插入的子表外键值在父表里不存在。先查父表是否有对应记录或者确认外键字段是否允许NULL。调试思路有个共性原则先把条件逐步放宽比如去掉HAVING看分组结果是否正确再去掉WHERE看基础数据是否正常一层层定位问题出在过滤还是分组上。这种分层排查的思路比对着报错干猜有效得多。5. MySQL 8.0实操笔记sql_mode、版本差异与性能调优方向最后写点实操层面的经验这部分是书本上很少详细讲但你在真实环境一定会遇到的东西。5.1 ONLY_FULL_GROUP_BY 为什么让人头疼MySQL 5.7之后默认开启ONLY_FULL_GROUP_BY这种严格的SQL模式8.0里也是默认开启的。它要求SELECT列表中的非聚合字段必须全部出现在GROUP BY子句中。前文说过5.7默认配置下不检查只随机取值8.0默认检查直接报错。很多从5.6迁移到8.0的项目第一波报错往往就是这条。我建议初学者不要在配置文件里关掉它而是养成规范写SQL的习惯。因为ONLY_FULL_GROUP_BY报错本质上是在提醒你你的分组逻辑不严谨查询结果可能随机。附上查看当前sql_mode的语句SELECT sql_mode;如果确实遇到维护老系统需要临时关闭可以这样设置但我强烈不建议在正式环境做SET SESSION sql_mode (SELECT REPLACE(sql_mode, ONLY_FULL_GROUP_BY, ));5.2 5.7 vs 8.0约束与聚合函数的差异点这篇文章涉及的内容在两个大版本间有几个需注意的差异CHECK约束MySQL 5.7只解析不执行8.0.16开始真正生效。我在5.7上曾经天真地写了CHECK(gender IN (male,female))结果插入非法数据照样成功白高兴一场。窗口函数8.0引入了ROW_NUMBER()、RANK()等窗口函数做排名统计比如求每个部门工资前3名简洁很多。但窗口函数不属于聚合函数范畴建议先掌握GROUP BY基础再进阶窗口函数。隐式类型转换两个版本都存在需要注意。比如把字符类型字段和数字比较时MySQL会尝试把字符串转成数字查不到数据别怀疑索引先看类型匹配问题。如果你还在用5.7至少有两点要注意一是外键和CHECK约束不能全信逻辑校验要留在应用层二是写分页查询时尽量用ORDER BY加确定字段避免数据结果不稳定。5.3 给新手的练习路径这三块内容我建议按下面的路线练第一步建一个简单的学生选课系统包含学生表、课程表、成绩表动手实践主键、唯一约束、外键然后把成绩表拆成满足第二范式、第三范式的样子。第二步写统计SQL每个学生的平均分、最高分、总选课数每门课程的选课人数用COUNT和GROUP BY配合HAVING过滤。第三步给自己出几个真实业务问题比如统计每个学生有成绩的课程数和统计每个学生的选课数为什么结果可能不同——前者用COUNT(score)后者用COUNT(*)——把NULL语义搞清楚。第四步故意写几条错误SQL触发ONLY_FULL_GROUP_BY和HAVING的报错再根据报错信息修复比做一百道填空选择题都管用。我在教学和带新人时一直强调基础要慢工出细活。约束、范式、聚合函数这三个概念孤立看都不难难的是组合起来解决实际业务问题。把这一篇里的表和SQL亲手跑一遍再自己改一改字段和条件以后再遇到面试或真实开发里的统计需求你就不只是眼熟概念而是真的能动手写出来。