MySQL实现—新闻发布系统数据库设计

系统概述

本章介绍的是一个小型新闻发布系统，管理员可以通过该系统发布新闻信息、管理新闻信息。 一个典型的新闻发布系统网站至少应该包含新闻信息管理、新闻信息显示和新闻信息查询 3 种功 能。

新闻发布系统所要实现的功能具体包括新闻信息添加、新闻信息修改、新闻信息删除、显示 全部新闻信息、按类别显示新闻信息、按关键字查询新闻信息、按关键字进行站内查询。

本站为一个简单的新闻信息发布系统，该系统具有以下特点。

• 实用：系统实现了一个完整的信息查询过程。
• 简单易用：为使用户尽快掌握和使用整个系统，系统结构简单但功能齐全，简洁的页面设 计使操作起来非常简便。
• 代码规范：作为一个实例，文中的代码规范简洁、清晰易懂。

本系统主要用于发布新闻信息、管理用户、管理权限、管理评论等功能。这些信息的录入、 查询、修改和删除等操作都是该系统重点解决的问题。

本系统的主要功能包括以下几点：

（1）具有用户注册及个人信息管理功能。

（2）管理员可以发布新闻、删除新闻。

（3）用户注册后可以对新闻进行评论、发表留言。

（4）管理员可以管理留言和对用户进行管理。

**系统功能 **

新闻发布系统分为 5 个管理部分，即用户管理、管理员管理、权限管理、新闻管理和评论管 理。本系统的功能模块如下图 所示

中模块的详细介绍如下：

（1）用户管理模块：实现新增用户、查看和修改用户信息功能。

（2）管理员管理模块：实现新增管理员，查看、修改和删除管理员信息功能。

（3）权限管理模块：实现对管理员、对管理的模块和管理的评论赋权功能。

（4）新闻管理模块：实现有相关权限的管理员对新闻的增加、查看、修改和删除功能。

（5）评论管理模块：实现有相关权限的管理员对评论的审核和删除功能。

通过上面的介绍，读者对这个新闻发布系统的主要功能有一定的了解，下面将向读者介绍 本系统所需要的数据库和表。

数据库设计和实现

数据库设计是开发管理系统最重要的一个步骤。如果数据库设计得不够合理，将会为后续的 开发工作带来很大的麻烦。本节为读者介绍新闻发布系统的数据库开发过程。

数据库设计时要确定设计哪些表、表中包含哪些字段、字段的数据类型和长度。通过本节的 学习，读者可以对 MySQL 数据库的知识有一个全面的了解。

1. **设计表 **

本系统所有的表都放在 webnews 数据库下。创建和选择 webnews 数据库的 SQL 代码如下

CREATE DATABASE webnews;
USE webnews; 

在这个数据库下总共存放 9 张表，分别是 user、admin、roles、news、category、comment、 admin_Roles、news_Comment 和 users_Comment。

（1）user 表

user 表用于存储用户 ID、用户名、密码和用户 Email 地址，所以 user 表设计了 5 个字段。user 表每个字段的信息如下表 所示。

根据表的内容创建 user 表，SQL 语句如下：

CREATE TABLE user(
userID INT PRIMARY KEY UNIQUE NOT NULL, 
userName VARCHAR(20) NOT NULL,
userPassword VARCHAR(20) NOT NULL,
sex varchar(10) NOT NULL,
userEmail VARCHAR(20) NOT NULL
); 

创建完成后，可以使用 DESC 语句查看 user 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 user 表的详细信息。

（2）admin 表

管理员信息（admin）表主要用来存放用户账号信息，如下所示

根据表的内容创建 admin 表。创建 admin 表的 SQL 语句如下：

CREATE TABLE admin(
adminID INT PRIMARY KEY UNIQUE NOT NULL,
adminName VARCHAR(20) NOT NULL,
adminPassword VARCHAR(20) NOT NULL
); 

创建完成后，可以使用 DESC 语句查看 admin 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 admin 表的详细信息。

（3）roles 表

权限信息（roles）表主要用来存放权限信息，如下所示。

根据表的内容创建 roles 表，SQL 语句如下：

CREATE TABLE roles(
roleID INT PRIMARY KEY UNIQUE NOT NULL,
roleName VARCHAR(20) NOT NULL
); 

创建完成后，可以使用 DESC 语句查看 roles 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 roles 表的详细信息。

（4）news 表

新闻信息（news）表主要用来存放新闻信息，如下 、所示

根据表的内容创建 news 表，SQL 语句如下：

CREATE TABLE news(
newsID INT PRIMARY KEY UNIQUE NOT NULL,
newsTitle VARCHAR(50) NOT NULL,
newsContent TEXT NOT NULL,
newsDate TIMESTAMP,
newsDesc VARCHAR(50) NOT NULL,
newsImagePath VARCHAR(50),
newsRate INT,
newsIsCheck BIT,
newsIsTop BIT
); 

创建完成后，可以使用 DESC 语句查看 news 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 news 表的详细信息。

（5）category 表

栏目信息（categroy）表主要用来存放新闻栏目信息，如下所示。

根据表的内容创建 categroy 表。创建 categroy 表的 SQL 语句如下：

CREATE TABLE categroy (
categoryID INT PRIMARY KEY UNIQUE NOT NULL,
categoryName VARCHAR(50) NOT NULL, 
categoryDesc VARCHAR(50) NOT NULL
); 

创建完成后，可以使用 DESC 语句查看 categroy 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 categroy 表的详细信息

（6）comment 表

评论信息（comment）表主要用来存放新闻评论信息，如下所示

根据表的内容创建 comment 表，SQL 语句如下：

CREATE TABLE comment (
commentID INT PRIMARY KEY UNIQUE NOT NULL,
commentTitle VARCHAR(50) NOT NULL,
commentContent TEXT NOT NULL,
commentDate DATETIME
); 

创建完成后，可以使用 DESC 语句查看 comment 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 comment 表的详细信息。

（7） admin_Roles 表

管理员_权限（admin_Roles）表主要用来存放管理员和权限的关系，如下所示。

根据表的内容创建 admin_Roles 表，SQL 语句如下：

CREATE TABLE admin_Roles (
aRID INT PRIMARY KEY UNIQUE NOT NULL,
adminID INT NOT NULL,
roleID INT NOT NULL
); 

创建完成后，可以使用 DESC 语句查看 admin_Roles 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 admin_Roles 表的详细信息。

（8）news_Comment 表

新闻_评论（news_Comment）表主要用来存放新闻和评论的关系，如下 所示。

根据表的内容创建 news_Comment 表，SQL 语句如下：

CREATE TABLE news_Comment (
nCommentID INT PRIMARY KEY UNIQUE NOT NULL,
newsID INT NOT NULL,
commentID INT NOT NULL
); 

创建完成后，可以使用 DESC 语句查看 news_Comment 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 news_Comment 表的详细信息。

（9）users_Comment 表

用户_评论（users_Comment）表主要用来存放用户和评论的关系，如表所示。

根据表的内容创建 users_Comment 表，SQL 语句如下

CREATE TABLE news_Comment (
uCID INT PRIMARY KEY UNIQUE NOT NULL,
userID INT NOT NULL,
commentID INT NOT NULL
); 

创建完成后，可以使用 DESC 语句查看 users_Comment 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 users_Comment 表的详细信息。

设计索引

索引是创建在表上的，是对数据库中一列或者多列的值进行排序的一种结构。索引可以提高 查询的速度。新闻发布系统需要查询新闻的信息，这就需要在某些特定字段上建立索引，以便提高 查询速度。

• **在 news 表上建立索引 **

新闻发布系统中需要按照 newsTitle 字段、newsDate 字段和 newsRate 字段查询新闻信息。

本小节将使用 CREATE INDEX 语句和 ALTER TABLE 语句创建索引。 下面使用 CREATE INDEX 语句在 newsTitle 字段上创建名为 index_new_title 的索引。SQL 语 句如下：

CREATE INDEX index_new_title ON news(newsTitle); 

然后，使用 CREATE INDEX 语句在 newsDate 字段上创建名为 index_new_date 的索引。SQL 语句如下：

CREATE INDEX index_new_date ON news(newsDate); 

最后，使用 ALTER TABLE 语句在 newsRate 字段上创建名为 index_new_rate 的索引。SQL 语句如下：

ALTER TABLE news ADD INDEX index_new_rate (newsRate); 

• 在 categroy 表上建立索引

在新闻发布系统中，需要通过栏目名称查询该栏目下的新闻，因此需要在这个字段上创建索 引。创建索引的语句如下：

CREATE INDEX index_categroy_name ON categroy (categroyName); 

代码执行完成后，读者可以使用 SHOW CREATE TABLE 语句查看 categroy 表的详细信息。

• **在 comment 表上建立索引 **

新闻发布系统需要通过 commentTitle 字段和 commentDate 字段查询评论内容，因此可以在这 两个字段上创建索引。创建索引的语句如下：

CREATE INDEX index_comment_title ON comment (commentTitle);
CREATE INDEX index_comment_date ON comment (commentDate); 

代码执行完成后，读者可以通过 SHOW CREATE TABLE 语句查看 comment 表的结构。

设计视图

视图是由数据库中一个表或者多个表导出的虚拟表，作用是方便用户对数据的操作。在这个 新闻发布系统中，也设计了一个视图改善查询操作。

在新闻发布系统中，如果直接查询 news_Comment 表，显示信息时会显示新闻编号和评论编 号。这种显示不直观，为了以后查询方便，可以建立一个视图 news_view。这个视图显示评论编号、 新闻编号、新闻级别、新闻标题、新闻内容和新闻发布时间。创建视图 news_view 的 SQL 代码如 下：

CREATE VIEW news_view 
AS SELECT c.commentID,n.newsID,n.newsRate,n.newsTitle,n.newsContent,n.newsDate
FROM news_Comment c,news n
WHERE news_Comment.newsID=news.newsID; 

news_Comment 表的别名为 c，news 表的别名为 n，这个视图从这两个表中取出相应的字段。 视图创建完成后，可以使用 SHOW CREATE VIEW 语句查看 news_view 视图的详细信息。

设计触发器

触发器由 INSERT、UPDATE 和 DELETE 等事件来触发某种特定的操作。满足触发器的触发 条件时，数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。 为了使新闻发布系统的数据更新更加快速和合理，可以在数据库中设计几个触发器。

• **设计 UPDATE 触发器 **

在设计表时，news 表和 news_Comment 表的 newsID 字段的值是一样的。如果 news 表中的 newsID 字段的值更新了，那么 news_Comment 表中的 newsID 字段的值也必须同时更新。这可以通 过一个 UPDATE 触发器来实现。创建 UPDATE 触发器 update_newsID 的 SQL 代码如下：

DELIMITER &&
CREATE TRIGGER update_newsID AFTER UPDATE
ON news FOR EACH ROW
BEGIN
 UPDATE news_Comment SET newsID=NEW. newsID
END
&&
DELIMITER ; 

其中，NEW.newsID 表示 news 表中更新的记录的 newsID 值。

• **设计 DELETE 触发器 **

如果从 user 表中删除一个用户的信息，那么这个用户在 users_Comment 表中的信息也必须同 时删除。这也可以通过触发器来实现。在 user 表上创建 delete_user 触发器，只要执行 DELETE 操 作，就删除 users_Comment 表中相应的记录。创建 delete_user 触发器的 SQL 语句如下：

DELIMITER &&
CREATE TRIGGER delete_user AFTER DELETE
ON user FOR EACH ROW
BEGIN
 DELETE FROM users_Comment WHERE userID=OLD.userID
END
&&
DELIMITER ; 

其中，OLD.userID 表示新删除的记录的 userID 值