数据插入更新删除

一、插入数据

1.1 INSERT语法

1.1.1 基本语法

语法格式：

INSERT INTO 表名 (列名1, 列名2, ...)
VALUES (值1, 值2, ...);

示例：

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED,
    gender ENUM('男', '女') DEFAULT '男',
    email VARCHAR(100)
);

-- 插入单条数据
INSERT INTO students (name, age, gender, email)
VALUES ('张三', 20, '男', 'zhangsan@example.com');

1.1.2 省略列名

语法格式：

INSERT INTO 表名
VALUES (值1, 值2, ...);

示例：

-- 必须按列顺序提供所有值
INSERT INTO students
VALUES (NULL, '李四', 22, '女', 'lisi@example.com');

⚠️ 注意：不推荐省略列名，表结构变化时容易出错。

1.2 插入多条数据

1.2.1 批量插入

语法格式：

INSERT INTO 表名 (列名1, 列名2, ...)
VALUES 
    (值1, 值2, ...),
    (值1, 值2, ...),
    (值1, 值2, ...);

示例：

INSERT INTO students (name, age, gender, email)
VALUES 
    ('王五', 21, '男', 'wangwu@example.com'),
    ('赵六', 19, '女', 'zhaoliu@example.com'),
    ('孙七', 23, '男', 'sunqi@example.com');

1.2.2 性能优势

单条插入：

INSERT INTO students (name, age) VALUES ('学生1', 20);
INSERT INTO students (name, age) VALUES ('学生2', 21);
INSERT INTO students (name, age) VALUES ('学生3', 22);

批量插入：

INSERT INTO students (name, age)
VALUES 
    ('学生1', 20),
    ('学生2', 21),
    ('学生3', 22);

性能对比：

• 批量插入减少SQL解析次数
• 减少网络传输
• 提高插入效率

1.3 INSERT SET语法

1.3.1 SET语法格式

语法格式：

INSERT INTO 表名
SET 列名1 = 值1, 列名2 = 值2, ...;

示例：

INSERT INTO students
SET name = '周八', age = 20, gender = '男';

优点：

• 列名和值对应清晰
• 可读性强
• 适合插入部分列

1.4 INSERT SELECT

1.4.1 从其他表复制数据

语法格式：

INSERT INTO 目标表 (列名1, 列名2, ...)
SELECT 列名1, 列名2, ...
FROM 源表
[WHERE 条件];

示例：

-- 创建备份表
CREATE TABLE students_backup (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age TINYINT UNSIGNED,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 复制数据
INSERT INTO students_backup (id, name, age)
SELECT id, name, age FROM students;

1.4.2 条件复制

-- 只复制年龄大于20的学生
INSERT INTO students_backup (id, name, age)
SELECT id, name, age 
FROM students 
WHERE age > 20;

1.5 INSERT IGNORE

1.5.1 忽略错误

语法格式：

INSERT IGNORE INTO 表名 (列名1, 列名2, ...)
VALUES (值1, 值2, ...);

示例：

-- 创建有唯一约束的表
CREATE TABLE unique_test (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 插入数据
INSERT INTO unique_test VALUES (1, 'test@example.com');

-- 重复插入会报错
INSERT INTO unique_test VALUES (2, 'test@example.com');
-- ERROR 1062 (23000): Duplicate entry 'test@example.com' for key 'email'

-- 使用IGNORE忽略错误
INSERT IGNORE INTO unique_test VALUES (2, 'test@example.com');
-- Query OK, 0 rows affected

1.6 REPLACE INTO

1.6.1 替换插入

语法格式：

REPLACE INTO 表名 (列名1, 列名2, ...)
VALUES (值1, 值2, ...);

特点：

• 如果主键或唯一键冲突，先删除再插入
• 如果不冲突，直接插入

示例：

-- 插入数据
INSERT INTO unique_test VALUES (1, 'test1@example.com');

-- 使用REPLACE替换
REPLACE INTO unique_test VALUES (1, 'test2@example.com');

-- 查看结果
SELECT * FROM unique_test;

输出：

+----+--------------------+
| id | email              |
+----+--------------------+
|  1 | test2@example.com  |
+----+--------------------+

1.7 ON DUPLICATE KEY UPDATE

1.7.1 冲突时更新

语法格式：

INSERT INTO 表名 (列名1, 列名2, ...)
VALUES (值1, 值2, ...)
ON DUPLICATE KEY UPDATE 列名1 = 新值1, 列名2 = 新值2, ...;

示例：

-- 创建表
CREATE TABLE counter (
    id INT PRIMARY KEY,
    count INT DEFAULT 0
);

-- 插入或更新
INSERT INTO counter (id, count) 
VALUES (1, 1)
ON DUPLICATE KEY UPDATE count = count + 1;

-- 第一次执行：插入 (1, 1)
-- 第二次执行：更新为 (1, 2)
-- 第三次执行：更新为 (1, 3)

二、更新数据

2.1 UPDATE语法

2.1.1 基本语法

语法格式：

UPDATE 表名
SET 列名1 = 值1, 列名2 = 值2, ...
[WHERE 条件];

示例：

-- 更新单个字段
UPDATE students 
SET age = 21 
WHERE name = '张三';

-- 更新多个字段
UPDATE students 
SET age = 22, email = 'newemail@example.com'
WHERE name = '李四';

2.1.2 省略WHERE

⚠️ 危险操作：

-- 更新所有记录
UPDATE students SET age = 20;

建议：

• 始终使用WHERE子句
• 更新前先用SELECT确认
• 使用事务确保安全

2.2 条件更新

2.2.1 使用WHERE子句

单条件更新：

UPDATE students 
SET gender = '女' 
WHERE name = '王五';

多条件更新：

UPDATE students 
SET age = age + 1 
WHERE age < 20 AND gender = '男';

2.2.2 使用表达式

-- 年龄加1
UPDATE students SET age = age + 1 WHERE id = 1;

-- 字符串拼接
UPDATE students 
SET email = CONCAT(name, '@example.com')
WHERE email IS NULL;

2.3 多表更新

2.3.1 UPDATE JOIN

语法格式：

UPDATE 表1
JOIN 表2 ON 表1.列 = 表2.列
SET 表1.列 = 值
[WHERE 条件];

示例：

-- 创建订单表和客户表
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    level VARCHAR(20)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    discount DECIMAL(5, 2)
);

-- 根据客户等级更新订单折扣
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.discount = 0.1
WHERE c.level = 'VIP';

2.4 ORDER BY和LIMIT

2.4.1 限制更新数量

语法格式：

UPDATE 表名
SET 列名 = 值
[WHERE 条件]
ORDER BY 排序列
LIMIT 数量;

示例：

-- 更新年龄最大的3个学生
UPDATE students 
SET gender = '男'
ORDER BY age DESC
LIMIT 3;

三、删除数据

3.1 DELETE语法

3.1.1 基本语法

语法格式：

DELETE FROM 表名
[WHERE 条件];

示例：

-- 删除单条记录
DELETE FROM students WHERE id = 1;

-- 删除多条记录
DELETE FROM students WHERE age < 18;

3.1.2 省略WHERE

⚠️ 危险操作：

-- 删除所有记录
DELETE FROM students;

建议：

• 始终使用WHERE子句
• 删除前先用SELECT确认
• 使用事务确保安全

3.2 条件删除

3.2.1 使用WHERE子句

单条件删除：

DELETE FROM students WHERE name = '张三';

多条件删除：

DELETE FROM students 
WHERE age < 18 OR email IS NULL;

3.2.2 使用子查询

-- 删除没有订单的客户
DELETE FROM customers
WHERE id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

3.3 多表删除

3.3.1 DELETE JOIN

语法格式：

DELETE 表1, 表2
FROM 表1
JOIN 表2 ON 表1.列 = 表2.列
WHERE 条件;

示例：

-- 删除订单及相关客户
DELETE o, c
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.level = 'inactive';

3.4 ORDER BY和LIMIT

3.4.1 限制删除数量

语法格式：

DELETE FROM 表名
[WHERE 条件]
ORDER BY 排序列
LIMIT 数量;

示例：

-- 删除年龄最小的3个学生
DELETE FROM students
ORDER BY age ASC
LIMIT 3;

3.5 TRUNCATE TABLE

3.5.1 清空表数据

语法格式：

TRUNCATE TABLE 表名;

示例：

TRUNCATE TABLE students;

3.5.2 TRUNCATE与DELETE区别

性能对比：

-- TRUNCATE：快速清空
TRUNCATE TABLE students;

-- DELETE：逐行删除
DELETE FROM students;

四、数据复制

4.1 表间复制

4.1.1 复制到新表

语法格式：

CREATE TABLE 新表 AS
SELECT * FROM 源表;

示例：

-- 复制表结构和数据
CREATE TABLE students_copy AS
SELECT * FROM students;

-- 复制部分数据
CREATE TABLE students_2024 AS
SELECT * FROM students
WHERE YEAR(create_time) = 2024;

4.1.2 复制表结构

语法格式：

CREATE TABLE 新表 LIKE 源表;

示例：

CREATE TABLE students_template LIKE students;

4.2 跨数据库复制

4.2.1 不同数据库间复制

语法格式：

INSERT INTO 目标数据库.表名
SELECT * FROM 源数据库.表名;

示例：

-- 从school数据库复制到backup数据库
INSERT INTO backup.students
SELECT * FROM school.students;

4.3 导入导出数据

4.3.1 导出数据

使用SELECT INTO OUTFILE：

SELECT * FROM students
INTO OUTFILE '/tmp/students.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

4.3.2 导入数据

使用LOAD DATA INFILE：

LOAD DATA INFILE '/tmp/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

五、事务处理

5.1 事务基础

5.1.1 ACID特性

5.1.2 事务控制语句

开始事务：

START TRANSACTION;
-- 或
BEGIN;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

5.2 事务示例

5.2.1 转账示例

-- 创建账户表
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO accounts VALUES 
(1, '张三', 1000.00),
(2, '李四', 1000.00);

-- 开始事务
START TRANSACTION;

-- 张三转账100给李四
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 检查余额
SELECT * FROM accounts;

-- 提交事务
COMMIT;

5.2.2 回滚示例

-- 开始事务
START TRANSACTION;

-- 更新数据
UPDATE students SET age = 100 WHERE id = 1;

-- 发现错误，回滚
ROLLBACK;

5.3 保存点

5.3.1 SAVEPOINT

设置保存点：

SAVEPOINT 保存点名;

回滚到保存点：

ROLLBACK TO SAVEPOINT 保存点名;

示例：

START TRANSACTION;

INSERT INTO students (name, age) VALUES ('测试1', 20);
SAVEPOINT sp1;

INSERT INTO students (name, age) VALUES ('测试2', 21);
SAVEPOINT sp2;

INSERT INTO students (name, age) VALUES ('测试3', 22);

-- 回滚到sp2
ROLLBACK TO SAVEPOINT sp2;

-- 提交事务
COMMIT;
-- 最终只插入了测试1和测试2

六、安全操作建议

6.1 操作前检查

6.1.1 使用SELECT确认

更新前确认：

-- 先查询要更新的数据
SELECT * FROM students WHERE age < 18;

-- 确认无误后再更新
UPDATE students SET status = 'minor' WHERE age < 18;

删除前确认：

-- 先查询要删除的数据
SELECT * FROM students WHERE age < 18;

-- 确认无误后再删除
DELETE FROM students WHERE age < 18;

6.2 使用事务

6.2.1 重要操作使用事务

-- 开始事务
START TRANSACTION;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 检查结果
SELECT * FROM accounts;

-- 确认无误后提交
COMMIT;
-- 或发现错误回滚
-- ROLLBACK;

6.3 备份数据

6.3.1 操作前备份

-- 创建备份表
CREATE TABLE students_backup AS SELECT * FROM students;

-- 执行危险操作
DELETE FROM students WHERE age < 18;

-- 如果需要恢复
INSERT INTO students SELECT * FROM students_backup;

七、本章小结

7.1 核心要点

✅ INSERT插入数据，支持批量插入 ✅ UPDATE更新数据，必须使用WHERE ✅ DELETE删除数据，TRUNCATE快速清空 ✅ 事务保证数据一致性 ✅ 操作前备份和确认

7.2 验证清单

完成本章学习后，请确认您能够：

• 使用INSERT插入单条和多条数据
• 使用UPDATE更新数据
• 使用DELETE删除数据
• 区分DELETE和TRUNCATE
• 使用事务保证数据安全