SQL Like & Regexp

1. 定义

慕课解释:LikeRegexp操作符一般与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 将pedropeter二人同时搜索出来了。

分析

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

语句

SELECT username, age FROM imooc_user WHERE username LIKE 'pe%';
+----------+-----+
| username | age |
+----------+-----+
| pedro    | 23  |
| peter    | 18  |
+----------+-----+

%表示匹配一个或多个字符,pe是 pedro 和 peter 的前缀,后缀droter都以通配符%来匹配,因此 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表中用户名以pf开头的用户。

分析

从题干中可以得出,username是条件字段,条件值是用户名以pf开头,用正则表达式可表示为^[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,可以直接忽略大小写进行匹配。