SQL Server创建存储过程、触发器、函数等(学习札记)
视图是从一个或几个基本表(或视图)导出的表。不同的是，它是一虚表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然存放在原本的基本表中。所以一旦基本表发生变化，从视图中查询的数据也就随之改变.
作用：视图更加方便用户的查询。
存储过程是事先经过编译并保存在数据库中的一段sql语句集合.
使用时调用即可.
例： 返回学号为"2020005"学生的成绩情况，存储过程命名为p1
-- 如果不使用存储过程的查询
select * from sc where sno="2020005"
-- 存储过程：返回学号2020005学生的成绩情况
create proc p1
as
begin
select * from sc where sno="2020005"
end
--调用存储过程p1
exec p1
可编程性中查看建立的存储过程
上面的学号被写死了，现在假如有一个需求是：查询某同学指定课程号的成绩和学分，alter存储存储过程p1
alter proc p1 @sno varchar(13),@cno varchar(13)
as
begin
select sc.*,course.credit
from sc,course
where sno=@sno and sc.cno=@cno and sc.cno=course.cno
end
-- 执行存储过程(传入指定参数即可)
exec p1 '2020004','20203'
如何删除存储过程p1呢
drop proc p1
可以发现存储过程类似于传统编程语言中函数的概念。
触发器 触发器的定义：监视某种情况，并触发某种操作，当对一个表格进行增删改操作就有可能自动激活执行它，不同于存储过程需要手动调用执行。
触发器的语法结构如下：
create trigger trigger_name on [table_name | view_name]
[for | after | instead of]
[update | insert | delete]
as
begin
sql_statement
end
例：学生人数不能大于17
方法一：先插入后检查(after)，不符合要求则回滚(rollback)操作
create trigger t1 on stu
after insert
as
begin
if (select count(*) from stu) >17
begin
print 'error'
rollback tran
-- rollback transaction事务回滚
end
else
begin
print 'right'
end
end
方法二：先检查后插入(instead of)，如何符合条件之后才允许操作
alter trigger t1 on stu
instead of insert
as
begin
select * from inserted
select * from deleted
if (select count(*) from stu) >16
begin
print 'error'
rollback tran
end
else
begin
print 'right'
-- insert
declare @sno varchar(13)
declare @sname varchar(30)
declare @age int
select @sno=sno from inserted
select @sname=sname from inserted
select @age=age from inserted
insert into stu(sno,sname,age) values(@sno,@sname,@age)
end
end
例：如何删除一个触发器
drop trigger t1
例：当新增学生成绩55-59之间，将该学生的诚意修改为60分
create trigger t3 on sc
instead of inserted
as
begin
declare @sno varchar(13)
declare @cno varchar(13)
declare @grade decimal(5,2)
select @sno=sno from inserted
select @cno=cno from inserted
select @grade=grade from inserted
if @grade>=55 and @grade<=59
begin
set @grade=60
end
insert into sc values(@cno,@sno,@grade)
end
函数（自定义函数）
函数和之前讲过的存储过程很像，不同之处就是函数多一个return.
1-计算某门课程的平均分 output value
create function fun1(@cno varchar(13))
returns int
as
begin
declare @avg_score int
declare @avgscore = avg(grade) from sc where cno=@cno
return @avgscore
end
-- 调用函数语句
select dbo.fun1('20201')
2-输入专业号，返回学生学号和姓名(这个专业) output table
create function fun2(@mno int)
returns @snoSname table(
sno varchar(13)
sname varchar(30)
)
as
begin
declare @sno varchar(13)
declare @sname varchar(30)
select @sno=sno,@sname=sname from stu where mno=@mno
insert into @snoSname(sno,sname) values(@sno,@sname)
return
end
注：上面的语句块执行只会返回插入一条语句，所以想查询到的结果集都插入到返回表中，就需要边查询边插入数据。将上面的语句块修改成如下即可：
begin
insert into @snoSname(sno,sname) select sno,sname from stu where mno=@mno
return
end
--函数调用
select * from dbo.fun2(2)
3-输入专业号，return这个专业所有学生的每个课程对应成绩的一个表
create function fun3(@mno int)
returns @mSc table(
sno varchar(13),
cno varchar(13),
grade decimal(5,2)
)
as
begin
insert into @mSc select stu.sno,sc.cno,sc.grade from major,stu,sc where major.mno=stu.mno and stu.sno=sc.sno
and mno=@mno
return
end
-- 调用函数
select * fun3(1)
索引
定义：索引是对数据库表中的一列或多列值进行排序的一种结构
目的：加快查询速度(目录)，但是占用一定的存储空间，更新和维护
不创建索引原则：
1-频繁更新的字段或者经常增删改的表，不适合创建索引
2-表记录太少，不需要创建索引
3-如果某些数据包含大量重复数据，因此建立索引就没有太大的效果，例如性别字段，只有男(0)或女(1)，不适合建立索引.
SQL Server默认主键为聚集索引
聚集索引定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
非聚集索引定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序可能不同，一个表中可以拥有一个或多个非聚集索引。
1-sc表按学号升序和课程号降序建立唯一索引
create unique index scno on sc(sno asc, cno desc)
2-删除索引scno
drop index scno on sc
作者：Training.L
