msyql基础 | 您所在的位置:网站首页 › 分组查询怎么用函数计算的 › msyql基础 |
聚合函数又称组函数。默认情况下,聚合函数会对当前所在表当做一个组进行统计。 (注意:是把一个表当做一个组去处理,与group by类似)。 常见聚合函数:以下五个常见分组函数都会默认把null给过滤掉,所以当使用分组函数时,你不需要考虑过滤掉null。 以user表为例: 1、count ( ) : 求数据表的行数(只针对非空字段!)。 结果如下: 补充: count(*) 查询结果集的行数
2、max ( ) : 求某列的最大值 注意:如果有多个最大值,只能返回一个。 结果如下:
3、min( ) : 求某列的最小值 (也是只能返回一个最小值) 结果如下:
4、sum( ) : 对数据表求和,也可以加上筛选条件 结果如下:
5、avg( ) : 对表进行平均值操作。 结果如下:
6、分组函数合一搭配distinct关键字,来达到去重统计 select count(distinct ( )) from 表; 7、补充: count(*) 和count(字段名) 的区别: count(*) 是查询数据的总条数;而count(字段名) 统计的时某个字段,如果某一条的字段为null,会被过滤掉。 分组查询 - group by 以下边user表为例: 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进行分组 -> 分完组之后,然后再计算每一组的总数。 结果如下: 规律: 一般需要使用group by来分组的字段,有相同的特点: (1)字段中的值,有一些重复的,比如:上边的部门。 (2)类型都是同一类型的,这句话等于废话,因为都是同一列的值,这里只是总结下。 (3)这个字段通常会与多条数据有关联,比如部门会包含多个人。 2、group_count( ) 分组完之后,现在又加了一个需求,需要把每个部门的员工姓名查询出来,此时我们需要对分组后的结果进行查询,那么可以使用关键字:group_concat ( ) select department, group_concat(name) names, count(*) staff_count from user group by department 结果如下: 3、group by + 聚合函数 一起使用 还是以user表为例: (1)按照部门分组,查询每个部门员工的姓名及薪水、薪水总数 select department, group_concat(name, '-', salary) staff_salary, sum(salary) sum_salary from user group by department 结果如下: (2)查询每个部门的名称和每个部门的人数 select department, count(*) staff_counts from user group by department 结果如下: (3)查询每个部门的部门名称以及每个部门工资大于8000的人数和大于8000的员工姓名 补充:使用where会先把工资大于8000的筛选出来,然后在进行分组;而如果使用having的话,它会把数据全部查询出来,然后进行分组,最后在把大于8000的数据给过滤掉,这样可想而知,效率很慢。 结果如下: (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的部门以及工资和 结果如下: 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; ** 一个平时开发必用的分页查询 ** 前端通常会传给后台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}` }
|
CopyRight 2018-2019 实验室设备网 版权所有 |