写在开头
在掌握了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 = CASE
WHEN quantity < 20 THEN price * 0.9
WHEN quantity BETWEEN 20 AND 50 THEN price
ELSE 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 name
FROM sales
GROUP BY name
ORDER BY SUM(quantity) DESC
LIMIT 1
);
这里，子查询找出了销量最高的水果名称，并更新了其价格。
2.10 使用TRIGGERS自动更新
在某些高级应用中，你可能想在某些事件发生时自动执行更新。这可以通过在数据库中创建触发器（TRIGGERS）来实现。例如，每次向sales表中添加一条新记录时，自动更新fruits表中相应水果的数量：
CREATE TRIGGER update_fruit_quantity
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
UPDATE fruits
SET quantity = quantity - NEW.sold_quantity
WHERE 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语句。从创建一个简单的水果表开始，我们逐步展示了如何插入新数据、更新现有数据，以及删除不需要的数据。这些操作是数据库管理中非常重要的技能。掌握它们不仅有助于管理和维护数据库中的数据，还能提高你处理数据的灵活性和效率。文章来源：https://www.toymoban.com/news/detail-795850.html
请记住，实践是学习数据库管理的关键。我建议你在自己的数据库环境中创建这样的表，并尝试执行这些操作。随着实践的增多，你将对MySQL的使用更加得心应手。如果在学习过程中遇到任何问题，不妨继续探索或寻求帮助。祝你在MySQL的学习之旅上取得更多进步！文章来源地址https://www.toymoban.com/news/detail-795850.html
到了这里，关于MySQL修炼手册7：数据修改基础：INSERT、UPDATE、DELETE语句详解的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
