在第三天的时候学习了数据的基本查询结构,包括:查询指定列,返回指定大小结果集,数据排序,数据去重。今天将学习查询里面另外一个基础且重要的查询结构:过滤查询(也叫条件查询),可以说现今你能看到的所有涉及到 RDBMS 软件一定都有 过滤查询 。所以今天的内容一定要掌握,也是一切用到 RDBMS 系统的所需掌握的基础知识之一。

今天涉及到的知识要点:

  • where子句子结构
  • 数值、时间
  • 字符串匹配: like、百分号(%)通配符、下划线(_)通配符
  • 范围值检查:between 、in
  • 空值查询:is null、not
  • 组合查询:and、or、求值顺序

WHERE 子句结构

既然是子句就一定是跟在主句后面,where 子句可以用在 selectupdatedelete 语句中。今天只会讲解在 select 语句中的使用,updatedelete 中的使用会在后面 数据插入、修改和删除 中讲解。首先还是来看一下 where 子句在 select 中的结构

select [distinct] 列1,列2,... from table_name
[where 条件1 [and|or] 条件2 ...]
[order by 列1 desc[, 列2 asc]]
[limit offset, size];

where 条件1 [and|or] 条件2 … 过滤条件。可以使用逻辑连接符(and 或 or)连接多个条件。

条件N 每个条件。格式: <列> <操作符> <值>

大家可以和前一天的 Select 语句做对比看有什么不同

操作符

操作符 说明 操作符 说明
= 等于 > 大于
<> 不等于 >= 大于等于
!= 不等于 !> 不大于
< 小于 between 在指定两个值之间
<= 小于等于 is null 为null的值
!< 不小于 like 匹配
and 逻辑与 or 逻辑或
not 逻辑非

数值、时间过滤

还记得之前在《数据表创建》时说明的数据类型吗?如果没有什么印象可以转过去浏览一遍。在做数据过滤查询的,我们要关注所使用过滤列的数据类型,判断我们所需要使用的操作符和数值表达方式,我们还是通过实例讲解。

查询已开学班级的名称和班主任

分析:查询 已开学 班级名称班主任

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级
  3. 得到哪些信息:名称、班主任
  4. 过滤条件:已开学
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select 名称,班主任

from 班级

where 已开学;

这里的 已开学 怎么表示呢?我们看班级的创建 SQL,会发现列 c_status 的描述是

班级状态【0:报名未开始,1:报名中,2:报名完成,3:已开学,4:已结业】

在结合*条件格式: <列> <操作符> <值>,这时候就比较清晰了吧:班级状态 是/等于 已开学,班级状态在数据库存的数据类型是 数值型 ,这里也就是 3 。这个时候在将语言翻译成代码表达:c_status = 3

现在所有需要的信息都收集完成了,组装我们 SQL 语句:

select c_name, c_head_teacher
from class
where c_status = 3;

执行这个语句会的一下结果

查询标识 id大于10 的班级的标识 id 和名称信息

分析:查询 标识 id大于10** 班级标识 id名称 信息

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级
  3. 得到哪些信息:标识 id,名称
  4. 过滤条件:标识 id大于10
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select 名称,班主任

from 班级

where 标识 id 大于 10;

结合上面的操作符描述 标识 id 大于 10 的代码标识:c_id > 10

所以的 SQL 语句:

select c_name, c_head_teacher
from class
where  c_id > 10;

执行这个语句会的一下结果

查询在2018年4月2日开班的班级名称、标识 id和开班时间

分析:查询在 2018年4月2日开班班级名称标识 id

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级
  3. 得到哪些信息:标识 id,名称,开班时间
  4. 过滤条件:在 2018年4月2日开班
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select 名称,标识 id,开班时间

from 班级

where 在 2018年4月2日开班;

在这里我们发现 开班时间 在表里面的存储类型是日期 date 类型,这里就涉及到日志的表示方式,以下是默认表示方式:

日志类型 表示格式 例子
year YYYY 或 YY 第一种是直接插入4位字符串或者4位数字(推荐)。
第二种是插入2位字符串。如插入‘00’~‘69’,则相当于插入2000~2069;如果插入‘70’~‘99’,则相当于插入1970~1999。第二种情况下插入的如果是‘0’,则与插入‘00’效果相同,都是表示2000年。
第三种是插入2位数字,它与第二种(插入两位字符串)不同之处仅在于:如果插入的是一位数字0,则表示的是0000,而不是2000年。所以在给YEAR类型赋值时,一定要分清0和‘0’,虽然两者相差个引号,但实际效果确实相差了2000年。
time HH:MM:SS 14:23:23
date YYYY-MM-DD 2018-04-23
datetime YYYY-MM-DD HH:MM:SS 2018-04-23 14:23:23
timestamp YYYY-MM-DD HH:MM:SS A、CURRENT_TIMESTAMPNULL无任何输入 这时系统会自动填入当前 timestamp 值(这个需要创建列时设置)
B、`2018-04-23 14:23:23
注意: timestamp数值是与时区相关的。

通过以上分析,得到 SQL 语句:

select c_name, c_status, c_start_time, c_end_time
from class
where  c_start_time='2018-04-02';

执行这个语句会的一下结果

试试

字符串匹配

首先字符串在 SQL 中需要单引号包裹起来,比如:'字符串1''hello world!'

查询班主任姓名为 老师10 的班级信息,结束时间最晚的在前面

分析:查询班主任姓名为 老师10班级信息结束时间最晚的在前面

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级
  3. 得到哪些信息:所有信息
  4. 过滤条件:班主任姓名为 老师10
  5. 排序字段:结束时间 -> 倒序(结束时间最晚的在前面)
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select *

from 班级

where 班主任姓名为 老师10

order by 结束时间 倒序;

所以得到 SQL

select * 
from class
where c_head_teacher = '老师10'
order by c_end_time desc;

执行 sql 得到如下结果

在实际使用字符串匹配查询的时候,处理上面例子演示的 精确查找 以外,我们还会有 模糊查找 的需求(也叫 *模糊匹配*)。比如我们常常有 查找姓王的用户查询名字中含有水字的用户 等需求。

字符串的模糊匹配使用 like 关键字,既然是模式匹配就说明列里面的部分信息我们是不关心的,也不知道他们具体是什么值,这时对于这些不关心的数据我们使用 占位符 。MySQL 数据库支持的占位符有 2 个: %_ ,这两个的比较说明如下:

占位符 说明 实例
% 一次匹配多个任意字符 王%:所有以 开头的任意长度的数据,例如:王,王x、王 xx
%王:所有以 开头的任意长度的数据,例如:王,x王、xx王
%王%:所有包含 的数据,不论王所在位置
_ 一次匹配一个任意字符 王_:所以以 开头的 2 个长度的数据,例如:王 x,王2



试试

查询所有以老师开头的班主任信息,结果去重

分析:查询 所有 以老师开头班主任信息结果去重

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级(班主任信息在班级表里)
  3. 得到哪些信息:班主任信息
  4. 过滤条件:以 “老师” 开头的班主任
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select 去重 班主任

from 班级

where 以 “老师” 开头的班主任;

所得 SQL

select  distinct c_head_teacher 
from class
where c_head_teacher like '老师%';

直接结果

试试

空值查询

在数据库里面还存在一种类型:null ,他表示没有值,意味着我们什么都没有输入。注意这个值和 空字符串'' 的区别:'' 标识值是空字符串(有值);null 标识无值。我们要查询一个列是否包含有无值得数据使用 is null 。大家可以执行下面这个 SQL ,大家可以自行分析一下这个 SQL 所表达的需求

select * from class where c_start_time is null;

由于我们现在数据库里面没有无值的数据,所以结果是空的

现在我们在加入 2 条空值数据,请执行下面的插入 SQL

INSERT INTO `sql-learn`.`class`(`c_id`, `c_name`, `c_head_teacher`, `c_start_time`, `c_end_time`, `c_status`, `c_created`) 
VALUES 
(17, '酱油班1', '酱油老师', NULL, NULL, 3, '2018-04-24 13:03:39'),
(18, '酱油班2', '酱油老师', NULL, NULL, 2, '2018-04-24 13:03:33');

插入完成过后,再执行上面的查询 SQL ,这时候就会出现我们刚才插入的 2 条数据:酱油班1 和 酱油班2 。这时我们查询出了所有的 无值 数据,那我们怎么得到所有 有值 数据呢?这里只要我们对过滤条件取反(或叫取非)就可以了:关键字 not 。请大家执行下面 SQL 分析结果:

select * from class where not c_start_time is null;

not 关键字就是对某一过滤条件取反,它不仅仅是用在 is null 条件上面,可以用在任何一个过滤条件上,甚至后面的组合查询里面的组合条件也是可以使用的。大家可以将前面实验的 SQL 语句的查询条件上面加上 not 在执行看看结果,是否和你理解的一致。

建议大家不要随意使用 not ,这个会导致你的 SQL 语句变复制、不易理解,同时导致后面优化 SQL 变得困难。建议只使用在 is nulllike 操作上。

范围查询

前面的所有查询都是单个值查询,现在如果我们知道了 2 个班主任老师的名称:酱油老师 和 *会计老师1*,我们需要查询这 2 个老师的负责的所有班级怎么办呢?用我们的上面的查询都办不到,现在需要一种新的查询类型:范文查询。范围查询有 2 中格式:between start_value and end_valuein ,between 表示连续值范围查询,多用在数值和时间范围;in 表示非连续值范围,多用在字符串范围查询。具体我们通过实例分析

查询 会计老师1酱油老师 2个老师负责的所有班级信息

分析:查询 会计老师1酱油老师 2个老师负责的所有班级信息

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级
  3. 得到哪些信息:所有信息
  4. 过滤条件:会计老师1酱油老师 (使用 in)
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select *

from 班级

where 会计老师1酱油老师;

在这里我们可以看到同时有 2 个值,同时这两个值没有相关性和连续性,所以这里使用 in

得到以下 SQL

select * from class
where c_head_teacher in ('会计老师1', '酱油老师');

执行 SQL 得到以下结果

查询开班时间在2017-12-03到2018-04-01之间的所有班级信息

分析:查询开班时间在2017-12-03到2018-04-01之间的所有班级信息

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级
  3. 得到哪些信息:所有信息
  4. 过滤条件:开班时间在2017-12-03到2018-04-01之间(使用 between)
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select *

from 班级

where 开班时间在2017-12-03到2018-04-01之间;

在这里我们发现也是 2 个条件值,但是这次的要求是找到 2017-12-03到2018-04-01之间 的所有班级,因为在我们查询数据之前没有办法知道每个班级的具体开班时间,同时从2017-12-03到2018-04-01之间 的所有日期时间点我们没有办法穷举出来(这里可能有人会说可以列举出来,这之间也没有多少天,但是如果我们这里存入的不是天,而是需要精确到秒呢,更进一步这里需要精确到毫秒呢(如创建时间)),所以这里我们就不能用 in 了,需要使用 between

通过上面分析,得到如下 SQL

select * from class
where c_start_time between '2017-12-03' and '2018-04-01';

得到如下结果

试试

组合查询

在前面我们已经学习了很多的过滤条件,但是大家会发现这些条件都是基于某一个属性的单个值或多个值进行查询,那我们如果我们需要同时匹配多个属性的值查询应该怎么办呢?这个时候就需要用到我们接下来学习的 组合查询 了。组合查询顾名思义就是组合了多个过滤条件的查询语句,其中组合的方式有 2 中:andor 。这两个的含义和区别如下:

组合方式 含义 实例
and 逻辑与(同时满足) A and B and C:A、B、C 3个条件必须全部为真才满足
or 逻辑或(满足其一) A or B or C:A、B、C 3个条件 只要其中一个为真就满足

下面我们还是通过 2 个实例来具体学习

查询老师10在2018-04-02这天开班的班级信息

分析:查询老师10在2018-04-02这天开班的班级信息

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级
  3. 得到哪些信息:所有信息
  4. 过滤条件:老师10 、2018-04-02开班
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select *

from 班级

where 老师10 、2018-04-02开班;

这这里我们看到有 老师102018-04-02开班 2 个值,我们发现 老师10 是班主任信息字段,而 2018-04-02开班 是开班时间字段,两个不是同一个字段,这样就不能使用上面的范围查询了(between 和 in)。既然是 2 个字段条件,那我们就来分开分析:

  • 老师10:要求班主任是老师10 => c_head_teacher='老师10'
  • 2018-04-02开班:开班时间在2018-04-02 => c_start_time='2018-04-02'

现在两个条件都分析清楚了,那这两个条件的组合方式是什么呢?根据问题 *老师10在 2018-04-02这天开班*: 必须需要 老师102018-04-02 这天开班的班级信息,老师和开班时间 2 个条件必须满足,所以组合方式是 and,组合得到的过滤条件就是:c_head_teacher='老师10' and c_start_time='2018-04-02' 。现在所有的分析都完成了,来看我们得到的 SQL 语句

所得 SQL 语句

select *
from class
where c_head_teacher='老师10' and c_start_time='2018-04-02';

执行得到以下结果

查询老师5 或 2018-04-02这天开班的班级信息

分析:查询老师10在2018-04-02这天开班的班级信息

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级
  3. 得到哪些信息:所有信息
  4. 过滤条件:老师10 或 2018-04-02开班
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select *

from 班级

where 老师10 或 2018-04-02开班;

根据之前的分析这里的两个条件

  • c_head_teacher='老师5'
  • c_start_time='2018-04-02'

但是这里这里的组合方式和上面会不一样,有发现吗?这里的需求是: 老师5 2018-04-02 ,应该使用的组合方式是 or

所以组合得到的过滤条件就是:c_head_teacher='老师5' or c_start_time='2018-04-02'

所得 SQL 语句

select *
from class
where c_head_teacher='老师5' or c_start_time='2018-04-02';

执行得到以下结果

当组合多个过滤的时候如果同时有 andor 的组合方式,需要注意执行的优先级和先后顺序

sql语句中not and or的执行优先级从高到低依次为: not > and > or

执行时从左到右依次执行

重要建议

对于不能确定的时候可以使用小括号 () 改变或指明优先级

对于同时又 not、and、or 的组合查询始终使用 () 指明优先级

试试

总结

这次的东西有点多,特别是组合查询需要多多联系和实践,

  • where子句子结构,操作符
  • 数值、时间:时间的表示方法
  • 字符串匹配: like、百分号(%)通配符、下划线(_)通配符
  • 范围值检查:between … and … 、in
  • 空值查询:is null、not
  • 组合查询:and、or、优先级和求值顺序