这份《SQL基础语法查询手册》是我从零开始整理SQL学习笔记时的第一份成型产物。当初学SQL最大的困惑不是不会写而是不知道怎么写才算“对”——是选子查询还是JOIN是先过滤再分组还是先分完组再过滤差一步结果就完全不一样。手册的定位也很简单不追求覆盖面只把日常开发中最常用的增删改查、关联查询、聚合统计和索引相关语法整理成一套可以随时翻看、直接套用的笔记本。如果你也刚开始学SQL或者已经工作一段时间但基础语法总是边用边忘这份手册应该能给你省下不少翻文档的时间。1. 为什么SQL基础语法查询手册还有存在价值以前我也觉得现在后端开发基本都有现成的数据访问框架写SQL的机会好像越来越少。但真到排查线上问题、写统计报表、做数据分析时框架越来越不管用最后所有人都会回到一条SELECT面前。SQL是少数花一次时间学会、之后几十年都在用的技能它的基础语法值得你认真做一份自己的学习笔记。这份手册我建议按“查字典”的思路使用而不是从头到尾死记硬背。你可以先通读一遍知道每个语法点大概在什么位置真正写的时候想不起来再回来翻对应小节。我现在写项目里的统计SQL偶尔也会回来翻这里的聚合分组部分因为它把最容易记混的几个地方用案例固定下来了。1.1 手册的核心设计场景带语法我把SQL学习笔记分成两大板块一类是“改数据”建表、插入、更新、删除一类是“查数据”筛选、关联、聚合、排序、分页。查数据是绝对重点因为日常需求里至少有八成是查剩下两成才是写。每个语法点我尽量配一个“什么时候用”的说明。比如IN和EXISTS只看概念会纠结半天但一旦知道“查子表是否存在记录”就用EXISTS立刻就能决定。场景带语法的好处是你不需要记住全部细节只需要记住“这个场景我见过”然后翻笔记抄一遍就能跑通。1.2 谁适合使用这份笔记如果你是刚入门的初学者建议不要跳着读按建表、增删改、查询、关联、聚合、索引的顺序来。这个顺序就是实际开发中从无到有搭建数据模块的顺序。如果你已经写过一些简单SQL但发现自己总是卡在多表关联和分组统计上重点看第5章和第6章这两章是区分“会写”和“写得好”的分水岭。如果你只是临时要用SQL查个数那么直接翻第4章把SELECT骨架和WHERE过滤看懂大部分临时查询都能应付。2. 从建表开始类型、约束与索引选择很多SQL学习笔记一上来就讲SELECT我把建表放在最前面是有原因的。表结构决定了你之后怎么写查询字段类型选错了写出来的条件就会别扭主键设计不好关联查询会绕一大圈索引不加同样的SQL在数据量大了以后会慢到怀疑人生。2.1 一条规范的CREATE TABLE长什么样先看一个简单但完整的建表语句CREATE TABLE user_info ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_name VARCHAR(50) NOT NULL DEFAULT COMMENT 用户名, age INT DEFAULT NULL COMMENT 年龄, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, status TINYINT DEFAULT 1 COMMENT 状态1启用0禁用, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id) ) COMMENT用户信息表;这里有几个字段设计上的细节。id用了BIGINT而不是INT因为线上数据量增长很快INT最大也就21亿出头一旦接近上限就很麻烦。created_at直接给默认值CURRENT_TIMESTAMP这样插入数据时不用手动填时间也不容易漏。status用了TINYINT而不是VARCHAR因为状态这种东西是给程序判断的不是给人看的数字比字符串更节省空间。2.2 常用数据类型怎么选不同关系型数据库的数据类型命名稍有差异但下面这些是最常见的适用大部分场景类型用途注意事项INT / BIGINT整数ID、数量主键、计数用 BIGINT 更稳妥DECIMAL(p,s)金额、精确小数禁止用 FLOAT/DOUBLE 存金额精度会出问题VARCHAR(n)短文本、姓名、邮箱长度按业务上限设置不要滥用超大长度TEXT / LONGTEXT长文本、备注尽量不要用在经常查询的字段上DATETIME / TIMESTAMP日期时间建议统一用DATETIME避免时区陷阱TINYINT状态、开关大部分数据库用它模拟布尔值兼容性好我在笔记中特别标注了一条金额字段必须用DECIMAL。浮点数在计算时会产生精度误差比如0.1 0.2在某些数据库里算出来不是严格等于0.3。做账、算金额这种场景误差是不可接受的。2.3 约束数据库帮你把关的规则建表时另一个重点就是约束它是数据库拒绝脏数据的第一道防线CREATE TABLE order_info ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status VARCHAR(20) DEFAULT pending, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) COMMENT订单表;NOT NULL保证关键字段不能为空UNIQUE保证订单号不重复DEFAULT提供合理的默认值主键则保证每一行都能被唯一标识。外键约束我反而建议谨慎使用学习阶段一定要懂它的含义但实际项目中有时为了性能和灵活性外键不建在数据库层而是靠应用层逻辑保证。2.4 主键设计的直觉主键是SQL学习笔记里看似简单、实际容易踩坑的环节。我的建议很简单默认情况下用自增BIGINT主键。原因有三个插入效率高、查询快、值稳定。不要拿真实业务字段当主键比如身份证号、手机号。这类字段虽然看起来唯一但业务上可变更一旦变动所有关联它的地方都会跟着遭殃。如果业务上需要一个不重复的编号可以用一个独立字段加UNIQUE约束不用非要当主键。2.5 修改表结构ALTER TABLE常用语法建表之后几乎一定会改表常见的操作有-- 新增字段 ALTER TABLE user_info ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT 手机号; -- 删除字段 ALTER TABLE user_info DROP COLUMN phone; -- 修改字段类型和默认值 ALTER TABLE user_info MODIFY COLUMN age INT DEFAULT 0 COMMENT 年龄; -- 修改表名 ALTER TABLE user_info RENAME TO member_info;改表有个建议先备份再执行。尤其是删除字段、修改字段类型这种不可逆操作线上环境最好先在测试库跑一遍。2.6 索引建表时就要考虑的“加速器”索引是SQL性能的第一课程建表阶段至少要考虑主键之外的两类索引唯一索引和普通索引。唯一索引已经在order_no上体现了普通索引的典型用法是给高频查询字段加ALTER TABLE order_info ADD INDEX idx_user_id (user_id);加了这个索引以后按user_id查订单就会快很多。但要注意索引不是越多越好每个索引都会拖慢写入速度还会占用存储空间。我的原则是先不加太多索引等到出现慢查询了再用执行计划确认到底该建哪个。索引的深入内容我会在第8章展开讲。3. 增删改INSERT、UPDATE和DELETE的安全写法“增删改”看似简单却是整个SQL操作里事故率最高的部分。最常见的翻车方式就是忘记写WHERE把全表数据改了或者删了。做SQL学习笔记时我特意把安全习惯写在了语法的前面因为语法本身很短真正需要反复强调的是习惯。3.1 INSERT的常用写法插入数据最常见的是这几种写法-- 标准写法指定字段插入 INSERT INTO user_info (user_name, age, email, status) VALUES (张三, 28, zhangsanexample.com, 1); -- 一次性插入多行 INSERT INTO user_info (user_name, age, email, status) VALUES (李四, 25, lisiexample.com, 1), (王五, 30, wangwuexample.com, 1); -- 把另一张表的数据复制进来 INSERT INTO user_info (user_name, age, email, status) SELECT name, age, email, 1 FROM temp_user;第三种写法在数据迁移、初始化时会经常用到。需要注意的是如果两张表的字段数量、顺序不一致一定要在SELECT之后继续使用SELECT逐字段对应不要偷懒用SELECT *否则字段错位很难发现。3.2 批量插入与回滚批量插入能大幅提升写入效率但它也有副作用。一次插入几百上千行如果中间某一行违反了约束整个批次可能全部失败也可能部分成功这取决于数据库的存储引擎和事务设置。我在笔记里给自己定了一个原则大批量导入时每批控制在500到1000行并且包在事务里执行。这样即使出了问题也可以回滚重来不会把表搞成“半成品”状态。3.3 UPDATE必须带着WHERE写更新语句的结构很简单UPDATE user_info SET status 0 WHERE user_id 1001;但“忘记WHERE”是新手最容易犯的错误。只要少写这一行全表user都会被禁用。防止这种情况我有一个固定动作先写一条SELECT确认范围再改成UPDATE。-- 第一步确认要影响哪些行 SELECT * FROM user_info WHERE user_id 1001; -- 第二步执行更新 UPDATE user_info SET status 0 WHERE user_id 1001;关联更新也比较常见比如按照另一个表的字段更新当前表。大体语法是UPDATE配合JOIN在主流关系型数据库里写法略有差异。我的建议是如果一张表超过几十万行关联更新前一定要先评估影响行数最好拆成小批量逐步处理。3.4 DELETE与TRUNCATE的取舍删除数据有两种常见手段-- 删除指定行 DELETE FROM order_info WHERE order_id 10086; -- 清空整张表 TRUNCATE TABLE order_info;DELETE可以加WHERE、可以只删一部分而且如果开启事务还有机会回滚TRUNCATE则是直接清空整张表速度快但很多数据库里不能按行回滚。需要清空整张表时TRUNCATE确实比DELETE快得多但我建议无论用哪种方式都先确认“这张表真的可以不要了”。3.5 数据变更的安全习惯我给SQL学习笔记专门写了一段安全操作流程-- 开启事务 BEGIN; -- 执行删除 DELETE FROM order_info WHERE order_id 10086; -- 先不提交确认影响行数正确 SELECT ROW_COUNT(); -- 正确则提交错误则回滚 COMMIT; -- ROLLBACK;开发环境里这个习惯也能救命。我有一次删测试数据忘了写第二个条件还好事务是开着的直接回滚否则那批造数脚本得重新跑半小时。生产环境操作更要走审批流程这个习惯要从第一天学SQL就养成。3.6 小坑自增主键删了为什么还会变大很多人会发现删除表中最后一条记录以后再插入新数据主键并不会从原来的位置继续而是继续增长。比如删除了ID为100的记录新插入的数据ID可能是101。这是因为自增计数器不会回退它只负责“递增”不负责“补全”。有些项目对ID连续有执念但绝大多数场景完全不需要管它。ID只是一个唯一标识不连续完全没问题。如果实在想重置不同的数据库有不同的处理方式但重置自增计数是个高风险操作生产环境不要随便做。4. SELECT语法骨架与过滤条件SELECT是SQL学习笔记的绝对核心。不管多复杂的报表拆到底都是先确定从哪些表拿数据再过滤到目标行然后分组、排序、截取。骨架搭对了后面就不容易乱。4.1 一条完整SELECT语句长什么样先看一个尽量覆盖了主要子句的完整示例SELECT user_name, COUNT(*) AS order_cnt FROM order_info WHERE status paid AND created_at 2024-01-01 GROUP BY user_name HAVING COUNT(*) 2 ORDER BY order_cnt DESC LIMIT 10;这条SQL的含义是查2024年以后、状态为“已支付”的订单按用户名分组找出订单数至少2笔的用户按订单数从高到低排序取前10名。每个子句各干一件事组合起来就是一个标准的分组统计查询。4.2 各子句的逻辑执行顺序这是SQL学习笔记里最值得记的一张表执行顺序子句作用1FROM / JOIN确定数据来源表并完成关联2WHERE过滤行级数据3GROUP BY分组4HAVING过滤分组结果5SELECT投影要返回的列6DISTINCT对结果去重7ORDER BY排序8LIMIT截取行数很多人的疑惑——为什么WHERE里不能用SELECT中的别名原因就在这里WHERE是第2步执行的而别名是在第5步SELECT时才产生的。普通数据库认为别名此时不存在。反过来ORDER BY能使用别名因为它排在SELECT后面执行。4.3 WHERE条件组合与运算符优先级最简单的过滤条件就是比较运算、、、、、。多个条件组合时AND优先级高于OR所以下面这条SQL的结果可能和直觉不同WHERE status paid OR status pending AND amount 100它的实际含义是状态为pending且金额大于100或者状态为paid。如果不想要这个逻辑一定要加括号WHERE (status paid OR status pending) AND amount 100我见过不少因为优先级理解错误导致的报表数据不对排查半天才发现是少了对括号。建议只要有两个以上条件就主动加括号明确表达意图。4.4 NULL与空字符串不是一回事NULL表示“没有值”空字符串表示“一个长度为0的值”二者完全不同。判断NULL不能用等号必须用IS NULL-- 正确 SELECT * FROM user_info WHERE email IS NULL; -- 错误 SELECT * FROM user_info WHERE email NULL;email NULL永远不会返回任何行因为NULL不等于任何值包括它自己。这是SQL学习笔记里非常经典的一个坑几乎每个初学者都会踩。4.5 LIKE模糊查询LIKE是文本模糊匹配的主要工具-- 以张开头 SELECT * FROM user_info WHERE user_name LIKE 张%; -- 姓名中第二字是三 SELECT * FROM user_info WHERE user_name LIKE _三%; -- 包含example SELECT * FROM user_info WHERE email LIKE %example%;%表示任意多个字符_表示一个字符。需要提醒的是前后的%会降低查询效率尤其是%关键字这种写法索引很难生效。能用前缀查询就尽量用前缀查询。4.6 DISTINCT去重的边界DISTINCT用于去重但它作用于整行不是单个列SELECT DISTINCT user_name, status FROM order_info;上面这行会过滤掉“用户名和状态完全相同”的重复行而不是只针对user_name去重。如果你想看一个字段有哪些不同的值直接用SELECT DISTINCT user_name ...没问题如果你想统计不同值的个数更推荐的写法是COUNT(DISTINCT user_name)。4.7 条件过滤综合练习给一个综合例子查询2024年创建的、状态不是“关闭”的用户订单按金额从高到低取前5笔。SELECT order_id, user_name, amount, status FROM order_info WHERE created_at 2024-01-01 AND created_at 2025-01-01 AND status closed ORDER BY amount DESC LIMIT 5;这个例子把WHERE、比较条件、排除条件、排序、分页都串起来了也是日常查询最常用的组合。5. 多表关联JOIN家族与适用场景单表查询只是基础真实业务中的表通常要拆开设计查询时再拼回来。JOIN就是拼接的手段也是新手从“能跑”到“能写对”的最大坎。5.1 为什么需要JOIN拆开存合起来查为了减少数据冗余数据库设计阶段会把信息拆到多张表里。例如用户信息放一张表订单信息放另一张表两张表通过user_id关联。查询时如果需要同时看“是谁下的单、订单金额多少”就必须把两张表重新拼在一起这就是JOIN做的事情。SELECT u.user_name, o.order_no, o.amount FROM user_info u JOIN order_info o ON u.id o.user_id;这里u和o是表的别名就是给表起个短名字后面写字段时省事。我用别名u表示用户表用o表示订单表ON后面写清楚关联条件。5.2 四类JOIN的差异对照表JOIN类型保留哪些行典型场景INNER JOIN只保留两边都匹配成功的行查“确实存在关联关系”的数据LEFT JOIN保留左表全部行右表没匹配上就填NULL查“所有用户及他们的订单没有订单也显示”RIGHT JOIN保留右表全部行左表没匹配上就填NULL和LEFT JOIN方向相反实际用得少FULL OUTER JOIN保留两边全部行没匹配上就填NULL求两表并集时使用部分数据库不支持日常开发中INNER JOIN和LEFT JOIN是绝对主力。RIGHT JOIN能用LEFT JOIN换方向替代所以很多项目里根本不写。FULL OUTER JOIN虽然在标准SQL里存在但有些数据库不支持不熟悉之前不建议优先使用。5.3 自连接案例员工-经理有时候一张表里就存在层级关系典型场景是员工表普通员工有manager_id指向同一个表中的经理ID。要查“每个员工以及他的经理姓名”就需要把自己和自己关联SELECT e.user_name AS employee_name, m.user_name AS manager_name FROM employee e LEFT JOIN employee m ON e.manager_id m.id;这种写法叫自连接。关键点在于要给同一张表起两个不同的别名一个是员工视角一个是经理视角。初学看到同一个表出现两次会犯晕但真正理解了“别名代表不同角色”之后自连接就很简单了。5.4 ON条件与WHERE条件的边界JOIN的ON条件负责“怎么匹配两张表”WHERE负责“在匹配结果上过滤”。二者分工不同但有个容易踩的坑对于LEFT JOIN如果右表的过滤条件写在WHERE里左表行会因为匹配不到而变成NULL随后被WHERE过滤掉最终结果和INNER JOIN差不多。-- 这样写没订单的用户会被过滤掉 SELECT u.user_name, o.order_no FROM user_info u LEFT JOIN order_info o ON u.id o.user_id WHERE o.status paid; -- 这样写没订单的用户仍然保留 SELECT u.user_name, o.order_no FROM user_info u LEFT JOIN order_info o ON u.id o.user_id AND o.status paid;当你想保留左表全部记录同时对右表加条件时条件应放在ON后面。这一点在写报表时非常关键很多“为什么LEFT JOIN出来的数据少了一部分”的问题都是这个原因。5.5 关联查询的数据膨胀问题一对多关联时左表的一行可能会对应右表的多行导致左表行被复制多次。比如一个用户有10个订单用户表JOIN订单表后这个用户会出现10行。如果不注意统计SUM就会变大。解决方案是如果只想统计用户维度信息可以先把订单表按用户聚合再去JOIN用户表。比如先算出每个用户的订单数量SELECT u.user_name, o.order_count FROM user_info u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_count FROM order_info GROUP BY user_id ) o ON u.id o.user_id;这样order_info先被压缩成每个用户一行再和用户表关联就不会膨胀了。这个思路在复杂统计里特别常用。5.6 我最常见到的JOIN错误见过最多的问题有两个一是完全没有ON条件导致笛卡尔积即左表每行都和右表每行组合结果行数爆炸二是ON条件写反比如ON o.user_id u.id和ON o.user_id u.id写反了最终结果可能相同但逻辑顺序会变得难读。我的建议是写JOIN时先明确“谁是主表、谁是从表”再写ON再检查关联方向。出现行数异常时第一反应就是用COUNT(*)看下结果集行数是否合理。6. 聚合分组与子查询统计报表的武器库JOIN拿到明细GROUP BY把明细变成汇总。这就是统计报表的核心逻辑。聚合函数本身不难但很容易踩到“和SELECT字段不匹配”的坑。6.1 聚合函数速查表函数作用注意点COUNT(*)统计行数包含NULL行COUNT(列)统计某列非NULL个数NULL不计数SUM(列)求和忽略NULLAVG(列)求平均值忽略NULLMAX(列)取最大值忽略NULL常用于日期取最新MIN(列)取最小值忽略NULL这里最值得记的是COUNT(*)和COUNT(列)的区别COUNT(*)统计一行有多少行包括所有字段都是NULL的行COUNT(列)只统计该列非NULL的行。想要“数行数”时直接用COUNT(*)最稳妥。6.2 COUNT(*)、COUNT(1)与COUNT(列)的区别很多人纠结COUNT(*)和COUNT(1)哪个更快。在主流关系型数据库里这两个性能基本没区别COUNT(*)是标准写法也最清晰。真正需要区分的是COUNT(列)-- 统计用户总数 SELECT COUNT(*) FROM user_info; -- 统计有邮箱的用户数 SELECT COUNT(email) FROM user_info; -- 统计不同的邮箱数量 SELECT COUNT(DISTINCT email) FROM user_info;如果你是统计某个字段的非NULL数量用COUNT(列)如果是单纯统计总行数用COUNT(*)。6.3 GROUP BY的分组逻辑与SELECT字段限制GROUP BY把相同值的行合并成一组每组只输出一行。语法上有一条重要规则SELECT里出现的普通列必须同时出现在GROUP BY中否则逻辑上就说不通。-- 合法 SELECT user_id, COUNT(*) AS order_cnt FROM order_info GROUP BY user_id; -- 不合法user_name 没有在 GROUP BY 中出现 SELECT user_id, user_name, COUNT(*) AS order_cnt FROM order_info GROUP BY user_id;同样的逻辑一个用户可能有多个不同的用户名吗按user_id分组以后每组只剩下一行这一行的user_name该取哪一条就不确定了。某些数据库直接报错某些数据库悄悄取一行结果不可控。所以规范做法是分组后只输出分组字段和聚合函数计算出来的值。6.4 HAVING过滤分组结果WHERE过滤的是行HAVING过滤的是组。想找“下单次数超过5的用户”就要先按用户分组再用HAVING筛掉组数小于等于5的组。SELECT user_id, COUNT(*) AS order_cnt FROM order_info GROUP BY user_id HAVING COUNT(*) 5;如果你还提前想过滤状态记得把状态条件放在WHERE里而不是HAVING里。优先在WHERE阶段缩小数据量效率更高。6.5 子查询的三种形态形态返回内容典型位置标量子查询单个值SELECT后面或WHERE后面做比较列子查询一列多行IN后面表子查询/派生表多行多列FROM后面当临时表用标量子查询最常见比如查用户最近一笔订单金额SELECT id, (SELECT MAX(amount) FROM order_info WHERE user_id user_info.id) AS max_order_amount FROM user_info;列子查询配合IN查“有订单的用户”SELECT * FROM user_info WHERE id IN (SELECT user_id FROM order_info);表子查询就是之前用过的FROM (SELECT ...) AS o写法相当于把查询结果当临时表用。6.6 EXISTS与IN怎么选IN适合结果集较小、逻辑简单的场景EXISTS通常适合判断“是否存在”这种语义。看一个例子-- 查有订单的用户 SELECT * FROM user_info u WHERE EXISTS ( SELECT 1 FROM order_info o WHERE o.user_id u.id );这个查询里子查询只要找到一条存在记录就可以返回不需要真的返回所有行所以当order_info数据量很大时用EXISTS往往更合适。IN有相对更简洁的写法但遇到子查询结果包含NULL时IN的行为可能和预期不一致。我的经验是判断“存在不存在”优先用EXISTS意图更清晰。6.7 实战月度销售统计把聚合、分组、排序放在一起就是一个能直接用于报表的SQL。假设统计每个月的订单总金额和订单数量SELECT DATE_FORMAT(created_at, %Y-%m) AS month, -- 具体函数名因数据库而异 COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE status paid AND created_at 2024-01-01 AND created_at 2025-01-01 GROUP BY DATE_FORMAT(created_at, %Y-%m) ORDER BY month;这里GROUP BY按月份字符串分组把同一个月的数据汇成一行。如果你所在项目用的数据库不支持DATE_FORMAT用年份和月份字段分别提取也是同样思路。统计报表做完以后建议逐月手算一个月的数据做核对确认结果对得上再发布。7. 排序、分页与常用函数筛选和聚合之外排序分页是查询输出端的常规动作。常用函数则能帮你在SQL里直接处理字符串、日期和空值省得把数据拉到程序里再加工。7.1 ORDER BY多字段排序与NULL排序SELECT user_id, amount FROM order_info ORDER BY user_id ASC, amount DESC;先按user_id升序相同user_id内再按amount降序。多字段排序的核心是先后顺序前面的字段是主排序键后面的字段只在前面相等时起作用。NULL排序在各数据库里不太一致有的把NULL排最前面有的把NULL排最后面。如果业务上要求稳定可以用COALESCE把NULL转成一个不会出现的值或者用表达式控制排序位置。7.2 LIMIT分页与偏移量的代价分页查询最基础的写法是-- 第1页每页20条 SELECT * FROM order_info ORDER BY id LIMIT 20 OFFSET 0; -- 第2页每页20条 SELECT * FROM order_info ORDER BY id LIMIT 20 OFFSET 20;OFFSET表示跳过多少行。这种方式在页数小时没问题但一旦页数很深数据库还是要先把前面的行数数出来性能会越来越差。对大量数据的分页我推荐用“键集分页”记住上一页最后一条记录的ID或排序键值下一页直接用WHERE id 上一页最后ID LIMIT 20。这样每次都能直接定位到起点不用跳过大偏移量。7.3 字符串函数速查函数作用CONCAT(a, b)拼接字符串SUBSTRING(str, start, len)截取子串UPPER / LOWER转大写/小写TRIM去掉首尾空格LENGTH计算字符数REPLACE替换子串综合示例把“姓”和“名”拼成全名再转成大写SELECT id, CONCAT(last_name, first_name) AS full_name, UPPER(email) AS upper_email FROM user_info;字符串函数每个数据库都有名字大同小异。SQL学习笔记里我习惯把最常用的几个记成一张速查表用到的时候翻一眼比死记强得多。7.4 日期时间函数统计场景最常见的坑日期时间处理是统计报表里的重头戏也是最容易出错的环节。常见需求是“查某一天的数据”-- 正确做法左闭右开区间 SELECT * FROM order_info WHERE created_at 2024-01-01 AND created_at 2024-01-02; -- 容易漏数据的写法 SELECT * FROM order_info WHERE created_at BETWEEN 2024-01-01 AND 2024-01-01 23:59:59;为什么BETWEEN写法容易出问题因为23:59:59只能覆盖到秒如果数据精确到了毫秒2024-01-01 23:59:59.500就会被漏掉。更好的做法是直接把结束时间写成第二天零点用“大于等于开始时间小于结束时间”的区间保证把当天所有数据都包含进来。7.5 数值函数与CASE WHEN数值函数常用这些ROUND(x, n)四舍五入、CEIL向上取整、FLOOR向下取整、ABS绝对值。配合CASE WHEN能实现“按条件打标签”SELECT order_id, amount, CASE WHEN amount 1000 THEN 大额 WHEN amount 100 THEN 中额 ELSE 小额 END AS amount_level FROM order_info;CASE WHEN在SQL里的地位相当于程序里的if-else写报表时绕不开。7.6 空值处理三兄弟处理NULL有三个常用函数COALESCE(a, b, c)返回第一个非NULL参数IFNULL(a, b)是双参数版空值替换NULLIF(a, b)当两个值相同时返回NULL。SELECT user_id, COALESCE(phone, email, 无联系方式) AS contact FROM user_info;这样查询时手机号为空就用邮箱邮箱也为空就显示“无联系方式”避免了界面上一堆NULL。8. 索引入门查询性能的第一课SQL学习笔记如果只讲“能不能查出”不讲“查得快不快”实用性会大打折扣。数据量小时漏了索引可能没感觉数据量到几十万、几百万行时全表扫描和执行计划走索引查询速度差几十倍。8.1 没有索引时查询怎么工作没有索引的查询只能全表扫描也就是一页一页往下翻逐行检查是否符合条件。这个过程可以类比成一本没有目录的字典想查一个词只能从第一页翻到最后一页。-- 如果 user_id 没有索引这条语句会扫描所有订单 SELECT * FROM order_info WHERE user_id 10086;小表全表扫描无所谓几百行数据眨眼就扫完了。但一旦表里有一百万行订单每次查询都翻一遍性能就会急剧下降。8.2 索引的工作原理索引的本质是一种排序过的数据结构。它把字段值按照一定顺序组织起来查询时可以通过高效查找算法快速定位而不是逐行扫描。作用相当于字典的目录、书的目录。代价也很明确索引需要额外存储空间每次插入、更新、删除数据时索引也要跟着更新写操作会变慢。这就是“索引不能乱建”的根本原因。8.3 索引的三种常见形态类型特点语法示例普通索引允许重复加速查询ALTER TABLE t ADD INDEX idx_name (col)唯一索引保证字段值不重复ALTER TABLE t ADD UNIQUE KEY uk_name (col)联合索引多个字段组合成一个索引ALTER TABLE t ADD INDEX idx_multi (a, b)唯一索引除了加速还有约束作用。比如订单号如果业务上不允许重复就应该建唯一索引数据库会自动拒绝重复值。联合索引则是为了应对WHERE a ? AND b ?这类多条件查询。8.4 联合索引的最左前缀原则联合索引有一个非常容易被忽略的规则最左前缀原则。意思是一个联合索引(a, b, c)并不是随便使用其中某个字段都能命中。可以命中的情况是使用了a使用了a, b使用了a, b, c。如果单独使用b或c索引基本用不上。这个原则就像点菜索引是按a先排序再按b排序最后按c排序。a没点后面的b和c自然无从谈起。所以联合索引的字段设计要遵守“高频字段放最前面”的策略。8.5 什么时候不要建索引不是所有字段都适合建索引。一个典型的场景是性别字段只有两个值区分度太低建了索引以后查询优化器也未必愿意用还有频繁更新的字段索引会放大写操作的开销小表也完全不需要索引全表扫描反而更快。我见过很多“索引无论有没有用都先建一堆”的同学结果表写入慢存储浪费查询却不一定变快。正确做法是先观察慢查询确定某个查询确实频繁、且数据量大再针对性地建索引。8.6 看懂执行计划的第一层查询性能是否走了索引最简单的办法是用执行计划键字查看。EXPLAIN SELECT * FROM order_info WHERE user_id 10086;执行计划返回结果里最值得先看三个字段type表示访问方式如果看到ALL基本就是在全表扫描key表示实际使用的索引为空说明没走索引rows是估计扫描的行数行数越大越危险。看到ALL并不一定代表一定需要优化但如果数据量大、查询频繁就要考虑加索引了。8.7 慢查询优化小案例假设有一个查询查所有“1月创建订单的用户数量”原来写法是直接从订单表关联用户表再统计。如果订单表user_id没索引关联时只能全表扫描订单表逐行匹配。优化步骤通常是先给order_info.user_id加索引再把WHERE条件尽量前置减少数据量最后避免SELECT *只取需要的列。这三个改动做完原SQL通常能从“秒级”降到“毫秒级”。需要注意的是加索引前要模拟真实数据量测试不要在只有几千条数据的测试库里下结论。9. 整理这本SQL学习笔记时踩过的坑与建议写SQL学习笔记的过程本身就是踩坑记录的过程。很多语法点我第一遍看的时候觉得自己会了数字一写错、场景一变就又翻车。这些坑没有一个是高深的原理问题全是“当时没注意”的小细节但每一个都实实在在地浪费过排查时间。9.1 坑一WHERE里用别名报错都不知道错在哪有一次我写SELECT amount * 0.1 AS commission FROM order_info WHERE commission 10数据库直接报错。当时还很困惑commission明明在上面的SELECT里定义了为什么用不了原因就是执行顺序WHERE在SELECT之前执行别名还没生成。后来我的解决办法是要么在WHERE里把表达式重写一遍要么把查询包一层。这个认知一旦建立就不会再犯相关错误。9.2 坑二JOIN少写ON结果行数爆表另一个记忆深刻的事是JOIN后忘了写ON条件表里只有几千行结果集膨胀出几十万行。当时统计报表怎么对都对不上后来一查是两张表做成了笛卡尔积。现在写JOIN我会先写ON再写其他内容写完还会用COUNT(*)看一下行数是否符合直觉。如果一张用户表只有几百人却查出来几十万行那一定是JOIN条件有问题。9.3 坑三DELETE忘记写WHERE靠事务保命有次清测试数据我想删掉“测试账号”的数据结果WHERE条件少写了一半SQL执行完以后才发现整个表都被清空了。当时因为这个表数据无关紧要所以影响不大但这件事给我留下特别深的印象。以后凡是对数据库做更新删除我先把事务打开不带WHERE的DELETE绝不直接执行。尤其要提醒的是DELETE这种DML操作在大部分数据库里能回滚但像TRUNCATE、DROP这类操作一旦执行就基本没有后悔药。操作前备份永远是最稳妥的手段。9.4 坑四时间统计月底少一天统计某月订单时有人喜欢写created_at BETWEEN 2024-01-01 AND 2024-01-31。这个写法看着没毛病月底最后一天晚上的数据却经常漏。重新理解之后我统一改成“左闭右开”区间WHERE created_at 2024-01-01 AND created_at 2024-02-01这样无论数据精确到秒还是毫秒1月31日整天都能被覆盖。现在所有时间范围统计我都按这个格式写基本没再漏过数据。9.5 建议搭一个自己的练习库动手实践是学好SQL的唯一捷径。我建议你本地起一个测试数据库建立四张表用户表、订单表、商品表、订单明细表然后写脚本生成几百行测试数据。有了练习库你可以在上面演练几乎所有的SQL基础语法单表查询、多表关联、分组统计、子查询、分页排序、索引优化。遇到不确定的写法先在练习库验证再放到正式环境这个习惯能帮你避免很多低级失误。9.6 我对这本手册的维护习惯做SQL学习笔记这件事与其说是“学完就完”不如说是一次持续积累的过程。我每遇到一个新语法点、一个没见过的坑都会往对应章节里补一条并标注适用的场景和注意事项。手册不是用来背的是用来“翻”的。写得越细、案例越多之后返工排查的时间就越少。SQL入门不难但它是个熟练活真正能让工作效率明显提升的是把常用语法练成条件反射而这份手册就是在朝那个方向推你一把。