MySQL全面瓦解7:查询的过滤条件

翁智华 2020-11-09 16:59:11
Mysql 查询 全面 过滤 瓦解


概述

在实际的业务场景应用中,我们经常要根据业务条件获取并筛选出我们的目标数据。这个过程我们称之为数据查询的过滤。而过滤过程使用的各种条件(比如日期时间、用户、状态)是我们获取精准数据的必要步骤,

这样才能得到我们期望的结果。所以本章我们来学习MySQL中查询过滤条件的各种用法。

关系运算

关系运算就是where语句后跟上一个或者n个条件,满足where后面条件的数据会被返回,反之不满足的就会被过滤掉。operators指的是运算符 ,有如下几种情况:

运算符 说明
= 等于
<> 或者 != 不等于
> 大于
>= 大于等于
< 小于
<= 小于等于

 

关系运算基本的语法格式如下:

1 select cname1,cname2,... from tname where cname operators cval 

等于=

查询出 列和后面的值严格相等的数据,非值类型的需要对后面值加上引号,值类型的不需要。

语法格式如下:

1 select cname1,cname2,... from tname where cname = cval; 
 1 mysql> select * from user2;
 2 +----+-------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+-------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 +----+-------+-----+----------+-----+
 9 3 rows in set
10
11 mysql> select * from user2 where name='helen';
12 +----+-------+-----+----------+-----+
13 | id | name | age | address | sex |
14 +----+-------+-----+----------+-----+
15 | 2 | helen | 20 | quanzhou | 0 |
16 +----+-------+-----+----------+-----+
17 1 row in set
18
19 mysql> select * from user2 where age=21;
20 +----+-------+-----+---------+-----+
21 | id | name | age | address | sex |
22 +----+-------+-----+---------+-----+
23 | 1 | brand | 21 | fuzhou | 1 |
24 | 3 | sol | 21 | xiamen | 0 |
25 +----+-------+-----+---------+-----+
26 2 rows in set

不等于(<>、!=)

不等于有两种写法,一种是<>,另一种是!=,意思一样,可随意切换使用,但是 <> 先于 != 出现,所以看很多以前的例子,<> 出现频率比较高,可移植性更强,推荐使用。

不等于的目的是查询出与条件不符和结果,格式如下:

select cname1,cname2,... from tname where cname <> cval;
或
select cname1,cname2,... from tname where cname != cval;

 

 1 mysql> select * from user2;
 2 +----+-------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+-------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 +----+-------+-----+----------+-----+
 9 3 rows in set
10
11 mysql> select * from user2 where age<>20;
12 +----+-------+-----+---------+-----+
13 | id | name | age | address | sex |
14 +----+-------+-----+---------+-----+
15 | 1 | brand | 21 | fuzhou | 1 |
16 | 3 | sol | 21 | xiamen | 0 |
17 +----+-------+-----+---------+-----+
18 2 rows in set 

大于小于(> <)

一般用于数值或者日期、时间类型的比较,格式如下:

1 select cname1,cname2,... from tname where cname > cval;
2
3 select cname1,cname2,... from tname where cname < cval;
4
5 select cname1,cname2,... from tname where cname >= cval;
6
7 select cname1,cname2,... from tname where cname <= cval;

 

 1 mysql> select * from user2 where age>20;
 2 +----+-------+-----+---------+-----+
 3 | id | name | age | address | sex |
 4 +----+-------+-----+---------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 3 | sol | 21 | xiamen | 0 |
 7 +----+-------+-----+---------+-----+
 8 2 rows in set
 9
10 mysql> select * from user2 where age>=20;
11 +----+-------+-----+----------+-----+
12 | id | name | age | address | sex |
13 +----+-------+-----+----------+-----+
14 | 1 | brand | 21 | fuzhou | 1 |
15 | 2 | helen | 20 | quanzhou | 0 |
16 | 3 | sol | 21 | xiamen | 0 |
17 +----+-------+-----+----------+-----+
18 3 rows in set
19
20 mysql> select * from user2 where age<21;
21 +----+-------+-----+----------+-----+
22 | id | name | age | address | sex |
23 +----+-------+-----+----------+-----+
24 | 2 | helen | 20 | quanzhou | 0 |
25 +----+-------+-----+----------+-----+
26 1 row in set
27
28 mysql> select * from user2 where age<=21;
29 +----+-------+-----+----------+-----+
30 | id | name | age | address | sex |
31 +----+-------+-----+----------+-----+
32 | 1 | brand | 21 | fuzhou | 1 |
33 | 2 | helen | 20 | quanzhou | 0 |
34 | 3 | sol | 21 | xiamen | 0 |
35 +----+-------+-----+----------+-----+
36 3 rows in set

逻辑运算

 

运算符 说明
AND 多个条件都成立
OR 多个条件中满足一个
NOT 对条件进行取非操作

AND(且)

当需要多个条件进行数据过滤的时候,使用这种方式,and的每个表达式都是要成立,过滤出来的数据就是用户需要的。

下面过滤出年龄和性别两个条件都成立的数据,语法格式如下: 

1 select cname1,cname2,... from tname where cname1 operators cval1 and cname2 operators cval2  
 1 mysql> select * from user2;
 2 +----+-------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+-------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 | 4 | weng | 33 | guizhou | 1 |
 9 +----+-------+-----+----------+-----+
10 4 rows in set
11
12 mysql> select * from user2 where age >20 and sex=1;
13 +----+-------+-----+---------+-----+
14 | id | name | age | address | sex |
15 +----+-------+-----+---------+-----+
16 | 1 | brand | 21 | fuzhou | 1 |
17 | 4 | weng | 33 | guizhou | 1 |
18 +----+-------+-----+---------+-----+
19 2 rows in set 

OR(或)

当多个条件中只要满足一个条件即进行数据过滤。

下面条件过滤出年龄大于21岁和小于21岁的数据,语法格式如下:

1 select cname1,cname2,... from tname where cname1 operators cval1 or cname2 operators cval2 

 

 1 mysql> select * from user2;
 2 +----+-------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+-------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 | 4 | weng | 33 | guizhou | 1 |
 9 +----+-------+-----+----------+-----+
10 4 rows in set
11
12 mysql> select * from user2 where age>21 or age<21;
13 +----+-------+-----+----------+-----+
14 | id | name | age | address | sex |
15 +----+-------+-----+----------+-----+
16 | 2 | helen | 20 | quanzhou | 0 |
17 | 4 | weng | 33 | guizhou | 1 |
18 +----+-------+-----+----------+-----+
19 2 rows in set 

NOT(取非)

对某个满足的条件进行取反,过滤出来的数据就是用户需要的。 

下面过滤不属于年龄大于20的数据,语法格式如下:

1 select cname1,cname2,... from tname where not(cname operators cval) 
 1 mysql> select * from user2;
 2 +----+-------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+-------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 | 4 | weng | 33 | guizhou | 1 |
 9 +----+-------+-----+----------+-----+
10 4 rows in set
11
12 mysql> select * from user2 where not(age>20);
13 +----+-------+-----+----------+-----+
14 | id | name | age | address | sex |
15 +----+-------+-----+----------+-----+
16 | 2 | helen | 20 | quanzhou | 0 |
17 +----+-------+-----+----------+-----+
18 1 row in set 

模糊匹配

就像我们上面的那个用户表信息表(包含名称、年龄、地址、性别),当我们要查询名称为s开头的用户时,就可以用到 like 关键字了,他用以模糊匹配数据。

语法格式如下,pattern中可以包含通配符,有两种。%:表示匹配任意一个或n个字符; _:表示匹配任意一个字符。

1 select cname1,cname2,... from tname where cname like pattern; 

%的使用

 1 mysql> select * from user2;
 2 +----+--------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+--------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 | 4 | weng | 33 | guizhou | 1 |
 9 | 5 | selina | 25 | taiwang | 0 |
10 +----+--------+-----+----------+-----+
11 5 rows in set
12
13 mysql> select * from user2 where name like 's%';
14 +----+--------+-----+---------+-----+
15 | id | name | age | address | sex |
16 +----+--------+-----+---------+-----+
17 | 3 | sol | 21 | xiamen | 0 |
18 | 5 | selina | 25 | taiwang | 0 |
19 +----+--------+-----+---------+-----+
20 2 rows in set 

_的使用

 1 mysql> select * from user2;
 2 +----+--------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+--------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 | 4 | weng | 33 | guizhou | 1 |
 9 | 5 | selina | 25 | taiwang | 0 |
10 +----+--------+-----+----------+-----+
11 5 rows in set
12
13 mysql> select * from user2 where name like 's_l';
14 +----+------+-----+---------+-----+
15 | id | name | age | address | sex |
16 +----+------+-----+---------+-----+
17 | 3 | sol | 21 | xiamen | 0 |
18 +----+------+-----+---------+-----+
19 1 row in set 

注意点

1、不要过度使用模糊匹配得通配符。如果其他操作符能达到相同的目的,应该使用其他操作符
2、
对大体量的表进行模糊匹配的时候尽量不要以%开头,比如 like '%username',这样会执行扫表,效率较慢。尽量明确模糊查找的开头部分,比如 like 'brand%',会先定位到brand开头的数据,效率高很多。

范围值检查

BETWEEN AND(区间查询)

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围,这些值可以是数值、文本或者日期,属于一个闭区间查询。

and 的左边val1 和 右边 val2 分别表示两个临界值,等同于数学公式[val1,val2] ,属于这两个区间的数据会被过滤出来(>=val1 和 <=val2),所以语法格式如下:

1 selec cname1,cname2,... from tname where cname between val1 and val2;
2 等同于
3 selec cname1,cname2,... from tname where cname >= val1 and cname <= val2;

查询年龄在[21,25]之间的数据:

 1 mysql> select * from user2;
 2 +----+--------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+--------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 | 4 | weng | 33 | guizhou | 1 |
 9 | 5 | selina | 25 | taiwang | 0 |
10 +----+--------+-----+----------+-----+
11 5 rows in set
12
13 mysql> select * from user2 where age between 21 and 25;
14 +----+--------+-----+---------+-----+
15 | id | name | age | address | sex |
16 +----+--------+-----+---------+-----+
17 | 1 | brand | 21 | fuzhou | 1 |
18 | 3 | sol | 21 | xiamen | 0 |
19 | 5 | selina | 25 | taiwang | 0 |
20 +----+--------+-----+---------+-----+
21 3 rows in set
22
23 mysql> select * from user2 where age >= 21 and age <= 25;
24 +----+--------+-----+---------+-----+
25 | id | name | age | address | sex |
26 +----+--------+-----+---------+-----+
27 | 1 | brand | 21 | fuzhou | 1 |
28 | 3 | sol | 21 | xiamen | 0 |
29 | 5 | selina | 25 | taiwang | 0 |
30 +----+--------+-----+---------+-----+
31 3 rows in set

IN(包含查询)

按照上面得数据,如果我们想查出居住地位于福州和厦门得用户数据,应该使用 IN操作符,因为 IN 操作符允许我们在 WHERE 子句中指定多个值,符合这些值中得某一项,既满足条件返回数据。

语法格式如下,in 后面列表的值类型必须一致或兼容,且不支持通配符:

1 select cname1,cname2,... from tname where cname in (val1,val2,...);

 

 1 mysql> select * from user2;
 2 +----+--------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+--------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 | 4 | weng | 33 | guizhou | 1 |
 9 | 5 | selina | 25 | taiwang | 0 |
10 +----+--------+-----+----------+-----+
11 5 rows in set
12
13 mysql> select * from user2 where address in('fuzhou','xiamen');
14 +----+-------+-----+---------+-----+
15 | id | name | age | address | sex |
16 +----+-------+-----+---------+-----+
17 | 1 | brand | 21 | fuzhou | 1 |
18 | 3 | sol | 21 | xiamen | 0 |
19 +----+-------+-----+---------+-----+
20 2 rows in set

NOT IN(对包含查询取反)

我们上面已经学习过了not得用户,对not后面执行得表达式进行取反得操作,测试下:

 1 mysql> select * from user2;
 2 +----+--------+-----+----------+-----+
 3 | id | name | age | address | sex |
 4 +----+--------+-----+----------+-----+
 5 | 1 | brand | 21 | fuzhou | 1 |
 6 | 2 | helen | 20 | quanzhou | 0 |
 7 | 3 | sol | 21 | xiamen | 0 |
 8 | 4 | weng | 33 | guizhou | 1 |
 9 | 5 | selina | 25 | taiwang | 0 |
10 +----+--------+-----+----------+-----+
11 5 rows in set
12
13 mysql> select * from user2 where address not in('fuzhou','quanzhou','xiamen');
14 +----+--------+-----+---------+-----+
15 | id | name | age | address | sex |
16 +----+--------+-----+---------+-----+
17 | 4 | weng | 33 | guizhou | 1 |
18 | 5 | selina | 25 | taiwang | 0 |
19 +----+--------+-----+---------+-----+
20 2 rows in set

空值检查

IS NULL/IS NOT NULL

判断是否为空,语法格式如下,这边注意的是,对值为null的数据,各种比较运算符、likebetween andinnot in查询都不起作用,只有is null 能够过滤出来

1 select cname1,cname2,... from tname where cname is null;
2 或者
3 select cname1,cname2,... from tname where cname is not null;

 

 1 mysql> select * from user2 where address is null;
 2 +----+--------+-----+---------+-----+
 3 | id | name | age | address | sex |
 4 +----+--------+-----+---------+-----+
 5 | 5 | selina | 25 | NULL | 0 |
 6 +----+--------+-----+---------+-----+
 7 1 row in set
 8
 9 mysql> select * from user2 where address is not null;
10 +----+-------+-----+----------+-----+
11 | id | name | age | address | sex |
12 +----+-------+-----+----------+-----+
13 | 1 | brand | 21 | fuzhou | 1 |
14 | 2 | helen | 20 | quanzhou | 0 |
15 | 3 | sol | 21 | xiamen | 0 |
16 | 4 | weng | 33 | guizhou | 1 |
17 +----+-------+-----+----------+-----+
18 4 rows in set

 

有一种关键字 <=>,可以包含对null值得判断,但是目前用的比较少了,有兴趣可以去查查,这边不赘述。

总结

1、like表达式中的%匹配一个到多个任意字符,_匹配一个任意字符

2、空值查询需要使用IS NULL或者IS NOT NULL,其他查询运算符对NULL值无效。即使%通配符可以匹配任何东西,也不能匹配值NULL的数据。 

 

3、建议创建表的时候,表字段不设置空,给字段一个default 默认值。

4、MySQL支持使用NOTIN BETWEEN EXISTS子句取反 。

 

版权声明
本文为[翁智华]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/wzh2010/p/13843027.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云