摘要:本篇MySQL教程对聚合函数进行了详细阐述,并用实例说明了AVG() 、COUNT() 、MAX() 、MIN() 、SUM() 如何应用,希望本文对大家学习MySQL有所帮助。
数据准备:
mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(20) NOT NULL, -> age SMALLINT UNSIGNED DEFAULT 10, -> sex BOOLEAN -> ); Query OK, 0 rows affected (0.12 sec) mysql> INSERT users VALUES(DEFAULT,'tom',23,1); Query OK, 1 row affected (0.06 sec) mysql> INSERT users VALUES(DEFAULT,'tomi',23,1); Query OK, 1 row affected (0.06 sec) mysql> INSERT users VALUES(DEFAULT,'jerry',26,0); Query OK, 1 row affected (0.05 sec) mysql> INSERT users VALUES(DEFAULT,'jerry',26,NULL); Query OK, 1 row affected (0.05 sec) mysql> INSERT users VALUES(DEFAULT,'marry',DEFAULT,NULL); Query OK, 1 row affected (0.06 sec) mysql> INSERT users VALUES(DEFAULT,'marry',DEFAULT,1); Query OK, 1 row affected (0.11 sec) mysql> INSERT users VALUES(DEFAULT,'tom',34,0); Query OK, 1 row affected (0.05 sec) mysql> SELECT * FROM users; +----+----------+------+------+ | id | username | age | sex | +----+----------+------+------+ | 1 | tom | 23 | 1 | | 2 | tomi | 23 | 1 | | 3 | jerry | 26 | 0 | | 4 | jerry | 26 | NULL | | 5 | marry | 10 | NULL | | 6 | marry | 10 | 1 | | 7 | tom | 34 | 0 | +----+----------+------+------+ 7 rows in set (0.00 sec)
1、聚集函数
聚集函数是运行在行组上,计算和返回单个值的函数。
SQL聚集函数
函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某个列之和
(1)、AVG()函数
可以返回所有列的平均值,也可以返回特定列的平均值。
mysql> SELECT AVG(age) AS avg_age FROM users; #计算所有记录中age字段的均值。 +---------+ | avg_age | +---------+ | 21.7143 | +---------+ 1 row in set (0.00 sec) mysql> SELECT AVG(age) AS avg_age FROM users WHERE sex=1;#计算所有的sex=1的记录中age字段的均值。 +---------+ | avg_age | +---------+ | 18.6667 | +---------+ 1 row in set (0.00 sec)
(2)、COUNT()函数
COUNT(*)对表中行的数目进行计数,不管表列中包含的是空(NULL)还是非空值;
COUNT(column)对特定列中有值的行进行计算,忽略NULL值。
mysql> SELECT COUNT(*) FROM users;#对表中行的数目进行计数 +----------+ | COUNT(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(sex) FROM users;#对sex列中,有值的行进行计算,忽略NULL值。 +------------+ | COUNT(sex) | +------------+ | 5 | +------------+ 1 row in set (0.00 sec)
(3)、MAX()函数
MAX()返回指定列的最大值,要求指定列名,忽略NULL值。
在MySQL中,MAX()函数可以对非数据列使用,在用于文本数据时,如果数据按相应的列排序,MAX()返回最后一行。
mysql> SELECT MAX(age) FROM users; +----------+ | MAX(age) | +----------+ | 34 | +----------+ 1 row in set (0.00 sec) mysql> SELECT MAX(sex) FROM users; +----------+ | MAX(sex) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> SELECT MAX(username) FROM users; +---------------+ | MAX(username) | +---------------+ | tomi | +---------------+ 1 row in set (0.00 sec) mysql> SELECT username FROM users ORDER BY username ASC; #可以看到,tomi排在最后。 +----------+ | username | +----------+ | jerry | | jerry | | marry | | marry | | tom | | tom | | tomi | +----------+ 7 rows in set (0.00 sec)
(4)、MIN()函数
MIN()返回指定列的最小值,要求指定列名,忽略NULL值。
在MySQL中,MIN()函数可以对非数据列使用,在用于文本数据时,如果数据按相应的列排序,MIN()返回最前面的一行。
mysql> SELECT MIN(sex) FROM users; +----------+ | MIN(sex) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> SELECT MIN(age) FROM users; +----------+ | MIN(age) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec)
(5)、SUM()函数
用来返回指定列的和(总计),忽略NULL值的行。
mysql> SELECT SUM(age) FROM users; +----------+ | SUM(age) | +----------+ | 152 | +----------+ 1 row in set (0.00 sec) mysql> SELECT SUM(sex) FROM users; +----------+ | SUM(sex) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql> SELECT SUM(age) AS sum_age FROM users WHERE sex=1;#对sex=1的记录,求年龄之和。 +---------+ | sum_age | +---------+ | 56 | +---------+ 1 row in set (0.00 sec)
SUM()也可以合计计算值。
mysql> SELECT * FROM users WHERE sex=0; +----+----------+------+------+ | id | username | age | sex | +----+----------+------+------+ | 3 | jerry | 26 | 0 | | 7 | tom | 34 | 0 | +----+----------+------+------+ 2 rows in set (0.00 sec) mysql> SELECT SUM(age*id) AS sum_age_id FROM users WHERE sex=0; +------------+ | sum_age_id | +------------+ | 316 | +------------+ 1 row in set (0.00 sec)
2、聚集不同值
DISTINCT关键字:如果使用DISTINCT关键字,则需要去掉重复值。(即:重复的数值只能使用1次)
mysql> SELECT * FROM users WHERE sex=1; +----+----------+------+------+ | id | username | age | sex | +----+----------+------+------+ | 1 | tom | 23 | 1 | | 2 | tomi | 23 | 1 | | 6 | marry | 10 | 1 | +----+----------+------+------+ 3 rows in set (0.00 sec) mysql> SELECT AVG(DISTINCT age) FROM users WHERE sex=1;#对23,10求均值。 +-------------------+ | AVG(DISTINCT age) | +-------------------+ | 16.5000 | +-------------------+ 1 row in set (0.03 sec) mysql> SELECT AVG(age) FROM users WHERE sex=1;#对23,23,10求均值 +----------+ | AVG(age) | +----------+ | 18.6667 | +----------+ 1 row in set (0.00 sec)
3、组合聚集函数
聚集函数可以组合使用
mysql> SELECT COUNT(*) AS num,MIN(age) AS age_min,MAX(age) AS age_max,AVG(age) AS age_avg FROM users;+-----+---------+---------+---------+| num | age_min | age_max | age_avg |+-----+---------+---------+---------+| 7 | 10 | 34 | 21.7143 |+-----+---------+---------+---------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) AS num,MIN(age) AS age_min,MAX(age) AS age_max,AVG(age) AS age_avg FROM users WHERE sex=1;+-----+---------+---------+---------+| num | age_min | age_max | age_avg |+-----+---------+---------+---------+| 3 | 10 | 23 | 18.6667 |+-----+---------+---------+---------+1 row in set (0.00 sec)mysql> SELECT * FROM users WHERE sex=1;+----+----------+------+------+| id | username | age | sex |+----+----------+------+------+| 1 | tom | 23 | 1 || 2 | tomi | 23 | 1 || 6 | marry | 10 | 1 |+----+----------+------+------+3 rows in set (0.00 sec)mysql> SELECT COUNT(*) AS num,MIN(age) AS age_min,MAX(age) AS age_max,AVG(DISTINCT age) AS age_avg FROM users WHERE sex=1;+-----+---------+---------+---------+| num | age_min | age_max | age_avg |+-----+---------+---------+---------+| 3 | 10 | 23 | 16.5000 |+-----+---------+---------+---------+1 row in set (0.00 sec)
本文由职坐标整理并发布,希望对同学们有所帮助。了解更多详情请关注数据库MySQL频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号