MySQL中的高级查询

通过条件查询可以查询到符合条件的数据，但如同要实现对字段的值进行计算、根据一个或多个字段对查询结果进行分组等操作时，就需要使用更高级的查询，MySQL提供了聚合函数、分组查询、排序查询、限量查询、内置函数以实现更复杂的查询需求。接下来将针对这些高级查询的知识进行讲解。

1.聚合函数

在实际开发中，经常需要做一些数据统计操作，例如统计某个字段的最大值、最小值、平均值等。像这样对一组值执行计算并将计算后的值返回的操作称为聚合操作，聚合操作一般通过聚合函数实现。使用聚合函数实现查询的基本语法格式如下。

SELECT [字段名1，字段名2，···，字段名n] 聚合函数 FROM 数据表名；

MySQL中常用的聚合函数如下：

COUNT(e)

返回查询的记录总数，参数e可以是字段名或*

SUM(e)

返回e字段中值的总和

AVG(e)

返回e字段中值的平均值

MAX(e)

返回e字段中的最大值

MIN(e)

返回e字段中的最小值

上面的聚合函数都是MySQL中内置的函数，使用者根据函数的语法格式直接调用即可。

接下来，通过一些例子学习聚合函数在数据统计中的使用。

为了方面演示，我把之前的员工表删了，又创建了一个员工表并插入了一些数据：

mysql> CREATE TABLE emp(
    -> empno INT PRIMARY KEY,
    -> ename VARCHAR(16),
    -> job VARCHAR(16),
    -> sal INT,
    -> bon INT
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int         | NO   | PRI | NULL    |       |
| ename | varchar(16) | YES  |     | NULL    |       |
| job   | varchar(16) | YES  |     | NULL    |       |
| sal   | int         | YES  |     | NULL    |       |
| bon   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

表格介绍：

empno：员工编号

ename ：员工姓名

job：职位

sal：工资

bon：奖金

插入语句：

mysql> INSERT INTO emp VALUES
    -> (9880,'张三','销售',3000,200),
    -> (9885,'李四','保洁',2500,100),
    -> (9775,'王五','销售',3500,500),
    -> (9900,'孙七','销售',2500,200),
    -> (9990,'周八','经理',7000,1000)
    -> (9770,'吴九','保洁',2500,null),
    -> (9888,'郑十','销售',3500,null);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

表中的具体内容如下：

mysql> SELECT*FROM emp;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9770 | 吴九   | 保洁   | 2500 | NULL |
|  9775 | 王五   | 销售   | 3500 |  500 |
|  9880 | 张三   | 销售   | 3000 |  200 |
|  9885 | 李四   | 保洁   | 2500 |  100 |
|  9888 | 郑十   | 销售   | 3500 | NULL |
|  9900 | 孙七   | 销售   | 2500 |  200 |
|  9990 | 周八   | 经理   | 7000 | 1000 |
+-------+--------+--------+------+------+
7 rows in set (0.00 sec)

1.COUNT()函数

COUNT()函数用于检索数据表行中的值的计数，COUNT(*)可以统计数据表中记录的总条数，即数据表中有多少行记录。例如，想要使用SQL语句查询员工表中有多少个员工的记录。在查询时可以使用COUNT()函数进行统计，具体SQL语句及执行结果如下。

mysql> SELECT COUNT(*) FROM emp;

+----------+

| COUNT(*) |

+----------+

| 7 |

+----------+

1 row in set (0.00 sec)

由上述执行结果可以得出，数据表emp中有7条记录，也就是说员工表中有7个员工的记录。

COUNT()函数中的参数除可以使用号，还可以使用字段的名称。两者不同的是，使用COUNT()统计结果时，相当于统计数据表的行数，不会忽略字段中值为NULL的行；如果使用COUNT(字段)统计，那么字段值为NULL的记录不会被统计。例如，想要使用SQL语句查询员工表中奖金不为NULL的员工个数，具体SQL语句及执行结果如下。

mysql> SELECT COUNT(bon) FROM emp;
+------------+
| COUNT(bon) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

由上述执行结果可以得出，数据表emp中奖金不为NULL的员工有5个。

2.SUN()函数

如果字段中存放的是数值型数据，需要统计该字段中所有值的总数，可以使用SUM()函数。SUM()函数会对指定字段中的值进行累加，并且在数据累加时忽略字段中的NULL值。

例如，想要使用SQL语句查询员工表中员工奖金的总和。可以在查询时使用SUM()函数进行统计，具体SQL语句及执行结果如下。

mysql> SELECT SUM(bon) FROM emp;
+----------+
| SUM(bon) |
+----------+
|     2000 |
+----------+
1 row in set (0.00 sec)

上述SELECT语句使用SUM()函数对COMM字段中的值进行求和统计，执行结果中显示员工奖金总和为2000元。

3.AVG()函数

如果字段中存放的是数值型数据，需要统计该字段中所有值的平均值，可以使用AVG()函数。AVG()函数会计算指定字段值的平均值，并且计算时会忽略字段中的NULL值。

例如，想要使用SQL语句查询员工表中员工的平均奖金。查询时可以使用AVG()函数进行统计，具体SQL语句及执行结果如下。

mysql> SELECT AVG(bon) FROM emp;
+----------+
| AVG(bon) |
+----------+
| 400.0000 |
+----------+
1 row in set (0.00 sec)

上述SELECT语句使用AVG()函数计算bon字段的平均值。由执行结果可以得出，bon字段的平均值为400.0000。AVG()函数在计算时会忽略bon字段中的NULL值，即只对非NULL的数值进行累加，然后将累加和除以非NULL的行数计算出平均值。

上面已经设置了是五个人有奖金，有两个是没有奖金的，奖金是空值，因此那两个人也不会计算入内。

如果想要统计所有员工的平均奖金，即奖金平均到所有员工身上，可以借助IFNULL()函数。

IFNULL(v1,v2)

上述格式表示，如果v1的值不为NULL，则返回v1的值，否则返回v2。例如，想要使用SQL语句查询所有员工的平均奖金。查询时可以调用AVG()函数和IFNULLO函数进行统计，先调用IFNULL()函数将bon字段中所有的NULL值转换为0，再调用AVG()函数统计平均值，具体SQL语句及执行结果如下。

mysql> SELECT AVG(IFNULL(BON,0)) FROM emp;
+--------------------+
| AVG(IFNULL(BON,0)) |
+--------------------+
|           285.7143 |
+--------------------+
1 row in set (0.00 sec)

上述SELECT语句在执行AVG()函数之前调用IFNULL()函数对bon字段中的值进行判断，如果是NULL值就转换成0返回；由执行结果并结合数据表中的数据可以得出，本次统计的平均奖金是所有员工的平均奖金。

4.MAX()函数

MAX(函数用于计算指定字段中的最大值，如果字段的值是数值类型，则比较的是值的大小。例如，想要使用SQL语句查询员工表中最高的工资。查询时可以使用MAX**()**函数进行计算，具体SQL语句及执行结果如下。

mysql> SELECT MAX(sal) FROM emp;
+----------+
| MAX(sal) |
+----------+
|     7000 |
+----------+
1 row in set (0.00 sec)

上述SELECT语句使用MAX**()**函数获取了sal字段中最大的数值。

5.MIN()函数

MIN()函数用于计算指定字段中的最小值，如果字段的值是数值类型，则比较的是值的大小。例如，想要使用SQL语句查询员工表中最低的工资。查询时可以使用MIN()函数进行计算，具体SQL语句及执行结果如下。

mysql> SELECT MIN(sal) FROM emp;
+----------+
| MIN(sal) |
+----------+
|     2500 |
+----------+
1 row in set (0.00 sec)

在上述代码中，使用MIN()函数获取了sal字段中最小的数值。

分组查询

在对数据表中的数据进行统计时，有时需要按照一定的类别作统计。例如，财务在统计每个部门的工资总数时，属于同一个部门的所有员工就是一个分组。在MySQL中，可以使用GROUP BY根据指定的字段结果集进行分组，如果某些记录的指定字段具有相同的价值，那么分组后被合并为一条数据。使用GROUP BY分组查询的语法如下：

1.单独使用GROUP BY 分组

单独使用GROUP BY进行分组时将根据指定的字段合并数据行。例如，我们想要使用SQL语句查询员工表的职位有有哪几种，具体SQL语句及执行结果如下：

mysql> SELECT job FROM emp GROUP BY job;
+--------+
| job    |
+--------+
| 保洁   |
| 销售   |
| 经理   |
+--------+
3 rows in set (0.00 sec)

在上述SELECT语句中，使用GROUP BY根据job字段中的值对数据表中的记录进行分组；从执行结果来看，员工的职位一共有三种。

2.GROUP BY和聚合函数一起使用

如果分组查询时要进行统计汇总，此时需要将GROUP BY和聚合函数一起使用。例如，统计员工表各部门的薪资总和或平均薪资，可以使用GROUP BY和聚合函数AVG()、SUM()进行统计，具体SQL语句及执行结果如下:

mysql> SELECT job,AVG(sal),SUM(sal) FROM emp GROUP BY job;
+--------+-----------+----------+
| job    | AVG(sal)  | SUM(sal) |
+--------+-----------+----------+
| 保洁   | 2500.0000 |     5000 |
| 销售   | 3125.0000 |    12500 |
| 经理   | 7000.0000 |     7000 |
+--------+-----------+----------+
3 rows in set (0.01 sec)

在上述SELECT语句中，使用GROUP BY根据job字段中的值对数据表的记录进行分组，值相同的为一组，并计算出各个职位的总工资和平均工资。

3.GROUP BY和HAVING关键字一起使用

通常情况下GROUP BY和HAVING关键字一起使用，用于对分组后的结果进行条件过滤。例如，假如我们想要使用SQL语句查询员工表中的平均工资小于3000的部门编号及这些部门的平均工资。查询时可以使用GROUP BY和HAVING进行统计，具体SQL语句及执行结果如下：

mysql> SELECT empno,AVG(sal) FROM emp GROUP BY empno HAVING AVG(sal)<3000;
+-------+-----------+
| empno | AVG(sal)  |
+-------+-----------+
|  9770 | 2500.0000 |
|  9885 | 2500.0000 |
|  9900 | 2500.0000 |
+-------+-----------+
3 rows in set (0.00 sec)

在上述SELECT语句中，使用GROUP BY根据empno字段中的值对数据表的记录进行分组，并且使用HAVING筛选平均工资小于3000的数据，最终返回了平均工资小于3000的部门编号及平均工资。

在MySQL中，HAVING 子句用于在 GROUP BY 子句后对聚合结果进行过滤。它通常与聚合函数（如 SUM(), COUNT(), AVG(), MAX(), MIN() 等）一起使用，以筛选满足特定条件的分组。

与 WHERE 子句不同，WHERE 子句在聚合之前对单个记录进行过滤，而 HAVING 子句在聚合之后对分组进行过滤。这一点不要弄错哦。

排序查询

对数据表的数据进行查询时，可能查询出来的数据是无序的，或者其排列顺序不是用户期望的。如果想要对查询结果按指定的方式排序，例如对员工信息按姓名顺序排列等，可以使用ORDER BY对查询结果进行排序。查询语句中使用ORDER BY的基本语法格式如下。

SELECT*|{字段名1，字段名2,···} FROM 表名 ORDER BY 字段名1 [ASC | DESC], 字段名2 [ASC | DESC]......

在上面的语法格式中，ORDER BY后指定的字段名1、字段名2等是对查询结果排序的依据，即按照哪一个字段进行排序。参数ASC表示按照升序进行排序，DESC表示按照降序进行排序。

使用ORDER BY对查询结果进行排序时，如果不指定排序方式，默认按照ASC方式进行排序。例如，技术人员想要使用SQL语句查询员工表中职位为销售的员工信息，查询出的结果根据员工工资升序排列，具体SQL语句及执行结果如下：

mysql> SELECT * FROM emp WHERE job='销售' ORDER BY sal;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9900 | 孙七   | 销售   | 2500 |  200 |
|  9880 | 张三   | 销售   | 3000 |  200 |
|  9775 | 王五   | 销售   | 3500 |  500 |
|  9888 | 郑十   | 销售   | 3500 | NULL |
+-------+--------+--------+------+------+
4 rows in set (0.00 sec)

上述SELECT语句使用ORDER BY对job字段值为销售的所有记录按照工资从低到高进行排序，即sal字段的值按升序排序。因为没有设置怎么排序，所以默认ASC升序排序。

要注意的是如果有字段中的值为NULL，那么NULL会被当做最小值进行排序，下面按照奖金对销售员工进行排序：

mysql> SELECT * FROM emp WHERE job='销售' ORDER BY bon;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9888 | 郑十   | 销售   | 3500 | NULL |
|  9880 | 张三   | 销售   | 3000 |  200 |
|  9900 | 孙七   | 销售   | 2500 |  200 |
|  9775 | 王五   | 销售   | 3500 |  500 |
+-------+--------+--------+------+------+
4 rows in set (0.00 sec)

上述SELECT语句查询职位为销售的员工信息，并且根据员工奖金值进行升序排序。从执行结果可以看出，奖金值为NULL的员工信息排在第一位，说明排序时NULL被当作最小值。

ORDER BY可以对多个字段的值进行排序，并且每个排序字段可以有不同的排序顺序。例如，技术人员想要使用SQL语句查询员工表中工资为2500的员工所有记录，查询出的记录先按职位的升序排序，再按员工编号降序排序，具体SQL语b 句及执行结果如下。

mysql> SELECT * FROM emp WHERE sal=2500 ORDER BY job,empno DESC;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9885 | 李四   | 保洁   | 2500 |  100 |
|  9770 | 吴九   | 保洁   | 2500 | NULL |
|  9900 | 孙七   | 销售   | 2500 |  200 |
+-------+--------+--------+------+------+
3 rows in set (0.00 sec)

在上述SELECT语句中，查询sal字段工资为2500的所有记录，先将这些记录按照job字段的值升序排序，如果job字段的值相同，则按照empno字段的值进行降序排序。如果排序字段的值是字符串类型，则会按字符串中字符的ASCII码值进行排序。

上面的例子job字段后面没有设置排序，因此默认是升序，然后又给empno字段设置了降序，大家不要迷了啊。

mysql> SELECT * FROM emp WHERE sal=2500 ORDER BY bon DESC,empno DESC;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9900 | 孙七   | 销售   | 2500 |  200 |
|  9885 | 李四   | 保洁   | 2500 |  100 |
|  9770 | 吴九   | 保洁   | 2500 | NULL |
+-------+--------+--------+------+------+
3 rows in set (0.00 sec)

这个是两个字段都设置了排序，并且都是降序。

限量查询

查询数据时，SELECT语句可能会返回很多条记录，而用户需要的记录可能只是其中的一条或几条。中的一条或几条。例如，在员工管理系统中，希望每一页默认展示10条员工信息，并且可以通过下拉框更改每页展示的员工信息数。MySQL中提供了一个关键字LIMIT可以指定查询结果从哪一条记录开始以及一共查询多少条信息。在SELECT语句中使用LIMIT的基本语法格式如下。

SELECT 字段名1，字段名2,..… FROM 数据表名 LIMIT [OFFSET，] 记录数；

在上面的语法格式中，LIMIT后面可以跟2个参数。第一个参数OFFSET为可选值，表示偏移量，如果偏移量为0则从查询结果的第一条记录开始，偏移量为1则从查询结果的第二条记录开始，以此类推。如果不指定OFFSET的值，其默认值为0。第二个参数“记录数”表示返回查询记录的条数。

例如，技术人员想要使用SQL语句查询员工表中工资最高的前3名的员工信息，查询时可以使用LIMIT进行限量，具体SQL语句及执行结果如下。

mysql> SELECT * FROM emp ORDER BY sal DESC LIMIT 3;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9990 | 周八   | 经理   | 7000 | 1000 |
|  9775 | 王五   | 销售   | 3500 |  500 |
|  9888 | 郑十   | 销售   | 3500 | NULL |
+-------+--------+--------+------+------+
3 rows in set (0.00 sec)

在上述SELECT语句中，首先使用ORDER BY根据字段sal的值对数据表中的记录进行降序排序，接着使用LIMIT指定返回第1~3条记录。

除了指定查询记录数，LIMIT还可以通过指定OFFSET的值指定查询的偏移量，也就是查询时跳过几条记录。

例如，我们要使用SQL语句查询员工表中工资第二名到第五名的员工信息。具体SQL语句及执行结果如下：

mysql> SELECT * FROM emp ORDER BY sal DESC LIMIT 1,4;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9775 | 王五   | 销售   | 3500 |  500 |
|  9888 | 郑十   | 销售   | 3500 | NULL |
|  9880 | 张三   | 销售   | 3000 |  200 |
|  9770 | 吴九   | 保洁   | 2500 | NULL |
+-------+--------+--------+------+------+
4 rows in set (0.00 sec)

在上述SELECT语句中，先使用ORDER BY根据字段sal的值对数据表中的记录进行降序排序，然后指定返回记录的偏移量为1，查询记录的条数为4.执行结果跳过了排序后的第一条员工信息，返回工资前2~5名的员工信息。

这里和Python的索引有点像，可以联系理解一下，下标就像偏移量，都是从0开始。

下篇文章是MySQL中的内置函数的讲解。点个关注不迷路。