游戏管理系统数据库设计与操作

一、数据库设计

-- 创建游戏管理系统数据库
CREATE DATABASE game_management_db;
USE game_management_db;

-- 创建游戏表（主表）
CREATE TABLE games (
  game_id INT PRIMARY KEY,  -- 游戏编号，主键
  game_name VARCHAR(100) NOT NULL,  -- 游戏名称，非空约束
  developer VARCHAR(50) NOT NULL,  -- 开发商，非空约束
  release_year YEAR,  -- 发布年份
  genre VARCHAR(30),  -- 游戏类型
  copies_in_stock INT DEFAULT 0  -- 库存数量，默认值0
);

-- 创建玩家表（主表）
CREATE TABLE players (
  player_id INT PRIMARY KEY,  -- 玩家编号，主键
  player_name VARCHAR(50) NOT NULL,  -- 玩家姓名，非空约束
  gender CHAR(2),  -- 性别
  registration_date DATE,  -- 注册日期
  contact_number VARCHAR(20) UNIQUE  -- 联系方式，唯一约束
);

-- 创建租借表（从表，关联游戏和玩家）
CREATE TABLE rental_records (
  record_id INT PRIMARY KEY,  -- 租借记录编号，主键
  game_id INT,  -- 游戏编号，外键
  player_id INT,  -- 玩家编号，外键
  rental_date DATE,  -- 租借日期
  return_date DATE,
  -- 设置外键约束，关联游戏表
  FOREIGN KEY (game_id) REFERENCES games(game_id),
  -- 设置外键约束，关联玩家表
  FOREIGN KEY (player_id) REFERENCES players(player_id)
);

! 注意：创建包含外键的表时，必须先创建被关联的主表，否则会出现 "表不存在" 的错误

二、操作实例

1. 插入数据

-- 向游戏表插入数据
INSERT INTO games (game_id, game_name, developer, release_year, genre, copies_in_stock)
VALUES
(1, '塞尔达传说：王国之泪', '任天堂', 2023, '冒险', 15),
(2, '英雄联盟', 'Riot Games', 2009, 'MOBA', 20),
(3, '只狼：影逝二度', 'FromSoftware', 2019, '动作', 12),
(4, '动物森友会', '任天堂', 2020, '模拟', 8);

-- 向玩家表插入数据
INSERT INTO players (player_id, player_name, gender, registration_date, contact_number)
VALUES
(101, '小明', '男', '2022-01-15', '13812345678'),
(102, '小红', '女', '2022-03-20', '13987654321'),
(103, '小刚', '男', '2022-05-10', '13711223344');

-- 向租借表插入数据
INSERT INTO rental_records (record_id, game_id, player_id, rental_date, return_date)
VALUES
(1001, 1, 101, '2023-06-01', NULL),  -- 未归还
(1002, 3, 102, '2023-06-05', '2023-06-20'),  -- 已归还
(1003, 2, 101, '2023-06-10', NULL);  -- 未归还

! 常见错误：插入外键数据时，如果关联的主键不存在，会出现 "外键约束失败" 错误。例如向租借表插入 game_id=100 的数据会失败，因为游戏表中没有这个编号。

2. 查询数据

-- 1. 查询所有游戏信息
SELECT * FROM games;

-- 2. 查询库存大于5的游戏
SELECT game_id, game_name, copies_in_stock
FROM games
WHERE copies_in_stock > 5;

-- 3. 查询冒险类的游戏
SELECT game_name, developer
FROM games
WHERE genre = '冒险';

-- 4. 查询未归还的租借记录
SELECT *
FROM rental_records
WHERE return_date IS NULL;

-- 5. 查询姓名为小明的玩家信息
SELECT *
FROM players
WHERE player_name = '小明';

! 注意：判断字段是否为 NULL 时，必须使用 IS NULL 或 IS NOT NULL，不能使用 = 或！=

3. 更新数据

-- 1. 更新游戏库存
UPDATE games
SET copies_in_stock = 18
WHERE game_id = 1;

-- 2. 修改玩家联系方式
UPDATE players
SET contact_number = '13899998888'
WHERE player_id = 103;

-- 3. 标记游戏归还
UPDATE rental_records
SET return_date = '2023-07-01'
WHERE record_id = 1001;

! 重要提示：执行 UPDATE 语句时必须加上 WHERE 条件，否则会更新表中所有记录，建议操作前先备份数据

4. 删除数据

-- 1. 删除特定租借记录
DELETE FROM rental_records
WHERE record_id = 1003;

-- 2. 删除特定玩家（注意：如果该玩家有租借记录，会删除失败）
DELETE FROM players
WHERE player_id = 103;

! 常见错误：如果要删除的主表记录被从表引用（有外键关联），会删除失败。需要先删除关联的从表记录，才能删除主表记录。

三、约束分析

1. 主键约束

每个表只能有一个主键
主键值不能重复，也不能为 NULL
例如：不能向 games 表插入 game_id 相同的两条记录

2. 外键约束

保证数据关联性和一致性
从表外键的值必须来自主表对应的主键
例如：rental_records 表的 game_id 必须是 games 表中存在的 game_id

3. 非空约束

确保字段必须有值
例如：向 games 表插入数据时，game_name 和 developer 不能为空

4. 唯一约束

确保字段值不重复
例如：players 表的 contact_number 字段，不能有两个玩家使用相同的联系方式

四、主键外键不对应后果分析

在游戏管理系统数据库中，若主键与外键不对应，会产生一系列严重后果：

数据完整性破坏：外键值无对应主键时，从表数据失去关联对象（如租借表 game_id 在游戏表中不存在），导致数据逻辑关联断裂。
操作执行错误：主键外键不匹配会导致插入失败；更新主表主键未同步从表会造成关联错误；删除有外键关联的主表记录会失败或产生孤立数据。
业务逻辑混乱：主键外键不对应会导致业务计算错误（如游戏租借量统计失准），影响管理决策。
数据查询异常：表关联查询时，主键外键不匹配会使 JOIN 操作返回错误结果（如玩家与错误游戏记录关联），影响查询功能。

五、简单表关联查询

-- 查询玩家租借的游戏信息
SELECT
  p.player_name,
  g.game_name,
  r.rental_date
FROM rental_records r
JOIN games g ON r.game_id = g.game_id
JOIN players p ON r.player_id = p.player_id;

! 注意：JOIN 操作需要指定关联条件，否则会产生笛卡尔积，导致查询结果不正确