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

页面导航:首页 > 数据库 > Mysql > MySQL学习足迹记录07--数据过滤--用正则表达式进行

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

来源: 作者: 时间:2016-02-18 10:28 【

MySQL学习足迹记录07--数据过滤--用正则表达式进行检索 本文用到的检索数据 mysql SELECT prod_name FROM products - ORDER BY prod_name;+----------------+| ...
MySQL学习足迹记录07--数据过滤--用正则表达式进行检索
 
      本文用到的检索数据
    > 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.'^'的双重用途:在集合'[]'中用来否定集合,否则,用来指串的开始处
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<