【MySQL】基本查询（2）

目录

• • Update
  • • 将孙悟空同学的数学成绩变更为 80 分
    • 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
    • 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
    • 将所有同学的语文成绩更新为原来的 2 倍
    • Delete
    • • 删除数据
      • • 删除孙悟空同学的考试成绩
        • 删除倒数第一名同学
        • 删除整张表数据
        • 截断表
        • 插入查询结果
        • • 具体实现去重表的操作：
          • 聚合统计(聚合函数)
          • • 统计班级共有多少学生
            • 统计本次考试的数学成绩分数个数
            • 统计数学成绩的总分
            • 统计数学平均分
            • 统计英语小于60分的人
            • 统计数学平均分
            • 返回英语最高分
            • 返回 > 70分以上的数学最低分
            • group by子句的使用（分组聚合统计）
            • • having VS where 区别理解
              • • 再举例：SMITH员工不参与统计

                  作者：爱写代码的刚子

                  时间：2024.3.27

                  前言：本篇博客将会介绍Update，Delete，插入查询结果，聚合函数以及group by子句的使用

                  Update

                  语法：

                  UPDATE table_name SET column = expr [, column = expr ...]
                      [WHERE ...] [ORDER BY ...] [LIMIT ...]
                  

                  对查询到的结果进行列值更新

                  案例：

                  将孙悟空同学的数学成绩变更为 80 分

                  

                  将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

                  

                  将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

                  

                  

                  

                  将所有同学的语文成绩更新为原来的 2 倍

                  

                  Delete

                  删除数据

                  语法：

                  DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
                  

                  删除孙悟空同学的考试成绩

                  

                  删除倒数第一名同学

                  

                  删除整张表数据

                  注意：删除整表操作要慎用！

                  

                  

                  • 会有 AUTO_INCREMENT=n项

                    

                    所以delete操作不会将AUTO_INCREMENT清空

                    截断表

                    语法：

                    TRUNCATE [TABLE] table_name
                    

                    这个关键字直接将表清空，不走事务，delete走事务

                    TRUNCATE不会将自己的操作记录在日志里！，比delete快

                    注意：这个操作慎用

                    1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作;
                    2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
                    3. 会重置AUTO_INCREMENT项

                    

                    • 表还在只不过内容进行了清空

                      

                      • truncate会清空AUTO_INCREMENT项

                        

                        • AUTO_INCREMENT项重新计数

                          

                          • mysql会有对应的log来记录我们的操作

                            关于持久化方式：

                            1. 记录历史SQL语句（bin log，实现主从同步，默认情况先bin log是被关闭的（配置问题））
                            2. 记录数据本身

                            插入查询结果

                            语法：

                            INSERT INTO table_name [(column [, column ...])] SELECT ...
                            

                            案例：删除表中的重复记录，重复的数据只能有一份

                            

                            • 我们之前有介绍select也有去重的功能：

                              

                              具体实现去重表的操作：

                              • 创建一张结构一样的空表：

                                

                                • 使用insert语句和select语句配合使用：

                                  

                                  • 将已经去重了的新表进行重命名：

                                    

                                    • 这样就得到了一个去重的表：

                                      

                                      【问题】：为什么最后是通过rename方式进行的？

                                      就是单纯的想等一切都就绪了，然后统一放入、更新、生效等。（因为有可能有其他用户在访问该数据库）

                                      聚合统计(聚合函数)

                                      函数	说明
                                      COUNT([DISTINCT] expr)	返回查询到的数据的 数量
                                      SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
                                      AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
                                      MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
                                      MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

                                      COUNT:

                                      案例：

                                      统计班级共有多少学生

                                      

                                      • 另一种写法：

                                        

                                        统计本次考试的数学成绩分数个数

                                        

                                        • 也可以使用res进行重命名

                                          

                                          能否对math去重然后再统计？：

                                          • 错误写法：

                                            

                                            这样写表示对res去重

                                            • 正确写法是先去重再count

                                              

                                              SUM

                                              案例：

                                              统计数学成绩的总分

                                              

                                              统计数学平均分

                                              

                                              统计英语小于60分的人

                                              

                                              AVG

                                              案例：

                                              统计数学平均分

                                              

                                              MIN和MAX

                                              案例：

                                              返回英语最高分

                                              

                                              返回 > 70分以上的数学最低分

                                              

                                              group by子句的使用（分组聚合统计）

                                              分组的目的是为了进行分组(按需求，指定列名，实际是用该列的不同的行数据进行分组的)之后，方便进行聚合统计

                                              分组，不就是把一组按照条件拆成了多个组，进行各自组内的统计（分组不就是把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计）

                                              思想：先分组，再聚合

                                              在select中使用group by 子句可以对指定列进行分组查询

                                              select column1, column2, .. from table group by column;
                                              

                                              案例：

                                              • 准备工作，创建一个雇员信息表(来自oracle 9i的经典测试表)
                                                • EMP员工表
                                                • DEPT部门表
                                                • SALGRADE工资等级表
                                                • 显示总平均工资：

                                                  

                                                  • 如何显示每个部门的平均工资和最高工资

                                                    select deptno,avg(sal),max(sal) from EMP group by deptno;
                                                    

                                                    

                                                    

                                                    分组的条件deptno，组内一定是相同的！可以被聚合压缩

                                                    • 显示每个部门的每种岗位的平均工资和最低工资

                                                      select avg(sal),min(sal),job, deptno from EMP group by deptno, job;
                                                      

                                                      

                                                      • 分析一个错误：为什么ename会报错：

                                                        

                                                        因为ename不在分组条件里面，无法进行分组，无法进行压缩和聚合。

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

                                                          • 统计各个部门的平均工资(先聚合)

                                                            select avg(sal) from EMP group by deptno
                                                            

                                                            • having和group by配合使用，对group by结果进行过滤（再判断）

                                                              select avg(sal) as myavg from EMP group by deptno having myavg<2000; 
                                                              --having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。
                                                              

                                                              

                                                              having是对聚合后的统计数据，条件筛选。

                                                              having VS where 区别理解

                                                              执行顺序，构建对“结果”的理解

                                                              • 之前的mysql语句having换成where会报错

                                                                

                                                                • 但是这样却不报错：

                                                                  

                                                                  因为：即使没被分组，也可以看成是一组数据（但是我们选择禁止这样使用）

                                                                  再举例：SMITH员工不参与统计

                                                                  

                                                                  

                                                                  where：对具体的任意列进行条件筛选

                                                                  having：对分组聚合之后的结果进行条件筛选

                                                                  执行顺序：

                                                                  from->where->group->聚合统计+重命名->having

                                                                  所以where和having的区别在于条件筛选的阶段是不同的！

                                                                  【附】：

                                                                  SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit

                                                                  • 不要单纯的认为，只有在磁盘上表结构导入到mysql，真实存在的表，才叫做表
                                                                  • 中间筛选出来的，包括最终结果，全部都是逻辑上的表！“MySQL一切皆表”，未来只要我们能够处理好单表的CURD，所有的sql场景，我们全部都能用统一的方式进行

                                                                   
                                                                   feb是几月份英文缩写  站长工具seo综合查询分析  网建天地  工程建设概况  广西网站建设工具  体验营销策略