《MYSQL教程MySql官方手冊學習筆記2 MySql的模糊查詢和正則表達式》要點：
本文介紹了MYSQL教程MySql官方手冊學習筆記2 MySql的模糊查詢和正則表達式，希望對您有用。如果有疑問，可以聯系我們。
SQL模式匹配允許你使用“_”匹配任何單個字符,而“%”匹配任意數目字符(包括零字符).在?MySQL中,SQL的模式默認是忽略大小寫的.下面給出一些例子.注意使用SQL模式時,不能使用=或!=；而應使用LIKE或NOT LIKE比較操作符.MYSQL入門
要想找出以“b”開頭的名字：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name???| owner??| species | sex??| birth??????| death??????|
+--------+--------+---------+------+------------+------------+
| Buffy??| Harold | dog?????| f????| 1989-05-13 | NULL???????|
| Bowser | Diane??| dog?????| m????| 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
?
要想找出以“fy”結尾的名字：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name???| owner??| species | sex??| birth??????| death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat?????| f????| 1993-02-04 | NULL??|
| Buffy??| Harold | dog?????| f????| 1989-05-13 | NULL??|
+--------+--------+---------+------+------------+-------+
?
要想找出包含“w”的名字：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name?????| owner | species | sex??| birth??????| death??????|
+----------+-------+---------+------+------------+------------+
| Claws????| Gwen??| cat?????| m????| 1994-03-17 | NULL???????|
| Bowser???| Diane | dog?????| m????| 1989-08-31 | 1995-07-29 |
| Whistler | Gwen??| bird????| NULL | 1997-12-09 | NULL???????|
+----------+-------+---------+------+------------+------------+
?
要想找出正好包含5個字符的名字,使用“_”模式字符：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name??| owner??| species | sex??| birth??????| death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen???| cat?????| m????| 1994-03-17 | NULL??|
| Buffy | Harold | dog?????| f????| 1989-05-13 | NULL??|
+-------+--------+---------+------+------------+-------+
?
由MySQL提供的模式匹配的其它類型是使用擴展正則表達式.當你對這類模式進行匹配測試時,使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE,它們是同義詞).MYSQL入門
擴展正則表達式的一些字符是：MYSQL入門
??????????‘.'匹配任何單個的字符.MYSQL入門
??????????字符類“[...]”匹配在方括號內的任何字符.例如,“[abc]”匹配“a”、“b”或“c”.為了命名字符的范圍,使用一個“-”.“[a-z]”匹配任何字母,而“[0-9]”匹配任何數字.MYSQL入門
??????????“?*?”匹配零個或多個在它前面的字符.例如,“x*”匹配任何數量的“x”字符,“[0-9]*”匹配任何數量的數字,而“.*”匹配任何數量的任何字符.MYSQL入門
為了說明擴展正則表達式如何工作,下面使用REGEXP重寫上面所示的LIKE查詢：MYSQL入門
為了找出以“b”開頭的名字,使用“^”匹配名字的開始：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name???| owner??| species | sex??| birth??????| death??????|
+--------+--------+---------+------+------------+------------+
| Buffy??| Harold | dog?????| f????| 1989-05-13 | NULL???????|
| Bowser | Diane??| dog?????| m????| 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
?
如果你想強制使REGEXP比較區分大小寫,使用BINARY關鍵字使其中一個字符串變為二進制字符串.該查詢只匹配名稱首字母的小寫‘b'.MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name REGEXP BINARY '^b';
?
為了找出以“fy”結尾的名字,使用“$”匹配名字的結尾：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name???| owner??| species | sex??| birth??????| death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat?????| f????| 1993-02-04 | NULL??|
| Buffy??| Harold | dog?????| f????| 1989-05-13 | NULL??|
+--------+--------+---------+------+------------+-------+
?
為了找出包含一個“w”的名字,使用以下查詢：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name?????| owner | species | sex??| birth??????| death??????|
+----------+-------+---------+------+------------+------------+
| Claws????| Gwen??| cat?????| m????| 1994-03-17 | NULL???????|
| Bowser???| Diane | dog?????| m????| 1989-08-31 | 1995-07-29 |
| Whistler | Gwen??| bird????| NULL | 1997-12-09 | NULL???????|
+----------+-------+---------+------+------------+------------+
?
既然如果一個正則表達式出現在值的任何地方,其模式匹配了,就不必在先前的查詢中在模式的兩側放置一個通配符以使得它匹配整個值,就像你使用了一個SQL模式那樣.MYSQL入門
為了找出包含正好5個字符的名字,使用“^”和“$”匹配名字的開始和結尾,和5個“.”實例在兩者之間：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name??| owner??| species | sex??| birth??????| death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen???| cat?????| m????| 1994-03-17 | NULL??|
| Buffy | Harold | dog?????| f????| 1989-05-13 | NULL??|
+-------+--------+---------+------+------------+-------+
?
你也可以使用“{n}”“重復n次”操作符重寫前面的查詢：MYSQL入門
?MYSQL入門
mysql>?SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name??| owner??| species | sex??| birth??????| death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen???| cat?????| m????| 1994-03-17 | NULL??|
| Buffy | Harold | dog?????| f????| 1989-05-13 | NULL??|
+-------+--------+---------+------+------------+-------+轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5240.html
