#聚合函数

#AVG(【DISTINCT】 expr) 返回expr的平均值

SELECT AVG(basic_salary) FROM t_salary;

#COUNT（【DISTINCT】 expr）返回expr的非NULL值的数目

#统计员工总人数

SELECT COUNT(*) FROM t_employee;#count(*)统计的是记录数

#统计员工表的员工所在部门数

SELECT COUNT(dept_id) FROM t_employee;#统计的是非NULL值

SELECT COUNT(DISTINCT dept_id) FROM t_employee;#统计的是非NULL值，并且去重

#MIN(【DISTINCT】 expr)返回expr的最小值

#查询最低基本工资值

SELECT MIN(basic_salary) FROM t_salary;

#MAX(【DISTINCT】 expr)返回expr的最大值

#查询最高基本工资值

SELECT MAX(basic_salary) FROM t_salary;

#查询最高基本工资与最低基本工资的差值

SELECT MAX(basic_salary)-MIN(basic_salary) FROM t_salary;

#SUM（【DISTINCT】 expr）返回expr的总和

#查询基本工资总和

SELECT SUM(basic_salary) FROM t_salary;

#group by + 聚合函数

#统计每个部门的人数

SELECT dept_id,COUNT(*) FROM t_employee

GROUP BY dept_id;

#统计每个部门的平均基本工资

SELECT emp.dept_id,AVG(s.basic_salary )

FROM t_employee AS emp,t_salary AS s

WHERE emp.eid = s.eid

GROUP BY emp.dept_id;

#统计每个部门的年龄最大者

SELECT dept_id,MIN(birthday) FROM t_employee GROUP BY dept_id;

#统计每个部门基本工资最高者

SELECT emp.dept_id,MAX(s.basic_salary )

FROM t_employee AS emp,t_salary AS s

WHERE emp.eid = s.eid

GROUP BY emp.dept_id;

#统计每个部门基本工资之和

SELECT emp.dept_id,SUM(s.basic_salary )

FROM t_employee AS emp,t_salary AS s

WHERE emp.eid = s.eid

GROUP BY emp.dept_id;