where 是 sql语句 中的条件关键字，类似于其他语言的额 if，只要是条件查询，就一定需要用到各种 条件运算符 来配合，我们就用下面的表来进行简单的学习测试。
mysql> select * from test; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | | 2 | xyq | 20 | | 3 | xyq | 20 | +----+------+------+
算数运算符：
+ - * / % 加 减 乘 除 取余
mysql> select * from test where id=1+1; +----+------+------+ | id | name | age | +----+------+------+ | 2 | xyq | 20 | +----+------+------+
比较运算符：
相等： = 不相等： <> 或 != 大于： > 大于等于： >= 小于：< 小于等于：<= ``` mysql> select * from test where id=1; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | +----+------+------+
mysql> select * from test where name!='dzm'; +----+------+------+ | id | name | age | +----+------+------+ | 2 | xyq | 20 | | 3 | xyq | 20 | +----+------+------+ ``` * 逻辑运算符：
逻辑与：&& 或 and 逻辑或：|| 或 or 逻辑非：! 或 not
``` mysql> select * from test where id=1 and age=20; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | +----+------+------+ 上面的 and 判断条件还可以写成这样 row 关键字可以省略： mysql> select * from test where (id, age) = (1, 20); mysql> select * from test where row(id, age) = (1, 20); +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | +----+------+------+
mysql> select * from user where name='dzm' or age=20; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | dzm | 20 | +----+-------+------+
mysql> select * from test where not name='dzm'; +----+------+------+ | id | name | age | +----+------+------+ | 2 | xyq | 20 | | 3 | xyq | 20 | +----+------+------+ ```
like 模糊查询运算符：
`` like：用来判断某个字符字段的值是否包含给定的字符，如果包含则放入查询结果。 %：代表任意个数的任意字符，也可以叫通配符。 _：代表一个任意字符，也就是用一个_`下杠，就代表一个字符，多个就多个字符。
查询出 name 字段中所有完全等于 y 字符的，这么写的也就相当于 name='y'，多一个少一个都不行，必须相等 select * from test where name like 'y';
查询出 name 字段中所有包含 y 字符的 select * from test where name like '%y%';
查询出 name 字段中所有以 y 字符结尾的 select * from test where name like '%y';
查询出 name 字段中所有以 y 字符开头的 select * from test where name like 'y%';
查询出 name 字段中所有刚好 3 个字符且第 2 个字符为 y 的 select * from test where name like 'y';
查询出 name 字段中所有刚好 3 个字符且第 2 个字符为 y 的 select * from test where name like '__y';
查询出 name 字段中所有刚好 2 个字符且第 1 个字符为 y 的 select * from test where name like 'y_';
查询出 name 字段中所有最少 2 个字符且第 2 个字符为 y 的，y 字符后面有N个字符都行 select * from test where name like '_y%'; ```
``` mysql> select * from test where name like '%y%'; +----+------+------+ | id | name | age | +----+------+------+ | 2 | xyq | 20 | | 3 | xyq | 20 | | 4 | djy | 5 | +----+------+------+
mysql> select * from test where name like '%m'; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | +----+------+------+
mysql> select * from test where name like '__m'; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | +----+------+------+
mysql> select * from test where name like '_z%'; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | +----+------+------+ ``` between 范围限定运算符：
``` between：用于判断某个字段的值是否在给定的两个数据范围之间。
select * from test where id between 2 and 3; 其实就相当于： select * from test where id>=2 and id<=3; ```
``` mysql> select * from test where id between 2 and 3; +----+------+------+ | id | name | age | +----+------+------+ | 2 | xyq | 20 | | 3 | xyq | 20 | +----+------+------+ mysql> select * from test where id between 2 and 1; Empty set (0.00 sec)
也相当于: mysql> select * from test where id>=2 and id<=3; +----+------+------+ | id | name | age | +----+------+------+ | 2 | xyq | 20 | | 3 | xyq | 20 | +----+------+------+ ```
in 运算符：
``` in：用于判断某个字段的值是否存在指定的值列表中，存在就算成立。
相当于 name 字段只要存在 in 后面的列表中即可，也可以说 in 后面的数组包含 name 字段值即可。 select * from test where name in ('d', 'z', 'm', 'dzm'); ```
``` mysql> select * from test where name in ('d', 'z', 'm'); Empty set (0.00 sec) mysql> select * from test where name in ('d', 'z', 'm', 'dzm'); +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | +----+------+------+
也相当于: mysql> select * from test where name='d' or name='z' or name='m' or name='dzm'; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | +----+------+------+ ```
is 运算符：
``` is: 用于判断一个字段值是否存在或不存在，它只有两种写法，要么 is null 要么 is not null: select * from test where name is null; select * from test where name is not null;
null 与 not null 必须用 is 来判断，is 也是专用于这两个值使用，不能写成： select * from test where name=null; select * from test where name=not null;
测试数据: mysql> select * from test; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | | 2 | xyq | 20 | | 3 | xyq | 20 | | 4 | djy | 5 | | 5 | NULL | NULL | +----+------+------+ ```
``` mysql> select * from test where name is null; +----+------+------+ | id | name | age | +----+------+------+ | 5 | NULL | NULL | +----+------+------+
mysql> select * from test where name is not null; +----+------+------+ | id | name | age | +----+------+------+ | 1 | dzm | 20 | | 2 | xyq | 20 | | 3 | xyq | 20 | | 4 | djy | 5 | +----+------+------+ ```
