开窗函数，视图，事务，存储过程，索引，触发器，游标_索引，触发器，存储，游标

• 补充：DDL

create table TableName
(
    fid int identity(1,1) primary key not null,
    ftitle nvarchar(10),
    fmoney money
)
1
2
3
4
5
6

• case：

例：将性别显示成男和女
select *,case eGender when 0 then '男' when 1 then '女' end
from TblEmployee

例：case用于不等的判断，低于60分的学生提示不及格
select tsname,
isnull(
case when tEnglish<60 then '不及格' else CAST(tEnglish as varchar(10)) end
,'缺考')
from TblStudent
left join TblScore on TblStudent.tSId=TblScore.tSId
1
2
3
4
5
6
7
8
9
10
11

• 变量声明

声明：declare @UserName nvarchar(10)
赋值：set UserName='test2'或select @UserName='test2'
输出：print @UserName或select @UserName

如：
declare @uname nvarchar(10)='aa'
set @uname='bb'
print @uname
select @uname


例：求1-100的和
declare @j int=1
declare @sum int=0
while(@j<=100)
begin
    set @sum+=@j
    set @j+=1
end
print @sum
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

• 全局变量：@@……
  @@identity：与一个insert语句连用，表示最新生成的标识
  @@error：返回最近一个语句的错误编号，如果没有错误返回0，结合if替代try—catch的使用

• over子句
  开窗函数：将多行数据进行划区。几个行作为一个区，就被称为一个窗，能够进行按行划区的函数就是开窗函数。
  排名函数：rank() over(order by 列名 desc)，比row_number()函数更适合用于排名。
  用途一：排序order by ,row_number
  用途二：划区partition by,结合聚合函数针对某部分数据进行汇总
  关于rank()和row_bumber:
  rank()：这个函数，成绩相同的两名是并列，例如下图1 2 2 4
  
  row_number()：：这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名，如下图
  
  (来自网络)

例：按照英语成绩降序排列
select *,RANK() over(order by tenglish desc) from TblScore

传统的统计汇总：这个语句表示对整个数据进行划区
select oname,SUM(ocount) from TblOrders group by oname

分页,要求3条数据显示一页
select * from(
    select *,row_number() over(order by oprice desc) as num
    from TblOrders
) t1
where num between 4 and 6
1
2
3
4
5
6
7
8
9
10
11
12

• 视图
  视图就是一个select语句，需要多次使用，并且语句较复杂时可以考虑创建视图
  创建视图语法：
  create view 名称 as 查询语句
  查看视图中的代码
  exec sp_helptext 视图名称
• 事务
  事务：操作全部成功，则提交，如果有一条语句失败则回滚，整体操作失败。
  操作：
  begin transaction[tran] ——开始事务
  comit transaction[tran] ——提交事务，没错后执行
  rollback transaction[tran] ——回滚，出错后执行

例：
declare @errornum int=0
begin tran
insert into tblorders values('测试数据')
set @errornum+=@@error
if(@errornum>0)
begin
    rollback tran
end
else
begin
    commit tran
end
1
2
3
4
5
6
7
8
9
10
11
12
13

• 存储过程
  存储过程：就是一个函数，存储了一段处理代码。可以封装代码，实现代码重用。
  语法：
  create proc 存储过过程名
  参数列表
  as
  自定义代码段
  调用： exec 存储过程名
  查看存储过程代码：exec sp_helptext 存储过程名

例：
1、存储过程完成一段sql代码的封装
create proc trim
@str varchar(10)
as
declare @str1 varchar(10)
set @str1=LTRIM(RTRIM(@str))
print @str1

调用存储过程：
exec trim ' abc '

2、带输出参数的存储过程：求两个数的和
create proc sum1
@num1 int,
@num2 int,
@result int output--这个参数可以将结果带出存储过程
as
set @result=@num1+@num2

调用：
declare @result int
exec sum1 1,2,@result output--不写output关键字，调用会出错
print @result

3、参数带默认值的存储过程：默认值必须是最后一个参数
alter proc multi
@num1 int,
@num2 int=10 output--output的功能类似于C#中的ref修饰参数
as
set @num2=@num2*@num1
print @num2
--有值时：6
declare @num int=2
exec multi 3,@num output
select @num
--没有值时，使用默认值：10*3=30
exec multi 3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

• 索引
  索引：使数据查找起来更加快速。索引不是越多越好，因为没建立一个列的索引，都要排序存储一次，会让数据库文件变大。

例：
create index index_name
on tblorders(oname)
1
2
3

• 触发器
  触发器：用于进行增、删、改时，自动进行一个操作。根据触发机制：有after触发器，instead of替换触发器
  创建触发器语法：
  create trigger 触发器名称
  on 表名
  [after | instead of] [insert|delete|update ]
  as
  begin
  ……
  end

例：在保存数据时,完成备份
create trigger bak_order
on tblorders
after insert
as
begin
    insert into tblOrders_bak(oname,ocount,oprice)
    select oname,ocount,oprice from inserted
end
1
2
3
4
5
6
7
8
9

• 游标
  关于游标