当前位置： 首页 > news >正文

手把手教你写出优雅高效的SQL：从入门到精通

news 2026/4/28 18:35:12

作为程序员，SQL几乎是每天都要打交道的语言。但你真的会写SQL吗？很多人能写出来，却写不好——性能差、可读性差、难以维护。本文将带你从零开始，系统掌握SQL编写的最佳实践，涵盖基础语法、高级查询、性能优化、常见陷阱等内容，建议收藏慢慢看。

一、SQL基础：建表与数据准备

1.1 一个好的表结构设计

先创建一个电商系统的典型表结构，后续所有示例都基于此：

sql

-- 用户表 CREATE TABLE `user` ( `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID', `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', `email` VARCHAR(100) NOT NULL COMMENT '邮箱', `phone` CHAR(11) COMMENT '手机号', `gender` TINYINT DEFAULT 0 COMMENT '0未知 1男 2女', `register_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间', `status` TINYINT DEFAULT 1 COMMENT '1正常 2冻结', INDEX idx_email (`email`), INDEX idx_register_time (`register_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'; -- 商品表 CREATE TABLE `product` ( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(200) NOT NULL COMMENT '商品名称', `category_id` INT COMMENT '分类ID', `price` DECIMAL(10,2) NOT NULL COMMENT '售价', `stock` INT NOT NULL DEFAULT 0 COMMENT '库存', `status` TINYINT DEFAULT 1 COMMENT '1上架 0下架', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_category (`category_id`), INDEX idx_price (`price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 订单表 CREATE TABLE `orders` ( `id` BIGINT PRIMARY KEY AUTO_INCREMENT, `order_no` VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号', `user_id` INT NOT NULL COMMENT '用户ID', `total_amount` DECIMAL(10,2) NOT NULL COMMENT '总金额', `status` TINYINT DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消', `pay_time` DATETIME COMMENT '支付时间', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (`user_id`), INDEX idx_status (`status`), INDEX idx_create_time (`create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 订单明细表 CREATE TABLE `order_item` ( `id` BIGINT PRIMARY KEY AUTO_INCREMENT, `order_id` BIGINT NOT NULL, `product_id` INT NOT NULL, `quantity` INT NOT NULL, `price` DECIMAL(10,2) NOT NULL COMMENT '下单时快照价格', INDEX idx_order_id (`order_id`), INDEX idx_product_id (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.2 插入测试数据（快速生成百万级数据）

sql

-- 插入100万用户（使用存储过程） DELIMITER $$ CREATE PROCEDURE insert_users(IN n INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= n DO INSERT INTO user(username, email, phone) VALUES( CONCAT('user_', i), CONCAT('user_', i, '@test.com'), LPAD(FLOOR(RAND()*10000000000), 11, '1') ); SET i = i + 1; IF i % 10000 = 0 THEN COMMIT; END IF; END WHILE; END$$ DELIMITER ; CALL insert_users(1000000); -- 慎用，根据自己环境 -- 插入商品 INSERT INTO product(name, category_id, price, stock) VALUES ('iPhone 15 Pro', 1, 7999.00, 100), ('华为 Mate 60 Pro', 1, 6999.00, 50), ('联想ThinkPad X1', 2, 12999.00, 30), ('罗技鼠标', 3, 89.00, 500); -- ... 可自行扩充

二、SELECT查询基本功

2.1 查询所有列 vs 指定列

sql

-- 不推荐：SELECT * （尤其在生产环境） SELECT * FROM user WHERE id = 1; -- 推荐：只取需要的列 SELECT id, username, email FROM user WHERE id = 1;

理由：SELECT *会返回所有字段，浪费网络带宽；如果表结构变更（加列），应用层可能出错；同时无法利用覆盖索引优化。

2.2 WHERE条件中的常用操作符

操作符示例说明
= , != , <>age = 18不等于推荐<>
IN / NOT INstatus IN (1,2,3)适合枚举值少的情况
BETWEENprice BETWEEN 100 AND 200闭区间
LIKEname LIKE '张%'注意前导通配符会全表扫描
IS NULL / IS NOT NULLphone IS NULL索引可用
EXISTS / NOT EXISTSEXISTS (SELECT 1 ...)用于关联子查询

2.3 排序与分页

sql

-- 排序：ASC升序（默认），DESC降序 SELECT id, username, register_time FROM user WHERE status = 1 ORDER BY register_time DESC, id ASC LIMIT 20; -- 分页的坑：LIMIT 100000, 20 会先扫描10万行再丢弃 -- 优化技巧：使用延迟关联 SELECT u.id, u.username, u.email FROM user u INNER JOIN ( SELECT id FROM user WHERE status = 1 ORDER BY id LIMIT 100000, 20 ) AS tmp ON u.id = tmp.id;

核心：利用子查询先走覆盖索引拿到分页的ID，再回表取整行，避免扫描大量行。

三、函数的使用：让计算在数据库完成

3.1 聚合函数

sql

-- 常用聚合：COUNT, SUM, AVG, MAX, MIN SELECT COUNT(*) AS total_orders, -- 总订单数（包含NULL行） COUNT(pay_time) AS paid_orders, -- 已支付订单数（不包含NULL） SUM(total_amount) AS sum_amount, AVG(total_amount) AS avg_amount, MAX(total_amount) AS max_amount, MIN(total_amount) AS min_amount FROM orders WHERE status >= 1; -- 已支付的订单

注意：COUNT(*)vsCOUNT(列)：COUNT(*)包含NULL行，COUNT(列)忽略NULL。

3.2 字符串函数

sql

-- 拼接：CONCAT SELECT CONCAT(username, '(', email, ')') AS user_info FROM user; -- 截取：SUBSTRING SELECT SUBSTRING(order_no, -6) AS last_six FROM orders; -- 后6位 -- 替换：REPLACE SELECT REPLACE(phone, SUBSTR(phone,4,4), '****') AS masked_phone FROM user; -- 长度：LENGTH（字节） vs CHAR_LENGTH（字符） SELECT username, CHAR_LENGTH(username) FROM user; -- 大小写转换：UPPER / LOWER SELECT UPPER(email) FROM user;

3.3 日期时间函数（重点）

sql

-- 获取当前时间 SELECT NOW(), CURDATE(), CURTIME(); -- 提取部分 SELECT YEAR(register_time), MONTH(register_time), DAY(register_time) FROM user; -- 日期加减 SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 7天后 SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 1个月前 -- 日期差 SELECT DATEDIFF('2025-12-31', CURDATE()); -- 距离年底还有几天 -- 按天分组统计 SELECT DATE(create_time) AS day, COUNT(*) FROM orders GROUP BY DATE(create_time);

性能警告：不要在WHERE条件中对日期列使用函数，如WHERE YEAR(create_time) = 2025，会导致索引失效。应改为范围查询：WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31'。

四、分组与聚合：GROUP BY + HAVING

4.1 基础分组

sql

-- 统计每个用户的订单总额 SELECT user_id, SUM(total_amount) AS total_spent, COUNT(*) AS order_count FROM orders WHERE status = 1 -- 已支付 GROUP BY user_id ORDER BY total_spent DESC LIMIT 10;

4.2 HAVING：对分组结果过滤

WHERE在分组前过滤行，HAVING在分组后过滤聚合结果。

sql

-- 找出下单次数超过10次的用户 SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id HAVING cnt > 10; -- 错误示例：不能在WHERE中使用聚合函数 -- WHERE COUNT(*) > 10 ❌

4.3 GROUP BY + WITH ROLLUP

sql

-- 按分类和状态统计商品数量，并增加总计和小计行 SELECT COALESCE(category_id, '总计') AS category, COALESCE(status, '全部') AS status, COUNT(*) AS cnt FROM product GROUP BY category_id, status WITH ROLLUP;

输出会包含多行汇总：每个分组的小计，以及最后的总计。

五、多表查询：JOIN的精髓

5.1 五种JOIN类型图解

text

INNER JOIN: 只返回匹配的行 LEFT JOIN: 返回左表全部 + 右表匹配的行（无匹配则NULL） RIGHT JOIN: 返回右表全部 + 左表匹配的行 FULL JOIN: MySQL不支持，可用LEFT JOIN UNION RIGHT JOIN模拟 CROSS JOIN: 笛卡尔积（谨慎使用）

5.2 实战示例

sql

-- 查询每个订单的详情（关联用户和商品） SELECT o.order_no, u.username, p.name AS product_name, oi.quantity, oi.price FROM orders o INNER JOIN user u ON o.user_id = u.id INNER JOIN order_item oi ON o.id = oi.order_id INNER JOIN product p ON oi.product_id = p.id WHERE o.status = 1 LIMIT 100;

5.3 LEFT JOIN的常见误区

需求：查询所有用户及其订单数量（没有订单的用户也要显示0）。

sql

-- 错误写法（INNER JOIN会过滤掉无订单用户） SELECT u.id, COUNT(o.id) AS order_count FROM user u INNER JOIN orders o ON u.id = o.user_id GROUP BY u.id; -- 正确写法 SELECT u.id, u.username, COUNT(o.id) AS order_count FROM user u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;

陷阱：COUNT(o.id)比COUNT(*)好，因为NULL不计入。

5.4 多表关联优化原则

  1. 小表驱动大表：优化器通常会做，但可以主动用STRAIGHT_JOIN强制驱动顺序。

  2. 关联字段必须要有索引：如果没有，会触发多次全表扫描。

  3. 避免在ON条件中使用函数或计算。

六、子查询：何时使用，何时避免

6.1 标量子查询（一行一列）

sql

-- 查询价格高于平均价格的商品 SELECT name, price FROM product WHERE price > (SELECT AVG(price) FROM product);

6.2 IN子查询 vs EXISTS

sql

-- 查询有订单的用户（方式1：IN） SELECT * FROM user WHERE id IN (SELECT DISTINCT user_id FROM orders); -- 方式2：EXISTS SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

性能对比：

  • 当子查询结果集很小，IN 可能更快（子查询执行一次，物化结果集）。

  • 当子查询结果集大且外查询较小，EXISTS 更优（关联子查询，逐行判断）。

  • MySQL 8.0以上优化器两者差异不大。

6.3 相关子查询

sql

-- 查询每个分类中价格最高的商品 SELECT p1.* FROM product p1 WHERE p1.price = ( SELECT MAX(p2.price) FROM product p2 WHERE p2.category_id = p1.category_id );

注意：相关子查询会对外查询的每一行执行一次内查询，性能较差，可改写为JOIN或窗口函数。

sql

-- 优化版本（使用窗口函数，MySQL 8.0+） SELECT * FROM ( SELECT *, RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS rn FROM product ) t WHERE rn = 1;

七、窗口函数：SQL中的数据分析神器（MySQL 8.0+）

窗口函数在不合并行的情况下进行聚合计算，非常强大。

7.1 语法结构

sql

函数() OVER( PARTITION BY 分组列 ORDER BY 排序列 ROWS/RANGE 窗口范围 )

7.2 常用窗口函数

函数作用
ROW_NUMBER()行号，从1开始
RANK()排名，相同值会并列且跳号
DENSE_RANK()连续排名，不跳号
LAG(expr, offset)访问当前行之前的第offset行
LEAD(expr, offset)访问当前行之后的第offset行
SUM() OVER()累积求和

7.3 实战案例

案例1：每个用户按时间排序的订单序号

sql

SELECT user_id, order_no, total_amount, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY create_time) AS order_seq FROM orders;

案例2：计算每个用户订单金额的环比增长率

sql

WITH order_stats AS ( SELECT user_id, DATE_FORMAT(create_time, '%Y-%m') AS month, SUM(total_amount) AS month_amount, LAG(SUM(total_amount)) OVER(PARTITION BY user_id ORDER BY DATE_FORMAT(create_time, '%Y-%m')) AS prev_amount FROM orders WHERE status = 1 GROUP BY user_id, month ) SELECT user_id, month, month_amount, COALESCE((month_amount - prev_amount)/prev_amount * 100, 0) AS growth_rate FROM order_stats;

八、DML语句：写操作的注意事项

8.1 INSERT的几种形式

sql

-- 单行插入 INSERT INTO user(username, email) VALUES('张三', 'zhang@test.com'); -- 批量插入（推荐，减少事务开销） INSERT INTO user(username, email) VALUES ('李四', 'li@test.com'), ('王五', 'wang@test.com'); -- 插入查询结果 INSERT INTO user_archive (id, username, email) SELECT id, username, email FROM user WHERE register_time < '2023-01-01';

8.2 UPDATE优化

sql

-- 错误：在UPDATE子查询中引用同一个表（MySQL限制） UPDATE user SET status = 2 WHERE id IN (SELECT user_id FROM orders WHERE status = 0 GROUP BY user_id HAVING COUNT(*) > 10); -- 报错：You can't specify target table 'user' for update in FROM clause -- 正确做法：使用多表UPDATE UPDATE user u INNER JOIN ( SELECT user_id, COUNT(*) AS cnt FROM orders WHERE status = 0 GROUP BY user_id HAVING cnt > 10 ) t ON u.id = t.user_id SET u.status = 2; -- 或者使用临时表

8.3 DELETE的温和方式

生产环境不建议直接DELETE，尤其是大表，容易造成锁表、主从延迟。推荐：

  • 逻辑删除：增加is_deleted字段，UPDATE标记。

  • 分批删除：每次删除1000行，循环。

sql

-- 分批删除示例 DELETE FROM logs WHERE create_time < '2024-01-01' LIMIT 1000; -- 重复执行直到影响行数为0

九、SQL书写规范与可读性

9.1 命名规范

  • 表名：小写+下划线（如user_order）

  • 字段名：小写+下划线（如register_time）

  • 别名：有意义且简短，避免a,b,c

sql

-- 不推荐 SELECT u.n, o.t FROM user u, orders o WHERE u.i = o.u_i; -- 推荐 SELECT user.username, orders.total_amount FROM user INNER JOIN orders ON user.id = orders.user_id;

9.2 格式化风格

sql

-- 推荐：关键字大写，每行一个主要子句 SELECT user_id, COUNT(*) AS order_count FROM orders WHERE status = 1 AND create_time >= '2025-01-01' GROUP BY user_id HAVING order_count > 5 ORDER BY order_count DESC LIMIT 20;

9.3 注释

sql

-- 单行注释 /* 多行注释 解释复杂逻辑 */

十、性能优化：写SQL时必须刻在脑子里

10.1 十大避坑指南

坏习惯后果改进
SELECT *浪费IO，无法覆盖索引只取需要的列
对索引列使用函数索引失效改成范围查询
隐式类型转换索引失效保持类型一致，phone = 13800138000可能失效
OR连接不同字段可能不走索引改用UNION
LIKE '%xxx'索引失效避免前导通配符，或用ES
LIMIT m, n大偏移量扫描大量无用行使用延迟关联或游标
NOT IN子查询性能差，NULL陷阱改用NOT EXISTS
关联表没有索引全表扫描给关联字段加索引
ORDER BY RAND()全表排序换用其他随机算法
大量批量操作不控制频率锁竞争，主从延迟分批 + 睡眠

10.2 执行计划分析（必会）

sql

EXPLAIN SELECT ... -- 查看执行计划

重点关注字段：

  • type：ALL（全表扫描）最差，range/ref/const好

  • key：实际使用的索引，为NULL就是没用到

  • rows：估算扫描行数，越小越好

  • Extra：Using filesort（需要排序）、Using temporary（用了临时表）通常是性能瓶颈

十一、实战综合案例：一个复杂报表SQL

需求：统计2025年1月，每个分类的销售总额、销量、以及该分类下销量前三的商品。

sql

WITH -- 第一步：订单明细关联商品，获取销售数据 sale_detail AS ( SELECT p.category_id, p.id AS product_id, p.name AS product_name, oi.quantity, oi.price * oi.quantity AS sale_amount FROM order_item oi INNER JOIN product p ON oi.product_id = p.id INNER JOIN orders o ON oi.order_id = o.id WHERE o.status = 1 -- 已支付 AND o.pay_time >= '2025-01-01' AND o.pay_time < '2025-02-01' ), -- 第二步：分类汇总 category_summary AS ( SELECT category_id, SUM(sale_amount) AS total_amount, SUM(quantity) AS total_quantity FROM sale_detail GROUP BY category_id ), -- 第三步：商品排名（每个分类内按销售额降序） product_rank AS ( SELECT category_id, product_id, product_name, SUM(sale_amount) AS product_sales, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY SUM(sale_amount) DESC) AS rn FROM sale_detail GROUP BY category_id, product_id, product_name ) -- 最终输出 SELECT cs.category_id, cs.total_amount, cs.total_quantity, pr.product_name AS top1_product, pr.product_sales AS top1_sales, (SELECT product_name FROM product_rank WHERE category_id = cs.category_id AND rn = 2) AS top2_product, (SELECT product_name FROM product_rank WHERE category_id = cs.category_id AND rn = 3) AS top3_product FROM category_summary cs LEFT JOIN product_rank pr ON cs.category_id = pr.category_id AND pr.rn = 1 ORDER BY cs.total_amount DESC;

这个案例综合运用了CTE、聚合、窗口函数、子查询，是高级SQL的典型写法。

十二、写在最后：持续精进

SQL看似简单，实则需要大量实践。建议：

  1. 多写：在LeetCode、牛客网上刷SQL题（推荐难度：中等~困难）

  2. 多读：看公司生产库的慢查询日志，尝试优化

  3. 多思考：每次写SQL时，想想是否能走索引、是否能减少扫描行数

  4. 掌握进阶：存储过程、触发器、事件、分区表等，但慎用于业务逻辑

http://www.jsqmd.com/news/715149/

相关文章：

  • SpringBoot项目里，Mybatis-Plus的主键策略（IdType）到底怎么选？AUTO、INPUT还是NONE？
  • Hacklock未来展望：AI时代下图案锁安全测试的发展趋势
  • rope集成VSCode与PyCharm：在IDE中实现智能重构
  • 2026中国钛合金棒厂家TOP4权威排名：医用钛棒/TC4钛棒首选供应商 - 深度智识库
  • （Linux）进程控制
  • LeetCode 深度优先搜索（DFS）题解
  • 猫抓浏览器扩展完全指南：免费开源资源嗅探工具终极教程
  • 从感受野计算到代码实现：用Python可视化带你彻底搞懂空洞卷积的等效卷积核
  • 3个关键步骤：实现浏览器媒体资源智能捕获的完整方案
  • axilite + ap_memory约束数组-突破单口RAM限制
  • AI赋能的个性化国际教育崛起：2026深圳国际学校革命性择校指南 - 深度智识库
  • 三步掌握SakuraFrp：内网穿透终极实战指南
  • Kodi IPTV Simple完整指南：3步搭建专业级家庭电视直播系统
  • 瑞芯微(EASY EAI)RV1126B ROS2安装
  • 你的宽带真的支持IPv6吗？手把手教你用手机热点+MobaXterm远程办公
  • 避坑指南：在Ruoyi-Vue中实现登录拦截与密码重置，我踩了这三个Token管理的坑
  • 2026年数控钣金公司实力排行/钣金，钣金加工，钣金件加工，精密不锈钢钣金加工 - 品牌策略师
  • Amulet-Map-Editor完整功能解析：从世界编辑到格式转换
  • Yew物联网：MQTT和WebSocket通信的终极指南
  • 终极Python多线程与多进程编程指南：从入门到实践的完整路径
  • 如何利用Composer二进制包支持高效分发PHP扩展和工具
  • Smithbox终极指南：如何轻松修改你最喜欢的魂系游戏
  • 一键安装HS2-HF_Patch：解锁Honey Select 2完整游戏体验的终极指南
  • OpCore Simplify：3步完成黑苹果OpenCore配置的完整指南
  • 2026年，还想要入局大模型领域的学习和工作，还来得及吗？红利期还在吗？
  • Hostinger主机稳定吗 - 麦麦唛
  • AI领域重大周记：超级学习者获11亿美元融资、生成式AI监管落地、大模型与具身智能双线突破
  • 专业级VR视频转换工具：将沉浸式3D内容转为2D格式的技术解析与实践指南
  • 告别‘XXX is not a type’：一份Qt Quick项目的.qrc文件配置保姆级指南（含CMake/QMake）
  • DIY一个低成本气象站：STM32F103C8T6核心板+OLED显示风速风向温湿度