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

偏执的太偏执、 2022-01-10 23:25 166阅读 0赞

**本文用到的检索数据  
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; \#****串中的第一个字符，才匹配它**