2022年新闻系统数据库设计 .pdf

《2022年新闻系统数据库设计 .pdf》由会员分享，可在线阅读，更多相关《2022年新闻系统数据库设计 .pdf（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1 根据需求分析的结果规划出的实体有：管理员实体、单条新闻实体、新闻版块实体、新闻评论实体管理员表(admin)Sql：DROP DATABASE IF EXISTS webnews;CREATE DATABASE webnews;USE webnews;CREATE TABLE admin(adminID int(4)NOT NULL auto_increment,adminType int(4)not null,adminName char(30)not null,loginName char(30)not null,loginPwd char(30)not null,PRIMARY KE

2、Y (adminID)ENGINE=InnoDB DEFAULT CHARSET=gb2312;INSERT INTO admin(adminID,adminType,adminName,loginName,loginPwd)VALUES(1,1,系统管理员,admin1,admin1);INSERT INTO admin(adminID,adminType,adminName,loginName,loginPwd)VALUES(2,2,新闻管理员,admin2,admin2);INSERT INTO admin(adminID,adminType,adminName,loginName,lo

3、ginPwd)VALUES(3,3,会员管理员,admin3,admin3);INSERT INTO admin(adminID,adminType,adminName,LoginName,LoginPwd)VALUES(4,4,评论管理员,admin4,admin4);会员表（Member）字段名类型长度是否为空注释默认值memberIDint4Not null主键，会员 ID无loginName varchar50Not null登录名无loginPwd varchar50Not null登录密码无名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 6 页 -realName v

4、archar50Not null真实姓名无emailvarchar 100 Not null 会员 email 无CREATE TABLE member(memberID int(4)NOT NULL auto_increment,loginName char(50)not null,loginPwd char(50)not null,realName char(50)not null,email varchar(100)not null,PRIMARY KEY (memberID)ENGINE=InnoDB DEFAULT CHARSET=gb2312;INSERT INTO member(

5、memberID,loginName,loginPwd,realName,email)VALUES(1,member1,member1,会员 1 号,);INSERT INTO member(memberID,loginName,loginPwd,realName,email)VALUES(2,member2,member2,会员 2 号,);INSERT INTO member(memberID,loginName,loginPwd,realName,email)VALUES(3,member3,member3,会员 3 号,);INSERT INTO member(memberID,log

6、inName,loginPwd,realName,email)VALUES(4,member4,member4,会员 4 号,);INSERT INTO member(memberID,loginName,loginPwd,realName,email)VALUES(5,member5,member5,会员 5 号,);INSERT INTO member(memberID,loginName,loginPwd,realName,email)VALUES(6,member6,member6,会员 6 号,);INSERT INTO member(memberID,loginName,login

7、Pwd,realName,email)VALUES(7,member7,member7,会员 7 号,);INSERT INTO member(memberID,loginName,loginPwd,realName,email)VALUES(8,member8,member8,会员 8 号,);INSERT INTO member(memberID,loginName,loginPwd,realName,email)VALUES(9,member9,member9,会员 9 号,);名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 6 页 -INSERT INTO member(

8、memberID,loginName,loginPwd,realName,email)VALUES(10,member10,member10,会员 10 号,);新闻评论表（Comments）字段名类 型长 度 是否为空注 释默认值CommentID int4否主键评论标识（无）reviewerint4是外键评论者姓名（会员ID）（无）Titlevarchar100否评论标题（无）ContentText16否评论内容（无）CommentDate Datetime8否评论时间Now（）newsID int4是外键所属新闻 ID（无）CREATE TABLE comments(commentID

9、int(4)NOT NULL auto_increment,reviewer int(4),news int(4),title varchar(100)not NULL,content text not null,commentDate datetime not null,PRIMARY KEY (commentID),foreign key(reviewer)references member(memberID)on delete cascade,foreign key(news)references news(newsID)on delete cascade)ENGINE=InnoDB D

10、EFAULT CHARSET=gb2312;INSERT INTO comments(commentID,reviewer,news,title,content,commentDate)VALUES(1,1,1,真假？,火箭就是垃圾,now();INSERT INTO comments(commentID,reviewer,news,title,content,commentDate)VALUES(2,2,1,强大的队伍正常表现,期待火箭会越来越好,now();INSERT INTO comments(commentID,reviewer,news,title,content,commentD

11、ate)VALUES(3,1,3,真假？,饭岛爱死了，太可惜了。多好的一人啊！,now();INSERT INTO comments(commentID,reviewer,news,title,content,commentDate)VALUES(4,1,2,读书？,读书到死,now();名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 6 页 -INSERT INTO comments(commentID,reviewer,news,title,content,commentDate)VALUES(5,2,1,真假？,我也是,now();新闻版块表（section）字段名类 型长

12、 度是否为空注 释默认值sectionID int4否主键 版块标识（无）sectionNameVarchar30否模板名（无）descriptionVarchar100是版块描述（无）CREATE TABLE section(sectionID int(4)NOT NULL auto_increment,sectionName varchar(30)not NULL,description varchar(100),PRIMARY KEY (sectionID)ENGINE=InnoDB DEFAULT CHARSET=gb2312;INSERT INTO section(sectionID

13、,sectionName,description)VALUES(1,NBA,NBA赛事最新最快信息);INSERT INTO section(sectionID,sectionName,description)VALUES(2,读书,读书时代，今天你读书了吗？);INSERT INTO section(sectionID,sectionName,description)VALUES(3,财经,财经报道，股票、基金);INSERT INTO section(sectionID,sectionName,description)VALUES(4,娱乐,我最八卦);INSERT INTO sectio

14、n(sectionID,sectionName,description)VALUES(5,军事,最新军事报道);INSERT INTO section(sectionID,sectionName,description)VALUES(6,时事,国内外时事);新闻表（news）字段名类 型长 度是否为空注 释默认值NewsID int4否主键 新闻标识（无）submtDatedatetime8否新闻提交时间 now（）Authorvarchar50否记者姓名（无）Titlevarchar100否新闻标题（无）Contenttext16否新闻内容（无）名师资料总结-精品资料欢迎下载-名师精心整理-

15、第 4 页，共 6 页 -sectionID int4是外键 所属版块（无）keyWordsVarchar100是关键字（无）clickedTimesInt4否点击次数0picturevarchar(100)varchar(100)是图片，存放图片路径default NULLCREATE TABLE news(newsID int(4)NOT NULL auto_increment,section int(4)not null,title varchar(100)not NULL,author varchar(50)not null,content text not null,keyWords

16、 varchar(100)not NULL,submitDate datetime not null,clickedTimes int(4)default 0,picture varchar(100),PRIMARY KEY (newsID),foreign key(section)references section(sectionID)on delete cascade)ENGINE=InnoDB DEFAULT CHARSET=gb2312;INSERT INTO news(newsID,section,title,author,content,keywords,submitDate,c

17、lickedTimes,picture)VALUES(1,1,火箭四连败,小猫,正文四连败,nba 火箭,now(),0,图片 1);INSERT INTO news(newsID,section,title,author,content,keywords,submitDate,clickedTimes,picture)VALUES(2,2,我在读书,小明,正文我在读书，别烦我！,读书,now(),0,图片 2);INSERT INTO news(newsID,section,title,author,content,keywords,submitDate,clickedTimes,pictu

18、re)VALUES(3,3,财经新闻,洋洋,正文财经股票大涨,财经 股票,now(),0,图片 3);INSERT INTO news(newsID,section,title,author,content,keywords,submitDate,clickedTimes,picture)VALUES(4,4,饭岛爱死了!,石头,正文 AV 女星饭岛爱死了，真可惜,饭岛爱 AV 女星,now(),0,图片 4);名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 6 页 -INSERT INTO news(newsID,section,title,author,content,key

19、words,submitDate,clickedTimes,picture)VALUES(5,5,南方枪击事件,驴子,正文枪击,军事 枪击,now(),0,图片 5);INSERT INTO news(newsID,section,title,author,content,keywords,submitDate,clickedTimes,picture)VALUES(6,6,奥巴马演讲,小狗,正文奥巴马发表就职演说,美国 奥巴马大选,now(),0,图片 6);在数据库中 测试 一下：注意：删除一条新闻，它的所有评论一并同时删除。删除一个会员，他的所有评论一并同时删除。删除一个版块，它的所有新

20、闻一并同时删除。(1)查看一个会员的所有评论个数？select count(commentID)from comments where reviewer=2;(2)查看一下每条新闻有几条评论？select count(commentID)from comments where news=2;(3)删除一个会员试验一下，看是否其所有评论一并删除？delete from member where memberID=1;(4)删除一个版块试验一下，看是否其所有新闻一并删除？delete from section where sectionID=6;(5)删除一条新闻试验一下，看是否其所有评论一并删除？delete from news where newsID=1;名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 6 页 -