09｜第九话：基础篇-MySQL之初级查询

要说MySQL最常用的功能是什么？那一定是查询功能，在第四话《04｜第四话：基础篇-MySQL简单的增删改查操作》中我们学习了查询语句，其声明语法是：

SELECT * FROM 表名;

本篇文章我们展开来看看简单查询中还有多少尚未挖掘的知识，不过巧妇难为无米之炊，在进行各种查询之前，我们有必要先准备一些数据。本篇文章整体脉络如下：

一、数据准备

准备一张表，名字叫做users，是我们某个系统里面的用户信息表，表结构如下：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `username` varchar(32) NOT NULL COMMENT '用户名',
  `password` varchar(64) NOT NULL COMMENT '密码',
  `nickname` varchar(32) DEFAULT NULL COMMENT '昵称',
  `realname` varchar(128) DEFAULT NULL COMMENT '真实姓名',
  `face` varchar(1024) NOT NULL COMMENT '头像',
  `mobile` varchar(32) DEFAULT NULL COMMENT '手机号',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱地址',
  `sex` tinyint(11) DEFAULT NULL COMMENT '性别 1:男  0:女  2:保密',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `created_time` datetime NOT NULL COMMENT '创建时间',
  `updated_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

我们预置几条数据进来：

INSERT INTO `users` (`username`, `password`, `nickname`, `realname`, `face`, `mobile`, `email`, `sex`, `birthday`, `created_time`, `updated_time`)
VALUES
 ('fossi', 'Qpf0SxOVUjUkWySXOZ16kw==', 'fossi', NULL, 'http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png', NULL, NULL, 2, '1900-01-01', '2022-08-15 22:11:58', '2022-08-15 22:11:58'),
 ( '我去个地方', 'Qpf0SxOVUjUkWySXOZ16kw==', '我去个地方', NULL, 'http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png', NULL, NULL, 1, '1900-01-01', '2022-08-16 23:14:12', '2022-08-16 23:14:12'),
 ( 'alite', 'Qpf0SxOVUjUkWySXOZ16kw==', 'alite', NULL, 'http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png', NULL, NULL, 0, '1900-01-01', '2022-08-18 13:25:30', '2022-08-18 13:25:30'),
 ( 'yummy', '4QrcOUm6Wau+VuBX8g+IPg==', 'yummy', NULL, 'http://bloghello.oursnail.cn/avatar.png', NULL, NULL, 0, '1900-01-01', '2022-08-25 17:46:25', '2022-08-25 17:46:25');

下面我们围绕这张用户信息表学习更多初级查询相关的知识。

二、查询所有数据

查询某一列的语法是：

SELECT 列名 FROM 表名;

比如要查询用户信息表所有的用户注册的名字，可以这样查询：

mysql> select username from users;
+-----------------+
| username        |
+-----------------+
| fossi           |
| 我去个地方      |
| alite           |
| yummy           |
+-----------------+
4 rows in set (0.00 sec)

我们可以看到第一行显示的是username，因为我们的字段名字就叫做username，有的时候为了更加清楚地看到展示列的含义，我们可以使用别名，语法是：

SELECT 列名 [AS] 列的别名 FROM 表名;

其中AS是可写可不写的，比如我定义这列的名称叫做“用户姓名”，我们可以这样查询：

mysql> select username as '用户姓名' from users;
+-----------------+
| 用户姓名        |
+-----------------+
| fossi           |
| 我去个地方      |
| alite           |
| yummy           |
+-----------------+
4 rows in set (0.00 sec)

能查询一个列，就可以查询多个列，语法是：

SELECT 列名1, 列名2, … 列名n FROM 表名;

mysql> select id as '主键', username as '用户姓名', nickname as '用户昵称' from users;
+--------+-----------------+-----------------+
| 主键   | 用户姓名          | 用户昵称         |
+--------+-----------------+-----------------+
|      1 | fossi           | fossi           |
|      2 | 我去个地方        | 我去个地方       |
|      3 | alite           | alite           |
|      4 | yummy           | yummy           |
+--------+-----------------+-----------------+
4 rows in set (0.00 sec)

如果需要把记录中的所有列都查出来，MySQL也提供一个省事儿的办法，我们之前也介绍过，就是直接用星号*来表示要查询的东西：

SELECT * FROM 表名;

但是，需要注意的是，除非你确实需要表中的每个列，否则一般最好别使用星号来查询所有列，虽然星号看起来很方便，不用明确列出所需的列，但是查询不需要的列通常会降低性能。

三、限制查询结果条数

每个管理系统都有分页展示的需求，比如第一页展示10条，这就会用到分页查询，语法是在查询语句的后面增加：

LIMIT 开始行, 限制条数;

开始行指的是我们想从第几行数据开始查询，限制条数是结果集中最多包含多少条记录。需要注意，计算机中一般都是从0开始计算，所以第一条数据的位置是0，比如从第一条数据开始、查询条数为2条：

mysql> select id as '主键', username as '用户姓名', nickname as '用户昵称' 
from users limit 0,2;
+--------+-----------------+-----------------+
| 主键   | 用户姓名        | 用户昵称        |
+--------+-----------------+-----------------+
|      1 | fossi           | fossi           |
|      2 | 我去个地方      | 我去个地方      |
+--------+-----------------+-----------------+
2 rows in set (0.00 sec)

这里的逻辑比较好理解，比如我们接到一个分页展示的页面需求，前端会传递pageno和pagesize两个参数给到后端接口，pogeno是指查询第几页，pagesize是指一页返回多少条数据，一般第一页传递pageno=0、pagesize=10，表示给我返回第一页10条数据我要去展示给用户，此时后端接口执行的查询sql大概是：select xxx from xxx limit 0,10；当用户要查询第二页的时候，前端传递pageno=1、pagesize=10，表示给我返回第二页10条数据我要去展示给用户，此时后端接口执行的查询sql应该是：select xxx from xxx limit 1,10。

LIMIT后边也可以只有一个参数，那这个参数就代表着限制行数。也就是说我们可以不指定开始行，默认的开始行就是第0行：

mysql> select id as '主键', username as '用户姓名', 
nickname as '用户昵称' from users limit 2;
+--------+-----------------+-----------------+
| 主键   | 用户姓名        | 用户昵称        |
+--------+-----------------+-----------------+
|      1 | fossi           | fossi           |
|      2 | 我去个地方      | 我去个地方      |
+--------+-----------------+-----------------+
2 rows in set (0.00 sec)

四、对查询结果去重

有的时候我们查询某个列的数据时会有一些重复的结果，比如我们查询用户信息表的头像列：

mysql> select face from users;
+------------------------------------------------------------------------------+
| face                                                                         |
+------------------------------------------------------------------------------+
| http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png |
| http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png |
| http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png |
| http://bloghello.oursnail.cn/avatar.png                                      |
+------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

可以看到有三个用户用的是同样的头像（系统默认头像），如果我们想做下去重，可以这样来做：

SELECT DISTINCT 列名 FROM 表名;

mysql> select distinct face from users;
+------------------------------------------------------------------------------+
| face                                                                         |
+------------------------------------------------------------------------------+
| http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png |
| http://bloghello.oursnail.cn/avatar.png                                      |
+------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

distinct后面也可以跟随多个字段，表示基于多列进行去重，对于查询多列的情况，两条结果重复的意思是：两条结果的每一个列中的值都相同，语法是：

SELECT DISTINCT 列名1, 列名2, … 列名n FROM 表名;

distinct后面有几个字段，就基于这几个字段一起做重复判断，只要某一行数据有一个字段值不同，就是不重复的。

反过来说，如果distinct后面有几个字段，做不到只针对其中一列做去重。

五、对查询结果排序

很多时候，我们需要基于某个字段或多个字段进行排序，比较典型的场景是基于时间排序。

我们来看看基于单列排序的语法：

ORDER BY 列名 ASC|DESC

ASC和DESC指的是排序方向。ASC是指按照指定列的值进行由小到大进行排序，也叫做升序，DESC是指按照指定列的值进行由大到小进行排序，也叫做降序，中间的|表示这两种方式只能选一个，如果不填，默认是ASC。

比如这里按照用户信息的更新时间倒序排列展示：

mysql> select username as '用户姓名',updated_time as '更新时间' 
from users order by updated_time desc;
+-----------------+---------------------+
| 用户姓名          | 更新时间            |
+-----------------+---------------------+
| yummy           | 2022-08-25 17:46:25 |
| alite           | 2022-08-18 13:25:30 |
| 我去个地方        | 2022-08-16 23:14:12 |
| fossi           | 2022-08-15 22:11:58 |
+-----------------+---------------------+
4 rows in set (0.00 sec)

也可以同时指定多个排序的列，多个排序列之间用逗号,隔开就好了，就是这样：

ORDER BY 列1 ASC|DESC, 列2 ASC|DESC …

比如我们先按照姓名正序倒序排列、再按照更新时间正序排列，可以写成：

mysql> select username as '用户姓名',updated_time as '更新时间' 
from users order by username desc , updated_time asc;
+-----------------+---------------------+
| 用户姓名        | 更新时间            |
+-----------------+---------------------+
| 我去个地方      | 2022-08-16 23:14:12 |
| yummy           | 2022-08-25 17:46:25 |
| fossi           | 2022-08-15 22:11:58 |
| alite           | 2022-08-18 13:25:30 |
+-----------------+---------------------+
4 rows in set (0.00 sec)

可以看到，写在前面的优先，后面根据前面的排序结果再排序，请注意这么个先后关系。

我们可以让ORDER BY语句和LIMIT语句结合使用，不过 ORDER BY 语句必须放在 LIMIT 语句前边，比如查询最新更新时间的一个用户是谁，可以这样查询：

mysql> select username as '用户姓名',updated_time as '更新时间' 
from users order by updated_time desc limit 1;
+--------------+---------------------+
| 用户姓名     | 更新时间            |
+--------------+---------------------+
| yummy        | 2022-08-25 17:46:25 |
+--------------+---------------------+
1 row in set (0.00 sec)

六、简单搜索条件

数据那么多，我们往往只对里面一小部分感兴趣，比如有10万个用户，我们想看看有没有fossi这个用户以及他的基本信息，就需要按照名字为fossi去查询，数据库根据此搜索条件去筛选，并返回符合条件的结果。

mysql> select id,username from users where username='fossi';  
+----+----------+
| id | username |
+----+----------+
|  1 | fossi    |
+----+----------+
1 row in set (0.00 sec)

由于这里使用了“=”，所以有的时候称之为等值条件查询，简称为等值查询，后面的文章可能会用到此名词。

也可以查询名字不为fossi的用户，使用!=或<>来判断即可：

mysql> select id,username from users where username!='fossi';
+----+-----------------+
| id | username        |
+----+-----------------+
|  2 | 我去个地方      |
|  3 | alite           |
|  4 | yummy           |
+----+-----------------+
3 rows in set (0.00 sec)

mysql> select id,username from users where username<>'fossi';
+----+-----------------+
| id | username        |
+----+-----------------+
|  2 | 我去个地方      |
|  3 | alite           |
|  4 | yummy           |
+----+-----------------+
3 rows in set (0.00 sec)

也支持范围的查询，比如查询id大于2的所有用户信息：

mysql> select id,username from users where id > 2;
+----+----------+
| id | username |
+----+----------+
|  3 | alite    |
|  4 | yummy    |
+----+----------+
2 rows in set (0.00 sec)

如果需要查询id为2到4之间的所有用户，可以用>=和<=来做范围的限定，也可以使用关键字between来实现，我们来看看：

mysql> select id,username from users where id >= 2 and id <= 4;
+----+-----------------+
| id | username        |
+----+-----------------+
|  2 | 我去个地方      |
|  3 | alite           |
|  4 | yummy           |
+----+-----------------+
3 rows in set (0.00 sec)

mysql> select id,username from users where id between 2 and 4;
+----+-----------------+
| id | username        |
+----+-----------------+
|  2 | 我去个地方      |
|  3 | alite           |
|  4 | yummy           |
+----+-----------------+
3 rows in set (0.00 sec)

以上其实我们是借助了比较操作符来实现的，MySQL中支持多种比较操作符：

操作符	示例	描述
=	a = b	a等于b
<>或者!=	a <> b	a不等于b
<	a < b	a小于b
<=	a <= b	a小于或等于b
>	a > b	a大于b
>=	a >= b	a大于或等于b
BETWEEN	a BETWEEN b AND c	满足 b <= a <= c
NOT BETWEEN	a NOT BETWEEN b AND c	不满足 b <= a <= c

七、匹配列表中的元素

有时候搜索条件中指定的匹配值并不是单个值，而是一个列表，只要匹配到列表中的某一项就算匹配成功，这种情况可以使用IN操作符：

操作符	示例	描述
IN	a IN (b1, b2, …)	a是b1, b2, … 中的某一个
NOT IN	a NOT IN (b1, b2, …)	a不是b1, b2, … 中的任意一个

比如同时查询用户名是fossi和yummy的用户信息，可以这样写：

mysql> select id,username from users where username in ('fossi','yummy');
+----+----------+
| id | username |
+----+----------+
|  1 | fossi    |
|  4 | yummy    |
+----+----------+
2 rows in set (0.00 sec)

八、匹配NULL值

前边说过，NULL代表没有值，意味着你并不知道该列应该填入什么数据，在判断某一列是否为NULL的时候并不能单纯的使用=操作符，而是需要专业判断值是否是NULL的操作符：

操作符	示例	描述
IS NULL	a IS NULL	a的值是NULL
IS NOT NULL	a IS NOT NULL	a的值不是NULL

比如查询真实名字为null的用户有哪些：

mysql> select id,username,realname from users where realname is null;
+----+-----------------+----------+
| id | username        | realname |
+----+-----------------+----------+
|  1 | fossi           | NULL     |
|  2 | 我去个地方      | NULL     |
|  3 | alite           | NULL     |
|  4 | yummy           | NULL     |
+----+-----------------+----------+
4 rows in set (0.01 sec)

再次强调一遍，不能直接使用普通的操作符来与NULL值进行比较，必须使用IS NULL或者IS NOT NULL！

九、多个搜索条件的查询

上面叨叨了单个维度的搜索条件，而实际工程中往往会需要用到多个搜索条件，比如我想搜索：名字叫做fossi并且性别是男的用户，这里的搜索条件分为两部分，一个是限制名字等于fossi，一个条件限定性别是男，这两个条件需要同时满足才行，这种情况我们可以使用AND操作符来连接多个搜索条件，sql可以这么写：

mysql> select id,username from users where username='fossi' and sex=0;
Empty set (0.00 sec)

为啥查出来是个空结果集呢？原来该用户注册的时候性别选择的是2，即未知：

mysql> select id,username from users where username='fossi' and sex=2;
+----+----------+
| id | username |
+----+----------+
|  1 | fossi    |
+----+----------+
1 row in set (0.00 sec)

这次由于同时满足了username等于fossi并且性别是未知的条件，因此匹配到了相应的数据进行返回。如果将and的前后两个条件调换位置，可以容易推断出实际上是一个效果。

对于and，就会有or，在给定多个搜索条件的时候，我们有时需要某条记录在符合某一个搜索条件的时候就将其加入结果集中，这种情况我们可以使用OR操作符来连接多个搜索条件。

比如查询用户中性别是男或者未知的数据：

mysql> select id,username,sex from users where sex=0 or sex=2;             
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | fossi    |    2 |
|  3 | alite    |    0 |
|  4 | yummy    |    0 |
+----+----------+------+
3 rows in set (0.00 sec)

我们难度升级，此时我希望查询username为fossi，并且sex是0或者2，该如何实现呢？

我们看一眼表的数据，实际上fossi这个用户的sex是2，因此如果查询正确，那么只有一条数据满足条件并返回：

mysql> select id,username,sex from users where username='fossi' and sex=0 or sex=2;
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | fossi    |    2 |
+----+----------+------+
1 row in set (0.00 sec)

但是此时我调换and前后两个条件的位置，会发生一些意想不到的结果：

mysql> select id,username,sex from users where sex=0 or sex=2 and username='fossi';
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | fossi    |    2 |
|  3 | alite    |    0 |
|  4 | yummy    |    0 |
+----+----------+------+
3 rows in set (0.00 sec)

因为：AND操作符的优先级高于OR操作符，也就是说在判断某条记录是否符合条件时会先检测AND操作符两边的搜索条件。所以

where sex=0 or sex=2 and username=’fossi’

这个搜索条件被分解为了下面两条：

sex=0
sex=2 and username=’fossi’

这两个搜索条件之间使用or来连接，因此结果集还将sex=0的所有数据也一同返回了。

为了避免这种尴尬，在一个查询中有多个搜索条件时最好使用小括号()来显式的指定各个搜索条件的检测顺序，比如上边的例子可以写成下边这样：

mysql> select id,username,sex from users where (sex=0 or sex=2) and username='fossi';
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | fossi    |    2 |
+----+----------+------+
1 row in set (0.01 sec)

括号里面作为一个整体来执行，而不会因为优先级问题导致sql执行不符合我们的预想，这个规则在一般的编程语言中都很适用，相信任何掌握一门主流编程语言的同学都可以很容易地接受这个规则了。

十、模糊查询

很多时候我们都需要使用到模糊查询，比如数据库中存储的是很多商品，我想搜索里面有没有一个叫做马卡龙的商品，由于马卡龙可能并不是商品全称，可能叫做好吃的马卡龙，也可能叫做马卡龙小甜点，此时我们需要使用模糊查询来实现。MySQL中使用下边这两个操作符来支持模糊查询：

操作符	示例	描述
LIKE	a LIKE b	a匹配b
NOT LIKE	a NOT LIKE b	a不匹配b

既然我们不能完整描述要查询的信息，那就用某个符号来替代这些模糊的信息，这个符号就被称为通配符。MySQL中支持下边这两个通配符：

%： matches any number of characters, even zero characters.
_ ：matches exactly one character.

以用户信息表为例，有一个用户叫做：我去个地方，如果只记得有个关键字叫做“地方”，我该如何将其查出来呢？

mysql> select id,username from users where username like '%地方%';
+----+-----------------+
| id | username        |
+----+-----------------+
|  2 | 我去个地方      |
+----+-----------------+
1 row in set (0.01 sec)

这里的%代表任意一个字符串，“%地方%”表示我们仅仅知道名字里面包含了这两个字，但是我们不清楚前面和后面还有没有字符，因此我们前后都要加上“%”。

如果我们明确知道一个用户的名字前缀叫做“我去”，此时就是以“我去”去模糊匹配，只需要写为“地方%”即可：

mysql> select id,username from users where username like '我去%';
+----+-----------------+
| id | username        |
+----+-----------------+
|  2 | 我去个地方      |
+----+-----------------+
1 row in set (0.01 sec)

如果我们查询以“地方”打头的名字，显然就不会查询到了：

mysql> select id,username from users where username like '地方%';
Empty set (0.00 sec)

我们可以看到，%是替代的是一个字符串，这个字符串可以是空，也可以是任意长度的字符串，范围是未知的，导致匹配的范围较大。有的时候我们知道要查询的字符串中有多少个字符，我们就可以用_来做通配符，就像是支付宝的万能福卡，一张万能福卡能且只能代表任意一张福卡(也就是它不能代表多张福卡)。

比如我要想查询“我去个地方”，但是我只知道名字是五个字并且前缀是“我去个”，那么我就可以这样查询：

mysql> select id,username from users where username like '我去个__';
+----+-----------------+
| id | username        |
+----+-----------------+
|  2 | 我去个地方      |
+----+-----------------+
1 row in set (0.00 sec)

可以看到依然可以查询出来，但是多一个下划线或少一个下划线就匹配不上了：

mysql> select id,username from users where username like '我去个_';
Empty set (0.00 sec)

mysql> select id,username from users where username like '我去个___';
Empty set (0.01 sec)