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.newsDateFROM news_Comment c,news nWHERE 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 UPDATEON news FOR EACH ROWBEGIN UPDATE news_Comment SET newsID=NEW. newsIDEND&&DELIMITER ; 

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

• 设计 DELETE 触发器

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

DELIMITER &&CREATE TRIGGER delete_user AFTER DELETEON user FOR EACH ROWBEGIN DELETE FROM users_Comment WHERE userID=OLD.userIDEND&&DELIMITER ; 

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