MySQL入門系列：查詢簡介（二）

過濾資料

我們上邊介紹的student_info、student_score表中的記錄都很少，但是實際應用中的表裡可能儲存幾千萬條，甚至上億條記錄。而且我們通常並不是對所有的記錄都感興趣，只是想查詢到符合某些條件的那些記錄。比如我們只想查詢名字為範劍的學生基本資訊，或者計算機學院的學生都有哪些什麼的，這些條件也被稱為搜尋條件或者過濾條件，當某條記錄符合搜尋條件時，它將被放入結果集中。

簡單搜尋條件

我們需要把搜尋條件放在WHERE語句中，比如我們想查詢student_info表中名字是範劍的學生的一些資訊，可以這麼寫：

mysql> SELECT number, name, id_number, major FROM student_info WHERE name = '範劍';
+----------+--------+--------------------+-----------------+
| number   | name   | id_number          | major           |
+----------+--------+--------------------+-----------------+
| 20180105 | 範劍   | 181048199308156368 | 飛行器設計      |
+----------+--------+--------------------+-----------------+
1 row in set (0.01 sec)

mysql>
複製程式碼

這個例子中的搜尋條件就是name = '範劍'，也就是當記錄中的name列的值是'範劍'的時候，該條記錄的number、name、id_number、major這些欄位才可以被放入結果集。像name = '範劍'這種搜尋條件稱為精確匹配，=稱為條件操作符。我們看MySQL中都有哪些簡單的條件操作符：

操作符	示例	描述
=	a = b	等於
<>或者!=	a <> b	不等於
<	a < b	小於
<=	a <= b	不大於
>	a > b	大於
>=	a >= b	不小於
BETWEEN	a BETWEEN b AND c	滿足 b <= a <= c
NOT BETWEEN	a NOT BETWEEN b AND c	不滿足 b <= a <= c

我們想查詢學號大於20180103的學生資訊可以這麼寫：

mysql> SELECT number, name, id_number, major FROM student_info WHERE number > 20180103;
+----------+-----------+--------------------+-----------------+
| number   | name      | id_number          | major           |
+----------+-----------+--------------------+-----------------+
| 20180104 | 史珍香    | 141992199701078600 | 軟體工程        |
| 20180105 | 範劍      | 181048199308156368 | 飛行器設計      |
| 20180106 | 朱逸群    | 197995199501078445 | 電子資訊        |
+----------+-----------+--------------------+-----------------+
3 rows in set (0.01 sec)

mysql>
複製程式碼

查詢專業不是電腦科學與工程的一些學生資訊可以這麼寫：

mysql> SELECT number, name, id_number, major FROM student_info WHERE major != '電腦科學與工程';
+----------+-----------+--------------------+-----------------+
| number   | name      | id_number          | major           |
+----------+-----------+--------------------+-----------------+
| 20180103 | 範統      | 17156319980116959X | 軟體工程        |
| 20180104 | 史珍香    | 141992199701078600 | 軟體工程        |
| 20180105 | 範劍      | 181048199308156368 | 飛行器設計      |
| 20180106 | 朱逸群    | 197995199501078445 | 電子資訊        |
+----------+-----------+--------------------+-----------------+
4 rows in set (0.00 sec)

mysql>
複製程式碼

需要注意的是BETWEEN ... AND ...操作符的使用，它表示一個範圍，比方說我們想查詢學號在20180102~20180104間的學生資訊，可以這麼寫：

mysql> SELECT number, name, id_number, major FROM student_info WHERE number BETWEEN 20180102 AND 20180104;
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20180102 | 杜琦燕    | 151008199801178529 | 電腦科學與工程         |
| 20180103 | 範統      | 17156319980116959X | 軟體工程                 |
| 20180104 | 史珍香    | 141992199701078600 | 軟體工程                 |
+----------+-----------+--------------------+--------------------------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

如果想查詢指定範圍之外的資料記錄，可以使用NOT BETWEEN ... AND ...的語法，比如這樣：

mysql> SELECT number, name, id_number, major FROM student_info WHERE number NOT BETWEEN 20180102 AND 20180104;
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子騰    | 158177199901044792 | 電腦科學與工程         |
| 20180105 | 範劍      | 181048199308156368 | 飛行器設計               |
| 20180106 | 朱逸群    | 197995199501078445 | 電子資訊                 |
+----------+-----------+--------------------+--------------------------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

這樣就可以查出學號不在20180102~20180104這個區間內的所有學生資訊。

多值匹配

有時候指定的匹配值並不是單個值，而是一個列表，只要匹配到列表中的某一項就算匹配成功，這種情況可以使用IN操作符：

操作符	示例	描述
IN	a IN (b1, b2, ...)	a是b1, b2, ... 中的某一個
NOT IN	a NOT IN (b1, b2, ...)	a不是b1, b2, ... 中的任意一個

比如我們想查詢軟體工程和飛行器設計專業的學生資訊，可以這麼寫：

mysql> SELECT number, name, id_number, major FROM student_info WHERE major IN ('軟體工程', '飛行器設計');
+----------+-----------+--------------------+-----------------+
| number   | name      | id_number          | major           |
+----------+-----------+--------------------+-----------------+
| 20180103 | 範統      | 17156319980116959X | 軟體工程        |
| 20180104 | 史珍香    | 141992199701078600 | 軟體工程        |
| 20180105 | 範劍      | 181048199308156368 | 飛行器設計      |
+----------+-----------+--------------------+-----------------+
3 rows in set (0.01 sec)

mysql>
複製程式碼

如果想查詢不是這兩個專業的學生的資訊，可以這麼寫：

mysql> SELECT number, name, id_number, major FROM student_info WHERE major NOT IN ('軟體工程', '飛行器設計');
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子騰    | 158177199901044792 | 電腦科學與工程         |
| 20180102 | 杜琦燕    | 151008199801178529 | 電腦科學與工程         |
| 20180106 | 朱逸群    | 197995199501078445 | 電子資訊                 |
+----------+-----------+--------------------+--------------------------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

NULL值檢查

我們前邊說過，NULL代表沒有值，意味著你並不知道該列應該填入什麼資料，在判斷某一列是否為NULL的時候並不能單純的使用=操作符，而是需要專業判斷值是否是NULL的操作符：

操作符	示例	描述
IS NULL	a IS NULL	a的值是NULL
IS NOT NULL	a IS NOT NULL	a的值不是NULL

比如我們想看一下student_info表的name列是NULL的學生記錄有哪些，可以這麼寫：

mysql> SELECT number, name, id_number, major FROM student_info WHERE name IS NULL;
Empty set (0.00 sec)

mysql>
複製程式碼

由於所有記錄的name列都不是NULL值，所以最後結果是空的，我們看一下查詢name列不是NULL值的方式：

mysql> SELECT number, name, id_number, major FROM student_info WHERE name IS NOT NULL;
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子騰    | 158177199901044792 | 電腦科學與工程         |
| 20180102 | 杜琦燕    | 151008199801178529 | 電腦科學與工程         |
| 20180103 | 範統      | 17156319980116959X | 軟體工程                 |
| 20180104 | 史珍香    | 141992199701078600 | 軟體工程                 |
| 20180105 | 範劍      | 181048199308156368 | 飛行器設計               |
| 20180106 | 朱逸群    | 197995199501078445 | 電子資訊                 |
+----------+-----------+--------------------+--------------------------+
6 rows in set (0.00 sec)

mysql>
複製程式碼

name列不是NULL值的記錄就被查詢出來啦！

再次強調一遍，不能直接使用普通的操作符來與NULL值進行比較，必須使用IS NULL或者IS NOT NULL！

多個搜尋條件的查詢

上邊介紹的都是指定單個的搜尋條件的查詢，我們也可以在一次查詢中指定多個搜尋條件。

AND操作符

在給定多個搜尋條件的時候，我們有時需要某條記錄只有在符合所有搜尋條件的時候，這條記錄才可以被加入到結果集當中，這種情況我們可以使用AND操作符來連線多個搜尋條件。比如我們想從student_score表中找出科目為'母豬的產後護理'並且成績大於75分的記錄，可以這麼寫：

mysql> SELECT * FROM student_score WHERE subject = '母豬的產後護理' AND score > 75;
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180101 | 母豬的產後護理        |    78 |
| 20180102 | 母豬的產後護理        |   100 |
+----------+-----------------------+-------+
2 rows in set (0.00 sec)

mysql>
複製程式碼

其中的subject = '母豬的產後護理'和score > 75是兩個搜尋條件，我們使用AND操作符把這兩個搜尋條件連線起來表示只有當兩個條件都滿足的記錄才能被加入到結果集。

OR操作符

在給定多個搜尋條件的時候，我們有時需要某條記錄在符合某一個搜尋條件的時候，這條記錄就可以被加入到結果集當中，這種情況我們可以使用OR操作符來連線多個搜尋條件。比如我們想從student_score表中找出成績大於95分或者小於55分的記錄，可以這麼寫：

mysql> SELECT * FROM student_score WHERE score > 95 OR score < 55;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 母豬的產後護理              |   100 |
| 20180102 | 論薩達姆的戰爭準備          |    98 |
| 20180104 | 論薩達姆的戰爭準備          |    46 |
+----------+-----------------------------+-------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

更復雜的搜尋條件的組合

如果我們需要在某個查詢中指定很多的搜尋條件，比方說我們想從student_score表中找出課程為'論薩達姆的戰爭準備'，並且成績大於95分或者小於55分的記錄，那我們可能會這麼寫：

mysql> SELECT * FROM student_score WHERE score > 95 OR score < 55 AND subject = '論薩達姆的戰爭準備';
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 母豬的產後護理              |   100 |
| 20180102 | 論薩達姆的戰爭準備          |    98 |
| 20180104 | 論薩達姆的戰爭準備          |    46 |
+----------+-----------------------------+-------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

為什麼結果中仍然會有'母豬的產後護理'課程的記錄呢？因為：AND操作符的優先順序預設高於OR操作符，也就是說在判斷某條記錄是否符合條件時會先執行AND操作符兩邊的搜尋條件。所以

score > 95 OR score < 55 AND subject = '論薩達姆的戰爭準備'
複製程式碼

可以被看作下邊這兩個條件中任一條件成立則整個式子成立：

1. score > 95

2. score < 55 AND subject = '論薩達姆的戰爭準備'

因為結果集中subject是'母豬的產後護理'的記錄中score值為100，符合第1個條件，所以整條記錄會被加到結果集中。為了避免這種尷尬，在一個查詢中有多個搜尋條件時最好使用小括號()來顯式的指定各個搜尋條件的執行順序，比如上邊的例子可以寫成下邊這樣：

mysql> SELECT * FROM student_score WHERE (score > 95 OR score < 55) AND subject = '論薩達姆的戰爭準備';
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 論薩達姆的戰爭準備          |    98 |
| 20180104 | 論薩達姆的戰爭準備          |    46 |
+----------+-----------------------------+-------+
2 rows in set (0.00 sec)

mysql>
複製程式碼

萬用字元

有時候我們並不能精確的描述我們要查詢的東西，比方說我們只是想看看姓'杜'的學生資訊，而不能精確的描述出這些姓'杜'的同學的完整姓名，我們稱這種查詢為模糊查詢。MySQL中使用下邊這兩個操作符來支援模糊查詢：

操作符	示例	描述
LIKE	a LIKE b	a匹配b
NOT LIKE	a NOT LIKE b	a不匹配b

既然我們不能完整描述要查詢的資訊，那就用某個符號來替代這些模糊的資訊，這個符號就被稱為萬用字元。MySQL中支援下邊這兩個萬用字元：

1. %：代表任意一個字串。

   比方說我們想查詢student_info表中name以'杜'開頭的記錄，我們可以這樣寫：

   mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '杜%';
   +----------+-----------+--------------------+--------------------------+
   | number   | name      | id_number          | major                    |
   +----------+-----------+--------------------+--------------------------+
   | 20180101 | 杜子騰    | 158177199901044792 | 電腦科學與工程         |
   | 20180102 | 杜琦燕    | 151008199801178529 | 電腦科學與工程         |
   +----------+-----------+--------------------+--------------------------+
   2 rows in set (0.00 sec)
   
   mysql>
   複製程式碼

   或者我們只知道學生名字裡邊包含了一個'香'字，那我們可以這麼查：

   mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '%香%';
   +----------+-----------+--------------------+--------------+
   | number   | name      | id_number          | major        |
   +----------+-----------+--------------------+--------------+
   | 20180104 | 史珍香    | 141992199701078600 | 軟體工程     |
   +----------+-----------+--------------------+--------------+
   1 row in set (0.00 sec)
   
   mysql>
   複製程式碼

2. _：代表任意一個字元。

   有的時候我們知道要查詢的字串中有多少個字元，而使用%時匹配的範圍太大，我們就可以用_來做萬用字元。就像是支付寶的萬能福卡，一張萬能福卡能且只能代表任意一張福卡(也就是它不能代表多張福卡)。

   比方說我們想查詢姓'範'，並且姓名只有2個字元的記錄，可以這麼寫：

   mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '範_';
   +----------+--------+--------------------+-----------------+
   | number   | name   | id_number          | major           |
   +----------+--------+--------------------+-----------------+
   | 20180103 | 範統   | 17156319980116959X | 軟體工程        |
   | 20180105 | 範劍   | 181048199308156368 | 飛行器設計      |
   +----------+--------+--------------------+-----------------+
   2 rows in set (0.00 sec)
   
   mysql>
   複製程式碼

   不過下邊這個查詢卻什麼都沒有查到：

   mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '杜_';
   Empty set (0.00 sec)
   
   mysql>
   複製程式碼

   這是因為一個_只能代表一個字元（%是代表任意一個字串），並且student_info表中並沒有姓'杜'並且姓名長度是2個字元的記錄，所以這麼寫是查不出東西的。

轉義萬用字元

如果我們匹配的字串中就包含普通字元'%'或者'_'該咋辦，怎麼區分它是一個萬用字元還是一個普通字元呢？

答：如果匹配字串中需要普通字元'%'或者'_'的話，需要在它們前邊加一個反斜槓\以和萬用字元區分開來：

• '\%'代表普通字元'%'
• '\_'代表普通字元'_' 比方說這樣：

mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '範\_';
Empty set (0.00 sec)
    
mysql>
複製程式碼

由於student_info表中沒有叫範_的學生，所以查詢結果為空。

注意事項

使用萬用字元時需要特別注意一下，萬用字元不能代表NULL，如果需要匹配NULL的話，需要使用IS NULL或者IS NOT NULL！

小冊

本系列專欄都是MySQL入門知識，想看進階知識可以到小冊中檢視：《MySQL是怎樣執行的：從根兒上理解MySQL》的連結 。小冊的內容主要是從小白的角度出發，用比較通俗的語言講解關於MySQL進階的一些核心概念，比如記錄、索引、頁面、表空間、查詢優化、事務和鎖等，總共的字數大約是三四十萬字，配有上百幅原創插圖。主要是想降低普通程式設計師學習MySQL進階的難度，讓學習曲線更平滑一點～