MySQL聚合函数

AVG()                    平均值

COUNT()             计数

MAX()                   最大值

MIN()                    最小值

SUM()                  求和

先预备一张表，结构如下

mysql> DESC goodstest;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(6)          | NO   | PRI | NULL    | auto_increment |
| goodname | varchar(20)          | NO   |     | NULL    |                |
| price    | smallint(5) unsigned | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

记录如下

mysql> SELECT * FROM goodstest;
+----+----------+-------+
| id | goodname | price |
+----+----------+-------+
|  1 | A        |     1 |
|  2 | B        |     2 |
|  3 | C        |     3 |
|  4 | D        |     4 |
|  5 | E        |     5 |
|  6 | F        |     6 |
|  7 | G        |     7 |
+----+----------+-------+
7 rows in set (0.00 sec)

例1:求平均值

mysql> SELECT AVG(id) FROM goodstest;
+---------+
| AVG(id) |
+---------+
|  4.0000 |
+---------+
1 row in set (0.00 sec)

例2:取出id排名前三的记录

mysql> SELECT * FROM goodstest LIMIT 3;
+----+----------+-------+
| id | goodname | price |
+----+----------+-------+
|  1 | A        |     1 |
|  2 | B        |     2 |
|  3 | C        |     3 |
+----+----------+-------+
3 rows in set (0.00 sec)

算出平均值并按照新的名字输出

mysql> SELECT AVG(price) AS ave_price FROM goodstest ;
+-----------+
| ave_price |
+-----------+
|    4.0000 |
+-----------+
1 row in set (0.00 sec)

例3:四舍五入

mysql> SELECT ROUND(2.4561,2);
+-----------------+
| ROUND(2.4561,2) |
+-----------------+
|            2.46 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(2.4561,3);
+-----------------+
| ROUND(2.4561,3) |
+-----------------+
|           2.456 |
+-----------------+
1 row in set (0.00 sec)

例4:COUNT 计数

mysql> SELECT COUNT(id) AS counts FROM goodstest;
+--------+
| counts |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

例5:求最大值

mysql> SELECT MAX(price) AS counts FROM goodstest;
+--------+
| counts |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

例6:求最小值

mysql> SELECT MIN(price) AS counts FROM goodstest;
+--------+
| counts |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

例7:求总数

mysql> SELECT SUM(price) AS counts FROM goodstest;
+--------+
| counts |
+--------+
|     28 |
+--------+
1 row in set (0.00 sec)