SQL学习（三）

print @@versionprint @@errorprint @@servernameprint @@language------------局部变量---------------declare @name nvarchar(20);declare @age int;----------------------声明变量set @age=20; -------------赋值方法1select @name='康凯';-------------------复制方法2print @age;print @name;-----------------------------变量print时,要与声明变量语句同时执行declare @rcount int;set @rcount=1;print @rcount;select @rcount=0;print @rcount;----------------------------求平均值 if else 判断   begin end  就是大括号啊use master;select english from score;declare @mark float;select @mark=AVG(english) from score;print @mark;if @mark>=60begin     select top 3 * ,标记='前3名'from score order by english desc    endelsebegin      select top 3 * ,标记='后3名'from score order by english asc      endgo------------------------while   求1-100 的和   while declare @sum int;declare @suum int;declare @i int;set @sum=0;set @suum=0;set @i=1;while @i<=100begin   if @i%2<>0   begin      set @sum=@sum+@i      set @i=@i+1;   end      else      begin      set @suum=@suum+@i      set @i=@i+1;      endendprint @sum;print @suum;go-----------------------------while实现如果english不及格的人超过半数，则给每个人增加2分declare @av intdeclare @bv intselect @av=COUNT(*) from score;select @bv=COUNT(*) from score where english<60;while @bv>@av/2begin      update score set english=english+2;     select @bv=COUNT(*) from score where english<60;endselect * from score -------------------------------事务  ----转账问题create table Bank(  Cid char(4) primary key,  balance money)alter table Bankadd constraint CH_balance check (balance>=10)godelete Bank;select * from Bank;goinsert into Bank values('0001',1000)insert into Bank values('0002',10)goupdate Bank set balance=balance-1000 where Cid=0001;update Bank set balance=balance+1000 where Cid=0002;----↑不加事务时Cid1因为约束原因没有执行,但是Cid2还是+了1000这时update1与update2是相互独立的----↓事务，就是把一系列操作作为    一件事     处理begin tran------------------打开事务declare @error int update Bank set balance=balance-1000 where Cid=0001;set @error=@error+@@ERRORupdate Bank set balance=balance+1000 where Cid=0002;set @error=@error+@@ERRORif @error=0--没有错误begin    commit tran-------------提交事务endelsebegin    rollback tran-----------回滚事务endselect * from Bank---------------------------try catch    begin try--end try  begin catch---end catchbegin trybegin tranupdate Bank set balance=balance-1000 where Cid=0001;update Bank set balance=balance+1000 where Cid=0002;commit tranend trybegin catchrollback tranend catch------------------------------隐式事务set implicit_Transactions on---隐式事务开delete from Bankrollbackselect * from Bankset implicit_Transactions off--隐式事务关-------------------------------系统存储过程exec sp_renamedb 'test','test123'exec sp_tables;exec sp_columns 'student'-------------------------------自定义存储过程create proc usp_helloworldasbegin    print 'hello world'endexec usp_helloworld;