SQL-DML与数据修改

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

-- 插入一条新记录
INSERT INTO students (class_id, name, gender, score) 
VALUES (2, '大牛', 'M', 80);

-- 查询验证
SELECT * FROM students;

-- 一次性插入多条记录
INSERT INTO students (class_id, name, gender, score) VALUES
    (1, '大宝', 'M', 87),
    (2, '二宝', 'M', 81),
    (3, '三宝', 'F', 92);

-- 从另一个表插入数据
INSERT INTO students_backup (name, gender, score)
SELECT name, gender, score FROM students WHERE score > 80;

-- 创建表并插入数据
CREATE TABLE students_top AS 
SELECT * FROM students WHERE score >= 90;

-- 如果主键冲突则忽略
INSERT IGNORE INTO students (id, name, gender, score) 
VALUES (1, '新名字', 'M', 100);

-- 如果 id=1 存在则更新，不存在则插入
INSERT INTO students (id, name, gender, score) 
VALUES (1, '大牛', 'M', 95)
ON DUPLICATE KEY UPDATE 
    name = '大牛', 
    score = 95;

-- 如果主键存在则删除后插入，不存在则直接插入
REPLACE INTO students (id, name, gender, score) 
VALUES (1, '大牛', 'M', 95);

UPDATE 表名 SET 字段1=值1, 字段2=值2, ... WHERE 条件;

-- 更新 id=1 的记录
UPDATE students SET name = '大牛', score = 66 WHERE id = 1;

-- 查询验证
SELECT * FROM students WHERE id = 1;

-- 更新 id 在 5-7 之间的记录
UPDATE students SET name = '小牛', score = 77 
WHERE id >= 5 AND id <= 7;

-- 更新所有分数低于 60 的记录
UPDATE students SET score = 60 WHERE score < 60;

-- 所有分数加 10 分
UPDATE students SET score = score + 10 WHERE score < 80;

-- 根据条件更新不同值
UPDATE students SET 
    score = CASE 
        WHEN score < 60 THEN 60 
        WHEN score > 95 THEN 95 
        ELSE score 
    END;

-- 关联更新
UPDATE students s
JOIN classes c ON s.class_id = c.id
SET s.class_name = c.name
WHERE c.name = '一班';

mysql> UPDATE students SET name = '大宝' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE students SET name = '大宝' WHERE id = 999;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

-- 危险！没有 WHERE 条件会更新所有记录
UPDATE students SET score = 60;

-- 安全做法：先用 SELECT 验证
SELECT * FROM students WHERE score < 60;
-- 确认无误后再执行 UPDATE
UPDATE students SET score = 60 WHERE score < 60;

DELETE FROM 表名 WHERE 条件;

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

-- 删除 id 在 5-7 之间的记录
DELETE FROM students WHERE id >= 5 AND id <= 7;

-- 删除所有分数低于 60 的记录
DELETE FROM students WHERE score < 60;

-- 危险！删除所有记录（保留表结构）
DELETE FROM students;

-- 更高效的方式：TRUNCATE（重置自增ID）
TRUNCATE TABLE students;

-- 关联删除
DELETE s FROM students s
JOIN classes c ON s.class_id = c.id
WHERE c.name = '已删除班级';

-- 删除多个表的数据
DELETE s, c FROM students s
JOIN classes c ON s.class_id = c.id
WHERE c.id = 999;

mysql> DELETE FROM students WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM students WHERE id = 999;
Query OK, 0 rows affected (0.01 sec)

-- 先查询确认
SELECT * FROM students WHERE score < 60;

-- 确认无误后再执行
DELETE FROM students WHERE score < 60;

BEGIN;
DELETE FROM students WHERE score < 60;
-- 检查结果
SELECT * FROM students;
-- 确认无误后提交
COMMIT;
-- 或者回滚
ROLLBACK;

-- 只删除前 10 条符合条件的记录
DELETE FROM students WHERE score < 60 LIMIT 10;

-- 只更新前 100 条记录
UPDATE students SET score = 60 WHERE score < 60 LIMIT 100;

-- 删除前备份
CREATE TABLE students_backup AS SELECT * FROM students WHERE score < 60;

-- 确认备份后再删除
DELETE FROM students WHERE score < 60;

-- 批量更新学生班级
UPDATE students SET class_id = 2 WHERE id IN (1, 2, 3, 4, 5);

-- 只插入不存在的记录
INSERT INTO students (name, gender, score)
SELECT '新学生', 'M', 80
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM students WHERE name = '新学生'
);

-- 更新学生成绩，同时更新班级平均分
BEGIN;
UPDATE students SET score = 95 WHERE id = 1;
UPDATE classes SET avg_score = (
    SELECT AVG(score) FROM students WHERE class_id = 1
) WHERE id = 1;
COMMIT;