《诗词飞花令游戏数据库搭建与实践》

1）创建名称为PoemGameDB的数据库，默认字符集设置为utf8mb4.

CREATE DATABASE PoemGameDB

CHARACTER SET utf8mb4

COLLATE utf8mb4_unicode_ci;

（2）根据诗词飞花令游戏数据库的物理模型，在PoemGameDB数据库中添加诗词表（poem）、诗人表（poet）、飞花令表（feihualing）、诗词飞花令关联表（poemling）、诗词类别表（poemType）、诗词分类表（poemIndex）。

USE PoemGameDB;

 

CREATE TABLE poem (

   id INT AUTO_INCREMENT PRIMARY KEY,

   title VARCHAR(255) NOT NULL,

   content TEXT NOT NULL,

   pmHot INT DEFAULT 0 CHECK (pmHot >= 0),

   poet_id INT

);

 

CREATE TABLE poet (

   id INT AUTO_INCREMENT PRIMARY KEY,

   name VARCHAR(255) NOT NULL,

   pHot INT DEFAULT 0 CHECK (pHot >= 0)

);

 

CREATE TABLE feihualing (

   id INT AUTO_INCREMENT PRIMARY KEY,

   keyword VARCHAR(255) NOT NULL

);

 

CREATE TABLE poemling (

   poem_id INT,

   feihualing_id INT,

   PRIMARY KEY (poem_id, feihualing_id),

   FOREIGN KEY (poem_id) REFERENCES poem(id),

   FOREIGN KEY (feihualing_id) REFERENCES feihualing(id)

);

 

CREATE TABLE poemType (

   id INT AUTO_INCREMENT PRIMARY KEY,

   type_name VARCHAR(255) NOT NULL

);

 

CREATE TABLE poemIndex (

   poem_id INT,

   poemType_id INT,

   PRIMARY KEY (poem_id, poemType_id),

   FOREIGN KEY (poem_id) REFERENCES poem(id),

   FOREIGN KEY (poemType_id) REFERENCES poemType(id)

);

（3）根据飞花令游戏的数据库设计，为PoemGameDB数据库中数据表添加如下约束。

● 根据物理模型，为poem、poemling、poemIndex表添加相应的外键。

● 为poem表中的pmHot字段、poet中的pHot字段添加DEFAULT约束，默认值为0。

● 为poem表中的pmHot字段、poet中的pHot字段添加约束，不允许负值（小于零的值）出现。

（4）向poem表中添加一首你喜欢的诗词，并完善诗词相应信息。若该诗词的作者并未记录在本数据库中，同时向poet表中添加诗人的相应数据。

INSERT INTO poet (name) VALUES ('李白');

-- 获取诗人ID

SET @poet_id = LAST_INSERT_ID();

-- 添加诗词

INSERT INTO poem (title, content, poet_id) VALUES ('静夜思', '床前明月光，疑是地上霜。举头望明月，低头思故乡。', @poet_id);

（5）向poemling表及poemIndex表中添加相应的数据，为步骤（4）中所添加的诗词完成分类及飞花令关联信息。

SET @feihualing_id = (SELECT id FROM feihualing WHERE keyword = '月'); SET@poemType_id = (SELECT id FROM poemType WHERE type_name = '唐诗');

-- 获取诗词ID

SET @poem_id = (SELECT id FROM poem WHERE title = '静夜思');

-- 添加飞花令关联

INSERT INTO poemling (poem_id, feihualing_id) VALUES (@poem_id,@feihualing_id);

-- 添加诗词分类

INSERT INTO poemIndex (poem_id, poemType_id) VALUES (@poem_id,@poemType_id);

（6）修改步骤（4）中在poem表中所添加诗词的诗词热度，将热度之改为10，同时该诗词作者的热度相应增加10。

UPDATE `poem` SET `pmHot`=10 WHERE `pmID`=1;

UPDATE `poet` SET `pHot`=`pHot`+10 WHERE `pID`=1；

（7）删除步骤（4）中在poem表中所添加的数据，同时将poemling表及poemIndex表中相应的数据删除。

DELETE FROM `poemling` WHERE `pmID`=1;

DELETE FROM `poemIndex` WHERE `pmID`=1;

DELETE FROM `poem` WHERE `pmID`=1;