msyql基础 - 聚合函数和分组函数

忘是亡心i 2022-11-05 05:23 271阅读 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