SQL存储的操作

--实现数据库查询从十一到二十的记录的四种方法：
--top
select top 10 * from AdminInfo as a where a.Id not in(select top 10 Id from AdminInfo )

--MAX
--首先
select top 10 Id from AdminInfo
--其次
select MAX(Id) from AdminInfo as a where  a.Id in(select top 10 Id from AdminInfo)
--整合
select top 10 * from AdminInfo as b where b.Id>(select MAX(Id)
 from AdminInfo as a where  a.Id in(select top 10 Id from AdminInfo)
);

--between and
select * from AdminInfo where Id between 11 and 20

select * from (select *,ROW_NUMBER()over(order by Id)as number from AdminInfo)
 as a where a.number between 11 and 20


--sql中创建循环的存储过程
create procedure xh (@count int )
as 
declare @num int ;	--定义变量名
set @num=@count;	--给变量名赋值
while(@num>0)		--循环方法
begin 				--条件的开始
print @num;			--输出
set @num-=1;		--循环赋值
end					--条件的结束
exec dbo.xh 3		--执行存储过程通过关键字exec 存储过程的方法名 所需参数


--通过·sql实现分页存储过程
exec dbo.pageListproc 1,5,'dbo.AdminInfo','*','Id','ASC'   		--执行调用自定义方法并给指定参数

create procedure pageListproc(@page int,
@pagesize int,@tableName varchar(200),
@columnName varchar(500),
@oederByName varchar(50),
@sort varchar(50))
as
declare @sql nvarchar(2000);		--定义一个查询分页的sql语句
--编写sql查询分页  并将所需表，列，页码等用所传的指定参数赋值替代
set @sql='select ' +@columnName+ ' from (select ' +
@columnName+ ',ROW_NUMBER()over(order by ' +@oederByName+ ')
as number from ' +@tableName+ ') 
as a where a.number 
between ' +CAST(((@page-1)*@pagesize) 
as varchar(200))+ ' and '+CAST((@page*@pagesize)as varchar(200))+'';