常用的sql语句的总结（触发器、游标、case判断）

—————————————————case判断———————————————————————————

--case用于不等的判断
--优化：低于60分的学生提示不及格
select tsname,
isnull(
case when tEnglish<60 then '不及格' else CAST(tEnglish as varchar(10)) end
,'缺考')
from TblStudent
left join TblScore on TblStudent.tSId=TblScore.tSId

--判断等的情况
use hem09

select * from Employee
update Employee set eGender=1 where eName='sk'

--将性别显示成男和女
select *,case egender when 0 then '男' when 1 then '女' end
from Employee

--有一个财务流水表
--MoneyFlow	fid	fTitle	fMoney
create table MoneyFlow
(
	fid int identity(1,1) primary key not null,
	ftitle nvarchar(10),
	fmoney money
)

select * from MoneyFlow
insert into MoneyFlow values('发工资',1000)
insert into MoneyFlow values('奖金',500)
insert into MoneyFlow values('捡钱',200)
insert into MoneyFlow values('请客',-1500)
insert into MoneyFlow values('洗脚',-3000)

select fid,ftitle,case when fmoney>0 then fmoney else 0 end as '收入',
case when fmoney<0 then ABS(fmoney) else 0 end as '支出'
from MoneyFlow

 —————————————————触发器———————————————————————————

--触发器
--在保存数据时,完成备份
select * from TblOrders

create trigger bak_order
on tblorders
after insert
as
begin
	insert into tblOrders_bak(oname,ocount,oprice)
	select oname,ocount,oprice from inserted
end

alter trigger update_order
on tblorders
instead of update
as
begin
	update tblOrders_bak set ocount=(select ocount from inserted)
	where oname in (select oname from inserted)
end


select * from TblOrders
where oname='ff'
select * from tblOrders_bak
where oname='ff'

drop trigger update_order


insert into TblOrders values('ff',10,20)

update TblOrders set ocount=20 where oname='ff'

  —————————————————游标———————————————————————————

----============游标
--游标的定义
declare s1 cursor for select oname,ocount,oprice from tblorders
--在访问游标中某行的数据时,需要声明变量
declare @name nvarchar(10)
declare @count int
declare @price money
--打开游标
open s1
--通过游标从结果集中取数据,注意:每次取一行的值
fetch s1 into @name,@count,@price
while(@@FETCH_STATUS=0)
begin
	set @price=@price+RAND(100)
	update TblOrders set oprice=@price where oname=@name and ocount=@count
	fetch s1 into @name,@count,@price
end
--关闭游标
close s1
--释放游标
deallocate s1