【笔记五】：聚合函数

目录

 

1，COUNT 计算总量

2，MAX 求最大值

3，MIN 求最小值

4，SUM求和

5，AVG 求平均值

---

1，COUNT 计算总量

select count(*) from <表名>;
select count(1) from <表名>;          【* 和数字的意义完全相同】
select count(<列名>) from <表名>;     【只会统计有效值，null是不会进行统计的】

MariaDB [mydb]> select * from score;
+-------+-----------+------+----------+--------+
| id    | name      | math | language | sports |
+-------+-----------+------+----------+--------+
| 20001 | 小明      |   81 |       86 |     93 |
| 20002 | 小红      |   86 |       86 |     89 |
| 20003 | 小张      |   77 |       83 |     93 |
| 20004 | 露丝      |   88 |       78 |     65 |
| 20005 | 丽丽      |   92 |       94 |     64 |
| 20006 | 李明      |   75 |       78 |     88 |
| 20007 | 张大明    |   54 |       65 |     95 |
+-------+-----------+------+----------+--------+
7 rows in set (0.000 sec)

MariaDB [mydb]> select count(*) from score;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.001 sec)

MariaDB [mydb]> select count(1) from score;
+----------+
| count(1) |
+----------+
|        7 |
+----------+
1 row in set (0.001 sec)

MariaDB [mydb]> select count(name) from score;
+-------------+
| count(name) |
+-------------+
|           7 |
+-------------+
1 row in set (0.001 sec)

2，MAX 求最大值

select max(<列名>) from <表名>;

MariaDB [mydb]> select * from score;
+-------+-----------+------+----------+--------+
| id    | name      | math | language | sports |
+-------+-----------+------+----------+--------+
| 20001 | 小明      |   81 |       86 |     93 |
| 20002 | 小红      |   86 |       86 |     89 |
| 20003 | 小张      |   77 |       83 |     93 |
| 20004 | 露丝      |   88 |       78 |     65 |
| 20005 | 丽丽      |   92 |       94 |     64 |
| 20006 | 李明      |   75 |       78 |     88 |
| 20007 | 张大明    |   54 |       65 |     95 |
+-------+-----------+------+----------+--------+
7 rows in set (0.000 sec)

MariaDB [mydb]> select max(math) from score;
+-----------+
| max(math) |
+-----------+
|        92 |
+-----------+
1 row in set (0.001 sec

3，MIN 求最小值

select min(<列名>) from <表名>;

MariaDB [mydb]> select name,min(math) 最低分 from score;
+--------+-----------+
| name   | 最低分    |
+--------+-----------+
| 小明   |        54 |
+--------+-----------+
1 row in set (0.001 sec)

4，SUM求和

select sum(<列名>) from <表名>;

 

MariaDB [mydb]> select sum(math) from score;
+-----------+
| sum(math) |
+-----------+
|       553 |
+-----------+
1 row in set (0.001 sec)

MariaDB [mydb]> 
MariaDB [mydb]> select name, math+language+sports 总分 from score;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 小明      |    260 |
| 小红      |    261 |
| 小张      |    253 |
| 露丝      |    231 |
| 丽丽      |    250 |
| 李明      |    241 |
| 张大明    |    214 |
+-----------+--------+
7 rows in set (0.001 sec)

5，AVG 求平均值

select avg(<列名>)  from <表名>;

MariaDB [mydb]> select avg(math) 数学平均分, avg(language) 语文平均分, avg(sports) 体育平均分 from score;
+-----------------+-----------------+-----------------+
| 数学平均分      | 语文平均分      | 体育平均分      |
+-----------------+-----------------+-----------------+
|         79.0000 |         81.4286 |         83.8571 |
+-----------------+-----------------+-----------------+
1 row in set (0.001 sec)