mysql查询语法总结(MySQL全面瓦解之查询的过滤条件详解)
mysql查询语法总结
MySQL全面瓦解之查询的过滤条件详解概述
在实际的业务场景应用中,我们经常要根据业务条件获取并筛选出我们的目标数据。这个过程我们称之为数据查询的过滤。而过滤过程使用的各种条件(比如日期时间、用户、状态)是我们获取精准数据的必要步骤,
这样才能得到我们期望的结果。所以本章我们来学习MySQL中查询过滤条件的各种用法。
关系运算
关系运算就是where语句后跟上一个或者n个条件,满足where后面条件的数据会被返回,反之不满足的就会被过滤掉。operators指的是运算符 ,有如下几种情况:
运算符 | 说明 |
= | 等于 |
<> 或者 != | 不等于 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
关系运算基本的语法格式如下:
|
select cname1,cname2,... from tname where cname operators cval |
等于=
查询出 列和后面的值严格相等的数据,非值类型的需要对后面值加上引号,值类型的不需要。
语法格式如下:
|
select cname1,cname2,... from tname where cname = cval; |
|
mysql> select * from user2; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | + ----+-------+-----+----------+-----+ 3 rows in set mysql> select * from user2 where name = 'helen' ; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 2 | helen | 20 | quanzhou | 0 | + ----+-------+-----+----------+-----+ 1 row in set mysql> select * from user2 where age=21; + ----+-------+-----+---------+-----+ | id | name | age | address | sex | + ----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | + ----+-------+-----+---------+-----+ 2 rows in set |
不等于(<>、!=)
不等于有两种写法,一种是<>,另一种是!=,意思一样,可随意切换使用,但是 <> 先于 != 出现,所以看很多以前的例子,<> 出现频率比较高,可移植性更强,推荐使用。
不等于的目的是查询出与条件不符和结果,格式如下:
|
select cname1,cname2,... from tname where cname <> cval; 或 select cname1,cname2,... from tname where cname != cval; |
|
mysql> select * from user2; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | + ----+-------+-----+----------+-----+ 3 rows in set mysql> select * from user2 where age<>20; + ----+-------+-----+---------+-----+ | id | name | age | address | sex | + ----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | + ----+-------+-----+---------+-----+ 2 rows in set |
大于小于(> <)
一般用于数值或者日期、时间类型的比较,格式如下:
|
select cname1,cname2,... from tname where cname > cval; select cname1,cname2,... from tname where cname < cval; select cname1,cname2,... from tname where cname >= cval; select cname1,cname2,... from tname where cname <= cval; |
|
mysql> select * from user2 where age>20; + ----+-------+-----+---------+-----+ | id | name | age | address | sex | + ----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | + ----+-------+-----+---------+-----+ 2 rows in set mysql> select * from user2 where age>=20; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | + ----+-------+-----+----------+-----+ 3 rows in set mysql> select * from user2 where age<21; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 2 | helen | 20 | quanzhou | 0 | + ----+-------+-----+----------+-----+ 1 row in set mysql> select * from user2 where age<=21; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | + ----+-------+-----+----------+-----+ 3 rows in set |
逻辑运算
运算符 | 说明 |
---|---|
AND | 多个条件都成立 |
OR | 多个条件中满足一个 |
NOT | 对条件进行取非操作 |
AND(且)
当需要多个条件进行数据过滤的时候,使用这种方式,and的每个表达式都是要成立,过滤出来的数据就是用户需要的。
下面过滤出年龄和性别两个条件都成立的数据,语法格式如下:
|
select cname1,cname2,... from tname where cname1 operators cval1 and cname2 operators cval2 |
|
mysql> select * from user2; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | + ----+-------+-----+----------+-----+ 4 rows in set mysql> select * from user2 where age >20 and sex=1; + ----+-------+-----+---------+-----+ | id | name | age | address | sex | + ----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 4 | weng | 33 | guizhou | 1 | + ----+-------+-----+---------+-----+ 2 rows in set |
OR(或)
当多个条件中只要满足一个条件即进行数据过滤。
下面条件过滤出年龄大于21岁和小于21岁的数据,语法格式如下:
|
select cname1,cname2,... from tname where cname1 operators cval1 or cname2 operators cval2 |
|
mysql> select * from user2; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | + ----+-------+-----+----------+-----+ 4 rows in set mysql> select * from user2 where age>21 or age<21; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 2 | helen | 20 | quanzhou | 0 | | 4 | weng | 33 | guizhou | 1 | + ----+-------+-----+----------+-----+ 2 rows in set |
NOT(取非)
对某个满足的条件进行取反,过滤出来的数据就是用户需要的。
下面过滤不属于年龄大于20的数据,语法格式如下:
|
select cname1,cname2,... from tname where not (cname operators cval) |
|
mysql> select * from user2; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | + ----+-------+-----+----------+-----+ 4 rows in set mysql> select * from user2 where not (age>20); + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 2 | helen | 20 | quanzhou | 0 | + ----+-------+-----+----------+-----+ 1 row in set |
模糊匹配
就像我们上面的那个用户表信息表(包含名称、年龄、地址、性别),当我们要查询名称为s开头的用户时,就可以用到 like 关键字了,他用以模糊匹配数据。
语法格式如下,pattern中可以包含通配符,有两种。%:表示匹配任意一个或n个字符; _:表示匹配任意一个字符。
|
select cname1,cname2,... from tname where cname like pattern; |
%的使用
|
mysql> select * from user2; + ----+--------+-----+----------+-----+ | id | name | age | address | sex | + ----+--------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+----------+-----+ 5 rows in set mysql> select * from user2 where name like 's%' ; + ----+--------+-----+---------+-----+ | id | name | age | address | sex | + ----+--------+-----+---------+-----+ | 3 | sol | 21 | xiamen | 0 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+---------+-----+ 2 rows in set |
_的使用
|
mysql> select * from user2; + ----+--------+-----+----------+-----+ | id | name | age | address | sex | + ----+--------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+----------+-----+ 5 rows in set mysql> select * from user2 where name like 's_l' ; + ----+------+-----+---------+-----+ | id | name | age | address | sex | + ----+------+-----+---------+-----+ | 3 | sol | 21 | xiamen | 0 | + ----+------+-----+---------+-----+ 1 row in set |
注意点
1、不要过度使用模糊匹配得通配符。如果其他操作符能达到相同的目的,应该使用其他操作符
2、对大体量的表进行模糊匹配的时候尽量不要以%开头,比如 like '%username',这样会执行扫表,效率较慢。尽量明确模糊查找的开头部分,比如 like 'brand%',会先定位到brand开头的数据,效率高很多。
范围值检查
BETWEEN AND(区间查询)
操作符 BETWEEN … AND 会选取介于两个值之间的数据范围,这些值可以是数值、文本或者日期,属于一个闭区间查询。
and 的左边val1 和 右边 val2 分别表示两个临界值,等同于数学公式[val1,val2] ,属于这两个区间的数据会被过滤出来(>=val1 和 <=val2),所以语法格式如下:
|
selec cname1,cname2,... from tname where cname between val1 and val2; 等同于 selec cname1,cname2,... from tname where cname >= val1 and cname <= val2; |
查询年龄在[21,25]之间的数据:
|
mysql> select * from user2; + ----+--------+-----+----------+-----+ | id | name | age | address | sex | + ----+--------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+----------+-----+ 5 rows in set mysql> select * from user2 where age between 21 and 25; + ----+--------+-----+---------+-----+ | id | name | age | address | sex | + ----+--------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+---------+-----+ 3 rows in set mysql> select * from user2 where age >= 21 and age <= 25; + ----+--------+-----+---------+-----+ | id | name | age | address | sex | + ----+--------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+---------+-----+ 3 rows in set |
IN(包含查询)
按照上面得数据,如果我们想查出居住地位于福州和厦门得用户数据,应该使用 IN操作符,因为 IN 操作符允许我们在 WHERE 子句中指定多个值,符合这些值中得某一项,既满足条件返回数据。
语法格式如下,in 后面列表的值类型必须一致或兼容,且不支持通配符:
|
select cname1,cname2,... from tname where cname in (val1,val2,...); |
|
mysql> select * from user2; + ----+--------+-----+----------+-----+ | id | name | age | address | sex | + ----+--------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+----------+-----+ 5 rows in set mysql> select * from user2 where address in ( 'fuzhou' , 'xiamen' ); + ----+-------+-----+---------+-----+ | id | name | age | address | sex | + ----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | + ----+-------+-----+---------+-----+ 2 rows in set |
NOT IN(对包含查询取反)
我们上面已经学习过了not得用户,对not后面执行得表达式进行取反得操作,测试下:
|
mysql> select * from user2; + ----+--------+-----+----------+-----+ | id | name | age | address | sex | + ----+--------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+----------+-----+ 5 rows in set mysql> select * from user2 where address not in ( 'fuzhou' , 'quanzhou' , 'xiamen' ); + ----+--------+-----+---------+-----+ | id | name | age | address | sex | + ----+--------+-----+---------+-----+ | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | + ----+--------+-----+---------+-----+ 2 rows in set |
空值检查
IS NULL/IS NOT NULL
判断是否为空,语法格式如下,这边注意的是,对值为null的数据,各种比较运算符、like、between and、in、not in查询都不起作用,只有is null 能够过滤出来。
|
select cname1,cname2,... from tname where cname is null ; 或者 select cname1,cname2,... from tname where cname is not null ; |
|
mysql> select * from user2 where address is null ; + ----+--------+-----+---------+-----+ | id | name | age | address | sex | + ----+--------+-----+---------+-----+ | 5 | selina | 25 | NULL | 0 | + ----+--------+-----+---------+-----+ 1 row in set mysql> select * from user2 where address is not null ; + ----+-------+-----+----------+-----+ | id | name | age | address | sex | + ----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | + ----+-------+-----+----------+-----+ 4 rows in set |
有一种关键字 <=>,可以包含对null值得判断,但是目前用的比较少了,有兴趣可以去查查,这边不赘述。
总结
1、like表达式中的%匹配一个到多个任意字符,_匹配一个任意字符
2、空值查询需要使用IS NULL或者IS NOT NULL,其他查询运算符对NULL值无效。即使%通配符可以匹配任何东西,也不能匹配值NULL的数据。
3、建议创建表的时候,表字段不设置空,给字段一个default 默认值。
4、MySQL支持使用NOT对IN 、BETWEEN 和EXISTS子句取反 。
到此这篇关于MySQL全面瓦解之查询的过滤条件的文章就介绍到这了,更多相关MySQL查询的过滤条件内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://www.cnblogs.com/wzh2010/p/13843027.html
- mysql8.0.15官方最新版本安装教程(MySQL8.0.24版本Release Note的一些改进点)
- 查询按照部门分组的mysql语句(Mysql根据某层部门ID查询所有下级多层子部门的示例)
- mysql中如何进行模糊查询(MySQL模糊查询用法大全正则、通配符、内置函数)
- mysqlinnodb有什么功能(Mysql技术内幕之InnoDB锁的深入讲解)
- mysql主键为什么用varchar(Mysql中varchar类型一些需要注意的地方)
- python操作mysql连接池(详解Python连接MySQL数据库的多种方式)
- 常见的mysql优化策略(MySQL pt-slave-restart工具的使用简介)
- MySQL批量执行SQL脚本文件
- mysql怎么建立主键外键(mysql 实现设置多个主键的操作)
- mysql怎么看脱机数据(MYSQL电话号码,身份证数据脱敏的实现)
- 查看mysql所有权限(MySQL 权限控制细节分析)
- mysql删除表内所有数据(mysql 大表批量删除大量数据的实现方法)
- mysql突然报错206无法连接(详解MySQL连接挂死的原因)
- mysql索引基本知识(MySql索引使用策略分析)
- 创建数据库入门教程mysql(MySQL数据库安装教程一学就会)
- mysql数据库调优技术大全(Mysql数据库性能优化三分表、增量备份、还原)
- 14岁丧父 20岁丧母,从苦难走向辉煌的银泰创始人沈国军(14岁丧父20岁丧母从苦难走向辉煌的银泰创始人沈国军)
- 银泰集团董事长沈国军获评 北京影响力 十大企业家(银泰集团董事长沈国军获评)
- 15帅气男士发型,清爽时尚很有型,喜欢就试试(清爽时尚很有型)
- 哪几个历史人物被影协主席李雪健演的活灵活现(哪几个历史人物被影协主席李雪健演的活灵活现)
- 王伦狭隘,晁盖霸道,宋江奸诈骨头软,只有鲁智深才适合当寨主(王伦狭隘晁盖霸道)
- 他是梁山最早的头目,江湖人称 旱地忽律 ,宋江几乎将其遗忘(他是梁山最早的头目)
热门推荐
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9