MySQL操作符(and、or、in、not)的具体使用
作者：李子捌 发布时间：2024-01-17 22:46:44
目录
1、简介
2、正文
2.1 and操作符
2.2 or操作符
2.3 in操作符
2.4 not操作符
2.5 操作符顺序
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的时候，可以养成习惯使用括号，通过括号对操作符分组，能够避免使用默认顺序带来的错误风险。
来源：https://blog.csdn.net/qq_41125219/article/details/121278048
0
投稿猜你喜欢
- 基本用法?在大型项目中，我们可能需要拆分应用为更小的块，并仅在需要时再从服务器加载相关组件。Vue 提供了defineAsyncCompon
- 问题你想创建一个内嵌变量的字符串，变量被它的值所表示的字符串替换掉。解决方案Python并没有对在字符串中简单替换变量值提供直接的支持。 但
- Bottle是一个轻量级的Web框架，此框架只由一个 bottle.py 文件构成，不依赖任何第三方模块。#!/usr/bin/env py
目录前言yarn create 做了什么源码解析项目依赖模版配置工具函数copycopyDiremptyDir核心函数命令行交互并创建文件夹- 之前遇到技术问题总能在技术博客上得到启发，十分感谢各位的无私分享。而自己却很少发文，固然是水平有限，但也限制了知识积累和总结。今后多总结分享
- # 判断是否是素数def is_sushu(num): res=True for x in range(2,num-1)
- 目录step1 搭建框架step2 填充搭建好了的Promise框架总结step1 搭建框架1. 首先我们需要在这里放置一个promise函
- 环境搭建准备工具：pyton3.5,selenium,phantomjs我的电脑里面已经装好了python3.5安装Seleniumpip3
- 本文实例讲述了python简单的函数定义和用法。分享给大家供大家参考。具体分析如下：这里定义了一个温度转换的函数及其用法。def conve
- PDOStatement::columnCountPDOStatement::columnCount — 返回结果集中的列数。(PHP 5
- 简介tuple1.元组是以圆括号“()”包围的数据集合，不同成员以“,”分隔。通过下标进行访问2.不可变序列，可以看做不可变的列表，与列表不
- 查询数据库SELECT * FROM `student`查询结果idnamebirthday1张三1970-10-012李四1990-10-
- 矩阵相乘需要前面矩阵的行数与后面矩阵的列数相同方可相乘。第一步，先将前面矩阵的每一行分别与后面矩阵的列相乘，作为结果矩阵的行列；第二步算出结
- 另外，我们在 控制结构比较两个数值是否一直的时候，也应该尽量使用 === 来代替 ==（当然，这个也根据具体的业务逻辑选用比较合适的）。 小
- Ubuntu 安装jdk：[链接]Ubuntu安装eclipse：[链接]Ubuntu下安装MySQL与mysql workbench：[链
- python结构体数组在C语言中我们可以通过struct关键字定义结构类型，结构中的字段占据连续的内存空间，每个结构体占用的内存大小都相同，
- 以下的文章主要是介绍SQL Server数据库与其实际应用元数据，我前两天在相关网站看见SQL Server数据库与其实际应用元数据的资料，
- 内容摘要合理使用渐变留白网格布局提高字体应用明确而有效的导航设计漂亮、有用的页脚介绍优秀设计和卓越设计之间的区别是比较小的。一般人可能无法解
- 之前说过要聊聊 干职业设计经理的活 的问题，貌似有些朋友对这个事情还挺关心的，我理解为一方面是掌握对付猎头时候的标准答案，一方面是
- 去过新浪或者搜狐吗？虽然我们都不愿意看广告，但是它们做广告的技术我们却应该学到手，这不，又一种很流行的做法儿，做成那种两边对称的对联式的广告
