条件可以有:比较运算符、范围、集合
1、比较运算符(
> >= <= < = <=> != <>)
mysql> -- 等号mysql> SELECT * FROM user WHERE username='张1';+----+----------+------+| id | username | age |+----+----------+------+| 6 | 张1 | 21 |+----+----------+------+mysql> -- 大于等于mysql> SELECT * FROM user WHERE age>=22;+----+----------+------+| id | username | age |+----+----------+------+| 1 | 张三 | 45 || 7 | 张2 | 22 || 8 | 张3 | 23 || 9 | 大大 | 88 |+----+----------+------+mysql> -- <>等同于!=mysql> SELECT * FROM user WHERE age<>22;+----+----------+------+| id | username | age |+----+----------+------+| 1 | 张三 | 45 || 2 | 长一岁 | 16 || 6 | 张1 | 21 || 8 | 张3 | 23 || 9 | 大大 | 88 |mysql> -- <=>等同于= 但是 <=>可以查找空值,=不可以mysql> INSERT user(username,age) VALUES('WHH',NULL);Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM user WHERE age<=>NULL;+----+----------+------+| id | username | age |+----+----------+------+| 10 | WHH | NULL |+----+----------+------+
2、通过
IS NULL或者
IS NOT NULL 监测是否为空
mysql> SELECT * FROM user WHERE age IS NOT NULL;+----+----------+------+| id | username | age |+----+----------+------+| 1 | 张三 | 45 || 2 | 长一岁 | 16 || 6 | 张1 | 21 || 7 | 张2 | 22 || 8 | 张3 | 23 || 9 | 大大 | 88 |+----+----------+------+
3、查询范围
BETWEEN ... AND 或者
NOT BETWEEN ... AND
mysql> SELECT * FROM user WHERE age BETWEEN 21 AND 50;+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | 张三 | 45 |
| 6 | 张1 | 21 |
| 7 | 张2 | 22 |
| 8 | 张3 | 23 |
+----+----------+------+
4、IN和NOT IN在集合中的形式
IN(值1,值2,...)
mysql> SELECT * FROM user WHERE age IN(22,44,88);+----+----------+------+| id | username | age |+----+----------+------+| 7 | 张2 | 22 || 9 | 大大 | 88 |+----+----------+------+
5、实现模糊查询
LINK
%:代表0个、1个或者多个任意字符
_:代表1个任意字符
mysql> -- 查询用户名中包含张的记录
mysql> SELECT * FROM user WHERE username LIKE '%张%';+----+----------+------+| id | username | age |+----+----------+------+| 1 | 张三 | 45 || 6 | 张1 | 21 || 7 | 张2 | 22 || 8 | 张3 | 23 |+----+----------+------+4 rows in set (0.00 sec)mysql> -- 查询用户名中不包含2的记录mysql> SELECT * FROM user WHERE username NOT LIKE '%2%';+----+----------+------+| id | username | age |+----+----------+------+| 1 | 张三 | 45 || 2 | 长一岁 | 16 || 6 | 张1 | 21 || 8 | 张3 | 23 || 9 | 大大 | 88 || 10 | WHH | NULL |+----+----------+------+mysql> -- 查询用户名为2位的字符mysql> SELECT * FROM user WHERE username LIKE '__';
+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | 张三 | 45 |
| 6 | 张1 | 21 |
| 7 | 张2 | 22 |
| 8 | 张3 | 23 |
| 9 | 大大 | 88 |
+----+----------+------+
6、逻辑运算符(
AND OR)
mysql> -- 查询用户名为2位的字符 并且年龄大于30的记录
mysql> SELECT * FROM user WHERE username LIKE '__' AND age > 30;+----+----------+------+| id | username | age |+----+----------+------+| 1 | 张三 | 45 || 9 | 大大 | 88 |+----+----------+------+