BBS论坛数据库设计代码.docx

BBS论坛数据库设计代码 BBS论坛数据库设计代码 use master go -新建数据bbsDB的库- -判断数据库是否存在使用exists关键字，在用sysdatabases关键字查询- if exists(select * from sysdatabases where name = 'bbsDB') -如果数据库存在就删除此数据库用drop（删除）database（数据库对象）bbsDB（数据库名）- drop database bbsDB -删除后在添加一个数据库create（添加）database（数据库对象）bbsDB（数据库名）on- create database bbsDB on ( name = 'bbsDB_MDF', -数据库主文件名- filename = 'F:SQL第四阶段bbsDB_MDF.mdf', -数据库存储的路径- size = 10mb, -数据库初使大小- filegrowth = 10% -增长变化- ) -设置日志文件- log on ( name = 'bbsDB_LDF', -日志文件名- filename = 'F:SQL第四阶段bbsDB_LDF.ldf', -日志文件存储路径- size = 1mb, -日志文件初使大小- maxsize = 20mb, -日志文件最大的可储存- filegrowth = 10% -增长变化- ) go -以上信息为新建的数据库- use bbsDB go -新建bbsUsers表(用户表)- -判断bbsDB库是否有表bbsUser的存在exists关键字+sysobjects判断当前数据库表- if exists(select * from sysobjects where name='bbsUsers') -如果表存在就删除此表- drop table bbsUser -新建bbsUser表- create table bbsUsers ( UID int identity(1,1) not null, -用户编号,自动增长列，不允许空- Uname varchar(50) not null, -用户名,不允许空- Upassword varchar(50) not null, -密码,不允许空- Uemail varchar(50), -邮箱- Usex char(2) not null, -性别,不允许空- Uclass int, -用户等级- Uremark varchar(50), -备注信息- UregDate datetime not null, -注册日期,不允许空- Ustate int, -状态，是否在线等- Upoint int, -用户的积分- ) go -为用户表添加各种约束- -为bbsUser表添加约束，add constraint关键字PK_UID约束名，primary key（主键）设置UID字段为主键- alter table bbsUsers add constraint PK_UID primary key(UID) -为Upassword添加约束，check(Upassword length>=6)字段长度大于等于6位，default设置默认值为888888- alter table bbsUsers add constraint CK_Upassword check(len(Upassword) >= 6) alter table bbsUsers add constraint DF_Upassword default('888888') for Upassword -为Uemail字段添加约束，必须包含号- alter table bbsUsers add constraint CK_Uemail check(Uemail like '%') -为User字段添加约束，设置默认值为男，并且值只能是男或女- alter table bbsUsers add constraint DF_Usex default('男') for Usex alter table bbsUsers add constraint CK_Usex check(Usex='男' or Usex='女') -为Uclass字段添加约束，默认值为1- alter table bbsUsers add constraint DF_Uclass default(1) for Uclass -为UregDate字段添加约束，默认值为当前日期- alter table bbsUsers add constraint DF_UregDate default(getDate() for UregDate -为Ustate添加约束，默认值为0- alter table bbsUsers add constraint DF_Ustate default(0) for Ustate -为Upoint字段添加约束，默认值为20- alter table bbsUsers add constraint DF_Upoint default(20) for Upoint -以上信息为以建立的bbsUser（用户表）及各种约束- go -新建bbsSection表（版块表）- -判断数据库内是否有表bbsSection的存在- if exists(select * from sysobjects where name = 'bbsSection') -如果有此表执行删除- drop table bbsSection -新建bbsSection表,及个字段- create table bbsSection SID int identity(1,1) not null, -版块编号，自动增长列- Sname varchar(50) not null, -版块名称，不允许空- SmasterID int not null, -版主ID- Sprofile varchar(50), -版面简介- SclickCount int, -点击率- StopicCount int -发贴数- ) -为表bbsSection（版块表）添加约束- go -将SID字段设置为主键- alter table bbsSection add constraint PK_SID primary key(SID) -为SmasterID设置外键，他的主键是bbsUsers表的UID字段foreign key(外键) references 主键表(主键表字段)- alter table bbsSection add constraint FK_SmasterID foreign key(SmasterID) references bbsUsers (UID) -为SclickCount字段添加默认值为0- alter table bbsSection add constraint DF_SclickCount default(0) for SclickCount -为字段StopicCount设置默认值为0- alter table bbsSection add constraint DF_StopicCount default(0) for StopicCount -以上为表bbsSection（版块表）建立及添加相应约束- -新建bbsTopic表（主贴表）- go -判断数据库中是否有bbsTopic表的存在- if exists(select * from sysobjects where name = 'bbsTopic') -删除此表- drop table bbsTopic -新建bbaTopic表及个字段- create table bbsTopic ( TID int identity(1,1) not null, -帖子编号- TsID int not null, -版块编号- TuID int not null, -发贴人ID- TreplyCount int, -回复数量- Tface int, -发贴表情- Ttopic varchar(50) not null, -标题- Tcontents varchar(50) not null, -正文- Ttime datetime, -发贴时间- TclickCount int, -点击数- Tstate int not null, -状态- TlastReply datetime -最后回复时间- -为bbsTopic(主贴表)表个字段添加约束- go -将TID字段设置为主键- alter table bbsTopic add constraint PK_TID primary key(TID) -将TsID设置为外键，引用bbsSection表的主键SID字段- alter table bbsTopic add constraint FK_TsID foreign key(TsID) references bbsSection (SID) -将TuID字段设置为外键盘，引用bbsUsers表的主键UID字段- alter table bbsTopic add constraint FK_TuID foreign key(TuID) references bbsUsers (UID) -设置TreplyCount字段的默认值为0- alter table bbsTopic add constraint DF_TreplyCount default(0) for TreplyCount -为Tcontents字段添加约束，他的值必须大于等于6位- alter table bbsTopic add constraint CK_Tcontents check(len(Tcontents) >= 6) -为Ttime字段添加默认值，为当前日期- alter table bbsTopic add constraint DF_Ttime default(getDate() for Ttime -设置TclickCount字段默认值为0- alter table bbsTopic add constraint DF_TclickCount default(0) for TclickCount -设置Tstate字段默认值为1- alter table bbsTopic add constraint DF_Tstate default(1) for Tstate -为TlastReply字段添加约束，最后回复时间必须要晚于发贴时间- alter table bbsTopic add constraint CK_TlastReply check(TlastReply > Ttime) -以上为bbsTopic（主贴表）表建立及建立个字段- -新建表bbsReply（回帖表）及个字段- go -判断数据库中是否有bbsReply（回帖表）的存在- if exists(select * from sysobjects where name = 'bbsReply') -删除bbsReply表- drop table bbsReply -创建bbsReply（回帖）表及个字段- create table bbsReply ( RID int identity(1,1) not null, -帖子编号- RtID int not null, -主贴ID- RsID int not null, -版块ID- RuID int not null, -回帖人ID- Rface int, -回帖表情- Rcontents varchar(50) not null, -正文- Rtime datetime, -回帖时间- RclickCount int -点击数- ) -为bbsReply（回帖）表个字段添加约束- go -设置RID字段为主键- alter table bbsReply add constraint PK_RID primary key(RID) -设置RtID字段为外键引用bbsTopic表的主键TID字段- alter table bbsReply add constraint FK_RtID foreign key(RtID) references bbsTopic(TID) -设置RsID为外键引用bbsSection表的主键SID- alter table bbsReply add constraint FK_RsID foreign key(RsID) references bbsSection(SID) -设置RuID为外键引用bbsUsers表的主键UID字段- alter table bbsReply add constraint FK_RuID foreign key(RuID) references bbsUsers(UID) -设置Rcontents字段必须大于6位- alter table bbsReply add constraint DF_Rcontents check(len(Rcontents) >= 6) -设置Rtime字段默认值为当前日期- alter table bbsReply add constraint DF_Rtime default(getDate() for Rtime -以上为创建bbsReply（回帖）表的内容- -向表格中插入数据- go -向bbsUsers（用户表插入数据）- insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('天天','123456','zhong ','女',2,'我们的家乡在希望的田野上',3,100) insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('可卡因','HYXS007','ss ','女',1,'我要去公安局自首',1,200) insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('心酸果冻','888888','yy ','男',2,'牵匹瘦马闯天涯',2,600) insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('冬篱儿','fangdong','bb ','女',3,'爱迷失在天堂',4,1200) insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('Supper','master','dd ','女',5,'BBS大斑竹',1,500) -向bbsSection（版块表插入数据）- insert into bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount) values('Java技术核心',3,'包含框架，开源',500,1) insert into bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount) values('.Net技术',5,'包含C#，ASP',800,1) insert into bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount) values('Linux/Unix社区',5,'包含系统维护运行',0,0) -向bbsTopic（主贴表）插入数据- insert into bbsTopic(TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate,TlastReply) values(1,3,3,1,'还是JAVA','JAVA文件如何读取',200,1,'2022-8-1') insert into bbsTopic(TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate,TlastReply) values(2,2,1,2,'.NET文件部署','项目包含、WINFROM',0,1,'2022-8-5') -向bbsReply（跟贴表）插入数据- insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(1,1,5,2,'JAVA中乱码问题怎么解决好？',100) insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(1,1,4,4,'你好你好我们我们',200) insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(1,1,4,4,'呵呵哈哈嘿嘿西西',200) insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(2,1,4,4,'JSP文件转换',200) insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(2,1,4,4,'啊大安定大安定',200) -以上为各表格插入测试数据- -查询数据库情况- print 'SQL Server的版本是：'+version print 'SQL Server的服务器名称是：'+servername - -系统变量查询上一条语句错误- update bbsUsers set Upassword = '1234' where Uname = '天天' print '执行上条语句产生错误: '+convert(varchar(5),error) -输出上一条语句的错误信息- - -查询用户天天的信息- set nocount on -不打印影响的信息- -查询个人信息- print '天天个人信息如下' select Uname,Uremark,Upoint from bbsUsers where Uname = '天天' -用变量存储ID查询信息- declare id int -声明变量- select id = UID from bbsUsers where Uname = '天天' -将bbsUsers表中的UID字段赋值给id变量- print '发贴情况如下' select convert(varchar(10),Ttime,111),TclickCount,Ttopic,Tcontents,TclickCount,Tcontents from bbsTopic where TuID = id print '回贴情况如下' select convert(varchar(10),Rtime,111),RclickCount,Rcontents from bbsReply where RuID = id if(id > 30) -if判断- print '有权发贴' -条件为真输出- else -否则- print '无权发贴' -输出- - -查询心酸果冻用户发贴回帖显示级别- -声明变量用于存储用户ID- declare uNameID int -声明变量用于存储用户发贴数量- declare count int -声明变量用于存储用户回帖数量- declare count1 int -将心酸果冻用户的ID号存到变量uNameID中- select uNameID = UID from bbsUsers where Uname = '心酸果冻' -用count统计函数将用户的发贴数量存到变量count中- select count = count(*) from bbsTopic where TuID = uNameID -输出- print '心酸果冻发贴数量为：' + convert(varchar(10),count) print '' print '具体内容如下：' -显示信息,判断用户发贴数量大于0时显示帖子的信息- if(count > 0) select Ttime as 发贴时间,TclickCount as 点击率,Ttopic as 主题,Tcontents as 内容from bbsTopic where TuID = uNameID -用count统计函数将用户的回贴数量存到变量count1中- select count1 = count(*) from bbsReply where RuID = uNameID -输出- print '心酸果冻的回帖数量为: '+convert(varchar(10),count1) -显示信息，用户回帖数量大于0时显示帖子信息- if(count1 > 0) select Rtime as 回帖时间,RclickCount as 点击数量,Rcontents as 回帖内容from bbsReply where RuID = uNameID -判断用户的等级- if(count + count1) = 10) and (count + count1) = 20) and (count + count1) = 30) and (count + count1) = 40) and (count + count1) = 50) print '心酸果冻贴数总计为: '+convert(varchar(10),count + count1)+' 功臣级别为: 法老' - -回帖最多的为精华贴- -声明整形变量用于存储主贴编号,和作者编号- declare tidid int declare zhuzheid int declare huifu int -将主贴标号存到tidid变量中- select top 1 tidid = TID from bbsTopic order by TreplyCount desc -将用户编号存到zhuzheid变量中- select zhuzheid = TuID from bbsTopic where TID = tidid -输出- print '第一精华贴的信息如下：' -查出回贴最多的主贴，（用户表与主贴表两表连查）- select Ttime as 发贴时间,TclickCount as 点击率,Uname as 作者,Ttopic as 主题,Tcontents as 内容from bbsTopic inner join bbsUsers on bbsUsers.UID = bbsTopic.TuID where bbsUsers.UID = zhuzheid and bbsTopic.TID = tidid -为了美观输出空行- print '' -利用tidid（主贴编号）变量查出回复数量存到huifu变量中- select huifu = TreplyCount from bbsTopic where TID = tidid -输出- print '回帖数量'+convert(varchar(10),huifu)+',如下所示：' -查询回帖信息- select Rtime as 回帖时间,RclickCount as 点击率,回帖表情= case -case多分之语句判断回帖表情- when Rface = 1 then '(00)猪头' when Rface = 2 then '*:0)小丑' when Rface = 3 then ':|机器人' when Rface = 4 then '00老人家' when Rface = 5 then '(:1000为“人气熊旺旺”，否则为“一般般” 2、年度品牌版块：主贴量最多的版块 3、年度倒胃版块：主贴量最少的版块 4、年度回帖