SQL 语句

casethen

*********************************



select * from studentStatistics
go 
print '成绩如何分级如下：'
select student_name,score_Avg,
     score=case
       when score_avg between 90 and 100 then 'A'
       when score_avg between 75 and 89 then 'B'
       when score_avg between 60 and 74 then 'C'
       when score_avg<60 then 'D'
       else  '缺考'
     end
from studentstatistics
go



*****************************

go
************


use master 
go
select * from studentinformation
select * from studentscore
update studentscore
set score_test=score_test*(0.7)
go






************

if-else
***************


declare @avgAll float
select @avgAll=avg(score_avg)from studentstatistics
print '平均分：'+convert(varchar(10),@avgAll)
if(@avgAll>70) 
   begin 
       print '前三甲为：'
       select top 3 student_name,score_avg
       from studentStatistics 
       order by score_avg desc
   end
else
   begin
       print '如下同学需要加油：'
       select  top 3 student_name,score_avg 
       from studentstatistics 
       order by score_avg
 end 





********************

transaction
******************************



begin transaction
declare @errorCount int
set @errorCount=0
update bankTest
set current_money=current_money-1000 --999
where customer_name='A'
set @errorCount=@errorCount+@@error
update BankTest
set current_money=current_money+1000
where customer_name='B'
set @errorCount=@errorCount+@@error
print '账户余额：'
select * from BankTest
if(@errorCount!=0)
begin
 print '转账失败，执行回滚'
rollback transaction
end
else 
begin 
print '转账成功，执行提交'
commit transaction
end 
print '账户余额：'
select * from BankTest
go




*************

virable1

************



use testNccp
go
declare @age int
set @age=
   (select student_age from studentinformation
where student_name='成龙')

select * from studentinformation
where student_age=@age
go




***********

virable2

*******************



use testNccp
go
declare @name varchar(20),@age int
set @name='成龙'
set @age=
   (select student_age from studentinformation
where student_name=@name)

select * from studentinformation
where student_age=@age
go







***************
while
***********************




create table studentTest
( student_name varchar(20),score_java int)

go 
insert into studentTest values('haobin',57)

go
declare @scoreJava int
while(1=1)
    begin
     select @scoreJava=(
         select score_java
         from studenttest
         where student_name='haobin'
        )
     if (@scorejava<60)
         begin
           print '加一分'
           update studentTest
           set score_java=score_java+1
           print '成绩为：'+convert(varchar(50),@scoreJava+1)
         end 
     else 
         break
  end 
go