数据库的查询与存储过程

范围查询：

//dbo.AdministratorsInfo 是一张表，Admin_Id 主键Id

//第一种top查询
select top 10 * from dbo.AdministratorsInfo where Admin_Id not in(select top 10 Admin_Id from dbo.AdministratorsInfo)

//第二种max()查询
select top 10 * from dbo.AdministratorsInfo where Admin_Id>(select MAX(Admin_Id) from dbo.AdministratorsInfo where Admin_Id in(select top 10 Admin_Id from dbo.AdministratorsInfo))

//第三种between  and查询
select * from dbo.AdministratorsInfo where Admin_Id between 1 and 20

//第四种ROW_NUMBER()over()查询
select * from (select *,ROW_NUMBER() over(order by Admin_Id)as number from dbo.AdministratorsInfo)t where t.number between 10 and 20

存储过程

//Aebs表名，declare 定义，set赋值，while（）BEGIN END循环，exec存储
//存储过程：循环
CREATE PROCEDURE Aebs
AS
	declare @count int;
	set @count=10;

	while(@count>0)
BEGIN
    print @count;
    set @count=@count-1
	
END
exec  Aebs


//存储过程：分页查询
create proc [dbo].[pagelistproc] (@pageIndex int,
@pagesize int,@tableName varchar(200),@columnName varchar(500),@orderby varchar(50),@sort varchar(50)
)as
declare @sql nvarchar(2000);
set @sql='select  '+@columnName+'  from(select '+@columnName+' ,ROW_NUMBER()over(order by '+@orderby+' )as number from '+@tableName+' )t where t.number between '+cast(((@pageIndex-1)*@pagesize)as varchar(200))+' and '+ cast((@pagesize*@pageIndex) as varchar(200))
exec(@sql)

//调用
//在新建查询中调用pagelistproc  参数（第几页，多少条记录，表名，需查询的字段，排序的字段，升序/倒序，）
 
exec pagelistproc 1,5,'dbo.AdministratorsInfo','*','Admin_Id','desc'