数据操作

本章将介绍MySQL中数据的插入、更新和删除操作，即CRUD操作中的C（Create）、U（Update）、D（Delete）。

插入数据

基本插入

-- 创建示例表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    gender ENUM('男', '女'),
    score DECIMAL(5, 2)
);

-- 插入单条数据（指定列名）
INSERT INTO students (name, age, gender, score)
VALUES ('张三', 20, '男', 95.5);

-- 插入单条数据（不指定列名，需按顺序）
INSERT INTO students
VALUES (NULL, '李四', 21, '女', 88.0);
-- NULL表示让AUTO_INCREMENT自动生成ID

-- 查看插入的数据
SELECT * FROM students;

批量插入

-- 批量插入多条数据
INSERT INTO students (name, age, gender, score) VALUES
('王五', 22, '男', 92.0),
('赵六', 19, '女', 85.5),
('钱七', 23, '男', 90.0);

-- 查看所有数据
SELECT * FROM students;

/*
 * 批量插入的优点：
 * 1. 减少网络传输次数
 * 2. 提高插入效率
 * 3. 减少事务开销
 */

插入查询结果

-- 创建备份表
CREATE TABLE students_backup LIKE students;

-- 将查询结果插入到另一个表
INSERT INTO students_backup (name, age, gender, score)
SELECT name, age, gender, score FROM students WHERE score >= 90;

-- 查看备份表
SELECT * FROM students_backup;

插入特殊值

-- 插入NULL值
INSERT INTO students (name, age, gender, score)
VALUES ('孙八', NULL, '男', NULL);

-- 插入默认值
INSERT INTO students (name, age, gender, score)
VALUES ('周九', DEFAULT, '女', DEFAULT);

-- 使用DEFAULT关键字
INSERT INTO students
VALUES (DEFAULT, '吴十', DEFAULT, DEFAULT, DEFAULT);

-- 使用表达式
INSERT INTO students (name, age, gender, score)
VALUES ('测试用户', 18 + 2, '男', 80 + 10);

INSERT IGNORE

-- 创建带唯一约束的表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

INSERT INTO users (username, email) VALUES ('user1', 'user1@test.com');

-- 普通插入会报错（重复键）
-- INSERT INTO users (username, email) VALUES ('user1', 'user2@test.com');
-- Error: Duplicate entry 'user1' for key 'username'

-- 使用INSERT IGNORE忽略错误
INSERT IGNORE INTO users (username, email) 
VALUES ('user1', 'user2@test.com');
-- 不会报错，但也不会插入数据

-- 查看受影响的行数
SELECT ROW_COUNT();  -- 返回0表示没有插入

INSERT ... ON DUPLICATE KEY UPDATE

-- 如果记录存在则更新，不存在则插入
INSERT INTO users (username, email) 
VALUES ('user1', 'newemail@test.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- 或使用更清晰的写法
INSERT INTO users (username, email) 
VALUES ('user1', 'updated@test.com')
ON DUPLICATE KEY UPDATE 
    email = VALUES(email),
    username = VALUES(username);

-- 查看结果
SELECT * FROM users;

REPLACE

-- REPLACE：先删除再插入
-- 如果存在则删除旧记录，插入新记录
REPLACE INTO users (id, username, email)
VALUES (1, 'user1_updated', 'user1_updated@test.com');

-- 注意：REPLACE会删除旧记录，可能导致自增ID变化
SELECT * FROM users;

更新数据

基本更新

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

-- 更新多个字段
UPDATE students 
SET age = 21, score = 97.0 
WHERE name = '张三';

-- 查看更新结果
SELECT * FROM students WHERE name = '张三';

条件更新

-- 更新多条记录
UPDATE students 
SET score = score + 5 
WHERE gender = '女';

-- 使用表达式更新
UPDATE students 
SET score = score * 1.1 
WHERE score < 90;

-- 使用CASE表达式
UPDATE students 
SET score = CASE 
    WHEN score >= 90 THEN 100
    WHEN score >= 80 THEN score + 5
    ELSE score + 10
END;

-- 使用子查询更新
UPDATE students 
SET score = (
    SELECT AVG(score) FROM students
)
WHERE score IS NULL;

多表更新

-- 创建关联表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary DECIMAL(10, 2),
    department_id INT
);

INSERT INTO departments (name) VALUES ('技术部'), ('销售部');
INSERT INTO employees (name, salary, department_id) 
VALUES ('张三', 10000, 1), ('李四', 8000, 2);

-- 多表更新
UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.1
WHERE d.name = '技术部';

SELECT * FROM employees;

安全更新

/*
 * 更新操作注意事项：
 * 
 * 1. 始终使用WHERE子句
 * 2. 更新前先用SELECT验证
 * 3. 使用事务确保安全
 * 4. 备份重要数据
 */

-- 错误示例：更新所有记录
-- UPDATE students SET score = 0;  -- 危险！

-- 正确做法：先查询再更新
SELECT * FROM students WHERE id = 1;
UPDATE students SET score = 0 WHERE id = 1;

-- 使用事务
START TRANSACTION;
UPDATE students SET score = 100 WHERE id = 1;
-- 检查结果
SELECT * FROM students WHERE id = 1;
-- 确认无误后提交
COMMIT;
-- 或回滚
-- ROLLBACK;

删除数据

基本删除

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

-- 删除多条记录
DELETE FROM students WHERE score < 60;

-- 删除所有记录（危险！）
-- DELETE FROM students;  -- 不推荐

-- 查看删除后的数据
SELECT * FROM students;

条件删除

-- 使用复杂条件删除
DELETE FROM students 
WHERE age > 25 AND gender = '男';

-- 使用子查询删除
DELETE FROM students 
WHERE id IN (
    SELECT id FROM students_backup
);

-- 使用LIMIT限制删除数量
DELETE FROM students 
WHERE score < 70 
LIMIT 10;  -- 最多删除10条

-- 按顺序删除
DELETE FROM students 
ORDER BY score ASC 
LIMIT 5;  -- 删除分数最低的5条

多表删除

-- 创建关联数据
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_name VARCHAR(100)
);

INSERT INTO orders (user_id, amount) VALUES (1, 100), (2, 200);
INSERT INTO order_items (order_id, product_name) VALUES (1, '商品A'), (1, '商品B');

-- 多表删除
DELETE o, oi
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 1;

-- 使用LEFT JOIN删除
DELETE o
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE oi.id IS NULL;  -- 删除没有订单项的订单

TRUNCATE vs DELETE

/*
 * TRUNCATE vs DELETE：
 * 
 * DELETE：
 * - 可以带WHERE条件
 * - 逐行删除，记录日志
 * - 不会重置AUTO_INCREMENT
 * - 可以回滚（在事务中）
 * - 触发器会被触发
 * 
 * TRUNCATE：
 * - 删除所有数据
 * - 整表删除，不记录日志
 * - 重置AUTO_INCREMENT
 * - 不能回滚
 * - 触发器不会被触发
 * - 执行更快
 */

-- 使用DELETE
DELETE FROM students;

-- 使用TRUNCATE
TRUNCATE TABLE students;

-- TRUNCATE后自增ID重置
INSERT INTO students (name, age, gender, score)
VALUES ('新学生', 20, '男', 90);

SELECT * FROM students;  -- id从1开始

实际应用示例

-- 创建完整的示例数据库
CREATE DATABASE IF NOT EXISTS shop;
USE shop;

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    status TINYINT DEFAULT 1 COMMENT '1正常 0禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category_id INT,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) DEFAULT 0,
    status TINYINT DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单明细表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL
);

-- 插入测试数据
INSERT INTO users (username, password, email, phone) VALUES
('user1', 'password123', 'user1@test.com', '13800000001'),
('user2', 'password123', 'user2@test.com', '13800000002'),
('user3', 'password123', 'user3@test.com', '13800000003');

INSERT INTO products (name, price, stock) VALUES
('iPhone 15', 7999.00, 100),
('MacBook Pro', 14999.00, 50),
('AirPods', 1299.00, 200);

-- 创建订单
INSERT INTO orders (order_no, user_id, total_amount) VALUES
('ORD001', 1, 9298.00),
('ORD002', 2, 14999.00);

-- 添加订单明细
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 7999.00),
(1, 3, 1, 1299.00),
(2, 2, 1, 14999.00);

-- 更新订单状态
UPDATE orders SET status = 1 WHERE order_no = 'ORD001';

-- 更新库存
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- 查询订单详情
SELECT 
    o.order_no,
    u.username,
    p.name AS product_name,
    oi.quantity,
    oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

本章小结

本章学习了：

• 插入数据：INSERT、INSERT IGNORE、REPLACE
• 批量插入：多值插入、INSERT...SELECT
• 更新数据：UPDATE、多表更新、条件更新
• 删除数据：DELETE、TRUNCATE、多表删除
• 安全操作：使用事务、先查询再操作

下一章，我们将学习查询进阶，了解更复杂的查询技巧。