添加唯一约束命令: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))