MySQL資料庫學習足跡記錄07–數據過濾–用正則表達式進行檢索

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.'^'的雙重用途:在集合'[]'中用來否定集合,否則,用來指串的開始處

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *