聚合函数在数据库数据的查询分析中,应用十分广泛。本节将分别对各聚合函数的应用进行说明。
- 求和函数——SUM()
求和函数SUM( )用于对数据求和,返回选取结果集中所有值的总和。语法如下。
SELECTSUM(column_name)
FROMtable_name
说明:SUM()函数只能作用于数值型数据,即列column_name中的数据必须是数值型的。
计数函数——COUNT()
COUNT()函数用来计算表中记录的个数或者列中值的个数,计算内容由SELECT语句指定。使用COUNT函数时,必须指定一个列的名称或者使用星号,星号表示计算一个表中的所有记录。两种使用形式如下。
COUNT(*),计算表中行的总数,即使表中行的数据为NULL,也被计入在内。
COUNT(column),计算column列包含的行的数目,如果该列中某行数据为NULL,则该行不计入统计总数。
使用COUNT(*)函数对表中的行数计数
COUNT(*)函数将返回满足SELECT语句的WHERE子句中的搜索条件的函数。
使用COUNT( )函数对一列中的数据计数
COUNT( )函数可用于对一列中的数据值计数。与忽略了所有列的COUNT(*)函数不同,COUNT()函数逐一检查一列(或多列)中的值,并对那些值不是NULL的行计数。
使用COUNT( )函数对多列中的数据计数
COUNT()函数不仅可用于对一列中的数据值计数,也可以对多列中的数据值计数。如果对多列计数,则需要将要计数的多列通过连接符连接后,作为COUNT()函数的参数。
使用COUNT函数对满足某种条件的记录计数
也可以在SELECT语句中添加一些子句约束来指定返回记录的个数。最大/最小值函数—MAX()/MIN()
当需要了解一列中的最大值时,可以使用MAX()函数;同样,当需要了解一列中的最小值时,可以使用MIN()函数。语法如下。
SELECTMAX (column_name) / MIN (column_name)
FROMtable_name
说明:列column_name中的数据可以是数值、字符串或是日期时间数据类型。MAX()/MIN()函数将返回与被传递的列同一数据类型的单一值。
均值函数——AVG()
函数AVG()用于计算一列中数据值的平均值。语法如下。
SELECT AVG (column_name)
FROMtable_name
说明:AVG()函数的执行过程实际上是将一列中的值加起来,再将其和除以非NULL值的数目。所以,与SUM()函数一样,AVG()函数只能作用于数值型数据,即列column_name中的数据必须是数值型的。
聚合分析的重值处理
前面介绍的5种聚合函数,可以作用于所选列中的所有数据(不管列中的数据是否有重置),也可以只对列中的非重值进行处理,即把重复的值只取一次进行聚合分析。当然,对于MAX()/MIN()函数来讲,重值处理意义不大。
可以使用ALL关键字指明对所选列中的所有数据进行处理,使用DISTINCT关键字指明对所选列中的非重值数据进行处理。以AVG()函数为例,语法如下。
SELECT AVG ([ALL/DISTINCT] column_name)
FROMtable_name
说明:[ALL/DISTINCT]在缺省状态下,默认是ALL关键字,即不管是否有重值,处理所有数据。其他聚合函数的用法与此相同。
聚合函数的组合使用
select s_subject 科目,sum(s_score)各科总成绩,max(s_score)单科最高分, min(s_score)单科最低分,
avg (s_score)单科平均分 from student_score wheres_score>60 group by s_subject;--分组查询各科成绩
实例练习代码如下:
use t ;
create table student_score(s_id int primary key ,
s_name varchar(20),s_scoreint);--在数据库t中创建表单student,同时增加s_id,s_age和s_score三列
select * from student_score;--查看 student_score 表单所有数据
--drop table student_score;--删除table表单
insert into student_score (s_id,s_name,s_score) values(1,'jr',68);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(2,'jj',63);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(3,'mm',70);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(4,'mmj',80);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(5,'乞丐',69);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(6,'混混',90);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(7,'美女',75);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(8,'鸟人',70);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(9,'SB',33);--增加初始值数据
insert into student_score (s_id,s_name,s_score) values(10,'傻妞',59);--增加初始值数据
select * from student_score;--查看 student_score 表单所有数据
--使用聚合函数
select count (*) 学生个数 from student_score;--count对行计数学生个数
select count (s_score) 学生个数 fromstudent_score;--count对行计数有分数的学生个数
insert into student_score (s_id,s_name) values(11,'毛毛');--增加初始值数据
select count (*) 学生个数 from student_score;--count对行计数学生个数
select count (s_score) 学生个数 fromstudent_score;--count对行计数有分数的学生个数
select count(distinct s_score) 有分且不重复学生个数 fromstudent_score;--计算有分,并且分数不相同的学生个数
select sum (s_score) 所有学生总分 fromstudent_score;--用聚合函数计算所有学生总分
select avg(s_score) 所有学生平均分 fromstudent_score;--用聚合函数计算所有学生平均分
select max(s_score) 所有学生最高分 fromstudent_score;--用聚合函数计算所有学生最高分
select min(s_score) 所有学生最低分 fromstudent_score;--用聚合函数计算所有学生最低分
select * from student_score;--查看 student_score 表单所有数据
--分组查询
alter table student_score add s_subject varchar(20);--增加科目这一列
update student_score set s_subject ='语文';--把student_score表中所有科目设置为语文
select * from student_score;--查看 student_score 表单所有数据
update student_score set s_subject = '数学' wheres_idin('5','6','7','8');--把student_score表中id为'5','6','7','8'科目设置为数学
update student_score set s_subject = '英语' wheres_idin('9','10','11');--把student_score表中id为'9','10','11'科目设置为英语
select * from student_score;--查看 student_score 表单所有数据
select s_subject 科目 ,sum(s_score)各科总成绩 fromstudent_score group by s_subject;--分组查询各科总成绩
select s_subject 科目 ,sum(s_score)各科总成绩,max(s_score)单科最高分,min(s_score)单科最低分,
avg (s_score)单科平均分 from student_score wheres_score>60 group by s_subject;--分组查询各科成绩
select * from student_score;--查看 student_score 表单所有数据
--having子句
--begin transaction --开启事务
update student_score set s_name ='sb';--把student_score表中所有名字改成jj
update student_score set s_name = 'jr' where s_idin('3','7','11');--把student_score表中id为'5','6','7','8'名字设置为jr
update student_score set s_name = 'mm' where s_idin('2','6','10');--把student_score表中id为'9','10','11'名字设置为mm
update student_score set s_name = 'ff' where s_idin('4','8');--把student_score表中id为'4','8'名字设置为ff
select s_name 姓名 ,sum(s_score)各科总成绩,max(s_score)单科最高分,min(s_score)单科最低分,
avg (s_score)平均分 from student_score group bys_name ;--分组查询各科成绩
select s_name 姓名 ,sum(s_score)各科总成绩,max(s_score)单科最高分,min(s_score)单科最低分,
avg (s_score)平均分 from student_score group bys_name having avg(s_score)>70;--分组查询各科成绩
select * from student_score;--查看 student_score 表单所有数据
--commit; --提交
--rollback; --回滚