SqlServer视图、存储过程、分页

最新推荐文章于 2022-11-22 17:06:43 发布

Rki-dor

最新推荐文章于 2022-11-22 17:06:43 发布

阅读量183

点赞数

文章标签：数据库系统安全大数据

原文链接：http://www.cnblogs.com/qq0827/articles/3209325.html

版权

基础

   1:   --先复习sql基础

   2:   --查询语句执行顺序

   3:   --from→where→group by→having→select(column(列)→distinct→top)

   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

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

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

转载于:https://www.cnblogs.com/qq0827/articles/3209325.html

关注