Mysqls

引言 MySQL的InnoDB引擎使用B+树作为索引的数据结构。理解B+树是掌握索引优化的基础。 一、从二叉树到B+树的演进 1.1 二叉查找树（BST） 10 / \ 5 15 / \ / \ 3 7 12 20 问题：可能退化为链表，查询O(n) 1.2 平衡二叉树（AVL） 通过旋转保持平衡，查询O(log n)。 问题： 每个节点只存1个元素 树高度大：100万数据需要20层 磁盘IO次数多（每层一次IO） 1.3 B树 [10, 20, 30] / | | \ [1,5] [12,15] [22,25] [35,40] 特点： 多路：每个节点存多个元素 所有节点都存数据 降低树高度 问题： 非叶子节点也存数据，占用空间 范围查询需要中序遍历 1.4 B+树 非叶子节点（只存索引）： [10, 20, 30] / | | \ / | | \ 叶子节点（存数据+索引）： [1,5,8] → [10,12,15] → [20,22,25] → [30,35,40] ↑ 双向链表 ↑ 特点： ...

引言 索引是数据库性能优化的关键。理解索引的本质，才能正确使用索引。 一、没有索引的查询 1.1 全表扫描 -- 查询id=1000的用户（无索引） SELECT * FROM users WHERE id = 1000; 执行过程： 从第1行开始 逐行检查id是否等于1000 找到后返回 如果表有100万行，最坏情况需要扫描100万行 时间复杂度：O(n) 1.2 问题 查询慢（全表扫描） 资源消耗大（CPU、磁盘IO） 并发性能差 二、索引的本质 2.1 从二分查找说起 # 在有序数组中查找 def binary_search(arr, target): left, right = 0, len(arr) - 1 while left <= right: mid = (left + right) // 2 if arr[mid] == target: return mid elif arr[mid] < target: left = mid + 1 else: right = mid - 1 return -1 时间复杂度：O(log n) 关键：数据有序 + 快速定位 2.2 索引的原理 索引是一种排序的数据结构，存储列值和行位置的映射。 索引结构（简化示例）： +-------+-----------+ | id值 | 行位置 | +-------+-----------+ | 1 | 0x1000 | | 10 | 0x1050 | | 100 | 0x1100 | | 1000 | 0x2000 | <-- 通过索引快速定位 +-------+-----------+ 查询过程： ...

引言 本文通过电商订单系统的实战案例，综合运用前面学到的SQL知识，实现各种复杂的业务查询。 一、表结构设计 1.1 核心表 -- 用户表 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100), phone VARCHAR(20), register_date DATE, level INT DEFAULT 1 -- 用户等级 ); -- 商品表 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, category VARCHAR(50), price DECIMAL(10,2), stock INT DEFAULT 0, sales INT DEFAULT 0 ); -- 订单表 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(50) UNIQUE, user_id INT, total_amount DECIMAL(10,2), status VARCHAR(20), -- pending, paid, shipped, completed, cancelled created_at DATETIME, paid_at DATETIME, FOREIGN KEY (user_id) REFERENCES users(id) ); -- 订单明细表 CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT, product_id INT, quantity INT, price DECIMAL(10,2), -- 下单时价格 FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) ); 二、基础查询实战 2.1 用户订单查询 -- 查询用户的所有订单 SELECT u.username, o.order_no, o.total_amount, o.status, DATE_FORMAT(o.created_at, '%Y-%m-%d %H:%i') AS order_time FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.id = 1 ORDER BY o.created_at DESC; 2.2 订单详情查询 -- 查询订单明细（包含商品信息） SELECT o.order_no, p.name AS product_name, oi.quantity, oi.price, oi.quantity * oi.price AS subtotal FROM orders o INNER JOIN order_items oi ON o.id = oi.order_id INNER JOIN products p ON oi.product_id = p.id WHERE o.order_no = 'ORD20241121000001'; 三、统计分析实战 3.1 用户消费统计 -- 统计每个用户的消费情况 SELECT u.id, u.username, u.level, COUNT(o.id) AS order_count, IFNULL(SUM(o.total_amount), 0) AS total_spent, ROUND(AVG(o.total_amount), 2) AS avg_order_value, MAX(o.created_at) AS last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.level ORDER BY total_spent DESC; 3.2 商品销售排行 -- 销量Top 10商品 SELECT p.name, p.category, SUM(oi.quantity) AS total_sales, SUM(oi.quantity * oi.price) AS total_revenue FROM products p INNER JOIN order_items oi ON p.id = oi.product_id INNER JOIN orders o ON oi.order_id = o.id WHERE o.status = 'completed' GROUP BY p.id, p.name, p.category ORDER BY total_sales DESC LIMIT 10; 3.3 按月销售统计 -- 每月销售额趋势 SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS customer_count, SUM(total_amount) AS total_sales, ROUND(AVG(total_amount), 2) AS avg_order_value FROM orders WHERE status = 'completed' AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH) GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY month; 四、高级查询实战 4.1 用户购买行为分析 -- 分析用户首次购买、最近购买、购买频次 SELECT u.username, u.register_date, MIN(o.created_at) AS first_order_date, MAX(o.created_at) AS last_order_date, COUNT(o.id) AS order_count, TIMESTAMPDIFF(DAY, u.register_date, MIN(o.created_at)) AS days_to_first_order, TIMESTAMPDIFF(DAY, MIN(o.created_at), MAX(o.created_at)) AS active_days FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.register_date HAVING order_count > 0; 4.2 复购率分析 -- 计算复购率（购买2次及以上的用户占比） SELECT COUNT(DISTINCT user_id) AS total_customers, COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) AS repeat_customers, ROUND(COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) AS repeat_rate FROM ( SELECT user_id, COUNT(*) AS order_count FROM orders WHERE status = 'completed' GROUP BY user_id ) t; 4.3 客户分层（RFM模型） -- RFM分析：Recency（最近购买）、Frequency（购买频次）、Monetary（消费金额） SELECT user_id, username, DATEDIFF(NOW(), last_order_date) AS recency, order_count AS frequency, total_amount AS monetary, CASE WHEN DATEDIFF(NOW(), last_order_date) <= 30 AND order_count >= 5 AND total_amount >= 10000 THEN 'VIP客户' WHEN DATEDIFF(NOW(), last_order_date) <= 90 AND order_count >= 3 THEN '活跃客户' WHEN DATEDIFF(NOW(), last_order_date) > 180 THEN '流失客户' ELSE '普通客户' END AS customer_segment FROM ( SELECT u.id AS user_id, u.username, MAX(o.created_at) AS last_order_date, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username ) t ORDER BY monetary DESC; 五、复杂业务场景 5.1 购物车转化率 -- 统计下单未支付的订单（购物车） SELECT COUNT(*) AS cart_count, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count, ROUND(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS conversion_rate FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY); 5.2 商品关联分析 -- 查找经常一起购买的商品 SELECT p1.name AS product1, p2.name AS product2, COUNT(*) AS co_purchase_count FROM order_items oi1 INNER JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id INNER JOIN products p1 ON oi1.product_id = p1.id INNER JOIN products p2 ON oi2.product_id = p2.id GROUP BY p1.id, p1.name, p2.id, p2.name ORDER BY co_purchase_count DESC LIMIT 10; 5.3 库存预警 -- 库存不足且销售较好的商品 SELECT name, category, stock, sales, CASE WHEN stock = 0 THEN '缺货' WHEN stock < sales * 0.1 THEN '严重不足' WHEN stock < sales * 0.3 THEN '库存紧张' ELSE '正常' END AS stock_status FROM products WHERE stock < sales * 0.3 ORDER BY stock_status, sales DESC; 六、性能优化实战 6.1 创建索引 -- 为常用查询字段创建索引 CREATE INDEX idx_user_id ON orders(user_id); CREATE INDEX idx_status ON orders(status); CREATE INDEX idx_created_at ON orders(created_at); CREATE INDEX idx_order_id ON order_items(order_id); CREATE INDEX idx_product_id ON order_items(product_id); -- 联合索引 CREATE INDEX idx_status_user_id ON orders(status, user_id); CREATE INDEX idx_status_created_at ON orders(status, created_at); 6.2 创建视图简化查询 -- 用户订单统计视图 CREATE VIEW v_user_order_stats AS SELECT u.id AS user_id, u.username, u.level, COUNT(o.id) AS order_count, IFNULL(SUM(o.total_amount), 0) AS total_spent, MAX(o.created_at) AS last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.level; -- 使用视图 SELECT * FROM v_user_order_stats WHERE total_spent > 10000; 七、总结 知识点回顾 本案例综合运用了： ...

引言 视图（View）是虚拟表，本质是存储的SELECT语句。可以像表一样查询，但不实际存储数据。 一、视图基础 1.1 什么是视图？ 视图是一个虚拟表，由查询结果组成。 特点： 不存储数据（只存储定义） 基于基础表动态生成 可以像表一样查询 -- 创建视图 CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition; -- 查询视图 SELECT * FROM view_name; 1.2 视图的作用 简化复杂查询：封装复杂SQL 提高安全性：隐藏敏感字段 逻辑数据独立：修改表结构不影响应用 权限控制：只授予视图权限 二、创建视图 2.1 基础语法 CREATE [OR REPLACE] VIEW view_name [(column_list)] AS select_statement [WITH CHECK OPTION]; 2.2 简单视图 -- 创建用户信息视图（隐藏敏感字段） CREATE VIEW v_user_info AS SELECT id, name, email, created_at FROM users; -- 查询视图 SELECT * FROM v_user_info; 2.3 复杂视图 -- 用户订单统计视图 CREATE VIEW v_user_order_stats AS SELECT u.id, u.name, COUNT(o.id) AS order_count, IFNULL(SUM(o.amount), 0) AS total_amount, ROUND(AVG(o.amount), 2) AS avg_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name; -- 使用视图 SELECT * FROM v_user_order_stats WHERE order_count > 5; 2.4 带条件的视图 -- 只显示有效商品 CREATE VIEW v_active_products AS SELECT id, name, price, stock FROM products WHERE status = 1 AND stock > 0; 三、查看和管理视图 3.1 查看视图列表 -- 查看所有视图 SHOW FULL TABLES WHERE Table_type = 'VIEW'; -- 查看视图定义 SHOW CREATE VIEW v_user_info; 3.2 修改视图 -- 方式1：CREATE OR REPLACE CREATE OR REPLACE VIEW v_user_info AS SELECT id, name, email, phone, created_at FROM users; -- 方式2：ALTER VIEW ALTER VIEW v_user_info AS SELECT id, name, email, phone, created_at FROM users; 3.3 删除视图 DROP VIEW IF EXISTS v_user_info; 四、视图的更新 4.1 可更新视图 满足以下条件的视图可以更新： ...

引言 在查询中经常需要根据不同条件返回不同结果。MySQL提供了CASE WHEN和IF等条件表达式来实现。 一、CASE WHEN 表达式 1.1 简单CASE表达式 -- 语法 CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default_result END -- 示例：订单状态转换 SELECT order_id, CASE status WHEN 1 THEN '待支付' WHEN 2 THEN '已支付' WHEN 3 THEN '已发货' WHEN 4 THEN '已完成' ELSE '未知' END AS status_text FROM orders; 1.2 搜索CASE表达式（推荐） -- 语法 CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END -- 示例：价格分级 SELECT name, price, CASE WHEN price < 2000 THEN '低价' WHEN price BETWEEN 2000 AND 5000 THEN '中价' WHEN price > 5000 THEN '高价' ELSE '未知' END AS price_level FROM products; 1.3 嵌套CASE SELECT name, CASE WHEN stock = 0 THEN '缺货' WHEN stock > 0 THEN CASE WHEN stock < 10 THEN '库存紧张' WHEN stock < 50 THEN '库存正常' ELSE '库存充足' END END AS stock_status FROM products; 二、IF() 函数 2.1 基本用法 -- IF(condition, value_if_true, value_if_false) SELECT name, price, IF(price > 5000, '高价', '普通价') AS price_tag FROM products; -- 多条件判断（嵌套IF） SELECT name, IF(stock > 0, IF(stock > 50, '充足', '正常'), '缺货' ) AS stock_status FROM products; 2.2 IF vs CASE WHEN -- IF：适合简单的二元判断 SELECT IF(score >= 60, '及格', '不及格') FROM students; -- CASE WHEN：适合多条件判断 SELECT CASE WHEN score >= 90 THEN '优秀' WHEN score >= 80 THEN '良好' WHEN score >= 60 THEN '及格' ELSE '不及格' END AS grade FROM students; 三、IFNULL() / COALESCE() 3.1 IFNULL() - 处理NULL -- IFNULL(expr, default_value) SELECT name, IFNULL(phone, '未提供') AS phone FROM users; -- 计算时避免NULL SELECT name, price * IFNULL(discount, 1) AS final_price FROM products; 3.2 COALESCE() - 返回第一个非NULL值 -- COALESCE(value1, value2, value3, ...) SELECT name, COALESCE(mobile, phone, email, '无联系方式') AS contact FROM users; 四、NULLIF() - 返回NULL -- NULLIF(expr1, expr2)：如果相等返回NULL，否则返回expr1 SELECT NULLIF(10, 10); -- NULL SELECT NULLIF(10, 20); -- 10 -- 避免除零错误 SELECT sales / NULLIF(days, 0) AS avg_daily_sales FROM stats; 五、实战案例 案例1：分组统计 -- 统计各价格区间的商品数 SELECT SUM(CASE WHEN price < 2000 THEN 1 ELSE 0 END) AS low_price_count, SUM(CASE WHEN price BETWEEN 2000 AND 5000 THEN 1 ELSE 0 END) AS mid_price_count, SUM(CASE WHEN price > 5000 THEN 1 ELSE 0 END) AS high_price_count FROM products; 案例2：数据透视 -- 按月统计各状态订单数 SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count, SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count FROM orders GROUP BY DATE_FORMAT(created_at, '%Y-%m'); 案例3：动态排序 -- 根据参数动态排序 SET @sort_field = 'price'; SELECT * FROM products ORDER BY CASE @sort_field WHEN 'price' THEN price WHEN 'sales' THEN sales WHEN 'stock' THEN stock END DESC; 案例4：数据清洗 -- 清洗性别数据 UPDATE users SET gender = CASE WHEN gender IN ('male', 'M', '男', '1') THEN '男' WHEN gender IN ('female', 'F', '女', '0') THEN '女' ELSE '未知' END; 案例5：行列转换 -- 将多行转为多列 SELECT user_id, MAX(CASE WHEN subject = '数学' THEN score END) AS math_score, MAX(CASE WHEN subject = '语文' THEN score END) AS chinese_score, MAX(CASE WHEN subject = '英语' THEN score END) AS english_score FROM scores GROUP BY user_id; 六、性能优化 6.1 索引失效 -- ❌ 差：在索引列上使用CASE，索引失效 SELECT * FROM products WHERE CASE WHEN category = '手机' THEN price > 3000 ELSE price > 5000 END; -- ✅ 好：拆分条件 SELECT * FROM products WHERE (category = '手机' AND price > 3000) OR (category != '手机' AND price > 5000); 6.2 避免重复计算 -- ❌ 差：CASE重复计算 SELECT CASE WHEN price * 0.8 > 5000 THEN '高价' ELSE '普通' END AS tag1, CASE WHEN price * 0.8 > 3000 THEN '贵' ELSE '便宜' END AS tag2 FROM products; -- ✅ 好：先计算再判断 SELECT CASE WHEN discount_price > 5000 THEN '高价' ELSE '普通' END AS tag1, CASE WHEN discount_price > 3000 THEN '贵' ELSE '便宜' END AS tag2 FROM ( SELECT *, price * 0.8 AS discount_price FROM products ) t; 七、总结 核心要点 CASE WHEN：多条件判断，功能强大 简单CASE：匹配固定值 搜索CASE：复杂条件判断 IF()：二元判断，简洁易读 IFNULL() / COALESCE()：处理NULL值 NULLIF()：避免除零错误 性能：避免在索引列上使用条件表达式 记忆口诀 条件表达式两大将，CASE WHEN IF各擅长， CASE多条件分级判，IF二元选择强。 IFNULL处理空值好，COALESCE多值找， NULLIF避免除零错，实战案例记心上。 本文字数：约2,600字 难度等级：⭐⭐（SQL进阶）

引言 MySQL提供了丰富的内置函数来处理字符串和日期。掌握这些函数能大幅提升开发效率。 一、字符串函数 1.1 CONCAT() / CONCAT_WS() - 拼接 -- CONCAT：拼接字符串 SELECT CONCAT('Hello', ' ', 'World'); -- Hello World -- CONCAT_WS：指定分隔符拼接 SELECT CONCAT_WS('-', '2024', '11', '21'); -- 2024-11-21 -- 实战：拼接姓名 SELECT CONCAT(last_name, first_name) AS full_name FROM users; -- 处理NULL SELECT CONCAT('Hello', NULL); -- NULL SELECT CONCAT_WS(',', 'A', NULL, 'C'); -- A,C（忽略NULL） 1.2 SUBSTRING() / SUBSTR() - 截取 -- SUBSTRING(str, pos, len) SELECT SUBSTRING('Hello World', 1, 5); -- Hello（从位置1开始，长度5） SELECT SUBSTRING('Hello World', 7); -- World（从位置7到结尾） SELECT SUBSTRING('Hello World', -5); -- World（从右边第5个） -- 实战：提取手机号后4位 SELECT SUBSTRING(phone, -4) FROM users; 1.3 LENGTH() / CHAR_LENGTH() - 长度 -- LENGTH：字节长度 SELECT LENGTH('Hello'); -- 5 SELECT LENGTH('你好'); -- 6（UTF-8，每个汉字3字节） -- CHAR_LENGTH：字符长度 SELECT CHAR_LENGTH('Hello'); -- 5 SELECT CHAR_LENGTH('你好'); -- 2 -- 实战：过滤长度 SELECT * FROM products WHERE CHAR_LENGTH(name) > 10; 1.4 UPPER() / LOWER() - 大小写 SELECT UPPER('hello'); -- HELLO SELECT LOWER('WORLD'); -- world -- 实战：不区分大小写查询 SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; 1.5 TRIM() / LTRIM() / RTRIM() - 去空格 SELECT TRIM(' Hello '); -- 'Hello' SELECT LTRIM(' Hello '); -- 'Hello ' SELECT RTRIM(' Hello '); -- ' Hello' SELECT TRIM('x' FROM 'xxxHelloxxx'); -- 'Hello' 1.6 REPLACE() - 替换 SELECT REPLACE('Hello World', 'World', 'MySQL'); -- Hello MySQL -- 实战：隐藏手机号中间4位 SELECT CONCAT( SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, -4) ) AS masked_phone FROM users; 1.7 INSTR() / LOCATE() - 查找位置 SELECT INSTR('Hello World', 'World'); -- 7 SELECT LOCATE('o', 'Hello World'); -- 5 SELECT LOCATE('o', 'Hello World', 6); -- 8（从第6个位置开始找） 1.8 LEFT() / RIGHT() - 左右截取 SELECT LEFT('Hello World', 5); -- Hello SELECT RIGHT('Hello World', 5); -- World 二、日期时间函数 2.1 NOW() / CURDATE() / CURTIME() SELECT NOW(); -- 2024-11-21 19:00:00（当前日期时间） SELECT CURDATE(); -- 2024-11-21（当前日期） SELECT CURTIME(); -- 19:00:00（当前时间） SELECT SYSDATE(); -- 2024-11-21 19:00:00（系统时间） 2.2 DATE_FORMAT() - 格式化 -- DATE_FORMAT(date, format) SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2024-11-21 SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); -- 2024年11月21日 SELECT DATE_FORMAT(NOW(), '%H:%i:%s'); -- 19:00:00 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-11-21 19:00:00 SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y'); -- Thursday, November 21, 2024 常用格式符： ...

引言 窗口函数（Window Functions）是MySQL 8.0引入的强大分析功能，可以在不改变行数的情况下进行复杂的计算和排序。 一、窗口函数基础 1.1 基本语法 function_name() OVER ( [PARTITION BY column] [ORDER BY column] [frame_specification] ) 核心概念： PARTITION BY：分组（类似GROUP BY，但不合并行） ORDER BY：排序 frame_specification：窗口框架（可选） 1.2 与GROUP BY的区别 -- GROUP BY：合并行 SELECT category, COUNT(*) FROM products GROUP BY category; -- 结果：3行 -- 窗口函数：保留所有行 SELECT name, category, COUNT(*) OVER (PARTITION BY category) AS category_count FROM products; -- 结果：10行（每行都显示） 二、ROW_NUMBER() - 行号 为每行分配唯一的序号。 2.1 基础用法 -- 为所有商品编号 SELECT name, price, ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num FROM products; 结果： ...

引言 UNION用于合并多个SELECT语句的结果集，适用于需要从不同表或不同条件查询结果合并的场景。 一、UNION基础 1.1 基本语法 SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2; 要求： 列数必须相同 列的数据类型要兼容 默认去重 1.2 示例 -- 合并2024年和2025年的订单 SELECT order_id, amount, '2024' AS year FROM orders_2024 UNION SELECT order_id, amount, '2025' AS year FROM orders_2025; 二、UNION vs UNION ALL 2.1 UNION - 去重合并 SELECT name FROM products WHERE category = '手机' UNION SELECT name FROM products WHERE price > 5000; 特点： 自动去重 性能较差（需要排序去重） 2.2 UNION ALL - 保留重复 SELECT name FROM products WHERE category = '手机' UNION ALL SELECT name FROM products WHERE price > 5000; 特点： 保留所有记录（包括重复） 性能好（不需要去重） 2.3 性能对比 -- UNION：去重需要额外排序，慢 EXPLAIN SELECT id FROM orders_2024 UNION SELECT id FROM orders_2025; -- Extra: Using temporary -- UNION ALL：直接合并，快 EXPLAIN SELECT id FROM orders_2024 UNION ALL SELECT id FROM orders_2025; -- Extra: (无) 建议： ...

引言 子查询（Subquery）是嵌套在其他查询中的SELECT语句，用于实现复杂的查询逻辑。 一、子查询分类 1.1 按返回结果分类 标量子查询：返回单个值（1行1列） 列子查询：返回一列多行 行子查询：返回一行多列 表子查询：返回多行多列 1.2 按执行方式分类 非相关子查询：独立执行，与外层查询无关 相关子查询：依赖外层查询，每行都执行一次 二、标量子查询 返回单个值，可用于比较运算。 -- 查询价格高于平均价格的商品 SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products); -- 查询销量最高的商品 SELECT name, sales FROM products WHERE sales = (SELECT MAX(sales) FROM products); 三、列子查询 返回一列多行，通常配合IN、ANY、ALL使用。 3.1 IN / NOT IN -- 查询有订单的用户 SELECT name FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders); -- 查询没有订单的用户 SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL); ⚠️ NOT IN的陷阱：子查询包含NULL会返回空结果。 ...

引言 实际开发中，数据通常分散在多个表中。要获取完整信息，需要将多个表关联起来查询。本文讲解MySQL的多表连接。 一、连接的本质：笛卡尔积 1.1 笛卡尔积 -- 创建测试表 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2) ); INSERT INTO users VALUES (1, '张三'), (2, '李四'), (3, '王五'); INSERT INTO orders VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150); -- 笛卡尔积：3 * 3 = 9 条记录 SELECT * FROM users, orders; 结果：所有可能的组合（9条）。 1.2 加上连接条件 -- 只保留有意义的组合 SELECT * FROM users, orders WHERE users.id = orders.user_id; 这就是连接的本质：笛卡尔积 + 过滤条件。 二、INNER JOIN - 内连接 2.1 基本语法 SELECT columns FROM table1 INNER JOIN table2 ON table1.key = table2.key; 2.2 示例 -- 查询用户及其订单信息 SELECT u.name, o.id AS order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id; 结果： ...