mysql聚合函数有哪些及怎么使用

53次阅读
没有评论

共计 4658 个字符,预计需要花费 12 分钟才能阅读完成。

这篇“mysql 聚合函数有哪些及怎么使用”文章的知识点大部分人都不太理解,所以丸趣 TV 小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“mysql 聚合函数有哪些及怎么使用”文章吧。

AVG 和 SUM

可以对数值型数据使用 AVG 和 SUM 函数

代码案例

SELECT AVG(salary), SUM(salary), AVG(salary) * 107 FROM employees;
#  错误案例:#  如下的操作没有意义,因为不是数值类型
SELECT SUM(last_name), AVG(last_name), SUM(hire_date) FROM employees;

MIN 和 MAX 函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数

代码案例

SELECT MAX(salary), MIN(salary) FROM employees;
SELECT MAX(last_name), MIN(last_name), MAX(hire_date), MIN(hire_date) FROM employees;

COUNT 函数

COUNT(*) 返回表中记录总数,适用于任意数据类型

代码案例

#  计算指定字段在查询结构中出现的个数(不包含 NULL 值的)SELECT COUNT(employee_id), COUNT(salary), COUNT(2 * salary), COUNT(1), COUNT(2), COUNT(*) FROM employees;
#  计算表中有多少条记录
#  方式 1:COUNT(*)
#  方式 2:COUNT(1)
#  方式 3:COUNT(具体字段),不一定对
#  注意:计算指定字段出现的个数时,是不计算 NULL 值的
SELECT COUNT(commission_pct) FROM employees;
#  公式:AVG = SUM / COUNT
SELECT AVG(salary), SUM(salary)/COUNT(salary),
AVG(commission_pct), SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;
#  查询公司中平均奖金率
#  错误写法:如果某些人的奖金为 null,则不会计算
SELECT AVG(commission_pct) FROM employees;
#  正确写法
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;
#  如何需要统计表中的记录数,使用 COUNT(*)、COUNT(1)、COUNT(具体字段)  哪个效率更高
#  如果使用的是 MyISAM  存储引擎,则三者效率相同,都是 O(1)
#  如果使用的是 InnoDB  存储引擎,则三者效率:COUNT(*) = COUNT(1)  COUNT(字段)

GROUP BY

可以使用 GROUP BY 子句将表中的数据分成若干组

代码案例

#  查询各个部门的平均工资,最高工资
SELECT department_id, AVG(salary), SUM(salary) FROM employees GROUP BY department_id
#  查询各个 job_id 的平均工资
SELECT job_id, AVG(salary) FROM employees GROUP BY job_id;
#  查询各个 department_id,job_id 的平均工资
#  方式 1:SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id;
#  方式 2:SELECT job_id, department_id, AVG(salary) FROM employees GROUP BY job_id,department_id;
#  错误写法:job_id 是非组函数,但没有写在 group by 中
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id;
#  错误原因:SELECT 中出现的非组函数的字段必须声明在 GROUP BY  中
#  反之,GROUP BY 中声明的字段可以不出现在 SELECT 中
# GROUP BY  声明在 FROM 后面、WHERE 后面,ORDER BY  前面、LIMIT 前面
# MySQL 中 GROUP BY 中使用 WITH ROLLUP,用于统计
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
#  查询各个部门的平均工资,按照平均工资升序排列
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;
#  当使用 ROLLUP 时,不能同时使用 ORDER BY 子句进行结果排序,即 ROLLUP 和 ORDER BY 是互相排斥的
#  错误写法
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

HAVING

简介

1.  行已经被分组
2.  使用了聚合函数
3.  满足 HAVING  子句中条件的分组将被显示
4. HAVING  不能单独使用,必须要跟  GROUP BY  一起使用 

代码案例

#  查询各个部门中最高工资比 10000 高的部门信息
#  错误写法:不能在  WHERE  子句中使用聚合函数
SELECT department_id, MAX(salary)
FROM employees
WHERE MAX(salary)   10000
GROUP BY department_id;
#  如果过滤条件中使用了聚合函数,则必须使用 HAVING 来替换 WHERE。否则会报错
# HAVING  必须声明在  GROUP BY  的后面
#正确的写法:SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)   10000;
#  开发中,我们使用 HAVING 的前提是 SQL 中使用了 GROUP BY,也即是说使用了 having 就必须使用 group by
#  查询部门 id 为 10,20,30,40 这 4 个部门中最高工资比 10000 高的部门信息
#  方式 1:推荐,执行效率高于方式 2
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);
#  当过滤条件中有聚合函数时,则此过滤条件必须声明在 HAVING 中
#  当过滤条件中没有聚合函数时,则此过滤条件声明在 WHERE 中或 HAVING 中都可以。但建议声明在 WHERE 中
 * WHERE  与  HAVING  的对比
 * 1.  从适用范围上来讲,HAVING 的适用范围更广。 * 2.  如果过滤条件中没有聚合函数:这种情况下,WHERE 的执行效率要高于 HAVING
 */

sql 语句书写顺序

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

sql 语句执行顺序

FROM -  WHERE -  GROUP BY -  HAVING -  SELECT  的字段  -  DISTINCT -  ORDER BY -  LIMIT

课后练习

# 1. where 子句不能使用组函数进行过滤
# 2. 查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary) max_sal, MIN(salary) mim_sal, AVG(salary) avg_sal, SUM(salary) sum_sal FROM employees;
# 3. 查询各 job_id 的员工工资的最大值,最小值,平均值,总和
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees GROUP BY job_id;
# 4. 选择具有各个 job_id 的员工人数
SELECT job_id, COUNT(*) FROM employees GROUP BY job_id;
# 5. 查询员工最高工资和最低工资的差距(DIFFERENCE)SELECT MAX(salary) - MIN(salary)  DIFFERENCE  FROM employees;

# 6. 查询各个管理者手下员工的最低工资,其中最低工资不能低于 6000,没有管理者的员工不计算在内 SELECT manager_id, MIN(salary) FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary)  = 6000; # 7. 查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序   SELECT d.department_name, d.location_id, COUNT(employee_id), AVG(salary)  FROM departments d LEFT JOIN employees e ON d.`department_id` = e.`department_id` GROUP BY department_name, location_id # 8. 查询每个工种、每个部门的部门名、工种名和最低工资   SELECT d.department_name, e.job_id, MIN(salary) FROM departments d LEFT JOIN employees e ON d.`department_id` = e.`department_id` GROUP BY department_name, job_id

以上就是关于“mysql 聚合函数有哪些及怎么使用”这篇文章的内容,相信大家都有了一定的了解,希望丸趣 TV 小编分享的内容对大家有帮助,若想了解更多相关的知识内容,请关注丸趣 TV 行业资讯频道。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-13发表,共计4658字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)