 |
|
|
 首页 |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
/*数据结构*/ /*bbs用户表*/ if exists(select * from sysobjects where id = object_id(\\\'BBSUser\\\')) drop table BBSUser go create table BBSUser ( id int identity primary key , UserName varchar(20) default \\\'\\\' not null , Password varchar(10) default \\\'\\\' not null , Email varchar(100) default \\\'\\\' not null , Homepage varchar(150) default \\\'\\\' not null , Signature varchar(255) default \\\'\\\' not null , SignDate datetime default getdate() not null , Point int default 0 not null ) go create index ix_bbsuser on bbsuser (id , username , password) /*bbs表情表*/ if exists(select * from sysobjects where id = object_id(\\\'Face\\\')) drop table Face go create table Face ( id tinyint identity primary key , Face varchar(30) default \\\'\\\' not null ) go /*bbs表*/ if exists(select * from sysobjects where id = object_id(\\\'BBS\\\')) drop table BBS go create table BBS ( id int identity primary key , RootID int default 0 not null , --根ID FatherID int default 0 not null , --父ID Layer tinyint default 0 not null , --层 OrderNum float(53) default 0 not null , --排序基数 UserID int default 0 not null , --发言人ID ForumID tinyint default 1 not null , --版面ID Subject varchar(255) default \\\'\\\' not null , --主题 Content text default \\\'\\\' not null , --内容 FaceID tinyint default 1 not null , --表情 Hits int default 0 not null , --点击数 IP varchar(20) default \\\'\\\' not null , --发贴IP Time datetime default getdate() not null , --发表时间 Posted bit default 0 not null --是否精华贴子 ) go create index ix_bbs on bbs(id , rootid ,layer , fatherid , subject,posted) with DROP_EXISTING create index ix_bbs1 on bbs(fatherid , forumid) with DROP_EXISTING create index ix_bbs2 on bbs(forumid , rootid , ordernum) with drop_existing /*精华区*/ if exists(select * from sysobjects where id = object_id(\\\'PostedTopic\\\')) drop table PostedTopic go create table PostedTopic ( id int identity primary key , UserID int default 0 not null , --发言人ID ForumID tinyint default 1 not null , --版面ID Subject varchar(255) default \\\'\\\' not null , --主题 Content text default \\\'\\\' not null , --内容 FaceID tinyint default 1 not null , --表情 Hits int default 0 not null , --点击数 IP varchar(20) default \\\'\\\' not null , --发贴IP Time datetime default getdate() not null --发表时间 ) go /*forum版面表*/ if exists(select * from sysobjects where id = object_id(\\\'forum\\\')) drop table forum go create table Forum ( ID tinyint identity primary key , RootID tinyint default 0 not null , --根ID FatherID tinyint default 0 not null , --父ID Layer tinyint default 0 not null , --层 Title varchar(50) default \\\'\\\' not null , --版面名称 Description varchar(255) default \\\'\\\' not null , --版面描述 MasterID int default 1 not null , --版主ID TopicCount int default 0 not null , --贴子总数 Time datetime default getdate() not null , --创建时间 IsOpen bit default 0 not null --是否开放 ) go insert into forum(rootid , fatherid , layer , title , description , masterid) values(1 , 0 , 0 , "谈天说地" , "在不违犯国家法律的情况下，你可以发表你自己的言论。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(2 , 0 , 0 , "体育" , "在不违犯国家法律的情况下，你可以对体育发表你自己的评论。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(1 , 1 , 1 , "笑话站" , "笑话，让你在工作间隙轻松一下。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(2,2 , 1 , "体育沙龙" , "体育总和评论。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(2,2 , 1 , "足球" , "足球评论。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(2,2 , 1 , "海牛俱乐部" , "海牛球迷的讨论园地。" , 1) select * from forum /*论坛通告表*/ if exists(select * from sysobjects where id = object_id(\\\'Notify\\\')) drop table Notify go create table Notify ( ID int identity primary key , TopicID int default 0 not null , Closed bit default 0 not null , ) go select * from notify delete from notify where id=5 /***********以下为存储过程************************************************************/ /*************************************************************************/ /* */ /* procedure : up_GetBBSInfo */ /* */ /* Description: 取得整个论坛的相关信息 */ /* */ /* Parameters: none */ /* */ /* Use table: forum , bbs , bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/3 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id(\\\'up_GetBBSInfo\\\')) drop proc up_GetBBSInfo go create proc up_GetBBSInfo as declare @ForumCount int declare @TopicCount int declare @UserCount int set nocount on select @ForumCount = count(*) from Forum where layer <> 0 select @TopicCount = count(*) from BBS select @UserCount = count(*) from BBSUser /*取得论坛本身信息*/ select \\\'ForumCount\\\' = @ForumCount , \\\'TopicCount\\\' = @TopicCount , \\\'UserCount\\\' = @UserCount go up_getbbsinfo /*************************************************************************/ /* */ /* procedure : up_GetForumInfo */ /* */ /* Description: 取得指定版面的相关信息 */ /* */ /* Parameters: @a_intForumID */ /* */ /* Use table: forum , bbs , bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/3 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id(\\\'up_GetForumInfo\\\')) drop proc up_GetForumInfo go create proc up_GetForumInfo @a_intForumID int as declare @intTopicCount int declare @intRootTopicCount int set nocount on if not exists(select * from Forum where id=@a_intForumID) return 0 select @intTopicCount = count(*) from bbs where forumid = @a_intForumID select @intRootTopicCount = count(*) from bbs where forumID=@a_intForumID and fatherid=0 select * , \\\'TopicCount\\\'=@intTopicCount , \\\'RootTopicCount\\\' = @intRootTopicCount from Forum where id = @a_intForumID set nocount off go select id , rootid , title , fatherid from forum /*************************************************************************/ /* */ /* procedure : up_GetPostedForumInfo */ /* */ /* Description: 取得指定版面精华区的相关信息 */ /* */ /* Parameters: @a_intForumID */ /* */ /* Use table: forum , bbs , bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/4/17 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id(\\\'up_GetPostedForumInfo\\\')) drop proc up_GetPostedForumInfo go create proc up_GetPostedForumInfo @a_intForumID int as declare @intTopicCount int declare @intRootTopicCount int set nocount on if not exists(select * from Forum where id=@a_intForumID) return 0 select @intTopicCount = count(*) from bbs where forumid = @a_intForumID and posted=1 select * , \\\'TopicCount\\\'=@intTopicCount , \\\'RootTopicCount\\\' = @intTopicCount from Forum where id = @a_intForumID set nocount off go 返回类别: 教程 上一教程: 完整的访问统计系统(一：数据库篇) 下一教程: 一种比较方便的ASP分页程序 您可以阅读与"BBS的数据结构和存储过程(一)"相关的教程: · BBS的数据结构和存储过程(二) · BBS的数据结构和存储过程(三) · 一个高效的数据分页的存储过程 · ASP 用存储过程实现数据分页 · 用存储过程实现ASP对数据库访问 |
  |
| 快精灵印艺坊 版权所有 |
首页 会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
|
||
