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}`
}