mysql where 运算符_MySql where 查询条件与运算符

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 |

+----+------+------+

复制代码