作为程序员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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 订单表 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 订单明细表 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;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 HAVING4.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 ROLLUPsql-- 按分类和状态统计商品数量并增加总计和小计行 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类型图解textINNER 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 多表关联优化原则小表驱动大表优化器通常会做但可以主动用STRAIGHT_JOIN强制驱动顺序。关联字段必须要有索引如果没有会触发多次全表扫描。避免在ON条件中使用函数或计算。六、子查询何时使用何时避免6.1 标量子查询一行一列sql-- 查询价格高于平均价格的商品 SELECT name, price FROM product WHERE price (SELECT AVG(price) FROM product);6.2 IN子查询 vs EXISTSsql-- 查询有订单的用户方式1IN SELECT * FROM user WHERE id IN (SELECT DISTINCT user_id FROM orders); -- 方式2EXISTS 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每个用户按时间排序的订单序号sqlSELECT user_id, order_no, total_amount, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY create_time) AS order_seq FROM orders;案例2计算每个用户订单金额的环比增长率sqlWITH 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(张三, zhangtest.com); -- 批量插入推荐减少事务开销 INSERT INTO user(username, email) VALUES (李四, litest.com), (王五, wangtest.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 cant 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,csql-- 不推荐 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连接不同字段可能不走索引改用UNIONLIKE %xxx索引失效避免前导通配符或用ESLIMIT m, n大偏移量扫描大量无用行使用延迟关联或游标NOT IN子查询性能差NULL陷阱改用NOT EXISTS关联表没有索引全表扫描给关联字段加索引ORDER BY RAND()全表排序换用其他随机算法大量批量操作不控制频率锁竞争主从延迟分批 睡眠10.2 执行计划分析必会sqlEXPLAIN SELECT ... -- 查看执行计划重点关注字段typeALL全表扫描最差range/ref/const好key实际使用的索引为NULL就是没用到rows估算扫描行数越小越好ExtraUsing filesort需要排序、Using temporary用了临时表通常是性能瓶颈十一、实战综合案例一个复杂报表SQL需求统计2025年1月每个分类的销售总额、销量、以及该分类下销量前三的商品。sqlWITH -- 第一步订单明细关联商品获取销售数据 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看似简单实则需要大量实践。建议多写在LeetCode、牛客网上刷SQL题推荐难度中等~困难多读看公司生产库的慢查询日志尝试优化多思考每次写SQL时想想是否能走索引、是否能减少扫描行数掌握进阶存储过程、触发器、事件、分区表等但慎用于业务逻辑