SqlServer视图、存储过程、分页

基础

   1:   --先复习sql基础
   2:   --查询语句执行顺序
   3:   --fromwheregroup byhavingselect(column(列)→distincttop) 
   4:   select * from Info;
   5:   --将数据取出存放到临时表中 #temp局部临时表 ##temp全局临时表
   6:   select * into #temp from(select * from Info) as t; 
   7:   --查询临时表
   8:   select * from #temp;
   9:   --删除临时表
  10:   drop table #temp;
  11:   --删除数据
  12:   delete from Info where uId>1000000
  13:   
  14:   --与delete区别:truncate删除数据快,会重置自增长主键的默认值,不触发delete触发器
  15:   truncate table Info
  16:   
  17:   --变量 
  18:   --用@来声明局部变量 先声明,后赋值 如:@Id
  19:   --用@@来声明全局变量  内置常用的有:@error 返回错误号
  20:   --                                  @identity 返回上次插入的主键Id、
  21:   --                                  @rowcount 返回上次操作影响的行数
  22:   --定义变量并初始化值不然,就是null
  23:   declare @a int set @a=0
  24:   print @a
  25:   declare @b int set @b=1
  26:   print @b
  27:   declare @sum int set @sum=@a+@b
  28:   print @sum
  29:   
  30:   --if语句
  31:    if @sum>@a
  32:      begin
  33:          print '城管强'
  34:      end
  35:    else
  36:      begin
  37:          print '拆迁队牛'
  38:     end
  39:   
  40:   --while语句
  41:   declare @value int set @value=0
  42:   declare @sum int set @sum=0
  43:   while @a<100
  44:   begin
  45:     set @a=@a+1
  46:     set @sum=@sum+@a
  47:   end
  48:   print @sum
  上面主要是为了存储过程、及游标做准备的!

视图

   1:   --视图不存储数据(所以不能增/删/改),不能在视图中使用order by
   2:   --创建视图 
   3:   create view View_StudentScore
   4:   as
   5:      select student.stuName as '姓名',student.stuAge as '年龄',score.tScoreId as '学号',
   6:   case 
   7:         when score.tEnglish is null then '缺考'
   8:   else convert(varchar(16),score.tEnglish)
   9:   end as '英语',
  10:   case
  11:         when score.tMath is null then '缺考'
  12:   else convert(varchar(16),score.tMath)
  13:   end as '数学',
  14:   case
  15:          when score.tEnglish<=59 and score.tMath<=59 then '不及格'
  16:   else '及格'
  17:   end as '是否及格'
  18:   from dbo.TblStudent as student
  19:   left join dbo.TblScore as score on student.stuId=score.tSId

存储过程

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

事务

   1:   --事务允许你定义一个操作单元,要么全部成功,要么全部失败
   2:   --开启事务
   3:   begin tran
   4:   declare @error int =0 --声明一个局部变量 接收全局@error
   5:   set @error=@error+@@ERROR
   6:   update  TblScore set tEnglish=59 where tScoreId=1
   7:   set @error=@error+@@ERROR
   8:   if(@error<>0)
   9:      begin
  10:       rollback tran;
  11:       print 'update failed'
  12:      end 
  13:   else
  14:      begin
  15:       commit tran;
  16:       print 'success'
  17:      end    

游标

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

常用的分页语句

   1:  --取出表A中第31到第40记录(SQLServer,以自动增长的ID作为主键,注意:ID可能不是连续的
   2:   --以上免为例(很多面试都会有这道题)
   3:   --第一种 not in
   4:   --先查询一部分数据排序,排除在外;适合用与sql server 2000及更高版本
   5:   select top 10 * from Info where uId not in (select top 30 uId from Info order by uId)order by uId
   6:   
   7:   --第二种 先查出一部分数据 排序 用Max找出最大Id 然后将其Max(uId)最大排除在外;适合用与sql server 2000及更高版本
   8:   select top 10 * from Info
   9:   where uId >
  10:             (
  11:             select ISNULL(MAX(uId),0)  
  12:             from 
  13:                   (
  14:                   select top 30 uId from Info order by uId
  15:                   ) a
  16:             )
  17:   order by uId
  18:   
  19:   --第三种 row_number
  20:   --把所有的数据查询出来,进行重新编号,通过where条件进行筛选数据;适用于sql server2005及更高版本
  21:     select top 10 * from (select row_number() over (order by uId) as rowId,* from Info)t where rowId>30
  22:   
  23:   --第四种 offset fetch
  24:   --offset后参数 越过多少条 fetch next后参数 取多少条;适用于sql server2012
  25:   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-07-23 20:20  秋壶冰月  阅读(564)  评论(0编辑  收藏