1、COUNT()函数
统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数
MariaDB [vincen]> select COUNT(*) AS f_num #计算所有行数,并改名为f_num名输出
-> from fruits #从fruits表中
-> ; #结束
+-------+
| f_num |
+-------+
| 16 |
+-------+
1 row in set (0.01 sec)
MariaDB [vincen]> select s_id, #查询s_id列
-> COUNT(f_id) #查询f_id列总的行数,以COUNT(f_id)名输出
-> from fruits #从fruits表中
-> GROUP BY s_id #按s_id列排序
-> ; #结束
+------+-------------+
| s_id | COUNT(f_id) |
+------+-------------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
+------+-------------+
7 rows in set (0.00 sec)
2、SUM()函数
求总和的函数,返回指定列值的总和
MariaDB [vincen]> select s_id, #查询s_id列
-> SUM(f_price) AS sum_price #查询f_price列数值的总和并改名为sum_price输出
-> from fruits #从fruits表中
-> GROUP BY s_id #按s_id列排序
-> ; #结束
+------+-----------+
| s_id | sum_price |
+------+-----------+
| 101 | 18.60 |
| 102 | 26.80 |
| 103 | 11.40 |
| 104 | 14.00 |
| 105 | 22.40 |
| 106 | 15.60 |
| 107 | 7.20 |
+------+-----------+
7 rows in set (0.01 sec)
3、AVG()函数
计算返回的行数和每一行数据的和,指定列数据的平均值
MariaDB [vincen]> select s_id, #查询s_id列
-> AVG(f_price) AS avg_price #查询f_price列数值的平均值并改名为avg_price输出
-> from fruits #从fruits表中
-> GROUP BY s_id #按s_id列排序
-> ; #结束
+------+-----------+
| s_id | avg_price |
+------+-----------+
| 101 | 6.200000 |
| 102 | 8.933333 |
| 103 | 5.700000 |
| 104 | 7.000000 |
| 105 | 7.466667 |
| 106 | 15.600000 |
| 107 | 3.600000 |
+------+-----------+
7 rows in set (0.01 sec)
4、MAX()函数
返回指定列的最大值
MariaDB [vincen]> select s_id, #查询s_id列
-> MAX(f_price) AS max_price #查询f_price列中最大的数值并改名为max_price输出
-> from fruits #从fruits表中
-> GROUP BY s_id #按s_id列排序
-> ; #结束
+------+-----------+
| s_id | max_price |
+------+-----------+
| 101 | 10.20 |
| 102 | 11.20 |
| 103 | 9.20 |
| 104 | 7.60 |
| 105 | 11.60 |
| 106 | 15.60 |
| 107 | 3.60 |
+------+-----------+
7 rows in set (0.00 sec)
5、MIN()函数
返回指定列的最小值
MariaDB [vincen]> select s_id, #查询s_id列
-> MIN(f_price) AS min_price #查询f_price列中最小的数值并改名为min_price输出
-> from fruits #从fruits表中
-> GROUP BY s_id; #以s_id列排序
+------+-----------+
| s_id | min_price |
+------+-----------+
| 101 | 3.20 |
| 102 | 5.30 |
| 103 | 2.20 |
| 104 | 6.40 |
| 105 | 2.60 |
| 106 | 15.60 |
| 107 | 3.60 |
+------+-----------+
7 rows in set (0.01 sec)