SQL 表数据的增删操作详解
1. 增
1.1 CREATE
CREATE 语句用于创建数据表。
1.2 全列插入
全列插入指为表中的每一列都提供值。
1.3 指定列插入
指定列插入只给部分列赋值，未指定的列默认为 NULL。
1.4 插入否则更新（ON DUPLICATE KEY UPDATE）
具体语法：
INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...
当插入的主键或唯一键值已存在时，直接报错。使用此指令可在冲突时更新现有行。
1.5 替换（REPLACE）
主键 或者 唯一键 没有冲突，则直接插入；
主键 或者 唯一键 如果冲突，则删除后再插入
若无冲突则直接插入。若冲突则先删除旧行再插入新行。可通过受影响行数判断：1 表示插入，2 表示删除后插入。
1.7 duplicate 和 replace 的区别
1.7.1 核心操作逻辑不同
- REPLACE：冲突时先删除旧行，再插入新行。等价于 DELETE + INSERT。
- ON DUPLICATE KEY UPDATE：冲突时不删除，直接更新指定字段。等价于 UPDATE。
1.7.2 对自增主键的影响不同
- REPLACE：生成新的自增 ID，原有 ID 废弃。
- ON DUPLICATE KEY UPDATE：保持原有自增 ID 不变。
1.7.3 对未指定字段的处理不同
- REPLACE：未指定字段使用默认值，覆盖原值。
- ON DUPLICATE KEY UPDATE：未指定字段保持原值不变。
1.7.4 示例对比
假设表 student 有数据 (id=1, name='Tom', score=80)。
使用 ON DUPLICATE KEY UPDATE：
INSERT INTO student (name, score) VALUES ('Tom', 90) ON DUPLICATE KEY UPDATE score = VALUES(score);
结果：(1, 'Tom', 90)，ID 不变。
使用 REPLACE：
REPLACE INTO student (name, score) VALUES ('Tom', 90);
结果：(2, 'Tom', 90)，ID 变为 2。
1.7.5 适用场景
- REPLACE：需完全替换行，接受 ID 变化。
- ON DUPLICATE KEY UPDATE：需保留 ID 和部分数据，效率更高。
总结
REPLACE 是'删旧插新'，改变行存在性和 ID；ON DUPLICATE KEY UPDATE 是'原地更新'，仅修改字段。根据需求选择。
2. 删
2.1 删除数据（DELETE）
语法：
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
DELETE 可删除特定行或整表数据。
-- 查看原数据
SELECT * FROM exam_result WHERE name = '孙悟空';
-- 删除数据
DELETE FROM exam_result WHERE name = '孙悟空';
-- 查看删除结果
SELECT * FROM exam_result WHERE name = '孙悟空';
PS: DELETE 不会重置 AUTO_INCREMENT 计数器，删除后再次插入数据时，ID 会继续递增。
2.2 截断表（TRUNCATE）
语法：
TRUNCATE [TABLE] table_name
注意：慎用
- 只能整表操作，不能针对部分数据。
- 速度快，但不经过事务，无法回滚。
- 会重置 AUTO_INCREMENT 项。
2.3 删除的注意点
DELETE 和 TRUNCATE 都只删除表中的数据，表的结构（列定义、索引、约束等）会被完整保留。
- DELETE：仅删除行，元数据不变。
- TRUNCATE：清空内容但保留容器，重置自增 ID。