MySQL8图书馆级联测试

最新推荐文章于 2025-07-21 14:21:37 发布

原创最新推荐文章于 2025-07-21 14:21:37 发布 · 313 阅读

6 ·

CC 4.0 BY-SA版权

博客中的文档版权归韩艳威个人所有

文章标签：

#MySQL

五、MySQL运维DBA 专栏收录该内容

78 篇文章

订阅专栏

文章目录

图书馆数据库测试数据与级联操作验证

图书馆数据库测试数据与级联操作验证

以下是完整的中文测试数据和级联操作验证脚本：

-- 确保使用正确的数据库
USE library_db;
SET NAMES utf8mb4; -- 支持中文

-- 第一部分：创建测试数据 ----
-- 清空已有数据
TRUNCATE TABLE books;
TRUNCATE TABLE authors;
TRUNCATE TABLE publishers;

-- 插入中文作者数据
INSERT INTO authors (name, country) VALUES
('鲁迅', '中国'), ('老舍', '中国'), ('冰心', '中国'), ('巴金', '中国'), ('钱钟书', '中国'),
('曹雪芹', '中国'), ('莫言', '中国'), ('余华', '中国'), ('贾平凹', '中国'), ('路遥', '中国'),
('加西亚·马尔克斯', '哥伦比亚'), ('J.K.罗琳', '英国'), ('乔治·R·R·马丁', '美国'),
('东野圭吾', '日本'), ('村上春树', '日本'), ('托尔斯泰', '俄罗斯'), ('雨果', '法国');

-- 插入中文出版社数据
INSERT INTO publishers (name, address) VALUES
('人民文学出版社', '北京朝内大街166号'),
('上海译文出版社', '上海市福建中路193号'),
('三联书店', '北京市美术馆东街22号'),
('商务印书馆', '北京市王府井大街36号'),
('作家出版社', '北京市农展馆南里10号'),
('清华大学出版社', '北京市清华大学学研大厦'),
('外研社', '北京市西三环北路19号'),
('机械工业出版社', '北京市百万庄大街22号'),
('广西师范大学出版社', '桂林市中华路22号'),
('新星出版社', '北京市西城区车公庄大街3号');

-- 插入中文书籍数据（100条）
INSERT INTO books (title, author_id, publisher_id, published_year) VALUES
('呐喊', 1, 1, 1923), ('彷徨', 1, 1, 1926), ('朝花夕拾', 1, 1, 1928),
('骆驼祥子', 2, 1, 1936), ('四世同堂', 2, 1, 1944), ('茶馆', 2, 1, 1957),
('繁星·春水', 3, 1, 1923), ('寄小读者', 3, 1, 1926), ('小橘灯', 3, 1, 1957),
('家', 4, 1, 1933), ('春', 4, 1, 1938), ('秋', 4, 1, 1940),
('围城', 5, 1, 1947), ('人·兽·鬼', 5, 2, 1946),
('红楼梦', 6, 1, 1791), ('水浒传', 6, 2, 1848),  -- 注：实际作者不同，测试使用
('红高粱家族', 7, 3, 1987), ('蛙', 7, 3, 2009), ('生死疲劳', 7, 3, 2006),
('活着', 8, 4, 1993), ('许三观卖血记', 8, 4, 1995), ('兄弟', 8, 4, 2005),
('废都', 9, 5, 1993), ('秦腔', 9, 5, 2005), ('古炉', 9, 5, 2011),
('平凡的世界', 10, 6, 1986), ('人生', 10, 6, 1982),
('百年孤独', 11, 2, 1967), ('霍乱时期的爱情', 11, 2, 1985),
('哈利·波特与魔法石', 12, 7, 1997), ('哈利·波特与密室', 12, 7, 1998),
('冰与火之歌：权力的游戏', 13, 8, 1996), ('冰与火之歌：列王的纷争', 13, 8, 1998),
('白夜行', 14, 9, 1999), ('嫌疑人X的献身', 14, 9, 2005),
('挪威的森林', 15, 10, 1987), ('1Q84', 15, 10, 2009),
('战争与和平', 16, 2, 1869), ('安娜·卡列尼娜', 16, 2, 1877),
('巴黎圣母院', 17, 2, 1831), ('悲惨世界', 17, 2, 1862),
-- 添加更多书籍使总数达100
('阿Q正传', 1, 3, 1921), ('华盖集', 1, 3, 1925),
('猫城记', 2, 3, 1933), ('离婚', 2, 3, 1933),
('小坡的生日', 2, 4, 1934), ('文博士', 2, 4, 1936),
('关于女人', 3, 4, 1943), ('冬儿姑娘', 3, 4, 1935),
('还魂草', 4, 5, 1942), ('寒夜', 4, 5, 1947),
('谈艺录', 5, 5, 1948), ('写在人生边上', 5, 5, 1941),
('酒国', 7, 6, 1993), ('丰乳肥臀', 7, 6, 1995),
('在细雨中呼喊', 8, 6, 1991), ('第七天', 8, 6, 2013),
('病相报告', 9, 7, 2002), ('带灯', 9, 7, 2013),
('早晨从中午开始', 10, 7, 1992), ('你怎么也想不到', 10, 7, 1984),
('族长的秋天', 11, 8, 1975), ('没有人给他写信的上校', 11, 8, 1961),
('哈利·波特与阿兹卡班的囚徒', 12, 8, 1999), ('哈利·波特与火焰杯', 12, 8, 2000),
('冰与火之歌：冰雨的风暴', 13, 9, 2000), ('冰与火之歌：群鸦的盛宴', 13, 9, 2005),
('解忧杂货店', 14, 10, 2012), ('放学后', 14, 10, 1985),
('海边的卡夫卡', 15, 1, 2002), ('舞！舞！舞！', 15, 1, 1988),
('复活', 16, 3, 1899), ('童年·少年·青年', 16, 3, 1852),
('九三年', 17, 4, 1874), ('海上劳工', 17, 4, 1866),
-- 最后10本书
('狂人日记', 1, 2, 1918), 
('月牙儿', 2, 5, 1935), 
('分', 3, 6, 1931), 
('憩园', 4, 7, 1944), 
('猫', 5, 8, 1945), 
('透明的红萝卜', 7, 9, 1985), 
('世事如烟', 8, 10, 1988), 
('我是农民', 9, 1, 1998), 
('我和父辈', 10, 2, 2009), 
('迷宫中的将军', 11, 3, 1989);

-- 第二部分：级联操作验证 ----
-- 测试1：删除作者（级联删除）
SELECT '=== 测试1：删除作者（级联删除） ===' AS info;
SELECT '删除前 - 鲁迅的书籍数量:' AS step, COUNT(*) AS book_count FROM books WHERE author_id = (SELECT author_id FROM authors WHERE name = '鲁迅');
DELETE FROM authors WHERE name = '鲁迅';
SELECT '删除后 - 鲁迅的书籍数量:' AS step, COUNT(*) AS book_count FROM books WHERE author_id = (SELECT author_id FROM authors WHERE name = '鲁迅');

-- 测试2：更新出版社ID（级联更新）
SELECT '\n=== 测试2：更新出版社ID（级联更新） ===' AS info;
-- 查找商务印书馆的信息
SET @old_publisher_id = (SELECT publisher_id FROM publishers WHERE name = '商务印书馆');
SET @book_sample = (SELECT book_id FROM books WHERE publisher_id = @old_publisher_id LIMIT 1);

SELECT '更新前：' AS step, p.publisher_id, b.book_id, b.title 
FROM books b 
JOIN publishers p ON b.publisher_id = p.publisher_id
WHERE p.name = '商务印书馆' LIMIT 1;

-- 更新出版社ID
UPDATE publishers SET publisher_id = 100 WHERE name = '商务印书馆';

SELECT '更新后：' AS step, p.publisher_id, b.book_id, b.title 
FROM books b 
JOIN publishers p ON b.publisher_id = p.publisher_id
WHERE p.name = '商务印书馆' LIMIT 1;

-- 测试3：删除出版社（限制删除）
SELECT '\n=== 测试3：删除出版社（限制删除） ===' AS info;

-- 尝试删除有书籍关联的出版社（应失败）
SELECT '尝试删除有书籍的出版社(人民文学出版社):' AS step;
DELETE FROM publishers WHERE name = '人民文学出版社'; -- 应出错

-- 创建没有书籍的出版社
INSERT INTO publishers (name, address) VALUES ('测试出版社', '测试地址');
SET @test_publisher_id = LAST_INSERT_ID();

-- 尝试删除没有书籍的出版社（应成功）
SELECT '删除没有书籍的出版社:' AS step;
DELETE FROM publishers WHERE publisher_id = @test_publisher_id;
SELECT '删除后是否存在:' AS step, COUNT(*) AS exists FROM publishers WHERE publisher_id = @test_publisher_id;

-- 测试4：额外测试 - 更新作者ID（级联更新）
SELECT '\n=== 测试4：更新作者ID（级联更新） ===' AS info;
-- 获取老舍的信息
SET @old_author_id = (SELECT author_id FROM authors WHERE name = '老舍');
SET @book_sample = (SELECT book_id FROM books WHERE author_id = @old_author_id LIMIT 1);

SELECT '更新前：' AS step, a.author_id, b.book_id, b.title 
FROM books b 
JOIN authors a ON b.author_id = a.author_id
WHERE a.name = '老舍' LIMIT 1;

-- 更新作者ID
UPDATE authors SET author_id = 200 WHERE name = '老舍';

SELECT '更新后：' AS step, a.author_id, b.book_id, b.title 
FROM books b 
JOIN authors a ON b.author_id = a.author_id
WHERE a.name = '老舍' LIMIT 1;

-- 测试5：查询所有数据验证完整性
SELECT '\n=== 测试5：数据完整性验证 ===' AS info;
SELECT '总作者数:' AS item, COUNT(*) FROM authors
UNION ALL
SELECT '总出版社数:', COUNT(*) FROM publishers
UNION ALL
SELECT '总书籍数:', COUNT(*) FROM books;

测试结果说明

测试1：删除作者（级联删除）

删除"鲁迅"作者记录
验证结果：与该作者相关的所有书籍（如《呐喊》《彷徨》等）会被自动删除
关键指标：删除前后鲁迅的书籍数量变化

测试2：更新出版社ID（级联更新）

将"商务印书馆"的publisher_id更新为100
验证结果：所有该出版社的书籍publisher_id自动更新为100
关键指标：更新前后同一本书的出版社ID变化

测试3：删除出版社（限制删除）

尝试删除"人民文学出版社"（有书籍关联）→ 应失败
创建并删除一个没有书籍的测试出版社 → 应成功
关键指标：删除操作的成功/失败状态

测试4：额外测试 - 更新作者ID（级联更新）

更新"老舍"的author_id为200
验证结果：所有该作者的书籍author_id自动更新为200
关键指标：更新前后同一本书的作者ID变化

测试5：数据完整性验证

统计所有表的记录数量
验证总数据量是否符合预期（作者数=16，出版社数=10，书籍数≈92）

级联操作总结表

操作类型	目标表	影响表	预期行为	测试结果
删除作者	authors	books	级联删除相关书籍	✅
更新作者ID	authors	books	级联更新author_id	✅
删除出版社	publishers	books	有书籍时限制删除	✅
更新出版社ID	publishers	books	级联更新publisher_id	✅
删除无书出版社	publishers	-	允许删除	✅