mysql学习--mysql必知必会1
例如以下为mysql必知必会第九章開始:
正則表達式用于匹配特殊的字符集合。mysql通过where子句对正則表達式提供初步的支持。
keywordregexp用来表示后面跟的东西作为正則表達式处理。
(.)是正則表達式的一个符号,表示匹配随意一个字符:
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.14 sec)
|匹配符:
表示匹配当中之中的一个
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)
[]匹配符: 匹配几个字符之中的一个
2 rows in set (0.00 sec) mysql> select prod_name -> from products -> where prod_name regexp '[123] Ton' -> ; +-------------+ | 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-5] Ton'; +--------------+ | prod_name | +--------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | +--------------+ 3 rows in set (0.02 sec)
(^)否定匹配符:
mysql> select prod_name from products where prod_name regexp '[^1-3] Ton'; +--------------+ | prod_name | +--------------+ | .5 ton anvil | +--------------+ 1 row in set (0.00 sec)
匹配特殊字符,必须用\\为前导。
mysql> select prod_name from products where prod_name regexp '\\.' ; +--------------+ | prod_name | +--------------+ | .5 ton anvil | +--------------+ 1 row in set (0.00 sec)
匹配字符类:
mysql> select prod_name from products where prod_name REGEXP '\\([0-9] sticks?\\)' order by prod_name; +----------------+ | prod_name | +----------------+ | TNT (1 stick) | | TNT (5 sticks) | +----------------+ 2 rows in set (0.05 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)
定位符使用方法:
mysql> select prod_name -> from products -> where prod_name REGEXP '^[0-9\\.]' -> order by prod_name; +--------------+ | prod_name | +--------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | +--------------+ 3 rows in set (0.00 sec)