MySQL之聚合函数

浏览数：21 / 时间：2015年06月12日

1.AVG()

用法：求平均值

举例：

mysql> select * from t1;
+----+------+----------+---------+-------+-------+
| id | name | password | test001 | test3 | test4 |
+----+------+----------+---------+-------+-------+
|  2 | 1    | 1        | 1       | 1     | 1     |
|  3 | 3    | 4        | 5       | 6     | 7     |
|  4 | 3    | 4        | 5       | 6     | 7     |
|  5 | 3    | 4        | 5       | 6     | 7     |
|  6 | 3    | 4        | 5       | 6     | 7     |
|  7 | 3    | 4        | 5       | 6     | 7     |
|  8 | 3    | 4        | 5       | 6     | 7     |
|  9 | 3    | 4        | 5       | 6     | 7     |
| 10 | 3    | 4        | 5       | 6     | 7     |
+----+------+----------+---------+-------+-------+
9 rows in set (0.00 sec)

mysql> select AVG(id) from t1;
+---------+
| AVG(id) |
+---------+
|  6.0000 |
+---------+
1 row in set (0.00 sec)

2.COUNT()

用法：记录的条数

举例：

mysql> select COUNT(id) from t1;
+-----------+
| COUNT(id) |
+-----------+
|         9 |
+-----------+
1 row in set (0.00 sec)

3.MAX()

用法：返回最大值

举例：

mysql> select MAX(id) from t1;
+---------+
| MAX(id) |
+---------+
|      10 |
+---------+
1 row in set (0.01 sec)

4.MIN()

用法：返回最小值

举例：

mysql> select MIN(id) from t1;
+---------+
| MIN(id) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

5.SUM()

用法：求和

举例：

mysql> select SUM(id) from t1;
+---------+
| SUM(id) |
+---------+
|      54 |
+---------+
1 row in set (0.00 sec)

其他：MD5(),PASSWORD()

mysql> select MD5(‘MYSQL‘);
+----------------------------------+
| MD5(‘MYSQL‘)                     |
+----------------------------------+
| 14498b83dd1667a0c78f4fdaf5afbf4b |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select PASSWORD(‘MYSQL‘);
+-------------------------------------------+
| PASSWORD(‘MYSQL‘)                         |
+-------------------------------------------+
| *A97AF8E14502C1B988202FB7B96CF565B6523F9D |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select ENCYPT(‘MYSQL‘);
ERROR 1305 (42000): FUNCTION test.ENCYPT does not exist
mysql> select ENCRYPT(‘MYSQL‘);
+------------------+
| ENCRYPT(‘MYSQL‘) |
+------------------+
| NULL             |
+------------------+
1 row in set (0.03 sec)