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

正则表达式

bitsCN.com

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

 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.'^'的双重用途：在集合'[]'中用来否定集合，否则，用来指串的开始处

bitsCN.com