SqlServer视图、存储过程、分页

基础

--先复习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
 上面主要是为了存储过程、及游标做准备的!

视图

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

存储过程

--存储过程是一段可执行服务端程序(类似方法)
--优点:执行速度更快;允许模块化程序设计(复用);提高系统安全(防止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

常用的分页语句

--取出表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
大数据量暂时未测