--创建数据库
create database bbs
on
(
 name='bbs',
 size=6MB,
 filegrowth=1MB,
 maxsize=unlimited,
 filename='E:\DATA\bbs.mdf'
)
log on
(
name='bbs_log',
size=3MB,
 maxsize=unlimited,
 filename='E:\DATA\bbs_log.ldf'
)
--创建用户信息表
create table bbsUsers
(
Uid int identity(1,1),
uName varchar(2) not null unique,
uSex varchar(2) check(uSex in('男','女')) default('男') not null,
uAge int check(uAge>=15 and uAge<=60) not null,
uPoint int check(upoint>=0) not null
)
go
--创建主贴表
create table bbsTopic
(
tID int identity(1,1),
tUID int,
tSID int,
tTitle varchar(100) not null,
tMsg text not null,
time datetime,
tCount int
)
alter table bbsTopic
add constraint FK_bbsTopic_tUID foreign key(tUID) references bbsUsers(UID)
alter table bbsTopic
add constraint FK_bbsTopic_tSID foreign key(tSID) references bbsSection(tSID)
--创建回帖表
create table bbsReply
(
rID int identity(1,1),
rUID int,
rTID int,
rMsg text not null,
rTime datetime
)
alter table bbsReply
add constraint FK_bbsReply_rUID foreign key(rUID) references bbsUsers(rUID)
alter table bbsReply
add constraint FK_bbsReply_rTID foreign key(rTID) references bbsUsers(rTID)

--创建版块表
create table bbsSection
(
sID int identity(1,1),
sName varchar(10) not null,
sUid int
)
alter table bbsSection
add constraint FK_bbsSection_sUid foreign key(sUid) references bbsUsers(sUid)

insert into bbsUsers(UID,uName,uSex,uAge,uPoint)
values('小雨点','女','20','0'),('逍遥','男','18','4'),('七年级生','男','19','2')
select * into bbsPoint from bbsUsers
insert into bbsSection(sName )
values('技术交流','小雨点'),('读书世界','七年级生'),('生活百科','小雨点'),('八卦区','七年级生')
insert into bbsTopic(uName,sName,tTitle,tMsg,tTime,tCount)
values('逍遥','八卦区','范跑跑','谁是范跑跑','2008-7-8','1'),('七年级生','技术交流','.NET','与JAVA的区别是什么呀？','2008-9-1','2'),('小雨点','生活百科','今年夏天最流行什么','有谁知道今年夏天最流行','2008-9-10','0')
delete from bbsUsers where UID=2
update bbsUsers set uPoint=10 where UID=1
delete from bbsSection where sName=3
select * from bbsReply
truncate table bbsReply

--创建职工信息表
create table Staff
(
sID int identity(1,1),
sName varchar(2) not null unique,
sSex varchar(2) check(sSex in('男','女')) default('男') not null,
sGrade int default('1'),
sPhone int,
sAddress int
)
insert into Staff(sID,sName,sSex,sGrade,sPhone,sAddress)
values ('1','张三','男','1',null,null),('2','李四','男','2',null,null),('3','王二麻子','女','3',null,null)
select * from Staff
delete from Staff
--创建职工工资表
create table Salary
(
sID int identity(1,1),
sSalay int,
sPost int
)
alter table Salary
add constraint PK_Staff_sID foreign key(sID) references Salary(sID)