当前位置：首页 -> AI编程基础 -> 数据库编程

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

2014-11-24 17:08:10 · 作者: · 浏览: 2
标签: 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和