当前位置： 首页 > news >正文

MySQL表的增删查改(下)(7)

news 2025/7/12 9:54:01

文章目录

  • 前言
  • 一、更新
  • 二、删除
  • 三、插入查询结果
  • 四、聚合函数
  • 五、分组查询
  • 总结

前言

  来喽，继续加油！！

一、更新

语法：

UPDATE table_name SET column = expr [, column = expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]
  • set 后面跟的是要重新设定的值，可以是多列。
  • 一般在 update 的时候必须采用对应 where子句 进行条件筛选，如果没有的话会把这个表中指定的列全部都更新，这是不合理的。

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

  1. 将孙悟空同学的数学成绩变更为 80 分
update exam_result set math=80 where name='孙悟空';

在这里插入图片描述

  1. 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
update exam_result set math=60, chinese=70 where name='曹孟德';

在这里插入图片描述

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

  不支持 math += 30 这种语法。

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

在这里插入图片描述

  1. 将所有同学的语文成绩更新为原来的 2 倍
update exam_result set chinese = chinese * 2;

在这里插入图片描述

二、删除

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
  • 一般都是拿着条件删除 where
  • 不加条件就是把整表的内容删除了，不过表结构还在。
  • 删表结构drop

  比如说我们现在要删除 孙悟空 同学的成绩

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

在这里插入图片描述

-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

  好的，我们现在删除这个表结构，但是查看创建表结构这个语句还在，并且自增长也还在。

在这里插入图片描述

三、插入查询结果

  现在我们来个复杂的，把我们前面学得稍微小小综合一下

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

  我们要插就插，要删就删，要改就改，要查就查，**实际我们也可以将 select 和 insert 组合。**可以把数据从其他表里面筛选出来，然后插入到另一个表里面。

  现在我们来玩个小游戏，就是删除表中的重复记录，重复的数据只能有一份，你会怎么做？

我给出的步骤如下：

  • 创建一个和原表一样结构的空表 no_duplicate_table ，
  • 从原始表中把去重之后的结果筛选出来插入到 no_duplicate_table 这个表不就是不重复的吗，
  • 然后对 duplicate_table 重命名，no_duplicate_table 改名字为 duplicate_table 。最终不就是完成了duplicate_table去重了吗。
create table no_duplicate_table like duplicate_table;
  • 创建一个完全一样的表，使用like即可
  • 全列插入就不用指定列了
insert into no_duplicate_table select distinct * from duplicate_table;
  • 现在有两个表，一个duplicate_table，一个no_duplicate_table。然后对这两个表做一下重命名
rename table duplicate_table to old_duplicata_table;
rename table no_duplicate_table to duplicate_table;

  这样我们就完成了去重！

四、聚合函数

函数说明
COUNT([DISTINCT] expr)返回查询到的数据的数量
SUM([DISTINCT] expr)返回查询到的数据的总和
AVG([DISTINCT] expr)返回查询到的数据的平均值
MAX([DISTINCT] expr)返回查询到的数据的最大值
MIN([DISTINCT] expr)返回查询到的数据的最小值
  • 这里是为 select分组 查询做准备的，聚合函数是以查出来的记录为单位帮我们进行数据聚合统计的。这种聚合统计方式通常是产出一个期望的结果，如个数、和、平均值、最大值、最小值。
  • mysql 中其实也是有函数的，这个函数可以被直接调用，我们可以在 mysql 直接使用聚合函数直接对一组结果进行聚合统计。
  • 聚合函数（）里面可以是全列，可以是指定列。

  统计班级共有多少同学

select count(*) from exam_result;

  统计班级去重后数学成绩有多少

select count(distinct math) from exam_result;

  统计数学成绩总分

select sum(math) from exam_result;

五、分组查询

  分组查询的SQL如下：

SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];
  • SQL中大写的表示关键字， [ ] 中代表的是可选项。
  • 查询SQL中各语句的执行顺序为：where、group by、select、order by、limit。
  • group by后面的列名，表示按照指定列进行分组查询。

我们来个示例讲解吧！

  分组查询测试表 —— 雇员信息表

  雇员信息表中包含三张表，分别是员工表（emp）、部门表（dept）和工资等级表（salgrade）。

员工表（emp）中包含如下字段：

  • 雇员编号（empno）。
  • 雇员姓名（ename）。
  • 雇员职位（job）。
  • 雇员领导编号（mgr）。
  • 雇佣时间（hiredate）。
  • 工资月薪（sal）。
  • 奖金（comm）。
  • 部门编号（deptno）。

部门表（dept）中包含如下字段：

  • 部门编号（deptno）。
  • 部门名称（dname）。
  • 部门所在地点（loc）。

工资等级表（salgrade）中包含如下字段：

  • 等级（grade）。
  • 此等级最低工资（losal）。
  • 此等级最高工资（hisal）。

SQL语句如下：

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;USE `scott`;DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (`grade` int(11) DEFAULT NULL COMMENT '等级',`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

  上述SQL中创建了一个名为 scott 的数据库，在该数据库中分别创建了部门表（dept）、员工表（emp）和工资等级表（salgrade），并分别向三张表中插入了一些数据用于查询。

  现在我们进入 scott 这个数据库，可以看到雇员信息表中的三张表：

在这里插入图片描述
  其中部门表（dept）的表结构和表中的内容如下：
在这里插入图片描述
  员工表（emp）的表结构和表中的内容如下：
在这里插入图片描述
  工资等级表（salgrade）的表结构和表中的内容如下：
在这里插入图片描述

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

  在 group by子句 中指明依次按照部门号和岗位进行分组，在 select语句 中使用 avg函数 和 min函数 ，分别查询每个部门的每种岗位的平均工资和最低工资。如下：

在这里插入图片描述
  说明一下： 上述SQL会先将表中的数据按照部门号进行分组，然后各自在组内做聚合查询得到每个组的平均工资和最高工资。

HAVING条件

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];

它与 where 的区别在于：

  • where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
  • where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
  • where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名。

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

在这里插入图片描述

总结

  增删查改结束！！接下来迎来的是MySQL的内置函数！！

http://www.lryc.cn/news/585288.html

相关文章：

  • 2025 年第十五届 APMCM 亚太地区大学生数学建模竞赛-B题 疾病的预测与大数据分析
  • 藏不住了，全是硬货！极空间快照，夸克网盘挂载，HDMI桌面输出全部安排！
  • 数据结构 之 【链式二叉树】(C语言实现二叉树的前序中序后序层序遍历，节点个数、树的高度、第K层的节点个数、查找、完全二叉树的判别、销毁创建二叉树)
  • 北京-4年功能测试2年空窗-报培训班学测开-第四十八天
  • 奇哥面试记：SpringBoot整合RabbitMQ与高级特性，一不小心吊打面试官
  • Ant ASpin自定义 indicator 报错
  • map数据结构在Golang中是无序的，并且键值对的查找效率较高的原因
  • 一些有意思的Python语法特性
  • pytorch的介绍以及张量的创建
  • 企业培训笔记：Vue3前端框架配置
  • mac电脑的usr/libexec目录是干什么的？
  • 怎么处理多源异构数据？搞不清楚就别谈数据融合！
  • Linux的基础I/O
  • PDF 转图助手 PDF2JPG 绿色版：免安装直接用，急处理文件的救急小天使
  • Genus：设计信息结构以及导航方式（路径种类）
  • 牛客 —— JZ22 链表中倒数最后k个结点
  • cesium添加原生MVT矢量瓦片方案
  • 云暴露面分析完整指南
  • 香港站群服务器8C/4C/2C/1C有什么区别
  • Elasticsearch混合搜索深度解析（上）：问题发现与源码探索
  • C++11中的std::minmax与std::minmax_element：原理解析与实战
  • 12. 说一下 https 的加密过程
  • 笔记 | 理解C/汇编中的数组元素访问
  • 飞算JavaAI：给Java开发装上“智能引擎”的超级助手
  • UNet改进（21）：门控注意力机制在UNet中的应用与优化
  • 前端高频面试题深度解析（JavaScript + Vue + jQuery）
  • 云蝠智能 VoiceAgent重构企业呼入场景服务范式
  • SpringCloud云间剑歌 第一章：云间阁现，群雄并起
  • 智能运维管理平台：AI赋能的数字化转型引擎
  • DNS（Domain Name System，域名系统）