做SQL Server开发的人十有八九都会遇到这种场景运营那边甩来一句“帮我查一下姓名里带‘伟’的人”或者“日志表里关键字包含‘timeout’的记录有多少”。第一反应基本都是where name like %伟%一条语句甩过去结果出来了大家也就散了。但like真正的用法远不止一个“两头带百分号”这么简单。我在一个运行了十来年的业务系统上做过大量的数据维护和查询优化吃过不注重排序规则的亏也踩过通配符转义没处理导致误查的坑。这篇就围绕SQL Server里的模糊查询like用法和方向各异的查询函数把每个细节掰开揉碎讲清楚包括为什么这样写、什么时候那样写、写错了会发生什么。这篇文章适合刚接触SQL Server的初学者也适合写了不少SQL但一直没系统整理过like 和函数用法的开发或运维朋友。读的时候建议打开SSMS顺手执行一遍光看是记不住的。1. LIKE 的三种匹配模式% _ 和字符集各自解决什么问题1.1 LIKE 的基本结构LIKE在SQL Server里的定位是一个谓词Predicate负责判断某个字段的值是否符合一个模式字符串。基本写法固定在以下形态SELECT 列1, 列2 FROM 表名 WHERE 列名 LIKE 模式串;模式串里允许放两类东西一类是普通字符要求字段值原样包含这些字符另一类是通配符表示“任意内容”。SQL Server支持的通配符只有三个半分别是%、_、[]以及[^]很多人只用了%剩下几个基本没碰过但这几个恰恰是提高查询精度、减少多余结果的关键。1.2 %匹配任意长度字符含零个字符%是使用频率最高的通配符含义是“匹配任意数量包括0个的任意字符”。三个最常见的写法-- 以“张”开头的姓名 SELECT * FROM student WHERE name LIKE 张%; -- 以“伟”结尾的姓名 SELECT * FROM student WHERE name LIKE %伟; -- 姓名中任意位置含“小” SELECT * FROM student WHERE name LIKE %小%;很多人觉得%小%、%伟、张%三个写法差不多但实际在索引使用上差别很大这个我在第2部分详细讲。这里先记住一个原则只要%出现在模式串的最前面前缀位置数据库就没法用常规索引做范围扫描。像LIKE %小%这种写法SQL Server等于要把整列数据全部拉出来逐个比对。%还能匹配零个字符所以LIKE 张%能查出姓名为“张”的人因为“张”后面跟零个字符也满足模式。1.3 _匹配单个字符做定长筛选_匹配且仅匹配一个字符多一个少一个都不行。它在处理“XX届XX班”这类定长编码时特别有用。举几个实际例子-- 学号总共6位查倒数第3位是5的 SELECT * FROM student WHERE student_id LIKE __5___; -- 查姓氏为张且名字恰好只有一个字如“张伟” SELECT * FROM student WHERE name LIKE 张_;这里有个坑特别值得提一下对中文来说一个_只能匹配一个汉字吗是的。在SQL Server里_匹配一个字符而中文字符也是单字符。但是如果你用的是nvarchar且启用了某些补充字符集比如代理对形式的生僻字一个_可能只匹配半个补充字符这个概率低遇到别慌知道有这回事就行。1.4 字符集匹配 [ ] 与排除 [^][]允许在方括号内指定一组字符匹配其中任意一个[^]则反过来匹配“不在这个集合里的任意一个字符”。示例-- 姓氏是“张、李、王”其中之一的 SELECT * FROM student WHERE name LIKE [张李王]%; -- 查第一位数字是1、2、3的电话 SELECT * FROM contact WHERE phone LIKE [1-3]%; -- 查姓名不以“张”或“李”开头的人 SELECT * FROM student WHERE name LIKE [^张李]%;[1-3]这类写法属于范围表示等价于[123]。字母范围如[a-z]也比较常用不过要注意大小写是否敏感受排序规则影响这个下一节专门展开。[]这个符号在正则表达式里也出现但SQL Server的[]并不是完整正则只是简化版字符集匹配别把正则那套量词* ?拿进来用通配符只有%和_两个。2. 模糊查询最容易翻车的地方排序规则、转义与索引失效2.1 排序规则COLLATE决定大小写是否敏感先看一个让很多人抓头的场景SELECT * FROM users WHERE username LIKE %admin%;表里明明有一条Admin结果却没查出来不对多数情况下恰好相反——你只想查小写admin结果Admin、ADMIN全冒出来了。SQL Server默认装的排序规则通常长这样Chinese_PRC_CI_AS中间那个CI是Case Insensitive的缩写也就是“大小写不敏感”。在这种规则下LIKE %admin%等价于LIKE %ADMIN%也等价于LIKE %Admin%。如果业务上必须区分大小写可以在查询级别临时指定排序规则SELECT * FROM users WHERE username LIKE %admin% COLLATE Chinese_PRC_CS_AS;注意COLLATE要写在模式串所在的那一侧即username LIKE %admin% COLLATE Chinese_PRC_CS_AS或者username COLLATE Chinese_PRC_CS_AS LIKE %admin%效果一样。CS表示Case Sensitive。这个方法在排查“为什么我 where 条件写对了还是查多数据”时非常有用。除了大小写还要注意全角半角问题SQL Server的默认规则通常也不区分全角半角若必须区分可以使用Chinese_PRC_CS_AS_KS_WS其中KS区分假名类型WS区分全半角。2.2 通配符转义字段里真的存在 % 或 _ 怎么办这是业务系统里特别容易被忽视的场景。假设有个商品编码规则允许编码本身包含_字符比如ABC_123。你想查所有以ABC开头、后跟任意内容的编码SELECT * FROM product WHERE code LIKE ABC%;这条会把ABC_123、ABCX999、ABC全部查出来。但如果你要查“编码里确实包含下划线_的记录”情况就不一样了-- 错误示范这里面 _ 会被当成“匹配单个字符”的通配符 SELECT * FROM product WHERE code LIKE %_%;这条会返回全部记录因为所有编码都至少有一个字符可以被_匹配。解决办法用ESCAPE关键字指定一个转义符-- 转义符设为感叹号表示紧跟其后的 _ 是普通字符 SELECT * FROM product WHERE code LIKE %!_% ESCAPE !;同理要查包含百分号%的记录SELECT * FROM product WHERE code LIKE %!%% ESCAPE !;这里转义符是个人为约定的字符选哪个都行只要不在模式串中产生歧义。字符集[]内如果要匹配]本身写法稍微绕一点可以借道ESCAPE或者把]放第一位但实际业务中遇到不多了解即可。2.3 索引失效前缀通配符为什么让查询变慢直接给结论LIKE abc%在sql server里有可能用上索引LIKE %abc%和LIKE %abc基本走不了索引。原因在于B树索引是按值排序存储的前缀确定时SQL Server可以把“abc之后的所有值”视为一个有序区间去扫描一旦开头就是%就失去了区间定位的依据只能全表扫描或者全索引扫描。但这里有个常见的误区模糊查询变慢就怪%。实际生产环境里数据量只有几万行的话全表扫描也就几十毫秒根本不用紧张。真正需要担心的是几百万行、上千万行的大表。我遇到过一个日志表超过一亿行开发在界面上做关键字搜索每个用户点一次查询数据库CPU立刻飙到100%后来把这类搜索改造成全文索引FULLTEXT情况才缓解。如果不想上全文索引还能用的替代思路有两个把%关键字%拆成两个条件配合计算列和索引比如在写入时把文本反转存储然后用LIKE 关键字%查反转列实现后缀匹配加速。用CHARINDEX函数代替LIKE做包含匹配不过要注意CHARINDEX同样无法使用索引只适合小表。-- 等价于 LIKE %timeout% SELECT * FROM log_table WHERE CHARINDEX(timeout, log_message) 0;还有一点容易被忽略LIKE不会匹配NULL。如果字段值是NULL不管模式串写什么结果都是不匹配。所以模糊查询前要先想好是否需要把NULL数据也纳入统计。3. 查询函数里的字符串派SUBSTRING、CHARINDEX 这些比 LIKE 更精准3.1 截取函数LEFT、RIGHT、SUBSTRINGLIKE 负责筛选但“把字段里的某一段取出来”得靠截取函数。三个核心函数的参数如下LEFT(字符串, 长度)从左侧截取指定长度。RIGHT(字符串, 长度)从右侧截取。SUBSTRING(字符串, 起始位置, 长度)从任意位置截取起始位置从1开始。SELECT name, LEFT(name, 1) AS surname, -- 姓 RIGHT(name, LEN(name) - 1) AS given_name, -- 名字去掉姓 SUBSTRING(name, 2, 2) AS mid_two -- 第2个字符起的两个字符 FROM student;这里有人会踩一个坑中文一个字符占一个位置LEN(张伟)返回2没问题。但如果用的是varchar且存了生僻字或表情符号长度计算会出现偏差。LEN返回字符数而DATALENGTH返回字节数。对于一个汉字DATALENGTH在varchar下通常是2在nvarchar下是4。需要明确到底按字符取还是按字节取别混用。3.2 定位函数CHARINDEX 和 PATINDEXCHARINDEX(要查找的子串, 原字符串)返回子串在原字符串中的起始位置没找到返回0。它和LIKE的定位不一样LIKE告诉你“匹不匹配”CHARINDEX还告诉你“匹配在哪个位置”。典型应用是拆分字符串-- 取邮箱 前面的用户名部分 SELECT email, LEFT(email, CHARINDEX(, email) - 1) AS username FROM users WHERE CHARINDEX(, email) 0;PATINDEX是CHARINDEX的加强版允许在查找串中使用%和_通配符但注意PATINDEX返回的是“第一次匹配的位置”。当LIKE %关键字%只是做布尔判断时PATINDEX可以进一步拿到位置这在解析字符串场景里非常有用-- 找到第一个数字出现的位置 SELECT PATINDEX(%[0-9]%, 订单号A123B); -- 返回43.3 替换与拼接REPLACE、STUFF、CONCAT处理脏数据时REPLACE是利器。比如手机号、身份证号脱敏-- 把手机号第4到第7位变成* SELECT phone, STUFF(phone, 4, 4, ****) AS masked_phone FROM users;STUFF(原字符串, 开始位置, 删除长度, 插入字符串)的逻辑是先删掉指定位置的若干个字符再把新字符串插进去。这个函数在做脱敏、拼接时比SUBSTRING加CONCAT更简洁。字符串拼接要注意NULL问题。直接使用拼接时只要有一方为NULL结果整体就是NULL。CONCAT函数会自动把NULL转成空字符串SELECT CONCAT(first_name, last_name) AS full_name FROM users; -- 等价但更安全即使 last_name 为 NULL 也不会导致全名变 NULL我刚工作那会儿经常因为没处理NULL拼接出来的地址少了一段排查了半天才发现在第5个字段上有个空值。自那之后涉及拼接我一律优先CONCAT。3.4 用字符串函数做模糊查询的进阶组合实际业务里LIKE 和字符串函数经常配合使用。比如要查“身份证号倒数第二位是奇数”的人SELECT * FROM citizen WHERE RIGHT(id_card, 2) LIKE [13579]_;再比如按名字长度过滤查所有名字只有两个字的用户SELECT * FROM users WHERE LEN(real_name) 2;这种方式虽然能用但要提醒LEN(real_name) 2写在 where 里会让该列的索引失效因为对列做了函数运算小表无所谓大表要谨慎。4. 聚合函数与 GROUP BY从“查出来”到“算出来”4.1 五大基础聚合函数与 NULL 的坑COUNT、SUM、AVG、MIN、MAX是查询函数里数据统计的中坚力量。它们的作用范围是一组行返回一个汇总值。SELECT COUNT(*) AS total_users, COUNT(phone) AS users_with_phone, -- 自动忽略 NULL AVG(age) AS avg_age, MIN(create_time) AS earliest_time, MAX(amount) AS max_amount FROM users;NULL 对聚合函数的影响常常让人意外。COUNT(列名)会跳过NULLCOUNT(*)则统计所有行AVG(列名)同样忽略NULL。假设10行数据里有2行的amount是NULLAVG(amount)计算的是剩下8行的平均值而不是把2个NULL当0算。如果业务上需要把NULL当0处理必须先ISNULL(amount, 0)或COALESCE(amount, 0)再聚合。还有一点SUM作用于空结果集会返回NULL而不是0。很多报表程序因为这个出现显示空白。稳妥做法是SELECT ISNULL(SUM(amount), 0) AS total_amount FROM orders WHERE order_date 2099-01-01; -- 查不到数据时也返回0而不是NULL4.2 GROUP BY 的执行逻辑GROUP BY干了什么事把同一个分组键的值合成一组然后对每一组分别执行聚合函数。理解这个逻辑对写对SQL很重要。SELECT category_id, COUNT(*) AS cnt, SUM(price * quantity) AS category_total FROM order_detail GROUP BY category_id ORDER BY category_total DESC;这里有个常被忽略的约束SELECT子句中出现的列要么是分组键GROUP BY后面的列要么必须包在聚合函数里。比如上面查了category_id它是分组键没毛病如果还想查product_name除非它也在GROUP BY里或者用MAX(product_name)包起来否则SQL Server会直接报错。这个报错不是SQL Server矫情是为了防止“每组里有多行到底取哪一行的 product_name”这种语义不明确的问题。4.3 HAVING 与 WHERE过滤时机的差异WHERE在分组前过滤原始行HAVING在分组后过滤聚合结果。这个顺序差异决定了写法。-- 查订单数量大于等于100的客户 SELECT customer_id, COUNT(*) AS order_cnt FROM orders WHERE order_status completed -- 先剔除无效订单 GROUP BY customer_id HAVING COUNT(*) 100; -- 再筛高频客户如果把HAVING COUNT(*) 100换成WHERE COUNT(*) 100会直接报错因为COUNT(*)在WHERE阶段还没计算出来。反过来如果条件能下推到WHERE就尽量下推比如上面的order_status completed写在WHERE里比写在HAVING里效率高因为可以在分组前就缩小数据量。4.4 聚合函数与 LIKE 组合的典型场景汇总统计经常需要先模糊筛选再聚合。比如统计“姓名包含‘张’”的客户的下单总金额SELECT COUNT(DISTINCT customer_id) AS customer_cnt, SUM(order_amount) AS total_amount FROM orders o JOIN customers c ON o.customer_id c.customer_id WHERE c.name LIKE %张%;COUNT(DISTINCT 列)是容易忽略的变体用于计算去重后的数量。模糊查询筛选出的人有重复订单时用它可以准确统计人数。这个写法在大数据量下性能一般但对中小系统完全够用。5. 时间函数、连接查询与行转列这几类函数和模糊查询配合得最多5.1 时间函数GETDATE、DATEADD、DATEDIFF 的正确用法业务系统里时间字段是查询条件中出现频率最高的字段类型之一。SQL Server 的时间函数很多但实际工作中常用的就那么几个。GETDATE()返回当前系统时间。DATEADD(日期部分, 增量, 日期)用于日期加减。DATEDIFF(日期部分, 开始日期, 结束日期)用于计算两个日期的差值。-- 近7天的订单 SELECT * FROM orders WHERE order_date DATEADD(DAY, -7, GETDATE()); -- 按月统计近12个月的订单数 SELECT YEAR(order_date) AS order_year, MONTH(order_date) AS order_month, COUNT(*) AS order_cnt FROM orders WHERE order_date DATEADD(MONTH, -12, GETDATE()) GROUP BY YEAR(order_date), MONTH(order_date) ORDER BY order_year, order_month;5.2 时间字段与 LIKE 组合的坑有些初学者会把日期字段直接跟LIKE配合-- 错误示范 SELECT * FROM orders WHERE order_date LIKE %2024-06%;这通常会报错因为order_date是datetime类型不能直接跟字符串模式比。正确做法有两种-- 方式一转成字符串再匹配不推荐慢 SELECT * FROM orders WHERE CONVERT(VARCHAR(7), order_date, 120) 2024-06; -- 方式二用日期范围推荐可用索引 SELECT * FROM orders WHERE order_date 2024-06-01 AND order_date 2024-07-01;方式二是最稳的写法。加把6月整月框进区间既能走到索引又避免了BETWEEN在 datetime 精度下可能漏掉23:59:59.997之后数据的问题。5.3 连接查询LEFT JOIN 和 CROSS JOIN 别搞混虽然标题核心是模糊查询和查询函数但查询函数最终要服务于实际的取数逻辑连接是绕不开的。LEFT JOIN返回左表全部记录右表没有匹配时用NULL补齐INNER JOIN只返回两边能匹配上的行。实际业务中一定要想清楚“以哪边为主表”。比如统计每个客户的订单数即便某客户没有订单也希望显示数量0这时必须LEFT JOINSELECT c.customer_name, COUNT(o.order_id) AS order_cnt FROM customers c LEFT JOIN orders o ON c.customer_id o.customer_id GROUP BY c.customer_name;很多人在这里犯的错是用了LEFT JOIN却把o.order_id的条件写在WHERE里结果左连接被“抹平”成了内连接。原因很简单一旦在WHERE中过滤右表字段为某个非NULL值左表中无匹配的行就全被剔除了。要加过滤条件就放在ON子句中或者把过滤条件一起放进ANDSELECT c.customer_name, COUNT(o.order_id) AS order_cnt FROM customers c LEFT JOIN orders o ON c.customer_id o.customer_id AND o.order_status completed GROUP BY c.customer_name;这样能统计出“每个客户的已完成订单数”同时保留没有已完成订单的客户。CROSS JOIN则是笛卡尔积左表每行都乘上右表每行。在没有明确需求时少用行数爆炸是分分钟的事。5.4 行转列PIVOT 和条件聚合很多人一听到行转列就觉得难其实本质是“把某个列里的不同值变成多个列”然后对每个值做聚合。SQL Server 提供了PIVOT操作符但语法有点反直觉我平时更推荐用条件聚合实现逻辑更清晰-- 按月份转列统计订单金额 SELECT product_name, SUM(CASE WHEN MONTH(order_date) 1 THEN amount END) AS Jan_amount, SUM(CASE WHEN MONTH(order_date) 2 THEN amount END) AS Feb_amount, SUM(CASE WHEN MONTH(order_date) 3 THEN amount END) AS Mar_amount FROM orders GROUP BY product_name;CASE WHEN配合聚合函数的写法可读性强也方便扩展推荐新手优先掌握。5.5 查询函数使用频率排序与个人建议按我在实际项目里的使用频率给这些查询函数排个序方便你判断优先级GETDATE、DATEADD、DATEDIFF这类时间函数用得最多因为报表查询永远带着时间范围其次是COUNT、SUM、ISNULL这类聚合与空值处理再是LEFT、SUBSTRING、CHARINDEX这类字符串函数最后才是PIVOT这类进阶操作。写SQL查询函数时我自己的习惯是先在草稿纸上写出“我想从哪些原始列经过什么变换汇出哪些结果列”再落成SQL。这样写出来的查询通常结构清晰不容易在GROUP BY和SELECT的对应关系上翻车。另外提醒一句函数虽好用但查询中尽量别对索引列套函数。像WHERE DATEADD(DAY, 1, order_date) GETDATE()这种写法会让order_date上的索引失效。遇到这种情况调整为WHERE order_date DATEADD(DAY, -1, GETDATE())效果相同但引擎能正常用索引。我最初调优一个报表接口的时候就是把三个这种“列上套函数”的条件全部改写成“在常量侧运算”整个查询从 8 秒降到了 0.3 秒。这段经验比背一百个函数签名都管用——函数本身不难难的是知道在哪些位置用、哪些位置不用。