sqlserver 创建数据库
发布时间：2020-12-12 14:43:04 所属栏目：MsSql教程 来源：网络整理
导读：--创建数据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:pro
|
--创建数据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:projectbbsDB_data.mdf' ) log on ( name ='bbsDB_log', filename ='D:projectbbsDB_log.ldf' ) go --创建表bbsSection use bbsDB go if exists(select * from sysobjects where name='Department') --批量插入数据 insert into Department (num,Name,Note) select '0003','销售1','备注1' union if exists(select * from sys.objects where name='bbsUsers') ?drop table bbsUsers create table bbsUsers ( UID int identity(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,'耍',30) insert into bbsUsers(Uname,Upoint) values('王二','12345678',300) insert into bbsUsers(Uname,'22222111','BBs大吧主',1000) go --创建表bbsSection create table bbsSection ( SID int identity(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,--帖子编号 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,--帖子编号 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,'public static DataSo....',5000) insert into bbsReply values(5,'帮测试人员架ASP。NET环....',800) insert into bbsSection values('java技术','讨论java相关技术...',500,1) insert into bbsSection values('.NET',5,'讨论Web Service/....',800,1) insert into bbsSection values('Linx/Unix社区','程序开发,...',0) insert into bbsTopic values(7,2,'还是jisp中..','jisp文件中读..','2009-1-14',200,'2009-11-14') insert into bbsTopic values(8,'部署net中..','项目包括we..','2009-11-15','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,点击数=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,简介=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,'什么是.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),内容= 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,RclickCount) values (@tID,@sID,'.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 回帖用户='王二',内容= Rcontents from bbsReply where RID =@@identity select 昵称=Uname,积分=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,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,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,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,积分=Upoint from bbsUsers go（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |