基础
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
下面表格简单数据量不大的测试
方式 | 取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
大数据量暂时未测