【MySQL】数据库必考知识点：查询操作全面详解与深度解剖

        前言：本节内容讲述基本查询， 基本查询要分为两篇文章进行讲解。 本篇文章主要讲解的是表内删除数据、查询结果进行插入、聚合统计、分组聚合统计。 如果想要学习对应知识的可以观看哦。

        ps:本篇内容友友们只要会创建表了就可以看起来了哦！！！

目录

删除数据

delete from

truncate

插入查询结果

创建空表

去重

插入新表

更换旧表

重命名

聚合函数

group by分组聚合查询        

根据部门列出最高薪资和平均薪资

根据部门和工作列出最低薪资和平均薪资

显示平均工资低于2000的部门和他的平均工资

having 和 where的区别

---

删除数据

delete from

delete from 表名 （where筛选） （排序）（limit）;

       下面为示例： 

        如果不加where筛选就会将全部的表删除， 所以要使用where语句进行筛选， 删除某一条：

delete from exam_result where id = 6;

        另外， 还有一点需要注意的就是， delete 操作不会影响自增约束的字段。就比如一个表里面有一个id列， 这个列的约束是auto_increment。 当我们插入了3个数据后， 这个id自增就会增长到4， 如果这个时候我们将三个数据删除， 这个自增数不会重置， 还是4。 也就是不会影响自增。

         下面为示例：

create table for_delete( id int primary key auto_increment, name varchar(20) );
insert into for_delete(name) values('张三');
insert into for_delete(name) values('张三');
insert into for_delete(name) values('张三');

然后将整个表删除，再进行插入：

truncate

        想要将auto_increment清空， 就可以使用截断表：

create table for_truncate( id int primary key auto_increment, name varchar(20) );
insert into for_truncate(name) values('张三');
insert into for_truncate(name) values('张三');
insert into for_truncate(name) values('张三');

然后我们截断表：

truncate 表名;

        ps：其实truncate和delete from也有其他的区别， 但是博主没有学习过事务， 对于它们之间的更深度的区别理解不了。 有兴趣的友友们可以自行查阅。

插入查询结果

        插入查询结果我们使用一个案例来进行理解， 就是对一个表进行去重。

将select和insert做组合。 现在进行一个案例：

create table duplicate_table( id int, name varchar(20) );

insert into duplicate_table values(100, 'aaa'), (200, 'bbb'), (100, 'aaa'), (200, 'bbb'), (200, 'bbb'), (300, 'ccc');

然后想要对上图的数据进行去重， 需要五步：

        第一步：创建一个空表， 空表的表结构和我们上面的表的结构一样。 

        第二步：通过select 筛选出去重后的查找结果。

        第三步：将查找结果直接插入到新的空表当中。

        第四步：重命名原表

        第五步：重命名新创建的表。 

创建空表

        首先创建一个空表， 这个空表和原本的表是一样的：

去重

        然后将原表中的去重后的结果筛选出来：

插入新表

        然后将这个结果插入到新的空表当中：

更换旧表

    然后rename旧的表变成old_duplicate_table：

重命名

        最后rename新的去重后的表：

        以上就能对一个表内的数据进行去重。 

        为什么最后是使用rename方式进行的？这是因为就是相等一切都已经就绪了， 然后统一放入，更新， 生效等。 

聚合函数

        mysql当中也是有函数的， 这些函数是可以直接被我们调用的。 我们可以对一组数据进行聚合统计， 而使用的就是聚合函数

下面是几个聚合函数：

•         count ： 返回查询到的数据的数量。
•         sum ： 返回查询到的若干列的总和，
•         avg ： 返回特定数据的平均值。
•         max ： 返回数据的平均值。
•         min ： 返回数据的最小值。 

        下面进行示例：

        如果我们使用count计算*, 那么就是返回一共有多少行数据。 

        统计数学成绩的个数：

        但是有些同学的数学成绩是相同的。 我们想要统计不同的数学的成绩的个数， 也就是对数学成绩进行去重。 所以为了能够得到不一样的数学成绩， 就要使用distinct：

        想要统计班级中， 数学成绩不及格的人：

查看三个成绩的平均分：

select avg(english + chinese + math) 平均分 from exam_result;

查找数学超过50分的数学最少的同学：

group by分组聚合查询        

        分组的目的是为了分组之后， 方便进行聚合统计。我们下面使用员工数据库的示例进行理解， 整个员工数据库可以在网络上面找一下

        我们先来看一下这三张表：

        我们使用对应这个数据库， 然后就能看到下面这三张表： 

        下面这是里面的所有员工， 属性包括员工的姓名、员工的工作、员工的工号、员工的时间、员工的工资、员工的奖金、员工的所属部门：

         下面是所有的部门， 属性包括部分的名称， 部门的地点：

         下面是所有的薪资等级：

​

根据部门列出最高薪资和平均薪资

select max(sal) 最高, avg(sal) 平均, from emp; //这是一张表的聚合统计。select deptno, max(sal) 最高, avg(sal) 平均, from emp group by deptno;//然后分组，再聚合统计。  //分组的时候要明确列名，但实际分组是用该列的不同的行进行分组的。分组的条件比如deptno，在组内一定是相同的---可以被聚合压缩。并且， 分组， 就是把一组按照条件拆成了多个组，进行各自组内的统计， 就是把一个表， 在逻辑上拆成了多个子表，然后对多个子表进行聚合统计。

根据部门和工作列出最低薪资和平均薪资

select deptno, job, avg(sal) 平均, min(sal) 最低 from emp group by deptno, job;

         这里要讨论的是， 如果我们这里select后面跟enamel， 请问可不可以呢？ 

        答案是不可以， 因为对于ename来说， ename不是被分组的数据， 对于分组聚合统计，只有聚合统计的以及分组的可以被查看， 其他的， 都不可以查看。 

显示平均工资低于2000的部门和他的平均工资

        我们就要先统计出每个部门的平均工资（结果先聚合出来）， 再进行判断（对聚合出来的结果进行判断）。（这里要用到一个新的函数——having：对聚合后的数据进行统计）

        先统计所有的部门：

select deptno, avg(sal) 平均工资 from emp group by deptno;

        然后再对上面的聚合结果进行筛选（利用having， 对聚合统计的结果进行判断， 所以having的执行顺序在最后）

select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资 < 2000;

having 和 where的区别

        having和where都能做条件筛选， 但是这两个是完全不同的。

        我们利用下面的这个例子进行理解。 首先， 我们查出员工表里面不是SMITH的人：

        然后将这些数据按照部门， 工作进行分组：

        然后做聚合统计， 统计平均工资：

        然后筛选出平均工资小于2000的那些：

然后我们就开始讲解上面的例子了：

        首先， 这里的where， 是对具体的任意列进行条件筛选。having是对分组聚合之后的结果进行条件筛选。所以， 这里面的一定是from第一执行， where 第二执行， 分组第三执行， 聚合统计第四执行， 筛选统计结果第五执行。

•         所以， where 和 having的第一个区别就是条件筛选的阶段是不同的！
•         第二个区别：不要认为只有磁盘上真实的表结构才是表， 我们筛选条件， 或者执行各种语句之中， 中间筛选出来的， 包括最终结果， 在我看来，全部都是逻辑上的表。“MySQL一切皆表“——未来只要我们能够处理好单表的CURD， 所有的sql场景，我们全部都能利用统一的方式进行！

  ——————以上就是本节全部内容哦， 如果对友友们有帮助的话可以关注博主， 方便学习更多知识哦！！！