本文共 5266 字，大约阅读时间需要 17 分钟。

聚集函数（aggregate function）

运行在行组上，计算和返回单个值的函数。

AVG()函数

• AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。
• AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

MariaDB [test]> select avg(age) from linux;+-------------------+| avg(age)          |+-------------------+| 18.11111111111111 |+-------------------+1 row in set (0.00 sec)MariaDB [test]> select avg(age),avg(price) from linux;+-------------------+-------------------+| avg(age)          | avg(price)        |	/求多个列的时候需要加上多个AVG（）+-------------------+-------------------+| 18.11111111111111 | 89.33333333333333 |+-------------------+-------------------+1 row in set (0.00 sec)MariaDB [test]> select avg(age) from linux where sex='boy';+----------+| avg(age) |		/相比上一条增加了筛选条件，值求男生的平均年龄+----------+|     13.6 |+----------+1 row in set (0.02 sec)MariaDB [test]> select avg(age) from linux where sex='girl';+----------+| avg(age) |+----------+|    23.75 |+----------+1 row in set (0.00 sec)

TIPS： AVG()函数忽略列值为NULL的行。

COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式:

• 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

MariaDB [test]> select count(*) from linux;+----------+| count(*) |	/查看linux表的用户数，一共有9行+----------+		|        9 |+----------+1 row in set (0.00 sec)MariaDB [test]> select user from linux;+--------+| user   |+--------+| user2  || user3  || user4  || user5  || a      || 1      || (cay)  || (cays) || user1  |+--------+

• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

MariaDB [test]> select * from linux;+--------+----------+------+------+-------+------------+| user   | passwd   | sex  | age  | price | math_price |+--------+----------+------+------+-------+------------+| user2  | 222      | girl | 23   | 155   | 36         || user3  | 333      | boy  | 12   | 78    | 66         || user4  | 444      | boy  | 22   | NULL  | NULL       || user5  | 555      | boy  | 30   | NULL  | 43         || a      | passa    | boy  | 22   | NULL  | NULL       || 1      | pass1    | girl | 30   | NULL  | NULL       || (cay)  | passcay  | girl | 21   | NULL  | NULL       || (cays) | passcays | girl | 21   | NULL  | NULL       || user1  | 111      | boy  | -18  | 35    | 88         |+--------+----------+------+------+-------+------------+9 rows in set (0.00 sec)MariaDB [test]> select count(math_price) from linux;+-------------------+| count(math_price) |+-------------------+|                 4 |		/只对具有math_price的用户计数+-------------------+1 row in set (0.00 sec)MariaDB [test]> select count(price) from linux;+--------------+| count(price) |+--------------+|            3 |		    /只对具有price的用户计数+--------------+1 row in set (0.00 sec)

max,min,sum函数

MAX()函数忽略列值为NULL的行。

MariaDB [test]> select max(price) from linux ;+------------+| max(price) |+------------+| 78         |+------------+1 row in set (0.00 sec)MariaDB [test]> select max(user) from linux ;+-----------+| max(user) |+-----------+| user5     |	/非数值数据使用MAX（）时，会按照一定顺序排列返回文本列中最大值+-----------+1 row in set (0.00 sec)MariaDB [test]> select max(passwd) from linux ;+-------------+| max(passwd) |+-------------+| passcays    |+-------------+1 row in set (0.00 sec)

MIN()的功能正好与MAX()功能相反。

MariaDB [test]> select user from linux order by user;+-------+| user  |+-------+| 1     || a     || user1 || user2 || user3 || user4 || user5 |+-------+7 rows in set (0.00 sec)MariaDB [test]> select min(user) from  linux ;+-----------+| min(user) |+-----------+| 1         |+-----------+1 row in set (0.01 sec)

sum（） 函数，SUM()函数忽略列值为NULL的行。

MariaDB [test]> select sum(price) from linux;+------------+| sum(price) |+------------+|        268 |+------------+1 row in set (0.00 sec)MariaDB [test]> select sum(age),sum(price) from linux;+----------+------------+| sum(age) | sum(price) |+----------+------------+|      121 |        268 |+----------+------------+1 row in set (0.00 sec)

聚集不同值DISTINCT的使用

MariaDB [test]> insert into linux value('user6','666','girl','16','35','43');Query OK, 1 row affected (0.00 sec)MariaDB [test]> select * from linux;+-------+--------+------+------+-------+------------+| user  | passwd | sex  | age  | price | math_price |+-------+--------+------+------+-------+------------+| user2 | 222    | girl | 23   | 155   | 36         || user3 | 333    | boy  | 12   | 78    | 66         || user4 | 444    | boy  | 22   | NULL  | NULL       || user5 | 555    | boy  | 30   | NULL  | 43         || a     | passa  | boy  | 22   | NULL  | NULL       || 1     | pass1  | girl | 30   | NULL  | NULL       || user1 | 111    | boy  | -18  | 35    | 88         || user6 | 666    | girl | 16   | 35    | 43         |+-------+--------+------+------+-------+------------+8 rows in set (0.00 sec)MariaDB [test]> select avg(price) from linux;+------------+| avg(price) |+------------+|      75.75 |+------------+1 row in set (0.00 sec)MariaDB [test]> select avg(distinct price) from linux;+---------------------+| avg(distinct price) |+---------------------+|   89.33333333333333 |	/加上distinct参数后就去重了35的那个值，平均值就提高了。+---------------------+1 row in set (0.00 sec)

组合聚合函数

MariaDB [test]> select count(*),avg(price),max(math_price) from linux;+----------+------------+-----------------+| count(*) | avg(price) | max(math_price) |+----------+------------+-----------------+|        8 |      75.75 | 88              |+----------+------------+-----------------+1 row in set (0.00 sec)

转载地址：http://zyxli.baihongyu.com/