MySQL必知必会 -- 数据汇总

聚集函数（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)