use bbsDB
insert dbo.bbsUsers(Uname,Upassword,Uemail,Ubirthday,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
select '可卡','s007fdfd','ss@hotmail.com','1978-07-09',1,1,'我要去云南','2008-10-06',1,200 union
select '心酸果冻','88fdfdf','yy@hotmail.com','1980-08-09',0,2,'走遍天涯海角','2008-10-10',2,600 union
select '冬天里尔','007ppp','bb@hotmail.com','1979-07-09',1,3,'爱迷失子那里','2005-10-06',4,1200 union
select 'Super','fsfjhj','dd@hotmail.com','1977-07-09',1,5,'BBs大版主','2007-10-06',1,5000
use bbsDB
GO
if exists(select * from sysobjects where name='bbsSection')
print '存在'
else
print '不存在'
--这段代码最好要加上,否则在执行下面语句时候有可能会出现bbsUsers不存在的错误提示
print 'SQL Server版本号'+@@version
print 'SQL Server服务器名称'+@@servername
update dbo.bbsUsers set Upassword='123' where Uname='可卡'
print '错误号'+convert(varchar(5),@@error)
declare @UID int
declare @Uname varchar(15)
declare @point int
set @Uname='可卡'
select @UID=UID from dbo.bbsUsers where @Uname=Uname
print '个人资料如下'
select 昵称=Uname,等级=Uclass,个人说明=Uremark,积分=Upoint from dbo.bbsUsers where UID=@UID
select @point=Upoint from dbo.bbsUsers where UID=@UID
print @Uname+'发帖发帖如下'
select 发帖时间=Ttime,点击率=TclickCount,主题=Ttopic,内容=Tcontents from dbo.bbsTopic where TuID=@UID
print @Uname+'回帖如下'
select 回帖时间=Rtime,点击率=RclickCouont,回复内容=Rcontents from dbo.bbsReply where RuID=@UID
if(@point<50)
print+'可卡:无权发帖'
else
print+'可卡:有权发帖'
print+'上机二'
print+'开始提分,请稍后。。。'
declare @AVG_Point int
declare @finalAdd int
set @finalAdd=0
select @AVG_Point=avg(Upoint) from dbo.bbsUsers
while(@AVG_Point<=2000)
begin
update dbo.bbsUsers set Upoint=Upoint+50
select @AVG_Point=avg(Upoint) from dbo.bbsUsers
set @finalAdd =@finalAdd+50
end
print+'------------加分后的用户级别情况--------------'
print+'提升分值'+convert(varchar(10),@finalAdd)
select 昵称=Uname,星级=case
when Upoint<499 then '*'
when Upoint between 500 and 1000 then '**'
when Upoint between 1001 and 2000 then '***'
when Upoint between 2001 and 4000 then '****'
when Upoint between 4001 and 5000 then '*****'
end
,积分=Upoint from dbo.bbsUsers
--阶段三
use bbsDB
declare @Uid int --要查询的用户的UID
declare @num_fatie int --发帖数
declare @num_huitie int --发帖数
declare @count int --帖数总计
select @Uid=UID from dbo.bbsUsers where Uname='心酸果冻'
select @num_fatie=count(*) from dbo.bbsTopic where TuID=@Uid
select @num_huitie=count(*) from dbo.bbsReply where RuID=@Uid
if (@num_fatie=0)
print '心酸果冻发帖数为'+convert(varchar(5),@num_fatie)
else
begin
print '心酸果冻发帖数为:'+convert(varchar(5),@num_fatie)+'帖子如下:'
select 发帖时间=Ttime,点击率=TclickCount,主题=Ttopic,内容=Tcontents from dbo.bbsTopic where TuID=@Uid
end
if (@num_huitie=0)
print '心酸果冻回帖数为'+convert(varchar(5),@num_huitie)
else
begin
print '心酸果冻回帖数为:'+convert(varchar(5),@num_fatie)+'帖子如下:'
select 发帖时间=Rtime,内容=Rcontents from dbo.bbsReply where RuID=@Uid
end
set @count=@num_fatie+@num_huitie
print '心酸果冻帖数总计:'+convert(varchar(5),@count)+'功臣级别:'+case
when @count between 0 and 9 then '新手上路'
when @count between 10 and 20 then '侠客'
when @count between 21and 30 then '骑士'
when @count between 31and 40 then '精灵士'
when @count between 41 and 50 then '光明使者'
when @count>50 then '法老'
end