MySQL进阶----过滤条件
select * from ... where ...;
通常我们并不需要查看一个表的所有行,我们需要查看的是具备某种条件的行。前面MySQL使用的基础学习中,就使用过简单的条件过滤
where age > 20
,本文将针对where条件做更详细说明!没有学过基础内容可以看【MySQL使用入门】
简单筛选
符号 | 含义 |
---|---|
> | 大于 |
< | 小于 |
= | 等于 |
>= | 大于等于 |
<= | 小于等于 |
!= | 不等于 |
<> | 不等于 |
复合条件--and 、or和not
来学习MySQL的朋友我默认你学过至少一门编程语言。
一个条件就是一个逻辑表达式,表达式的结果只有true和false。多个表达式可以用
and
和or
来连接,分别表示与和或,还可以用not
来表示非。下面是一些具体使用的示例。
-- 年龄大于10且小于20
select s_name from students where age > 10 and age < 20;
-- 年龄小于10或大于20
select s_name from students where age < 10 or age > 20;
-- 非(年龄小于10或大于20),等价于年龄不小于10且不大于20
select s_name from students where not (age < 10 or age > 20);
- 在使用
and
和or
时需要注意他们的优先级,具体看下面的需求
-- 检索出所有年龄为20或21岁,成绩优秀(90分以上)的学生的信息
select * from students where age = 20 or age = 21 and score >= 90;
-- 上面的语句貌似是我们想要的结果,然而实际上返回的结果却不是我们想要的。
-- 上面条件的实际含义是:20岁的学生或21岁成绩优秀的学生
-- 为什么会这样,原因是在MySQL中and的优先级比or更高,导致后面两个条件先结合,也就是
select * from students where age = 20 or (age = 21 and score >= 90);
-- 所以为了达到我们的本意,应该向下面这样写
select * from students where (age = 20 or age = 21) and score >= 90;
当你不清楚优先级是怎样安排的,使用括号总是一个保险的选择,而且这样做可以让条件的含义更加清晰。这一点相信你在其他语言中也有同样的经验。
字符串的筛选条件
=
这个筛选条件对于字符串来说显然是不够的,下面介绍like
和regexp
like
-- 下面两条语句的结果完全相同
select * from students where s_name = ‘张三‘;
select * from students where s_name like ‘张三‘;
-- like 只有搭配%和_时才和=不一样
-- 下面这条语句筛选出姓张的同学,比如‘张三‘,‘张无忌‘等,它还能匹配‘张‘。
select * from students where s_name = ‘张%‘;
-- 可以匹配‘张三‘、‘张飞‘等,但无法匹配‘张无忌‘。
select * from students where s_name = ‘张_‘;
%
和 _
都是通配符,前者匹配任意字符任意长度,包括0长度,后者匹配单个任意字符。
regexp
除了like这种简单的筛选条件,我们还可以在MySQL中使用正则表达式作为筛选条件
select prod_name from products where regexp ‘ipad air[12]‘;
-- 可以找出商品名:‘ipad air1 64g‘、‘ipad air2 128g‘、‘ipad air1 128g‘等
关于正则表达式的知识这里不详细阐述,请自行查阅相关资料学习。
需要说明的地方:
- MySQL中正则表达式的转义字符为
\\
。 - 注意
like
和regexp
的重要区别,前者对字符串是完全匹配,后者正则是部分匹配,正则只有在加上首位定位符的情况下才是完全匹配。 - MySQL的3.23.4之后的版本不区分正则表达式中的大小写!如果需要区分大小写要使用
regexp binary
文本处理函数
文本处理函数可以在过滤条件中使用
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Right() | 返回串右边的字符 |
Locate() | 找出串的一个子串 |
SubString() | 返回子串的字符 |
LTrim() | 去掉串左边的空格 |
RTrim() | 返回串右边的字符 |
Length() | 返回串的长度 |
Soundex() | 返回串的SOUNDEX值 |
Lower() | 将串转换为小写 |
Upper() | 将串转换为大写 |
-- 示例:找出‘tom‘,‘Tom‘,‘TOM‘等
select name from students where Lower(name) = ‘tom‘;
数值处理函数
函数 | 说明 |
---|---|
Abs() | 返回一个数的绝对值 |
Mod() | 返回除操作的余数 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
Sin() | 返回一个角度的正弦 |
Cos() | 返回一个角度的余弦 |
Tan() | 返回一个角度的正切 |
Sqrt() | 返回一个数的平方根 |
Pi() | 返回圆周率 |
Rand() | 返回一个随机数 |
-- 示例:找出所有成绩个位数为5的学生信息,比如75、85、65等
select * from students where Mod(score,10) = 5;
日期处理函数
函数 | 说明 |
---|---|
AddDate() | 增加一个日期(天、周等) |
AddTime() | 增加一个时间(时、分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format() | 返回一个格式化的日期或时间串 |
Day() | 返回一个日期的天数部分 |
DayOfWeek() | 对于一个日期,返回对应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回一个时间的秒部分 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
-- 示例:找出所有1999年出生的同学的信息
select * from students where Year(birthday) = 1999;
<!>注意 在MySQL中为日期类型赋值格式为yyyy-mm-dd
,datetime类型不仅包含日期还包含时间,所以如果对于想筛选日期为1999-07-24时使用birthday = ‘1999-07-42‘
是不准确的,这样只能匹配00:00:00时间。应该在birthday的外面套上Date()函数。在对日期处理时要善用日期处理函数!
between
用于数值区间或时间区间
-- 示例:找出所有90后同学的信息
select * from students where Date(birthday) between ‘1990-01-01‘ and ‘1990-12-31‘;
in
in
可以表示范围,准确的说是一个集合
- 用法一:圆括号
-- 示例:找出所有年龄为15、18或24的学生的信息
select * from students where age in (15,18,24);
- 用法二:子查询
-- 假设有一个表items存储商品信息,一个表orders存储订单信息(假设一个订单只有一个商品)
-- 示例:找出所有购买ipad air系列产品的订单
select * from orders where orders.item_id in (
select item_id from items where item_name regexp ‘ipad air‘
);
空值检查
null值在MySQL中比价特殊,它表示未指定或者不确定的值。是否具有null值需要用is null
来判断
-- 示例:找出所有未填写简介信息(必须值为null,空字符串不算,仅空格也不算)的书的book_id
select book_id from books where info is null;
<!>注意 NULL与不匹配
在通过过滤选择出不具有特定值的行时,你可能希望返回具有NULL值的行。但是,不行。因为未知具有特殊的含义,数据库不知道它们是否匹配,所以在匹配过滤或不匹配过滤时不返回它们。 因此,在过滤数据时,一定要验证返回数据中确实给出了被过滤列具有NULL的行。
总结
- 比较关系:大于、小于、等于...
- and、or、not
- 字符串筛选:like、正则、文本处理函数
- 函数:数值处理、日期处理、文本处理
- between、in、控制检查