if exists(select * from sysdatabases where name='bbsDB')
drop database bbsDB
create database bbsDB
on
(
name='bbsDB_data',
filename='c:\bbsDB_data.mdf',
size=10mb,
filegrowth=20%
)
log on
(
name='bbsDB_log',
filename='c:\bbsDB_log.ldf',
size=1mb,
maxsize=20mb,
filegrowth=10%
)
use bbsDB
go
/*创建表bbsusers*/
if exists(select * from sysobjects where name='bbsUsers')
drop table bbsUers
create table bbsUsers
(
UID int identity(1,1) not null,
Uname varchar (15) not null,
Upassword varchar(10) not null,
Uemail varchar(20) not null,
Usex bit not null,
Uclass int ,
Uremark varchar(20) not null,
UregDate datetime not null,
Ustate int not null,
Upoint int not null
)
insert into bbsUsers values('可卡因','HYXS007','ss@hotmail.com',1,1,'我去公安局自首','2003-08-13 15:09:51.000',1,200)
insert into bbsUsers values('心酸果冻','88888888','yy@hotmail.com',0,2,'牵皮瘦马闯天涯','2004-09-13 15:09:51.000',2,600)
insert into bbsUsers values('冬篱儿','fangdong','bb@sohu.com',1,3,'爱迷失在天堂','2004-07-13 15:09:51.000', 4,1200)
insert into bbsUsers values('supper','master','dd@p.com',1,5,'BBS大斑竹','2005-06-13 15:09:51.000',4,1200)
select * from bbsUsers
alter table bbsUsers
add constraint PK_UID primary key (UID)
alter table bbsUsers
add constraint DF_Upass 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)
if exists(select * from sysobjects where name ='bbsSection')
drop table bbsSection
create table bbsSection
(
SID int identity(1,1) not null,
Sname varchar(32) not null,
SmasterID int not null,
Sprofile varchar(20),
SclickCount int ,
StopicCount int
)
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
select * from bbsSection
insert into bbsSection values('Java技术',1,'我的最爱',500,1)
insert into bbsSection values('.net技术',2,'包含C# asp,.net',800,1)
insert into bbsSection values('linux/unix社区',3,'包含系统维护和使用',0,0)
if exists(select * from sysobjects where name='bbsTopic')
drop table 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
)
select * from bbsTopic
alter table bbsTopic
add constraint PK_TID primary key (TID)
alter table bbsTopic
add constraint FK_T1sID 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_Tcontents check(len(Tcontents)>=6)
alter table bbsTopic
add constraint CK_TlastReply check(Ttime>TlastReply)
select * from bbsTopic
insert into bbsTopic values (1,3,2,1,'还是jsp','jsp文件中读取','2005-08-01 8:30',200,1,'2005-08-01 10:12')
insert into bbsTopic values(2,2,0,2,'部署.net','项目包括winse ','2005-07-01 8:54',0,1,'2005-07-01 14:24')
if exists(select * from sysobjects where name='bbsReply')
drop table 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 ,
Rconntents varchar(30) not null,
Rtime datetime ,
RclickCount int
)
select * from bbsReply
alter table bbsReply
add constraint FK_Rt1ID foreign key (RtID) references bbsTopic (TID)
alter table bbsReply
add constraint FK_Rs1ID foreign key (RsID) references bbsSection(SID)
alter table bbsReply
add constraint FK_R1uID foreign key (RuID) references bbsUsers(UID)
alter table bbsReply
add constraint DF_Rtime default (getDate()) for Rtime
alter table bbsReply
add constraint CK_Rconnents check(len(Rconntents)>=6)
insert into bbsReply values (1,1,5,2,'jsp乱码问题该怎么办','2005-01-01 12:50',100)
insert into bbsReply values (1,1,4,4,'转换jsp','2005-07-01',200)
insert into bbsReply values (2,2,2,3,'.net很精彩','2005-07-07 14:30',0)
/*第三章*/
select * from bbsReply
select * from bbsTopic
select * from bbsSection
select * from bbsUsers
go
declare @uid int
declare @fatei int
declare @huitei int
select @uid=Uid from bbsUsers where Uname='心酸果冻'
select @fatei=count(*) from bbsTopic where Tuid= @uid
if(@fatei>0)
begin
print '心酸果冻法帖数: ' + convert(varchar(10),@fatei)+',帖子如下：'
select 发帖时间=Ttime,点击率=SclickCount,主题=Ttopic,内容=Tcontents from bbsTopic left join bbsSection on
TuID=SmasterID where Tuid=@uid
end
else
begin
print'心酸果冻法帖数:'+convert(varchar(10),@fatei)
end
select @huitei =count(*) from bbsReply where Ruid=@uid
if(@huitei>0)
begin
print '心酸果冻的回帖数：'+convert(varchar(10),@huitei)+'帖子如下：'
select 回帖时间=Rtime,点击数=RclickCount,内容=Rcontents from bbsReply where RuID=@uid
end
else
begin
print '心酸果冻的回帖数：'+convert(varchar(10),@huitei)
end
print '心酸果冻的贴数总计：'+convert(varchar(20),(@fatei+@huitei))
print '功臣级别:'+case
when (@fatei+@huitei)<10 then '新手上路'
when (@fatei+@huitei) between 10 and 20 then '侠客'
when (@fatei+@huitei) between 20 and 30 then '骑士'
when (@fatei+@huitei) between 30 and 40 then '精灵王'
when (@fatei+@huitei) between 40 and 50 then '光明使者'
else '法老'
end
go
select * from bbsReply
select * from bbsTopic
select * from bbsSection
select * from bbsUsers
declare @huitie int
declare @Tid int
declare @Tsid int
declare @Tuid int
select top 1 @Tid=TID,@huitie=TreplyCount ,@Tsid=TsID ,@Tuid=Tuid from bbsTopic order by(TreplyCount) desc
print '第一精华贴信息的如下：'
select 发帖时间=Ttime,点击率=SclickCount, 作者=Uname,主题=Ttopic, 内容=Tcontents from bbsTopic left join bbsSection on SID=TsID left join bbsUsers on TuId=UId where TID=@Tid
print '回帖数：'+convert(varchar(10),@huitie)+'贴，如下所示：'
select 回帖时间=Rtime,点击率=SclickCount,回帖表情=case
when Rface=1 then '^(oo)^猪头'
when Rface=2 then '*:o小丑'
when Rface=3 then '[:1]机器人'
when Rface=4 then '{^o~o^}老人家'
else '(:<吹水大王'
end
,回帖内容=Rcontents from bbsReply left join bbsSection on SmasterID=RsId where Rtid=@Tid
go
/*第四章*/
select * from bbsReply
select * from bbsTopic
select * from bbsSection
select * from bbsUsers
///////////////////////////////上机练习
declare @tid int ,@tsid int ,@tuid int ,@uid int
select @uid=Uid from bbsUsers where uname='可卡因'
select @tid=TID,@tsid=TsID,@tuid=TuID from bbsTopic where Ttopic like'%什么是.net啊%'
--插入数据
insert into bbsReply values(@tid,@tsid,@tuid,5,'.net真的很棒！！！','2008-1-16',4)
--更新数据
update bbsTopic set TreplyCount=TreplyCount+1,TclickCount=TclickCount+1 where TsID=@tsid and TuID=@tuid
update bbsSection set SclickCount=SclickCount+1 where sid=@tsid
if not exists(select * from bbsReply where Rcontents like '.net真的很棒！！！'and RuId<>@uid)
update bbsUsers set Upoint =Upoint +100 where UID=@uid
else
update bbsUsers set Upoint =Upoint +50 where UID=@uid
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 3000 then 4
when upoint between 3001 and 4000 then 5
else 6
end
where uid=@uid
select 发贴作者=(select uname from bbsusers where uid =@tuid ) ,发贴时间=convert(varchar(10),ttime,111),主题=Ttopic,内容=Tcontents from bbsTopic where TID=@tid
select 回帖作者='可卡因',回帖时间=Rtime,内容=Rcontents from bbsReply where RID=@@identity
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
use bbsDb
select * from bbsReply
select * from bbsTopic
select * from bbsSection
select * from bbsUsers
////////////////////////////////第四章上机作业
declare @uid int
declare @tid int
declare @Userid int
select @uid=uid from bbsusers where uname='心酸果冻'
---更新心酸果冻的积分
update bbsusers set upoint =upoint -100 where uid=@uid
select @tid=TID from bbsTopic where Tuid=@uid
---查找回帖的版主并相应的减分
select @Userid=RuID from bbsReply where RtID=@tid
update bbsusers set upoint =upoint-50 where uid=@Userid
---更新主贴量
update bbsSection set StopicCount =StopicCount-1 where SmasterID=@uid
if((select count(*) from bbsReply where RtID=@tid and RuID=@Userid)>10)
print '给予'+convert(varchar(10),@Userid)+'严重警告！'
else
print '给予'+convert(varchar(10),@Userid)+'一般警告！'
--删除回帖
delete from bbsReply where RtID=@tid
--删除主帖
delete from bbsTopic where TuID=@uid
--重新排等级
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 3000 then 4
when upoint between 3001 and 4000 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
/*第五章上机作业*/
/*第二部分*/
select * from bbsUsers
create nonclustered index IX_Uclass
on bbsUsers (Uclass)with fillfactor=30
go
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 3000 then 4
when upoint between 3001 and 4000 then 5
else 6
end
create view view_bbsUsers
as
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
select * from view_bbsUsers
/*第三部分*/
select * from bbsTopic
select * from bbsusers
declare @errorsum int
set @errorsum=0
update bbsTopic set Ttopic='配置问题',Tcontents='.net配置',Tface=4 where TID= (select UID from bbsUsers where Uname='可卡因')
set @errorsum=@errorsum+@@error
if(@errorsum>0)
begin
print '交易失败'
rollback transaction
end
else
begin
print '交易成功'
commit transaction
end
go
print '结果'
select * from bbsTopic
go
/*第六章上机*/
/*练习*/
select * from bbsReply
select * from bbsTopic
select * from bbsSection
select * from bbsUsers
if exists(select * from sysobjects where name='proc_find1')
drop proc proc_find1
go
create proc proc_find1
@userName varchar(10)
as
set nocount on
declare @userID varchar(20)
select @userID=UID from bbsusers where Uname=@userName
if exists (select * from bbsTopic where TUID=@userID)
begin
print @userName+'发表的主帖如下'
select 发帖时间=convert (varchar(10),Ttime,111),点击数=TclickCount,主题=Ttopic,内容=Tcontents from bbsTopic where TuID =@userID
end
else
print @userName+'没有发表过主帖 '
if exists(select * from bbsReply where RuID=@userID)
begin
print @userName+'发表的回帖如下：'
select 回帖时间=convert (varchar (20),Rtime,111) ,点击数=RclickCount,回帖内容=Rconntents from bbsReply where RuID=@userID
end
else
print @userName+'没有发表过回帖 '
go
exec proc_find1 '可卡因'
if exists (select * from sysobjects where name='proc_find2')
drop proc proc_find2
go
create proc proc_find2
@userame varchar(10),
@sumTopic int output,
@sumReply int output
as
set nocount on
declare @userID varchar (10)
select @userID=UID from bbsUser where Uname=@userame
if exists (select * from bbsTopic where TuID=@userID)
begin
select @sumTopic=count (*)from bbsReply where RuID=@userID
print @userame+'发表的回帖如下'
select 发帖时间=convert (varchar (10),Ttime,111 ),点击数=TclickCount,主题=Ttopic,内容=tcontents from bbsTopic where TuID=@userID
end
else
begin
set @sumTopic=0
print @userame+'没有发表过主帖'
end
if exists (select * from bbsReply where RuID=@userId)
begin
select @sumReply=count(*) from bbsReply where RuID=@userID
print @userame+'发表的回帖如下'
select 发帖时间=convert (varchar (10),Rtime,111),点击数=RclickCount,回帖内容=Rconntents from bbsReply where RuID=@userID
end
go
declare @sum1 int ,@sum2 int
exec proc_find2 '可卡因'
,@sum1 output,@sum2 output
if @sum1>@sum2
print '小弟发帖比回帖多,看来比较喜欢标新立异'
else
print '小弟回帖 比发帖多，看来比较关心民众'
print '总帖数'+convert (varchar(10),@sum1+@sum2)
go
/*第二部分 练习 */
if exists (select * from sysobjects where name='proc_find3')
drop proc proc_find3
go
create proc proc_find3
as
declare @umfatie int
declare @sumhuitie int
declare @sum int
select @umfatie=count (*) from bbsSection
select @sumhuitie=count (*) from bbsTopic
set @sum=@umfatie+@sumhuitie
print '回帖数是'+ convert (varchar(10),@umfatie)
print '主帖数是'+convert (varchar(10),@sumhuitie)
exec proc_find3
/*第三部分 作业*/
if exists (select * from sysobjects where name='proc_delive')
drop proc proc_delive
go
create proc proc_delive
@name varchar (10) ,
@bankuai varchar (10) ,
@toptic varchar (10) ,
@content varchar (10) ,
@face varchar (10)
as
select @name=Uname,@bankuai=Sname from bbsUsers inner join bbsSection on UID=SID
select @toptic=Ttopic,@content=Tcontents,@face=Tface from bbsTopic
select 发帖者=@name,所在板块=@bankuai,主题=@toptic,内容=@content,发帖表情=@face
/*排名*/
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 3000 then 4
when upoint between 3001 and 4000 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
exec proc_delive '冬篱儿 ','net技术','部署.net','项目包括winse',1
/*第七章上机练习*/
if exists (select name from sysobjects where name='trig_delete_bbsTopic')
drop trigger trig_delete_bbsTopic
go
--创建删除触发器
create trigger trig_delete_bbsTopic on bbsTopic
for delete
as
set nocount on
declare @userid int,@topicid int
select @userid=TuID,@topicid=TID from deleted
update bbsUsers set Upoint=Upoint-100 where UID=@userid
update bbsUsers set Upoint=Upoint-50 from bbsUsers inner join bbsReply on bbsUsers.UID=bbsReply.RuID
where bbsReply.RtID=@topicid
delete from bbsReply where RtID=@topicid
go
select * from bbsTopic
select * from bbsReply
set nocount on
declare @topicid int
set @topicid=@@identity
go
print '-----------------------删帖前---------------------------------------'
select 昵称=Uname,贴主ID=UID,等级=Uclass,积分=Upoint from bbsUsers
select 贴主ID=RuID,回帖内容=Rconntents from bbsReply
delete bbsTopic where Ttopic like'%jsp%'
print '-----------------------删帖后-----------------------------------------'
select 昵称=Uname,贴主ID=UID,等级=Uclass,积分=Upoint from bbsUsers
select 贴主ID=TuID,主题=Ttopic,内容=Tcontents from bbsTopic
select 回帖人=RuID,回帖内容=Rconntents from bbsReply
发表于 @ 2008年05月02日 09:53:09|评论(loading...)|编辑