11-8（今天老师上课讲的）

 

--1声明变量  @age和@name
declare @age int
declare @name varchar(50),
--2赋值
----(1)方法1
set @age=18
set @name ='lxf'
----(2)方法2
select @age =18
select @name ='lxg'

--3分别使用set和select 为行号变量@rcount赋值
declare @rcount int
set @rcount=(select count(*)from student )--先查询后赋值
--select @rcount =COUNT (*)from student --即查询有赋值
print @rcount

---********************************注意
select * from student
select * from Score
declare @sAge int
--SET @sAge=(select sAge from Student)--出错！子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
select  @sAge= sAge from Student--当查询返回多值的时候，是把 最后一个值赋值给变量。
print @sage
----*********
declare @Fage int ---只定义，没有赋值，因此@Fage为null
set @Fage=@Fage+1
select @Fage--这里输出的就是null
--计算score表中english平均成绩，如果〉=60，查询正数前三名，否则找出倒数前三名
--方法一
select AVG (ISNULL (english,0))
from Score
update Score set english =59 where english is null
update Score set english =69 where studentId =11
update Score set english =65 where studentId =10
select * from Score
--方法二
declare @count int
select @count=count(*)from score
declare @sum float
select @sum =SUM (english )from Score
declare @avg float
select @avg=(@sum/@count)
if @avg >=60
begin
select top 3 * from Score order by english desc
end
else
begin
select top 3* from Score order by english asc
end
--4通过while 计算1---100之间奇数的和。
declare @sum2 int=0
declare @i int=1
while @i<=100
begin
  if @i%2 <>0
  begin
  set @sum2=@sum2+@i
  end
  set @i=@i+1
end
print @sum2
--5通过while 计算1---100之间偶数的和。
declare @sum3 int=0
declare @i1 int=1
while @i1 <=100
begin
  if @i1 %2=0
  begin
  set @sum3=@sum3+@i1
  end
  set @i1 =@i1 +1
end
print @sum3
--6使用while实现如果english不及格的人超过半数，则给每个人增加2分,循环加，直到不及格的人数少于一半。
  --1,计算总人数
  declare @count2 int =(select COUNT(*)from Score)
  --2,计算英语不及格人数
  declare @loster int=(select COUNT(*)from Score where english<60)
 
  declare @harfcount int=floor(@count2/2)
  while @harfcount<@loster
    begin
       update Score set english=english+2 where english<60
       set @loster=(select COUNT(*)from Score where english<60)
    end
  --7使用事务实现--转账问题：从0001账户转1000块到0002账户。 
create table Bank
(
  Cid char(4) primary key,
  balance money
)
alter table Bank
add constraint CH_balance check (balance>=10)
insert into Bank values('0001',1000)
insert into Bank values('0002',10)
update Bank set balance=balance-1000 where Cid='0001'
update Bank set balance=balance+1000 where Cid='0002'
--事务，就是把一系列操作作为一件事处理，要么都完成，要么都不完成！
--begin transaction--打开事务

begin tran--打开事务
begin try

declare @errorSum int =0
update Bank set balance=balance-900 where Cid='0001'
set @errorSum=@errorSum+@@error
update Bank set balance=balance+900 where Cid='0002'
set @errorSum=@errorSum+@@error
commit
print '提交！！'

end try
begin catch
   rollback
   print '回滚！'
end catch
commit tran--提交事务
rollback tran--回滚事务

-----'自动提交事务'：系统检测sql语句是否出错，如果没有错误就自动提交--
insert into Bank values('0003',1000)
--如果希望手动提交，可以回滚在执行插入等操作的时候：
begin tran--打开事务
insert into Bank values('0004',4200000)
rollback--手动回滚或提交事务
--commit

 

--***************************"隐式事务"：默认情况为关，如果打开了则不自动提交，学要手动提交。
set implicit_Transactions on
delete from Bank
rollback
select * from Bank
--如果隐式事务打开，然后删除某个表，在事务没有结束前，其它查询不能访问该表。
--这就是“锁”，由于只是执行了一个sql的操作，没有结束事务，就会把表锁住，不让别人在对他进行操作。放置并发问题出现。
set implicit_Transactions off

----可以给事务起名，当多个事务存在时用以区分。
begin tran tran1
rollback tran1
commit tran1

--如果没有打开的事务，执行rollback时，就会报错。

------六、存储过程！！
--与C#中的方法一样。存储过程 有 名/可以有参数/可以有返回值。

--6.1系统存储过程
--放在系统数据库 master 中的--可编程性---存储过错--系统存储过程中。
  --select * from sys.databases
  exec sp_databases
  ----sp_databases的 内部 代码
   select
        DATABASE_NAME   = db_name(s_mf.database_id),
        DATABASE_SIZE   = convert(int,
                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                    when convert(bigint, sum(s_mf.size)) >= 268435456
                                    then null
                                    else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb
                                    end),
        REMARKS         = convert(varchar(254),null)
    from
        sys.master_files s_mf
    where
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
    group by s_mf.database_id
    order by 1
---使用 sp_helptext 来显示一个 存储过程 中的 代码 。
exec sp_helptext sp_databases
exec sp_helptext sp_helptext
--给数据库重命名：
exec sp_renamedb 'Test','Test123'
--查询当前数据库中有多少个表
exec sp_tables
--查询当前数据库中的列信息
exec sp_columns 'Student'
-------------------------------------------------
--以上是 几个 系统的存储过程：以sp_ 开头，exec
--自定义存储过程一般是以 usp_开头
create proc usp_Helloworld
as
 begin
   print 'hello world!'
 end
 
 exec usp_Helloworld
 --创建一个计算2个数的和的存储过程
 
   select * from Score  
   update Score set english=69 where english is null  
  update Score set english=22 where scoreId=9 
  ---7个人一半按3算，7/2向下取正。
print floor(7/2.0)
print ceiling(7/2.0)
print 7/