一、聚合函数
1.count——返回找到的记录数;
2.min——返回一个数字列或计算列的最小值;
3.max——返回一个数字列或计算列的最大值;
4.sum——返回一个数字列或计算列的综合;
5.avg——对一个数字列或计算列求平均值;
例1:练习count函数
解:查询当前登录用户下的student表全部信息:
用count查询记录数:
由此可以看出,count()括号既可以传*也可以传列名,如果传入列名就是计算这一列非空数据的个数,比如这时在student表中插入一个新数据:
这时候再用count(*)和count(sname)查询:
因为sname列最后一个数据为null,所以conut(sname)比count(*)少1。
例2:练习min和max函数
解:
例3:练习sum函数
解:
发现报错,因为sum只能用于数字类型数据,如下则不报错:
例4:练习avg函数
解:
同样avg只能用于数字类型的数据,如下则不报错:
【注意】avg只用于非空数据求平均,比如有11个数据,前10个有值,最后一个是null,那么avg是将前10个相加之后除以10,而不是除以11。
二、GROUP BY
1.应用场景:
当一个SQL语句中有某表的一列+一个聚合函数,那么就需要用group by,否则报错,如下:
2.Group by子句定义(或作用):Group by主要可以用于将行分组为具有相同列值的多个部分。
如:SQL>select job from emp group by job;
说白了,group by就是将值相同的数据分在一组,如现在的student表:
那么现在用group by:
例5:练习group by函数。
解:
上图这条,就是先将student表按照ssex分组,男的一组,女的一组,不男不女一组,然后再调用聚合函数count计算每组个数。
【注意】分组group by一般与聚合函数连用,即先分组再聚合(如先分组再求和、先分组再求平均等等)
3.group by如果只是对行进行单独的分组使用,那么使用意义不大。在复杂的SQL查询中Group by子句大多数时候与聚合函数一起出现。
如:查询各个职位的平均工资是多少?
SQL>select job, avg(sal) from emp group by job;
上述SQL语句执行思路:
按照job列相同值为一组的原则,使用group by子句将表中数据分组,使用avg函数对每组数据分别求平均。
【group by总结】
如果查询中包含一个聚合函数(如下面的sum函数),而所选择的列(如下面的ssex)并不在聚合函数(如下面的sum函数)中,那么这些列(如下面的ssex)就必须在group by子句中;否则就会报错:ORA-00937:不是单租分组函数。
如:SQL>select ssex, sum(sage) from student group by ssex;简单说,就是ssex没被聚合函数sum修饰,那么必须放在group by后面,否则必报错。
例6:在当前登录用户下的student表中查询男生和女生的平均年龄。
解:
【总结】
SQL>select★,聚合函数(△) from A group by★;
其中★填写的很简单,根据谁分组就写谁,比如根据性别分组,这★就是ssex组。执行的时候是先根据★分组,然后再在每个组里面计算聚合函数。
例7:在当前登录用户下的student表中,计算李姓学生的平均年龄。
解:
注意where的使用,更注意like的使用。
那么如果希望查询结果显示“李”怎么办?如下:
substr(sname,1,1)是从sname列每个数据的第1个位置开始取子串,只取1个长度的子串,然后去匹配where sname like ‘李%’条件,根据substr(sname,1,1)分组,用聚合函数avg计算平均年龄。