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