MySQL操作符(and、or、in、not)的具體使用
1、簡介
在MySQL中使用where子句對查詢數(shù)據(jù)進行過濾時，往往需要同時滿足多個過濾條件，或者滿足多個過濾條件中的某一個條件，此時我們就可以使用操作符將where子句聯(lián)結(jié)起來。
幾個操作符的作用：
| 操作符 | 作用 |
|---|---|
| and | 與，需要同時滿足where子句中的條件 |
| or | 或，只需要匹配多個where子句中的一個條件 |
| in | 用于指定where子句查詢的范圍 |
| not | 非，一般與in、between and、exists一起使用，表示取反 |
2、正文
首先準備一張User表，DDL和表數(shù)據(jù)如下所示，可以直接復制使用。
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;
數(shù)據(jù)的初始順序如下所示：
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條件之間是一個與的關(guān)系。
需求：
查詢年齡=18 并且 性別為男的用戶（注意：sex=1代表男性）
語句：
mysql> select * from user where age = 18 and sex =1;
結(jié)果：
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | 李子捌 | 18 | 1 |
+----+--------+-----+-----+
1 row in set (0.00 sec)
此時可以看到只有同時滿足age=18和sex=1的用戶才被查詢出來。以此類推，and可以同時存在多個，比如在上面的基礎(chǔ)上需要查詢** 姓名=李子柒**，只需要再跟一個and操作符即可。
mysql> select * from user where age = 18 and sex =1 and name = '李子柒'; Empty set (0.00 sec)
2.2 or操作符
與and不同，or只需要滿足多個where條件中的一個即可，不需要同時滿足，條件之間是一個或的關(guān)系。
需求：
查詢年齡=18 或者 性別為男的用戶（注意：sex=1代表男性）
語句：
mysql> select * from user where age = 18 or sex =1;
結(jié)果：
+----+--------+-----+-----+
| 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操作符來實現(xiàn)。
需求：
查詢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操作符來實現(xiàn)，但是in操作符很明顯SQL語句根據(jù)簡潔。
2.4 not操作符
當我們需要查詢某個值不在什么范圍之內(nèi)、不存在的時候，可以使用not操作符，not操作符不單獨使用，它經(jīng)常和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子句的結(jié)果是否應(yīng)該返回。not exists 和 exists作用于一個子查詢，向上級返回true和false；
示例語法：
SELECT … FROM table WHERE EXISTS (subquery) SELECT … FROM table WHERE NOT EXISTS (subquery)
為了演示效果，我們創(chuàng)建一個簡單的訂單表order，其建表語句和數(shù)據(jù)如下所示：
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 '創(chuàng)建日期', 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的關(guān)鍵字，所以建表時不建議直接取名為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)
細心可以發(fā)現(xiàn)，order用 ` 修飾，這樣MySQL就不會把它當成關(guān)鍵字解析了。如果不加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)
此時發(fā)現(xiàn)查詢的返回結(jié)果竟然包含張三，這是因為and的優(yōu)先級比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)
此時查詢的返回數(shù)據(jù)中已經(jīng)不包含張三了。
因此我們在寫SQL的時候，可以養(yǎng)成習慣使用括號，通過括號對操作符分組，能夠避免使用默認順序帶來的錯誤風險。
到此這篇關(guān)于MySQL操作符(and、or、in、not)的具體使用的文章就介紹到這了,更多相關(guān)MySQL操作符內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL實現(xiàn)查詢數(shù)據(jù)庫表記錄數(shù)
這篇文章主要介紹了MySQL實現(xiàn)查詢數(shù)據(jù)庫表記錄數(shù)，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-09-09
關(guān)于Mysql中ON與Where區(qū)別問題詳解
在編寫SQL腳本中,多表連接查詢操作需要使用到on和where條件,但是經(jīng)常會混淆兩者的用法,從而造成取數(shù)錯誤,下面這篇文章主要給大家介紹了關(guān)于Mysql中ON與Where區(qū)別問題的相關(guān)資料,需要的朋友可以參考下2022-02-02
這篇文章主要介紹了mysql 數(shù)據(jù)插入和更新及刪除，文章圍繞mysql 數(shù)據(jù)插入和更新及刪除的相關(guān)資料展開內(nèi)容,需要的朋友可以參考以下文章的具體內(nèi)容2021-10-10
這篇文章主要給大家介紹了一次神奇的MySQL死鎖排查的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-03-03
這篇文章主要給大家介紹了關(guān)于MySQL慢查日志的開啟方式與存儲格式的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-08-08
這篇文章主要介紹了MySql索引提高查詢速度常用方法代碼示例,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-10-10
MySQL8的JSON數(shù)據(jù)類型為存儲和查詢JSON數(shù)據(jù)提供了強大的支持,本文主要介紹了MySQL?JSON類型的功能與應(yīng)用,具有一定的參考價值,感興趣的可以了解一下2024-08-08
Windows環(huán)境MySQL全量備份+增量備份的實現(xiàn)
本文主要介紹了Windows環(huán)境MySQL全量備份+增量備份的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-08-08
本文主要介紹了MySQL里的反斜杠(\\)的使用，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-02-02
MySQL基礎(chǔ)快速入門知識總結(jié)(附思維導圖)
MySQL 為關(guān)系型數(shù)據(jù)庫(Relational Database Management System), 這種所謂的關(guān)系型可以理解為表格的概念, 一個關(guān)系型數(shù)據(jù)庫由一個或數(shù)個表格組成,這篇文章主要給大家介紹了關(guān)于MySQL基礎(chǔ)快速入門知識的相關(guān)資料,需要的朋友可以參考下2021-09-09