📄 bbs的数据结构和存储过程（一）.htm
字号:
,<BR>Signature varchar(255) default '' not null ,<BR>SignDate
datetime default getdate() not null ,<BR>Point int default 0 not
null<BR>)</P>
<P>go</P>
<P>create index ix_bbsuser on bbsuser (id , username ,
password)</P>
<P>/*bbs表情表*/<BR>if exists(select * from sysobjects where id =
object_id('Face'))<BR>drop table Face<BR>go</P>
<P>create table Face<BR>(<BR>id tinyint identity primary key
,<BR>Face varchar(30) default '' not null<BR>)<BR>go</P>
<P>/*bbs表*/<BR>if exists(select * from sysobjects where id =
object_id('BBS'))<BR>drop table BBS<BR>go</P>
<P>create table BBS<BR>(<BR>id int identity primary key
,<BR>RootID int default 0 not null , --根ID<BR>FatherID int default
0 not null , --父ID<BR>Layer tinyint default 0 not null ,
--层<BR>OrderNum float(53) default 0 not null , --排序基数<BR>UserID
int default 0 not null , --发言人ID<BR>ForumID tinyint default 1 not
null , --版面ID<BR>Subject varchar(255) default '' not null ,
--主题<BR>Content text default '' not null , --内容<BR>FaceID tinyint
default 1 not null , --表情<BR>Hits int default 0 not null ,
--点击数<BR>IP varchar(20) default '' not null , --发贴IP<BR>Time
datetime default getdate() not null , --发表时间<BR>Posted bit default
0 not null --是否精华贴子<BR>)<BR>go </P>
<P>create index ix_bbs on bbs(id , rootid ,layer , fatherid ,
subject,posted) with DROP_EXISTING <BR>create index ix_bbs1 on
bbs(fatherid , forumid) with DROP_EXISTING<BR>create index ix_bbs2
on bbs(forumid , rootid , ordernum) with drop_existing</P>
<P>/*精华区*/<BR>if exists(select * from sysobjects where id =
object_id('PostedTopic'))<BR>drop table PostedTopic<BR>go</P>
<P>create table PostedTopic<BR>(<BR>id int identity primary key
,<BR>UserID int default 0 not null , --发言人ID<BR>ForumID tinyint
default 1 not null , --版面ID<BR>Subject varchar(255) default '' not
null , --主题<BR>Content text default '' not null , --内容<BR>FaceID
tinyint default 1 not null , --表情<BR>Hits int default 0 not null ,
--点击数<BR>IP varchar(20) default '' not null , --发贴IP<BR>Time
datetime default getdate() not null --发表时间<BR>)<BR>go </P>
<P>/*forum版面表*/<BR>if exists(select * from sysobjects where id =
object_id('forum'))<BR>drop table forum<BR>go</P>
<P>create table Forum<BR>(<BR>ID tinyint identity primary key
,<BR>RootID tinyint default 0 not null , --根ID<BR>FatherID tinyint
default 0 not null , --父ID<BR>Layer tinyint default 0 not null ,
--层<BR>Title varchar(50) default '' not null ,
--版面名称<BR>Description varchar(255) default '' not null ,
--版面描述<BR>MasterID int default 1 not null , --版主ID<BR>TopicCount
int default 0 not null , --贴子总数<BR>Time datetime default getdate()
not null , --创建时间<BR>IsOpen bit default 0 not null
--是否开放<BR>)<BR>go</P>
<P>insert into forum(rootid , fatherid , layer , title ,
description , masterid) values(1 , 0 , 0 , "谈天说地" ,
"在不违犯国家法律的情况下，你可以发表你自己的言论。" , 1)<BR>insert into forum(rootid ,
fatherid , layer , title , description , masterid) values(2 , 0 ,
0 , "体育" , "在不违犯国家法律的情况下，你可以对体育发表你自己的评论。" , 1)<BR>insert into
forum(rootid , fatherid , layer , title , description , masterid)
values(1 , 1 , 1 , "笑话站" , "笑话，让你在工作间隙轻松一下。" , 1)<BR>insert into
forum(rootid , fatherid , layer , title , description , masterid)
values(2,2 , 1 , "体育沙龙" , "体育总和评论。" , 1)<BR>insert into
forum(rootid , fatherid , layer , title , description , masterid)
values(2,2 , 1 , "足球" , "足球评论。" , 1)<BR>insert into forum(rootid ,
fatherid , layer , title , description , masterid) values(2,2 , 1
, "海牛俱乐部" , "海牛球迷的讨论园地。" , 1)</P>
<P>select * from forum</P>
<P>/*论坛通告表*/<BR>if exists(select * from sysobjects where id =
object_id('Notify'))<BR>drop table Notify<BR>go</P>
<P>create table Notify<BR>(<BR>ID int identity primary key
,<BR>TopicID int default 0 not null ,<BR>Closed bit default 0 not
null ,<BR>)<BR>go<BR>select * from notify<BR>delete from notify
where id=5</P>
<P>/***********以下为存储过程************************************************************/</P>
<P>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_GetBBSInfo */<BR>/* */<BR>/* Description:
取得整个论坛的相关信息 */<BR>/* */<BR>/* Parameters: none */<BR>/* */<BR>/*
Use table: forum , bbs , bbsuser */<BR>/* */<BR>/* Author:
bigeagle@163.net */<BR>/* */<BR>/* Date: 2000/2/3 */<BR>/*
*/<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/</P>
<P>if exists(select * from sysobjects where id =
object_id('up_GetBBSInfo'))<BR>drop proc up_GetBBSInfo<BR>go</P>
<P>create proc up_GetBBSInfo<BR>as<BR>declare @ForumCount
int<BR>declare @TopicCount int<BR>declare @UserCount int</P>
<P>set nocount on<BR>select @ForumCount = count(*) from Forum
where layer <> 0<BR>select @TopicCount = count(*) from
BBS<BR>select @UserCount = count(*) from BBSUser</P>
<P>/*取得论坛本身信息*/<BR>select 'ForumCount' = @ForumCount ,
'TopicCount' = @TopicCount , 'UserCount' = @UserCount</P>
<P>go<BR>up_getbbsinfo<BR>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_GetForumInfo */<BR>/* */<BR>/*
Description: 取得指定版面的相关信息 */<BR>/* */<BR>/* Parameters:
@a_intForumID */<BR>/* */<BR>/* Use table: forum , bbs , bbsuser
*/<BR>/* */<BR>/* Author: bigeagle@163.net */<BR>/* */<BR>/* Date:
2000/2/3 */<BR>/* */<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/</P>
<P>if exists(select * from sysobjects where id =
object_id('up_GetForumInfo'))<BR>drop proc
up_GetForumInfo<BR>go</P>
<P>create proc up_GetForumInfo @a_intForumID int<BR>as<BR>declare
@intTopicCount int<BR>declare @intRootTopicCount int<BR>set
nocount on<BR>if not exists(select * from Forum where
id=@a_intForumID) return 0<BR>select @intTopicCount = count(*)
from bbs where forumid = @a_intForumID<BR>select
@intRootTopicCount = count(*) from bbs where forumID=@a_intForumID
and fatherid=0 <BR>select * , 'TopicCount'=@intTopicCount ,
'RootTopicCount' = @intRootTopicCount<BR>from Forum where id =
@a_intForumID<BR>set nocount off<BR>go <BR>select id , rootid ,
title , fatherid from
forum<BR>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_GetPostedForumInfo */<BR>/* */<BR>/*
Description: 取得指定版面精华区的相关信息 */<BR>/* */<BR>/* Parameters:
@a_intForumID */<BR>/* */<BR>/* Use table: forum , bbs , bbsuser
*/<BR>/* */<BR>/* Author: bigeagle@163.net */<BR>/* */<BR>/* Date:
2000/4/17 */<BR>/* */<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/</P>
<P>if exists(select * from sysobjects where id =
object_id('up_GetPostedForumInfo'))<BR>drop proc
up_GetPostedForumInfo<BR>go</P>
<P>create proc up_GetPostedForumInfo @a_intForumID
int<BR>as<BR>declare @intTopicCount int<BR>declare
@intRootTopicCount int<BR>set nocount on<BR>if not exists(select *
from Forum where id=@a_intForumID) return 0<BR>select
@intTopicCount = count(*) from bbs where forumid = @a_intForumID
and posted=1<BR>select * , 'TopicCount'=@intTopicCount ,
'RootTopicCount' = @intTopicCount<BR>from Forum where id =
@a_intForumID<BR>set nocount off<BR>go
<BR><BR><BR></P></BLOCKQUOTE></TD></TR>
<TR>
<TD class=p4 vAlign=top width="50%">
<BLOCKQUOTE>原作者：bigeagle<BR>来 源：chinaasp<BR>共有3909位读者阅读过此文<BR>【<A
href="http://bbs.aspsky.net/list.asp?boardid=1">发表评论</A>】
</BLOCKQUOTE></TD>
<TD class=p4 vAlign=top width="50%">
<P>
<LI><FONT color=#0772b1>上篇文章</FONT>：<A
href="http://www.aspsky.net/article/list.asp?id=2311">ASP编写完整的一个IP所在地搜索类</A>
<BR>
<LI><FONT color=#0772b1>下篇文章</FONT>：<A
href="http://www.aspsky.net/article/list.asp?id=2313">bbs的数据结构和存储过程（二）</A>
</LI></TD></TR>
<TR>
<TD bgColor=#297dff class=p4 height=20 width="50%"><FONT
color=#ceffff> → 本周热门</FONT></TD>
<TD bgColor=#297dff class=p4 width="50%"><FONT color=#ceffff> →
相关文章</FONT></TD></TR>
<TR>
<TD bgColor=#586011 colSpan=2 height=1><SPACER type="block"
width="1"></TD></TR>
<TR>
<TD colSpan=2 height=7></TD></TR>
<TR>
<TD class=p4 vAlign=top width="50%">
<LI><A href="http://www.aspsky.net/article/list.asp?id=1510"
target=_top title="SQL Server 7.0 入门（一）">SQL Server 7.0
入门（...</A>[<FONT color=red>7239</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1540"
target=_top title=PHP4实际应用经验篇(1)>PHP4实际应用经验篇(1)</A>[<FONT
color=red>7135</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1536"
target=_top
title=无组件文件上传代码实例（支持多文件上传及文件和input域混合上传）>无组件文件上传代码实例（支持多文件上...</A>[<FONT
color=red>6029</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=2557"
target=_top title=树型结构在ASP中的简单解决>树型结构在ASP中的简单解决</A>[<FONT
color=red>5757</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1545"
target=_top title=PHP4实际应用经验篇(6)>PHP4实际应用经验篇(6)</A>[<FONT
color=red>5599</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=2563"
target=_top title=一个老个写的无组件上传>一个老个写的无组件上传</A>[<FONT
color=red>5014</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1542"
target=_top title=PHP4实际应用经验篇(3)>PHP4实际应用经验篇(3)</A>[<FONT
color=red>4731</FONT>]<BR></LI></TD>
<TD class=p4 vAlign=top width="50%">
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2655">金额阿拉伯数字转换为中文的存储过程</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2609">存储过程使用大全</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2537">利用sql的存储过程实现dos命令的asp程序</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2401">显示数据库中的存储过程</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2314">bbs的数据结构和存储过程（三）</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2313">bbs的数据结构和存储过程（二）</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2312">bbs的数据结构和存储过程（一）</A><BR></LI></TD></TR>
<TR>
<TD colSpan=2 height=7></TD></TR></TBODY></TABLE>
<TD bgColor=#297dff width=1> </TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD bgColor=#297dff height=1><SPACER type="block"
width="1"></TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle height=30></TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle class=p2 width="100%">
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle class=p2 width="100%">
<P align=center><A
href="http://www.aspsky.net/produce/index.asp">客户服务</A> -- <A
href="http://www.aspsky.net/aspads.asp">广告合作</A> -- <A
href="http://www.aspsky.net/about.asp">关于本站</A> -- <A
href="http://www.aspsky.net/tell.asp">联系方法</A><BR><BR>动网先锋版权所有 <FONT
face=Verdana, size=1 Arial, Helvetica, sans-serif>Copyright ©
2000-2001 <B>AspSky<FONT color=#cc0000>.Net</FONT></B>, All Rights
Reserved .</FONT>
</P></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></CENTER></CENTER></BODY></HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -