SQL Server T-SQL

use bbsDB
insert dbo.bbsUsers(Uname,Upassword,Uemail,Ubirthday,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
select '可卡','s007fdfd','[e-mail]','1978-07-09',1,1,'我要去云南','2008-10-06',1,200 union
select '心酸果冻','88fdfdf','[e-mail]','1980-08-09',0,2,'走遍天涯海角','2008-10-10',2,600 union
select '冬天里尔','007ppp','[e-mail]','1979-07-09',1,3,'爱迷失子那里','2005-10-06',4,1200 union
select 'Super','fsfjhj','[e-mail]','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