MySQL操作符(and、or、in、not)的具體使用

更新時(shí)間：2021年11月12日 10:50:32 作者：李子捌

本文主要介紹了MySQL操作符(and、or、in、not)的具體使用，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

1、簡(jiǎn)介

在MySQL中使用where子句對(duì)查詢數(shù)據(jù)進(jìn)行過(guò)濾時(shí)，往往需要同時(shí)滿足多個(gè)過(guò)濾條件，或者滿足多個(gè)過(guò)濾條件中的某一個(gè)條件，此時(shí)我們就可以使用操作符將where子句聯(lián)結(jié)起來(lái)。

幾個(gè)操作符的作用：

操作符	作用
and	與，需要同時(shí)滿足where子句中的條件
or	或，只需要匹配多個(gè)where子句中的一個(gè)條件
in	用于指定where子句查詢的范圍
not	非，一般與in、between and、exists一起使用，表示取反

2、正文

首先準(zhǔn)備一張User表，DDL和表數(shù)據(jù)如下所示，可以直接復(fù)制使用。

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操作符

當(dāng)查詢需要同時(shí)滿足where子句中的條件，可以使用and操作符，and條件之間是一個(gè)與的關(guān)系。

需求：
查詢年齡=18 并且性別為男的用戶（注意：sex=1代表男性）
語(yǔ)句：

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)

此時(shí)可以看到只有同時(shí)滿足age=18和sex=1的用戶才被查詢出來(lái)。以此類推，and可以同時(shí)存在多個(gè)，比如在上面的基礎(chǔ)上需要查詢** 姓名=李子柒**，只需要再跟一個(gè)and操作符即可。

mysql> select * from user where age = 18 and sex =1 and name = '李子柒';
Empty set (0.00 sec)

2.2 or操作符

與and不同，or只需要滿足多個(gè)where條件中的一個(gè)即可，不需要同時(shí)滿足，條件之間是一個(gè)或的關(guān)系。

需求：
查詢年齡=18 或者性別為男的用戶（注意：sex=1代表男性）
語(yǔ)句：

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)

此時(shí)可以看到，滿足age=18或者sex=1的用戶都被查出來(lái)了。同樣的or操作符也可以同時(shí)作用于多個(gè)where子句。

2.3 in操作符

in操作符用于指定where子句的查詢范圍。它表示包含的意思，它可以用多個(gè)or操作符來(lái)實(shí)現(xiàn)。

需求：
查詢name等于張三、李四、王五的用戶信息。
語(yǔ)句：
使用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)

上面的需求，可以通過(guò)or操作符和in操作符來(lái)實(shí)現(xiàn)，但是in操作符很明顯SQL語(yǔ)句根據(jù)簡(jiǎn)潔。

2.4 not操作符

當(dāng)我們需要查詢某個(gè)值不在什么范圍之內(nèi)、不存在的時(shí)候，可以使用not操作符，not操作符不單獨(dú)使用，它經(jīng)常和in操作符、like操作符、between and、exists等一起使用。

not in
需求：
查詢姓名不等于張三、李四、王五的用戶信息。
語(yǔ)句：

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
需求：
查詢姓名不是以李子開頭的用戶
語(yǔ)句：

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之間的用戶
語(yǔ)句：

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用法一致，用于判斷當(dāng)前where子句的結(jié)果是否應(yīng)該返回。not exists 和 exists作用于一個(gè)子查詢，向上級(jí)返回true和false；
示例語(yǔ)法：

SELECT … FROM table WHERE EXISTS (subquery)
SELECT … FROM table WHERE NOT EXISTS (subquery)

為了演示效果，我們創(chuàng)建一個(gè)簡(jiǎn)單的訂單表order，其建表語(yǔ)句和數(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 '訂單號(hào)',
  `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)鍵字，所以建表時(shí)不建議直接取名為order，我這里取名order是為了講述如何解決這個(gè)問題。

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)

細(xì)心可以發(fā)現(xiàn)，order用 ` 修飾，這樣MySQL就不會(huì)把它當(dāng)成關(guān)鍵字解析了。如果不加MySQL會(huì)拋出異常。

回歸主題，我們此時(shí)使用exists進(jìn)行查詢
需求：
查詢已下單的用戶信息
語(yǔ)句：

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)

此時(shí)如果我們想查詢未下單的用戶信息，只需要使用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 操作符順序

上面說(shuō)了好幾個(gè)操作符，但是很多情況下需要多個(gè)操作符一起使用，這個(gè)時(shí)候我們就需要注意操作符的順序問題了。

比如說(shuō)如下需求：
查詢用戶表中，年齡大于20歲或者性別為男，并且姓名不等于張三的用戶。
語(yǔ)句：

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)

此時(shí)發(fā)現(xiàn)查詢的返回結(jié)果竟然包含張三，這是因?yàn)閍nd的優(yōu)先級(jí)比or高，在MySQL底層的SQL解析器，把上面的SQL解析成sex = 1 and name != '張三' or age > 20 ；因?yàn)閺埲凉M足age > 20所以也被查詢出來(lái)了。要想解決這個(gè)問題只需要使用括號(hào)將or語(yǔ)句括起來(lái)就好了。

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í)查詢的返回?cái)?shù)據(jù)中已經(jīng)不包含張三了。

因此我們?cè)趯慡QL的時(shí)候，可以養(yǎng)成習(xí)慣使用括號(hào)，通過(guò)括號(hào)對(duì)操作符分組，能夠避免使用默認(rèn)順序帶來(lái)的錯(cuò)誤風(fēng)險(xiǎn)。

到此這篇關(guān)于MySQL操作符(and、or、in、not)的具體使用的文章就介紹到這了,更多相關(guān)MySQL操作符內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: