MySQL分组函数

#分组函数 用作统计使用 
/*可以和distinct搭配使用
1、sum
2、avg
3、max min
4‘count
*/#这几个分组函数都忽略null值
SELECT SUM(salary) FROM employees;
SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;
SELECT SUM(salary),ROUND(AVG(salary),2),MIN(salary),MAX(salary),COUNT(salary) FROM employees;

#参数支持哪些类型
#sum和avg 一般只适用于数值型,忽略null值
SELECT SUM(last_name),AVG(last_name) FROM employees;#不报错，但是无意义
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
#7.80 0.222857 0.222857 0.072897 可得sum和avg都忽略null值

#max min 可处理任意类型，忽略null值
SELECT MAX(last_name),MIN(hiredate) FROM employees;#字符串可以进行排序，所以有最大最小

#count可支持所有类型，但是忽略null值
SELECT COUNT(last_name) FROM employees;
SELECT COUNT(commission_pct) FROM employees;

#去重
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

#count函数 一般使用count(*)
SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees; #统计行数

SELECT COUNT(1) FROM employees; #相当于给原有表加了一列，实质上是统计行数 

#效率
/*
myisam存储引擎下，count(*)效率高
innodb存储引擎下，count(*)和count(1)差不多
*/

#和分组函数一起查询的字段有限制，分组查询的结果是一行
#和分组函数一起查询的字段要求是 group by后的字段
SELECT AVG(salary),employee_id FROM employees;#不会报错但是无意义
 
#测试
#查询公司员工工资的最大值、最小值、平均值、总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees;

#查询员工表中的最大入职时间和最小入职时间相差的天数
SELECT (YEAR(MAX(hiredate))-YEAR(MIN(hiredate)))*365
+(MONTH(MAX(hiredate))-MONTH(MIN(hiredate)))*30
+(DAY(MAX(hiredate))-DAY(MIN(hiredate))) AS DIFFRENCE
FROM employees;
#函数datediff()计算两天之差
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS DIFFRENCE FROM employees;

SELECT DATEDIFF(NOW(),'2000-03-03');

#查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE department_id=90;