BBS源代码.docx
- 文档编号:6004356
- 上传时间:2023-01-02
- 格式:DOCX
- 页数:16
- 大小:19.15KB
BBS源代码.docx
《BBS源代码.docx》由会员分享,可在线阅读,更多相关《BBS源代码.docx(16页珍藏版)》请在冰豆网上搜索。
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) --
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- BBS 源代码