15 SQL Like & Regexp

优质

小牛编辑

126浏览

2023-12-01

1. 定义

慕课解释：Like和Regexp操作符一般与Where搭配用于搜索和匹配字符字段。

2. 前言

本小节中，我们将一起学习SQL Like和 SQL Regexp的基本使用。

很多时候，数据表中存储了大量的字符类型字段，如 varchar 和 char 。虽然它们可以和其它类型字段一样通过 > ，= 等基本运算符进行比较和操作。但是字符串的特性较为复杂，当需要字符串匹配时，基本运算符就无法满足了，因此 SQL 提供了 Like 和 Regexp两个特殊的操作来处理字符类型。

注意： Like 还可以作用于其它字段类型，如 int ，但一般情况下 int 类型字段直接使用基本运算符就足够了。

本小节测试数据如下，请先在数据库中执行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  username varchar(20),
  age int
);
INSERT INTO imooc_user(username,age) VALUES('pedro', 23), ('peter', 18), ('faker', 23);

3. SQL Like

我们可以直观地将 like 这个单词理解为像，当与 Where 搭配时，SQL语句可以大概的翻译为：搜索某个字段像什么的结果。

SQL Like 的语法如下：

SELECT [col] FROM [table_name] WHERE [col] LIKE [val];

其中col表示字段名称，table_name表示数据表名称，val表示条件值。

3.1 例1 单独使用 Like

Like 单独使用时，作用与=运算符一致。

请书写 SQL 语句，查询imooc_user表中用户名像pedro的用户。

分析：

题干中明确的指出，查询用户名像 pedro 的用户，可以得出username是条件字段，LIKE 是操作符，pedro 是条件值。

语句：

整理可得语句如下：

SELECT username, age FROM imooc_user WHERE username LIKE 'pedro';

查询结果如下：

+----------+-----+
| username | age |
+----------+-----+
| pedro    | 23  |
+----------+-----+

在上面例子中，Like 与 Where 搭配，搜索出了 pedro 这个用户。可在 imooc_user 表中不仅有 pedro ，其实还有 peter 这个用户，你能否书写一条 SQL 语句，使用 Like 将 pedro 和 peter 二人同时搜索出来了。

单独使用 Like 是无法做到的，但搭配通配符就可以轻松完成。

4. 通配符

SQL 通配符是用特殊的字符来表示一个或多个字符。

SQL 通配符必须与 Like 搭配使用。

常见的通配符如下：

通配符	描述
%	匹配一个或多个字符
—	匹配一个字符
[char_list]	char_list是一个字符列表，其匹配列表中的任意一个字符
[^char_list]	char_list是一个字符列表，其匹配不在列表中的任意一个字符

提示：[^char_list]与[!char_list]作用是一样的，即^和!等价。

如果你是初次接触通配符，可能还不甚明白，我们还是通过例子来学习。

4.1 例2 Like 搭配单个通配符%

Like 可搭配单个通配符%，用于匹配特定模式下的字符串。

请书写一条 SQL 语句，使用 Like 将pedro和peter二人同时搜索出来了。

分析：

现在有了通配符%，我们就可以轻松的搞定。

语句：

SELECT username, age FROM imooc_user WHERE username LIKE 'pe%';

+----------+-----+
| username | age |
+----------+-----+
| pedro    | 23  |
| peter    | 18  |
+----------+-----+

%表示匹配一个或多个字符，pe是 pedro 和 peter 的前缀，后缀dro和ter都以通配符%来匹配，因此 Like 在匹配搜索的时候迅速地找到了答案。

%通配符的作用远不止于此。

4.2 例3 Like 搭配两个通配符%

Like 也可搭配两个通配符%，匹配特定模式下的字符串。

请书写 SQL 语句，找出用户名内（不包括首尾字符）含有e的用户。

分析：

既然是含有e，因此e可以出现在除首尾以外的任何地方，通配符可表示为%e%。

语句：

SELECT username, age FROM imooc_user WHERE username LIKE '%e%';

结果如下：

+----------+-----+
| username | age |
+----------+-----+
| pedro    | 23  |
| peter    | 18  |
| faker    | 23  |
+----------+-----+

%是最常见的通配符，但有些时候我们也需要精确的位数匹配，这时_的作用就体现出来了。

4.3 例4 Like 搭配通配符_和%

Like 还可搭配两个不同的通配符%,_，匹配特定模式下的字符串。

请书写 SQL 语句，找出第二个字符为e的用户。

分析：

查询条件用通配符可表示为_e%。

语句：

整理可得语句如下：

SELECT username, age FROM imooc_user WHERE username LIKE '_e%';

结果如下：

+----------+-----+
| username | age |
+----------+-----+
| pedro    | 23  |
| peter    | 18  |
+----------+-----+

注意： MySQL 与 PostgreSQL 均不支持[char_list]和[^char_list]通配符，在实际场景中可以使用正则（Regexp）来替代。

5. SQL Regexp

正则表达式可谓是如雷贯耳，与 Like 对比起来，Regexp 更加强大，当然正则表达式的性能不如 Like。

同 Like 一样，SQL Regexp 的语法如下：

SELECT [col] FROM [table_name] WHERE [col] REGEXP [val];

其中col表示字段名称，table_name表示数据表名称，val表示条件值。

正则表达式好用却较为复杂，本小节不会介绍正则表达式具体的语法，如果你感兴趣，可以查阅相关资料进行学习。

5.1 例5 使用 Regexp 查询

Regexp 搭配正则表达式，可用于匹配特定模式下的字符串。

请书写 SQL 语句，找出imooc_user表中用户名以p或f开头的用户。

分析：

从题干中可以得出，username是条件字段，条件值是用户名以p或f开头，用正则表达式可表示为^[pf]。

语句：

整理可得语句如下：

SELECT username,age FROM imooc_user WHERE username REGEXP '^[pf]';

结果：

+----------+-----+
| username | age |
+----------+-----+
| pedro    | 23  |
| peter    | 18  |
| faker    | 23  |
+----------+-----+

从例子中可以看到 Regexp 的用法几乎与 Like 一致，只不过将条件值从通配符换成了正则表达式。

6. 小结

Like 绝大多数情况下都是与通配符一起使用的，否则其作用与=是一样的，无法发挥真正的威力。
%通配符使用最为广泛，也很好理解，多多练习即可掌握。
正则表达式是块难啃的骨头，尝试去理解它，在适当的时候查阅相关资料合理使用即可。
PostgreSQL 除了 like 之外还有一个ilike，可以直接忽略大小写进行匹配。