mysql下聚合函数count、avg、max、min

1.count()count()函数统计数据表中的行数，或根据查询结果统计列中包含的行数使用方法：（1）count(*) 计算表中总的行数，不管表中某列有值或是空值（2）count(字段名) 计算指定列下总的行数，计算的时候将忽略空值mysql> select * from customers;+-------+----------+---------------------+-------

Hejing_zhang

2164人浏览 · 2021-11-13 11:52:43

Hejing_zhang · 2021-11-13 11:52:43 发布

1.count()

count()函数统计数据表中的行数，或根据查询结果统计列中包含的行数

使用方法：

（1）count(*) 计算表中总的行数，不管表中某列有值或是空值
（2）count(字段名) 计算指定列下总的行数，计算的时候将忽略空值

mysql> select * from customers;
+-------+----------+---------------------+---------+--------+-----------+-------------------+
| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email           |
+-------+----------+---------------------+---------+--------+-----------+-------------------+
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LMing@163.com     |
| 10002 | Stars    | 333 Fromage Lane    | Dalian  | 116000 | Zhangbo   | Jerry@hotmail.com |
| 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL              |
| 10004 | JOTO     | 829 Riverside Drive | Haikou  | 570000 | YangShan  | sam@hotmail.com   |
+-------+----------+---------------------+---------+--------+-----------+-------------------+

计算customers表中总的行数：

mysql> select count(*) as total from customers;
+-------+
| total |
+-------+
|     4 |
+-------+

查询多少人有电子邮箱

mysql> select count(c_email) as c_mail_num from customers;
+------------+
| c_mail_num |
+------------+
|          3 |
+------------+

#########################################################################

mysql> select * from orderitems;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 |      1 | a1   |       10 |       5.20 |
| 30001 |      2 | b2   |        3 |       7.60 |
| 30001 |      3 | bs1  |        5 |      11.20 |
| 30001 |      4 | bs2  |       15 |       9.20 |
| 30002 |      1 | b3   |        2 |      20.00 |
| 30003 |      1 | c0   |      100 |      10.00 |
| 30004 |      1 | o2   |       50 |       2.50 |
| 30005 |      1 | c0   |        5 |      10.00 |
| 30005 |      2 | b1   |       10 |       8.99 |
| 30005 |      3 | a2   |       10 |       2.20 |
| 30005 |      4 | m1   |        5 |      14.99 |
+-------+--------+------+----------+------------+

使用count函数统计不同订单号中订购的水果种类

mysql> select o_num,count(f_id) as total from orderitems group by o_num;
+-------+-------+
| o_num | total |
+-------+-------+
| 30001 |     4 |
| 30002 |     1 |
| 30003 |     1 |
| 30004 |     1 |
| 30005 |     4 |
+-------+-------+
group by先按关键字进行分组，然后在再计算每个分组中的总记录数

2.sum()

注意：sum在进行计算的时候，将忽略行为null的值

求合函数，返回指定列值的总和

mysql> select * from orderitems;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 |      1 | a1   |       10 |       5.20 |
| 30001 |      2 | b2   |        3 |       7.60 |
| 30001 |      3 | bs1  |        5 |      11.20 |
| 30001 |      4 | bs2  |       15 |       9.20 |
| 30002 |      1 | b3   |        2 |      20.00 |
| 30003 |      1 | c0   |      100 |      10.00 |
| 30004 |      1 | o2   |       50 |       2.50 |
| 30005 |      1 | c0   |        5 |      10.00 |
| 30005 |      2 | b1   |       10 |       8.99 |
| 30005 |      3 | a2   |       10 |       2.20 |
| 30005 |      4 | m1   |        5 |      14.99 |
+-------+--------+------+----------+------------+

查询30005号订单一共购买的水果总量

mysql> select sum(quantity) as total from orderitems where o_num=30005;
+-------+
| total |
+-------+
|    30 |
+-------+

sum与group by连用，计算每个分组的总和

使用sum函数统计不同订单号订购的水果总量

mysql> select o_num,sum(quantity) as total from orderitems group by o_num;
+-------+-------+
| o_num | total |
+-------+-------+
| 30001 |    33 |
| 30002 |     2 |
| 30003 |   100 |
| 30004 |    50 |
| 30005 |    30 |
+-------+-------+

group by按订单号进行分组，sum统计每个分组中订购水果的总量

3.avg()

通过返回的行数和每一行数据的和，求指定列数据平均住

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

查询s_id=103的供应商的水果价格的平均值

mysql> select avg(f_price) from fruits where s_id=103;
+-----------+
| avg_price |
+-----------+
|  5.700000 |
+-----------+

avg与group by连用，计算每个分组的平均值

查询每一个供应商水果价格的平均值

mysql> select s_id,avg(f_price) as fruit_price from fruits group by s_id;
+------+-------------+
| s_id | fruit_price |
+------+-------------+
|  101 |    6.200000 |
|  102 |    8.933333 |
|  103 |    5.700000 |
|  104 |    7.000000 |
|  105 |    7.466667 |
|  106 |   15.700000 |
|  107 |    3.600000 |
+------+-------------+

4.max()

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

查询最贵的水果是啥

mysql> select f_name,max(f_price) from fruits;
+--------+--------------+
| f_name | max(f_price) |
+--------+--------------+
| apple  |        15.70 |
+--------+--------------+

max与group by连用，求每个分组的最大值

查询不同供应商提供的最贵的水果多少钱

mysql> select s_id,f_name,max(f_price) as total from fruits group by s_id;
+------+---------+-------+
| s_id | f_name  | total |
+------+---------+-------+
|  101 | apple   | 10.20 |
|  102 | orange  | 11.20 |
|  103 | apricot |  9.20 |
|  104 | berry   |  7.60 |
|  105 | melon   | 11.60 |
|  106 | mango   | 15.70 |
|  107 | xxxx    |  3.60 |
+------+---------+-------+

max函数还可以对字符类型进行计算

在fruits表中查找f_name最大值

mysql> select max(f_name) from fruits;
+-------------+
| max(f_name) |
+-------------+
| xxxx        |
+-------------+

提示：max函数除了用来找出最大的列值或日期值之外，还可返回任意列中的最大值，包括返回字符类型的最大值。在对字符类型数据进行比较时，按照字符的ASCII码值大小比较，从a~z,a的ASCII码最小，z的最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直等到两个字符不相等或者字符结束为止。例如，'b'与't'进行比较时，'t'为最大值，'bcd'与'bca'进行比较，'bcd'为最大值。

5.min()

查询列中的最小值

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

查询市场上最便宜的水果

mysql> select f_name,min(f_price) from fruits ;
+--------+--------------+
| f_name | min(f_price) |
+--------+--------------+
| apple  |         2.20 |
+--------+--------------+

min与group by连用，计算每个供应商最便宜的水果是啥

mysql> select s_id,f_name,min(f_price) as min_price from fruits group by s_id;
+------+---------+-----------+
| s_id | f_name  | min_price |
+------+---------+-----------+
|  101 | apple   |      3.20 |
|  102 | orange  |      5.30 |
|  103 | apricot |      2.20 |
|  104 | berry   |      6.40 |
|  105 | melon   |      2.60 |
|  106 | mango   |     15.70 |
|  107 | xxxx    |      3.60 |
+------+---------+-----------+

min也能用来查询字符类型，和上边的max一样

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

【计算机视觉】Pixel逐像素分类&Mask掩码分类理解摘要

魔乐社区

计算机视觉（opencv）实战三十二——CascadeClassifier 人脸微笑检测（摄像头）

本文从原理到实现，详细介绍了基于 OpenCV Haar 分类器的人脸与微笑检测：讲解了 Haar 特征和级联检测原理。对代码逐行拆解并解释参数含义。画出完整流程图，帮助理解执行过程。给出了常见问题和优化建议，甚至扩展到深度学习方法。这种方法简单、轻量、实时性好，非常适合入门和小型应用项目。但如果需要更高准确率和更强鲁棒性，建议使用深度学习检测器替代 Haar 分类器。