where
运算符 |
说明 |
= |
等于 |
!= |
不等于,某些数据库系统也写作 <> |
> |
大于 |
< |
小于 |
>= |
大于或等于 |
<= |
小于或等于 |
BETWEEN … AND … |
介于某个范围之内,例:WHERE age BETWEEN 20 AND 30 |
NOT BETWEEN …AND … |
不在某个范围之内 |
IN(项1,项2,…) |
在指定项内,例:WHERE city IN('beijing','shanghai') |
NOT IN(项1,项2,…) |
不在指定项内 |
LIKE |
搜索匹配,常与模式匹配符配合使用 |
NOT LIKE |
LIKE的反义 |
IS NULL |
空值判断符 |
IS NOT NULL |
非空判断符 |
NOT、AND、OR |
逻辑运算符,分别表示否、并且、或,用于多个逻辑连接。优先级:NOT > AND > OR |
% |
模式匹配符,表示任意字串,例:WHERE username LIKE '%user' |
REGEXP |
模式匹配符,表示任意字串,例:WHERE username LIKE '%user' |
--测试条件
a = 'test'
table.a = 'test' and table.b != 'test'
a > '3' or b < '5' or c >= '4' or d <= '5'
a > '3' and (b < '5' or c = 10)
a > '3' and (b < '5' or c = 10 and (d = 10 or e = 13))
a like '%test%' or b is null and c is not null
a not in (3,4,5) and b in (3,4,5)
a between '1' and '10' and b not bebween '1' and '13'
a REGEXP "^fo"