BBS源代碼.doc

-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%,-maxsize = 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 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 20 , - 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(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 BBSReplygocreate 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 table 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) references 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(tsid) 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,UBirthday,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,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)values(1003 ,158664,,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,,1992-1-4,1,ANGEL,2008-5-4,2,2)insertinto BBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)values(1005 ,198755,,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,,1994-12-14,2,fdfs,2000-12-12,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,TClickCount,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,TTopic,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,TSID,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 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)insertinto 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,RSID,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(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-update 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 bbstopic 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-SQLdeclare minPoint int -,minPointselect minPoint=min(upoint) from bbsUsers where dateadd(yyyy,2,uregdate)getdate()-while(minPoint100)begin update bbsUsers set Upoint=Upoint+2 where dateadd(yyyy,2,uregdate)getdate() and Upoint198 select minPoint=min(Upoint) from bbsUSers where dateadd(yyyy,2,uregdate)1000 then Awhen upoint500 then Bwhen upoint200 then Cwhen upoint100 then Delse E end from bbsUsersendgo- cursor-: -1. declare -2. open -3. fetch -4. close -deallocate-declare cursor for select -declare cur_bbs cursor for select uid,uname,upoint from bbsUSers-declare uid int,uname char(20),upoint intopen cur_bbs -fetch cur_bbs into uid,uname,upoint -while(fetch_status=0) -fetchbegin print convert(varchar(10),uid)+*+uname+*+convert(varchar(10),upoint) fetch next from cur_bbs into uid,uname,upoint -endclose cur_bbs -deallocate cur_bbs - ,goselect * from bbsSection-if exists(select * from sysobjects where name=proc_bbs and type=P) drop procedure proc_bbsgo-create procedure proc_bbs - masterID int -asbegin declare bbs_cur cursor for select t1.sname ,t2.uname ,t2.upoint from bbsSection t1,bbsUsers t2 where t1.smasterID=t2.uid and t1.sMasterID=masterID - declare sname varchar(10),uname varchar(10),upoint int - open bbs_cur - fetch bbs_cur into sname,uname,upoint print while(fetch_status=0) begin print sname+ * +uname+ * +convert(varchar(10),upoint) fetch next from bbs_cur into sname,uname,upoint end close bbs_cur - deallocate bbs_cur -endgo-exec sp_helptext proc_bbs-exec proc_bbs 2-exec sp_stored_proceduresuse mastergoexec xp_cmdshell md d:my_data- trigger- DML (insert ,delete ,update)- inserted deleted select * from bbsTopicselect * from bbsSection/*101*/-select * from sysobjects where type=tr-drop trigger tri_users_insert-if exists(select * from sysobjects where name=insert_topic_tri and type=tr) drop trigger insert_topic_trigo-create trigger insert_topic_trion bbsTopic for insert -asbegin declare uid int,sid int -IDID select uid=TUID,sid=TSID from inserted -BBSUsersBBSSection update bbsUsers set Upoint=Upoint+10 where uid=uid update bbsSection set STopicCount=STopicCount+1 where sid=sid print end go-insertinto BBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents ,TTime,TClickCount,TLastClickT)values(2008 ,01,1006,0,.,1,getdate(),1,getdate()select * from bbsUSersselect * from bbsSection-if exists(select * from sysobjects where name=insert_topic_tri and type=tr) drop trigger insert_topic_trigo-create trigger insert_topic_trion BBSReply for insert -asbegin declare uid int,sid int,tid int -IDID select uid=RUID,sid=RSID,tid=RTiD from inserted -BBSUsersBBSSectionBBSTopic update bbsUsers set Upoint=Upoint+1 where uid=uid update BBSTopic set TReplyCount=TReplyCount+1 where tid=tid update bbsSection set STopicCount=STopicCount+1 where sid=sid print end go-insertinto BBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)values(9005 ,2005,01,1005,getdate(),1)select * from bbsUSerssel