【MySQL必知必會】學習筆記Day3&1.23&D8-9章&P46-61頁

基本概念

• 通配符： 用來匹配值的一部分的特殊字符
• 搜索模式：由字面值、通配符或兩者組合構成的搜索條件
• LIKE：為在搜索子句中使用通配符，必須使用LIKE操作符
• %通配符:在搜索串中，%表示任何字符出現任意次數
• _通配符：下劃線只匹配單個字符而不是多個字符
• REGEXP：后所跟的東西作為正則表達式
• .：是正則表達式語言中一個特殊的字符。表示匹配任意一個字符
• |：為正則表達式的OR操作 符。它表示匹配其中之一
• [ ]：用來匹配特定的字符，[123]的意思是匹配1或2或3
• [^ ]：字符集合否定，[^123]可以匹配除這些字符123外的東西
• -：集合可用來定義要匹配的一個或多個字符，“-”來定義一個范圍

6、用通配符進行過濾

（1）LIKE操作符

• 為在搜索子句中使用通配符，必須使用LIKE操作符。LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配進行比較。

（2）百分號(%)通配符

• 在搜索串中，%表示任何字符出現任意次數
• eg：找出所有以詞jet起頭的產品

SELECT  prod_id,prod_name
FROM  products
WHERE  prod_name  LIKE 'jet%';

注：

• 區(qū)分大小寫:根據MySQL的配置方式，搜索可以是區(qū)分大小寫的

• 通配符可在搜索模式中任意位置使用，并且可以使用多個通配符

• eg:匹配任何位置包含文本anvil的值，而不論它之前或之后出現什么字符

SELECT  prod_id, prod_name
FROM  products
WHERE  prod_name  LIKE '%anvil%';

• 通配符也可以出現在搜索模式的中間
• eg：找出以s起頭以e結尾的所有產品

SELECT  prod_name
FROM  products
WHERE  prod_name  LIKE 's%e';

注：

• 除了一個或多個字符外，%還能匹配0個字符。% 代表搜索模式中給定位置的0個、1個或多個字符。
• 注意尾空格：尾空格可能會干擾通配符匹配。例如，在保存詞 anvil時，如果它后面有一個或多個空格，則子句WHERE prod_name LIKE '%anvil'將不會匹配它們，因為在最后的l 后有多余的字符。解決這個問題的一個簡單的辦法是在搜索模式最后附加一個%。還一個更好的辦法是使用函數去掉首尾空格。
• 注意NULL：雖然似乎%通配符可以匹配任何東西，但是不能匹配NULL。

（3）下劃線(_)通配符

• 下劃線只匹配單個字符而不是多個字符。

SELECT  prod_id，prod_name
FROM  products
WHERE  prod_name  LIKE '_ ton anvil';

（4）使用通配符的技巧

• 不要過度使用通配符。如果其他操作符能達到相同的目的，應該使用其他操作符
• 盡量不要在搜索模式的開始處用通配符，把通配符置于搜索模式的開始處，搜索起來是最慢的
• 仔細注意通配符的位置，如果放錯地方，可能不會返回想要的數據

7、用正則表達式進行搜索

（1） 正則表達式介紹

• 正則表達式是用來匹配文本的特殊的串(字符集合)。例如從文本文件中提取電話號碼，查找名字中間有數字的所有文件，在一個文本塊中找到所有重復的單詞， 替換一個頁面中的所有URL為這些 URL的實際HTML鏈接等

（2）使用MySQL正則表達式

• MySQL僅支持多數正則表達式實現的一個很小的子集

（3）基本字符匹配

• REGEXP后所跟的東西作為正則表達式
• eg：檢索列prod_name包含文本1000的所有行

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；

注:

• LIKE與REGEXP 在LIKE和REGEXP之間有一個重要的差別,如下，
  如果執(zhí)行下面兩條語句，會發(fā)現第一條語句不返回數據，而第二條語句返回一行。這是因為，LIKE匹配整個列。如果被匹配的文本在列值 中出現，LIKE將不會找到它，相應的行也不被返回(除非使用通配符)。而REGEXP在列值內進行匹配，如果被匹配的文本在 列值中出現，REGEXP將會找到它，相應的行將被返回。這是一 個非常重要的差別。

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；
 

注：

• 匹配不區(qū)分大小寫： MySQL中的正則表達式匹配不區(qū)分大小寫。為區(qū)分大小寫，可使用BINARY關鍵字，如WHERE prod_name REGEXP BINARY 'JetPack .000'

（4）進行OR匹配

• 為搜索兩個串之一(或者為這個串，或者為另一個串)，使用|。

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '1000|2000'
ORDER BY prod_name；

（5）匹配幾個字符之一

• 匹配任何單一字符，[ ]用來匹配特定的字符
• eg：[123]定義一組字符，它的意思是匹配1或2或3
• 其實，正則表達式[123]Ton 為[1|2|3]Ton的縮寫，也可以使用后者。

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '[123] Ton'
ORDER BY prod_name；

注：

• 如上的輸出效果，可以用'[123]Ton' 或者 '[1|2|3]Ton'，但不能是'1|2|3 Ton'，因為MySQL假定'1|2|3 Ton'的意思是'1'或 '2'或'3 ton'
• 字符集合也可以被否定，即[^123]可以匹配除這些字符123外的東西

（6）匹配范圍

• 集合可用來定義要匹配的一個或多個字符
• [0-9]：匹配數字0到9
• [a-z]：匹配任意字母字符
• eg：下面表達式意思是匹配1到5，因此返回3個匹配行，.5 ton，1 ton，2 ton，由于5 ton匹配， 所以返回.5 ton也返回。

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name；

（7）匹配特殊字符

• 為了匹配特殊字符，如.、|、[]等必須用\為前導，進行轉義,

• eg:匹配包含“.”的：

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP ' \\. '
ORDER BY prod_name；

• \\也用來引用元字符(具有特殊含義的字符)

元字符    說明
\\f      換頁 
\\n      換行 
\\r      回車 
\\t      制表 
\\v      縱向制表
\\\      \

注：

• \或\\? ,多數正則表達式實現使用單個反斜杠轉義特殊字符， 以便能使用這些字符本身。但MySQL要求兩個反斜杠(MySQL 自己解釋一個，正則表達式庫解釋另一個)。

（8）匹配字符類

• 存在找出你自己經常使用的數字、所有字母字符或所有數字字母字 符等的匹配。因此可以使用預定義的字符集，稱為字符類：

類         說明
[:alnum:]  任意字母和數字(同[a-zA-Z0-9]) 
[:alpha:]  任意字符(同[a-zA-Z])
[:blank:]  空格和制表(同[\\t])
[:cntrl:]  ASCII控制字符(ASCII 0到31和127) 
[:digit:]  任意數字(同[0-9])
[:graph:]  與[:print:]相同，但不包括空格
[:lower:]  任意小寫字母(同[a-z])
[:print:]  任意可打印字符
[:punct:]  既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]  包括空格在內的任意空白字符(同[\\f\\n\\r\\t\\v]) 
[:upper:]  任意大寫字母(同[A-Z])
[:xdigit:] 任意十六進制數字(同[a-fA-F0-9])

（9）匹配多個實例

• 以上所有正則表達式都試圖匹配單次出現。但有時需要對匹配的數目進行更強的控制。例如，可能需要尋找所有的數，不管數中包含多少數字，或者可能想尋找一個單詞并且還能夠適應一個尾隨的s等等。此時，可以用正則表達式重復元字符來完成。

元字符      說明
*          0個或多個匹配
+          1個或多個匹配(等于{1,})
?          0個或1個匹配(等于{0,1}) 
{n}        指定數目的匹配
{n,}       不少于指定數目的匹配
{n,m}      匹配數目的范圍(m不超過255)

• eg：想匹配 （1 stick）和 （5 sticks）
• 正則表達式\([0-9] sticks?\)。解說如下：\( 匹配（，[0-9]匹配任意數字，sticks?匹配stick 和sticks(s后的?使s可選，因為?匹配它前面的任何字符的0次或1次出現)，\)匹配)

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP ' \\([0-9] sticks?\\)'
ORDER BY prod_name；

• eg：想匹配連在一起的4位數字
• 解說如下： [:digit:]匹配任意數字，因而它為數字的一個集合。{4}確切地要求它前面的字符(任意數字)出現4次，所以 [[:digit:]]{4}匹配連在一起的任意4位數字。

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；

（10）定位符

• 以上所有例子都是匹配一個串中任意位置的文本。為了匹配特定位置的文本，需要使用下面的定位符：

元字符     說明
^         文本的開始 
$         文本的結尾 
[[:<:]]   詞的開始 
[[:>:]]   詞的結尾

• eg: 若想找以一個數(包括以小數點開始的數)開始的所有產品，簡單搜索[0-9\\.] 或[[:digit:]\\.])不行，因為它將在文本內任意位置查找匹配。解決辦法是使用^定位符。
• ^ 匹配串的開始。因此，^ [0-9\\.]只在.或任意數字開頭時才匹配它們。沒有^，則還要多檢索出那些中間有數字的行

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name；

注：

• ^的雙重用途：
  • 在集合中，用 [ 和 ] 定義，用 ^ 來否定該集合
  • 否則，用來指字符串的開始處
• 使REGEXP起類似LIKE的作用：LIKE和REGEXP 的不同在于，LIKE匹配整個串而REGEXP匹配子串。利用定位符，通過用^開始每個表達式，用$結束每個表達式，可以使 REGEXP的作用與LIKE一樣

【MySQL必知必會】系列筆記：
【MySQL必知必會1-4章】學習筆記Day1
【MySQL必知必會5-7章】學習筆記Day2