对表列的所有行或某些特定的行运行的函数,计算并返回一个值,叫做聚合函数。
返回某列的平均值,该列必须为数值类型。
Students表:
| id | class_id | name | gender | score | | --- | -------- | ---- | ------ | ----- | | 1 | 1 | 小明 | M | 90 | | 2 | 1 | 小红 | F | 95 | | 3 | 1 | 小军 | M | 88 | | 4 | 1 | 小米 | F | 73 | | 5 | 2 | 小白 | F | 81 | | 6 | 2 | 小兵 | M | 55 | | 7 | 2 | 小林 | M | 85 | | 8 | 3 | 小新 | F | 91 | | 9 | 3 | 小王 | M | 89 | | 10 | 3 | 小丽 | F | 88 |
SELECT AVG(score) AS average_score FROM Students; | average_score | | ------------- | | 83.5 |
AVG()也可以用来确定特定列或行的平均值:
SELECT AVG(score) average_score FROM Students WHERE gender = 'M'; | average_score | | ------------- | | 81.4 |
AVG()函数会忽略列值为NULL的行。
返回某列的行数。
使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值:
SELECT COUNT(*) AS total FROM Students; | total | | ----- | | 10 |
使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值:
SELECT COUNT(score) AS total FROM Students; | total | | ----- | | 10 |
返回某列的最大值。
SELECT MAX(score) AS max_score FROM Students; | max_score | | --------- | | 95 |
返回某列的最小值。
SELECT MIN(score) AS min_score FROM Students; | min_score | | --------- | | 55 |
MAX()和MIN()一般应用于数值或日期值,如果用于文本数据时,MAX()返回按该列排序后的最后一行,MIN()返回该列排序后最前面一行。
MAX()和MIN()函数会忽略列值为NULL的行。
返回某列值之和,该列必须为数值类型。
SELECT SUM(score) AS total_score FROM Students; | total_score | | ----------- | | 835 |
SUM()函数忽略列值为NULL的行。
以上所有聚合函数都可用来执行多个列上的计算,也可以组合使用:
SELECT COUNT(score) AS total, SUM(score) AS total_score, MAX(score) AS max_score, MIN(score) AS min_score, AVG(score) AS average_score FROM Students; | total | total_score | max_score | min_score | average_score | | ----- | ----------- | --------- | --------- | ------------- | | 10 | 835 | 95 | 55 | 83.5 |