赞
踩
聚合函数:他是对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个值。
SELECT AVG(salary),SUM(salary)
FROM employees;
SELECT MAX(salary),MIN(salary)
FROM employees;
日期最大和最小
SELECT MAX(hire_date),MIN(hire_date)
FROM employees;
SELECT COUNT(employee_id),COUNT(salary),COUNT(2*salary),COUNT(1)
FROM employees;
但是要注意null值并不会被计数,所以要计算不为空的段落
SELECT COUNT(commission_pct)
FROM employees;
平均值的三种计算方法
count计算数据记录的方法效率
每个部门员工的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
查询各个job_id的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
查询各个部门的不同工种的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
注意:
这个最后一列是多出的平均值
查询各个部门的平均工资,按照平均工资升序排列。
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;
但是在mysql5.7中如果我们加with rollup是无法排序的,因为平均值是不属于avg_sal这个组的
我这里是8.x版本不报错
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;
部门的最高工资比10000高的部门
SELECT department_id,MAX(salary) avg_sal
FROM employees
WHERE MAX(salary) > 10000
ORDER BY department_id;
这样写会报错
我们换成having
SELECT department_id,MAX(salary) avg_sal
FROM employees
HAVING MAX(salary) > 10000
ORDER BY department_id;
还是报错
这样执行就正常了
所以在开发中只有使用了group by 我们才会使用having
查询部门id为10,20,30,40这几个部门中最高工资比10000高的部门
方式1:
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
方式2:
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);
效率:方法一比方法二效率高
执行顺序:
执行流程:
例如:以上就是今天要讲的内容,本文仅仅简单介绍mysql聚合函数的基本操作和基本原理,里面有很多示例,希望大家多动手,麻烦看到这里的朋友点个声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:【wpsshop博客】
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。