用 MySQL 商品表玩转 DQL：从基础查询到真实业务场景（实战全解）

摘要

这篇笔记不聊虚的，就用咱们手头的product表说话。很多初学者觉得select、where这些命令枯燥难记，其实它们就是电商后台每天在用的最强“检索工具”。我会把这些语法揉进真实的商品管理场景里，帮你摆脱死记硬背，做到运营提需求，你脑子里立刻能浮现出对应的 SQL。

描述：入职第一天，运营的需求来了

假设你刚接手一个电商后台，负责商品模块。运营同事凑过来提了几个看似简单的要求：

1. “看看咱们都有啥分类？”（去重DISTINCT）
2. “找找 2000～5000 块的笔记本。”（范围BETWEEN）
3. “搜下名字带‘霸’字的，做个国潮专题。”（模糊LIKE）
4. “导入的数据里有没有没分类的废品？”（空值NULL）
5. “衣服类目里，哪个价位商品最多？”（分组GROUP BY）
6. “列表太长，分个页，一次 10 条。”（分页LIMIT）
7. “按价格从高到低排下，我看下贵的。”（排序ORDER BY）

你看，所有的需求，本质都是在操作这张product表。接下来的内容，我们就把这些需求一一落地。

题解答案：核心 SQL 汇总

为了应对上述需求，这几条 SQL 覆盖了后台 90% 的查询场景。

-- 1. 商品列表（后台首页基础版）SELECTpid,pname,price,category_idFROMproduct;-- 2. 分类下拉框数据源（去重）SELECTDISTINCTcategory_idFROMproductWHEREcategory_idISNOTNULL;-- 3. 高价商品（运营关注利润款）SELECT*FROMproductWHEREpriceBETWEEN2000AND5000;-- 4. 指定分类查询（如只关心电子和服装）SELECT*FROMproductWHEREcategory_idIN('c001','c002');-- 5. 搜索框模糊查询SELECT*FROMproductWHEREpnameLIKE'%霸%';-- 6. 数据清洗：找异常数据SELECT*FROMproductWHEREcategory_idISNULL;-- 7. 分页查询（每页10条，取第1页）SELECT*FROMproductLIMIT0,10;-- 8. 排序查询（按价格降序）SELECT*FROMproductORDERBYpriceDESC;-- 9. 聚合统计（统计每个分类的商品数量）SELECTcategory_id,COUNT(*)ASnumFROMproductGROUPBYcategory_id;

题解代码深度分析

这部分我们拆解每条 SQL 背后的业务含义和避坑指南。

基础查询：SELECT *是性能杀手

-- 教学用（不推荐生产环境）SELECT*FROMproduct;-- 生产环境推荐写法SELECTpid,pname,price,category_idFROMproduct;

为啥不用 `*”？

• 性能：*会读取所有字段，万一以后表加了商品详情（大文本），查询速度会断崖式下跌。
• 网络：多查一个字段，就多占一点带宽。高并发下，省出来的带宽就是钱。
• 健壮性：表结构变了（如字段改名），*会直接报错，而指定字段至少能提示缺字段，方便排查。
• 真实场景：这叫 SQL 层面的 VO（View Object），前端需要啥，你就查啥。

DISTINCT：不只是去重

-- 场景：获取所有分类ID，用于前端筛选SELECTDISTINCTcategory_idFROMproduct;

业务逻辑：如果不去重，1 万件商品就有 1 万个c001，前端下拉框会直接崩掉。
进阶：SELECT DISTINCT price, category_id是“组合去重”。只有当“价格+分类”都相同时才去重，常用于排查同款商品。

WHERE：业务规则的翻译机

范围查询 (BETWEEN)

SELECT*FROMproductWHEREpriceBETWEEN2000AND5000;

注意：

• 包左包右：包含 2000 和 5000。如果要“大于 2000 且小于 5000”，得用price > 2000 AND price < 5000。
• 时间坑：查某一天数据时，BETWEEN '00:00:00' AND '23:59:59'可能漏掉毫秒级数据，建议用< 第二天 00:00:00。

IN的使用场景

SELECT*FROMproductWHEREcategory_idIN('c001','c002');

优于OR：可读性强，且在有索引时性能通常更好。前端多选框传来的数组，直接拼成IN语句最顺手。

NULL的判断（大坑）

-- 错误写法（永远查不到）SELECT*FROMproductWHEREcategory_id=NULL;-- 正确写法SELECT*FROMproductWHEREcategory_idISNULL;

原理：NULL代表“未知”，不是空字符串。任何值与NULL运算结果都是假。数据导入后，第一时间查NULL是数据清洗的必修课。

模糊查询LIKE：搜索的基石

SELECT*FROMproductWHEREpnameLIKE'%霸%';

通配符：

• %：任意长度字符。
• _：单个字符。
  性能红线：
• LIKE '霸%'（前缀匹配）：能走索引。
• LIKE '%霸%'（全模糊）：全表扫描，千万级数据直接卡死。解决方案：Elasticsearch 或限制只能前缀搜索。

排序 (ORDER BY) 与 分页 (LIMIT)

-- 按价格降序，取前10SELECT*FROMproductORDERBYpriceDESCLIMIT10;-- 分页：第2页，每页5条SELECT*FROMproductLIMIT5,5;

深度分页问题：LIMIT 100000, 10意味着数据库要先扫 10 万条数据，再取 10 条，越往后翻越慢。
执行顺序：先WHERE过滤，再ORDER BY排序，最后LIMIT截取。

聚合与分组 (GROUP BY)

-- 统计各类商品数量SELECTcategory_id,COUNT(*)ASproduct_countFROMproductGROUPBYcategory_id;

逻辑：先按category_id分堆，再数每堆的数量。
HAVING过滤：WHERE过滤行，HAVING过滤分组后的结果。例如：HAVING COUNT(*) > 3（只显示商品数大于 3 的分类）。

别名 (AS)：说“人话”

SELECTpidAS'商品编号',price*0.9AS'折后价'FROMproduct;

必要性：导出 Excel 给运营或财务，price不如“售价”直观；计算字段必须有别名，否则列名就是乱码。

更多实战场景与 SQL 示例

为了让你更通透，补充 20 个高频业务场景：

场景一：价格调整（算术运算）
需求：双十一打 9 折。

SELECTpname,priceAS'原价',price*0.9AS'双十一价'FROMproduct;

场景二：排除特定分类
需求：不看服装类。

SELECT*FROMproductWHEREcategory_id!='c002';

场景三：多条件联合（精准）
需求：服装类且价格大于 500。

SELECT*FROMproductWHEREcategory_id='c002'ANDprice>500;

场景四：多条件任选
需求：电子产品或百元以下商品。

SELECT*FROMproductWHEREcategory_id='c001'ORprice<100;

场景五：复杂逻辑组合
需求：（服装且贵）或（化妆品）。

SELECT*FROMproductWHERE(category_id='c002'ANDprice>500)ORcategory_id='c003';

场景六：区间之外
需求：排除 100-500 元区间。

SELECT*FROMproductWHEREpriceNOTBETWEEN100AND500;

场景七：指定品牌
需求：只看“联想、海尔、雷神”。

SELECT*FROMproductWHEREpnameIN('联想','海尔','雷神');

场景八：二字商品名
需求：找“劲霸”、“面霸”。

SELECT*FROMproductWHEREpnameLIKE'__';

场景九：多字段排序
需求：先按分类，再按价格降序。

SELECT*FROMproductORDERBYcategory_idASC,priceDESC;

场景十：总商品数
需求：SKU 总量。

SELECTCOUNT(*)AStotal_countFROMproduct;

场景十一：有效商品数
需求：排除脏数据。

SELECTCOUNT(*)FROMproductWHEREcategory_idISNOTNULL;

场景十二：分类均价
需求：评估品类档次。

SELECTcategory_id,AVG(price)ASavg_priceFROMproductGROUPBYcategory_id;

场景十三：最贵商品
需求：镇店之宝。

SELECT*FROMproductORDERBYpriceDESCLIMIT1;

场景十四：最便宜商品
需求：引流款。

SELECT*FROMproductORDERBYpriceASCLIMIT1;

场景十五：排除特定前缀
需求：不看“香”字开头商品。

SELECT*FROMproductWHEREpnameNOTLIKE'香%';

场景十六：空字符串判断
需求：区分NULL和空字符串。

SELECT*FROMproductWHEREcategory_id='';

场景十七：排除无效分类
需求：既不是NULL也不是空。

SELECT*FROMproductWHEREcategory_idISNOTNULLANDcategory_id!='';

场景十八：标准分页写法
需求：MySQL 8.0+ 推荐。

SELECT*FROMproductLIMIT10OFFSET0;

场景十九：查看表结构
需求：新人接手代码。

DESCproduct;SHOWCREATETABLEproduct;

场景二十：空结果测试
需求：测试接口健壮性。

SELECT*FROMproductWHEREpname='根本不存在的商品名';

示例测试及结果分析

测试一：分类统计

SELECTcategory_id,COUNT(*)ASnumFROMproductGROUPBYcategory_id;

结果：c002(服装) 有 5 个，NULL有 1 个（苹果18PM）。
解读：运营一眼看出服装品类最丰富，且存在一条待处理脏数据。

测试二：模糊查询+排序

SELECTpname,priceFROMproductWHEREpnameLIKE'%霸%'ORDERBYpriceDESC;

结果：劲霸(2000) -> 面霸(10)。
解读：验证了 SQL 执行顺序——先WHERE过滤出两条，再ORDER BY排序。

时间复杂度与性能分析

• 全表扫描 (O(n))：SELECT *、LIKE '%x%'。数据翻倍，时间翻倍。
• 索引查找 (O(log n))：WHERE category_id = 'c002'（有索引时）。数据翻 10 倍，耗时微增。
• 排序 (Filesort)：ORDER BY无索引时，需在内存/磁盘排序，极慢。
• 分组 (Temporary Table)：GROUP BY类似排序，耗内存。

结论：小数据量随便写；大数据量下，SELECT *和%xxx%是性能杀手。

空间复杂度

• 临时表：DISTINCT、GROUP BY会产生临时表，大数据集会撑爆内存。
• 网络缓冲：SELECT *占用大量网络带宽。
• 排序缓冲：ORDER BY依赖排序缓冲区，过小则写磁盘，速度骤降。

总结

这一整套 SQL，就是电商后台的“商品查询引擎”。

• 查什么（SELECT）：拒绝*。
• 怎么筛（WHERE）：业务逻辑的核心。
• 怎么搜（LIKE）：警惕%开头。
• 怎么排（ORDER BY）：列表页的灵魂。
• 怎么统（GROUP BY）：运营报表的基础。
• 怎么清（NULL）：数据质量的底线。

当你能把运营的一句“帮我找下…”瞬间翻译成高效的 SQL，你就不再是语法搬运工，而是真正的后端开发。写 SQL 就像说话，想清楚再说，数据库自然听话。