sql变量、分页、存储过程、触发器

sql

1、case when……then……
select Num,case when money >0 then money else 0 END
as '收入',
case when money <0 then -money else 0 END as '支出'
FROM OrderMoney

2、@变量
declare @name varchar(50) --声明变量
set @name ='cnm!' --变量赋值
select @name ='en' --查询变量
declare @a int
set @a=1
set @a+=1
select @a
while(@a<50)

begin print str(@a)
set @a=@a+1
end

select *from bookinfo

declare @num int
select @num = count(*)from bookinfo where page <50
while(@num>0)
begin
update bookinfo set page = page +50
select @num =count(*)from bookinfo where page<50
end
select*from bookinfo

3、事务
开始事务：begin transaction
事务提交：commit transaction
事务回滚：rollback transaction
判断某条语句执行是否出错
全局变量@@ERROR
set @errorsum =@errorsum +@@error
(索引check 改为money>0)

declare @errNum int
set @errNum =0
begin transaction
update book set money=-1 where id =3
if(@@ERROR>0)
begin
set @errNum =@errNum+@@ERROR
end
update book set money=100 where id =13
if(@@ERROR>0)
begin
set @errNum =@errNum+@@ERROR
end
if(@errNum=0)
begin
commit transaction
end
else
begin
rollback transaction
end

4、存储过程
select top N条记录 * from 文章表 where id not in
(select top M条记录 id from 文章表 order by id desc )
order by id desc
select top N条记录 * from 文章表 where id <(select
min(id) from (select top M条记录 id from 文章表 order
by id desc ) as tblTmp) order by id desc
create 创建 alter 修改
alter proc us_getbookId
执行存储过程：
exec 存储过程 exec us_getbookId 2

create PROCEDURE GetPage
Data
(
@TableName varchar(30),--表名称
@IDName varchar(20),--表主键名称
@PageIndex int,--当前页数
@PageSize int--每页大小
)
AS
IF @PageIndex > 0
BEGIN
set nocount on
DECLARE @PageLowerBound int,@StartID int,@sql
nvarchar(225)
SET @PageLowerBound = @PageSize * (@PageIndex-1)
IF @PageLowerBound<1
SET @PageLowerBound=1
SET ROWCOUNT @PageLowerBound
SET @sql=N'SELECT @StartID = ['+@IDName+'] FROM
'+@TableName+' ORDER BY '+@IDName
exec sp_executesql @sql,N'@StartID int
output',@StartID output
SET ROWCOUNT 0
SET @sql='select top '+str(@PageSize) +' * from
'+@TableName+' where ['+@IDName+']>='+ str(@StartID)
+' ORDER BY ['+@IDName+'] '
EXEC(@sql)
set nocount off
END

5、分页
显示前5行，进行分页
create proc usp_GetIdBooks2
@idIndex int=1,
@idSize int= 5,
@rowCount int output
@idCount int output

as
select *from(
select row_number()over(order by bookid)as rl, * from
bookInfo
) as a
--where a.rl <=5
where a.rl > (@idIndex-1)*@idSize and a.rl
<=@idIndex*@idSize --分页条件
select @rowCount =count(*)from bookinfo
set @idCount = ceiling(convert
(float,@rowCount)/convert(float, @idSize))
--页码为整数，且向后去整。
set @idIndex=110
set @idSize=1000

exec usp_GetIdBooks2 1,7
执行语句（查第一页共7行）

declare @ii int,@is int,@rc int，@ic int
set @ii = 2 --变量：保存页码
set @is = 3 --变量：页容量（每页多少行）
set @rc = 0 --变量：总行数
set @ic = 0 --变量：总页数
exec usp_GetIdBooks2 @ii,@is,@rc,@ic output

select *from bookinfo
select count (*)from bookInfo

6、触发器 Trigger
--创建触发器 触发器名称 on 表名 for操作名
create TRIGGER bookinfo on Gategory for delete
as
begin
select *from bookinfo
end
delete bookinfo where bookid=4