MySql官方手冊進修筆記2 MySql的隱約查詢和正則表達式。本站提示廣大學習愛好者：（MySql官方手冊進修筆記2 MySql的隱約查詢和正則表達式）文章只能為提供參考，不一定能成為您想要的結果。以下是MySql官方手冊進修筆記2 MySql的隱約查詢和正則表達式正文
SQL形式婚配許可你應用“_”婚配任何單個字符，而“%”婚配隨意率性數量字符(包含零字符)。在 MySQL中，SQL的形式默許是疏忽年夜小寫的。上面給出一些例子。留意應用SQL形式時，不克不及應用=或!=；而應應用LIKE或NOT LIKE比擬操作符。
要想找出以“b”開首的名字：
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> 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> 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> 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，它們是同義詞)。
擴大正則表達式的一些字符是：
· ‘.'婚配任何單個的字符。
· 字符類“[...]”婚配在方括號內的任何字符。例如，“[abc]”婚配“a”、“b”或“c”。為了定名字符的規模，應用一個“-”。“[a-z]”婚配任何字母，而“[0-9]”婚配任何數字。
· “ * ”婚配零個或多個在它後面的字符。例如，“x*”婚配任何數目的“x”字符，“[0-9]*”婚配任何數目的數字，而“.*”婚配任何數目的任何字符。
為了解釋擴大正則表達式若何任務，上面應用REGEXP重寫下面所示的LIKE查詢：
為了找出以“b”開首的名字，應用“^”婚配名字的開端：
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> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
為了找出以“fy”開頭的名字，應用“$”婚配名字的開頭：
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> 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形式那樣。
為了找出包括正好5個字符的名字，應用“^”和“$”婚配名字的開端和開頭，和5個“.”實例在二者之間：
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> 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 |
+-------+--------+---------+------+------------+-------+
