程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL學習足跡記錄07--數據過濾--用正則表達式進行檢索

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

編輯：MySQL綜合教程

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