sqlserver 创建数据库

--创建数据bbsDB
use master
go
if exists(select*from sysdatabases where name='bbsDB')
drop database bbsDB
go
sp_configure 'show advanced options',1
reconfigure
go 
sp_configure 'xp_cmdshell',1
reconfigure
go
--exec xp_cmdshell 'mkdir D:\project'
create database bbsDB
on primary
(
name ='bbsDB_data',
filename = 'D:\project\bbsDB_data.mdf'
)
log on
(
name ='bbsDB_log',
filename ='D:\project\bbsDB_log.ldf'
)
go
--创建表bbsSection
use bbsDB

go

if exists(select * from sysobjects where name='Department')
drop table Department
create table Department
(
Id int primary key identity(1,1)  not null,
num varchar(50) not null,
Name varchar(20) not null,
Note varchar(200),
)

if exists(select * from sys.objects where name='Employee')
  drop table Employee
create table Employee
(
Id int identity(1,1) ,
Name varchar(20) not null, 
DId int foreign key(DId) references Department(Id)
)

--批量插入数据

insert into Department (num,Name,Note)

select '0003','销售1','备注1' union
select '0004','销售2','备注2'  

if exists(select * from sys.objects where name='bbsUsers')
 drop table bbsUsers

create table bbsUsers
(
UID int identity(1,1), --用户编号
Uname varchar(15)not null, --用名称
Upassword varchar(10) not null, --密码
Uemail varchar(20), --电子邮件
Usex bit not null, --性别
Uclass int , --用户的等级
Uremark varchar(20), --备注信息
UregDate datetime not null, --注册日期
Ustate int , --状态 是否在线
Upoint int --用户积分
)
--给表建表bbsDB的约束
go
alter table bbsUsers
add constraint pk_UID primary key (UID) --主键
alter table bbsUsers
add constraint df_Upassword default(88888888)for Upassword--密码默认88888888
alter table bbsUsers
add constraint ck_Uemail check(Uemail like '%@%') --Uemail必须包含@
alter table bbsUsers
add constraint df_Usex default(1)for Usex --性别默认为 男(1)
alter table bbsUsers 
add constraint df_Uclass default(1)for Uclass --等级默认1
alter table bbsUsers
add constraint df_UregDate default(getDate())for UregDate--默认当前时间
alter table bbsUsers
add constraint df_Ustate default(0)for Ustate --状态默认0（离线）
alter table bbsUsers
add constraint df_Upoint default(20)for Upoint --默认积分20
go


select * from bbsUsers
--插入数据到表bbsUsers
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('李四',default,'lisi@.con',1,3,'耍',default,1,30)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('王二','12345678','lisi@.con',1,3,'耍',default,1,300)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('李四','11111111','lisi@.con',1,3,'BBs大吧主',default,1,1000)
go


--创建表bbsSection
create table bbsSection
(
SID int identity(1,1), --板块编号
Sname varchar(32) not null, --版块名称
SmasterID int not null, --版主用户UID
Sprofile varchar(20), --版面简介
SclickCount int, --点击率
StopicCount int --发帖数
)
go
--创建表bbsSection的约束
alter table bbsSection
add constraint pk_SID primary key(SID) --主键
alter table bbsSection
add constraint df_SclickCount default(0)for SclickCount --默认0
alter table bbsSection
add constraint df_StopicCount default(0)for StopicCount --默认0
go
--创建表bbsTopic
create table bbsTopic
(
TID int identity(1,1), --帖子编号
TsID int not null, --板块编号
TUID int not null, --发帖人ID
TreplyCount int, --回复数量
Tface int, --发帖表情
Ttopic varchar(20) not null, --标题
Tcontents varchar(30) not null, --正文
Ttime datetime , --发帖时间
TclickCount int, --点击数
Tstate int, --状态
TlastReply datetime --最后回复时间
)
go
--创建表bbsTopic的约束
alter table bbsTopic
add constraint pk_TID primary key(TID) --主键
alter table bbsTopic
add constraint df_TreplyCount default(0)for TreplyCount --回复数量默认0
alter table bbsTopic
add constraint df_Ttime default(getDate())for Ttime --发帖时间默认当天
alter table bbsTopic
add constraint df_TclickCount default(0)for TclickCount --点击数量默认0
alter table bbsTopic
add constraint df_Tstate default(1)for Tstate --状态默认1
go


--创建表bbsReply
create table bbsReply
(
RID int identity(1,1), --帖子编号
RtID int not null, --主贴ID， 主键TID
RsID int not null, --板块ID， 主键UID
RuID int not null, --回帖人ID ，主键UID
Rface int , --回帖表情
Rcontents varchar(30) not null, --正文
Rtime datetime , --回帖时间
RclickCount int, --点击数
)
go
--创建表bbsReply的约束
alter table bbsReply
add constraint pk_RID primary key(RID) --主键
alter table bbsReply
add constraint df_Rtime default(getDate())for Rtime --回帖时间当天    


--外键 主贴表bbsTopic与 主键 版块表bbsSection
alter table bbsTopic 
add constraint fk_TsID foreign key(TsID) references bbsSection(SID)


--外键 主贴表bbsTopic与 主键 用户表bbsUsers
alter table bbsTopic
add constraint fk_TuID foreign key(TuID) references bbsUsers(UID)
--外键 跟贴表bbsReply与 主键 主贴表bbsTopic
alter table bbsReply
add constraint fk_RtID foreign key(RtID) references bbsTopic(TID)
--外键 跟贴表bbsReply与 主键 版块表bbsSection
alter table bbsReply
add constraint fk_RsID foreign key(RsID)references bbsSection(SID)
--外键 跟贴表bbsReply与 主键 用户表bbsUsers
alter table bbsReply
add constraint fk_RuID foreign key(RuID)references bbsUsers(UID)
--外键 版块表bbsSection 主键 用户表bbsUsers
alter table bbsSection 
add constraint fk_SuID foreign key(SmasterID)references bbsUsers(UID)


--创建数据库登录帐号
go
use bbsDB
go
exec sp_addlogin 'adminMaster','theMaster'
--创建数据库用户
exec sp_grantdbaccess 'adminMaster','master'
--设置权限
grant select,delete on bbsTopic to master
grant select,delete on bbsReply to master
grant update on bbsUsers to master
go


insert into bbsReply
values(5,9,10,8,'数据库连接池....','2009-11-16',1200)
insert into bbsReply
values(6,8,9,9,'public static DataSo....','2009-11-16',5000)
insert into bbsReply
values(5,9,8,10,'帮测试人员架ASP。NET环....','2009-11-16',800)


insert into bbsSection
values('java技术',3,'讨论java相关技术...',500,1)
insert into bbsSection
values('.NET',5,'讨论Web Service/....',800,1)
insert into bbsSection
values('Linx/Unix社区',5,'程序开发,...',0,0)


insert into bbsTopic
values(7,8,2,1,'还是jisp中..','jisp文件中读..','2009-1-14',200,1,'2009-11-14')
insert into bbsTopic
values(8,9,0,2,'部署net中..','项目包括we..','2009-11-15',200,1,'2009-11-15')
go


--查询李四
print 'sql Server的版本：'+@@version
print '服务器名称：'+@@servername
update bbsUsers set Upassword ='1234' where Uname ='李四'
print '执行上条语句产生的错误：'+convert(varchar(5),@@error)
go
set nocount on
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),点击数=TclickCount,主题=Ttopic,类容=Tcontents 
from bbsTopic
where TuID =@userID


print '李四回帖如下：'
select 回帖时间=convert(varchar(10),Rtime,111),点击数=RclickCount,回帖类容=Rcontents 
from bbsReply 
where RuID =@UserID
go
--提分
print '开始提分，请稍后....'
declare @score int ,@avg int
set @score =0
while(1=1)
begin
update bbsUsers set Upoint =Upoint+50 where Ustate<0
set @score =@score+50
select @avg =(Upoint) 
from bbsUsers
if(@score>10000)
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




--数据表的查询
declare @faTie int ,@huiTie int ,@dengJi varchar(50)
select @faTie = StopicCount from bbsSection 
print '李四发帖数：'+convert(varchar(20),@faTie)+'，帖子如下：'
select 发帖时间=t.Ttime,点击率=s.SclickCount,主题=t.Ttopic,内容=t.Tcontents
from bbsSection as s
inner join bbsTopic as t on (SID =TID)
select @huiTie =TreplyCount from bbsTopic
print '李四回帖数：'+convert(varchar(20),@huiTie)
select @dengJi = case
when (@faTie+@huiTie)<10 then '新手上路'
when (@faTie+@huiTie) between 10 and 20 then '新手上路'
when (@faTie+@huiTie) between 21 and 30 then '侠客'
when (@faTie+@huiTie) between 31 and 40 then '精灵王'
when (@faTie+@huiTie) between 41 and 50 then '光明使者'
else '法老'
end
print'李四帖子总计：'+convert(varchar(50),(@faTie+@huiTie))+' 贴 '+'工程级别：'+convert(varchar(20),@dengJi)




--会使用子查询In/EXISTS
set nocount on --不显示影响行数
print'---->>>>>各位大侠注意，本论坛即将发布年度无记名评奖<<<<<-----'
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 '年度回帖人气最In的前两名获奖者：'
select 大名=Uname,星级=Uclass from bbsUsers
where UID in (select top 2 TuID  from bbsTopic order by  TclickCount desc )


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
go


--星级
set nocount on --不显示影响行数
declare @userID varchar(10),@sID int
select @userID = UID from bbsUsers where Uname ='李四'
select @sID =SID from bbsSection where Sname like '%.NET%'
insert into bbsTopic (TsID,TuID,Tface,Ttopic,Tcontents)
values (@sID,@userID,3,'什么是.NET 啊？','微软的.Net广告超过...')
update bbsSection 
set StopicCount = StopicCount+1 
where SID =@UserID
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 2000then 3
when Upoint between 2001 and 4000 then 4
when Upoint between 4001 and 5000 then 5
else 6
end
where UID =@UserID
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
use bbsDB
go
select*from bbsReply
select*from bbsSection
select*from bbsTopic
select*from bbsUsers
go


--阶段3
set nocount on --不显示影响行数
declare @userID varchar(10),@sID int,@tID int
select @userID = UID from bbsUsers where Uname ='王二'
select @sID =SID from bbsSection where Sname like '%java技术%'
select @tID =TID from bbsTopic where Ttopic like'什么是.NET 啊？'
insert into bbsReply (RtID,RsID,RuID,Rface,Rcontents,Rtime,RclickCount)
values (@tID,@sID,@userID,3,'.NET是微软力推..','2009-11-13',1230)

update bbsTopic 
set TreplyCount = TreplyCount+1 
update bbsSection
set SclickCount = SclickCount+1


update bbsSection
set StopicCount = StopicCount+1 


if not exists(select * from bbsReply where Rcontents like '.NET是微软力推..')
update bbsUsers set Upoint =Upoint +100 where UID =@UserID
else
update bbsUsers set Upoint =Upoint +50 where UID =@UserID


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


--上机5


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
--添加约束
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
insert into bank(customerName,cardID,currentMoney)
values ('张三','1001 0001',1000)
insert into bank(customerName,cardID,currentMoney)
values ('李四','1001 0002',1)
go
print '-------------转账前的余额--------------'
select * from bank
go
begin transaction --开始事务
declare @errorSum int ,@myMoney Money
set @myMoney = 1000 --转账假定为1000元
set @errorSum = 0 --初始化错误为0
insert into transInfo(cardID,transType,transMoney)
values ('1001 0001','支取',@myMoney)
set @errorSum =@errorSum + @@error --累加错误到变量@errorSum中
update bank set currentMoney = currentMoney -@myMoney --更新用户余额
where cardID = '1001 0001' 
set @errorSum = @errorSum + @@error
insert into transInfo (cardID,transType,transMoney)
values('1001 0002','存入',@myMoney)
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


--取钱
print '-----------------支取前的余额-------------------'
select * from bank
go


begin transaction
--定义变量，用于保存错误，支取金额
declare @errorSum int , @drawMoney int
set @errorSum = 0
set @drawMoney = 500
insert into transInfo (cardID,transType,transMoney)
values('1001 1001','支取',@drawMoney)
set @errorSum = @errorSum + @@error
update bank set currentMoney = currentMoney - @drawMoney
where cardID = '1001 0001'
set @errorSum = @errorSum + @@error
if @errorSum <>0
begin
print '交易失败，回滚事务'
rollback transaction
end
else
begin
print '交易成功，提交事务'
commit transaction
end
print '----------------交易后的余额------------------'
select * from bank


--阶段3 排序
use bbsDB
go
--删除索引
if exists(select name from sysindexes where name = 'index_Uclass')
drop index bbsUsers.index_Uclass
--创建索引
create clustered index index_Uclass
on bbsUsers (Uclass)
go
--按星级排序
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