BBS源代码Word格式.docx

1、（name = BBS_data, -filename =d:BBS_data.mdf, -size = 20MB, -filegrowth=10%, -maxsize = 200MB -log onname =bbs_log, -bbs_data.ldfsize =10mb, -filegrowth = 10%,-maxsize = 100MB -use BBSif exists（select*from sysobjects where name =BBSUsersdrop table BBSUserscreate table BBSUsersUid int constraint pk_Ui

2、d 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 like%.%）,-EMAILUBirthday datetime not null,-Usex int constraint df_Usex default 1, -UClass int constraint df_UClass default UStatement varchar

3、（150）not null, -URegDate datetime not null default getdate（）, -UState tinyint constraint df_UState default UPoint int constraint df_UPoint default 20 , -constraint ck_UPassword check（UPassword like _BBSTopicdrop table BBSTopiccreate table BBSTopictid int constraint pk_tid primary key, -tsid int not

4、null, -tuid int not null, -treplycount int not null, -temotion char（10） not null, -TTopic varchar（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

5、CHECK （TLastClickT=TTime） -BBSReplydrop table BBSReplycreate table BBSReplyRID 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,-BBSSectiondrop table BBSSectioncreate table BBSSec

6、tionSID 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 -alter table BBSTopicadd constraint fk1_BBSTopic_BBSUsers foreign key（tuid） references BBSUsers（

7、Uid） -alter table BBSReplyadd constraint fk1_BBSReply_BBSTopic foreign key（RTID） references BBSTopic（TID） -add constraint fk1_BBSReply_BBSUsers foreign key（RUID） references BBSUsers（UID） -add constraint fk1_BBSTopic_BBSSection foreign key（tsid） references BBSSection（Sid） -add constraint fk1_BBSReply

8、_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,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint）values（1001 ,12345619901990-1

9、2-12ANGEL2010-5-72100265432119651980-7-12011-4-741003158664conao1997-1-032009-5-731004175175death1992-1-42008-5-41005198755sgg1994-1-42000-12-121001006192445dsfg1994-12-14fdfs97-Sectioninto BBSSection（SID,SName,SMasterID,SStatement,SClickCount,STopicCount）01502-topicinto BBSTopic（tid,TSID,TUID,TRepl

10、yCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT）2001.DEVIL MAY CRY2011-1-102011-6-2420022011-4-102011-6-28200312011-6-27200422011-6-262005320112011-1-1162011-6-292012DN72011-6-2320132011-2-11820142011-1-212011-6-11-replyinto BBSReply（RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime

11、,RClickCount）9001900290039004select* from BBSUsersselect* from BBSSectionselect* from BBSTopicselect* from BBSReplyselect uname as ,Uemail as ,UregDate as from bbsUsers where -datediff（yyyy,uregdate,getdate（）2 dateadd（yyyy,2,uregdate）getdate（）-update bbsUsers set UBirthday= where uid=1002select unam

12、e ,uBirthday from bbsUserswhere -datepart（mm,ubirthday）=datepart（mm,getdate（） and -datepart（dd,ubirthday）=datepart（dd,getdate（） day（getdate（）=day（ubirthday） and month（getdate（）=month（ubirthday） -（2）4-/,UPoint as order by UPoint descupdate bbsUsers set UState = where uid=1004select*where UState = 1 o

13、r UState = 3select RTopic as ,RTime as from bbsreplyorder by RTime descBBSTopic5select top 5 TTopic as ,TClickCount as from bbstopicorder by TClickCount desc-update bbstopic set ttime = where tid = update bbstopic set TLastClickT =2011-6-30-7,2-select top 2 tuid asID ,count（*） as where datediff（dd,ttime,getdate（）=7group by tuid -bbsTopic-Max（）Min（）IDselect tuid as ID,max（TReplyCount） ,min（TReplyCount） as -T-SQLbegin -T-SQL declare minPoint int - -,minPoint select minPoint=min（upoint） from bbsUsers where dateadd（yyyy,2,uregdate） -