msyql基础 - 聚合函数和分组函数 忘是亡心i 2022-11-05 05:23 215阅读 0赞 聚合函数又称组函数。默认情况下,聚合函数会对当前所在表当做**一个组**进行统计。 (注意:是把一个表当做一个组去处理,与group by类似)。 # **常见聚合函数:** # **以下五个常见分组函数都会默认把null给过滤掉,所以当使用分组函数时,你不需要考虑过滤掉null。** 以user表为例: ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70][] 1、**count ( )** : 求数据表的行数(只针对非空字段!)。 *结果如下:* ![20210304105248980.png][] 补充: ***count(\*) *** 查询结果集的行数 2、**max ( )** : 求某列的最大值 注意:如果有多个最大值,只能返回一个。 *结果如下:* ![20210304105324960.png][] 3、**min( )** : 求某列的最小值 (也是只能返回一个最小值) *结果如下:* ![20210304105511298.png][] 4、**sum( )** : 对数据表求和,也可以加上筛选条件 *结果如下:* ![20210304105605181.png][] 5、**avg( )** : 对表进行平均值操作。 *结果如下:* ![20210304111756820.png][] 6、分组函数合一搭配distinct关键字,来达到去重统计 ***select count(distinct ( )) from 表;*** 7、补充: **count(\*) 和count(字段名) 的区别:** count(\*) 是查询数据的总条数;而count(字段名) 统计的时某个字段,如果某一条的字段为null,会被过滤掉。 # 分组查询 - group by # 以下边**user表**为例: ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70 1][] **1、基本使用:** 语法**(注意书写顺序)**: ***select 查询字段,分组字段 from 表*** ***where 查询之前的筛选条件***(注意:这个是在原始表中筛选) ***group by 分组列表***(这个分组列表可以为单个字段,也可以为多个字段,中间以逗号隔开) ***having 分组后的筛选条件 ****** ***(注意:这个是在基于筛选后的表,也就是group by后边的结果集进行筛选,与where筛选有区别!!!) ***order by 排序列表*** 刚接触数据库,用group by感觉很不习惯,总感觉这东西好难理解,这里就当学习笔记先记录下,以后结合项目用熟练了在回来补充: group by 意思为分组,group by后边跟着的是以哪个字段分组,语法为: 比如,以部门分组: ***select department from user group by department*** 问题来了,它到底能干什么呢? 比如,**现在有一个页面要统计 上边user表中每个部门员工的总人数,**我想这样的需求很常见,你不可能原封不动的把数据都查出来,然后返给前端,这样不太讲码德!最终要返回的数据应该是下边这样的: \[\{ department: '开发部', staff\_count: 4 \}, \{ department: '市场部', staff\_count: 3 \}, \{ department: '行政部', staff\_count: 3 \}\] 那么此时,group by就用上了,我们需要以部门department字段分组: ***select department, count(\*) staff\_count from user group by department*** 执行顺序应该为:**先从user表中把数据查出来 -> 然后再根据department进行分组 -> 分完组之后,然后再计算每一组的总数。** *结果如下:* ![20210304163414558.png][] **规律:** 一般需要使用group by来分组的字段,有相同的特点: (1)字段中的值,有一些重复的,比如:上边的部门。 (2)类型都是同一类型的,这句话等于废话,因为都是同一列的值,这里只是总结下。 (3)这个字段通常会与多条数据有关联,比如**部门**会包含多个**人**。 **2、group\_count( )** 分组完之后,现在又加了一个需求,**需要把每个部门的员工姓名查询出来**,此时我们需要对分组后的结果进行查询,那么可以使用关键字:**group\_concat ( )** ***select department, group\_concat(name) names, count(\*) staff\_count from user group by department*** *结果如下:* ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70 2][] **3、group by + 聚合函数** 一起使用 还是以user表为例: (1)按照部门分组,查询每个部门员工的姓名及薪水、薪水总数 ***select department, group\_concat(name, '-', salary) staff\_salary, sum(salary) sum\_salary from user group by department*** *结果如下:* ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70 3][] (2)查询每个部门的名称和每个部门的人数 ***select department, count(\*) staff\_counts from user group by department*** *结果如下:* ![20210304195852515.png][] (3)查询每个部门的部门名称以及每个部门工资大于8000的人数和大于8000的员工姓名 **补充:使用where会先把工资大于8000的筛选出来,然后在进行分组;而如果使用having的话,它会把数据全部查询出来,然后进行分组,最后在把大于8000的数据给过滤掉,这样可想而知,效率很慢。** *结果如下:* ![20210304200621525.png][] (3.1)如果我们把上边的题目改下,查询每个部门名称以及每个部门的平均工资大于8000的人数 **这个题就不能先用where过滤了,因为统计的是每个部门的平均工资大于8000的,根据平均工资来计算,而where后边不能加分组函数,所以,此题只能用having来统计:** ***select deparment count(\*) stuff\_count from user group by department having avg(salary) > 8000;*** **4、group by + having** **· having的作用:**分组查询后,制定一些条件来输出查询结果(这块有个补充,能在where过滤掉的就先使用where进行过滤,然后在分组,这样效率较高一些)。 **· having的作用**和where一样,但是having只能用于group by。 例: (1)查询工资总和大于22000的部门以及工资和 *结果如下:* ![20210304202528304.png][] **having和where的区别:** **· hanving**是在分组后对数据进行过滤的。 **· where**是在分组前对数据进行过滤的。 **· hanving**在 后边可以使用分组函数(统计函数) **· where**后边不可以使用分组函数 **· where** 是对分组前去约束的条件,如果某行记录没有满足where子句的约束条件,那么这行记录不会参加分组;而hanving是对分组后的数据进行约束。 # 书写顺序: # **select -> from -> where -> grou by -> order by -> limit** # limit的使用: # **limit很简单,是对查询的条数进行限制,语法为:** ***select \* from user limit 0, 5*** 参数一:从哪一行开始查(起始条目)。 参数二:一共查几行(条目数)。 起始条目索引是从0开始的,0代表第一行,如果不写就是从0开始显示,所以,当我们要查询:从a开始,到b结束的数据如下: ***select \* from \* 表 limit a-1, b-(a-1);*** 取前5条数据,*结果如下:* *下边写法等同于:**select \* from user limit 5;*** ![20210304203833162.png][] **\*\* 一个平时开发必用的分页查询 \*\*** 前端通常会传给后台**currentPage - 当前页码**,**pageSize - 当前页显示的条数,下边是如何查询的一个小小技巧,** 假如一个只显示10条数据,**当前从哪一条开始查询的算法:** ***-- 当前为第一页, 查询应该 0行 - 10行数据, 算法为-> (1-1) \* 10 = 0;**最前边的1为当前的页数,后边的10为显示的条数。 * ***-- 当前为第二页, 查询应该 10行 - 20行数据, 算法为-> (2-1) \* 10 = 10;**参数同上。* ***-- 当前为第一页, 查询应该 20行 - 30行数据, 算法为-> (3-1) \* 10 = 20;**参数同上。* sql语句为: ***select \* from user limit (currentPage - 1) \* pageSize*** 当然limit后边不能有表达式,我这只是把这种规律写出来,看起来更加明白一些,currentPage和pageSize都是动态的,通常在后台语言中会先计算出来赋值给一个变量,然后把变量拼接到sql语句中。 **下边是我用node拼的sql示例:** app.use((ctx, next) => { let {currentPage, pageSize} = ctx.query // 获取参数中的页码和数量 query(currentPage, pageSize) next() }) function query(currentPage=1, pageSize=2) { currentPage = (currentPage -1)*pageSize // 分页查询 const pageBreak = `select * from study1 limit ${currentPage},${pageSize}` } [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70]: /images/20221023/e378fbfa34904bc78fc6d0dee2d2af8b.png [20210304105248980.png]: /images/20221023/de17006bc0fc4223856ba4175547495c.png [20210304105324960.png]: /images/20221023/a82a14b264b4439596daee6f297b1ee2.png [20210304105511298.png]: /images/20221023/6aef8339210b4ad892b2540d95a2eaee.png [20210304105605181.png]: /images/20221023/c5d6cbd980764c30be59d8c70c160e71.png [20210304111756820.png]: /images/20221023/1b428f523d4a4a5c802f92207037495f.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70 1]: /images/20221023/5d17ebd670ea489dae53c9a3e493071b.png [20210304163414558.png]: /images/20221023/6bb77dfcb2c6444a99d4f16e17bec123.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70 2]: /images/20221023/9188fbdcd0b44e8db659aa46358206b0.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70 3]: /images/20221023/45773539e01042aaa42b061265a2040d.png [20210304195852515.png]: /images/20221023/2a9772d9fe394392b5456ba328a215c8.png [20210304200621525.png]: /images/20221023/4c201999ad3c4a418b3ebf8206027eba.png [20210304202528304.png]: /images/20221023/737011ffd31d42e5b35db36d1306def5.png [20210304203833162.png]: /images/20221023/4cae13455f8f414e9efa34b07fc2713e.png
还没有评论,来说两句吧...