用通配符進行過濾 like操作符 %通配符 %可以匹配任意字元 下劃線通配符 下劃線只可以匹配一個字元 用正則表達式進行搜索 基本字元匹配 檢索prod_name包含文本1000的所有行 註意 SELECT prod_name FROM products WHERE prod_name LIKE ' ...
用通配符進行過濾
like操作符 %通配符 %可以匹配任意字元
SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%'
SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';
下劃線通配符 下劃線只可以匹配一個字元
SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil';
用正則表達式進行搜索
基本字元匹配
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
檢索prod_name包含文本1000的所有行
SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
註意
SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
為什麼第一like的語句會顯示為空呢，這是因為like在匹配整個列，如果被匹配的文本在列值中出現，like將不會找它，相應的行也不會被返回，除非是用通配符；而REGEXP在列值內進行匹配，如果被匹配的文本在列值中出現，REGEXP將會找到它，相應的行將被返回，這是一個非常重要的差別。
進行OR匹配
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
匹配範圍 [1-9]
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name; .是匹配任意字元
為了匹配特殊字元，需要使用\\為前導， \\- 表示查找-
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
| 元字元 | 說明 |
| * | 0個或者多個匹配 |
| + | 1個或多個匹配（等於{1，}） |
| ？ | 0個或者1個匹配 （等於{0,1}） |
| {n} | 制定數目匹配 |
| {n,} | 不少於制定數目匹配 |
| {n,m} | 匹配數目的範圍n到m m不超過255 |
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
sticks?匹配stick 或者sticks \\( 匹配小括弧
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;
| 類 | 說明 |
| [:alnum:] | 任意字母和數字（同[a-zA-Z0-9]） |
| [:alpha:] | 任意字元 （同[a-zA-Z]） |
| [:blank:] | 空格和字元表 (同 \\t) |
| [:cntrl:] | ASCLL控制字元， |
| [:digit:] | 任意數字 (同[0-9]) |
| [:graph:] | 與print相同，但是不包含空格 |
| [:lower:] | 任意小寫字母 同（[a-z]） |
| [:pirnt:] | 任意可以列印的字元 |
| [:upper:] | 任意大寫字母 同[A-Z] |
| [:xdigit:] | 任意十六進位數字（同[a-fA-F0-9]） |
定位符
| 元字元 | 說明 |
| ^ | 文本的開始 |
| & | 文本的結尾 |
| [[:<:]] | 詞的開始 |
| [[:>:]] | 詞的結尾 |
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
以一個數字（包括小數點開始的數）開始的所有產品
SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%'; SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%'; SELECT prod_name FROM products WHERE prod_name LIKE 's%e'; SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil'; SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name; SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name; SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name; SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
