SQL数据操作完全指南：增删改查实战详解（详细代码注释）

前言上篇讲了SQL查询的10个核心语句这篇讲SQL的另一半数据操作。查询SELECT是看数据而增删改INSERT/UPDATE/DELETE是动数据。很多同学学了SELECT就以为自己会SQL了结果一到实际工作中——录入数据不会INSERT改错了不会UPDATE删错了不会回滚。今天这篇把INSERT、UPDATE、DELETE三个语句讲透外加事务控制确保你不会因为误操作把数据库搞崩。建议先看上篇《SQL零基础入门10个语句解决80%的查询问题》再看这篇效果翻倍。〇、建表准备和上篇一样基于电商订单表来演示。如果上篇的表还在这步可以跳过。-- -- 电商订单表 orders -- 公主号船长Talk -- CREATE TABLE orders ( order_id INT PRIMARY KEY COMMENT 订单ID, customer VARCHAR(50) COMMENT 客户姓名, product VARCHAR(100) COMMENT 商品名称, category VARCHAR(50) COMMENT 商品分类, price DECIMAL(10,2) COMMENT 单价, quantity INT COMMENT 购买数量, total_amount DECIMAL(10,2) COMMENT 订单总金额, order_date DATE COMMENT 下单日期, status VARCHAR(20) COMMENT 订单状态 ); -- 插入初始测试数据 INSERT INTO orders VALUES (1, 张三, iPhone 15, 手机, 7999.00, 1, 7999.00, 2026-04-01, 已发货), (2, 李四, MacBook Pro, 电脑, 14999.00, 1,14999.00, 2026-04-01, 已完成), (3, 张三, AirPods Pro, 配件, 999.00, 2, 1998.00, 2026-04-02, 已完成), (4, 王五, iPhone 15, 手机, 7999.00, 1, 7999.00, 2026-04-02, 待发货), (5, 赵六, iPad Air, 平板, 4799.00, 2, 9598.00, 2026-04-03, 已取消), (6, 李四, Apple Watch, 配件, 2999.00, 1, 2999.00, 2026-04-03, 已完成), (7, 张三, MacBook Pro, 电脑, 14999.00, 1,14999.00, 2026-04-04, 待发货), (8, 王五, Magic Keyboard, 配件, 1999.00, 1, 1999.00, 2026-04-05, 已发货), (9, 赵六, iPhone 15, 手机, 7999.00, 1, 7999.00, 2026-04-05, 已完成), (10, 张三, HomePod mini, 配件, 749.00, 2, 1498.00, 2026-04-06, 已完成);一、INSERT —— 插入数据用途往表里新增一行或多行数据。这是所有数据操作的起点——没有INSERT表就是空的。1.1 基础插入一次插入一行-- -- INSERT 基础用法 -- 公主号船长Talk -- -- 最基本的插入按列的顺序填值 INSERT INTO orders VALUES (11, 孙七, AirPods 3, 配件, 1399.00, 1, 1399.00, 2026-04-07, 待发货); -- 指定列名插入推荐不依赖列的顺序更安全 INSERT INTO orders (order_id, customer, product, category, price, quantity, total_amount, order_date, status) VALUES (12, 周八, iPad mini, 平板, 3799.00, 2, 7598.00, 2026-04-07, 已完成);实战建议永远用指定列名的方式插入。如果以后表加了新列不指定列名的写法会直接报错。1.2 批量插入一次插入多行-- 批量插入效率远高于循环单条插入 -- 场景数据迁移、批量导入 INSERT INTO orders VALUES (13, 孙七, Apple Pencil, 配件, 999.00, 2, 1998.00, 2026-04-08, 已完成), (14, 周八, iPhone 15, 手机, 7999.00, 1, 7999.00, 2026-04-08, 待发货), (15, 吴九, MacBook Air, 电脑, 8999.00, 1, 8999.00, 2026-04-08, 已发货);性能提示批量插入比循环单条INSERT快10-100倍。导入10万条数据批量插入可能只要30秒循环单条可能要10分钟。1.3 插入查询结果INSERT INTO ... SELECT-- 把一个查询结果插入到另一张表 -- 场景数据备份、报表归档 -- 先创建历史订单表结构和orders一样 CREATE TABLE orders_history LIKE orders; -- 把已完成的订单归档到历史表 INSERT INTO orders_history SELECT * FROM orders WHERE status 已完成; -- 验证历史表里有多少条 SELECT COUNT(*) AS 归档订单数 FROM orders_history;实战场景每月把上月的已完成订单归档到历史表主表只保留近3个月的数据查询更快。1.4 插入时处理冲突ON DUPLICATE KEY UPDATE-- MySQL特有语法如果主键冲突就更新而不是报错 -- 场景用户积分系统——每天更新没有就新增 -- 创建用户积分表 CREATE TABLE user_points ( user_id INT PRIMARY KEY COMMENT 用户ID, points INT DEFAULT 0 COMMENT 积分余额, level VARCHAR(20) DEFAULT 普通会员 COMMENT 会员等级, updated_at DATE COMMENT 最后更新日期 ); -- 第一次插入不存在直接新增 INSERT INTO user_points (user_id, points, level, updated_at) VALUES (1001, 500, 银牌会员, 2026-04-07) ON DUPLICATE KEY UPDATE points points VALUES(points), level CASE WHEN points VALUES(points) 5000 THEN 金牌会员 WHEN points VALUES(points) 1000 THEN 银牌会员 ELSE 铜牌会员 END, updated_at 2026-04-07; -- 再执行一次已存在自动累加积分 INSERT INTO user_points (user_id, points, level, updated_at) VALUES (1001, 200, 银牌会员, 2026-04-08) ON DUPLICATE KEY UPDATE points points VALUES(points), updated_at 2026-04-08; -- 查看结果积分应该是 500 200 700 SELECT * FROM user_points WHERE user_id 1001;核心价值不用先SELECT判断存不存在再决定INSERT还是UPDATE。一条语句搞定代码更简洁也不会有并发问题。二、UPDATE —— 修改数据用途修改表中已有的数据。这是工作中最危险的操作之一——忘了加WHERE全表都会被改。2.1 基础更新-- -- UPDATE 基础用法 -- 公主号船长Talk -- -- 修改单个字段把订单4的状态改为已发货 UPDATE orders SET status 已发货 WHERE order_id 4; -- 修改多个字段同时改状态和日期 UPDATE orders SET status 已完成, order_date 2026-04-08 WHERE order_id 4; -- ⚠️ 危险操作演示千万别在生产环境跑 -- 忘了加WHERE所有订单的状态都会被改掉 -- UPDATE orders SET status 已完成;铁律UPDATE语句必须有WHERE条件。写完UPDATE先看一眼有没有WHERE没有就别执行。2.2 条件更新批量修改-- 把所有待发货的订单改为已发货 UPDATE orders SET status 已发货 WHERE status 待发货; -- 给所有手机品类的订单打9折 UPDATE orders SET price ROUND(price * 0.9, 2), total_amount ROUND(quantity * price * 0.9, 2) WHERE category 手机; -- 给张三的所有已完成订单备注需要有remark字段才能用 -- 如果表没有remark字段先加ALTER TABLE orders ADD COLUMN remark VARCHAR(200); ALTER TABLE orders ADD COLUMN remark VARCHAR(200) DEFAULT NULL; UPDATE orders SET remark VIP客户订单 WHERE customer 张三 AND status 已完成;2.3 基于其他表的数据更新-- 创建折扣表 CREATE TABLE discounts ( category VARCHAR(50) PRIMARY KEY COMMENT 品类, rate DECIMAL(3,2) COMMENT 折扣率 ); INSERT INTO discounts VALUES (手机, 0.90), (电脑, 0.95), (配件, 0.85), (平板, 0.92); -- 用折扣表批量更新订单价格 UPDATE orders o INNER JOIN discounts d ON o.category d.category SET o.price ROUND(o.price * d.rate, 2), o.total_amount ROUND(o.quantity * o.price * d.rate, 2) WHERE o.status 待发货;实战场景运营部门给不同品类设置折扣你用一条UPDATE批量应用不用每个品类单独写。2.4 UPDATE CASE WHEN复杂条件更新-- 根据消费金额自动升级客户等级需要在orders表加level字段 ALTER TABLE orders ADD COLUMN customer_level VARCHAR(20) DEFAULT NULL; -- 一次UPDATE用CASE WHEN判断不同条件 UPDATE orders SET customer_level CASE WHEN total_amount 10000 THEN A-大客户 WHEN total_amount 5000 THEN B-中客户 WHEN total_amount 1000 THEN C-小客户 ELSE D-散客 END; -- 验证结果 SELECT customer_level, COUNT(*) AS 订单数, ROUND(AVG(total_amount),2) AS 平均金额 FROM orders GROUP BY customer_level ORDER BY 平均金额 DESC;三、DELETE —— 删除数据用途删除表中的数据。这是最危险的操作没有之一。铁律DELETE之前先用SELECT查一遍确认范围然后用事务包裹。3.1 基础删除-- -- DELETE 基础用法 -- 公主号船长Talk -- -- 删除指定行 DELETE FROM orders WHERE order_id 15; -- 删除满足条件的行删除所有已取消的订单 DELETE FROM orders WHERE status 已取消; -- ⚠️ 危险操作删除全表数据千万别在生产环境跑 -- DELETE FROM orders; -- 没有WHERE 删光所有数据安全习惯删除前先SELECT确认数据范围。-- 第一步先看看要删哪些数据 SELECT * FROM orders WHERE status 已取消; -- 第二步确认无误后再删 DELETE FROM orders WHERE status 已取消;3.2 DELETE vs TRUNCATE vs DROP-- 三种删除的区别非常重要 -- 1. DELETE逐行删除记录日志可以回滚慢 DELETE FROM orders WHERE status 已取消; -- 2. TRUNCATE清空整张表不记录日志不可回滚极快 TRUNCATE TABLE orders_history; -- 3. DROP删除整张表结构和数据都没了 DROP TABLE IF EXISTS temp_table;速查表操作范围WHERE条件可回滚速度DELETE部分行或全部✅ 支持✅ 事务内可回滚慢逐行删除TRUNCATE整张表❌ 不支持❌ 不可回滚极快DDL操作DROP整张表含结构❌ 不支持❌ 不可回滚快3.3 关联删除基于其他表的数据删除-- 删除在历史表中已归档的订单 -- 场景主表清理只保留最近活跃数据 DELETE o FROM orders o INNER JOIN orders_history h ON o.order_id h.order_id WHERE h.status 已完成 AND h.order_date 2026-03-01;四、事务控制 —— 安全网用途把多个SQL操作绑定为一个整体要么全部成功要么全部回滚。这是保护数据安全的最后一道防线。不会用事务就不要在生产环境碰UPDATE和DELETE。4.1 事务基础-- -- 事务基础 -- 公主号船长Talk -- -- 事务四个关键字START TRANSACTION / COMMIT / ROLLBACK / SAVEPOINT -- 场景张三买了一个MacBook需要同时更新订单状态和扣减库存 START TRANSACTION; -- 开启事务 -- 操作1更新订单状态 UPDATE orders SET status 已发货 WHERE order_id 7; -- 操作2扣减库存假设有inventory表 -- CREATE TABLE inventory (product VARCHAR(100), stock INT); -- INSERT INTO inventory VALUES (MacBook Pro, 50); -- UPDATE inventory SET stock stock - 1 WHERE product MacBook Pro; -- 如果一切正常提交事务 COMMIT; -- 如果出错了回滚撤销所有操作 -- ROLLBACK;4.2 实战带事务的批量操作-- 场景月末批量归档 清理 -- 要求归档和清理必须同时成功或同时失败 START TRANSACTION; -- 1. 把已完成订单复制到历史表 INSERT INTO orders_history SELECT * FROM orders WHERE status 已完成 AND order_date 2026-04-01; -- 2. 从主表删除已归档的订单 DELETE FROM orders WHERE status 已完成 AND order_date 2026-04-01; -- 3. 验证检查归档数量是否正确 -- 这里用SELECT不会影响事务只是检查 -- 确认无误提交 COMMIT; -- 如果归档数量不对回滚 -- ROLLBACK;4.3 SAVEPOINT部分回滚-- 场景批量更新订单状态中间某一步出错了 START TRANSACTION; -- 更新第一批待发货 → 已发货 UPDATE orders SET status 已发货 WHERE status 待发货; SAVEPOINT sp1; -- 设置保存点 -- 更新第二批已发货 → 已完成假设这步出错了 UPDATE orders SET status 已完成 WHERE status 已发货 AND order_date 2026-04-01; SAVEPOINT sp2; -- 如果第二步有问题只回滚到sp1保留第一步的结果 -- ROLLBACK TO sp1; -- 全部OK提交 COMMIT;五、综合实战用户积分系统维护把INSERT/UPDATE/DELETE/事务全部串起来做一个完整的用户积分维护场景。-- -- 综合实战用户积分系统 -- 公主号船长Talk -- -- 1. 建表 CREATE TABLE IF NOT EXISTS user_points ( user_id INT PRIMARY KEY COMMENT 用户ID, username VARCHAR(50) NOT NULL COMMENT 用户名, points INT DEFAULT 0 COMMENT 积分余额, level VARCHAR(20) DEFAULT 普通会员 COMMENT 等级, created_at DATE COMMENT 注册日期, updated_at DATE COMMENT 最后活跃日期 ); -- 2. 初始化数据 INSERT INTO user_points VALUES (1001, 张三, 3200, 银牌会员, 2025-06-15, 2026-04-08), (1002, 李四, 8700, 金牌会员, 2024-11-20, 2026-04-08), (1003, 王五, 800, 铜牌会员, 2026-01-10, 2026-04-07), (1004, 赵六, 50, 普通会员, 2026-04-01, 2026-04-06); -- 3. 每日积分签到有就更新没有就插入 INSERT INTO user_points (user_id, username, points, created_at, updated_at) VALUES (1005, 孙七, 10, 2026-04-08, 2026-04-08) ON DUPLICATE KEY UPDATE points points 10, updated_at 2026-04-08; -- 4. 消费送积分每消费100元送10积分 START TRANSACTION; UPDATE user_points SET points points FLOOR(7999 / 100) * 10, -- iPhone消费得790积分 updated_at 2026-04-08 WHERE user_id 1001; -- 5. 积分过期清理超过180天未活跃的用户积分清零 DELETE FROM user_points WHERE DATEDIFF(2026-04-08, updated_at) 180; -- 6. 自动升级等级 UPDATE user_points SET level CASE WHEN points 10000 THEN 钻石会员 WHEN points 5000 THEN 金牌会员 WHEN points 1000 THEN 银牌会员 WHEN points 100 THEN 铜牌会员 ELSE 普通会员 END; -- 全部成功提交 COMMIT; -- 7. 查看最终结果 SELECT user_id, username, points, level, updated_at FROM user_points ORDER BY points DESC;六、安全操作清单在正式操作数据之前对照这个清单检查一遍操作安全检查项INSERT是否指定了列名数据类型是否匹配有没有主键冲突UPDATE有没有WHERE先用SELECT确认范围是否在事务内DELETE有没有WHERE先用SELECT确认范围是否在事务内是否需要备份事务START TRANSACTION写了操作验证了COMMIT/ROLLBACK别忘记写在最后INSERT/UPDATE/DELETE 事务控制这是SQL数据操作的完整闭环。和上篇的查询语句组合起来你已经掌握了SQL的全部基础操作查询SELECT / WHERE / ORDER BY / GROUP BY / HAVING上篇写入INSERT本篇修改UPDATE本篇删除DELETE本篇安全事务控制本篇下一步进阶学习JOIN多表查询下篇更新面试准备SQL面试50题关注公主号持续更新完整代码后台私信sql 代码获取建表SQL 全部练习代码-- 公主号船长Talk -- 更多数据分析干货SQL/Python/机器学习持续更新 -- 有问题欢迎评论区留言船长看到都会回复觉得有用的话点赞收藏转给身边学SQL的朋友。下一篇讲JOIN多表查询不想错过就关注一下。