BBS源代码.docx
《BBS源代码.docx》由会员分享,可在线阅读,更多相关《BBS源代码.docx(16页珍藏版)》请在冰豆网上搜索。
![BBS源代码.docx](https://file1.bdocx.com/fileroot1/2023-1/2/4f29f66e-aaaf-4b98-9102-abc991469faa/4f29f66e-aaaf-4b98-9102-abc991469faa1.gif)
BBS源代码
--½¨¿â£¬½¨±í£¬Ìí¼ÓÔ¼Êø
--²åÈë²âÊÔÊý¾Ý
--´æÔÚ¼ì²â
usemaster
go
ifexists(select*fromsysdatabaseswherename='BBS')
dropdatabasebbs
go
--½¨¿â
createdatabaseBBS
onprimary
(
name=BBS_data,--Âß¼Ãû
filename='d:
\BBS_data.mdf',--ÎïÀíÎļþ
size=20MB,--³õʼ´óС
filegrowth=10%,--ÎļþÔö³¤ÂÊ
maxsize=200MB--³õʼ´óС
)
logon
(
name=bbs_log,--Âß¼Ãû
filename='d:
\bbs_data.ldf',--ÎïÀíÎļþ
size=10mb,--³õʼ´óС
filegrowth=10%,--ÎļþÔö³¤ÂÊ
maxsize=100MB--×î´ó³ß´ç
)
useBBS
go
ifexists(select*fromsysobjectswherename='BBSUsers')
droptableBBSUsers
go
createtableBBSUsers
(
Uidintconstraintpk_Uidprimarykey,--Óû§±àºÅ
Unamechar(8)notnull,--Óû§ÐÕÃû
UPasswordchar(16)constraintdf_UPassworddefault'888888',--Óû§ÃÜÂë
UEmailchar(20)notnullconstraintck_Uemailcheck(Uemaillike'%@%.%'),--Óû§EMAIL
UBirthdaydatetimenotnull,--Óû§ÉúÈÕ
Usexintconstraintdf_Usexdefault'1',--Óû§ÐÔ±ð
UClassintconstraintdf_UClassdefault'1',--Óû§µÈ¼¶
UStatementvarchar(150)notnull,--Óû§ËµÃ÷
URegDatedatetimenotnulldefaultgetdate(),--Óû§×¢²áʱ¼ä
UStatetinyintconstraintdf_UStatedefault'1',--Óû§×´Ì¬
UPointintconstraintdf_UPointdefault'20',--Óû§»ý·Ö
constraintck_UPasswordcheck(UPasswordlike'______')
)
ifexists(select*fromsysobjectswherename='BBSTopic')
droptableBBSTopic
go
createtableBBSTopic
(
tidintconstraintpk_tidprimarykey,--Ö÷Ìù±àºÅ
tsidintnotnull,--Ö÷Ìù°å¿é±àºÅ
tuidintnotnull,--Ö÷ÌùÓû§±àºÅ
treplycountintnotnull,--Ö÷Ìù»Ø¸´´ÎÊý
temotionchar(10)notnull,--Ö÷Ìù±íÇé
TTopicvarchar(16)notnullCONSTRAINTck_ttcheck(TTopicnotlike'%''%'),--Ö÷Ìù±êÌâ
TContentstextnotnull,--Ö÷ÌùÄÚÈÝ
TTimedatetimenotnulldefaultgetdate(),--·¢Ìûʱ¼ä
TClickCountintnotnull,--µã»÷´ÎÊý
TLastClickTdatetimenotnull,
CONSTRAINT[CK_TLC]CHECK([TLastClickT]>=TTime)--Ö÷Ìù×îºóµã»÷ʱ¼ä
)
ifexists(select*fromsysobjectswherename='BBSReply')
droptableBBSReply
go
createtableBBSReply
(
RIDintconstraintpk_RIDprimarykey,--»Ø¸´±àºÅ
RTIDint,--»Ø¸´Ìû×Ó±àºÅ
RSIDint,--»Ø¸´°æ¿é±àºÅ
RUIDint,--»Ø¸´Óû§±àºÅ
REmotionchar(10),--»Ø¸´±íÇé
RTopicvarchar(20),--»ØÌûÖ÷Ìâ
RContentstext,--»ØÌûÄÚÈÝ
RTimedatetimedefaultgetdate(),--»ØÌûʱ¼ä
RClickCountint,--»ØÌûµã»÷´ÎÊý
)
ifexists(select*fromsysobjectswherename='BBSSection')
droptableBBSSection
go
createtableBBSSection
(
SIDintconstraintpk_SIDprimarykey,--°æ¿é±àºÅ
SNamechar(10),--°æ¿éÃû³Æ
SMasterIDint,--°æÖ÷±àºÅ
SStatementvarchar(80),--°æ¿é˵Ã÷
SClickCountintconstraintdf_SClickCountdefault'0',--°æ¿éµã»÷´ÎÊý
STopicCountintconstraintdf_STopicCountdefault'0'--°æ¿éÖ÷ÌâÊý
)
--ÁªÏµ£º
altertableBBSTopic
addconstraintfk1_BBSTopic_BBSUsersforeignkey(tuid)referencesBBSUsers(Uid)--Ò»¸öÓû§¿ÉÒÔ·¢±í¶àƪÖ÷ÌûÄÚÈÝ
altertableBBSReply
addconstraintfk1_BBSReply_BBSTopicforeignkey(RTID)referencesBBSTopic(TID)--һƪÖ÷ÌûÄܹ»¶ÔÓ¦¶àÌõ»ØÌû
altertableBBSReply
addconstraintfk1_BBSReply_BBSUsersforeignkey(RUID)referencesBBSUsers(UID)--Ò»¸öÓû§¿ÉÒÔ·¢±í¶àÌõ»ØÌûÄÚÈÝ
altertableBBSTopic
addconstraintfk1_BBSTopic_BBSSectionforeignkey(tsid)referencesBBSSection(Sid)--Ò»¸ö°æ¿é¿ÉÒÔ°üº¬¶àƪÖ÷Ìû
altertableBBSReply
addconstraintfk1_BBSReply_BBSSectionforeignkey(RSID)referencesBBSSection(Sid)--Ò»¸ö°æ¿é¿ÉÒÔ°üº¬¶àƪ»ØÌû
altertableBBSSection
addconstraintfk1_BBSSection_BBSUsersforeignkey(SMasterID)referencesBBSUsers(Uid)--Ò»¸öÓû§¿ÉÒÔ¹ÜÀí¶à¸ö°æ¿é
--²åÈëÊý¾Ý
insert
intoBBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1001','ÕÅÁú','123456','1990@','1990-12-12','1','','ANGEL','2010-5-7','2','')
insert
intoBBSUsers(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')
insert
intoBBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1003','´Ì¿Í','158664','conao@','1997-1-03','2','','ANGEL','2009-5-7','2','3')
insert
intoBBSUsers(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')
insert
intoBBSUsers(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')
insert
intoBBSUsers(uid,uname,upassword,uemail,UBirthday,USex,UClass,UStatement,URegDate,UState,UPoint)
values('1006','ħÍõ','192445','dsfg@','1994-12-14','2','','fdfs','2000-12-12','1','97')
--Section
insert
intoBBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)
values('01','¹àË®ÀÁ','1006','˵·Ï»°µÄµØ·½','5','5')
insert
intoBBSSection(SID,SName,SMasterID,SStatement,SClickCount,STopicCount)
values('02','ÓÎÏ·Ìü','1005','˵ÓÎÏ·µÄµØ·½','4','5')
--topic
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2001','01','1006','1','.....','ÎÒÊÇħÍõ','DEVILMAYCRY','2011-1-10','5','2011-6-24')
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2002','01','1006','0','.....','¹àË®À´×Å~~','DEVILMAYCRY','2011-4-10','4','2011-6-28')
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2003','01','1006','1','.....','¹àË®À´×Å1~~','DEVILMAYCRY','2011-1-10','3','2011-6-27')
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2004','01','1006','0','.....','¹àË®À´×Å2~~','DEVILMAYCRY','2011-1-10','2','2011-6-26')
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2005','01','1006','1','.....','¹àË®À´×Å3~~','DEVILMAYCRY','2011-4-10','1','2011-6-24')
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2011','02','1001','1','.....','ÇóÖú','ÇëÎÊ¡£¡£¡£¡£','2011-1-11','6','2011-6-29')
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2012','02','1002','0','.....','ÌÖÂÛ','DNºÃÍæ²»£¿','2011-1-11','7','2011-6-23')
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2013','02','1003','0','.....','Ñо¿','ÄǸö¡£¡£¡£¡£','2011-2-11','8','2011-6-23')
insert
intoBBSTopic(tid,TSID,TUID,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TLastClickT)
values('2014','02','1004','0','.....','ÎÒÊÇ´ò½´Ó͵Ä~','¾ÍÊÇÕâÑù¡£¡£¡£¡£','2011-1-21','1','2011-6-11')
--reply
insert
intoBBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('9001','2001','01','1004','¡£¡£¡£¡£','ÕâÑùµÄ','Èç´ËÈç´Ë£¬Õâ°ãÕâ°ã','2011-6-24','1')
insert
intoBBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('9002','2011','02','1002','¡£¡£¡£¡£','ÕâÑùµÄ','Èç´ËÈç´Ë£¬Õâ°ãÕâ°ã','2011-6-29','1')
insert
intoBBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('9003','2003','01','1005','¡£¡£¡£¡£','ÕâÑùµÄ','Èç´ËÈç´Ë£¬Õâ°ãÕâ°ã','2011-6-27','1')
insert
intoBBSReply(RID,RTID,RSID,RUID,REmotion,RTopic,RContents,RTime,RClickCount)
values('9004','2005','01','1005','¡£¡£¡£¡£','ÕâÑùµÄ','Èç´ËÈç´Ë£¬Õâ°ãÕâ°ã','2011-6-24','1')
--²é¿´½á¹û¼°½Øͼ
select*fromBBSUsers
select*fromBBSSection
select*fromBBSTopic
select*fromBBSReply
--²éѯԪÀÏ
selectunameasÐÕÃû,Uemailasµç×ÓÓʼþ,UregDateas×¢²áÈÕÆÚ
frombbsUsers
where--datediff(yyyy,uregdate,getdate())>2
dateadd(yyyy,2,uregdate)
--ΪÁËÈÃϸöÖ¸ÁîºÃ¿´ÇåЧ¹û£¬ÐÞ¸ÄÊý¾Ý
updatebbsUserssetUBirthday='1980-7-1'whereuid=1002
--²éѯ½ñÌì¹ýÉúÈÕµÄÓû§
selectunameÐÕÃû,uBirthday³öÉúÈÕÆÚ
frombbsUsers
where--datepart(mm,ubirthday)=datepart(mm,getdate())and
--datepart(dd,ubirthday)=datepart(dd,getdate())
day(getdate())=day(ubirthday)andmonth(getdate())=month(ubirthday)
--°´½µÐòÏÔʾËùÓÐÔªÀÏÓû§(×¢²áʱ¼ä³¬¹ý2Äê)µÄ»ý·ÖÇ°4Ãû
--ÐÕÃû/ÐÔ±ð/»ý·Ö/×¢²áÈÕÆÚ
selectunameasÐÕÃû,Uemailasµç×ÓÓʼþ,UregDateas×¢²áÈÕÆÚ,UPointas»ý·Ö
frombbsUsers
where--datediff(yyyy,uregdate,getdate())>2
dateadd(yyyy,2,uregdate)orderbyUPointdesc
--ΪÁË·½±ã²éѯ£¬ÐÞ¸ÄÊý¾Ý
updatebbsUserssetUState='3'whereuid=1004
--²éѯËùÓÐÔÚÏßÓû§ºÍ½ûÑÔÓû§
select*
frombbsUsers
whereUState=1orUState=3
--°´»ØÌûʱ¼äµÄ½µÐòÏÔʾËùÓлØÌû±êÌâÓë»ØÌûʱ¼ä
selectRTopicas»ØÌû±êÌâ,RTimeas»ØÌûʱ¼ä
frombbsreply
orderbyRTimedesc
--ÁгöBBSTopic±íÖеã»÷´ÎÊý×î¸ßµÄÇ°5ÃûÌû×Ó
selecttop5TTopicasÖ÷Ìâ±êÌâ,TClickCountasÖ÷Ìùµã»÷´ÎÊý
frombbstopic
orderbyTClickCountdesc
--ÒòΪÊý¾ÝÔÒòÎÞ·¨ÏÔʾ£¬ÏȽøÐÐÊý¾ÝÐÞ¸Ä
updatebbstopicsetttime='2011-6-27'wheretid='2002'
updatebbstopicsetttime='2011-6-26'wheretid='2003'
updatebbstopicsetttime='2011-6-28'wheretid='2011'
updatebbstopicsetTLastClickT='2011-6-30'wheretid='2012'
updatebbstopicsetttime='2011-6-24'wheretid='2012'
--½üÒ»ÖÜÓû§·¢ÌûÅÅÃû
--¸ù¾ÝϵͳÈÕÆÚÏòÇ°ÍÆ7Ìì,Ö®Äڵķ¢ÌûÊýÅÅÃûÇ°2Ãû
--ÏÔʾÖÐÎÄÁÐÃû
selecttop2tuidasÓû§ID,count(*)as·¢ÌûÊý
frombbstopic
wheredatediff(dd,ttime,getdate())<=7
groupbytuid
--²éѯÓû§Ëù·¢Ìû×ÓµÄ×î¸ßºÍ×îµÍ»ØÌûÊý--bbsTopic
--Max()¡¢Min()¡¢°´Óû§ID·Ö×é
selecttuidasÓû§ID,max(TReplyCount)×î¶à»ØÌûÊý,min(TReplyCount)as×îµÍ»ØÌûÊý
frombbstopic
groupbytuid
--T-SQLÅú´¦Àí¿é
begin
--T-SQL±à³Ì
declare@minPointint--¶¨Òå±äÁ¿£¬±íʾԪÀÏ×îµÍ»ý·Ö
--²éѯËùÓÐÔªÀÏÓû§µÄ×îµÍ»ý·Ö,²¢¸³Öµ¸ø@minPoint
select@minPoint=min(upoint)frombbsUsers
wheredateadd(yyyy,2,uregdate)--