1、设计背景
设计博客信息数据库是为了有效地存储、管理和检索博客内容。
该博客信息管理数据库包含用户管理、文章管理、评论管理、分类管理及标签管理等系统功能。
本博客后台管理系统采用B/S模式,后台数据库选用MySQL数据库,阐述了博客后台管理中所应具有的基本功能、设计、实现。通过集中式的信息数据库,将各方面的工作有机地结合为一个整体,大大提高了信息管理的质量和水平,信息处理的及时性、有效性、高效性和方便性。
2、概念结构设计
数据库系统概念结构设计的目标是创建一个能够准确反映现实世界数据关系的模型,为后续的逻辑模型设计和物理模型设计提供基础。一个好的概念结构设计能够确保数据库系统的性能、数据一致性和可扩展性,是数据库设计成功的重要保障。
2.1、 博客信息管理系统的实体属性
用户实体 (users):
用户ID (user_id): 唯一标识用户,设置为自动递增的整数。
昵称(nickname): 用户在博客系统中的名字
用户名 (user_name): 用户在博客系统中的唯一标识。
密码 (user_password): 用户的登录密码。
电子邮箱 (email): 用户的电子邮箱地址。
注册时间 (register_time): 用户注册博客系统的日期和时间。
文章实体 (articles):
文章ID (article_id): 唯一标识文章,设置为自动递增的整数。
作者ID (user_id): 文章的作者ID,与用户实体关联。
标题 (title): 文章的标题。
内容 (article_content): 文章的正文内容。
浏览量 (page_view): 文章的阅读次数。
点赞量 (like_count): 文章获得的点赞次数。
评论数 (comment_count): 文章的评论数量。
发布日期 (article_time): 文章的发布日期和时间。
评论实体 (comments):
评论ID (comment_id): 唯一标识评论,设置为自动递增的整数。
评论用户ID (user_id): 评论的作者ID,与用户实体关联。
文章ID (article_id): 评论所属的文章ID。
评论内容 (comment_content): 评论的具体内容。
点赞量 (like_count): 评论获得的点赞次数。
评论日期 (comment_time): 评论的发布日期和时间。
分类实体 (classifications):
分类ID (classification_id): 唯一标识分类,设置为自动递增的整数。
分类名称 (classification_name): 分类的名称。
分类描述 (classification_description): 对该分类类型的描述。
文章数量 (article_count): 该类型文章的数量。
标签实体 (labels):
标签ID (label_id): 唯一标识标签,设置为自动递增的整数。
标签名称 (label_name): 标签的名称。
标签描述 (label_description): 对该标签的描述。
2.2、 实体之间的联系
“用户”实体与“文章”实体之间是一对多的联系(1:n),两者是发表关系;
“用户”实体与“评论”实体之间是一对多的联系(1:n),两者是发布关系;
“文章”实体与“评论”实体之间是一对多的联系(1:n),两者是评论关系;
“文章”实体与“标签”实体之间是多对多的联系(n:m),两者是设置关系;
“文章”实体与“分类”实体之间是多对多的联系(n:m),两者是设置关系。
3、E_R图设计
用户与文章的局部E-R图
用户与评论的局部E-R图
文章与评论的局部E-R图
文章与标签的局部E-R图
文章与分类的局部E-R图
全局E-R图
4、物理结构设计
属性名 | 含义 | 数据类型 | 约束 |
user_id | 用户ID | BIGINT(20) | 主键,非空 |
nickname | 用户昵称 | VARCHAR(20) | |
user_name | 用户名 | VARCHAR(20) | 候选键,非空 |
user_password | 用户密码 | VARCHAR(30) | 非空 |
| 用户邮箱 | VARCHAR(50) | |
register_time | 注册时间 | DATETIME |
属性名 | 含义 | 数据类型 | 约束 |
article_id | 文章ID | BIGINT(20) | 主键,非空 |
user_id | 作者ID | BIGINT(20) | 外键(users表),非空 |
title | 文章标题 | VARCHAR(20) | 候选键,非空 |
article_content | 文章内容 | TEXT | 非空 |
page_view | 浏览量 | BIGINT(20) | 非空 |
like_count | 点赞量 | BIGINT(20) | 非空 |
comment_count | 评论量 | BIGINT(20) | 非空 |
article_time | 发布时间 | DATETIME |
属性名 | 含义 | 数据类型 | 约束 |
comment_id | 评论ID | BIGINT(20) | 主键,非空 |
user_id | 评论用户ID | BIGINT(20) | 外键(users),非空 |
article_id | 评论文章ID | BIGINT(20) | 外键(articles)非空 |
comment_content | 评论内容 | TEXT | 非空 |
like_count | 点赞量 | BIGINT(20) | 非空 |
comment_time | 评论日期 | DATETIME |
属性名 | 含义 | 数据类型 | 约束 |
classification_id | 分类ID | INT(10) | 主键,非空 |
classification_name | 分类名称 | VARCHAR(20) | 候选键,非空 |
classification_description | 分类描述 | VARCHAR(100) | 非空 |
属性名 | 含义 | 数据类型 | 约束 |
label_id | 标签ID | INT(10) | 主键,非空 |
label_name | 标签名称 | VARCHAR(20) | 非空 |
label_description | 标签描述 | VARCHAR(100) | 非空 |
属性名 | 含义 | 数据类型 | 约束 |
administrator_id | 管理员ID | INT(10) | 主键,非空 |
administrator_name | 用户名 | VARCHAR(20) | 非空 |
administrator_password | 管理员密码 | VARCHAR(30) | 非空 |
属性名 | 含义 | 数据类型 | 约束 |
article_id | 文章ID | BIGINT(20) | 外键(articles),非空 |
classification_id | 分类ID | INT(10) | 外键(classification),非空 |
属性名 | 含义 | 数据类型 | 约束 |
article_id | 文章ID | BIGINT(20) | 外键(articles),非空 |
label_id | 标签ID | INT(10) | 外键(labels),非空 |
5、建表代码示例
CREATE TABLE users (
user_id BIGINT(20) AUTO_INCREMENT NOT NULL COMMENT '用户ID',
nickname VARCHAR(20) DEFAULT NULL COMMENT '用户昵称',
user_name VARCHAR(20) NOT NULL COMMENT '用户名',
user_password VARCHAR(30) NOT NULL COMMENT '用户密码',
email VARCHAR(50) COMMENT '用户邮箱',
register_time DATE DEFAULT NULL COMMENT '注册时间',
PRIMARY KEY (user_id),
KEY user_name (user_name)
);
CREATE TABLE articles (
article_id BIGINT(20) AUTO_INCREMENT NOT NULL COMMENT '文章ID',
user_id BIGINT(20) NOT NULL COMMENT '作者ID',
title VARCHAR(20) NOT NULL COMMENT '文章标题',
article_content TEXT NOT NULL COMMENT '文章内容',
page_view BIGINT(20) NOT NULL COMMENT '浏览量',
like_count BIGINT(20) NOT NULL COMMENT '点赞量',
comment_count BIGINT(20) NOT NULL COMMENT '评论量',
article_time DATE DEFAULT NULL COMMENT '发布时间',
PRIMARY KEY (article_id),
KEY title (title),
FOREIGN KEY(user_id) REFERENCES users(user_id)
ON UPDATE CASCADE -- 级联更新
ON DELETE CASCADE -- 级联删除
);
CREATE TABLE comments (
comment_id BIGINT(20) AUTO_INCREMENT NOT NULL COMMENT '评论ID',
user_id BIGINT(20) NOT NULL COMMENT '评论用户ID',
article_id BIGINT(20) NOT NULL COMMENT '评论文章ID',
comment_content TEXT NOT NULL COMMENT '评论内容',
like_count BIGINT(20) NOT NULL COMMENT '点赞量',
comment_time DATE DEFAULT NULL COMMENT '评论日期',
PRIMARY KEY (comment_id),
FOREIGN KEY(user_id) REFERENCES users(user_id)
ON UPDATE CASCADE -- 级联更新
ON DELETE CASCADE, -- 级联删除
FOREIGN KEY(article_id) REFERENCES articles(article_id)
ON UPDATE CASCADE -- 级联更新
ON DELETE CASCADE -- 级联删除
);
CREATE TABLE classifications (
classification_id INT(10) AUTO_INCREMENT NOT NULL COMMENT '分类ID',
classification_name VARCHAR(20) NOT NULL COMMENT '分类名称',
classification_description VARCHAR(100) NOT NULL COMMENT '分类描述',
-- article_count INT(10) DEFAULT NULL COMMENT '文章数量',
PRIMARY KEY (classification_id),
KEY classification_name (classification_name)
);
CREATE TABLE labels (
label_id INT(10) AUTO_INCREMENT NOT NULL COMMENT '标签ID',
label_name VARCHAR(20) NOT NULL COMMENT '标签名称',
label_description VARCHAR(100) NOT NULL COMMENT '标签描述',
PRIMARY KEY (label_id)
);
CREATE TABLE administrators (
administrator_id INT(10) AUTO_INCREMENT NOT NULL COMMENT '管理员ID',
administrator_name VARCHAR(20) NOT NULL COMMENT '管理员用户名',
administrator_password VARCHAR(30) NOT NULL COMMENT '管理员密码',
PRIMARY KEY (administrator_id)
);
CREATE TABLE article_classification (
article_id BIGINT(20) NOT NULL COMMENT '文章ID',
classification_id INT(10) NOT NULL COMMENT '分类ID',
PRIMARY KEY (article_id, classification_id),
FOREIGN KEY(article_id) REFERENCES articles(article_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY(classification_id) REFERENCES classifications(classification_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
CREATE TABLE article_label (
article_id BIGINT(20) NOT NULL COMMENT '文章ID',
label_id INT(10) NOT NULL COMMENT '标签ID',
PRIMARY KEY (article_id,label_id),
FOREIGN KEY(article_id) REFERENCES articles(article_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY(label_id) REFERENCES labels(label_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
6、模拟数据测试
INSERT INTO users VALUES (20220001,'随风','admin1','123456','','2000-01-01');
INSERT INTO users VALUES (20220002,'若希','admin2','123456','','2002-09-01');
INSERT INTO users VALUES (20220003,'留白','admin3','123456','','2013-11-20');
INSERT INTO users VALUES (20220004,'夏凌','admin4','123456','','2007-02-20');
INSERT INTO users VALUES (20220005,'向阳','admin5','123456','','2000-06-06');
INSERT INTO users VALUES (20220006,'希飒','admin6','123456','','2000-08-08');
INSERT INTO users VALUES (20220007,'渝中','admin7','123456','','2000-07-27');
INSERT INTO users VALUES (20220008,'心晴','admin8','123456','','2000-06-28');
INSERT INTO users VALUES (20220009,'青丝','admin9','123456','','2000-12-12');
INSERT INTO users VALUES (20220010,'佳佳','admin10','123456','','2000-01-19');
INSERT INTO articles VALUES (1,20220010,'朝花夕拾','人生就像一座山,重要的不是它的高低,而在于灵秀;人生就像一场雨,重要的不是它的大小,而在于及时。',8642,465,0,'2009-06-17');
INSERT INTO articles VALUES (2,20220009,'学习','别站在原地,请保持努力',18642,5465,0,'2009-05-28');
INSERT INTO articles VALUES (3,20220005,'人生','放弃等同于失败',5642,2465,0,'2009-06-17');
INSERT INTO articles VALUES (4,20220002,'自由','且听风吟,随心而动',55642,8465,0,'2005-09-29');
INSERT INTO articles VALUES (5,20220008,'狂人日记','也许人类的悲哀便在于此,拥有的东西不去珍惜,对于得不到的却永远渴望。',59642,14465,0,'2004-04-29');
INSERT INTO articles VALUES (6,20220003,'生命_沈从文','山谷中应当有白中微带浅蓝色的百合花,弱颈长蒂,无语如语,香清而淡,躯干秀拔。花粉作黄色,小叶如翠珰。',7467,8465,0,'2012-07-29');
INSERT INTO articles VALUES (7,20220007,'吞噬星空','罗峰带着界兽摩罗撒闯过轮回,来到了起源大陆……',15382,9465,7552,'2008-04-29');
INSERT INTO articles VALUES (8,20220005,'诗经·采薇','昔我往矣,杨柳依依;今我来思,雨雪霏霏',35492,6465,0,'2006-11-29');
INSERT INTO articles VALUES (9,20220003,'自由','且行且看且随风,且做且看且从容',49687,5465,0,'2003-07-19');
INSERT INTO articles VALUES (10,20220004,'水调歌头','明月几时有,把酒问青天,不知天上宫阙,今夕是何年',89662,18955,0,'2011-10-21');
INSERT INTO articles VALUES (11,20220001,'空间维度','世界上真的存在四维空间吗...',53692,26465,0,'2004-08-26');
INSERT INTO articles VALUES (12,20220006,'未知领域探索','深海为什么比太空恐怖,深海环境极端(有大量的未知生物和事件)',65642,8965,0,'2005-12-11');
INSERT INTO comments VALUES (1,20220008,7,'文章写的不错!','8954','2008-12-31');
INSERT INTO comments VALUES (2,20220009,10,'文章写的不错!','7954','2011-10-22');
INSERT INTO comments VALUES (3,20220002,3,'文章写的不错!','8794','2010-02-02');
INSERT INTO comments VALUES (4,20220001,6,'文章写的不错!','8976','2012-08-19');
INSERT INTO comments VALUES (5,20220010,12,'文章写的不错!','5354','2006-01-02');
INSERT INTO comments VALUES (6,20220001,10,'文章写的不错!','8794','2011-11-22');
INSERT INTO comments VALUES (7,20220003,1,'文章写的不错!','4255','2009-07-18');
INSERT INTO comments VALUES (8,20220007,11,'文章写的不错!','9842','2004-11-20');
INSERT INTO comments VALUES (9,20220006,5,'文章写的不错!','4583','2004-05-26');
INSERT INTO comments VALUES (10,20220007,4,'文章写的不错!','3954','2005-09-29');
INSERT INTO comments VALUES (11,20220004,6,'文章写的不错!','7654','2012-08-15');
INSERT INTO comments VALUES (12,20220010,2,'文章写的不错!','3464','2009-06-01');
INSERT INTO comments VALUES (13,20220009,9,'文章写的不错!','8246','2003-07-20');
INSERT INTO comments VALUES (14,20220003,12,'文章写的不错!','8954','2005-12-21');
INSERT INTO comments VALUES (15,20220006,8,'文章写的不错!','8846','2006-12-02');
INSERT INTO classifications
VALUES (001,'诗歌','诗歌类型文章');
INSERT INTO classifications
VALUES (002,'散文','散文类型文章');
INSERT INTO classifications
VALUES (003,'小说','小说类型文章');
INSERT INTO classifications
VALUES (004,'哲理名言','哲理名言类型文章');
INSERT INTO classifications
VALUES (005,'奇闻轶事','奇闻轶事类型文章');
INSERT INTO labels
VALUES (1,'红心','收藏文章');
INSERT INTO labels
VALUES (2,'随心记','随心记文章');
INSERT INTO labels
VALUES (3,'每日日记','日记文章');
INSERT INTO labels
VALUES (4,'心事树洞','心事树洞文章');
INSERT INTO labels
VALUES (5,'成长过程','成长过程文章');
INSERT INTO article_classification VALUES (8,1);
INSERT INTO article_classification VALUES (10,1);
INSERT INTO article_classification VALUES (1,2);
INSERT INTO article_classification VALUES (6,2);
INSERT INTO article_classification VALUES (5,3);
INSERT INTO article_classification VALUES (7,3);
INSERT INTO article_classification VALUES (2,4);
INSERT INTO article_classification VALUES (3,4);
INSERT INTO article_classification VALUES (4,4);
INSERT INTO article_classification VALUES (9,4);
INSERT INTO article_classification VALUES (11,5);
INSERT INTO article_classification VALUES (12,5);
INSERT INTO article_label VALUES (1,1);
INSERT INTO article_label VALUES (2,5);
INSERT INTO article_label VALUES (3,3);
INSERT INTO article_label VALUES (4,4);
INSERT INTO article_label VALUES (5,5);
INSERT INTO article_label VALUES (6,1);
INSERT INTO article_label VALUES (7,2);
INSERT INTO article_label VALUES (8,2);
INSERT INTO article_label VALUES (9,5);
INSERT INTO article_label VALUES (10,3);
INSERT INTO article_label VALUES (11,4);
INSERT INTO article_label VALUES (12,3);
INSERT INTO administrators VALUES (101,'admin','1234');
INSERT INTO administrators VALUES (102,'admin','1234');
INSERT INTO administrators VALUES (103,'admin','1234');
7、视图、触发器、存储函数设计
创建索引
CREATE UNIQUE INDEX users_nickname_idx
ON users (nickname);
CREATE INDEX articles_title_idx
ON articles (title);
创建视图
CREATE VIEW user_article_count_view
AS
SELECT x.user_id AS 作者ID,
x.nickname AS 作者昵称,
COUNT(y.article_id) AS 文章数量
FROM users x
LEFT JOIN articles y
ON x.user_id = y.user_id
GROUP BY x.user_id;
创建触发器
示例一:评论时间自动设置为当前时间
DELIMITER $$
CREATE TRIGGER set_comment_time
BEFORE INSERT
ON comments
FOR EACH ROW
BEGIN
SET NEW.comment_time = CURRENT_DATE();
END$$
DELIMITER ;
示例二:当评论信息增加或删除时,文章表对应评论量也会随之改变
DELIMITER $$
CREATE TRIGGER update_article_comment_count AFTER INSERT ON comments
FOR EACH ROW
BEGIN
UPDATE articles
SET comment_count = comment_count + 1
WHERE article_id = NEW.article_id;
END$$
CREATE TRIGGER delete_article_comment_count AFTER DELETE ON comments
FOR EACH ROW
BEGIN
UPDATE articles
SET comment_count = comment_count - 1
WHERE article_id = OLD.article_id;
END$$
DELIMITER ;
创建存储过程
DELIMITER $$
CREATE PROCEDURE GetArticleList()
BEGIN
SELECT a.title, u.nickname, a.article_time
FROM articles a
JOIN users u ON a.user_id = u.user_id;
END$$
DELIMITER ;
8、数据库运行与维护
数据库运行与维护的主要任务包括:
A、维护数据库的安全性与完整性
B、监测并改善数据库性能
C、重新组织和构造数据库
只要数据库系统在运行,就需要不断地进行修改、调整和维护。