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
- centos7怎么安装mysql(Centos7 mysql数据库安装及配置实现教程)
- mysql架构示意图(MySQL创建横向直方图的解决方案)
- mysql binlog模式实际使用(实例验证MySQL|update字段为相同的值是否会记录binlog)
- mysql的基本索引类型(MySQL 隔离数据列和前缀索引的使用总结)
- mysql8.0安装版安装详细教程(mysql 8.0.24版本安装配置方法图文教程)
- mysql数据字段默认值怎么设置(MySQL 字段默认值该如何设置)
- mysql带log的版本(聊聊MYSQL中Redo Log是什么?)
- mysql查询count很慢(MySQL COUNT函数的使用与优化)
- mysql密码找回方法(MySQL8忘记密码的快速解决方法)
- mysql密码设置(mysql密码中有特殊字符&在命令行下登录的操作)
- navicat连接mysql1045解决方法(Navicat 连接服务器端中的docker数据库的方法)
- 怎样查看mysql的ddl窗口(详解MySQL8.0原子DDL语法)
- mysql字符串截取字段(MySQL如何从不固定位置提取字符串元素详解)
- MySql中date、datetime、time类型的区别
- mysql中date_format日期格式化
- mysql日常运维(MySQL从库维护经验分享)
- 8月23日11时16分将迎处暑,逐渐进入气象意义上的秋天(8月23日11时16分将迎处暑)
- 花不语 下 如果重来一次的话,你还会这么选择吗(花不语下如果重来一次的话)
- 城市记忆之上海 最难忘的是老弄堂里的市井味道(城市记忆之上海)
- 太鸡贼了,这老小区轻松搞定了停车问题(这老小区轻松搞定了停车问题)
- 太鸡贼了,这老小区轻松搞定了停车问题(这老小区轻松搞定了停车问题)
- 节日我在岗|警景相融 平安相伴(节日我在岗警景相融)
热门推荐
- sqlserver中复合索引(浅析SQL Server 聚焦索引对非聚集索引的影响)
- phparray函数的用法(php array_chunk函数用法与注意事项)
- webui控件在前端开发中的作用(HTML5 weui使用笔记)
- JS文件智能提示另一个JS文件中的成员
- centos如何安装mysql8.0版本(Centos7下安装MySQL8.0.23的步骤小白入门级别)
- http403错误怎么解决(HTTP 错误 403.1 - 禁止访问:执行访问被拒绝。 解决方法该页无法显示)
- nginx 处理服务器错误(nginx服务器异常502 bad gateway原因排查)
- 怎样在云服务器上搭建宝塔面板(利用宝塔面板同步服务器时间的实现方法)
- sqlserver怎么修改表结构(SQL Server中修改“用户自定义表类型”问题的分析与方法)
- react重点和难点(关于React状态管理的三个规则总结)