2:分组函数
1:常见的分组函数基本用法
# 分组函数 /* 功能:用作统计使用,又称聚合函数或者统计函数或者组函数。 分类:sum求和,avg平均值,max最大值,min最小值,count计算个数 特点: 1:sum,avg一般用来处理数字型,max,min,count可以用来处理任何类型。 2:是否忽略null值, 3:可以和dissinct搭配使用 4:count函数的详细介绍 5:和分组函数一起查询的字段要求是group by后的字段 */ #1简单使用 SELECT SUM(salary) FROM employees; SELECT AVG(salary) FROM employees; SELECT MIN(salary) FROM employees; SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高 FROM employees; SELECT count(salary) FROM employees; #2参数支持类型 SELECT SUM(last_name),AVG(last_name),SUM(hiredate) FROM employees; SELECT MAX(last_name),MIN(last_name) FROM employees; SELECT MAX(hiredate),MIN(hiredate) FROM employees; SELECT COUNT(last_name) FROM employees; #3是否忽略null值 null+任何事都是null,所以这里null肯定没有参加sum的求和运算。avg也是忽略了null在、值的 #经过下面的验证。所有的分组函数都是自动忽略null值的。 SELECT SUM(commission_pct) 和,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees; SELECT COUNT(commission_pct) FROM employees; #4搭配distinct使用,会将重读的去掉 SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; SELECT count(DISTINCT salary),count(salary) FROM employees; #5,count函数的详细介绍,查询所有数据的行数 SELECT COUNT(commission_pct) FROM employees; SELECT COUNT(*) FROM employees; SELECT COUNT(1) FROM employees; #在Myisam引擎下count(*)效率高。 SELECT COUNT('我想你了,宝贝驴驴') FROM employees; #,和分组函数以同查询的字段有限制 SELECT avg(salary),employee_id FROM employees; #测试小题 #1,查询公司员工工资的最大值,最小值,平均值,总和 SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees; #2,查询员工表中最大入职时间,最小入职时间相差的天数 SELECT MAX(hiredate) 最大入职,MIN(hiredate) 最小入职,(MAX(hiredate)-MIN(hiredate))/(60*60*24) 相差天数,DATEDIFF(MAX(hiredate),min(hiredate)) FROM employees; #3,查询部门编号90的员工个数 SELECT count(*) FROM employees WHERE department_id = 90;