MySQL - 第七章 | 多條件過濾資料

準備

在開始之前，我們先調整一下products這張表的結構和內容，後面學習中的過程範例會比較好呈現出來。

SQL：(主要多一個廠商ID還有調整prod_name可以為NULL)

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `vend_id` int(11) NOT NULL,
  `price` int(11) NOT NULL DEFAULT '0',
  `prod_name` varchar(64) DEFAULT '',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4;

INSERT INTO `products` (`id`, `price`, `prod_name`, `create_time`, `vend_id`) VALUES
('1', '100', 'underwear', '2020-06-13 04:53:07', '1001'),
('2', '222', 'boxers', '2020-06-13 04:53:07', '1002'),
('3', '111', 'sleepwear', '2020-06-13 04:53:07', '1002'),
('4', '20', 'socks', '2020-06-13 04:53:07', '1003'),
('5', '55', 'slippers', '2020-06-13 04:53:07', '1005'),
('6', '100', 'sandals', '2020-06-13 04:53:07', '1001'),
('7', '599', 'flip_flops', '2020-06-13 04:53:07', '1006'),
('8', '666', 'pants', '2020-06-13 04:53:07', '1001'),
('9', '399', 'long_sleeve_shirt', '2020-06-13 04:53:07', '1006'),
('10', '1000', 'blazer', '2020-06-13 04:53:07', '1006'),
('11', '9999', 'sweater', '2020-06-13 04:53:07', '1001'),
('12', '345', 'tie', '2020-06-13 04:53:07', '1006'),
('13', '123', 'dress_shoes', '2020-06-13 04:53:07', '1004'),
('14', '654', 'T-shirt', '2020-06-13 04:53:07', '1002'),
('15', '643', 'polo', '2020-06-13 04:53:07', '1005'),
('16', '234', 'short_sleeve_shirt', '2020-06-13 04:53:07', '1002'),
('17', '1233', 'jeans', '2020-06-13 04:53:07', '1005'),
('18', '877', 'shorts', '2020-06-13 04:53:07', '1006'),
('19', '236', 'swimsuit', '2020-06-13 04:53:07', '1001'),
('20', '724', 'casual_shoes', '2020-06-13 04:53:07', '1005'),
('21', '123', 'jacket', '2020-06-13 04:53:07', '1004'),
('22', '690', 'coat', '2020-06-13 04:53:07', '1004'),
('23', '890', 'raincoat', '2020-06-13 04:53:07', '1003'),
('24', '777', 'hat', '2020-06-13 04:53:07', '1006'),
('25', '3000', 'gloves', '2020-06-13 04:53:07', '1003'),
('26', '1234', 'scarf', '2020-06-13 04:53:07', '1004'),
('27', '9999', 'belt', '2020-06-13 04:53:08', '1003');

組合WHERE子句

前一章節WHERE子句在過濾資料時使用的都是單一的條件。為了進行更強的過濾控制，MySQL允許給出多個WHERE子句。這些子句可以兩種方式使用:

AND子句的方式
OR子句的方式

術語：作符(operator) 用來聯結或改變WHERE子句中的子句的關鍵字。也稱為邏輯操作符(logical operator)。

AND操作符

為了通過不止一個列進行過濾，可使用AND操作符給WHERE子句附加條件。下面的程式碼給出了一個例子。

mysql> select id, price, prod_name from products where vend_id = 1003 and price > 500;
+----+-------+-----------+
| id | price | prod_name |
+----+-------+-----------+
| 23 |   890 | raincoat  |
| 25 |  3000 | gloves    |
| 27 |  9999 | belt      |
+----+-------+-----------+
3 rows in set (0.01 sec)

分析：此SQL語句檢索由供應商1003製造且價格大於500元的所有產品的名稱和價格。這條SELECT語句中的WHERE子句包含兩個條件，並且用AND關鍵字聯結它們。AND指示DBMS只返回滿足所有給定條件的行。如果某個產品由供應商1003製造，但它的價格低於500元，則不檢索它。類似，如果產品價格大於500元，但不是由指定供應商製造的也不被檢索。

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

例子中使用了只包含一個關鍵字AND的語句，把兩個過濾條件組合在一起。還可以添加多個過濾條件，每添加一條就要使用一個AND。

OR操作符

OR操作符與AND操作符不同，它指示MySQL檢索匹配任一條件的行。

mysql> select id, price, prod_name from products where vend_id = 1003 or vend_id = 1002;
+----+-------+--------------------+
| id | price | prod_name          |
+----+-------+--------------------+
|  2 |   222 | boxers             |
|  3 |   111 | sleepwear          |
|  4 |    20 | socks              |
| 14 |   654 | T-shirt            |
| 16 |   234 | short_sleeve_shirt |
| 23 |   890 | raincoat           |
| 25 |  3000 | gloves             |
| 27 |  9999 | belt               |
+----+-------+--------------------+
8 rows in set (0.00 sec)

分析：此SQL語句檢索由任一個指定供應商製造的所有產品的產品名和價格。OR操作符告訴DBMS匹配任一條件而不是同時匹配兩個條件。如果這裡使用的是AND操作符，則沒有資料返回(此時創建的WHERE子句不會檢索到匹配的產品)。

1 2	mysql> select id, price, prod_name from products where vend_id = 1003 and vend_id = 1002; Empty set (0.00 sec)

術語：OR WHERE子句中使用的關鍵字，用來表示檢索匹配任一給定條件的行。

計算次序

WHERE可包含任意數目的AND和OR操作符。允許兩者結合以進行複雜和高級的過濾。

但是，組合AND和OR帶來了一個有趣的問題。為了說明這個問題。假如需要列出價格為500元以上且由1002或1003製造的所有產品。

mysql> select id, price, prod_name from products where vend_id = 1002 or vend_id = 1003 and price > 500;
+----+-------+--------------------+
| id | price | prod_name          |
+----+-------+--------------------+
|  2 |   222 | boxers             |
|  3 |   111 | sleepwear          |
| 14 |   654 | T-shirt            |
| 16 |   234 | short_sleeve_shirt |
| 23 |   890 | raincoat           |
| 25 |  3000 | gloves             |
| 27 |  9999 | belt               |
+----+-------+--------------------+
7 rows in set (0.00 sec)

分析：返回的行中有價格小於500元，顯然，返回的行未按預期的進行過濾。為什麼會這樣呢?原因在於計算的次序。SQL(像多數語言一樣)在處理OR操作符前，優先處理AND操作符。當SQL看到上述WHERE子句時，它理解為：

由供應商1003製造的任何價格為500元以上的產品；
或者由供應商1002製造的任何產品，而不管其價格如何。

換句話說，由於AND在計算次序中優先級更高，操作符被錯誤地組合了。

此問題的解決方法是使用圓括號明確地分組相應的操作符。

mysql> select id, price, prod_name from products where (vend_id = 1002 or  vend_id = 1003) and price > 500;
+----+-------+-----------+
| id | price | prod_name |
+----+-------+-----------+
| 14 |   654 | T-shirt   |
| 23 |   890 | raincoat  |
| 25 |  3000 | gloves    |
| 27 |  9999 | belt      |
+----+-------+-----------+
4 rows in set (0.00 sec)

分析：這條SELECT語句與前一條的唯一差別是，這條語句中，前兩個條件用圓括號括了起來。因為圓括號具有較AND或OR操作符高的計算次序，DBMS首先過濾圓括號內的OR條件。這時，SQL語句變成了選擇由供應商1002或1003製造的且價格都在500元以上的任何產品，這正是我們所希望的。

提示：在WHERE子句中使用圓括號任何時候使用具有AND和OR操作符的WHERE子句，都應該使用圓括號明確地分組操作符。不要過分依賴默認計算次序，即使它確實是你想要的東西也是如此。使用圓括號沒有什麼壞處，它能消除歧義。

IN操作符

圓括號在WHERE子句中還有另外一種用法。IN操作符用來指定條件範圍，範圍中的每個條件都可以進行匹配。IN取合法值的由逗號分隔的清單，全都括在圓括號中。

mysql> select prod_name, price from products where vend_id in (1002, 1003) order by prod_name;
+--------------------+-------+
| prod_name          | price |
+--------------------+-------+
| belt               |  9999 |
| boxers             |   222 |
| gloves             |  3000 |
| raincoat           |   890 |
| short_sleeve_shirt |   234 |
| sleepwear          |   111 |
| socks              |    20 |
| T-shirt            |   654 |
+--------------------+-------+
8 rows in set (0.01 sec)

分析：此SELECT語句檢索供應商1002和1003製造的所有產品。IN操作符後跟由逗號分隔的合法值清單，整個清單必須括在圓括號中。

另外IN操作符完成與OR相同的功能。

mysql> select prod_name, price from products where vend_id = 1002 or vend_id = 1003 order by prod_name;
+--------------------+-------+
| prod_name          | price |
+--------------------+-------+
| belt               |  9999 |
| boxers             |   222 |
| gloves             |  3000 |
| raincoat           |   890 |
| short_sleeve_shirt |   234 |
| sleepwear          |   111 |
| socks              |    20 |
| T-shirt            |   654 |
+--------------------+-------+
8 rows in set (0.00 sec)

為什麼要使用IN操作符?其優點具體如下：

在使用長的合法選項清單時，IN操作符的語法更清楚且更直觀。
在使用IN時，計算的次序更容易管理(因為使用的操作符更少)。
IN操作符一般比OR操作符清單執行更快。
IN的最大優點是可以包含其他SELECT語句，使得能夠更動態地建立WHERE子句。

術語：IN WHERE子句中用來指定要匹配值的清單的關鍵字，功能與OR相當。

NOT操作符

WHERE子句中的NOT操作符有且只有一個功能，那就是否定它之後所跟的任何條件。

術語：NOT WHERE子句中用來否定後跟條件的關鍵字。

下面的例子說明NOT的使用。為了列出除1002和1003之外的所有供應商製造的產品。

mysql> select prod_name, price from products where vend_id not in (1002, 1003) order by prod_name;
+-------------------+-------+
| prod_name         | price |
+-------------------+-------+
| blazer            |  1000 |
| casual_shoes      |   724 |
| coat              |   690 |
| dress_shoes       |   123 |
| flip_flops        |   599 |
| hat               |   777 |
| jacket            |   123 |
| jeans             |  1233 |
| long_sleeve_shirt |   399 |
| pants             |   666 |
| polo              |   643 |
| sandals           |   100 |
| scarf             |  1234 |
| shorts            |   877 |
| slippers          |    55 |
| sweater           |  9999 |
| swimsuit          |   236 |
| tie               |   345 |
| underwear         |   100 |
+-------------------+-------+
19 rows in set (0.00 sec)

分析：這裡的NOT否定跟在它之後的條件，因此，MySQL不是匹配1002和1003的vend_id，而是匹配1002和1003之外供應商的 vend_id。

為什麼使用NOT?對於簡單的WHERE子句，使用NOT確實沒有什麼優勢。但在更複雜的子句中，NOT是非常有用的。例如，在與IN操作符聯合使用時，NOT使找出與條件列表不匹配的行非常簡單。

結語

這邊學到最重要的是IN操作符一般比OR操作符清單執行更快。剩下的我覺得都滿基本上的，算是再重新複習一下。
還有AND和OR的一個計算次序，這個可以了解一下，試多一點範例來感受一下。

註：以上參考了
MySQL必知必会 MySQL Crash Course

MySQL - 第六章 | 單一條件過濾資料

MySQL - 第八章 | 萬用字元進行過濾資料

↑
If you enjoy the article, please feel free to donate~ Thx.
若本文對您有幫助，您也願意支持打賞，謝謝您的鼓勵。

本文由J.J. Huang 創作，採用CC BY 3.0 TW協議進行許可。可自由轉載、引用，但需署名作者且註明文章出處。

J.J.'s Blogs

J.J. Huang 2020-06-14 MySQL 瀏覽次數：次 {{moment(1592141400000).fromNow()}}