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
分享到:
相关推荐
Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes offers straightforward, practical answers when you need fast results. By working through 10-minute lessons, you’ll learn everything you ...
sql server T-SQl基础教程 源码和示例数据库
SQL T-SQL Recipes, 4th Edition
SQL Server T-SQL高级查询
SQL Server T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server. This edition has been lightly updated for SQL Server 2014 and provides ready-to-...
T-sql基本语法示例 1.建库建表,建约束 2.查询 3.视图 4.存储过程 5.触发器
Inside Microsoft SQL Server 2008 T-SQL Programming
本书全面深入地介绍了Microsoft SQL Server 2008 中高级T-SQL 查询、性能优化等方面的内容,以及SQLServer 2008 新增加的一些特性。主要内容包括SQL 的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表...
SQL Server 2012 T-SQL 实用技巧 (英文版)
该书解释并比较了sql server 2000和sql server 2005在数据库开发相关问题上的解决方案,深入讨论了sql server 2005中新增的t-sql编程特性,包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解...
详细深入的讲解了SQLServer2008的各个部分,如果你想了解SQLServer2008的底层机制,那么这本书不会让你失望!
SQL Server 2012 T-SQL 编程必备 -- 英文
SQL Server 2005 T-SQL增强
Inside Microsoft SQL Server 2008 T-SQL Querying Code Inside Microsoft SQL Server 2008 T-SQL Querying Code
介绍SQLSERVER2008 T-SQL语言的书,微软出版社出版
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
SQLServer数据库管理常用的SQL和T-SQL语句
SQL Server 2012 T-SQL 基础
Inside Microsoft SQL Server 2008: T-SQL Querying puts together all the ingredients you need to understand this declarative and set-oriented way of thinking and become a profi cient SQL programmer, ...