MySQL学习足迹记录07--数据过滤--用正则表达式进行检索

本文用到的检索数据

 mysql> SELECT prod_name FROM products -> ORDER BY prod_name;+----------------+| prod_name |+----------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil || Bird seed || Carrots || Detonator || Fuses || JetPack 1000 || JetPack 2000 || Oil can || Safe || Sling || TNT (1 stick) || TNT (5 sticks) |+----------------+14 rows in set (0.00 sec)

1.基本字符匹配

 eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '1000' #匹配"1000" -> ORDER BY prod_name;+--------------+| prod_name |+--------------+| JetPack 1000 |+--------------+1 row in set (0.00 sec) eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '.000' #'.'表示匹配任意一个字符 -> ORDER BY prod_name;+--------------+| prod_name |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.00 sec)

2.进行OR匹配

为了搜索N个串之一，使用 ‘|’

 eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '1000 | 2000' -> ORDER BY prod_name;+--------------+| prod_name |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.00 sec)

3.匹配几个字符之一

*匹配特定的单字符，可以通过指定一组【】括起来的字符来完成

 eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '[123] Ton' -> ORDER BY prod_name;+-------------+| prod_name |+-------------+| 1 ton anvil || 2 ton anvil |+-------------+2 rows in set (0.00 sec) 等效于： mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '[1|2|3] Ton' -> ORDER BY prod_name;+-------------+| prod_name |+-------------+| 1 ton anvil || 2 ton anvil |+-------------+2 rows in set (0.00 sec)

4.否定一个字符集‘^'

 eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '[^123] Ton' -> ORDER BY prod_name;+--------------+| prod_name |+--------------+| .5 ton anvil |+--------------+1 row in set (0.00 sec)

5.匹配范围【n-m】

 eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '[1-5] Ton' -> ORDER BY prod_name;+--------------+| prod_name |+--------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil |+--------------+3 rows in set (0.00 sec)

注：以下操作所用到的表格数据

mysql> SELECT vend_name FROM vendors ORDER BY vend_name;+----------------+| vend_name |+----------------+| ACME || Anvils R Us || Furball Inc. || Jet Set || Jouets Et Ours || LT Supplies |+----------------+6 rows in set (0.00 sec)

6.匹配特殊字符，需用//为前导，即转义字符

*MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）

匹配'.'

 eg: mysql> SELECT vend_name FROM vendors -> WHERE vend_name REGEXP '.' #未用转义字符，所以不是期望的结果 -> ORDER BY vend_name;+----------------+| vend_name |+----------------+| ACME || Anvils R Us || Furball Inc. || Jet Set || Jouets Et Ours || LT Supplies |+----------------+6 rows in set (0.00 sec) 正确的应为： mysql> SELECT vend_name FROM vendors -> WHERE vend_name REGEXP '//.' -> ORDER BY vend_name;+--------------+| vend_name |+--------------+| Furball Inc. |+--------------+1 row in set (0.00 sec)

#以下7，8，9，10列出的仅作参考，无需记忆

7.匹配字符类

[:alnum:] ==> [a-zA-Z0-9]

[:alpha:] ==> [a-zA-Z]

[:blank:] ==>空格和制表符[//t]

[:cntrl:] ==>ASCII控制字符(ASCII0到31和127）

[:digit:] ==>[0-9]

[:graph:] ==>与[:print:]相同，但不包括空格

[:lower:] ==>[a-z]

[:punct:] ==>即不在[:alnum:]又不在[:cntrl:]中的任意字符

[:space:] ==>包括空格在内的任意空白字符[//f//n//r//t//v]

[:upper:] ==>[A-Z]

[:xdigit:]==>任意十六进制数[a-fA-F0-9]

[:print:] ==>任意可打印字符

8.空白元字符

//f ==>换页

//r ==>回车

//v ==>纵向制表

9.匹配多个实例

重复元字符

* ==> 0个或多个匹配

+ ==> 1个或多个匹配（等于{1,}）

? ==> 0个或1个匹配（等于{0,1}）

{n} ==> 指定数目的匹配

{n,} ==> 不小于指定数目的匹配

{n,m} ==> 匹配数目的范围（m<=255)

 eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '//([0-9] sticks?//) ' #'?'匹配它前面的任何字符的0次或1次出现 -> ORDER BY prod_name; +----------------+| prod_name |+----------------+| TNT (1 stick) || TNT (5 sticks) |+----------------+2 rows in set (0.00 sec) mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '[[:digit:]]{4}' #匹配连在一起的任意四位数字 -> ORDER BY prod_name;+--------------+| prod_name |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.00 sec)

10.定位符

*定位元字符

^ ==> 文本的开始

$ ==> 文本的结尾

[[:<:]] ==> 词的开始

[[:>:]] ==> 词的结尾

 eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '^[0-9//.]' #'^'定位到串开头，[0-9//.]表示只有在'.'或任一数字为 -> ORDER BY prod_name; #串中的第一个字符，才匹配它+--------------+| prod_name |+--------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil |+--------------+3 rows in set (0.00 sec)

11.'^'的双重用途：在集合'[]'中用来否定集合，否则，用来指串的开始处