MySQL操作符之（and、or、in、not）
「這是我參與11月更文挑戰的第14天，活動詳情檢視：2021最後一次更文挑戰」
1、簡介
在MySQL中使用where子句對查詢資料進行過濾時，往往需要同時滿足多個過濾條件，或者滿足多個過濾條件中的某一個條件，此時我們就可以使用操作符將where子句聯結起來。
幾個操作符的作用：
| 操作符 | 作用 | | ------- | ----------------------------------- | | and | 與，需要同時滿足where子句中的條件 | | or | 或，只需要匹配多個where子句中的一個條件 | | in | 用於指定where子句查詢的範圍 | | not | 非，一般與in、between and、exists一起使用，表示取反 |
2、正文
首先準備一張User表，DDL和表資料如下所示，可以直接複製使用。
``` SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;
-- Table structure for user
DROP TABLE IF EXISTS user;
CREATE TABLE user (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '使用者名稱',
age int(11) NOT NULL COMMENT '年齡',
sex smallint(6) NOT NULL COMMENT '性別',
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- Records of user
INSERT INTO user VALUES (1, '李子捌', 18, 1);
INSERT INTO user VALUES (2, '張三', 22, 1);
INSERT INTO user VALUES (3, '李四', 38, 1);
INSERT INTO user VALUES (4, '王五', 25, 1);
INSERT INTO user VALUES (5, '六麻子', 13, 0);
INSERT INTO user VALUES (6, '田七', 37, 1);
INSERT INTO user VALUES (7, '謝禮', 18, 0);
SET FOREIGN_KEY_CHECKS = 1; ```
資料的初始順序如下所示：
mysql> select * from user;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
| 2 | 張三 | 22 | 1 |
| 3 | 李四 | 38 | 1 |
| 4 | 王五 | 25 | 1 |
| 5 | 六麻子 | 13 | 0 |
| 6 | 田七 | 37 | 1 |
| 7 | 謝禮 | 18 | 0 |
+----+--------+-----+-----+
7 rows in set (0.00 sec)
2.1 and操作符
當查詢需要同時滿足where子句中的條件，可以使用and操作符，and條件之間是一個與的關係。
需求：
查詢年齡=18 並且 性別為男的使用者（注意：sex=1代表男性）
語句：
mysql> select * from user where age = 18 and sex =1;
結果：
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
+----+--------+-----+-----+
1 row in set (0.00 sec)
此時可以看到只有同時滿足age=18和sex=1的使用者才被查詢出來。以此類推，and可以同時存在多個，比如在上面的基礎上需要查詢 姓名=李子柒，只需要再跟一個and操作符即可。
mysql> select * from user where age = 18 and sex =1 and name = '李子柒';
Empty set (0.00 sec)
2.2 or操作符
與and不同，or只需要滿足多個where條件中的一個即可，不需要同時滿足，條件之間是一個或的關係。
需求：
查詢年齡=18 或者 性別為男的使用者（注意：sex=1代表男性）
語句：
mysql> select * from user where age = 18 or sex =1;
結果：
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
| 2 | 張三 | 22 | 1 |
| 3 | 李四 | 38 | 1 |
| 4 | 王五 | 25 | 1 |
| 6 | 田七 | 37 | 1 |
| 7 | 謝禮 | 18 | 0 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)
此時可以看到，滿足age=18或者sex=1的使用者都被查出來了。同樣的or操作符也可以同時作用於多個where子句。
2.3 in操作符
in操作符用於指定where子句的查詢範圍。它表示包含的意思，它可以用多個or操作符來實現。
需求：
查詢name等於張三、李四、王五的使用者資訊。
語句：
使用or操作符
mysql> select * from user where name = '張三' or name = '李四' or name = '王五';
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 2 | 張三 | 22 | 1 |
| 3 | 李四 | 38 | 1 |
| 4 | 王五 | 25 | 1 |
+----+------+-----+-----+
3 rows in set (0.00 sec)
使用in操作符
mysql> select * from user where name in ('張三', '李四', '王五');
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 2 | 張三 | 22 | 1 |
| 3 | 李四 | 38 | 1 |
| 4 | 王五 | 25 | 1 |
+----+------+-----+-----+
3 rows in set (0.00 sec)
上面的需求，可以通過or操作符和in操作符來實現，但是in操作符很明顯SQL語句根據簡潔。
2.4 not操作符
當我們需要查詢某個值不在什麼範圍之內、不存在的時候，可以使用not操作符，not操作符不單獨使用，它經常和in操作符、like操作符、between and、exists等一起使用。
not in
需求：
查詢姓名不等於張三、李四、王五的使用者資訊。
語句：
mysql> select * from user where name not in ('張三', '李四', '王五');
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
| 5 | 六麻子 | 13 | 0 |
| 6 | 田七 | 37 | 1 |
| 7 | 謝禮 | 18 | 0 |
+----+--------+-----+-----+
4 rows in set (0.00 sec)
not like
需求：
查詢姓名不是以李子開頭的使用者
語句：
mysql> select * from user where name not like '李子%';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 2 | 張三 | 22 | 1 |
| 3 | 李四 | 38 | 1 |
| 4 | 王五 | 25 | 1 |
| 5 | 六麻子 | 13 | 0 |
| 6 | 田七 | 37 | 1 |
| 7 | 謝禮 | 18 | 0 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)
not between and
需求：
查詢年齡不屬於20 - 30之間的使用者
語句：
mysql> select * from user where age not between 20 and 30;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
| 3 | 李四 | 38 | 1 |
| 5 | 六麻子 | 13 | 0 |
| 6 | 田七 | 37 | 1 |
| 7 | 謝禮 | 18 | 0 |
+----+--------+-----+-----+
5 rows in set (0.00 sec)
not exists
not exists表，它與exists用法一致，用於判斷當前where子句的結果是否應該返回。not exists 和 exists作用於一個子查詢，向上級返回true和false；
示例語法：
SELECT … FROM table WHERE EXISTS (subquery)
SELECT … FROM table WHERE NOT EXISTS (subquery)
為了演示效果，我們建立一個簡單的訂單表order，其建表語句和資料如下所示：
``` SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;
-- Table structure for order
DROP TABLE IF EXISTS order;
CREATE TABLE order (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
number varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單號',
user_id bigint(20) NULL DEFAULT NULL COMMENT '使用者id',
price decimal(10, 2) NULL DEFAULT NULL COMMENT '金額',
create_date datetime(0) NULL DEFAULT NULL COMMENT '建立日期',
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- Records of order
INSERT INTO order VALUES (1, 'DD-20211110-000001', 1, 250.00, '2021-11-10 22:37:19');
SET FOREIGN_KEY_CHECKS = 1; ```
注意：由於order是MySQL的關鍵字，所以建表時不建議直接取名為order，我這裡取名order是為了講述如何解決這個問題。
mysql> select * from `order`;
+----+--------------------+---------+--------+---------------------+
| id | number | user_id | price | create_date |
+----+--------------------+---------+--------+---------------------+
| 1 | DD-20211110-000001 | 1 | 250.00 | 2021-11-10 22:37:19 |
+----+--------------------+---------+--------+---------------------+
1 row in set (0.00 sec)
細心可以發現，order用 ` 修飾，這樣MySQL就不會把它當成關鍵字解析了。如果不加MySQL會丟擲異常。
迴歸主題，我們此時使用exists進行查詢
需求：
查詢已下單的使用者資訊
語句：
mysql> select * from user where exists(select id from `order` where user_id = user.id);
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
+----+--------+-----+-----+
1 row in set (0.00 sec)
此時如果我們想查詢未下單的使用者資訊，只需要使用not exists即可
mysql> select * from user where not exists (select id from `order` where user_id = user.id);
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 2 | 張三 | 22 | 1 |
| 3 | 李四 | 38 | 1 |
| 4 | 王五 | 25 | 1 |
| 5 | 六麻子 | 13 | 0 |
| 6 | 田七 | 37 | 1 |
| 7 | 謝禮 | 18 | 0 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)
2.5 操作符順序
上面說了好幾個操作符，但是很多情況下需要多個操作符一起使用，這個時候我們就需要注意操作符的順序問題了。
比如說如下需求：
查詢使用者表中，年齡大於20歲或者性別為男，並且姓名不等於張三的使用者。
語句：
mysql> select * from user where age > 20 or sex = 1 and name != '張三';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
| 2 | 張三 | 22 | 1 |
| 3 | 李四 | 38 | 1 |
| 4 | 王五 | 25 | 1 |
| 6 | 田七 | 37 | 1 |
+----+--------+-----+-----+
5 rows in set (0.00 sec)
此時發現查詢的返回結果竟然包含張三，這是因為and的優先順序比or高，在MySQL底層的SQL解析器，把上面的SQL解析成sex = 1 and name != '張三' or age > 20 ；因為張三滿足age > 20所以也被查詢出來了。要想解決這個問題只需要使用括號將or語句括起來就好了。
mysql> select * from user where (age > 20 or sex = 1) and name != '張三';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
| 3 | 李四 | 38 | 1 |
| 4 | 王五 | 25 | 1 |
| 6 | 田七 | 37 | 1 |
+----+--------+-----+-----+
4 rows in set (0.00 sec)
此時查詢的返回資料中已經不包含張三了。
因此我們在寫SQL的時候，可以養成習慣使用括號，通過括號對操作符分組，能夠避免使用預設順序帶來的錯誤風險。
- MySQL操作符之（and、or、in、not）
- 如何檢視Java程序和執行緒
- 執行緒的三種建立方式
- HarmonyOS（鴻蒙）——Image（圖片）元件介紹
- HarmonyOS（鴻蒙）——Text（文字）元件介紹
- HarmonyOS（鴻蒙）——單擊事件
- VM安裝CentOS7
- 你的工具包中不能少了這個神奇——Apache HTTP server benchmarking tool(ab)
- 如何讓 a == 1 && a == 2 && a == 3同時成立？
- this與super
- interrupted()和isInterrupted()還是一知半解？
- Hystrix Hystrix Dashboard Turbine使用，它不香麼？
- MySQL中使用正則表示式
- Hystrix Dashboard
- Hystrix
- Spring Cloud Config 四種模式一文盡收囊中
- MySQL之like操作符
- OpenFeign入門
- Eureka分割槽叢集部署
- MySQL之where使用