- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

MySQL操作符（and、or、in、not）

李子捌发表于 2021/11/11 09:14:58 2021/11/11

【摘要】本专栏会持续输出MySQL系列文章，祝大家每天进步亿点点！文末私信作者，我们一起去大厂。本篇总结的是《MySQL操作符（and、or、in、not）》，后续会每日更新~关于《Redis入门到精通》、《并发编程》、《Java全面入门》、《鸿蒙开发》等知识点可以参考我的往期博客相信自己，越活越坚强，活着就该逢山开路，遇水架桥！生活，你给我压力，我还你奇迹！1、简介在MySQL中使用where子...

本专栏会持续输出MySQL系列文章，祝大家每天进步亿点点！文末私信作者，我们一起去大厂。
本篇总结的是《MySQL操作符（and、or、in、not）》，后续会每日更新~

关于《Redis入门到精通》、《并发编程》、《Java全面入门》、《鸿蒙开发》等知识点可以参考我的往期博客
相信自己，越活越坚强，活着就该逢山开路，遇水架桥！生活，你给我压力，我还你奇迹！

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的时候，可以养成习惯使用括号，通过括号对操作符分组，能够避免使用默认顺序带来的错误风险。

👇🏻 关注公众号 获取更多资料👇🏻

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

MySQL操作符（and、or、in、not）

1、简介

2、正文

2.1 and操作符

2.2 or操作符

2.3 in操作符

2.4 not操作符

2.5 操作符顺序

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

MySQL操作符（and、or、in、not）

1、简介

2、正文

2.1 and操作符

2.2 or操作符

2.3 in操作符

2.4 not操作符

2.5 操作符顺序

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品