alter table bbsSection add constraint DF_StopicCount default(0) for StopicCount

alter table bbsSection add constraint Fk_SmasterID

foreign key(SmasterID) references bbsUsers(UID)

为bbsTopic表添加约束条件

alter table bbsTopic add  constraint PK_TID primary key(TID)

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 FK_TsID

foreign key(TsID) references bbsSection(SID)

alter table bbsTopic add constraint FK_TuID

foreign key(TuID) references bbsUsers(UID)

为bbsReply表添加约束条件

alter table bbsReply add constraint PK_RID primary key(RID)

alter table bbsReply add constraint DF_Rtime default(getdate()) for Rtime

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)

向四个表中录入数据

insert into bbsUsers(Uname,Upassword,Uemail,Ubirthday,Uremark,Upoint)

values('可卡因','HYXS007','ss@HotMail.com','1978-7-09','我要去公安局',50)

values('冬离儿','fangdong','bb@sohu.com','1976-10-03','爱迷失在天堂',1200)

insert into bbsUsers(Uname,Upassword,Uemail,Ubirthday,Uremark,Upoint)

values('Super','master','dd@p.com','1977-5-16','BBS大斑竹',5000)

select * from bbsUsers

go

insert into bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)

values ('JAVA技术',3,'讨论JAVA相关技术',500,1)

insert into bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)

values ('.net',2,'讨论Web Service',800,1)

insert into bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)

values ('LinusUnix社区',2,'包含系统维',10,2)

select * from bbsSection

go

insert into bbsTopic

values (1,3,2,1,'还是jsp中的问题','jsp文件中读取','2007-10-10',200,1,'2007-10-11')

insert into bbsTopic

values (2,2,2,2,'部署.net中的问题','项目包括windows','2007-10-11',0,1,'2007-10-12')

select * from bbsTopic

go

insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,Rtime,RclickCount)

values (1,1,3,2,'数据库联接池在','2007-10-10',100)

insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,Rtime,RclickCount)

values (1,1,4,4,'public static date','2007-10-10',200)

insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,Rtime,RclickCount)

values (2,2,2,3,'帮测试人员架as','2007-10-10',0)

select * from bbsReply

go    

创建视图：为了向客户显示信息友好，查询各表，要求字段全为中文字段名。      

create view vbbsUsers

as

select 用户编号=UID,用户昵称=Uname,密码=Upassword，电子邮件=Uemail，性别=Usex，等级=Uclass，备注=Uremark，注册日期=UregDate，状态=Ustate，积分=Upoint

from bbsUsers

go

select * from vbbsUsers

create view vbbsSection

as

select版块编号=SID，版块名称=Sname，版主的用户编号=SmasterID，版面简介=Sprofile，点击率=SclickCount，发贴数=StopicCount

from  bbsSection

go

select * from  vbbsSection                                     

create view vbbsTopic

as

select 贴子编号=TID，所在版块编号=TsID，发贴人编号=TuID，回复数量=TreplyCount，发贴表情=Tface，标题=Ttopic，正文=Tcontents，发贴时间=Ttime，点击数=TclickCount，状态=Tstate，最后回复时间=TlastReply

from bbsTopic

go

select * from vbbsTopic

create view vbbsReply

as

select贴子编号=RID，回复的主贴编号=RtID，所在版块编号=RsID，回贴人编号=RuID，回贴=Rface，正文Rcontents，回贴时间=Rtime，点击率=RclickCount

from bbsReply

go

select * from vbbsReply

上一页  [1] [2] [3] [4] [5] [6] 下一页