--create database --d:project 10-13
use master
go
if exists (select* from sysdatabases where name='bbsDB')
DROP database bbsDB
--exec xp_cmdshell 'mkdir d:\project'
create database bbsDB
on primary
(
name='bbsDB_data',
filename='d:\project\bbsDB_data.mdf',
size=10mb,
filegrowth=20%
)
go
-- bbsUsers
set nocount on
use bbsDB
go
if exists (select*from sysobjects where name='bbsUsers')
drop table bbsUsers
create table bbsUsers
(
UID int identity (1,1) not null,
Uname varchar(15) not null,
Upassword varchar(10) not null,
Uemail varchar(20),
Ubirthday datetime not null,
Usex bit 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 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 values('卡因','888888','00@hotmail.com','1978-07-09','1','1','00','2007-10-10','0','0')
insert into bbsUsers values('可卡因','HYXS007','ss@hotmail.com','1978-07-09','1','1','我要去公园','2007-10-10','1','200')
insert into bbsUsers values('心酸果冻','888888','yy@hotmail.com','1980-02-12','0','2','走遍天涯海角','2007-10-10','2','600')
insert into bbsUsers values('冬篱儿','fangdong','bb@sohu.com','1976-10-03','1','3','爱迷失在天堂','2007-10-10','4','1200')
insert into bbsUsers values('Super','master','dd@p.com','1977-05-16','1','5','BBS大斑竹','2007-10-10','1','5000')
go
--bbsSection
set nocount on
use bbsDB
go
if exists(select *from sysobjects where name='bbsSection')
drop table bbsSection
go
create table bbsSection
(
SID int identity(1,1) not null,
Sname varchar(20) not null,
SmasterID int not null,
Sprofile varchar(50),
SclickCount int,
StopicCount int
)
go
alter table bbsSection
add constraint pk_SID primary key(SID),
constraint fk_SmasterID foreign key(SmasterID)references bbsUsers(UID),
constraint df_SclickCount default (0)for SclickCount,
constraint df_StopicCount default(0) for StopicCount
go
insert into bbsSection values('Java技术','3','讨论Java','500','1')
insert into bbsSection values('.Net','5','讨论Web Service','800','1')
insert into bbsSection values('00','5','00','00','0')
insert into bbsSection values('00','5','00','00','0')
insert into bbsSection values('Linux/Unix社区','5','包含系统维护','0','0')
--bbsTopic
set nocount on
use bbsDB
go
if exists(select*from sysobjects where name='bbsTopic')
drop table bbsTopic
go
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(50) not null,
Ttime datetime,
TclickCount int,
Tstate int not null,
TlastReply datetime
)
go
alter table bbsTopic
add constraint pk_TID primary key(TID),
constraint fk_SID foreign key(TsID) references bbsSection(SID),
constraint fk_UID foreign key(TuID) references bbsUsers(UID),
constraint ck_Tcontents check(len(Tcontents)>6),
constraint ck_Time check(TlastReply>Ttime),
constraint df_TreplyCount default(0)for TreplyCount,
constraint df_Time default(getdate()) for Ttime,
constraint df_TclickCount default(0)for TclickCount,
constraint df_Tstate default(1)for Tstate
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','0','2','部署.Net中的问题','项目包括windows','2007-10-11','0','1','2007-10-12')
go
--bbsReply
set nocount on
use bbsDB
go
if exists(select*from sysobjects where name='bbsReply')
drop table bbsReply
go
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(50) not null,
Rtime datetime,
RclickCount int
)
go
alter table bbsReply
add constraint pk_RID primary key (RID),
constraint fk_RtID foreign key(RtID) references bbsTopic(TID),
constraint fk_RsID foreign key(RsID) references bbsSection(SID),
constraint fk_RuID foreign key(RuID) references bbsUsers(UID),
constraint ck_Rcontents check(len(Rcontents)>6),
constraint df_Rtime default(getdate())for Rtime
go
insert into bbsReply values('1','1','5','2','数据库连接池在','2007-10-10','100')
insert into bbsReply values('1','1','4','4','piblic static Data','2007-10-10','200')
insert into bbsReply values('1','1','5','2','数据库连接池在','2007-10-10','100')
insert into bbsReply values('1','1','5','2','数据库连接池在','2007-10-10','100')
insert into bbsReply values('2','2','2','3','帮测试人员架AS','2007-10-10','0')
go
delete from bbsUsers where UID=1
delete from bbsSection where SID=3 OR SID=4
delete from bbsReply where RID=3 OR RID=4
go
--------------------------------第三章-----------------------------------------------
--3-1
set nocount on
use bbsDB
go
print '版本号为：'+@@version
print '服务器名称'+@@servername
--update bbsUsers set Upassword='1234' where Uname='可卡因'
--print'执行上条语句产生的错误号为：'+convert(varchar(5),@@error)
declare @id int
declare @Upoint int
select @id=UID from bbsUsers where Uname='可卡因'
select @Upoint=Upoint from bbsUsers where Uname='可卡因'
print'个人资料如下：'+convert(varchar(5),@Upoint)
select 昵称=Uname,等级=Uclass, 个人说明=Uremark, 积分=Upoint from bbsUsers
where UID=@id
print'发帖如下'
select 发帖时间=Ttime, 点击率=TclickCount, 主题=Ttopic, 内容=Tcontents
from bbsTopic where TuID=@id
print'回帖如下：'
select 回帖时间=Rtime, 点击率=RclickCount, 回帖内容=Rcontents
from bbsReply where RuID=@id
print 'fen is'+convert(varchar(5),@Upoint)
if(@Upoint>30)
print'有权发帖'
else
print'无权发帖'
go
--3-2
set nocount on
declare @avgPoint float
declare @score int
set @score=0
while(1=1)
begin
update bbsUsers set Upoint=Upoint+50 where Ustate<>4
set @score=@score+50
select @avgPoint=avg(Upoint)from bbsUsers
if(@avgPoint>2000)
break
end
print'add score is'+convert(varchar(5),@score)
update bbsUsers set Uclass=case
when Upoint<500 then 1
when Upoint between 500 and 1000 then 2
when Upoint between 1001 and 2000 then 3
when Upoint between 2001 and 4000 then 4
when Upoint between 4001 and 5000 then 5
else 6
end
select 昵称=Uname, 星级=case
when Uclass=0 then''
when Uclass=1 then'*'
when Uclass=2 then'**'
when Uclass=3 then'***'
when Uclass=4 then'****'
when Uclass=5 then'*****'
else '******'
end,
积分=Upoint from bbsUsers
go
--3-3
set nocount on
declare @id int
select @id=UID from bbsUsers where Uname='可卡因'
declare @sendNo int
select @sendNo=count(*) from bbsTopic where TuID=@id
declare @replyNo int
select @replyNo=count(*) from bbsReply where RuID=@id
declare @no int
set @no=@sendNo+@replyNo
if @sendNo>0
begin
print'发帖数是：'+convert(varchar(5),@sendNo)
select 发帖时间=Ttime, 点击率=TclickCount, 主题=Ttopic, 内容=Tcontents
from bbsTopic where TuID=@id
end
else
begin
print'发帖数是0'
end
if @replyNo>0
begin
print '回帖数是：'+convert(varchar(5),@replyNo)
select 回帖时间 =Rtime,点击=RclickCount, 内容=Rcontents
from bbsReply where RuID=@id
end
else
begin
print'回帖数是0'
end
declare @jibie varchar(20)
if(@no<1)
set @jibie='新手上路'
if(@no>=1 and @no<=20)
set @jibie='侠客'
if(@no>=21 and @no<=30)
set @ji
