bbsUSE数据库脚本

 ------------建库------------
use master
go
if exists(select * from sysdatabases where name='bbsDB')
drop database bbsDB
go
/*
exec xp_cmdshell 'mkdir D:/project'
*/
create database bbsDB
on
(
name='bbsDB_data',
filename='D:/project/bbsDB_data.mdf',
size=10mb,
filegrowth=20%
)
log on
(
name='bbsDB_log',
filename='D:/project/bbsDB_data.ldf',
size=1mb,
maxsize=20mb,
filegrowth=10%
)
go

use bbsDB
go
if exists(select * from sysobjects where name='bbsUsers')
drop table bbsUsers
go
/*--建表bbsUsers--*/
create table bbsUsers
(
UID INT identity(1,1) not null,
Uname VARCHAR(15) NOT NULL,
Upassword VARCHAR(10),
Uemail VARCHAR(20),
Usex varchar(2) NOT NULL,
Uclass INT,
Uremark VARCHAR(20),
UregDate DATETIME NOT NULL,
Ustate INT NULL,
Upoint INT NULL
)
go

/*---添加约束---*/
alter table bbsUsers
add constraint pk_UID
primary key(UID)

alter table bbsUsers
add constraint df_Upassword
default (888888) for Upassword

alter table bbsUsers
add constraint ck_Usex
check (Usex=0 or Usex=1)
alter table bbsUsers
add constraint df_Usex
default (1) for Usex

alter table bbsUsers
add constraint df_Uclass
default (1) for Uclass

alter table bbsUsers
add constraint df_UregDate
default (getDate()) for UregDate

alter table bbsUsers
add constraint df_Ustate
default (0) for Ustate

alter table bbsUsers
add constraint df_Upoint
default (20) for Upoint

alter table bbsUsers
add constraint ck_Uemail
check (Uemail like '%@%')

alter table bbsUsers
add constraint ck_Upassword
check (len(Upassword)>=6)

go
/*---插入测试数据---*/
insert into bbsUsers
(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint)
select 'aaaa','abcc537','st@163.com',0,1,'我是百事通',1,100 UNION
select '可卡因','hyxs007','ss@hotmail.com',1,1,'我要去公安局自首',1,200

UNION
select '心酸果冻','888888','yy@hotmail.com',0,2,'牵皮瘦马闯天涯',2,600

UNION
select '东篱儿','fangdong','bb@sohu.com',1,3,'爱迷失在天堂',4,1200

UNION
select 'Supper','master','dd@p.com',1,5,'BBS大斑竹',1,5000

select * from bbsUsers
go

use bbsDB
go
if exists(select * from sysobjects where name='bbsSection')
drop table bbsSection
go
/*--建表bbsSection--*/
create table bbsSection
(
SID INT identity(1,1) NOT NULL,
Sname VARCHAR(32) NOT NULL,
SmasterID INT NOT NULL,
Sprofile VARCHAR(30),
SclickCount INT,
StopicCount INT ,
)
go

/*---添加约束---*/
alter table bbsSection
add constraint pk_SID
primary key(SID)

alter table bbsSection
add constraint fk_SmasterID
foreign key(SmasterID) references bbsUsers(UID)

alter table bbsSection
add constraint df_SclickCount
default (0) for SclickCount

alter table bbsSection
add constraint df_Stopiccount
default (0) for StopicCount
go

/*---插入测试数据---*/
insert into bbsSection
(Sname,SmasterID,Sprofile,SclickCount,StopicCount)
select 'java技术',2,'包含框架、开源…',500,1 UNION
select '.NET技术',3,'包含C#,ASP.NET,…',800,1 UNION
select 'unix技术',5,'包含系统维护与使用区…',0,0

select * from bbsSection
go

use bbsDB
go
if exists(select * from sysobjects where name='bbsTopic')
drop table bbsTopic
go

/*--建表bbsTopic--*/
create table bbsTopic
(
TID INT identity(1,1) NOT NULL,
TsID INT NOT NULL,
TuID INT NOT NULL,
TreplyCount INT,
Tface INT,
Ttopic varchar(20) not null,
Tcontents varchar(30) not null,
Ttime datetime,
TclickCount int,
Tstate int not null,
TlastReply datetime
)
go

/*---添加约束---*/
alter table bbsTopic
add constraint pk_TID
primary key(TID)

alter table bbsTopic
add constraint fk_TsID
foreign key(TsID) references bbsSection(SID)

alter table bbsTopic
add constraint fk_TuID
foreign key(TuID) references bbsUsers(UID)

alter table bbsTopic
add constraint df_TreplyCount
default (0) for TreplyCount

alter table bbsTopic
add constraint df_Ttime
default (getdate()) for Ttime

alter table bbsTopic
add constraint df_TclickCount
default (0) for TclickCount

alter table bbsTopic
add constraint df_Tstate
default (1) for Tstate

alter table bbsTopic
add constraint ck_TlastReply
check(TlastReply>Ttime)
go

/*---插入测试数据---*/
insert into bbsTopic
(TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate)
select 1,3,2,1,'java基础','java是一门……',200,1 UNION
select 2,2,0,2,'关于.net','.net中常用……' ,0,1

select * from bbsTopic
go

use bbsDB
go
if exists(select * from sysobjects where name='bbsReply')
drop table bbsReply
go

/*--建表bbsReply--*/
create table bbsReply
(
RID INT identity(1,1) NOT NULL,
RtID INT NOT NULL,
RsID INT NOT NULL,
RuID INT NOT NULL,
Rface INT,
Rcontents varchar(30) not null,
Rtime datetime,
RclickCount int
)
go

/*---添加约束---*/
alter table bbsReply
add constraint pk_RID
primary key(RID)

alter table bbsReply
add constraint fk_RtID
foreign key(RtID) references bbsTopic(TID)

alter table bbsReply
add constraint fk_RsID
foreign key(RsID) references bbsSection(SID)

alter table bbsReply
add constraint fk_RuID
foreign key(RuID) references bbsUsers(UID)

alter table bbsReply
add constraint ck_Rcontents
check (len(Rcontents)>=6)

alter table bbsReply
add constraint df_Rtime
default (getdate()) for Rtime
go

/*---插入测试数据---*/
insert into bbsReply
(RtID,RsID,RuID,Rface,Rcontents,RclickCount)
select 1,1,5,2,'jsp乱码…',100 UNION
select 1,1,4,4,'转换jsp,…',200 UNION
select 2,1,2,3,'.net很精彩…',0

select * from bbsReply
go