sql server 分页、存储过程、视图

基础

  --先复习sql基础
  --查询语句执行顺序
  --from→where→group by→having→select(column(列)→distinct→top) 
  select * from Info;
  --将数据取出存放到临时表中 #temp局部临时表 ##temp全局临时表
  select * into #temp from(select * from Info) as t; 
  --查询临时表
  select * from #temp;
  --删除临时表
  drop table #temp;
  --删除数据
  delete from Info where uId>1000000
   
  --与delete区别:truncate删除数据快,会重置自增长主键的默认值,不触发delete触发器
  truncate table Info
   
  --变量 
  --用@来声明局部变量 先声明,后赋值 如:@Id
  --用@@来声明全局变量  内置常用的有:@error 返回错误号
  --                                  @identity 返回上次插入的主键Id、
  --                                  @rowcount 返回上次操作影响的行数
  --定义变量并初始化值不然,就是null
  declare @a int set @a=0
  print @a
  declare @b int set @b=1
  print @b
  declare @sum int set @sum=@a+@b
  print @sum
   
  --if语句
   if @sum>@a
     begin
         print '城管强'
     end
   else
     begin
         print '拆迁队牛'
    end
   
  --while语句
  declare @value int set @value=0
  declare @sum int set @sum=0
  while @a<100
  begin
    set @a=@a+1
    set @sum=@sum+@a
  end
  print @sum

    上面主要是为了存储过程、及游标做准备的!

视图

  --先复习sql基础
  --查询语句执行顺序
  --from→where→group by→having→select(column(列)→distinct→top) 
  select * from Info;
  --将数据取出存放到临时表中 #temp局部临时表 ##temp全局临时表
  select * into #temp from(select * from Info) as t; 
  --查询临时表
  select * from #temp;
  --删除临时表
  drop table #temp;
  --删除数据
  delete from Info where uId>1000000
   
  --与delete区别:truncate删除数据快,会重置自增长主键的默认值,不触发delete触发器
  truncate table Info
   
  --变量 
  --用@来声明局部变量 先声明,后赋值 如:@Id
  --用@@来声明全局变量  内置常用的有:@error 返回错误号
  --                              @identity 返回上次插入的主键Id、
  --                              @rowcount 返回上次操作影响的行数
  --定义变量并初始化值不然,就是null
  declare @a int set @a=0
  print @a
  declare @b int set @b=1
  print @b
  declare @sum int set @sum=@a+@b
  print @sum
   
  --if语句
   if @sum>@a
     begin
         print '城管强'
     end
   else
     begin
         print '拆迁队牛'
    end
   
  --while语句
  declare @value int set @value=0
  declare @sum int set @sum=0
  while @a<100
  begin
    set @a=@a+1
    set @sum=@sum+@a
  end
  print @sum

存储过程

   --存储过程是一段可执行服务端程序(类似方法)
   --优点:执行速度更快;允许模块化程序设计(复用);提高系统安全(防止SQL注入);减少网络流通量
   create proc usp_UserDefineProcedure
   @paremeterA int,
   @paremeterB int,
   @totalSum int output
   as
   begin
   set @totalSum=@paremeterA+@paremeterB
   if(@totalSum < 10)
        begin
         print 'so easy!'
        end
    else
      begin
         print 'too difficult'
      end
   end
   --声明变量 
   declare @Sum int
   exec usp_UserDefineProcedure 1,8,@totalSum = @Sum OUTPUT --执行存储过程 有参数的后跟参数(有输出参数 要先定义变量)
   print @Sum
   --删除存储过程
   drop proc usp_UserDefineProcedure

事务

 --事务允许你定义一个操作单元,要么全部成功,要么全部失败
 --开启事务
 begin tran
 declare @error int =0 --声明一个局部变量 接收全局@error
 set @error=@error+@@ERROR
 update  TblScore set tEnglish=59 where tScoreId=1
 set @error=@error+@@ERROR
 if(@error<>0)
    begin
     rollback tran;
     print 'update failed'
    end
 else
    begin
     commit tran;
     print 'success'
    end  

游标

--简单使用
--定义游标 有多种游标 fast_forward for/
  declare cur_MyInfo cursor fast_forward for select * from Info
--打开游标
  open cur_MyInfo
--对游标进行操作
--单行操作
--fetch next from cur_MyInfo
--多行操作
--@@FETCH_STATUS说明 0代表 fetch语句执行成功 -1fetch语句失败或行不在结果集中 -2提取的行不存在
 while @@FETCH_STATUS=0
   begin
     fetch next from cur_MyInfo
   end
--关闭游标
  close cur_MyInfo
--释放游标
  deallocate cur_MyInfo

多表连接加分页查询

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY tree.Id ) AS rownum ,
                    tree.Id AS tid ,
                    tree.Name AS NAME ,
                    inde.Info AS info ,
                    inde.Title AS title
          FROM      dbo.T_Tree AS tree
                    INNER JOIN dbo.T_IndexInfo AS inde ON tree.Id = inde.Id
        ) AS t
WHERE   rownum BETWEEN 1 AND 2

常用的分页语句

    --取出表A中第31到第40记录(SQLServer,以自动增长的ID作为主键,注意:ID可能不是连续的
   --以上免为例(很多面试都会有这道题)
   --第一种 not in
   --先查询一部分数据排序,排除在外;适合用与sql server 2000及更高版本
   select top 10 * from Info where uId not in (select top 30 uId from Info order by uId)order by uId
    
   --第二种 先查出一部分数据 排序 用Max找出最大Id 然后将其Max(uId)最大排除在外;适合用与sql server 2000及更高版本
   select top 10 * from Info
   where uId >
             (
             select ISNULL(MAX(uId),0)  
             from
                   (
                   select top 30 uId from Info order by uId
                   ) a
             )
   order by uId
    
   --第三种 row_number
   --把所有的数据查询出来,进行重新编号,通过where条件进行筛选数据;适用于sql server2005及更高版本
     select top 10 * from (select row_number() over (order by uId) as rowId,* from Info)t where rowId>30
    
   --第四种 offset fetch
   --offset后参数 越过多少条 fetch next后参数 取多少条;适用于sql server2012
   select * from Info order by uId offset (30)row fetch next 10 rows only

下面表格简单数据量不大的测试

方式取1000排除10000取1000排除100000取1000排除500000取10000排除100000取10000排除500000取10000排除900000
not in 75  133 1085 377 719 1035
max 82 76 236 205 270 1037
row_number 126 955 662 1040 4785 2618
offset fetch 79 407 186 180 239 882

offset要比row_number/not in/max方式要好,简洁更给力,not in/max(更通用)
小数据的情况下 max最好 offset次之 紧跟not in 最后row_number

posted @ 2013-03-27 17:43  秋壶冰月  阅读(1057)  评论(0编辑  收藏