数据库技术:MySQL操作符(and、or、in、not)的具体使用

目录1、简介2、正文2.1 and操作符2.2 or操作符2.3 in操作符2.4 not操作符2.5 操作符顺序1、简介在mysql中使用where子句对查询数据进行过滤时,往往需要同时满足多个过滤

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

到此这篇关于mysql操作符(and、or、in、not)的具体使用的文章就介绍到这了,更多相关mysql操作符内容请搜索<猴子技术宅>以前的文章或继续浏览下面的相关文章希望大家以后多多支持<猴子技术宅>!

需要了解更多数据库技术:MySQL操作符(and、or、in、not)的具体使用,都可以关注数据库技术分享栏目—猴子技术宅(www.ssfiction.com)

本文来自网络收集,不代表猴子技术宅立场,如涉及侵权请点击右边联系管理员删除。

如若转载,请注明出处:https://www.ssfiction.com/sqljc/941855.html

(0)
上一篇 2021年11月15日 上午6:04
下一篇 2021年11月15日 上午6:22

精彩推荐

发表评论

您的电子邮箱地址不会被公开。