简单BBS数据库的设计

基本信息表     

                                  BBSUsers  表
---------------------------------------------------------------------------------------------       属性名称        类型          长度        备注
       UID              varchar       16         用户账号------>（主键）
      UName          varchar          50          用户名
      UpassWord     varchar           16          账户密码
      UEmail           varchar        50         邮箱
      UBirthday       smalldate                 生日
      USex             nvarchar        4          性别------> in（男，女，保密）
      UClass           int                           账户级别 ------>（0级）    
      UStatement   nvarchar          100        个性签名
                                     BBSSession表
---------------------------------------------------------------------------------------------    SID                varchar      16          版块ID--> 主键
    SName           nvarchar        50          版块名称
    Smaster          varchar        16          用户ID
    Sclickcount      long           20          点击率 ------>（默认0）
    STopiccunt      long            20         主题点击率------>（默认0）
    SStatement     nvarchar         50         版块简介
---------------------------------------------------------------------------------------------                                      BBStopic表
---------------------------------------------------------------------------------------------   TID                 varchar         16        主题编号------>主键
   TNumber         int                           自增变量
   TSID               varchar          16        版块ID
   TUID               varchar          16         用户ID
   TReplycount     int                            回帖数------>默认0
   TEmotion         nvarchar           16        心情------>空
   TTopic            n varchar         50        主题题目
   TContent         text                         帖子内容
   TTime             datetime                   发帖时间 ------>getdate（）
   TClickcount      long               20        点击率 ------>默认为0
   TFlag              nvarchar         16        标签
   TLastclick        datetime                   最后浏览时间  ------>getdate（）
---------------------------------------------------------------------------------------------                                   BBSReplay表
---------------------------------------------------------------------------------------------   RID               varchar          16       楼主ID------>
  RNumber        int                          自增变量
  RTID             varchar            16       帖子ID
  RSID             varchar            16      版块ID
  RUID             varchar            16      用户ID
  REmotion      n varchar             16      心情------>空
  RTopic           nvarchar           50     帖子主题
  RContents      text                        回复内容
  RTime           datetime                 回复时间------>getdate（）
  RClickcount    int                          回复点击数  ------>默认0
---------------------------------------------------------------------------------------------

 

Sql 语句

--------------------------创建数据库---------------------------------use master
go
if exists(select * from sysdatabases where name='BBSDB')
Drop database BBSDB
create database BBSDB
on primary
(
 name='BBSDB_data',
 FileName='E:/SQLData/BBSDB_data.mdf',
 Size=5mb,
 Maxsize=1GB,
 Filegrowth=10%
)
log on
(
 name='BBSDB_log',
 FileName='E:/SQLData/BBSDB_log.ldf',
 Size=5mb,
 Maxsize=500mb,
 Filegrowth=20%
)
go
--------------------------创建数据BBSUsers表-------------------------/*----------------------------------------------------------------       属性名称        类型          长度        备注
       UID           varchar         16         用户账号------>（主键）
      UName          varchar         50         用户名
      UpassWord      varchar         16         账户密码
      UEmail         varchar         50         邮箱
      UBirthday      smalldate                  生日
      USex           varchar         4          性别------> in（男，女，保密）
      UClass         int                        账户级别 ------>（0级）    
      UStatement     nvarchar        100        个性签名
-----------------------------------------------------------------*/
use BBSDB
if exists(select * from sysobjects where name='BBSUsers')
drop table BBSUsers
create table BBSUsers
(
UID varchar(16) not null,
UName varchar(50) not null,
Upassword varchar(16)not null,
UEmail varchar (50) not null,
UBirthday smalldatetime,
USex varchar (4) not null,
UClass int not null,
UStatement nvarchar
)
go
--------------------------创建数据BBSSession表------------------------/*----------------------------------------------------------------    SID                varchar        16          版块ID--> 主键
    SName              varchar        50          版块名称
    Smaster            varchar        16          用户ID
    Sclickcount        long           20          点击率 ------>（默认0）
    STopiccunt         long           20          主题点击率------>（默认0）
    SStatement         nvarchar       50          版块简介
----------------------------------------------------------------- */
if exists(select * from sysobjects where name='BBSSession')
drop table BBSSession
create table BBSSession
(
SID varchar(16) not null,
SName varchar(50) not null,
Smaster varchar(16)not null,
Sclickcount int not null,
STopiccunt int not null,
SStatement nvarchar (50) not null
)
go
--------------------------创建数据BBStopic表-----------------------------------
/*----------------------------------------------------------------    TID                 varchar         16        主题编号------>主键
   TNumber             int                       自增变量
   TSID                varchar         16        版块ID
   TUID                varchar         16        用户ID
   TReplycount         int                       回帖数------>默认0
   TEmotion            nvarchar         16       心情------>空
   TTopic              nvarchar         50       主题题目
   TContent            text                      帖子内容
   TTime               datetime                  发帖时间 ------>getdate（）
   TClickcount         int                       点击率 ------>默认为0
   TFlag               varchar         16        标签
   TLastclickt         datetime                  最后浏览时间  ------>getdate（）
-----------------------------------------------------------------*/
if exists(select * from sysobjects where name='BBStopic')
drop table BBStopic
create table BBStopic
(
TID varchar(16) not null,
TNumber int identity(1,1) not null,
TSID varchar(16)not null,
TUID varchar(16) not null,
TReplycount int not null,
TEmotion nvarchar (16) not null,
TTopic nvarchar(50) not null,
TContent text not null,
TTime datetime  not null,
TClickcount int not null,
TFlag nvarchar(16)not null,
TLastclickt datetime not null,

)
go
--------------------------创建数据BBSReplay表------------------------/*-----------------------------------------------------------------  RID            varchar          16      楼主ID
  RNumber        int                      自增变量
  RTID           varchar          16      帖子ID
  RSID           varchar          16      版块ID
  RUID           varchar          16      用户ID
  REmotion       nvarchar         16      心情------>空
  RTopic         nvarchar         50      帖子主题
  RContents      text                     回复内容
  RTime          datetime                 回复时间------>getdate（）
  RClickcount    int                      回复点击数  ------>默认0
-----------------------------------------------------------------*/
if exists(select * from sysobjects where name='BBSReplay')
drop table BBSReplay
create table BBSReplay
(
RID varchar(16) not null,
RNumber int identity(1,1) not null,
RTID varchar(16)not null,
RSID varchar(16) not null,
RUID varchar(16) not null,
TEmotion nvarchar (16),
RTopic nvarchar(50) not null,
RContents text not null,
RTime datetime not null,
RClickcount int not null,
)
go
--------------------------为BBSUsers表创建约束-----------------------/*-----------------------------------------------------------------       属性名称        类型          长度        备注
      UID           varchar          16         用户账号------>（主键）
      UName          varchar         50         用户名
      UpassWord      varchar         16         账户密码
      UEmail         varchar         50         邮箱
      UBirthday      smalldate                  生日
      USex           varchar         4          性别------> in（男，女，保密）
      UClass         int                        账户级别 ------>（0级）    
      UStatement     nvarchar        100        个性签名
-----------------------------------------------------------------*/
------------------创建主键约束-------->PK_BBSUSERS_UID
if exists(select * from sysobjects where name='PK_BBSUSERS_UID')
alter table BBSUsers drop constraint PK_BBSUSERS_UID
alter table BBSUsers add constraint PK_BBSUSERS_UID
primary key(UID)
go
------------------创建检查约束-------->CK_BBSUSERS_USEX
if exists(select * from sysobjects where name='CK_BBSUSERS_USEX')
alter table BBSUsers drop constraint CK_BBSUSERS_USEX
alter table BBSUsers add constraint CK_BBSUSERS_USEX
check(USex in('男','女','保密'))
go
------------------创建默认约束-------->DF_BBSUSERS_USEX
if exists(select * from sysobjects where name='DF_BBSUSERS_USEX')
alter table BBSUsers drop constraint DF_BBSUSERS_USEX
alter table BBSUsers add constraint DF_BBSUSERS_USEX
default('保密') for USex
go
------------------创建默认约束-------->DF_BBSUSERS_UCLASS
if exists(select * from sysobjects where name='DF_BBSUSERS_UCLASS')
alter table BBSUsers drop constraint DF_BBSUSERS_UCLASS
alter table BBSUsers add constraint DF_BBSUSERS_UCLASS
default('0级') for UClass
go

---------------------------------------------------------------------------------------------为BBSSession表创建约束----------------------/*-----------------------------------------------------------------    SID                varchar        16          版块ID--> 主键
    SName              varchar        50          版块名称
    Smaster            varchar        16          用户ID
    Sclickcount        long           20          点击率 ------>（默认0）
    STopiccunt         long           20          主题点击率------>（默认0）
    SStatement         nvarchar       50          版块简介
----------------------------------------------------------------------------- */
------------------创建主键约束-------->PK_BBSSESSION_SID
if exists(select * from sysobjects where name='PK_BBSSESSION_SID')
alter table BBSSession drop constraint PK_BBSSESSION_SID
alter table BBSSession add constraint PK_BBSSESSION_SID
primary key(SID)
go
------------------创建默认约束-------->DF_BBSSESSION_SCLICKCONUT
if exists(select * from sysobjects where name='DF_BBSSESSION_SCLICKCONUT')
alter table BBSSession drop constraint DF_BBSSESSION_SCLICKCONUT
alter table BBSSession add constraint DF_BBSSESSION_SCLICKCONUT
default(0) for Sclickcount
go
------------------创建默认约束-------->DF_BBSSESSION_STOPICCONUT
if exists(select * from sysobjects where name='DF_BBSSESSION_STOPICCONUT')
alter table BBSSession drop constraint DF_BBSSESSION_STOPICCONUT
alter table BBSSession add constraint DF_BBSSESSION_STOPICCONUT
default(0) for STopiccunt
go
------------------创建外键约束-------->FK_BBSSESSION_SMASTER
if exists(select * from sysobjects where name='FK_BBSSESSION_SMASTER')
alter table BBSSession drop constraint FK_BBSSESSION_SMASTER
alter table BBSSession add constraint FK_BBSSESSION_SMASTER
foreign key(Smaster) references BBSUsers (UID)
go

--------------------------为BBStopic表创建约束------------------------/*-----------------------------------------------------------------   TID                 varchar         16        主题编号------>主键
   TNumber             int                       自增变量
   TSID                varchar         16        版块ID
   TUID                varchar         16        用户ID
   TReplycount         int                       回帖数------>默认0
   TEmotion            nvarchar         16       心情------>空
   TTopic              nvarchar         50       主题题目
   TContent            text                      帖子内容
   TTime               datetime                  发帖时间 ------>getdate（）
   TClickcount         int                       点击率 ------>默认为0
   TFlag               varchar         16        标签
   TLastclickt          datetime                  最后浏览时间  ------>getdate（）
-----------------------------------------------------------------*/
------------------创建主键约束-------->PK_BBSTOPIC_TID
if exists(select * from sysobjects where name='PK_BBSTOPIC_TID')
alter table BBStopic drop constraint PK_BBSTOPIC_TID
alter table BBStopic add constraint PK_BBSTOPIC_TID
primary key(TID)
go
------------------创建默认约束-------->PK_BBSTOPIC_TREPLYCOUNT
if exists(select * from sysobjects where name='PK_BBSTOPIC_TREPLYCOUNT')
alter table BBStopic drop constraint PK_BBSTOPIC_TREPLYCOUNT
alter table BBStopic add constraint PK_BBSTOPIC_TREPLYCOUNT
default(0) for TReplycount
go
------------------创建默认约束-------->PK_BBSTOPIC_TCLICKCOUNT
if exists(select * from sysobjects where name='PK_BBSTOPIC_TCLICKCOUNT')
alter table BBStopic drop constraint PK_BBSTOPIC_TCLICKCOUNT
alter table BBStopic add constraint PK_BBSTOPIC_TCLICKCOUNT
default(0) for TClickcount
go
------------------创建默认约束-------->DF_BBSTOPIC_TTIME
if exists(select * from sysobjects where name='DF_BBSTOPIC_TTIME')
alter table BBStopic drop constraint DF_BBSTOPIC_TTIME
alter table BBStopic add constraint DF_BBSTOPIC_TTIME
default(getdate()) for TTime
go
------------------创建默认约束-------->DF_BBSTOPIC_TLASTCLICKT
if exists(select * from sysobjects where name='DF_BBSTOPIC_TLASTCLICKT')
alter table BBStopic drop constraint DF_BBSTOPIC_TLASTCLICKT
alter table BBStopic add constraint DF_BBSTOPIC_TLASTCLICKT
default(getdate()) for TLastclickt
go
------------------创建外键约束-------->FK_BBSTOPIC_TUID
if exists(select * from sysobjects where name='FK_BBSTOPIC_TUID')
alter table BBStopic drop constraint FK_BBSTOPIC_TUID
alter table BBStopic add constraint FK_BBSTOPIC_TUID
foreign key(TUID) references BBSUsers (UID)
go
------------------创建外键约束-------->FK_BBSTOPIC_TSID
if exists(select * from sysobjects where name='FK_BBSTOPIC_TSID')
alter table BBStopic drop constraint FK_BBSTOPIC_TSID
alter table BBStopic add constraint FK_BBSTOPIC_TSID
foreign key(TSID) references BBSSession (SID)
go
--------------------------为BBSReplay表创建约束

/*-----------------------------------------------------------------   RID            varchar          16      楼主ID
  RNumber        int                      自增变量
  RTID           varchar          16      帖子ID
  RSID           varchar          16      版块ID
  RUID           varchar          16      用户ID
  REmotion       nvarchar         16      心情------>空
  RTopic         nvarchar         50      帖子主题
  RContents      text                     回复内容
  RTime          datetime                 回复时间------>getdate()
  RClickcount    int                      回复点击数  ------>默认0
-----------------------------------------------------------------*/
------------------创建主键约束-------->PK_BBSREPLAY_RID
if exists(select * from sysobjects where name='PK_BBSREPLAY_RID')
alter table BBSReplay drop constraint PK_BBSREPLAY_RID
alter table BBSReplay add constraint PK_BBSREPLAY_RID
primary key(RID)
go
------------------创建默认约束-------->DF_BBSREPLAY_RTIME
if exists(select * from sysobjects where name='DF_BBSREPLAY_RTIME')
alter table BBSReplay drop constraint DF_BBSREPLAY_RTIME
alter table BBSReplay add constraint DF_BBSREPLAY_RTIME
default(getdate()) for RTime
go
------------------创建默认约束-------->PK_BBSREPLAY_RCLICKCOUNT
if exists(select * from sysobjects where name='PK_BBSREPLAY_RCLICKCOUNT')
alter table BBSReplay drop constraint PK_BBSREPLAY_RCLICKCOUNT
alter table BBSReplay add constraint PK_BBSREPLAY_RCLICKCOUNT
default(0) for RClickcount
go
------------------创建外键约束-------->FK_BBSREPLAY_RTID
if exists(select * from sysobjects where name='FK_BBSREPLAY_RTID')
alter table BBSReplay drop constraint FK_BBSREPLAY_RTID
alter table BBSReplay add constraint FK_BBSREPLAY_RTID
foreign key(RTID) references BBSTopic (TID)
go
------------------创建外键约束-------->FK_BBSREPLAY_RSID
if exists(select * from sysobjects where name='FK_BBSREPLAY_RSID')
alter table BBSReplay drop constraint FK_BBSREPLAY_RSID
alter table BBSReplay add constraint FK_BBSREPLAY_RSID
foreign key(RSID) references BBSSession (SID)
go
------------------创建外键约束-------->FK_BBSREPLAY_RUID
if exists(select * from sysobjects where name='FK_BBSREPLAY_RUID')
alter table BBSReplay drop constraint FK_BBSREPLAY_RUID
alter table BBSReplay add constraint FK_BBSREPLAY_RUID
foreign key(RUID) references BBSUsers(UID)
go