mysql聚合函数有哪些及怎么使用
这篇“MySQL聚合函数有哪些及怎么使用”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“mysql聚合函数有哪些及怎么使用”文章吧。
创新互联是专业的永福网站建设公司,永福接单;提供成都做网站、成都网站建设,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行永福网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!
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聚合函数有哪些及怎么使用”这篇文章的内容,相信大家都有了一定的了解,希望小编分享的内容对大家有帮助,若想了解更多相关的知识内容,请关注创新互联行业资讯频道。
分享名称:mysql聚合函数有哪些及怎么使用
本文URL:http://myzitong.com/article/pddhce.html