分类
COUNT()
MAX()
MIN()
SUM()
AVG()
示例
SELECT COUNT(*) <br>FROM heros <br>WHERE hp_max > 6000;<br><br>生命值大于6000且有次要定位的英雄数量<br>SELECT COUNT(role_assist) <br>FROM heros <br>WHERE hp_max > 6000;<br><br>SELECT MAX(hp_max)<br>FROM heros<br>WHERE role_main = '射手' or role_assist = ‘射手’;<br><br>针对不同数据进行函数统计<br>SELECT COUNT(DISTINCT hp_max)<br>FROM heros;<br><br>SELECT ROUNT(AVG(DISTINCT hp_max), 2) <br>FROM heros;
如何分组，并进行聚集统计
使用GROUP BY进行分组
示例
按照英雄的主要定位进行分组，并统计每组英雄的数量<br>SELECT COUNT(*), role_main<br>FROM heros<br>GROUP BY role_main;<br><br>按照英雄的次要定位进行分组，并统计每组英雄的数量<br>SELECT COUNT(*), role_assist<br>FROM heros<br>GROUP BY role_assist;<br><br>SELECT COUNT(*) as num, role_main, role_assist<br>FROM heros<br>GROUP BY role_main, role_assist<br>ORDER BY num DESC
HAVING vs WHERE
示例
先对hp_max大于6000的英雄进行过滤，然后和上例一样操作<br>SELECT COUNT(*) as num, role_main, role_assist<br>FROM heros<br>WHERR hp_max > 6000<br>GROUP BY role_main, role_assist<br>HAVING num > 5<br>ORDER BY num DESC
按照英雄的主要定位，次要定位进行分组，并且筛选分组中英雄数量大于5的组<br>最后按照分组中的英雄数量从高到低进行排序<br>SELECT COUNT(*) as num, role_main, role_assist<br>FROM heros<br>GROUP BY role_main, role_assist<br>HAVING num > 5<br>ORDER BY num DESC
练习
筛选最大生命值大于6000的英雄，按照主要定位进行分组<br>选择分组英雄数量大于5的分组，按照分组英雄从高到低进行排序<br>并显示每个分组的英雄数量，主要定位和平均最大生命值<br><br>SELECT COUNT(*) an num, role_main, AVG(hp_max)<br>FROM heros<br>WHERE hp_max > 6000<br>GROUP BY role_main<br>HAVING num > 5<br>ORDER BY num DESC<br><br>筛选最大生命值与最大法力值之和大于7000的英雄<br>按照攻击范围来进行分组，显示分组的英雄数量，<br>以及分组英雄的最大生命值与法力值之和的平均值，最大值和最小值，<br>并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位<br><br>SELECT COUNT(*) as num, ROUND(AVG(hp_max, mp_max),2), ROUND(MAX(hp_max+mp_max),2), ROUND(MIN(hp_max, mp_max),2)<br>FROM heros<br>WHERE hp_max+mp_max > 7000<br>GROUP BY attck_range<br>ORDER BY num DESC
