SQL---模糊查询与正则表达式详解和对比

1：模糊查询

在进行数据库查询的时候，有完整查询和模糊查询之分；

完整查询就是我们平时查询的普通语句（对某个查询条件确定的前提下），而模糊查询就是常见的where 后面增加了 like 关键字。

一般模糊查询语句如下：

select 字段 from 表 where 某字段 like 条件

首先，为了直观地查看结果，我们创建一张学生信息表：

1. % 模式：表示任意0个或者多个字符。可以匹配任意类型和长度的字符，有些情况下如果是中文，则使用两个百分号（%%）表示，例如：

   select * from student where s_name like '赵%'； ----即查询以‘赵’姓所有的学生的信息
   

   因为只有一个学生姓氏为‘赵’，所以返回结果只有一条记录：

   **返回结果：**01 号学生赵雷的信息

2. _ 模式：表示任意单个字符，匹配单个单一字符，它常用来限制表达式的字符长度语句：

比如找出名字中第二个字是“兰”的学生信息：

select * from student where s_name like '_兰'； ----找出名字中第二个字是“兰“

**返回结果：**06号学生吴兰的信息

再比如，如果我们忘记名字中的一部分，也可以用 _模式

select * from student where s_name like '王_明';
---如我们只记得姓‘王’，名字的最后一个字是‘明’，中间的部分忘记了

**返回结果：**09号学生王小明的信息

3. [ ] 模式：表示括号内所列字符的一个（类似正则表达式）。指定一个字符、字符串或者范围，要求所屁【诶的对象为它们中的一个。

   比如我们要找出，“张”姓，“李”姓，“周姓”，中，名字带“梅”的学生的信息

   select * from student where s_name like '[张李周]梅';
   

   **返回结果：**05号学生周梅的信息

   注意这里只需要有一个匹配就行，而不是返回“张李周梅”的结果

4. [^ ] :表示不在括号所列之内的单个字符，取值和[ ]相同，但它要求所匹配的对象为指定字符以外的任意一个字符。

   找出不姓“张”，“李”，“周”的张梅，李梅，周梅等；即过滤掉这些信息

select * from student where s_name like '[张李周]梅';

---

2 : 正则表达式

​ 之前用模糊查询过滤一些数据条件，通过这些查询已经能够满足开发中大部分的工作，但假设筛选条件更加复杂一点，如：需要从邮箱+手机号混合注册的用户中找出手机号码注册用户，要给他们发生短信。此时，之前的过滤方法效用不大，但正则表达式可以满足。

​ 正则表达式就是用来匹配文本的特殊字符串，比如从文本中提取电话号码，带数字的用户名，或者提取某个特定格式的文本。

​ 几乎所有编程语言都支持正则表达式，因此，学习正则表达式很有必要。

下标中的正则模式可以应用于regexp操作符中

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
.	匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用象 ‘[.\n]’ 的模式。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2	匹配 p1 或 p2 。例如，‘z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

同样，为了直观地展示结果，我们首先创建一个用户表my_user：

接下来，通过正则表达式来匹配该表中的相关信息。

2.1 匹配字符串

​ 匹配字符串是正则表达式最基础的应用。我们先给出一个例子：我们从一个用户表中查询出名字中包含有100数字的用户 。

​ SQL语句：

	select * from my_user where name regexp '100';

​ **返回结果：**得到ID为1 的用户：张三100

​

​ 这里也可以用上面的Like模糊查询来实现，那为什么还要使用正则表达式呢…？？

​ 其实是这样：如果我们需要匹配的结果不仅仅包含数字100 的用户，而是200,300,500，只要是整百的都需要匹配，或者说，我们需要所有名字中包含数字的都需要匹配。在这样的需求下， 使用Like模糊查询就十分困难。但是使用正则表达式就很简单。

​ SQL语句：

	select * from my_user where name regexp '.00';--匹配包含整百的名字，如100,300,500等

表达式中的 .00 表示任意字符，无论是数字还是字母都可以匹配，任意匹配。

​

​ 返回结果：

​

如果要匹配所有名字里面包含数字的用户:

SQL语句：

select * from my_user where name regexp '[0-9]';--匹配所有名字里面包含数字的用户

返回结果：

2.2 使用或查询

​ 如果正则表达式仅仅就是和Like有那么一点儿的差别，也就不会有这么高的地位了。下面才是显示身手的时候。

​ 或查询也叫OR查询，是条件并列查询的一种情况，类似编程语言中的if-else语句，只要有一个条件符合就会匹配。

SQL语句：

	select * from my_user where name regexp '100|500'；---查询名字中带100或500数字的用户信息

返回结果：

​ 语句查询了名字中包含了100或者500数字的用户，就是说两个数值只要匹配一个就可以返回数据。当然，也可以用给出多个情况，用竖线分割即可。如：100|500|410

​ 使用或查询的情况，有点儿类似于select中使用or条件连接的情况，你可以把它们想象成并入了一个正则表达式。

2.3 匹配多个字符之一

正则表达式中的特殊的OR匹配：

SQL语句：

	select * from my_user where name regexp '[15]';---查询名字中包含有数字1或者数字5的记录，它是[1|5]的缩写。

返回结果：

它与 select * from my_user where name regexp ‘1|5’; 返回的结果是一样的。

​ 这种，使用方括号将字符串括起来的写法，无论方括号内有多少字符串，其表达的含义都是匹配其中任意一个。 如果是[123456789]那么就代表，匹配名字中包含1或2或3或4或5或6或7或8或9的任何一个记录。

​ 当然，字符串还可以查询被否定的情况。[^12]如果在12之前加上一个^符号，那么就代表除了1或2外的字符串。

SQL语句：

	select * from my_user where name regexp '[^15]';

返回结果：

**说明：**表中张三100，虽然包含1，但是他包含了0。0不属于1或者2，所以被匹配

2.4 匹配一个范围

​ 看下我们上面有个集合[123456789]，如果每次我们写正则时候都这么写，那么岂不是很麻烦。所以，我们可以简化这种情况，使用一个范围[1-9]来代替这串字符串。如果是匹配所有数字，那么就会包含0，我们可以使用[0-9]来表示。

SQL语句：

	select * from my_user where name regexp '[0-9]';

返回结果：

​ 这个表达式将会匹配出所有名字中包含数字的记录。拆开理解就是，包含0或者1或者2…或者8或者9的记录。

​ 而且，这里的范围不仅仅只能是数字，还可以是字母。比如[a-z]就是表示从字母a到字母z的所有数字，26个字母。小写完了，还有大写[A-Z]。那么我们将其组合起来[0-9a-zA-Z]这个表达式就十分强大了，可以表示包含数字，小写字母，大写字母的所有记录。

SQL语句：

	select * from my_user where name regexp '[0-9a-zA-Z]';

返回结果：

由于my_user表中名字没有包含字母的，所以结果和上面[0-9]相同。

2.5 元字符

​ 之前匹配的内容都是单词匹配。就是如果匹配到一次就显示，匹配不到就不显示。但是，复杂的情况有时候要求匹配不止一次。假设我需要匹配名字中包含2-3位数字的记录。这个时候就需要使用一种特殊的元字符来修饰。

SQL语句：

	select * from my_user where name regexp '[0-9]{2,3}';

返回结果：

拿出这个特殊字符串[0-9]{2,3}，除去前面的[0-9]后面的{2,3}就被成为重复元字符，它的作用就是使得前面的数字重复一定的次数，前面[0-9]中出现一次，就记录一次，因此返回的结果都是出现了3次的结果。

下面列一张元字符表：

元字符	作用
*	重复0次或者多次
+	重复一次或者多次。相当于{1,}
？	重复0次或者1次
{n}	重复n次
{n,}	重复至少n次
{n,m}	重复n-m次

SQL语句：

	select * from my_user where name regexp '[0-9]*';--匹配名字包含或者不包含数字的记录

返回结果：

SQL语句：

	select * from my_user where name regexp '[0-9]{2,3}'--匹配名字内包含2位数或者3位数的记录

返回结果：

2.6 定位元字符

除了之前的重复元字符，正则还有一种特殊的定位元字符

下面列一张定位元字符表：

元字符	作用
^	文本开始
$	文本结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

​ 还记得之前区别LIKE和正则表达式的例子么？LIKE是对整个字符串进行匹配，而正则是匹配到就可以。

​ 如果现在我们需要在邮箱+手机号码混合注册的账号中，挑选出手机号码，那么我们就要对账号进行从头到尾的匹配。

​ 比如手机号码是11位数字，也就是说a11111111111不行，但是他又具备11个数字条件。所以我们要求从头开始匹配到结尾，是11位数字。

​ 现在我们重新插入一条记录，my_user表变为：

​ SQL语句：

 	select * from my_user where name regexp '^[a-z][0-9]{11}$';--匹配以a-z开头，0-9出现11次的记录

​

返回结果：

​

---

3：模糊查询与正则表达式比较

​ 讨论模糊查询与正则表达式后，那么什么情况下使用Like，什么情况下使用regexp呢？

​ 这里只需要记住区分Like查询与正则查询的重要差别即可。

​ 两个例子：

​ 1：之前的一条语句

	select * from my_user where name regexp '100';

​ 返回结果：

​

	select * from my_user where name like '100';

​ 2.Like查询的实现

​ 返回结果：

​

可以清楚地看到两者的区别：正则表达式返回了一条记录，而Like查询没有匹配到任何记录

结论:

​ Like查询匹配整个列，如果需要匹配的字符串（比如上面的100）包含在列中，那么则无法匹配成功。而正则匹配则可以匹配列值内部的值，简单来说就是它会从第一个字符开始往后匹配，只要匹配有一个成功那么就会返回记录。这是两者重要的区别。

​