当初学习时做出的bbsDB数据库创建及一些常用操作

添加唯一约束命令：alter table TBL_USER add constraint UQ_userName unique(userName)

/*-----------------------------------------阶段1-------------------------------------------*/
USE master
go

/**---检查是否已存在bbsDB数据库：查询master数据库中的系统表sysdatabases---*/
if exists (select * from sysdatabases where name='bbsDB')
 drop database bbsDB
go

exec xp_cmdshell 'mkdir D:\Homework\project' --调用DOS命令创建文件夹
/*-----建库-----*/
create database bbsDB
on
(
 /*--数据文件的具体描述--*/
 name='bbsDB_data', --主数据文件的逻辑文件
 filename='D:\Homework\project\bbsDB_data.mdf', --主数据文件的物理名称
 size=10MB, --主数据文件的初始大小
 filegrowth=10% --主数据文件的增长率
)
log on
(
 /*--日志文件的具体描述，各参数含义同上--*/
 name='bbsDB_log',
 filename='D:\Homework\project\bbsDB_log.mdf',
 size=3MB,
 maxsize=20MB,
 fileGrowth=10%
)
go

/*-----------------------------------------阶段2-------------------------------------------*/
use bbsDB
go

/*---检查是否已存在表bbsUsers:查询bbsDB数据库中的系统表sysobjects---*/
if exists(select * from sysobjects where name='bbsUsers')
 drop table bbsUsers
go

/*-----创建表----*/
create table bbsUsers
(
 Uid int identity(1,1) not null, --自动编号，标识列
 Uname varchar(15) not null, --昵称
 Upassword varchar(10),  --密码
 Uemail varchar(20),  --邮件
 Usex bit not null,  --性别
 Uclass int,   --级别（几星级）
 Ubirthday datetime not null, --生日
 Uremark varchar(20),  --备注
 UregDate datetime not null, --注册日期
 Ustate int null,  --状态（是否禁言等）
 Upoint int null   --积分（点数）
)
go

select * from bbsUsers
go

/*-----------------------------------------阶段3-------------------------------------------*/
/*-----添加约束-----*/
alter table bbsUsers add constraint PK_Uid  --主键
 primary key(Uid)
alter table bbsUsers add constraint DF_Upassword --初始密码默认为6个8
 default (888888) for Upassword
alter table bbsUsers add constraint DF_Usex  --性别默认为男（1）
 default (1) for Usex
alter table bbsUsers add constraint DF_Uclass  --级别默认为1星级
 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  --默认积分20点
 default (20) for Upoint
alter table bbsUsers add constraint CK_Uemail  --必须包含'@'字符
 check (Uemail like '%@%') --%表示任意多个字符
alter table bbsUsers add constraint CK_Upassword --密码至少6位
 check (len(Upassword) >= 6)
go

select * from bbsUsers
go

/*-----------------------------------------阶段4-------------------------------------------*/
use bbsDB
go

/*-----验证是否已存在表bbsSection:查询数据库bbsDB里系统表sysobjects-----*/
if exists (select * from sysobjects where name='bbsSection')
 drop table bbsSection
go

/*------创建表bbsSection，版块表-----*/
create table bbsSection
(
 Sid int identity (1,1) not null,
 Sname varchar (32) not null,
 SmasterId int not null,
 Sprofile varchar (255),
 SclickCount int,
 StopicCount int
)

/*------添加约束-----*/
alter table bbsSection add constraint PK_Sid
 primary key(Sid)
alter table bbsSection add constraint DF_SclickCount
 default (0) for SclickCount
alter table bbsSection add constraint DF_StopicCount
 default (0) for StopicCount
alter table bbsSection add constraint FK_SmasterId
 foreign key(SmasterId) references bbsUsers(Uid)

/*-----验证是否已存在表bbsTopic:查询数据库bbsDB里系统表sysobjects-----*/
use bbsDB
go

if exists (select * from sysobjects where name='bbsTopic')
 drop table bbsTopic
go

/*------创建表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
)

/*------添加约束-----*/
alter table bbsTopic add constraint PK_Tid
 primary key(Tid)
alter table bbsTopic add constraint FK_bbsTsid
 foreign key(TsId) references bbsSection(Sid)
alter table bbsTopic add constraint FK_Tuid
 foreign key(TuId) references bbsUsers(Uid)
alter table bbsTopic add constraint CK_Tcontents
 check (len(Tcontents)>6)
alter table bbsTopic add constraint DF_Ttime
 default (getDate()) for Ttime
alter table bbsTopic add constraint DF_TreplyCount
 default (0) for TreplyCount
alter table bbsTopic add constraint DF_TclickCount
 default (0) for TclickCount
alter table bbstopic add constraint DF_Tstate
 default (1) for Tstate

/*-----验证是否已存在表bbsReply:查询数据库bbsDB里系统表sysobjects-----*/
use bbsDB
go

if exists (select * from sysobjects where name='bbsReply')
 drop table bbsReply
go

/*------创建表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,
 Rcontents varchar(30) not null,
 Rtime datetime,
 RclickCount int
)

/*------添加约束-----*/
alter table bbsReply add constraint PK_Rid
 primary key(Rid)
alter table bbsReply add constraint FK_Rtid
 foreign key(RtId) references bbsTopic(Tid)
alter table bbsReply add constraint FK_Rsid
 foreign key(RsId) references bbsSection(Sid)
alter table bbsReply add constraint FK_Ruid
 foreign key(RuId) references bbsUsers
alter table bbsReply add constraint CK_Rcontents
 check (len(Rcontents)>6)
alter table bbsReply add constraint DF_Rtime
 default (getDate()) for Rtime

/*创建用户并赋予权力*/
use bbsDB
go

exec sp_grantdbaccess 'cuiyunguang','rong'

grant select,update on bbsTopic to rong

grant select,update on bbsReply to rong

grant select,update on bbsUsers to rong

go

--第三章
/*-----------------------------------------阶段1-------------------------------------------*/
use bbsDB
go

insert into bbsUsers (Uname,Upassword,Uemail,Ubirthday,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
select '可卡因','HYXS007','ss@hotmail.com','1978-07-09',1,1,'我要去公...','2007-10-10',1,200 union
select '心酸果冻','888888','yy@hotmail.com','1980-02-12',0,2,'走遍天涯...','2007-10-10',2,600 union
select '冬篱儿','fangdong','bb@sohu.com','1976-10-03',1,3,'爱迷失在...','2007-10-10',4,1200 union
select 'Super','master','dd@p.com','1977-05-16',1,5,'BBS大斑竹','2007-10-10',1,5000

insert into bbsSection (Sname,SmasterId,Sprofile,SclickCount,StopicCount)
select 'Java技术',2,'讨论Java相关技术，包括J2EE、J2ME、J...',500,1 union
select '.NET',4,'讨论Web Service/XML、NET Remoting、...',800,1 union
select 'Linux/Unix社区',4,'包含系统维护与使用区，程序开发区',0,0

insert into bbsTopic (TsId,TuId,TreplyCount,Tface,Ttopic,Tcontents,Ttime,TclickCount,Tstate,TlastReply)
select 7,2,2,1,'还是jsp中...','jsp文件中读...','2007-10-10',200,1,'2007-10-11' union
select 8,1,0,2,'部署.net中...','项目包括wi...','2007-10-11',0,1,'2007-10-12'

insert into bbsReply (RtId,RsId,RuId,Rface,Rcontents,Rtime,RclickCount)
select 7,7,4,2,'数据库连接池在...','2007-10-10 13:40:39.180',100 union
select 7,7,3,4,'public static DataSo...','2007-10-10 13:42:14.193',200 union
select 8,8,2,3,'帮测试人员架ASP.NET环...','2007-10-10 13:59:59.753',0

select * from bbsUsers  --用户表
select * from bbsTopic  --主帖表
select * from bbsReply  --回帖表
select * from bbsSection --版块表

use bbsDB
go

/*-----使用系统变量，查询数据库系统情况-----*/
print 'SQL Server 的版本：'+@@VERSION
print '服务器的名称：'+@@SERVERNAME
update bbsUsers set Upassword='1234' where Uname='可卡因' --密码违反约束
print '执行上条语句产生的错误号：'+convert(varchar(5),@@ERROR)
go

/*-----使用变量和IF-ELSE语句，版主查看“可卡因”的情况------*/
set nocount on --不显示T-SQL语句影响的行数信息
print '' --为了显示方便，打印一空行
print '个人资料如下'
select 昵称=Uname,等级=Uclass,个人说明=Uremark,积分=Upoint from bbsUsers where Uname='可卡因'
declare @userId int --定义变量，用于存放用户编号值
select @userId=Uid from bbsUsers where Uname='可卡因' --变量赋值
print '可卡因发帖如下：'
select 发帖时间=convert(varchar(10),Ttime,111),  --111表示按年/月/日格式转换
 点击率=TclickCount,主题=Ttopic,内容=Tcontents from bbsTopic where Tuid=@userId
print '可卡因回帖如下：'
select 回帖时间=convert(varchar(10),Rtime,111),点击率=RclickCount,回帖内容=Rcontents
 from bbsReply where RuId=@userId
declare @point int --可卡因的积分
select @point=Upoint from bbsUsers where Uname='可卡因'
if(@point>30)
 print '可卡因的权限：有权发帖'
else
 print '可卡因的权限：无权发帖'
go

/*-----------------------------------------阶段2-------------------------------------------*/
print '开始提分，请稍后……'
declare @score int,@avg int --定义变量：提分值和平均分
set @score=0
while(1=1)
 begin
  update bbsUsers set Upoint=Upoint+50 where Ustate<>4 --除了被封杀的用户外
  set @score=@score+50    --累计提分值
  select @avg=avg(Upoint) from bbsUsers   --获取提分后的平均分
  if(@avg>2000) --如果平均分>2000，则退出循环，停止提分
   break
 end
print '提分值：'+convert(varchar(8),@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
print '------加分后的用户级别情况------'
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-------------------------------------------*/
declare @id int,@public int,@reply int,@total int,@level varchar
select @id=Uid from bbsUsers where Uname='心酸果冻'
select @public=count(*) from bbsTopic where TuId=@id
print '心酸果冻发帖数：'+convert(varchar(8),@public)+'，帖子如下：'
select 发帖时间=Ttime,点击率=TclickCount,主题=Ttopic,内容=Tcontents from bbsTopic where TuId=@id
select @reply=count(*) from bbsReply where RuId=@id
print '心酸果冻回帖数：'+convert(varchar(8),@reply)
set @total=@public+@reply
print '心酸果冻贴数总计：'+convert(varchar(8),@total)+'  贴 功臣级别：'+case
  when @total<10 then '新手上路'
  when @total between 10 and 20 then '侠客'
  when @total between 21 and 30 then '骑士'
  when @total between 31 and 40 then '精灵王'
  when @total between 41 and 50 then '光明使者'
  else '法老'
 end
go

/*-----------------------------------------上机作业-------------------------------------------*/
declare @number int,@total int,@id int --声明变量回帖最多数，回帖数量，用户编号
select @number=max(TreplyCount) from bbsTopic --查询最多的数量是多少
select 发帖时间=t.Ttime,点击率=t.TclickCount,作者=u.Uname,主题=t.Ttopic,内容=t.Tcontents from bbsTopic as t
inner join bbsUsers as u on t.TuId=u.Uid
where t.TreplyCount=@number  --根据回帖最多数查询
select @total=TreplyCount from bbsTopic where TreplyCount=@number  --回帖数量
print '回帖数：'+convert(varchar(8),@total)+'贴，如下所示：'
select @id=TId from bbsTopic where TreplyCount=@number  --用户编号
select 回帖时间=Rtime,点击率=RclickCount,回帖表情=case
   when Rface=1 then '^(oo)^猪头'
   when Rface=2 then '*:o)小丑'
   when Rface=3 then '[:|]机器人'
   when Rface=4 then '{^o~o^}老人家'
   else '(:<)吹水大王'
  end
 ,回帖内容=Rcontents from bbsReply where RtId=@id

--第四章
/*-----------------------------------------阶段1-------------------------------------------*/
print '--->>>>>>各位大虾注意了，本论坛即将发布年度无记名评奖<<<<<<---'
use bbsDB
go
set nocount on  --不显示有几行受影响
if(select sum(SclickCount) from bbsSection)>1000
 print '论坛人气年度评估：熊旺旺，大家辛苦了！'
else
 print '论坛人气年度评估：一般般，大家加油啊！'
--评选品牌版块和倒胃版块：主贴数量最多和最少的版块
print '年度品牌版块：'
select 版块名称=Sname,主帖数量=StopicCount,简介=Sprofile from bbsSection
 where StopicCount=(select max(StopicCount) from bbsSection)
print '年度倒胃版块：'
select 版块名称=Sname,主帖数量=StopicCount,简介=Sprofile from bbsSection
 where StopicCount=(select min(StopicCount) from bbsSection)
--评选回帖人气最旺的前两名作者
print ''
print '年度回帖人气最IN的前两名获奖作者：'
select 大名=Uname,星级=Uclass from bbsUsers
 where UID in (select top 2 TuId from bbsTopic order by TclickCount DESC)

--评选最差版主：如果存在发帖量为0或点击率低于500的版块，则评选最差版主
if exists (select * from bbsSection where StopicCount=0 or SclickCount<=500)
 begin
  print '请下列版块的版主加加油哦！'
  select 版块名称=Sname,主帖数量=StopicCount,点击率=SclickCount from bbsSection
   where StopicCount=0 or SclickCount<=500
 end

/*-----------------------------------------阶段2-------------------------------------------*/
/*--发主帖：
心酸果冻在.NET技术版块发帖：
怯怯地问：什么是.Net啊？
微软的.Net广告超过半个北京城啊…
--*/
declare @userId int,@sId int --定义变量存放用户编号和版块编号
--获取“心酸果冻”的用户编号
select @userId=uid from bbsUsers where Uname='心酸果冻'
--获取.Net版块的编号
select @sId=sid from bbsSection where Sname like '%.NET技术%'
--将“心酸果冻”的发帖插入主帖表
insert into bbsTopic (TsId,TuId,Tface,Ttopic,Tcontents)
 values (@sId,@userId,3,'什么是.Net啊？','微软的.Net广告超过半个北京城啊…')
--更新版块表：.NET技术版块主贴数+1
update bbsSection set StopicCount=StopicCount+1 where sid=@sid
--更新用户的积分：如果是新主题，则积分增加100分，否则增加50
if not exists (select * from bbsTopic where Ttopic like '什么是.Net啊？')
 update bbsUsers set Upoint=Upoint+100 where uid=@userId
else
 update bbsUsers set Upoint=Upoint+50 where uid=@userId
--更新用户的积分后，更新相应的级别
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
 where uid=@userId
--对外发布“心酸果冻”的发帖（使用系统变量@@IDENTITY查出刚才插入的编号值）
select 发帖作者='心酸果冻',发帖时间=convert(varchar(10),Ttime,111),主题=Ttopic,内容=Tcontents
 from bbsTopic where tid=@@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

/*-----------------------------------------阶段3-------------------------------------------*/
declare @tid int,@uid int --声明主帖tid，回帖人uid，版块sid
select @tid=Tid from bbsTopic where Ttopic='什么是.Net啊？'
select @uid=Uid from bbsUsers where Uname='可卡因'
select @sid=Sid from bbsSection where Sname like '%.NET技术%'
--发帖
insert into bbsReply (RtId,RsId,RuId,Rface,Rcontents)
 values (@tid,@sid,@uid,1,'.Net 是微软力推的企业级信息网络共享平台。')
--更新主帖回复和点击率都加+1
update bbsTopic set TreplyCount=TreplyCount-1,TclickCount=TclickCount-1 where Tid=@tid
--更新版块点击率+1
update bbsSection set SclickCount=SclickCount+1 where Sid=@tid
--回帖用户+分
if not exists (select * from bbsReply where RtId=@tid)
 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 4000 then 4
   when UPoint between 4001 and 5000 then 5
   else 6
  end
 where Uid=@uid
--发布主帖和跟帖
select 发帖作者='心酸果冻',发帖时间=convert(varchar(10),Ttime,111),主题=Ttopic,内容=Tcontents
 from bbsTopic where tid=9
select 跟帖作者='可卡因',跟帖时间=convert(varchar(10),Rtime,111),内容=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

/*-----------------------------------------上机作业-------------------------------------------*/
--声明需要用到的id
declare @sid int,@tuid int,@tid int,@ruid int --声明版块id，主帖id，发帖用户id，跟帖用户id
select @sid=Sid from bbsSection where Sname like '%.NET技术%'
select @tid=Tid from bbsTopic where Ttopic='什么是.Net啊？'
select @tuid=Uid from bbsUsers where Uname='心酸果冻'
select @ruid=TuId from bbsTopic where Ttopic='什么是.Net啊？'
--发帖人减分，跟帖人也减分
update bbsUsers set Upoint=Upoint-100 where Uid=@tuid
update bbsUsers set Upoint=Upoint-50 where Uid in (select TuId from bbsTopic where Ttopic='什么是.Net啊？')

--版块主帖量-1
update bbsSection set StopicCount=StopicCount-1 where sid=@sid
--查询回帖数量
declare @number int
set @number=0
select @number=count(*) from bbsReply where RtId=9
if (@number>10)
 print '严重警告'
else
 print '一般警告'
--删除所有跟帖
delete from bbsReply where Rtid=@tid
--删除发帖
delete from bbsTopic where tid=@tid
--所有用户星级重排名
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

--第五章
/*-----------------------------------------阶段1-------------------------------------------*/
/*-----创建表----*/
use bbsDB
go
set nocount on  --不显示有几行受影响
--创建账户信息表bank和交易信息表transInfo
if exists (select * from sysobjects where name='bank')
 drop table bank
if exists (select * from sysobjects where name='transInfo')
 drop table transinfo
go
create table bank --账户信息表
(
 customerName char(8) not null,  --顾客姓名
 cardId char(10) not null,   --卡号
 currentMoney money not null   --当前余额
)
go
create table transInfo --交易信息表
(
 cardId char(10) not null, --卡号
 transType char(4) not null, --交易类型
 transMoney money not null, --交易金额
 transDate datetime not null --交易日期
)
go
/*---添加约束：账户余额不能少于1元，交易日期默认为当天日期---*/
alter table bank
 add constraint CK_currentMoney check (currentMoney>=1)
alter table transInfo
 add constraint DF_transDate default(getDate()) for transDate,
  constraint CK_transType check(transType in ('存入','支取'))
go
/*--插入测试数据：张三开户，开户金额为1000,；李四开户，开户金额为1--*/
insert into bank(customerName,cardId,currentMoney)
 values('张三','1001 0001',1000) --张三的卡号假定为1001 0001
insert into bank(customerName,cardId,currentMoney)
 values('李四','1001 0002',1) --李四的卡号假定为1001 0002
print '-----------转账前的余额-----------'
select * from bank
go

/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体）--*/
set nocount on  --不显示有几行受影响
begin transaction
/*--定义变量，用于累计事务执行过程中的错误--*/
declare @errorSum int,@myMoney money
set @errorSum=0 --初始化为0，即无错误
set @myMoney=800 --转账金额假定为1000元

/*--转账：张三转账1000元到李四的账号上。
 现实中的转账依靠卡号，即张三的卡号支出1000元，李四的卡号存入1000元--*/
--张三的卡号支取1000元，并保存交易信息
insert into transInfo(cardId,transType,transMoney) --保存交易信息
 values('1001 0001','支取',@myMoney)
set @errorSum=@errorSum+@@error --累计是否有错误
update bank set currentMoney=currentMoney-@myMoney  --更新账户余额
 where cardId='1001 0001'
set @errorSum=@errorSum+@@error --累计是否有错误

--李四的卡号存入1000元，并保存交易信息
insert into transInfo(cardId,transType,transMoney) --保存交易信息
 values ('1001 0002','存入',@myMoney)
set @errorSum=@errorSum+@@error --累计是否有错误
update bank set currentMoney=currentMoney+@myMoney --更新账户余额
 where cardId='1001 0002'
set @errorSum=@errorSum+@@error --累计是否认有错误

print '------转账事务过程中的余额和交易信息-------'
select * from bank
select * from transInfo

/*--根据是否有错误，确定事务是提交还说撤销--*/
if @errorSum<>0 --如果有错误
 begin
  print '交易失败，回滚事务'
  rollback transaction
 end
else
 begin
  print '交易成功，提交事务，写入硬盘永久的保存'
  commit transaction
 end
go

print '------转账事务结束后的约和交易信息------'
select * from bank
select * from transInfo
go

/*-----------------------------------------阶段2-------------------------------------------*/
use bbsDB
go
set nocount on --不显示受影响行数
begin transaction
/*--定义变量存储错误和转账金额--*/
declare @errorSum int,@ourMoney money
set @errorSum=0
set @ourMoney=100

/*---取款交易---*/
insert into transInfo(cardId,transType,transMoney) values ('1001 0001','支取',@ourMoney)
set @errorSum=@errorSum+@@error
update bank set currentMoney=currentMoney-@ourMoney where cardId='1001 0001'
set @errorSum=@errorSum+@@error

insert into transInfo(cardId,transType,transMoney) values ('1001 0002','存入',@ourMoney)
set @errorSum=@errorSum+@@error
update bank set currentMoney=currentMoney+@ourMoney where cardId='1001 0002'
set @errorSum=@errorSum+@@error

/*--转账事务过程中的余额和交易信息--*/
select * from bank
select * from transInfo

/*---根据是否有错误，确定事务是提交还是撤销---*/
if @errorSum<>0
 begin
  print '交易失败，回滚事务'
  rollback transaction
 end
else
 begin
  print '交易成功，提交事务，写入硬盘永久保存'
  commit transaction
 end
go

print '--------转账事务结束后的余额和交易信息---------'
select * from bank
select * from transInfo
go

/*-----------------------------------------阶段3-------------------------------------------*/
use bbsDB
go

set nocount on

/*--检测是否存在该索引（索引在系统表sysindexes中）--*/
if exists (select name from sysindexes where name='IX_bbsUsers_Uclass')
 drop index bbsUsers.IX_bbsUsers_Uclass
/*--创建索引--*/
create nonclustered index IX_bbsUsers_Uclass on bbsUsers(Uclass) with fillfactor=30
go

if exists (select * from sysobjects where name='view_bbsUsers_Uclass')
 drop view view_bbsUsers_Uclass
go

create view view_bbsUsers_Uclass as
 select 昵称=Uname,性别=case
    when Usex=1 then '男'
    else '女'
   end
  ,级别=Uclass,积分=Upoint from bbsUsers
go

select * from view_bbsUsers_Uclass

/*-----------------------------------------上机作业-------------------------------------------*/
use bbsDB
go

begin transaction

set nocount on

declare @errorSum int,@Uid int,@Sid int
select @Uid=Uid from bbsUsers where Uname='可卡因'
select @Sid=Sid from bbsSection where Sname='.NET技术'
set @errorSum=0

--向主帖表插入主帖记录
insert into bbsTopic (TsId,TuId,Tface,Ttopic,Tcontents)
 values(@Sid,@Uid,4,'小弟菜菜请问.NET配置环境','.NET配置具体需要哪些环境啊，我的页面怎么跑步起来呢？')
set @errorSum=@errorSum+@@error

--更新版块表
update bbsSection set StopicCount=StopicCount+1 where Sid=@Sid
set @errorSum=@errorSum+@@error

--更新用户的积分
if not exists (select * from bbsTopic where Ttopic like '小弟菜菜请问.NET配置环境')
 update bbsUsers set Upoint=Upoint+100 where Uid=@Uid
else
 update bbsUsers set Upoint=Upoint+50 where Uid=@Uid
set @errorSum=@errorSum+@@error

--更新用户积分后，更新相应的级别
update bbsUsers set Uclass=case
   when Upoint<500 then 1
   when Upoint between 500 and 1000 then 2
   when Upoint between 1000 and 2000 then 3
   when Upoint between 2000 and 4000 then 4
   when Upoint between 4000 and 5000 then 5
   else 6
  end
 where Uid=@Uid
set @errorSum=@errorSum+@@error

--对外发布“可卡因”的发帖
select 发帖作者='可卡因',发帖时间=convert(varchar(10),Ttime,111),主题=Ttopic,内容=Tcontents
 from bbsTopic where tid=@@IDENTITY
set @errorSum=@errorSum+@@error

if @errorSum<>0
 begin
  print '发帖失败，回滚事务'
  rollback transaction
 end
else
 begin
  print '发帖成功，写入硬盘永久的保存'
  commit transaction
 end
go

--打开视图，显示最新排名
select * from view_bbsUsers_Uclass

--第六章
/*-----------------------------------------阶段1-------------------------------------------*/
/*-----使用系统存储过程-----*/
use bbsDB
go

exec sp_helpconstraint bbsUsers --查看表bbsUsers的约束
exec sp_helpindex bbsUsers --查看bisonbbsUsers的索引

use master
go

exec xp_cmdshell 'mkdir D:\Homework\project\test',no_output
exec xp_cmdshell 'dir D:\Homework\project\'
go

/*-----------------------------------------阶段2-------------------------------------------*/
use bbsDB
go

/*---创建存储过程proc_find1实现：查找某个用户的发帖情况（发主帖和回帖）---*/
--检测是否存在存储过程proc_find1
if exists (select * from sysobjects where name='proc_find1')
 drop procedure proc_find1
go

--创建存储过程proc_find1，查找某个用户的发帖情况（发主帖和回帖）
create procedure proc_find1
 @userName varchar(10) --输入参数用户名
 as
  set nocount on
  --获取用户对应的用户编号
  declare @userId varchar(10)
  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(10),Rtime,111),点击率=RclickCount,回帖内容=Rcontents
     from bbsReply where RuId=@userId
   end
  else
   print @userName+'没有发表过回帖。'
go

/*--调用存储过程，查看“可卡因”的发帖情况--*/
exec proc_find1 '可卡因'

/*-----------------------------------------阶段3-------------------------------------------*/
use bbsDB
go

if exists (select * from sysobjects where name='proc_finc2')
 drop procedure proc_find2

create procedure proc_find2
 @userName varchar(10), --输入参数：用户名
 @sumTopic int output, --输出参数：主贴数
 @sumReply int output --输出参数：回帖数
as
 set nocount on
 declare @userId varchar(10)
 select @userId=Uid from bbsUsers where Uname=@userName
 if exists (select * from bbsTopic where TuId=@userId)
  begin
   --获取主贴数，保存在输出参数中
   select @sumTopic=count(*) from bbsTopic where TuId=@userId
   print @userName+'发表的主贴如下：'
   select 发帖时间=convert(varchar(10),Ttime,111),点击率=TclickCount,
    主题=Ttopic,内容=Tcontents from bbsTopic where TuId=@userId
  end
 else
  begin
   set @sumTopic=0 --设置发帖数=0
   print @userName+'没有发表过主贴。'
  end
 if exists (select * from bbsReply where RuId=@userId)
  begin
   --获取回帖数，保存在输出参数中
   select @sumReply=count(*) from bbsReply where RuId=@userId
   print @userName+'发表的回帖如下：'
   select 回帖时间=convert(varchar(10),Rtime,111),点击率=RclickCount,
    回帖内容=Rcontents from bbsReply where RuId=@userId
  end
 else
  begin
   set @sumReply=0
   print @userName+'没有发表过回帖。'
  end
go

/*--调用存储过程获取“可卡因的主贴数和回帖数”--*/
declare @sum1 int,@sum2 int
exec proc_find2 '可卡因',@sum1 output,@sum2 output --注意output关键字
if @sum1>@sum2
 print '小弟发帖比回帖多，看来比较喜欢标新立异！'
else
 print '小弟回帖比发帖多，看来比较关心民众疾苦！'
print '总贴数：'+convert(varchar(5),@sum1+@sum2)
go

/*-----------------------------------------阶段4-------------------------------------------*/
use bbsDB
go

if exists (select * from sysobjects where name='proc_find3')
 drop proc proc_find3

create procedure proc_find3
 @userName varchar(10), --输入参数：用户名
 @sumTopic int output, --输出参数：主贴数
 @sumReply int output, --输出参数：回帖数
 @section varchar(32)=''
as
 set nocount on
 declare @userId varchar(10)
 select @userId=Uid from bbsUsers where Uname=@userName
 if @section<>'' --不等于''表示指定了
  begin
   declare @uId int,@sId int
   select @uId=Uid from bbsUsers where Uname=@userName
   select @sId=Sid from bbsSection where Sname=@section

   select @sumTopic=count(*) from bbsTopic where TsId=@sId andTuId=@uId
   print @userName+'发表的发帖如下：'
   select 发帖时间=convert(varchar(10),Ttime,111),点击率=TclickCount,
    主题=Ttopic,内容=Tcontents from bbsTopic where TsId=@sId andTuId=@uId

   select @sumReply=count(*) from bbsReply where RsId=@sId andRuId=@uId
   print @userName+'发表的回帖如下：'
   select 回帖时间=convert(varchar(10),Rtime,111),点击率=RclickCount,
    回帖内容=Rcontents from bbsReply where RsId=@sId andRuId=@uId
  end
 else
  begin
   select @uId=Uid from bbsUsers where Uname=@userName
   select @sumTopic=count(*) from bbsTopic where TuId=@uId
   print @userName+'发表的发帖如下：'
   select 发帖时间=convert(varchar(10),Ttime,111),点击率=TclickCount,
    主题=Ttopic,内容=Tcontents from bbsTopic where TuId=@uId

   select @sumReply=count(*) from bbsReply where RuId=@uId
   print @userName+'发表的回帖如下：'
   select 回帖时间=convert(varchar(10),Rtime,111),点击率=RclickCount,
    回帖内容=Rcontents from bbsReply where RuId=@uId
  end
go

declare @sum1 int,@sum2 int
exec proc_find3 '可卡因',@sum1 output,@sum2 output,'Java技术'
print '发帖：'+convert(varchar(5),@sum1)
print '回帖：'+convert(varchar(5),@sum2)

if exists (select * from sysobjects where name='proc_findReply')
 drop procedure proc_findReply

create proc proc_findReply
 @Topic varchar(32),
 @sumReply int output
as
 set nocount on
 declare @Tid int
 select @Tid=Tid from bbsTopic where Ttopic=@Topic
 select @sumReply=count(*) from bbsReply where RtId=@Tid
 print @Topic+'的回帖如下：'
 select 回帖时间=convert(varchar(10),Rtime,111),点击率=RclickCount,
  回帖内容=Rcontents from bbsReply where RtId=@Tid
go

declare @sum int
exec proc_findReply '小弟菜菜请问.NET配置环境',@sum output
print @sum

/*-----------------------------------------上机作业-------------------------------------------*/
use bbsDB
go

if exists (select * from sysobjects where name='proc_delive')
 drop proc proc_delive

create proc proc_delive
 @name varchar(15),
 @section varchar(32),
 @topic varchar(32),
 @contents varchar(255),
 @face int
as
 begin transaction
  declare @Sid int,@Uid int,@errorSum int
  set @errorSum=0
  --获取用户和版块编号
  select @Uid=Uid from bbsUsers where Uname=@name
  select @Sid=Sid from bbsSection where Sname=@section
  --插入帖子
  insert into bbsTopic (TsId,TuId,Tface,Ttopic,Tcontents)
   values (@Sid,@Uid,@face,@topic,@contents)
  select @errorSum=@errorSum+@@error
  --更新版块表
  update bbsSection set StopicCount=StopicCount+1 where Sid=@Sid
  select @errorSum=@errorSum+@@error
  --更新用户积分
  if not exists (select * from bbsTopic where Ttopic like @topic)
   update bbsUsers set Upoint=Upoint+100 where Uid=@Uid
  else
   update bbsUsers set Upoint=Upoint+50 where Uid=@Uid
  select @errorSum=@errorSum+@@error
  --更新相应的级别
  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
   where Uid=@Uid
  select @errorSum=@errorSum+@@error
  --对外发布@name的发帖
  select 发帖作者=@name,发帖时间=convert(varchar(10),Ttime,111),主题=Ttopic,
   内容=Tcontents from bbsTopic where tid=@@identity
  select @errorSum=@errorSum+@@error
  --显示目前最新的排名
  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
  select @errorSum=@errorSum+@@error

  if @errorSum<>0
   begin
    print '发帖失败，回滚事务'
    rollback transaction
   end
  else
   begin
    print '发帖成功，写入硬盘永久保存'
    commit transaction
   end
  go
go

set nocount on
exec proc_delive '心酸果冻','.Net技术','.NET控件问题','请问如何使用webBrowser控件。',3
exec proc_delive 'Super','.Net技术','.NET技术版主换届选举','请大家公开投票',4

--分页查询
use bbsDB
go

declare @page int,@numbers int --页数，数量
set @page=2
set @numbers=2
print @page
print @numbers
select top (@numbers) * from bbsUsers
 where Uid not in (select top ((@page-1)*@numbers) Uid from bbsUsers order by uid)order by uid

--用前几页的最大值来查询
use bbsDB
go

declare @page int,@numbers int --页数，数量
set @page=2
set @numbers=2
select top (@numbers) * from bbsUsers
 where Uid > (select max(Uid) from bbsUsers
  where Uid in (select top (@numbers*(@page-1)) * from bbsUsers))