mysql-4 數據檢索(2)，mysql-4數據檢索

mysql-4 數據檢索(2)，mysql-4數據檢索

用通配符進行過濾

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;