核心内容摘要
男生和女生在一起拆拆拆轮滑很痛大全_2
核心摘要如果说基础查询是“捡起地上的苹果”那么高级查询就是“设计一套自动化收割系统”。
本章是 SQL 学习的分水岭。
我们将深入研究聚合统计的底层差异COUNT(*)到底慢不慢、分组陷阱ONLY_FULL_GROUP_BY报错的根源、多表连接的本质Nested-Loop Join 算法、以及子查询与 JOIN 的性能博弈。
掌握本章你将有能力处理 95% 以上的复杂业务报表和数据分析需求。
环境准备为了演示多表连接我们需要构建更真实的电商数据模型categories(分类),products(商品),orders(订单),order_items(订单明细)。
0 高级数据环境构建为了让 Join 和 Group By 的演示有血有肉我们先执行以下 SQL 初始化环境。
USEshop_biz;--
分类表DROPTABLEIFEXISTScategories;CREATETABLEcategories(cat_idINTPRIMARYKEYAUTO_INCREMENT,cat_nameVARCHAR(
NOTNULL,parent_idINTDEFAULT0COMMENT父分类ID, 0表示顶级分类)CHARSETutf8;INSERTINTOcategories(cat_id,cat_name,parent_id)VALUES(1,电子产品,
,(2,服装,
,(3,手机,
,(4,电脑,
,(5,男装,
,(6,女装,
;--
确保商品表有分类ID (如果
没加这里加上)-- 我们先清理旧 products 表结构重新建立关联关系DROPTABLEIFEXISTSproducts;CREATETABLEproducts(product_idINTPRIMARYKEYAUTO_INCREMENT,cat_idINTNOTNULLDEFAULT0,product_nameVARCHAR(
,priceDECIMAL(10,
,stockINT,statusTINYINTDEFAULT
CHARSETutf8;INSERTINTOproducts(cat_id,product_name,price,stock,status)VALUES(3,iPhone 14,
5
00,100,
,(3,Xiaomi 13,
3
00,200,
,(4,MacBook Pro,
1
00,50,
,(4,Dell XPS,
8
00,30,
,(5,Levi牛仔裤,
5
00,500,
,(99,未知分类商品,
90,100,
;-- 故意弄个不存在的分类ID测试 Left Join--
订单主表DROPTABLEIFEXISTSorders;CREATETABLEorders(order_idINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULL,total_amountDECIMAL(10,
,created_atDATETIMEDEFAULTCURRENT_TIMESTAMP)CHARSETutf8;INSERTINTOorders(order_id,user_id,total_amount,created_at)VALUES(1,101,
5
00,
10:00:
,(2,101,
1
00,
11:00:
,-- 买了一个 MacBook 和一条裤子(3,102,
3
00,
12:00:
;--
订单明细表DROPTABLEIFEXISTSorder_items;CREATETABLEorder_items(item_idINTPRIMARYKEYAUTO_INCREMENT,order_idINTNOTNULL,product_idINTNOTNULL,quantityINTNOTNULL,unit_priceDECIMAL(10,
NOTNULL)CHARSETutf8;INSERTINTOorder_items(order_id,product_id,quantity,unit_price)VALUES(1,1,1,
5999.
,-- 订单1: iPhone 14(2,3,1,
12999.
,-- 订单2: MacBook(2,5,1,
599.
,-- 订单2: 牛仔裤(3,2,1,
3999.
;-- 订单3: Xiaomi
1 聚合函数 (Aggregation) —— 数据的宏观视角聚合函数的作用是将多行数据“压缩”成一行统计信息。
6.
1 常用聚合函数COUNT(): 计数SUM(): 求和AVG(): 平均值MAX(): 最大值MIN(): 最小值-- 统计商品总数、平均价格、最高价、最低价SELECTCOUNT(*)AStotal_products,AVG(price)ASavg_price,MAX(price)ASmax_price,MIN(price)ASmin_price,SUM(stock)AStotal_stockFROMproducts;
6.
2 深度辨析COUNT(*)vsCOUNT(
vsCOUNT(col)这是面试和实战中最高频的问题之一。
COUNT(column):行为统计该列不为 NULL的行数。
坑点如果你想统计行数却用了COUNT(parent_id)那么parent_id为 NULL 的行会被漏掉。
COUNT(*):行为统计总行数包含 NULL 值。
底层原理MySQL 优化器对COUNT(*)做了专门优化。
在 MyISAM 引擎中行数是直接存在磁盘上的O(
时间读取。
在 InnoDB 中虽然需要扫描全表或遍历最小的二级索引树但它是统计行数的标准写法性能最好。
COUNT(
:行为与COUNT(*)效果完全一致。
性能在 InnoDB 中COUNT(*)和COUNT(
没有任何性能区别优化器会把它们变成一样的执行计划。
结论统计行数请无脑使用COUNT(*)。
2 分组查询 (GROUP BY) —— 数据的切片分析
6.
1 基础分组场景统计每个分类下有多少个商品以及平均价格。
SELECTcat_id,COUNT(*)ASproduct_count,AVG(price)ASavg_priceFROMproductsGROUPBYcat_id;
6.
2HAVINGvsWHERE—— 过滤的时机WHERE: 在分组之前过滤。
对象是行。
HAVING: 在分组之后过滤。
对象是组。
场景查询商品数量超过 1 个的分类且只统计价格大于 1000 的商品。
SELECTcat_id,COUNT(*)AScount,AVG(price)ASavg_priceFROMproductsWHEREprice1000-- 第一步先剔除便宜货 (过滤行)GROUPBYcat_id-- 第二步按分类分组HAVINGcount1;-- 第三步只保留数量大于1的组 (过滤组)
6.
3 严格模式ONLY_FULL_GROUP_BY(MySQL
7 默认开启)错误示范-- 报错Select list is not in GROUP BY clause...SELECTcat_id,product_name,COUNT(*)FROMproductsGROUPBYcat_id;原因解析按照cat_id分组后比如 cat_id3 (手机) 这一组里有 “iPhone” 和 “Xiaomi” 两行。
SQL 引擎问“大哥你让我输出product_name但我这组里有两个名字我到底输出哪一个”这就产生了歧义。
规则在GROUP BY模式下SELECT后面的列要么出现在 GROUP BY 子句中要么必须被包裹在聚合函数如 MAX, MIN中。
修正-- 合法我想看这个分类下最贵的那个商品的名字虽然逻辑上可能需要子查询才更严谨SELECTcat_id,MAX(product_name),COUNT(*)FROMproductsGROUPBYcat_id;-- 或者使用 GROUP_CONCAT 把所有名字连起来SELECTcat_id,GROUP_CONCAT(product_name)FROMproductsGROUPBYcat_id;
3 多表连接查询 (Joins) —— 关系型数据库的灵魂没有 Join关系型数据库就只是 Excel。
Join 的本质是集合运算。
6.
1 内连接 (INNER JOIN)定义只返回两个表中连接字段匹配的行。
交集场景查询所有商品及其对应的分类名称。
SELECTp.product_name,c.cat_name,p.priceFROMproducts pINNERJOINcategories cONp.cat_idc.cat_id;注意products表里那个cat_id99的商品未知分类因为在categories表找不到对应 ID会被丢弃。
6.
2 左外连接 (LEFT JOIN) —— 最常用的连接定义返回左表的所有行如果右表没有匹配则右表字段填NULL。
场景查询所有商品及其分类即使没有分类也要显示商品。
SELECTp.product_name,c.cat_name,p.priceFROMproducts pLEFTJOINcategories cONp.cat_idc.cat_id;结果cat_id99的商品会显示出来但cat_name为NULL。
性能 Tips在 Left Join 中左表是驱动表。
尽量让左表的数据量小或过滤后小这样嵌套循环的次数少。
6.
3 右外连接 (RIGHT JOIN)与 Left Join 相反右表是主表。
实战建议一般不使用 RIGHT JOIN。
因为阅读习惯是从左到右混用 Left 和 Right 会让逻辑极其混乱。
统一用 Left Join 即可。
6.
4 全连接 (FULL JOIN)MySQL不支持标准的FULL OUTER JOIN语法。
如果需要模拟左表有右表有两边都有需要使用UNION-- 模拟 Full JoinSELECT*FROMt1LEFTJOINt2ONt
idt
idUNIONSELECT*FROMt1RIGHTJOINt2ONt
idt
id;
6.
5 笛卡尔积 (Cartesian Product) —— 性能灾难如果你忘记写ON条件-- 危险SELECT*FROMproducts,categories;结果行数 商品数 × 分类数。
如果是 1万商品 × 1千分类 1千万行数据。
服务器直接爆炸。
6.
6 自连接 (Self Join) —— 处理层级关系场景查询“手机”分类的父分类是谁categories 表自己连自己SELECTchild.cat_nameAS子分类,parent.cat_nameAS父分类FROMcategories childLEFTJOINcategories parentONchild.parent_idparent.cat_idWHEREchild.cat_name手机;
4 子查询 (Subqueries) —— 嵌套的艺术子查询就是把一个查询的结果当作另一个查询的条件或数据源。
6.
1 标量子查询 (Scalar Subquery)子查询结果只有一行一列一个值。
场景查询价格高于平均价的商品。
-- 分步思考--
算平均价: SELECT AVG(price) FROM products; - 假设是 5000--
查商品: SELECT * FROM products WHERE price 5000;-- 合并SELECT*FROMproductsWHEREprice(SELECTAVG(price)FROMproducts);
6.
2 列子查询 (IN / NOT IN)子查询结果是一列多行。
场景查询所有有下单记录的用户信息假设 user 表在另一个地方这里演示用 orders 表查。
SELECT*FROMusersWHEREuser_idIN(SELECTDISTINCTuser_idFROMorders);性能警示IN子查询在 MySQL 早期版本
5之前优化很差。
在
7 中MySQL 会自动优化为EXISTS或SEMI JOIN性能已有很大改观。
但处理大数据量时仍建议改写为JOIN。
优化为 JOINSELECTDISTINCTu.*FROMusers uINNERJOINorders oONu.user_ido.user_id;
6.
3EXISTSvsINEXISTS并不关心子查询返回的具体内容只关心有没有。
-- 查询下过单的用户SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_idu.user_id);选型原则小表驱动大表原则。
如果users表很小orders表很大千万级用EXISTS。
因为EXISTS会遍历users每拿一个 ID 去orders利用索引查一下速度很快。
如果users表很大orders表很小用IN。
因为IN会先把orders查出来放到内存里再让users去匹配。
6.
4 FROM 子句中的子查询 (Derived Table)把子查询结果当成一张临时表来用。
场景查询每个分类下价格最高的商品。
这很难直接用 GROUP BY 查出来因为 GROUP BY 只能查出最高价格查不出那个价格对应的商品名。
SELECTp.*FROMproducts pINNERJOIN(-- 这是一个临时表算出每个分类的最高价SELECTcat_id,MAX(price)ASmax_priceFROMproductsGROUPBYcat_id)AStmpONp.cat_idtmp.cat_idANDp.pricetmp.max_price;
5 联合查询 (UNION)将两个查询结果集垂直合并。
6.
1UNIONvsUNION ALLUNION合并后去重。
需要排序和比较慢。
UNION ALL合并后不去重。
直接追加快。
实战建议除非你明确需要去重否则永远优先使用UNION ALL。
-- 场景把 products 表和 deleted_products 表的数据展示在一起SELECTproduct_id,product_nameFROMproductsUNIONALLSELECTproduct_id,product_nameFROMdeleted_products;
6 综合实战电商报表大杀器让我们写一个复杂的 SQL检验本章的学习成果。
需求统计 2023 年 1 月份每个用户的消费总金额、订单数并按消费金额倒序排列只显示消费超过 5000 元的用户且显示该用户的最新一笔订单时间。
分析数据源orders表。
过滤条件created_at在
。
分组按user_id。
聚合SUM(total_amount),COUNT(*),MAX(created_at)。
分组后过滤 (HAVING)总金额 5000。
排序ORDER BY总金额 DESC。
SQL 实现SELECTuser_id,COUNT(*)ASorder_count,SUM(total_amount)AStotal_spend,MAX(created_at)ASlast_order_timeFROMordersWHEREcreated_at
00:00:00ANDcreated_at
00:00:00-- 推荐用范围查询代替 MONTH() 函数利于索引GROUPBYuser_idHAVINGtotal_spend5000ORDERBYtotal_spendDESC;如果你能毫无压力地写出并理解上面这条 SQL恭喜你你已经跨过了 SQL 开发者的中级门槛。
下一章我们将解锁 SQL 的“魔法工具箱”常用函数与操作符让你的数据处理更加得心应手。