第4天-sql通配符与正则式

一、LIKE操作符

mariadb的LIKE操作符支持以下通配符：

通配符	含义
%	任意字符出现任意次数
_	单个字符

书中出现的[](方括号)，mariadb的LIKE操作符并不支持，更精细的匹配需要使用正则式。

例子：

#查询name中含有'li'的记录
SELECT * FROM students
WHERE name LIKE('%li%');

#匹配name为三字符——'li'后跟任一个字符——的模式
SELECT * FROM students
WHERE name LIKE('li_');

要注意的是mariadb默认不区分大小写。

二、正则式

mariadb的正则模式使用REGEXP和NOT REGEXP操作符，或RLIKE和NOT RLIKE，它们是同义词。

mariadb正则式支持以下模式：

基本

模式	含义
.	任意单个字符
{m,n}	重复m到n次前面的字符，单独写{m}表示重复m次前面的字符
*	重复0个或多个前一字符，相当于{0,}
+	重复1个或多个前一字符，相当于{1,}
?	0个或一个前一字符，相当于{0,1}
|	OR匹配，匹配多个模式之一，如’zhang|li’
[]	匹配方括号内的任意字符，可以在里面使用-表示范围，如[0-9a-z]

字符类

模式	含义
[[:alnum:]]	任意字母和数字（同 [a-zA-Z0-9]）
[[:alpha:]]	任意字母（同 [a-zA-Z]）
[[:lower:]]	任意小写字母（同 [a-z]）
[[:upper:]]	任意大小字母（同 [A-Z]）
[[:digit:]]	任意数字（同[0-9]）
[[:xdigit:]]	任意十六进制数字（同 [a-fA-F0-9]）
[[:blank:]]	空格和制表（同 [\t]）
[[space:]]	包括空格在内的任意空白字符（同 [\f\n\t\r\v]）
[[:cntrl:]]	ASCII控制字符（ASCII 0到31和127）
[[:print:]]	任意可打印字符
[[:graph:]]	与[[:print:]] 相同，但不包括空格
[[:punct:]]	既不在 [[:alnum:]] 又不在 [[:cntrl:]] 中的任意字符

操作中使用[A-Z]或[a-z]，[[:lower:]]或[[:upper:]]并没有区别，因为mariadb默认不区分大小写，应该可以通过其他的设置区分，这里先不深究。

定位符

模式	含义
^	匹配行首，用在方括号内表示否定，如[^0-9]匹配非数字字符
$	匹配行尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

特殊字符

使用\\对特殊字符进行转义

模式	含义
\\-	-
\\.	.
\\其他特殊字符	其他特殊字符
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

LIKE 匹配整个串，而REGEXP匹配子串。

简单的正则表达式测试 可以在不使用数据库的情况下用SELECT来测试正则表达式。

REGEXP检查总是返回0（没有匹配）或1（匹配），可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下：

SELECT 'hello' REGEXP '[0-9]';

这个例子返回0（因为文本hello中没有数字）。

三、使用通配符的技巧

  正如所见，sql的通配符很有用。但这种功能是有代价的，即：通佩符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧：
  - 不要过分使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
  - 在却是需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来时最慢的。
  - 仔细注意通配符号的位置。如果放错地方，可能不会返回想要的数据。

——《SQL必知必会》