MySQL修炼手册7：数据修改基础：INSERT、UPDATE、DELETE语句详解

news/2024/7/27 7:42:35/文章来源:https://blog.csdn.net/qq_41780234/article/details/135574861

写在开头

在掌握了MySQL数据库的基础之后，学习如何对数据进行有效的修改是至关重要的。本篇博客旨在提供一个深入的指南，涵盖了数据修改的三大基础操作：插入（INSERT）、更新（UPDATE）、删除（DELETE）。为了更好地理解这些概念，我们首先创建一个示例表“水果表”，然后逐一解析每个操作的具体用法。

首先，让我们创建一个简单的“水果表”来演示这些操作：

CREATE TABLE fruits (id INT AUTO_INCREMENT,name VARCHAR(50),quantity INT,PRIMARY KEY (id)
);

这个表有三个字段：id（一个自增的主键），name（水果的名称），以及quantity（水果的数量）。

1 插入数据：INSERT语句

插入数据是数据库操作的基础，了解INSERT语句的多种用法对于高效地管理MySQL数据库至关重要。

1.1 基本插入

最基本的INSERT语句用于向表中添加单行数据。其语法如下：

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

例如，向水果表插入一行数据：

INSERT INTO fruits (name, quantity) VALUES ('Apple', 10);

1.2 插入多行数据

你可以一次性插入多行数据，这对于批量数据处理非常有用。语法如下：

INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;

例如，一次插入多种水果：

INSERT INTO fruits (name, quantity) VALUES ('Banana', 20), ('Orange', 30), ('Grapes', 15);

1.3 插入部分列

如果表中的其他列有默认值或允许为空，你可以只插入部分列的数据。例如：

INSERT INTO table_name (column1) VALUES (value1);

在水果表中，如果quantity有默认值，可以只插入name：

INSERT INTO fruits (name) VALUES ('Pineapple');

1.4 使用NULL值

在允许NULL值的列中，你可以显式地插入NULL。例如：

INSERT INTO table_name (column1, column2) VALUES (NULL, value2);

如果quantity列允许NULL值：

INSERT INTO fruits (name, quantity) VALUES ('Mango', NULL);

1.5 从其他表中插入

你也可以使用一个SELECT语句从另一个表中插入数据：

INSERT INTO table_name1 (column1, column2) SELECT column1, column2 FROM table_name2;

例如，从一个临时水果表插入数据到主水果表：

INSERT INTO fruits (name, quantity) SELECT name, quantity FROM temp_fruits;

1.6 插入数据并返回ID

对于自增主键的表，插入数据后你可能需要得到新插入行的ID。这可以通过LAST_INSERT_ID()函数实现：

INSERT INTO table_name (column1, column2) VALUES (value1, value2);
SELECT LAST_INSERT_ID();

1.7 插入默认值

如果表的所有列都有默认值，可以这样插入一行默认值：

INSERT INTO table_name DEFAULT VALUES;

1.8 插入JSON数据

对于存储JSON数据的列，可以这样插入：

INSERT INTO table_name (json_column) VALUES ('{"key": "value"}');

通过这些丰富多样的INSERT用法，你可以灵活地处理各种数据插入场景，从而更有效地管理MySQL数据库。

1.9 插入时忽略错误

在某些情况下，你可能希望在插入时忽略主键或唯一约束导致的错误，而不是终止操作。这可以通过INSERT IGNORE实现：

INSERT IGNORE INTO table_name (column1, column2) VALUES (value1, value2);

这将忽略由于重复键引起的错误，而不是抛出一个错误。

1.10 条件插入

有时你可能只在满足特定条件时才执行插入。虽然INSERT本身不支持条件语句，但可以与SELECT语句结合来实现这一点：

INSERT INTO table_name (column1, column2) SELECT value1, value2 FROM dual WHERE condition;

例如，只有当某个条件为真时才插入数据：

INSERT INTO fruits (name, quantity) SELECT 'Peach', 20 FROM dual WHERE EXISTS (SELECT * FROM users WHERE username = 'admin');

1.11 使用ON DUPLICATE KEY UPDATE

当尝试插入的行在表中已存在具有相同的唯一键或主键时，ON DUPLICATE KEY UPDATE子句允许你更新行而不是插入新行：

INSERT INTO table_name (column1, column2) VALUES (value1, value2) ON DUPLICATE KEY UPDATE column2 = value2;

这对于需要插入或更新记录的情况非常有用。

1.12 插入数据并获取信息

使用INSERT ... ON DUPLICATE KEY UPDATE时，你可以使用LAST_INSERT_ID()与ROW_COUNT()来获取插入或更新操作的详细信息：

INSERT INTO table_name (column1, column2) VALUES (value1, value2) ON DUPLICATE KEY UPDATE column2 = VALUES(column2);
SELECT LAST_INSERT_ID(), ROW_COUNT();

1.13 插入数据到分区表

如果你的表被分区了，你可以像往常一样使用INSERT语句。MySQL会自动将数据插入到适当的分区：

INSERT INTO partitioned_table (column1, column2) VALUES (value1, value2);

1.14 使用子查询插入

你可以使用一个复杂的子查询来插入数据，这对于需要从多个表中提取数据的情况非常有用：

INSERT INTO table_name (column1, column2) SELECT column1, SUM(column2) FROM another_table GROUP BY column1;

1.15 插入并加锁

在某些高并发情况下，你可能需要在插入时对表加锁以保证数据一致性：

LOCK TABLES table_name WRITE;
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UNLOCK TABLES;

2 更新数据：UPDATE语句

在MySQL中，UPDATE语句是用来修改表中现有数据的强大工具。它的基本语法是：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

我们将通过不同的示例来展示UPDATE语句的多样用法

2.1 更新单个字段

最基本的用法是更新表中单个字段的值。例如，假设我们要将水果表中苹果的数量更新为20：

UPDATE fruits
SET quantity = 20
WHERE name = 'Apple';

2.2 更新多个字段

UPDATE可以同时修改多个字段。例如，苹果的名称改为“Green Apple”，数量改为30：

UPDATE fruits
SET name = 'Green Apple', quantity = 30
WHERE name = 'Apple';

2.3 使用条件表达式

可以使用更复杂的条件表达式来更新特定的数据。例如，增加所有数量少于10的水果的数量：

UPDATE fruits
SET quantity = quantity + 10
WHERE quantity < 10;

2.4 使用ORDER BY和LIMIT

在某些情况下，你可能需要按特定顺序更新记录，并限制更新的行数。例如，仅更新数量最少的两种水果的数量：

UPDATE fruits
SET quantity = quantity + 5
ORDER BY quantity ASC
LIMIT 2;

2.5 更新与其他表关联的数据

你还可以更新与其他表关联的数据。假设有另一个表suppliers，记录了水果的供应商。如果要更新特定供应商供应的所有水果的价格，可以使用：

UPDATE fruits
SET price = price * 1.1
WHERE id IN (SELECT fruit_id FROM suppliers WHERE supplier_name = 'ABC供应商');

2.6 使用CASE语句进行条件更新

CASE语句可以在更新时提供更多的灵活性。例如，根据水果的存货量来调整价格：

UPDATE fruits
SET price = CASEWHEN quantity < 20 THEN price * 0.9WHEN quantity BETWEEN 20 AND 50 THEN priceELSE price * 1.1
END;

2.7 使用数学函数和表达式

可以在UPDATE语句中使用各种数学函数和表达式。例如，将所有水果的数量增加当前数量的10%：

UPDATE fruits
SET quantity = quantity * 1.1;

2.8 利用JOIN进行复杂更新

当需要根据其他表中的数据来更新一个表时，可以使用JOIN语句。例如，如果我们有一个供应商表suppliers和水果表fruits，且想根据供应商的某些条件来更新水果的价格，可以这样做：

UPDATE fruits
JOIN suppliers ON fruits.supplier_id = suppliers.id
SET fruits.price = fruits.price * 1.05
WHERE suppliers.country = 'China';

这个语句会将所有中国供应商提供的水果价格提高5%。

2.9 使用子查询更新

在某些情况下，你可能需要根据从同一表或不同表中检索到的数据来更新记录。例如，假设你想将最畅销水果的价格提高10%：

UPDATE fruits
SET price = price * 1.1
WHERE name = (SELECT nameFROM salesGROUP BY nameORDER BY SUM(quantity) DESCLIMIT 1
);

这里，子查询找出了销量最高的水果名称，并更新了其价格。

2.10 使用TRIGGERS自动更新

在某些高级应用中，你可能想在某些事件发生时自动执行更新。这可以通过在数据库中创建触发器（TRIGGERS）来实现。例如，每次向sales表中添加一条新记录时，自动更新fruits表中相应水果的数量：

CREATE TRIGGER update_fruit_quantity
AFTER INSERT ON sales
FOR EACH ROW
BEGINUPDATE fruitsSET quantity = quantity - NEW.sold_quantityWHERE name = NEW.fruit_name;
END;

在这个例子中，每次在sales表中添加新销售记录后，fruits表中相应水果的数量将自动减去销售数量。

2.11 使用安全更新

为了避免错误地更新过多的行，MySQL提供了安全更新模式。当启用时，如果UPDATE语句没有使用WHERE子句（或没有唯一索引），MySQL将拒绝执行更新。这可以防止不小心更新了表中的所有记录。你可以通过以下命令启用安全更新模式：

SET SQL_SAFE_UPDATES = 1;

之后，任何没有明确WHERE子句或唯一索引的UPDATE语句都会被拒绝执行。

3 删除数据：DELETE语句

在MySQL中，DELETE语句是用来删除表中一行或多行数据的强大工具。理解其多样的用法对于有效管理数据库至关重要。以下是一些常用的DELETE语句用法：

3.1 DELETE语句的基本语法

基本的DELETE语句格式如下：

DELETE FROM table_name WHERE condition;
  • table_name 是你想要从中删除记录的表名。
  • WHERE condition 是用来指定哪些行应该被删除的条件。如果省略了WHERE子句，所有行都会被删除，这将清空表但不会删除表本身。

3.2 删除特定行

根据特定条件删除行。例如，删除水果表中所有“Apple”的记录：

DELETE FROM fruits WHERE name = 'Apple';

3.3 使用比较运算符

可以使用比较运算符（如<, >, <=, >=）来删除行。例如，删除所有数量少于20的水果：

DELETE FROM fruits WHERE quantity < 20;

3.4 使用逻辑运算符

使用AND、OR等逻辑运算符来组合条件。例如，删除名称为“Apple”且数量小于10的记录：

DELETE FROM fruits WHERE name = 'Apple' AND quantity < 10;

3.5 删除满足IN条件的行

当需要删除匹配特定列表中值的行时，可以使用IN。例如，删除所有名称是“Apple”，“Banana”，或“Orange”的水果：

DELETE FROM fruits WHERE name IN ('Apple', 'Banana', 'Orange');

3.6 使用LIKE操作符进行模糊删除

使用LIKE操作符删除满足模糊匹配条件的行。例如，删除所有名字以“A”开头的水果：

DELETE FROM fruits WHERE name LIKE 'A%';

3.7 使用LIMIT控制删除行数

使用LIMIT来限制删除操作影响的行数。例如，只删除两条水果记录：

DELETE FROM fruits LIMIT 2;

3.8 使用ORDER BY和LIMIT组合

结合ORDER BY和LIMIT来删除特定顺序的记录。例如，删除数量最少的3种水果：

DELETE FROM fruits ORDER BY quantity ASC LIMIT 3;

3.9 删除关联表中的数据

在有外键约束的关联表中，要谨慎进行删除操作。删除操作必须遵守外键约束，或者需要首先删除或更新子表中的关联行。

3.10 安全的删除操作

在执行删除操作前，建议先用SELECT语句进行测试，确保你将删除正确的行。例如：

SELECT * FROM fruits WHERE quantity < 10;

检查返回的结果，如果是你想要删除的数据，再执行相应的DELETE语句。

写在最后

通过本篇博客，我们深入探讨了MySQL中的数据修改基础操作：INSERT、UPDATE、DELETE语句。从创建一个简单的水果表开始，我们逐步展示了如何插入新数据、更新现有数据，以及删除不需要的数据。这些操作是数据库管理中非常重要的技能。掌握它们不仅有助于管理和维护数据库中的数据，还能提高你处理数据的灵活性和效率。

请记住，实践是学习数据库管理的关键。我建议你在自己的数据库环境中创建这样的表，并尝试执行这些操作。随着实践的增多，你将对MySQL的使用更加得心应手。如果在学习过程中遇到任何问题，不妨继续探索或寻求帮助。祝你在MySQL的学习之旅上取得更多进步！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.luyixian.cn/news_show_925214.aspx

如若内容造成侵权/违法违规/事实不符，请联系dt猫网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

C++面试宝典第19题：最长公共前缀

C++面试宝典第19题：最长公共前缀

题目 编写一个函数来查找字符串数组中的最长公共前缀，如果不存在公共前缀，返回空字符串""。说明：所有输入只包含小写字母a-z。 示例1： 输入: ["flower", "flow", "flight"]输出: "fl" 示例2： 输入: ["dog",…
阅读更多...
如何在Windows 10/11的防火墙中禁止和允许某个应用程序，这里提供详细步骤

如何在Windows 10/11的防火墙中禁止和允许某个应用程序，这里提供详细步骤

想阻止应用程序访问互联网吗&#xff1f;以下是如何通过简单的步骤阻止和允许Windows防火墙中的程序。​ 一般来说&#xff0c;大多数用户永远不需要担心应用程序访问互联网。然而&#xff0c;在某些情况下&#xff0c;你需要限制应用程序访问互联网。 例如&#xff0c;有问题…
阅读更多...
vue知识-03

vue知识-03

购物车案例 要实现的功能&#xff1a; 1、计算商品总价格 2、全选框和取消全选框 3、商品数量的增加和减少 <body> <div id"app"><div class"row"><div class"col-md-6 col-md-offset-3"><h1 class"text-center…
阅读更多...
TinyLog iOS v3.0接入文档

TinyLog iOS v3.0接入文档

1.背景 为在线教育部提供高效、安全、易用的日志组件。 2.功能介绍 2.1 日志格式化 目前输出的日志格式如下&#xff1a; 日志级别/[YYYY-MM-DD HH:MM:SS MS] TinyLog-Tag: |线程| 代码文件名:行数|函数名|日志输出内容触发flush到文件的时机&#xff1a; 每15分钟定时触发…
阅读更多...
【Spring 篇】走进SpringMVC的世界：舞动Web的激情

【Spring 篇】走进SpringMVC的世界：舞动Web的激情

嗨&#xff0c;亲爱的小白们&#xff01;欢迎来到这篇关于SpringMVC的博客&#xff0c;让我们一起探索这个舞动Web的框架&#xff0c;感受它带来的激情和便利。在这个世界里&#xff0c;我们将学到SpringMVC的概述、开发步骤以及如何快速入门&#xff0c;一切都是如此的令人兴奋…
阅读更多...
C# Winform翻牌子记忆小游戏

C# Winform翻牌子记忆小游戏

效果 源码 新建一个winform项目命名为Matching Game&#xff0c;选用.net core 6框架 并把Form1.cs代码修改为 using Timer System.Windows.Forms.Timer;namespace Matching_Game {public partial class Form1 : Form{private const int row 4;private const int col 4;p…
阅读更多...
为什么光刻要用黄光

为什么光刻要用黄光

光刻是集成电路&#xff08;IC或芯片&#xff09;制造中的重要工艺之一。简单来说&#xff0c;它是通过使用光掩膜和光刻胶在基板上复制电路图案的过程。 基板将涂覆硅二氧化层绝缘层和光刻胶。光刻胶在被紫外光照射后可以容易地用显影剂溶解&#xff0c;然后在腐蚀后&#xf…
阅读更多...
MongoDB Compass当前版本及历史版本下载安装

MongoDB Compass当前版本及历史版本下载安装

mongoDB compass 当前版本下载 官网 https://www.mongodb.com/try/download/compass 官网下载一般只能下载最新版本。 github https://github.com/mongodb-js/compass MongoDB Compass与MongoDB的版本对应关系 MongoDB CompassMongoDB1.9.12MongoDB 2.6.11 Community
阅读更多...
Redis：原理速成+项目实战——Redis企业级项目实战终结篇（HyperLogLog实现UV统计）

Redis：原理速成+项目实战——Redis企业级项目实战终结篇（HyperLogLog实现UV统计）

&#x1f468;‍&#x1f393;作者简介&#xff1a;一位大四、研0学生&#xff0c;正在努力准备大四暑假的实习 &#x1f30c;上期文章&#xff1a;Redis&#xff1a;原理速成项目实战——Redis实战14&#xff08;BitMap实现用户签到功能&#xff09; &#x1f4da;订阅专栏&am…
阅读更多...
uniapp-uniCloud的基本使用（编写云存储的地区级联选择器）

uniapp-uniCloud的基本使用（编写云存储的地区级联选择器）

目录 新建项目&#xff0c;创建 uniCloud 服务空间并关联 1. 新建项目 2. 创建 uniCloud 服务空间并关联 manifest.json内未配置Appld,请重新获取后再 云数据库的使用 城市选择和云数据库 介绍 云端数据 DB Schema概述 新建项目&#xff0c;创建 uniCloud 服务空间并关…
阅读更多...
统计学-R语言-4.3

统计学-R语言-4.3

文章目录 前言直方图茎叶图箱线图练习 前言 本篇介绍的是数值型数据怎么进行数据可视化&#xff0c;本篇介绍的有直方图、茎叶图、箱线图。 直方图 直方图&#xff08;Histogram&#xff09;用于描述连续型变量的频数分布&#xff0c;实际应用中常用于考察变量的分布是否对称…
阅读更多...
Java零基础——Vue基础篇

Java零基础——Vue基础篇

1.【熟悉】Vue简介 1.1 简介 它是一个构建用户界面单页面的框架 Vue是一个前端框架 https://www.pmdaniu.com/#file UI网站 UI 一般开发者使用蓝湖 工具 看着UI图 写接口 https://lanhuapp.com/web/#/item 是一个轻量级的MVVM&#xff08;Model-View-ViewModel&#xff…
阅读更多...
maven管理使用

maven管理使用

maven基本使用 一、简介二、配置文件三、项目结构maven基本标签实践(例子) 四、pom插件配置五、热部署六、maven 外部手动加载jar打包方式Maven上传私服或者本地 一、简介 基于Ant 的构建工具,Ant 有的功能Maven 都有,额外添加了其他功能.本地仓库:计算机中一个文件夹,自己定义…
阅读更多...
Linux实操学习

Linux实操学习

Linux常用操作 一、帮助命令1. man1.1 基本语法1.2 快捷键1.3 注意事项 2. help2.1 基本语法2.2 注意事项 3. 常用快捷键 二、文件目录类1. 常规操作1.1 pwd1.2 cd1.3 ls 2. 文件夹操作2.1 mkdir2.2 rmdir 3. 文件操作3.1 touch3.2 cp3.3 rm3.4 mv 4. 文件查看4.1 cat4.2 more4…
阅读更多...
国内外好用的 LLM 列表

国内外好用的 LLM 列表

视频来源&#xff1a;https://www.bilibili.com/video/BV1c64y157Qm/?vd_source1e841703c91b5b77fd20e5707bae49d2 下图是测试括号闭合能力的得分
阅读更多...
windows系统下docker软件中使用ubuntu发行版本的linux系统

windows系统下docker软件中使用ubuntu发行版本的linux系统

1.软件下载 官网下载地址 下载安装之后&#xff0c;再去微软商店下载wsl软件&#xff0c;可以直接用&#xff0c;或者也可以使用命令行拉取&#xff08;下面会讲&#xff09; 2.在docker里面创建容器的两种方法 2.1.命令行创建 前言&#xff1a;输入 winr 打开命令行进行下面…
阅读更多...
软件测试|使用Python抓取百度新闻的页面内容

软件测试|使用Python抓取百度新闻的页面内容

简介 作为技术工程师&#xff0c;在繁忙的工作中我们不一定有时间浏览发生的热点新闻&#xff0c;但是懂技术的我们不需要访问网站来看当下发生的大事&#xff0c;我们可以使用网络爬虫的技术来获取当下最新最热的新闻&#xff0c;本文就来介绍一下使用Python抓取一下百度新闻…
阅读更多...
鸿蒙APP和Android的区别

鸿蒙APP和Android的区别

鸿蒙&#xff08;HarmonyOS&#xff09;和Android是两个不同的操作系统&#xff0c;它们有一些区别&#xff0c;包括架构、开发者支持、应用生态和一些设计理念。以下是鸿蒙APP和Android APP之间的一些主要区别&#xff0c;希望对大家有所帮助。北京木奇移动技术有限公司&#…
阅读更多...
码牛课堂首推——鸿蒙南北双向开发学习路线图标准版~

码牛课堂首推——鸿蒙南北双向开发学习路线图标准版~

鸿蒙&#xff01;鸿蒙&#xff01;鸿蒙&#xff01; 要说2023-2024年IT圈最火爆的名词&#xff0c;一定是鸿蒙&#xff01; 2023年9月25日&#xff0c;华为发布会正式宣布2024年第一季度将推出HarmonyOS NEXT版本&#xff0c;这意味着鸿蒙原生应用开发将彻底摆脱Android手机系…
阅读更多...
了解Python中的requests.Session对象及其用途

了解Python中的requests.Session对象及其用途

前言 在Python的网络编程中&#xff0c;requests库是一个非常流行的HTTP客户端库&#xff0c;用于发送各种类型的HTTP请求。在requests库中&#xff0c;requests.Session对象提供了一种在多个请求之间保持状态的方法本文将探讨Python中的requests.Session对象及其用途&#xf…
阅读更多...
推荐文章
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站 广利置业