mysql聚合函数有哪些,mysql聚合函数一定要分组吗

2023-10-26 10:06:42 墨初知识笔记 69阅读

一、五大常用聚合函数 SUM()求总和只适用于数值类型字段如果是字符串类型不会报错会返回0会自动过滤空值AVG()求平均值只适用于数值类型字段字符串类型不会报错会返回0会自动过滤空值MAX()求最大值适用于数值类型、字符串类型和日期时间类型字段MIN()求最小值适用于数值类型、字符串类型和日期时间类型字段COUNT()用于计算查询结果集中的数据共有多少条 COUNT(*)COUNT(常数)例如COUNT(0)COUNT(1)COUNT(指定字段)此方式只能用于那种不存在NULL的字段如果存在空值统计总数时不计入如果是MyISAM引擎这三种方式的效率相同因为此引擎内部有一个计数器在维护着行数。如果是InnoDB引擎那么第一和第二种效率高于第三种后面会细说

注意MySQL中聚合函数是不能嵌套使用的

我们创建一个表t_decade_book来进行验证

DROP TABLE IF EXISTS t_decade_book;CREATE TABLE t_decade_book  (  book_id int(10) NOT NULL AUTO_INCREMENT COMMENT 书id,  book_name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 书名,  book_count int(10) DEFAULT NULL COMMENT 数量,  detail varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 描述,  PRIMARY KEY (book_id) USING BTREE) ENGINE  InnoDB AUTO_INCREMENT  4 CHARACTER SET  utf8 COLLATE  utf8_general_ci ROW_FORMAT  Dynamic;INSERT INTO t_decade_book VALUES (1, Java从入门到秃头, 20, Java学习);INSERT INTO t_decade_book VALUES (2, 数据库从删库到跑路, 20, 数据库学习);INSERT INTO t_decade_book VALUES (3, 测试从入职到干架, 20, 测试脚本学习);INSERT INTO t_decade_book VALUES (4, 划水越划越爽, NULL, 划水技巧学习);

得到的表数据如下

SELECT AVG(book_count),SUM(book_count),AVG(book_count)*4 FROM t_decade_book;SELECT MAX(book_count),MIN(book_count),MAX(book_name),MIN(book_name) FROM t_decade_book;SELECT COUNT(book_id),COUNT(1),COUNT(*),COUNT(book_count) FROM t_decade_book;SELECT AVG(book_count),SUM(book_count),SUM(book_count)/4,SUM(book_count)/COUNT(book_count) FROM t_decade_book;

执行结果如下

二、GROUP BY

首先我们向之前创建的t_decade_blog表中插入一条数据

INSERT INTO t_decade_blog(id,name,author,create_time,views)VALUES(d3258b79-d543-49bb-9850-16cac7566666,JVM系列,十年,NOW(),8000);

表格中结果如下

然后我们测试一下GROUP BY操作

# 根据单列进行分组SELECT author,AVG(views) FROM t_decade_blog GROUP BY author;# 根据多列进行分组如果分组的条件相同顺序不同不会影响最终结果# 我们可以理解为根据这些条件进行组合只有符合这些条件的才会分到一个组里SELECT id,author,AVG(views) FROM t_decade_blog GROUP BY id,author;SELECT author,id,AVG(views) FROM t_decade_blog GROUP BY author,id;# 当使用GROUP BY关键字时SELECT中涉及到的非聚合函数包含的字段必须出现在GROUP BY后面# 但是GROUP BY关键字后面的字段不一定要出现在SELECT之后# 另外在不使用GROUP BY时聚合函数不能和普通字段放在一起进行查询SELECT author,id,AVG(views) FROM t_decade_blog GROUP BY author;# with rollup作用在聚合函数。如果聚合函数是COUNT(*)则会在统计的记录中再次求COUNT(*)# 如果是AVG(),则会去除分组条件求该字段的AVG()# 使用WITH ROLLUP后不能再使用ORDER BYSELECT author,AVG(views) FROM t_decade_blog GROUP BY author WITH ROLLUP;SELECT book_id,AVG(book_count) FROM t_decade_book GROUP BY book_id WITH ROLLUP;SELECT author,COUNT(views) FROM t_decade_blog GROUP BY author WITH ROLLUP;

执行结果如下

注意GROUP BY的使用顺序

放在FROM、WHERE后面放在ORDER BY、LIMIT前面三、HAVING

1、HAVING 子句可以让我们筛选分组后的各组数据

当我们想使用聚合函数作为数据的过滤条件时就不能搭配WHERE使用了必须使用HAVING来进行替换。比如我们想筛选出哪些部门的最高工资大于10000那么就要先根据部门id进行分组然后再使用HAVING对MAX(salary)进行过滤如果过滤条件中没有聚合函数那就强烈建议使用WHEREHAVING必须声明在GROUP BY后面在日常开发中使用HAVING的前提是我们使用了GROUP BY

2、HAVING和WHERE的对比

从适用范围来说HAVING更广如果过滤条件中没有聚合函数那么WHERE的执行效率要高于HAVING。因为WHERE的执行顺序是排在HAVING前面的它会筛选掉不满足条件的数据这样后面GROUP BY分组以及HAVING要处理的数据量就更小了

# 错误演示SELECT id,MAX(views)FROM t_decade_blogWHERE MAX(views) > 4000GROUP BY id;SELECT id,MAX(views)FROM t_decade_blogGROUP BY idHAVING MAX(views) > 4000;# 如果我们想查出特定博客id中最大浏览量大于4000的书籍# 方式一WHERE搭配HAVING推荐此方式执行效率更高SELECT id,MAX(views)FROM t_decade_blogWHERE id IN (76782763-48d0-4cef-b8e1-1054e181e41d,d3258b79-d543-49bb-9850-16cac7565f57,d3258b79-d543-49bb-9850-16cac7566666)GROUP BY idHAVING MAX(views) > 4000;# 方式二SELECT id,MAX(views)FROM t_decade_blogGROUP BY idHAVING MAX(views) > 4000 AND id IN (76782763-48d0-4cef-b8e1-1054e181e41d,d3258b79-d543-49bb-9850-16cac7565f57,d3258b79-d543-49bb-9850-16cac7566666);

四、SQL底层执行原理 1、SELECT语句的完整结构

SQL92语法结构

SELECT 字段1,字段2,...(可能存在聚合函数)FROM 表1,表2,...WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件GROUP BY 分组字段1,分组字段2...HAVING 包含聚合函数的过滤条件ORDER BY 排序字段1,排序字段2...(ASC / DESC)LIMIT 偏移量,条目数

SQL99语法结构

SELECT 字段1,字段2,...(可能存在聚合函数)FROM 表1 (LEFT / RIGHT) JOIN 表2 ON 多表的连接条件(LEFT / RIGHT) JOIN 表2 ON 多表的连接条件2...WHERE 不包含聚合函数的过滤条件GROUP BY 分组字段1,分组字段2...HAVING 包含聚合函数的过滤条件ORDER BY 排序字段1,排序字段2...(ASC / DESC)LIMIT 偏移量,条目数

2、SQL语句的执行过程

我们就以SQL99语法结构为例进行分析

首先执行FROM至HAVING范围内的语句先根据FROM找出所需要的表这里相当于之前说过的CROSS JOIN—>然后根据ON后面的连接条件去除无法被关联的数据—>判断是否是左/右外连接(LEFT / RIGHT JOIN)—>根据WHERE过滤数据—>根据GROUP BY分组这一步之后针对每组的聚合函数进行过滤才有了意义这就能说得通为什么WHERE中不能使用聚合函数—>根据HAVING进行分组然后执行SELECT执行完第一步会查出所有字段这一步筛选出我们需要哪些字段如果有DISTINCT关键字那么还会进行去重最后执行ORDER BY 和 LIMIT对上一步得到的结果集进行排序然后再进行分页

如有错误欢迎指正

标签：