Like Share Discussion Bookmark Smile

J.J. Huang   2020-06-14   MySQL   瀏覽次數:

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

準備

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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子句附加條件。下面的程式碼給出了一個例子。

1
2
3
4
5
6
7
8
9
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檢索匹配任一條件的行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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製造的所有產品。

1
2
3
4
5
6
7
8
9
10
11
12
13
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在計算次序中優先級更高,操作符被錯誤地組合了。

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

1
2
3
4
5
6
7
8
9
10
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取合法值的由逗號分隔的清 單,全都括在圓括號中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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相同的功能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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之外的所有供應商製造的產品。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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