20、MySQL-带条件查询（WHERE）

来源：互联网发布：人工智能的未来 mobi 编辑：程序博客网时间：2024/05/17 08:12

条件可以有：比较运算符、范围、集合

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 |
+----+----------+------+

0 0