BBS源代码.docx

1、BBS源代码-use mastergoif exists(select*from sysdatabases where name =BBS)drop database bbsgo-create database BBSon primary(name = BBS_data, -filename =d:BBS_data.mdf, -size = 20MB, -filegrowth=10%, -maxsize = 200MB -)log on(name =bbs_log, -filename =d:bbs_data.ldf, -size =10mb, -filegrowth = 10%,-maxsi

2、ze = 100MB -)use BBSgoif exists(select*from sysobjects where name =BBSUsers)drop table BBSUsersgocreate table BBSUsers(Uid int constraint pk_Uid primary key,-Uname char(8) not null, -UPassword char(16) constraint df_UPassword default 888888,-UEmail char(20) not null constraint ck_Uemail check(Uemail

3、 like%.%),-EMAILUBirthday datetime not null,-Usex int constraint df_Usex default 1, -UClass int constraint df_UClass default 1, -UStatement varchar(150)not null, -URegDate datetime not null default getdate(), -UState tinyint constraint df_UState default 1, - UPoint int constraint df_UPoint default 2

4、0 , - constraint ck_UPassword check(UPassword like _)if exists(select*from sysobjects where name =BBSTopic)drop table BBSTopicgocreate table BBSTopic(tid int constraint pk_tid primary key, -tsid int not null, -tuid int not null, -treplycount int not null, -temotion char(10) not null, -TTopic varchar

5、(16) not null CONSTRAINT ck_tt check(TTopic not like%), -TContents text not null, -TTime datetime not null default getdate() ,-TClickCount int not null, - TLastClickT datetime not null ,CONSTRAINT CK_TLC CHECK (TLastClickT=TTime) - )if exists(select*from sysobjects where name =BBSReply)drop table BB

6、SReplygocreate table BBSReply(RID int constraint pk_RID primary key,- RTID int,- RSID int,- RUID int,- REmotion char(10),- RTopic varchar(20),-RContents text,-RTime datetime default getdate(),-RClickCount int,-)if exists(select*from sysobjects where name =BBSSection)drop table BBSSectiongocreate tab

7、le BBSSection(SID int constraint pk_SID primary key,-SName char(10),- SMasterID int, - SStatement varchar(80), - SClickCount int constraint df_SClickCount default 0, - STopicCount int constraint df_STopicCount default 0 - )-alter table BBSTopicadd constraint fk1_BBSTopic_BBSUsers foreign key(tuid) r

8、eferences BBSUsers(Uid) -alter table BBSReplyadd constraint fk1_BBSReply_BBSTopic foreign key(RTID) references BBSTopic(TID) -alter table BBSReplyadd constraint fk1_BBSReply_BBSUsers foreign key(RUID) references BBSUsers(UID) -alter table BBSTopicadd constraint fk1_BBSTopic_BBSSection foreign key(ts

9、id) references BBSSection(Sid) -alter table BBSReplyadd constraint fk1_BBSReply_BBSSection foreign key(RSID) references BBSSection(Sid) -alter table BBSSectionadd constraint fk1_BBSSection_BBSUsers foreign key(SMasterID) references BBSUsers(Uid) -insertinto BBSUsers(uid,uname,upassword,uemail,UBirth

10、day,USex,UClass,UStatement,URegDate,UState,UPoint)values(1001 ,123456,1990,1990-12-12,1,ANGEL,2010-5-7,2,)insertinto BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)values(1002 ,654321,1965,1980-7-1,2,ANGEL,2011-4-7,2,4)insertinto BBSUsers(uid,uname,upassw

11、ord,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)values(1003 ,158664,conao,1997-1-03,2,ANGEL,2009-5-7,2,3)insertinto BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)values(1004 ,175175,death,1992-1-4,1,ANGEL,2008-5-4,2,2)insertinto BBSUse

12、rs(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)values(1005 ,198755,sgg,1994-1-4,1,ANGEL,2000-12-12,1,100)insertinto BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)values(1006 ,192445,dsfg,1994-12-14,2,fdfs,2000-12-12

13、,1,97)-Sectioninsertinto BBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)values(01 ,1006,5,5)insertinto BBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)values(02 ,1005,4,5)-topicinsertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickC

14、ount,TLastClickT)values(2001 ,01,1006,1,.,DEVIL MAY CRY,2011-1-10,5,2011-6-24)insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2002 ,01,1006,0,.,DEVIL MAY CRY,2011-4-10,4,2011-6-28)insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTo

15、pic,TContents ,TTime,TClickCount,TLastClickT)values(2003 ,01,1006,1,.,1,DEVIL MAY CRY,2011-1-10,3,2011-6-27)insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2004 ,01,1006,0,.,2,DEVIL MAY CRY,2011-1-10,2,2011-6-26)insertinto BBSTopic(tid,TS

16、ID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2005 ,01,1006,1,.,3,DEVIL MAY CRY,2011-4-10,1,2011-6-24)insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2011 ,02,1001,1,.,2011-1-11,6,2011-6-29)insertinto

17、 BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2012 ,02,1002,0,.,DN,2011-1-11,7,2011-6-23)insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2013,02,1003,0,.,2011-2-11,8,2011-6-23)insertin

18、to BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2014 ,02,1004,0,.,2011-1-21,1,2011-6-11)-replyinsertinto BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)values(9001 ,2001,01,1004,2011-6-24,1)insertinto BBSReply(RID,RTID,RS

19、ID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)values(9002 ,2011,02,1002,2011-6-29,1)insertinto BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)values(9003 ,2003,01,1005,2011-6-27,1)insertinto BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)values

20、(9004 ,2005,01,1005,2011-6-24,1)-select* from BBSUsersselect* from BBSSectionselect* from BBSTopicselect* from BBSReply-select uname as ,Uemail as ,UregDate as from bbsUsers where -datediff(yyyy,uregdate,getdate()2 dateadd(yyyy,2,uregdate)2 dateadd(yyyy,2,uregdate)getdate()order by UPoint desc-updat

21、e bbsUsers set UState =3 where uid=1004-select*from bbsUserswhere UState = 1 or UState = 3-select RTopic as ,RTime as from bbsreplyorder by RTime desc-BBSTopic5select top 5 TTopic as ,TClickCount as from bbstopicorder by TClickCount desc-update bbstopic set ttime = 2011-6-27 where tid = 2002update b

22、bstopic set ttime = 2011-6-26 where tid = 2003update bbstopic set ttime = 2011-6-28 where tid = 2011update bbstopic set TLastClickT =2011-6-30 where tid = 2012update bbstopic set ttime = 2011-6-24 where tid = 2012-7,2-select top 2 tuid asID ,count(*) as from bbstopicwhere datediff(dd,ttime,getdate()=7group by tuid- -bbsTopic-Max()Min()IDselect tuid as ID,max(TReplyCount) ,min(TReplyCount) as from bbstopicgroup by tuid-T-SQLbegin -T-SQL declare minPoint int - -,minPoint select minPoint=min(upoint) from bbsUsers where dateadd(yyyy,2,uregdate)getdate() -