MySQL–WHERE专题

MySQL进阶—-过滤条件

select * from ... where ...;

通常我们并不需要查看一个表的所有行,我们需要查看的是具备某种条件的行。前面MySQL使用的基础学习中,就使用过简单的条件过滤 where age > 20 ,本文将针对where条件做更详细说明!没有学过基础内容可以看【MySQL使用入门】

简单筛选

符号 含义
> 大于
< 小于
= 等于
>= 大于等于
<= 小于等于
!= 不等于
<> 不等于

复合条件–and 、or和not

来学习MySQL的朋友我默认你学过至少一门编程语言。

一个条件就是一个逻辑表达式,表达式的结果只有true和false。多个表达式可以用andor来连接,分别表示,还可以用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); 	
  • 在使用andor 时需要注意他们的优先级,具体看下面的需求
-- 检索出所有年龄为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;

当你不清楚优先级是怎样安排的,使用括号总是一个保险的选择,而且这样做可以让条件的含义更加清晰。这一点相信你在其他语言中也有同样的经验。

字符串的筛选条件

=这个筛选条件对于字符串来说显然是不够的,下面介绍likeregexp

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中正则表达式的转义字符\\
  • 注意likeregexp的重要区别,前者对字符串是完全匹配,后者正则是部分匹配,正则只有在加上首位定位符的情况下才是完全匹配。
  • 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、控制检查