数据操作

数据操作（DML）

DML（Data Manipulation Language）用于对表中的数据进行增（INSERT）、删（DELETE）、改（UPDATE）。下面详细介绍这三类操作。

一、插入数据（INSERT）

1. 插入完整行

sql

INSERT INTO table_name VALUES (value1, value2, ...);

值的顺序必须与表中定义的列顺序完全一致。
字符串和日期需要加引号。
自增列（AUTO_INCREMENT）可以填 NULL 或 DEFAULT，系统会自动分配。

示例（假设 students 表有 id, name, age 三列）：

sql

INSERT INTO students VALUES (NULL, '张三', 20);

2. 插入指定列

sql

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

未指定的列会使用默认值（如果有 DEFAULT 定义）或 NULL（若允许）。
推荐使用这种方式，避免依赖列的顺序，代码更健壮。

示例：

sql

INSERT INTO students (name, age) VALUES ('李四', 22);

3. 批量插入

sql

INSERT INTO table_name (col1, col2) VALUES
    (val1a, val2a),
    (val1b, val2b),
    ...;

一次插入多行，减少网络往返和日志开销，性能优于逐条插入。

示例：

sql

INSERT INTO students (name, age) VALUES
    ('王五', 19),
    ('赵六', 21);

4. 插入查询结果

sql

INSERT INTO table_name (col1, col2)
SELECT colA, colB FROM another_table WHERE condition;

可以将一个表的数据复制或筛选后插入到另一个表。

示例：将 temp_students 中年龄大于 18 的记录插入 students

sql

INSERT INTO students (name, age)
SELECT name, age FROM temp_students WHERE age > 18;

5. 插入时的冲突处理

INSERT IGNORE：插入时若违反主键或唯一约束，忽略该行，不报错继续。
ON DUPLICATE KEY UPDATE：当主键或唯一键冲突时，执行更新操作。

sql

INSERT INTO students (id, name, age) VALUES (1, '张三', 20)
ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

二、更新数据（UPDATE）

sql

UPDATE table_name
SET col1 = new_value1, col2 = new_value2, ...
WHERE condition;

WHERE 子句极其重要：如果省略 WHERE，会更新表中所有行。
可以同时更新多个列。
可以使用子查询和现有列值计算新值。

示例：

sql

-- 将 id=1 的学生年龄增加 1 岁
UPDATE students SET age = age + 1 WHERE id = 1;

-- 将所有女生的成绩提高 5 分
UPDATE scores SET score = score + 5 WHERE gender = 'F';

注意事项：

更新前建议先用 SELECT 相同条件确认范围。
在事务中执行时可以 ROLLBACK 撤销。
更新大量数据时，考虑分批进行，避免长事务锁表。

三、删除数据（DELETE）

sql

DELETE FROM table_name WHERE condition;

同样，WHERE 必不可少（除非你有意清空整张表）。
删除操作会逐行删除，并记录事务日志，可以回滚。
删除后，自增列的值不会重置（除非使用 TRUNCATE）。

示例：

sql

-- 删除 id=5 的学生
DELETE FROM students WHERE id = 5;

-- 删除年龄大于 50 的所有学生
DELETE FROM students WHERE age > 50;

全表删除：TRUNCATE vs DELETE

操作	速度	是否可回滚	重置自增	触发触发器
`DELETE FROM table`	慢（逐行删除）	可以（在事务中）	否	是
`TRUNCATE TABLE table`	极快（释放数据页）	不可回滚（DDL）	是	否

使用建议：

需要快速清空表且不需要回滚时，使用 TRUNCATE。
需要条件删除或希望保留自增计数器时，使用 DELETE。

四、最佳实践

始终使用 WHERE 保护
在 UPDATE 和 DELETE 中，务必加上 WHERE 条件。执行前先在测试环境用 SELECT 验证。
开启事务（InnoDB 引擎）
sql
```
START TRANSACTION;
UPDATE ...;
DELETE ...;
COMMIT;  -- 或 ROLLBACK;
```
确保多个操作要么全部成功，要么全部失败。
批量操作限制行数
对于大量数据的修改，可以加 LIMIT 分批进行，避免长时间锁表。
sql
```
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;
```
使用 INSERT ... ON DUPLICATE KEY UPDATE
实现“存在则更新，不存在则插入”的 upsert 语义，简化代码。
注意字符集和转义（防止 SQL 注入）
在应用代码中，应使用参数化查询（如 PreparedStatement）或 ORM 框架，避免直接拼接 SQL。

五、示例综合演示

sql

-- 插入几条记录
INSERT INTO employees (name, salary, dept_id) VALUES
    ('Alice', 5000, 1),
    ('Bob', 6000, 2),
    ('Charlie', 5500, 1);

-- 更新：给部门1的所有员工加薪10%
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 1;

-- 删除工资低于 4000 的员工
DELETE FROM employees WHERE salary < 4000;

-- 查看结果
SELECT * FROM employees;

掌握 DML 语句是日常数据库操作的核心，需要格外注意数据安全（尤其是 UPDATE 和 DELETE）。建议在进行危险操作前先备份或使用事务。

参考文献

资料	说明
MySQL 8.0 手册	官方
数据存储导读	学习路径