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、控制检查