第 6 章 · 数据的增删改

-- 用户 1 收藏作品 5
INSERT INTO `favorite` (`user_id`, `article_id`) VALUES (1, 5);

-- 插完必须查一眼，确认真的进去了
SELECT id, user_id, article_id FROM `favorite` ORDER BY id;

-- 演示完删掉，恢复种子的 3 条收藏
DELETE FROM `favorite` WHERE `user_id` = 1 AND `article_id` = 5;

-- 一次给点赞表补两条
INSERT INTO `user_like` (`user_id`, `target_type`, `target_id`) VALUES
  (1, 'comment', 1),
  (1, 'article', 3);

SELECT id, user_id, target_type, target_id FROM `user_like` ORDER BY id;

-- 演示完删掉，恢复种子的 3 条点赞
DELETE FROM `user_like`
WHERE `user_id` = 1
  AND (`target_type`, `target_id`) IN (('comment', 1), ('article', 3));

-- ① 建一张结构一致的空表
CREATE TABLE `article_hot` (
  `id`    INT UNSIGNED NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `views` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='热门作品备份表';

-- ② 把查询结果整批插入
INSERT INTO `article_hot` (`id`, `title`, `views`)
SELECT `id`, `title`, `views` FROM `article` WHERE `views` >= 300;

-- ③ 验证
SELECT * FROM `article_hot` ORDER BY `id`;

START TRANSACTION;

-- 只改 id = 1 这一行
UPDATE `article` SET `views` = `views` + 1 WHERE `id` = 1;

-- 改完立刻查，确认只动了这一行
SELECT id, title, views FROM `article` WHERE `id` = 1;

ROLLBACK; -- 演示完还原（原值 328）

-- ① 插入：用户 1 收藏作品 6《陋室铭》
INSERT INTO `favorite` (`user_id`, `article_id`) VALUES (1, 6);
SELECT id, user_id, article_id FROM `favorite` ORDER BY id;

-- ② 取消收藏：删掉刚才那条
DELETE FROM `favorite` WHERE `user_id` = 1 AND `article_id` = 6;
SELECT id, user_id, article_id FROM `favorite` ORDER BY id;

SELECT title, views,
       CASE WHEN views >= 400 THEN '热门'
            WHEN views >= 250 THEN '一般'
            ELSE '冷门' END AS 热度
FROM `article`
ORDER BY views DESC;

START TRANSACTION;

UPDATE `article`
SET `is_recommend` = CASE WHEN `views` >= 300 THEN 1 ELSE 0 END;

SELECT id, title, views, is_recommend FROM `article` ORDER BY id;

ROLLBACK; -- 演示完还原为种子值：1, 1, 1, 0, 0, 0

START TRANSACTION;

-- 把用户 1 收藏的作品从 2 改成 4
UPDATE `favorite` SET `article_id` = 4 WHERE `user_id` = 1 AND `article_id` = 2;
SELECT id, user_id, article_id FROM `favorite` ORDER BY id;

-- 一看改错了（想改的其实是另一条）→ 撤销
ROLLBACK;

-- 如果确认无误，就改成提交：
-- COMMIT;

START TRANSACTION;

-- 插入一篇新作品：id 自增，其余字段按业务填写
INSERT INTO `article`
  (`title`, `author`, `dynasty`, `category_id`, `summary`, `views`, `likes`, `is_recommend`, `status`)
VALUES
  ('望庐山瀑布', '李白', '唐', 1, '日照香炉生紫烟，遥看瀑布挂前川。', 0, 0, 0, 1);

-- 用「id 倒序取第一条」验证刚插入的行
SELECT id, title, author, dynasty, category_id, status
FROM `article` ORDER BY id DESC LIMIT 1;

ROLLBACK; -- 真实上线时改成 COMMIT;