MySQL之基本查询(二)(update || delete || 聚合函数 || group by)

ops/2024/9/24 4:42:49/

目录

一、表的更新update

二、表的删除delete

三、聚合函数

四、group by 分组查询


一、表的更新update

语法:

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

使用实列:

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

update exam_result set math=80 where name='孙悟空';

原先分数:

更改后分数:

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

mysql> update exam_result set math=60, chinese=70 where name='曹孟德';

 原先分数:

更改后分数:

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

对于这个问题,我们可以先拿到总成绩倒数前三的3位同学的总成绩和数学成绩:

select name,math,chinese+math+english total from exam_result order by total limit 3;

上图显示出来的数据其实也是一张表,我们可以对该表进行数据修改:

update exam_result set math=math+30 order by chinese + math + english limit 3;

 

注:如果没有筛选条件,update将进行整表更新。 

二、表的删除delete

删除数据

语法:

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

 使用实例:

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

mysql> delete from exam_result where name='孙悟空';

删除前的数据:

删除后数据:

~ 删除总分第一名的同学的考试成绩

mysql> delete from exam_result order by chinese+math+english desc limit 1;

原先数据:总分第一名是猪悟能,所以我们需要删除他的成绩。

删除后的数据:

 删除表的所有数据

我们先创建一张用于测试的表:

mysql> CREATE TABLE for_delete (-> id INT PRIMARY KEY AUTO_INCREMENT,-> name VARCHAR(20)-> );

然后插入测试数据:

INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

 

然后删除表的所有数据,并查看:

mysql> delete from for_delete;

我们查看一下创建语句:

mysql> show create table for_delete\G;

 

我们发现,auto_increment是4,那么如果我们再插入一条新的数据:自增 id 在原值上增长

INSERT INTO for_delete (name) VALUES ('D');

 

查看表结构,会有 AUTO_INCREMENT项: 

所以,对于delete清空表数据的方式,其不会清空AUTO_INCREMENT的值。

截断表

语法:

TRUNCATE [TABLE] table_name;

注:这个操作慎用,其特点如下,

1、只能对整表操作,不能像 DELETE 一样可以针对部分数据操作,即只能用于清空表的所有的数据。

2、实际上 TRUNCATE 不对数据操作,所以比 DELETE 更快,但是TRUNCATE在删除数据的时候,并不经过真正的事务,所以无法回滚。

3、会重置 AUTO_INCREMENT 项。

我们先创建一个测试表:

CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

插入一些测试数据:

然后,查看一下该表的创建语句:auto_increment是4。

接着,我们截断整表数据,注意影响行数是 0,所以实际上没有对数据真正操作:

然后,查看一下该表截断后的创建语句:auto_increment已经被清空了。

然后,向表中插入一条新的数据:

再查看一下该表的创建语句:auto_increment是2。

注:delete和truncate都是对表中的数据进行操作。所以数据没了,但是表任然存在。 

三、聚合函数

聚合函数可以对一组值执行计算并返回单一的值。

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

使用实例:

下面的所有操作和结果均来源下表:

~ 统计班级共有多少同学

mysql> select count(*) from exam_result;

~ 统计数学成绩总分

mysql> select sum(math) from exam_result;

~ 统计数学成绩平均分

mysql> select avg(math) from exam_result;

~ 返回英语成绩的最高分

mysql> select max(english) from exam_result;

~ 返回 < 70 分以下的数学成绩的最低分

mysql> select min(math) from exam_result where math<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;

 

所以说,分组统计的本质,就是把一组按照条件拆成了多个组,然后进行各自组内的统计。即分组就是,把一张表按照条件在逻辑上拆成了多个子表,然后分别对各自的子表进行聚合统计。

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

首先,我们分析一下需求,我们需要的数据是平均工资和最低工资,这个可以使用函数avg和min,来实现。限制条件就是,我们需要根据部门和岗位进行分组,可以使用group by。

mysql> select deptno,job,avg(sal),min(sal) from emp group by deptno,job;

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

首先,我们分析一下需求,我们需要根据部门分组后,得到部门的平均工资。

mysql> select deptno,avg(sal) from emp group by deptno;

然后,我们需要根据分组聚合的结果,进行筛选,显示平均工资低于2000的部门和它的平均工资。

select deptno,avg(sal) mysal from emp group by deptno having mysal<2000;

注:其中,having是对聚合统计后的数据,进行条件筛选。

having 和 where 

两者区别:条件筛选的阶段是不同的。

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

having——对分组聚合之后的数据结果进行条件筛选。

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


http://www.ppmy.cn/ops/115120.html

相关文章

word-break和word-wrap

1&#xff0c;word-break:break-all 例如div宽200px&#xff0c;它的内容就会到200px自动换行&#xff0c;如果该行末端有个英文单词很长&#xff08;congratulation等&#xff09;&#xff0c;它会把单词截断&#xff0c;变成该行末端为conra(congratulation的前端部分)&#…

Knife4j 一款基于Swagger的开源文档管理工具

一、简单介绍 1.1 简介 Knife4j 是一款基于Swagger的开源文档管理工具&#xff0c;主要用于生成和管理 API 文档 二、使用步骤&#xff1a; 2.1 添加依赖&#xff1a; <dependency><groupId>com.github.xiaoymin</groupId><artifactId>knife4j-spr…

python和pyqt-tools安装位置

一.python的安装位置 1.查询安装的python的位置 先查询python&#xff0c;然后输入import sys和sys.path 二.python-tools的安装位置 找到python的文件后按下图路径即可查到tools的文件

专业软件测试服务机构介绍:软件确认测试的类型和方法

随着现代科技的迅猛发展&#xff0c;软件开发逐渐成为各类企业发展的核心。然而&#xff0c;软件的质量直接关系到企业的运营效率和用户体验。因此&#xff0c;软件确认测试作为确保软件质量的重要环节&#xff0c;正受到越来越多的关注。 软件确认测试是指在软件开发周期的最…

【数据结构】散列(哈希)表简单介绍

散列表也叫做哈希表&#xff08;Hash table&#xff09;&#xff0c;散列表通过关键码和存储地址建立唯一确定的映射关系&#xff0c;能够快速查找到对应的元素&#xff0c;排序算法中的计数排序就是一种利用哈希进行排序的算法。 一、散列表的概念 散列表&#xff08;Hash ta…

Android使用OpenCV 4.5.0实现扑克牌识别(源码分享)

一、显示效果展示 二、OpenCV 4.5.0 OpenCV 4.5.0是OpenCV&#xff08;Open Source Computer Vision Library&#xff0c;开源计算机视觉库&#xff09;的一个重要更新版本&#xff0c;该版本在多个方面进行了优化和新增了多项功能。 三、ONNX模型 ONNX&#xff08;Open Neu…

Kubernets基础-包管理工具Helm详解

文章目录 什么是Helm?Helm 的基本概念Helm 的工作原理Helm 的主要功能使用 Helm 的步骤 values.yaml和Chart.yamlvalues.yaml 文件示例Chart.yaml 文件示例 什么是Helm? Helm 是 Kubernetes 的一个非常流行的包管理工具&#xff0c;它使得在 Kubernetes 上部署应用程序变得更…

JAVA同城生活新引擎外卖跑腿团购到店服务多合一高效系统小程序源码

&#x1f680;同城生活新风尚&#xff01;一站式高效系统&#xff0c;让日常更便捷&#x1f6cd;️ &#x1f37d;️【开篇&#xff1a;同城生活&#xff0c;一触即发】&#x1f37d;️ 在这个快节奏的时代&#xff0c;同城生活的便利性与效率成为了我们追求的新风尚。想象一下…