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 |
+----+------+------+
复制代码