写在前面

在实际工作中经常会有一些特殊需求，如在开发一个电商系统时，如果我们想查看某个用户的所有订单，或者查看某个用户在某个时间段内所有的订单，此时就需要对订单表中的数据进行筛选，然后按照用户和时间进行过滤，最后得到期望的结果，这是再简单不过的需求了。

但是基于之前学过的知识，我们是无法完成这个需要，此时就需要使用条件查询来对指定表进行操作，那么本篇就来学习SQL中常用的条件查询语句。

本篇是DQL的条件查询篇，主要学习的内容如下所示：（1）条件查询基本语法格式；（2）等于(=)；（3）不等于(<>或者!=)；（4）大于(>)；（5）逻辑查询运算符；（6）并且(AND)；（7）或者(OR)；（8）模糊查询(like)；（9）区间查询(between…and)；（10）IN查询；（11）NOT IN查询；（12）NULL的注意事项；（13）IS NULL查询；（14）IS NOT NULL查询；（15）安全等于(<=>)；（16）经典面试题；（17）文章小结等。

条件查询

在MySQL中，条件语句就是使用关键字where，后面跟上一个或多个条件，这些条件用于对前面的数据进行过滤，之后满足where后面条件的数据才会被返回。

语法格式

条件查询的语法格式如下所示：

1
select 列名 from 表名 where 列 运算符 值;

条件查询运算符

操作符 描述
= 等于
<>或者!= 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于

等于(=)

等于运算符的使用语法规则如下所示：

1
select 列名 from 表名 where 列 = 值;

请注意，该语句用于查询出指定的列和对应的值相等的记录。同时值如果是字符串类型，那么就需要使用单引号或者双括号括起来。

例子1：下面的例子演示了，如何使用等于运算符来查询满足条件的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
mysql> drop table if exists envy5;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table envy5(
    -> a int comment '字段a',
    -> b varchar(10) comment '字段b'
    -> );
Query OK, 0 rows affected (0.61 sec)

mysql> insert into envy5 values(1,'abc'),(2,'hello');
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from envy5;
+------+-------+
| a    | b     |
+------+-------+
|    1 | abc   |
|    2 | hello |
+------+-------+
2 rows in set (0.00 sec)

mysql> select * from envy5 where a =1;
+------+------+
| a    | b    |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.02 sec)

mysql> select * from envy5 where b='hello';
+------+-------+
| a    | b     |
+------+-------+
|    2 | hello |
+------+-------+
1 row in set (0.06 sec)

mysql> select * from envy5 where b="hello";
+------+-------+
| a    | b     |
+------+-------+
|    2 | hello |
+------+-------+
1 row in set (0.00 sec)

不等于(<>或者!=)

不等于运算符有两种写法，因此其使用语法规则也有两种，如下所示：

1
2
3
select 列名 from 表名 where 列 <> 值;
或者
select 列名 from 表名 where 列 != 值;

例子2：下面的例子演示了，如何使用不等于运算符来查询满足条件的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from envy5 where a<> 1;
+------+-------+
| a    | b     |
+------+-------+
|    2 | hello |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from envy5 where a !=1;
+------+-------+
| a    | b     |
+------+-------+
|    2 | hello |
+------+-------+
1 row in set (0.00 sec)

请注意，这个<>是最早的用法，!=是后面加入的语法，两者意义相同，但是在可移植性上前者优于后者，因此推荐在SQL语句中使用<>来做不等判断。

大于(>)

大于运算符的使用语法规则如下所示：

1
select 列名 from 表名 where 列 > 值;

例子3：下面的例子演示了，如何使用大于运算符来查询满足条件的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select * from envy5 where a >1;
+------+-------+
| a    | b     |
+------+-------+
|    2 | hello |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from envy5 where b > 'a';
+------+-------+
| a    | b     |
+------+-------+
|    1 | abc   |
|    2 | hello |
+------+-------+
2 rows in set (0.00 sec)

mysql> select * from envy5 where b > 'ac';
+------+-------+
| a    | b     |
+------+-------+
|    2 | hello |
+------+-------+
1 row in set (0.00 sec)

请注意，数值类型按照其大小进行比较，但是字符按照ASCII码对应的值进行比较，比较时按照字符对应的位置字符逐一进行比较。

其他运算符，如(<、>=、<=）等，由于和前面介绍的差不多，因此就不再赘述了。

逻辑查询运算符

当我们需要使用多个条件进行查询的时候，就会使用到逻辑查询运算符，如下所示：

逻辑运算符 描述
AND 多个条件都成立
OR 多个条件中满足其中一个

并且(AND)

并且逻辑运算符的使用语法规则如下所示：

1
select 列名 from 表名 where 条件1 and 条件2;

该语句仅返回满足条件1和条件2的记录。

例子4：下面的例子演示了，如何使用并且运算符来查询满足条件的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> drop table if exists envy6;
Query OK, 0 rows affected, 1 warning (0.27 sec)

mysql> create table envy6(
    -> a int not null,
    -> b varchar(10) not null
    -> );
Query OK, 0 rows affected (0.50 sec)

mysql> insert into envy6(a,b) values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from envy6;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.02 sec)

mysql> select * from envy6 as e6 where e6.a =2 and e6.b='b';
+---+---+
| a | b |
+---+---+
| 2 | b |
+---+---+
1 row in set (0.03 sec)

可以看到我们使用上述命令查询出了a =2且b=’b’的记录，符合条件的记录只有一条。

或者(OR)

或者逻辑运算符的使用语法规则如下所示：

1
select 列名 from 表名 where 条件1 or 条件2;

该语句会返回满足条件1或者条件2的记录。

例子5：下面的例子演示了，如何使用或者运算符来查询满足条件的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from envy6;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)

mysql> select * from envy6 as e6 where e6.a=1 or e6.b='c';
+---+---+
| a | b |
+---+---+
| 1 | a |
| 3 | c |
+---+---+
2 rows in set (0.00 sec)

可以看到我们使用上述命令查询出了a=1或者b=’c’的记录，符合条件的记录有两条。

模糊查询(like)

现在有一个商品表，里面包含商品id，名称和价格，当我们需要查询名称中包含“水”的商品的时候，此时应该如何操作呢？可以使用SQL中的like关键字进行模糊查询，使用的语法如下所示：

1
select 列名 from 表名 where 列 like pattern;

请注意，其中的pattern里面可以包含通配符，通配符有%和_；%表示匹配任意一个或者多个字符；_表示匹配任意一个字符。

例子6：下面的例子演示了，如何在商品表中查询名称中包含“水”的商品：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
mysql> drop table if exists product;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table product(
    -> id int not null comment '编号',
    -> price double not null comment '价格',
    -> name varchar(10) not null comment '名称'
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into product values(1,10,'矿泉水'),(2,20,'火腿肠'),(3,30,'补水面膜'),(4,40,'健力宝'),(5,50,'王水');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from product;
+----+-------+--------------+
| id | price | name         |
+----+-------+--------------+
|  1 |    10 | 矿泉水       |
|  2 |    20 | 火腿肠       |
|  3 |    30 | 补水面膜     |
|  4 |    40 | 健力宝       |
|  5 |    50 | 王水         |
+----+-------+--------------+
5 rows in set (0.00 sec)

mysql> select * from product p where p.name like '%水%';
+----+-------+--------------+
| id | price | name         |
+----+-------+--------------+
|  1 |    10 | 矿泉水       |
|  3 |    30 | 补水面膜     |
|  5 |    50 | 王水         |
+----+-------+--------------+
3 rows in set (0.00 sec)

注意上面我们使用了%水%这一格式，这样使得水的位置可以随意变化，只要其中包含水字即可。

当然也可以只查询水字在末尾的情况：

1
2
3
4
5
6
7
8
mysql> select * from product p where p.name like '%水';
+----+-------+-----------+
| id | price | name      |
+----+-------+-----------+
|  1 |    10 | 矿泉水    |
|  5 |    50 | 王水         |
+----+-------+-----------+
2 row in set (0.00 sec)

还可以查询商品名称只有2个字，且最后一个字包含水的商品：

1
2
3
4
5
6
7
mysql> select * from product p where p.name like '_水';
+----+-------+--------+
| id | price | name   |
+----+-------+--------+
|  5 |    50 | 王水   |
+----+-------+--------+
1 row in set (0.00 sec)

前面也说了，_代表任意一个字符，如果开发者想要查询商品名称只有3个字，且最后一个字包含水的商品，那么此时条件就变成了__水，这一点需要注意。

区间查询(between…and)

区间查询是指使用between…and操作符，来选取介于某两个值之间的数据，这些值可以使用数值、文本或者日期，注意它属于闭区间查询，也就是两边都包括在内。

区间查询使用的语法格式如下所示：

1
select 列名 from 表名 where 列名 between 值1 and 值2;

也就是说上面的语句用于返回对应列的值在[值1,值2]区间内的记录，同时使用between…and操作符可以提高语句的阅读性。

例子7：下面的例子演示了，如何在商品表中查询价格在25-45之间的商品：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select * from product;
+----+-------+--------------+
| id | price | name         |
+----+-------+--------------+
|  1 |    10 | 矿泉水       |
|  2 |    20 | 火腿肠       |
|  3 |    30 | 补水面膜     |
|  4 |    40 | 健力宝       |
|  5 |    50 | 王水         |
+----+-------+--------------+
5 rows in set (0.00 sec)

mysql> select * from product where price between 25 and 45;
+----+-------+--------------+
| id | price | name         |
+----+-------+--------------+
|  3 |    30 | 补水面膜     |
|  4 |    40 | 健力宝       |
+----+-------+--------------+
2 rows in set (0.00 sec)

当然开发者还可以使用如下的SQL命令来达到相同的目的，但是个人还是建议使用between…and操作符：

1
select * from product as p where p.price >=25 and p.price<=45;

IN查询

现在我们需要查询商品价格为10,30,50的商品信息，应该如何实现呢？开发者可能首先想到的是之前介绍的or查询：

1
2
3
4
5
6
7
8
9
mysql> select * from product where price =10 or price =30 or price =50;
+----+-------+--------------+
| id | price | name         |
+----+-------+--------------+
|  1 |    10 | 矿泉水       |
|  3 |    30 | 补水面膜     |
|  5 |    50 | 王水         |
+----+-------+--------------+
3 rows in set (0.01 sec)

可以发现这条语句比较臃肿，使用了较多的or，此时可以使用in字句来进行替换。

IN操作符允许开发者在where字句中规定多个值，IN查询使用的语法格式如下所示：

1
select 列名 from 表名 where 字段 in (值1,值2,值3,值4,...);

请注意，in后面括号中可以包含多个值，对应记录的字段只要满足in中任意一个都会被返回。同时请注意，in列表中的值的类型需要一致或者兼容，且in列表中不支持通配符。

因此上面的例子可以使用in字句来进行改写，如下所示：

1
2
3
4
5
6
7
8
9
mysql> select * from product as p where p.price in (10,30,50);
+----+-------+--------------+
| id | price | name         |
+----+-------+--------------+
|  1 |    10 | 矿泉水       |
|  3 |    30 | 补水面膜     |
|  5 |    50 | 王水         |
+----+-------+--------------+
3 rows in set (0.00 sec)

可以看到使用in查询比使用or查询语句要简介的多。

NOT IN查询

NOT IN查询和IN查询就刚好相反，IN是返回列表中满足指定条件的记录，而NOT IN则是返回列表中都不满足指定条件的记录。

NOT IN查询使用的语法格式如下所示：

1
select 列名 from 表名 where 字段 not in (值1,值2,值3,值4,...);

例子8：查询商品价格不是10,30,50的商品信息，如下所示：

1
2
3
4
5
6
7
8
mysql> select * from product as p where p.price not in (10,30,50);
+----+-------+-----------+
| id | price | name      |
+----+-------+-----------+
|  2 |    20 | 火腿肠    |
|  4 |    40 | 健力宝    |
+----+-------+-----------+
2 rows in set (0.00 sec)

NULL的注意事项

在使用NULL时，有一些地方需要格外注意，这里先通过代码来进行演示，然后再进行解释。

第一步，创建一个数据表，其中有三个字段，并且a不能为空，b和c可以为空，然后插入了三条数据，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> drop table if exists book1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table book1(
    -> a int not null,
    -> b int,
    -> c varchar(10));
Query OK, 0 rows affected (0.56 sec)

mysql> insert into book1 values (1,2,'a'),(3,null,'b'),(4,5,null);
Query OK, 3 rows affected (0.26 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from book1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 3 | NULL | b    |
| 4 |    5 | NULL |
+---+------+------+
3 rows in set (0.00 sec)

第二步，按照条件查询指定的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
mysql> select * from book1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 3 | NULL | b    |
| 4 |    5 | NULL |
+---+------+------+
3 rows in set (0.00 sec)

mysql> select * from book1 where b <=0;
Empty set (0.01 sec)

mysql> select * from book1 where b =NULL;
Empty set (0.10 sec)

mysql> select * from book1 as b1 where b1.b between 0 and 10;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 4 |    5 | NULL |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select * from book1 where c like '%';
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 3 | NULL | b    |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select * from book1 where c in ('a','b',NULL);
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 3 | NULL | b    |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select * from book1 where c not in ('a','b',NULL);
Empty set (0.00 sec)

仔细看上面的执行结果，可以发现其中有一些不同：
（1）带有条件的查询，对字段b进行查询时，b的值为NULL的都没有出现；（2）对c字段进行like '%'查询，in查询，not in查询，c中值为NULL的记录始终没有查询出来。（3）使用between…and查询，为空的记录也没查询出来。

也就是说查询运算符、like、between…and、in、not in等对NULL值查询不起效。

NULL值专用查询

前面介绍的各种运算符对NULL值均不起效的，因此只能使用IS NULL/IS NOT NULL进行查询。

IS NULL查询

IS NULL查询用于返回值为空的记录，其对应的语法格式如下所示：

1
select 列名 from 表名 where 列 is null;

该语法用于查询指定的列的值为NULL的记录。

例子9：下面的例子展示了，如何使用IS NULL来查询指定的列的值为NULL的记录，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
mysql> drop table if exists book2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table book2(
    -> a int,
    -> b varchar(10));
Query OK, 0 rows affected (0.57 sec)

mysql> insert into book2(a,b) values (1,'a'),(null,'b'),(3,null),(null,null),(4,'c');
Query OK, 5 rows affected (0.18 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from book2;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
| NULL | b    |
|    3 | NULL |
| NULL | NULL |
|    4 | c    |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from book2 as b where b.a is null;
+------+------+
| a    | b    |
+------+------+
| NULL | b    |
| NULL | NULL |
+------+------+
2 rows in set (0.13 sec)

mysql> select * from book2 as b where b.a is null or b.b is null;
+------+------+
| a    | b    |
+------+------+
| NULL | b    |
|    3 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.10 sec)
IS NOT NULL查询

IS NOT NULL查询用于返回值不为空的记录，其对应的语法格式如下所示：

1
select 列名 from 表名 where 列 is not null;

该语法用于查询指定的列的值不为NULL的记录。

例子10：下面的例子展示了，如何使用IS NOT NULL来查询指定的列的值不为NULL的记录，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from book2 as b where b.a is not null;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    3 | NULL |
|    4 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from book2 as b where b.a is not null and b.b is not null;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    4 | c    |
+------+------+
2 rows in set (0.00 sec)

安全等于(<=>)

<=>安全等于符号，即可以判断NULL值，又可以判断普通的数值，但是由于其可读性较低，因此用的不是很多。

例子11：下面的例子展示了，如何使用安全等于(<=>)来查询满足指定的条件的记录，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> select * from book2;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
| NULL | b    |
|    3 | NULL |
| NULL | NULL |
|    4 | c    |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from book2 where a <=> null;
+------+------+
| a    | b    |
+------+------+
| NULL | b    |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from book2 where a <=> 1;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

经典面试题

请问下面两个SQL的查询结果一致么？

1
2
select * from product;
select * from product where name like '%';

这个需要分情况进行讨论：当name中没有NULL值的时候，返回上述两个SQL返回结果是一致的；当name中有NULL值的时候，第二个SQL语句是无法查询出name为NULL的记录。

文章小结

下面对本篇文章进行小结，如下所示：
（1）在like字句中可以使用通配符%来匹配任意个字符；而_可以匹配任意一个字符；
（2）当需要进行空值查询的时候，必须使用IS NULL或者IS NOT NULL，因为其他查询运算符对NULL值无效；
（3）建议在创建表的时候，尽量设置表的字段不为空，并给它设置一个默认值，因为对于空值的判断其实也是挺消耗性能；
（4）后面介绍的安全等于(<=>)运算符在实际工作中不建议大家使用。

那么这样关于DQL的条件查询篇学习就到此为止，后续开始学习其他知识。