新闻发布系统数据库设计

数据库名与表名

create database webnews;
use webnews;

表与表

表结构与创建表

#user
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
);

#admin
create table admin(
adminID INT PRIMARY KEY UNIQUE NOT NULL,
adminName VARCHAR(20)  NOT NULL,
adminPassword VARCHAR(20)  NOT NULL
);

#roles
create table roles(
roleID INT PRIMARY KEY UNIQUE NOT NULL,
roleName VARCHAR(20)  NOT NULL
);

#news
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  NOT NULL,
newsIsCheck BIT  NOT NULL,
newsIsTop BIT  NOT NULL
);

#category
create table category(
categoryID INT PRIMARY KEY UNIQUE NOT NULL,
categoryName VARCHAR(20)  NOT NULL,
categoryDesc VARCHAR(50)  NOT NULL
);

#comment
create table comment(
commentID INT PRIMARY KEY UNIQUE NOT NULL,
commentTitle VARCHAR(50)  NOT NULL,
commentContent TEXT  NOT NULL,
commentDate TIMESTAMP 
);

#admin_Roles
create table admin_Roles(
aRID INT PRIMARY KEY UNIQUE NOT NULL,
adminID INT  NOT NULL,
roleID INT  NOT NULL
);

#news_Comment
create table news_Comment(
nCID INT PRIMARY KEY UNIQUE NOT NULL,
newsID INT  NOT NULL,
commentID INT  NOT NULL
);

#user_Comment
create table user_Comment(
uCID INT PRIMARY KEY UNIQUE NOT NULL,
userID INT  NOT NULL,
commentID INT  NOT NULL
);

设计索引

create index index_news_title on news(newsTitle);
create index index_news_date on news(newsDate);
create index index_news_rate on news(newsRate);
create index index_category_name on category(categoryName);
create index index_comment_title on comment(commentTitle);
create index index_comment_date on comment(commentDate);

设计视图

create view news_view
as select c.commentID,c.newsID,n.newsRate,n.newsTitle,n.newsContent,n.newsDate
from news_Comment c,news n
where c.newsID=n.newsID;

设计触发器

#UPDATE触发器
delimiter &&
create trigger update_newsID after update
on news for each row
begin
    update news_Comment set newsID=new.newsID;
end
&&
delimiter ;

#DELETE触发器
delimiter &&
create trigger delete_user after delete
on user for each row
begin
    delete from users_Comment where userID=old.userID;
end
&&
delimiter ;

备注：
BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开
一般情况下，MySQL默认是以;作为结束执行语句，与触发器中需要的分行起冲突
为解决此问题可用DELIMITER，如：DELIMITER &&，可以将结束符号变成&&
当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;