MySQL操作符 MySQL操作符(and、or、in、not)的具体使用
李子捌 人气:01、简介
在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的时候,可以养成习惯使用括号,通过括号对操作符分组,能够避免使用默认顺序带来的错误风险。
加载全部内容