如约数科

排序与分页：ORDER BY与LIMIT

引言在实际开发中，我们经常需要对查询结果进行排序和分页：商品列表按价格从低到高排序文章列表按发布时间倒序显示用户列表分页展示，每页20条排行榜按得分从高到低排序这些需求都需要通过 ORDER BY 和 LIMIT 来实现。为什么排序和分页如此重要？用户体验：有序的数据更符合用户的阅读习惯性能优化：分页可以减少数据传输量，提升响应速度业务需求：排行榜、Top N查询等场景必不可少数据管理：便于数据的浏览和检索本文将深入讲解排序和分页的原理、语法、性能优化技巧，以及如何解决深分页问题。一、ORDER BY 排序基础 1.1 基本语法 SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...; 执行顺序： FROM：确定要查询的表 WHERE：过滤出符合条件的行 ORDER BY：对结果进行排序 SELECT：选择要返回的列排序方向： ASC：升序（Ascending），从小到大，默认值 DESC：降序（Descending），从大到小 1.2 准备测试数据继续使用上一篇的商品表，并添加一些新数据： -- 补充更多测试数据 INSERT INTO products (name, category, price, stock, created_at, description) VALUES ('iPhone 14', '手机', 5999.00, 100, '2024-09-01', 'Apple上代旗舰'), ('小米13', '手机', 3299.00, 150, '2024-03-15', '小米上代旗舰'), ('华为P60', '手机', 4999.00, 80, '2024-04-20', '华为影像旗舰'), ('戴尔XPS', '电脑', 9999.00, 25, '2024-05-10', '戴尔高端笔记本'), ('Surface Pro', '平板', 6999.00, 40, '2024-06-15', '微软二合一平板'); 二、单字段排序 2.1 数值字段排序 -- 按价格升序排列（从低到高） SELECT name, price FROM products ORDER BY price ASC; -- 等价写法（ASC可省略） SELECT name, price FROM products ORDER BY price; 结果： ...

聚合函数：COUNT、SUM、AVG、MAX、MIN

引言在数据分析和报表统计中,我们经常需要对数据进行汇总计算：统计商品总数、总销售额计算平均价格、平均评分找出最高价、最低价统计用户数、订单数这些需求都需要通过聚合函数（Aggregate Functions）来实现。为什么聚合函数如此重要？数据分析的基础：90%的报表都需要聚合统计业务指标计算：GMV、客单价、转化率等核心指标性能优化关键：数据库层面的聚合比应用层效率高决策支持：为业务决策提供数据依据本文将系统讲解MySQL的五大聚合函数，以及它们在实际开发中的应用。一、聚合函数基础 1.1 什么是聚合函数？聚合函数对一组值执行计算，返回单个值。五大聚合函数： COUNT()：计数 SUM()：求和 AVG()：平均值 MAX()：最大值 MIN()：最小值 1.2 基本语法 SELECT aggregate_function(column_name) FROM table_name WHERE condition; 1.3 聚合函数的特点输入多行，输出一行：对多条记录进行计算，返回一个结果忽略NULL值：除了 COUNT(*) 外，其他聚合函数都忽略NULL 可与GROUP BY结合：对分组后的每组数据分别聚合不能在WHERE中使用：WHERE是在聚合之前执行的 1.4 准备测试数据 -- 创建订单表 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_name VARCHAR(100), price DECIMAL(10, 2), quantity INT, order_date DATE, status VARCHAR(20) ); -- 插入测试数据 INSERT INTO orders (user_id, product_name, price, quantity, order_date, status) VALUES (1, 'iPhone 15 Pro', 7999.00, 1, '2024-11-01', 'completed'), (1, 'AirPods Pro', 1999.00, 1, '2024-11-02', 'completed'), (2, '华为Mate 60', 6999.00, 1, '2024-11-03', 'completed'), (2, '小米14', 3999.00, 2, '2024-11-04', 'completed'), (3, 'MacBook Pro', 14999.00, 1, '2024-11-05', 'pending'), (3, 'iPad Air', 4799.00, 1, '2024-11-06', 'completed'), (4, '小米13', 3299.00, 1, '2024-11-07', 'cancelled'), (5, '联想ThinkPad', NULL, 1, '2024-11-08', 'completed'), -- 价格为NULL (6, 'AirPods Pro', 1999.00, 2, '2024-11-09', 'completed'), (7, '索尼WH-1000XM5', 2499.00, 1, '2024-11-10', 'completed'); 二、COUNT() - 计数函数 2.1 COUNT(*) - 统计总行数统计所有行数，包括NULL值的行。 ...

分组查询：GROUP BY与HAVING

引言在上一篇我们学习了聚合函数（COUNT、SUM、AVG、MAX、MIN），它们能对整个结果集进行统计。但在实际开发中，我们经常需要分组统计：统计每个类别的商品数量和平均价格分析每个用户的订单总额和订单数计算每个月的销售额和订单量按地区统计客户数量和消费金额这些需求都需要通过 GROUP BY 来实现——先分组，再对每组数据进行聚合计算。为什么GROUP BY如此重要？多维度分析：从不同角度分析数据（按时间、地区、类别等）报表统计的核心：几乎所有报表都需要分组统计业务洞察：发现不同群体的差异和规律决策支持：为精细化运营提供数据依据本文将系统讲解GROUP BY的原理、语法、使用技巧，以及HAVING子句的应用。一、GROUP BY 基础 1.1 什么是分组查询？分组查询将数据按照某个或某些列的值进行分组，然后对每个组分别进行聚合计算。执行流程： FROM：确定要查询的表 WHERE：过滤行（在分组之前） GROUP BY：将数据分组 HAVING：过滤分组（在分组之后） SELECT：选择要返回的列和聚合结果 ORDER BY：对结果排序 LIMIT：限制返回行数 1.2 基本语法 SELECT column1, aggregate_function(column2) FROM table_name WHERE condition GROUP BY column1 HAVING group_condition ORDER BY column1; 1.3 准备测试数据继续使用上一篇的订单表，并补充一些数据： -- 补充更多测试数据 INSERT INTO orders (user_id, product_name, price, quantity, order_date, status) VALUES (1, '小米13', 3299.00, 1, '2024-11-11', 'completed'), (2, 'iPad Air', 4799.00, 1, '2024-11-12', 'completed'), (3, 'AirPods Pro', 1999.00, 1, '2024-11-13', 'completed'), (4, '华为Mate 60', 6999.00, 1, '2024-11-14', 'completed'), (5, '小米14', 3999.00, 1, '2024-11-15', 'pending'), (6, 'MacBook Pro', 14999.00, 1, '2024-11-16', 'completed'), (7, 'iPhone 15 Pro', 7999.00, 1, '2024-11-17', 'cancelled'); 二、单字段分组 2.1 基础分组统计 -- 统计每个用户的订单数量 SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id; 结果： ...

多表连接：INNER JOIN、LEFT JOIN、RIGHT JOIN

引言实际开发中，数据通常分散在多个表中。要获取完整信息，需要将多个表关联起来查询。本文讲解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; 结果： ...

子查询：嵌套查询与相关子查询

引言子查询（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会返回空结果。 ...

联合查询：UNION与UNION ALL

引言 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: (无) 建议： ...

窗口函数：ROW_NUMBER、RANK、DENSE_RANK

引言窗口函数（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; 结果： ...

常用字符串函数与日期函数

引言 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 常用格式符： ...

条件表达式：CASE WHEN与IF

引言在查询中经常需要根据不同条件返回不同结果。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进阶）

视图：虚拟表的创建与使用

引言视图（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 可更新视图满足以下条件的视图可以更新： ...