MySQL快速回顧：高級查詢操作

8.1 排序資料

檢索出的資料并不是以純粹的隨機順序顯示的，如果不排序，資料一般將以它在底層表中出現的順序顯示，這可以是資料最初添加到表中的順序，但是，如果資料后來進行過更新或洗掉，則此順序將會受到MySQL重用回收存盤空間的影響，因此，如果不明確控制的話，不能依賴該排序順序，

關系資料庫設計理論認為，如果不明確規定排序順序，則不應該假定檢索出的資料的順序有意義，

子句的解釋：一個子句通常由一個關鍵字和所提供的資料組成，SQL陳述句由子句構成，有些子句是必需的，而有些是可選的，

為了明確地排序用SELECT陳述句檢索出的資料，可使用ORDER BY子句，ORDER BY子句取一個或多個列的名字，據此對輸出進行排序，排序順序有兩種：升序（ASC關鍵字）和降序（DESC關鍵字），

格式：

# 升序（默認不寫就是升序）
SELECT field1,field2,... FROM <table_name> ORDER BY field1,field2,...;
SELECT field1,field2,... FROM <table_name> ORDER BY field1,field2,... ;
# 降序
SELECT field1,field2,... FROM <table_name> ORDER BY field1,field2,... DESC;
# 對field1降序，對field2升序（類似這樣的例子也是可以的）
SELECT field1,field2,... FROM <table_name> ORDER BY field1 DESC,field2 ASC,... ;

注意：

• 通常，BRDER BY子句中使用的列將是為顯示所選擇的列，但是，實際上并不一定要這樣，用非檢索的列排序資料也是完全合法的，
• 對于多列的排序，僅在多個行具有相同的field1值時才會按field2排序，也就是優先按ORDER BY子句后的第一個列進行排序，當遇到相同的列，則會按照ORDER BY子句后的第二列來排序，以此類推，
• 也可以先對ORDER BY子句后的第一個列進行升序或降序，對ORDER BY子句后的第二個列進行降序或升序，
• 在對文本資料性的資料進行排序時，A與a相同嗎？a位于B之前還是位于Z之后？這些問題不是理論問題，其答案取決于資料庫如何設定，
• 在字典（dictionary）排序順序中，A被視為與a相同，這是MySQL（和大多數資料庫管理系統）的默認行為，但是，許多資料庫管理員能夠在需要時改變這種行為（如果資料庫包含大量外語字符，可能必須這樣做），如果確實需要改變這種排序順序，用ORDER BY子句做不到，必須要有資料庫管理員的幫助，
• ORDER BY子句的位置，應該保證它位于FROM 子句之后， 因為很多關鍵字是可以組合一起的，比如使用LIMIT，它必須位于ORDER BY子句之后，使用子句的次序不對將產生錯誤訊息，
• 對于字串（varchar）的排序：
  • 對于英文字串的排序，一般會根據字串的首字母：大些字母>小寫字母>特殊字符>數字，如果首位相同繼續排列第二位，直到不同的位，
  • 對于中文的排序：中文字串都大于英文字串，其他的還得根據資料庫所設定的字符集，比如UTF8,GBK等，對于GBK直接排序即可，對于UTF8需要對欄位進行轉碼后排序，然后會從中文字串的第一個字符的首個字母開始排序，

# UTF8
SELECT fiedl1,... FROM <表名>
    ORDER BY CONVERT(field1 using gbk);

單列，多列的升序和降序排序例子就不說了，如果使用ORDER BY子句和LIMIT的組合，則可以找出表的一個列中的最大或最小的值，

小結：學習了如何用SELECT陳述句的ORDER BY子句對檢索出的資料進行排序，

8.2 過濾資料

如果想要按某條件從表中查詢資料，那么就需要使用WHERE子句來指定搜索條件（過濾條件）進行過濾，WHERE子句在表名（FROM子句）后給出，

格式：

SELECT  field1,field2,... FROM <表名> WHERE condition1 AND/OR condition2 ...;

• 在同時使用ORDER BY和WHERE子句時，應該讓ORDER BY位于WHERE之后，否則將會產生錯誤，
• MySQL在執行匹配時默認不區分大小寫，
• 用非檢索的列做搜索條件也是可以的，

搜索條件是一個運算式，它支持以下運算子

單值查詢：查詢學生表中名為李四的學生

SELECT stu_name, stu_sex FROM student WHERE stu_name='李四';

不匹配查詢：查詢學生表中女性的學生

SELECT stu_name, stu_sex FROM student WHERE stu_sex<>'男';

范圍查詢：查詢學生表中id從2到4的學生

SELECT stu_name, stu_sex FROM student WHERE stu_id BETWEEN 2 AND 4;

• BETWEEN匹配范圍中所有的值，包括指定的開始值和結束值，

空值檢查：空值就是NULL，空值不是0不是空字串，使用WHERE子句也可以查詢，下面的例子不是很好，但搜索條件的格式就是：IS NULL，

SELECT stu_name, stu_sex FROM student WHERE stu_sex IS NULL;

小結：介紹了如何使用SELECT陳述句的WHERE子句過濾回傳的資料，也學習了如何對相等、不相等、大于、小于、值得范圍以及NULL值等測驗，

8.3 組合WHERE子句

可以使用邏輯運算子來進行多個條件的組合查詢，

8.3.1 AND運算子

AND：用在WHERE子句中的關鍵字，表示檢索滿足所有給定條件的行，

格式：

SELECT  field1,field2,... FROM <表名> WHERE condition1 AND condition2 AND ...;

可以有多個過濾條件，

8.3.2 OR運算子

OR：用在WHERE子句中的關鍵字，表示檢索匹配任一給定條件的行，（也就是有任意一個條件滿足就輸出，不用全部同時匹配）

格式：

SELECT  field1,field2,... FROM <表名> WHERE condition1 OR condition2 OR ...;

8.3.3 計算次序

WHERE子句可包含任意數目的AND和OR運算子，并且允許兩者結合以進行復雜和高級的過濾，

但是要注意這兩個運算子的優先級問題：MySQL優先處理AND運算子.

任何時候使用具有AND和OR運算子的WHERE子句，都應該使用圓括號明確地分組運算子，不要過分依賴默認計算次序，即使它確實是你想要的結果也是如此，而且使用圓括號也沒什么壞處，還可以消除歧義，

8.3.4 IN運算子

IN：用來指定條件范圍，范圍中的每一個條件都可以進行匹配，IN取合法值的由逗號分隔的清單，全都括在圓括號中，

格式：

SELECT  field1,field2,... FROM <表名> WHERE field1 IN(n,m,...);

IN運算子跟OR運算子的功能是一樣的，

SELECT  field1,field2,... FROM <表名> WHERE field1 IN(n,m,...);
# 等于
SELECT  field1,field2,... FROM <表名> WHERE field1=n OR field1=m OR ...;

IN運算子的優點如下：

• 在使用長的合法選項清單時，IN運算子的語法更清楚且更直觀，
• 在使用IN時，計算的次序更容易管理（因為使用的運算子更少），
• IN運算子一般比OR運算子清單執行更快，
• IN的最大優點是可以包含其他SELECT陳述句，使得能夠更動態地建立WHERE子句，（子查詢以后再說）

8.3.5 NOT運算子

WHERE子句中得NOT運算子有且只有一個功能，那就是否定它之后所跟得任何條件，

MySQL支持使用NOT對IN、BETWEEN和EXIST子句取反，這與多數其他DBMS允許使用NOT對各種條件取反有很大差別，

例子：

# 查詢學生表中學生的性別不為空的學生
SELECT stu_name, stu_sex FROM student WHERE stu_sex IS NOT NULL;
# 查詢學生表中學生的姓名不為小喬的學生
SELECT stu_name, stu_sex FROM student WHERE NOT stu_name='小喬';
# 如果是帶有邏輯運算子，NOT只作用于一個搜索條件，除非用括號括起來
SELECT stu_name, stu_sex FROM student WHERE NOT (stu_name='小喬' OR stu_name='大喬');

8.4 用通配符進行過濾

前面的都是針對具體值過濾的，但有時需要進行模糊查詢，比如：查詢某個字符或子字串開頭的字串，有時查詢包含某個字符或子字串的字串，有時查詢某個字符或子字串結尾的字串，那么就需要構造一個通配符搜索模式來查詢，

通配符（wildcard）：用來匹配值的一部分的特殊字符，通配符可以在搜索模式中任意位置使用，并且可以使用多個通配符，

搜索模式（search pattern）由字面值，通配符或兩者組合構成的搜索條件

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

8.4.1 百分號通配符

百分號通配符（%）：表示任何字符出現任意次數，也就是能夠匹配出現0次以上的字符，

# 以xx開頭或以XX開頭的字串
SELECT field1,field2,... FROM <table_name> WHERE field1 LIKE 'xx%';
# 包含xx或包含XX的字串
SELECT field1,field2,... FROM <table_name> WHERE field1 LIKE '%xx%';
# 以xx結尾或以XX結尾的字串
SELECT field1,field2,... FROM <table_name> WHERE field1 LIKE '%xx';

注意：MySQL是不區分大小寫的，也就是'xx%'可以匹配'XX%'和'xx%'，

如果想要在查詢時區分大小寫，可以加個BINARY關鍵字來區分大小寫：

# 包含xx的字串
SELECT field1,field2,... FROM <table_name> WHERE BINARY field1 LIKE '%xx%';

也可以在創建表的時候，在列定義上加上BINARY關鍵字，就表示該列區分大小寫，

CREATE TABLE <table_name>(
    field1 BINARY,
    ...
);

雖然看似%通配符可以匹配任何字符，但是有一個例外，即NULL，即使用搜索模式：'%'，也不會匹配到NULL，

還得注意尾空格，尾空格可能會干擾通配符匹配，比如搜索模式為：'%xx '（有個空格）就不會匹配以xx結尾或以XX結尾的字串，而是會匹配以xx 結尾（有個空格）或以XX 結尾（有個空格）的字串，即加了尾空格表示匹配具體的值了，即尾空格，如果想要避免，最好使用函式（TRIM(搜索模式)）來去掉，(函式待說，可以先看這個去空格的函式)

SELECT stu_name FROM student WHERE stu_name LIKE TRIM('%xx ');

8.4.2 下劃線通配符

下劃線通配符（_）:只匹配單個字符，即1個，不能多也不能少，否則就沒有，

# 以xx開頭或以XX開頭長度為3的字串
SELECT field1,field2,... FROM <table_name> WHERE field1 LIKE 'xx_';
# 包含xx或包含XX長度為4的字串
SELECT field1,field2,... FROM <table_name> WHERE field1 LIKE '_xx_';
# 以xx結尾或以XX結尾長度為3的的字串
SELECT field1,field2,... FROM <table_name> WHERE field1 LIKE '_xx';

百分號通配符和下劃線通配符可以一起使用，

8.4.3 使用通配符的技巧

可能注意到了，在搜索模式中不使用通配符，即具體值也可以匹配，如下：

SELECT field1,field2,... FROM <table_name> WHERE field1 LIKE 'xx';
# 等于
SELECT field1,field2,... FROM <table_name> WHERE field1 = 'xx%';

兩者都可以搜索具體值，使用 哪種好？通配符搜索的處理一般要比前面討論的其他搜索所花時間更長，

下面給出使用通配符要記住的技巧：

• 不要過渡使用通配符，如果其他運算子能達到相同的目的，應該使用其他運算子，
• 在確實需要使用通配符時，除非絕對有必要，否則不要把它們用在搜索模式的開始處，把通配符置于搜索模式的開始處，搜索起來是最慢的，
• 仔細注意通配符的位置，如果放錯地方，可能不會回傳想要的資料，

總結目前可以優化資料庫的點子：

• 表定義資料型別的選擇；
• 不要過渡使用通配符，要使用時，除非有必要，否則別使用通配符開搜索模式的開始處，

小結：介紹了如何用AND和OR運算子組合成WHERE子句，而且還介紹了如何明確地管理計算的次序，如何使用IN和NOT運算子，

8.5 使用正則運算式進行搜索

前面的過濾例子允許使用匹配、比較和通配運算子查詢資料，對于基本的過濾，學這些就足夠了，但是隨著過濾條件的復雜性的增加，WHERE子句本身的復雜性也有必要增加，

這就是正則運算式變得有用的地方，正則運算式是用來匹配文本的特殊的串（字符集合），

下面只是做個基礎的正則運算式學習，如果要完整覆寫正則運算式的以后再整，

正則運算式的作用是匹配文本，將一個模式（正則運算式）和一個文本串進行比較，MySQL用WHERE子句對正則運算式提供了初步的支持，，使用REGEXP關鍵字指定正則運算式，過濾SELECT檢索出的資料，

MySQL僅支持多數正則運算式實作的一個很小的子集，

8.5.1 基本字符匹配

格式：

# fieldN的N表示一個整數，
SELECT field1,field2,... FROM <table_name> WHERE fieldN REGEXP '字串';

比如：

# 會匹配包含喬的姓名的學生，
SELECT stu_name FROM student WHERE stu_name REGEXP '喬';

當然這個例子也可以使用LIKE和通配符來操作，下面說一下它們的區別：

• LIKE匹配整個列，如果被匹配的文本在列值中出現，LIKE將不會找到它，相應的行也不被回傳（除非使用通配符），
• REGEXP關鍵字在列值內進行匹配，如果被匹配的文本在列值中出現，REGEXP將會找到它，相應的行將被回傳，

MySQL的正則運算式匹配還是不區分大小寫，如果要區分大小寫，使用BINARY關鍵字，上面也有說，比如：

..... WHERE BINARY fieldN REGEXP '...';
# 或
..... WHERE fieldN REGEXP BINARY  '...';

還可用使用 . （點），. （點）是正則運算式語言中一個特殊的字符，它表示匹配任意一個字符，

# fieldN的N表示一個整數，
SELECT field1,field2,... FROM <table_name> WHERE fieldN REGEXP '.字串';

注意：上面陳述句中的 .（點）是表示匹配任意一個字符，而不是表示普通鍵盤上的特殊字符.（點），待會會將怎么匹配特殊字符，

8.5.2 進行OR匹配

OR運算子前面講了，在這里的功能也是一樣：為搜索兩個串之一，但是注意寫法：

SELECT field1,field2,... FROM <table_name> WHERE fieldN REGEXP '字串1|字串2|...';

即：使用 | 表示OR運算子，

8.5.3 匹配幾個字符之一

正則運算式OR運算子的另一種寫法：[]，比如：

SELECT field1,field2,... FROM <table_name> WHERE fieldN REGEXP '[字符1字符2...] ...';

[字符1字符2...]：定義一組字符，它的意思是 匹配字符1或匹配字符2等等，

也可以匹配字串

SELECT field1,field2,... FROM <table_name> WHERE fieldN REGEXP '[字串1, 字串2...] ...';

[字串1, 字串2...]：定義一組字串，匹配字串1或匹配字串1中的字符或匹配字串2或匹配字串2中的字符等等，

[] 形式的使用主要是用于有公共的部分，比如我要查詢姓周，周后面帶有 杰 或 雨 的同學（注意：不是包含）就可以這樣使用，

SELECT stu_name FROM student WHERE stu_name REGEXP BINARY '周[雨杰]';

匹配：周杰、周雨、周杰XXX、周雨XXX等，

按上面的例子，下面的這種寫法：

SELECT stu_name FROM student WHERE stu_name REGEXP BINARY '周杰|雨';

如果這樣寫，匹配的是：周杰或雨，

上面的例子除非把字符|括在一個集合中，如：(杰|雨)，否則它將應用于整個串， 懂了為什么有OR為什么要有：[]的形式了吧，

字符集合還可以被否定，即，它們將匹配指定字符外的任何東西，只需要在集合的開始放置一個^即可，比如：[^123]表示匹配除1或2或3外的任何東西，

8.5.4 匹配范圍

集合可用來定義要匹配的一個或多個字符，例如，下面的集合將匹配數字0到9：

[0123456789]

為簡化這種型別的集合，可使用-來定義個范圍，下面的式子等同于上述數字串列：

[0-9]

范圍不限于完整的集合，[1-3]和[4-9]都是合法的范圍，

下面是一些基礎的字符集總結：

[abc]：表示可能是字符a或是字符b或是字符c中的任意一位
[^abc]：表示不是字符a或是字符b或是字符c中的任意一位
[a-z]：所有的小寫字母
[^a-z]: 不匹配所有的小寫字母
[a-zA-Z]：字母中的任意一位
[^a-zA-Z]或[^(a-zA-Z)]：不匹配字母
[A-Z]：所有的大寫字母
[^A-Z]：不匹配所有的大寫字母
[0-9]：表示任意一位數字（只有一位，則范圍為0到9）
[^0-9]：表示不是任意一位數字

8.5.5 匹配特殊字符

正則運算式語言由具有特定含義的特殊字符構成，比如前面學的.（點）、[]、|、-等，還有一些其他字符，但是這些字符是有特殊含義的，如果要匹配這些特殊字符要怎么做？

為了匹配特殊字符，必須使用\\為前導，即：\\- 表示查詢特殊字符-； \\. 表示查詢特殊字符.（點），\\\ 表示查詢特殊字符\，
這種處理稱為轉義（escaping），正則運算式內具有特殊意義的所有字符都必須以這種方式轉義，

\\也用來參考元字符（具有特殊含義的字符）：

多半正則運算式實作使用單個反斜杠轉義特殊字符，以便能使用這些字符本身，但MySQL要求兩個反斜杠（MySQL自己解釋一個，正則運算式解釋另一個）

8.5.6 匹配字符類

存在找出自己經常使用的數字、所有的字母字符或所有數字字符等的匹配，為了方便作業，可用使用預定義的字符集，稱為字符類（character class）

不用死記，忘了就看，

8.5.7 匹配多個實體

到目前為止的所有正則運算式都是試圖匹配單次出現，有時需要對匹配的數目進行更強的控制，那么就需要下面的重復元字符來完成：

比如：
匹配17級學生的所有學號，17級都是以2017開頭的，然后后面有8位任意數字

... REGEXP '2017[0-9]{8}';
# 或
... REGEXP '2017[[:digit:]]{8}';

[[:digit:]]{8}的解釋：[:digit:]匹配任意數字，因而它為數字的一個集合，{8}確切要求它前面的字符（任意數字）出現8次，所以應該寫成這樣[[:digit:]]{8}：匹配連在一起的任意4位數字，

當然也可以這樣寫：

... REGEXP '2017[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

8.5.8 定位符

目前為止的所有例子都是匹配一個串中的任意位置的文本，為了匹配特定的位置，需要定位符：

例如，如果想要找出以一個數（包括小數點開始的數）開始的所有產品，要怎么辦？簡單搜索[0-9\\.]（或[[:digit:]\\.]）嗎？這樣是不行的，因為它將在文本內的任意位置查找匹配，解決的方式就是使用^定位符，如下：

... REGEXP '^[0-9\\.]';

^有兩種用法：

1. 在集合中（用[和]定義），用它來否定改集合，比如：[^0-9] 表示查詢除任意一個數字外的串，只要有包含一個數字的就不匹配，不論位置在哪，
2. 用來指串的開始處，比如：^[0-9] 表示在文本中查找以一個數字開頭的串，

前面說過，LIKE匹配整個串而REGEXO匹配子串，利用定位符，通過用^開始每個運算式，用%結束每個運算式，可用是REGEXP的作用跟LIKE一樣，

額外：可用在不使用資料庫表的情況下用SELECT來測驗正則運算式，REGEXP檢查總是回傳0（不匹配）或1（匹配），可用用待文字串的REGEXP來測驗運算式，比如

SELECT 'hello' REGEXP '[0-9]';

結果很明顯回傳0，

小結：介紹了正則運算式的基礎知識，學習了如何在MySQL的SELECT陳述句中通過REGEXP關鍵字使用它們，

標籤：MySQL

上一篇：mysql索引創建和使用細節(一)

下一篇：MySQL快速回顧：計算欄位與函式