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

吾爱主题阅读：151 2024-04-02 08:07:09 评论：0

1、简介

在MySQL中使用where子句对查询数据进行过滤时，往往需要同时满足多个过滤条件，或者满足多个过滤条件中的某一个条件，此时我们就可以使用操作符将where子句联结起来。

几个操作符的作用：

操作符	作用
and	与，需要同时满足where子句中的条件
or	或，只需要匹配多个where子句中的一个条件
in	用于指定where子句查询的范围
not	非，一般与in、between and、exists一起使用，表示取反

2、正文

首先准备一张User表，DDL和表数据如下所示，可以直接复制使用。

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 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;

数据的初始顺序如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 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代表男性）
语句：

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操作符即可。

1 2	`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代表男性）
语句：

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

1 2 3 4 5 6 7 8 9 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操作符

1 2 3 4 5 6 7 8 9 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
需求：
查询姓名不等于张三、李四、王五的用户信息。
语句：

1 2 3 4 5 6 7 8 9 10 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
需求：
查询姓名不是以李子开头的用户
语句：

1 2 3 4 5 6 7 8 9 10 11 12 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之间的用户
语句：

1 2 3 4 5 6 7 8 9 10 11 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；
示例语法：

1 2	`SELECT` `…` `FROM` `table` `WHERE` `EXISTS (subquery)` `SELECT` `…` `FROM` `table` `WHERE` `NOT` `EXISTS (subquery)`

为了演示效果，我们创建一个简单的订单表order，其建表语句和数据如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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是为了讲述如何解决这个问题。

1 2 3 4 5 6 7 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进行查询
需求：
查询已下单的用户信息
语句：

1 2 3 4 5 6 7 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即可

1 2 3 4 5 6 7 8 9 10 11 12 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岁或者性别为男，并且姓名不等于张三的用户。
语句：

1 2 3 4 5 6 7 8 9 10 11 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语句括起来就好了。

1 2 3 4 5 6 7 8 9 10 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)