汇总函数
| 函数 |
说明 |
| AVG() |
返回某列的平均值，自动忽略NULL值； |
| COUNT() |
返回某列的行数；COUNT(*)包含NULL值所在的行，COUNT(column)计算特定的行，会忽略NULL。 |
| MAX() |
某列的最大值，忽略NULL值；可以用于日期和数值，不建议用于非日期和非数值； |
| MIN() |
某列的最小值，忽略NULL值； |
| SUM() |
返回某列之和，忽略NULL值； |
mysql> SELECT * FROM person;
+----+-------+-----+----------+
| id | name | age | salary |
+----+-------+-----+----------+
| 1 | jack. | 23 | 8000.00 |
| 2 | Mary | 21 | 8000.00 |
| 3 | Ken | 25 | 10000.00 |
| 4 | jack. | 22 | 5600.00 |
+----+-------+-----+----------+
4 rows in set (0.00 sec)
mysql> update person set salary = null where name = 'jack';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> SELECT * FROM person;
+----+-------+-----+----------+
| id | name | age | salary |
+----+-------+-----+----------+
| 1 | jack. | 23 | 8000.00 |
| 2 | Mary | 21 | 8000.00 |
| 3 | Ken | 25 | 10000.00 |
| 4 | jack. | 22 | 5600.00 |
+----+-------+-----+----------+
4 rows in set (0.00 sec)
mysql> SELECT AVG(salary) AS AVG_SALARY FROM person
-> ;
+-------------+
| AVG_SALARY |
+-------------+
| 7900.000000 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(salary) AS max_salary,MIN(salary) AS min_salary FROM person;
+------------+------------+
| max_salary | min_salary |
+------------+------------+
| 10000.00 | 5600.00 |
+------------+------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS count FROM person;
+-------+
| count |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)
mysql> SELECT SUM(salary) FROM person;
+-------------+
| SUM(salary) |
+-------------+
| 31600.00 |
+-------------+
1 row in set (0.00 sec)
网友评论