数据库的高级应用
数据库的高级应用
数据库五大高级应用：
视图（view）索引（index）存储过程（proc）触发器（trigger）事务（trans）
●●●●●●●●●●●●下面将对这五大应用详细解●●●●●●●●●●●●
一 视图（view）：只是用来快速展示查询数据：
主要内容：
l 视图的概念以及优缺点
视图是一张虚拟表，他所展现的内容并没有存储在视图表中而是存储在他所引用的表中。
优点; 简化操作；根据不同用户定制不同数据；合并分割数据；安全性；
缺点：性能不高；数据修改受限；
l 单表视图 多表视图 基于视图的视图
创建视图 语法结构：
Create view 视图名[(column ,```````)]--column 列名
[with encryption ]
As
[With check option]--这条语句作用：1 规定在视图上做过的修改必须符合引用表的要求；2 通过视图修改的数据可以确保修改后的数据仍然可以通过视图看到。
注意点：视图中的查询语句中不能包含 order by/ compute /compute by 或者是into关键字
例子：1单表视图
创建视图 create view jiage
As select *from Car where Price between 30 and 50
go
引用 ：select *from jiage
2带有where 字段的视图
Create view view2
As
Select 姓名，工资 from 职工 where 工资>1800
Go
3带有计算字段的视图
Go
2 多表视图
3 带有聚合函数的视图
4更改原表中的字段名称
create view view1 (车名,车的价格) --将新设置的列名在此设置。
as
select name,price from Car where Name like'%宝马%'
go
select *from view1
l 利用sql 代码修改视图
alter view view1 (啥车,多钱) --可以同时更改原有视图中的列名
as
select name,price from Car where Name like '%奥迪%'
go
select *from view1
l 利用sql 删除 视图
《删除单张视图》 drop view 视图名 《删除多张视图》 drop view view1，view2~~~~
《带有判断条件删除视图》
l 视图数据的查询
l 通过视图添加更新以及删除数据
二 索引（index）
创建索引：create [unique][clustered][nonclustered] index index-name1 on [table|view](列名 asc|desc)
Unique 创建唯一索引，即索引的键值不能重复，在列包含重复值是不能创建唯一索引；
Clustered 指明创建的索引为簇索引，如果此选项默认，则创建的为非簇索引。
Nonclustered 指明创建的索引为非簇索引
例子： 1创建普通索引create index idex-scl on 仓库备份（面积）
2创建多列普通索引 create index idex-sc2 on 仓库备份（城市，创建时间）
3 删除索引 drop index 表名.索引名 drop index 仓库备份.index1
三存储过程（proc）
主要内容：
l 存储过程的分类以及优点
定义：为了完成特定功能的sql集合，编译后存储在服务器端数据库中，利用存储过程可以加速sql 语句的执行。
存储过程可以分为两类：系统存储过程和自定义存储过程。
系统存储过程：系统存储过程是在sql 成功安装后存在系统数据库master 中的，这些存储过程是以sp- 为前缀命名的，主要功能是在系统表中获取信息，系统管理员可以通过简单的调用系统存储过程完成复杂的管理工作，调用系统存储过程时在其他非master数据库中也可以调用 。
自定义存储过程：有用户自己定义完成特定功能，返回值只能表明返回值执行是否成功而且只能利用 想、execute 完成存储过程。
优点： 1 提高应用程序的通用性和可移植性 2 可以更有效的管理用户操作数据库的权限3 可以提高sql的速度 4 减轻服务器的负担。
l 创建并执行存储过程
语法结构：
Create procedure 过程名
@ming 参数类型 output
~~
~~~
As
Begin 命令块 end
创建完成后利用execute 过程名 [参数值~~][output]
例子：1功能为求0-5之间的和
alter proc proc3
as
declare @sum int,@a int
select @a=0,@sum=0
while @a<6
begin
set @sum=@sum+@a
set @a=@a+1
End
print '和是'+cast(@sum as varchar)
Go
Exectute proc3
功能2 判断系统商品库存 以及购买者账户金额 决定交易是否进行
create proc shuiguo2
@idss varchar(10),
@buyer varchar(10),
@shuliang int
as
--判断库存
if (select numbers from Fruit where Ids=@idss)>@shuliang
begin
if(select price from Fruit where Ids=@idss )*@shuliang>(select account from Login where UserName=@buyer )
begin
--减库存
update fruit set numbers=numbers-@shuliang where Ids=@idss
--减余额
update Login set Account=Account-(select price from Fruit)*@shuliang where UserName=@buyer
end
else
begin
print'余额不足，请充值！'
end
end
else
begin
print'抱歉，库存不足！'
end
go
功能3 不带参数的存储过程
4带输入参数的存储过程
5带有输入参数的数据查询功能的存储过程
6带有输入输出参数的存储过程
7带有登陆判断功能的存储过程
8 带有判断条件的插入功能的存储过程（exists）
9带有判断条件的删除功能的存储过程
10 带有判断条件的更新功能的存储过程
11加密存储过程
Create proc 过程名
With encryption
As 命令块 Go
★ 查看存储过程的属性以及功能代码信息
execute sp_help proc3 --查询存储过程的属性信息
execute sp_depends proc1--查询存储过程所使用的数据对象的信息,如果没有引用或者加密会提示 没有引用.
execute sp_helptext proc3--查询存储过程的功能代码,如果加密后不能被查询到
select name from car where type ='p'--能共查找本表创建存储过程的名字和时间
l 修改存储过程的功能代码
Alter proc 过程名
@参数 参数类型 ~~~~
As
Begin 命令行 end
l 重命名存储过程
Execute sp_rename 原名， 新名
l 删除存储过程
Drop proc 过程名1，过程名2~~ 同时可以删除多个过程
带有判断条件的删除存储过程（与if exists语句结合）
l 存储过程的自动执行和监控
l Oracle 和DB2数据库中存储过程的区别
四 触发器（trigger）
l 触发器与存储过程的区别
1.存储过程是独立于表存在的，触发器需要依附某个表的某个操作。
2.存储过程需要使用名称去调用才能执行，触发器则在表的操作过程中自动被触发调用。
l 触发器的分类和作用
定义; 触发器是一种特殊类型的存储过程，通过事件的触发来被执行，例如update delete drop alter drop等。 在sql 中有两种方法可以保持数据的完整性和有效性：约束和触发器，约束直接设置在表内只能执行一些比较简单的功能，而触发器可以执行比较复杂的过程。
作用： 1 可以调用存储过程 2强化数据条件约束 3跟踪数据库内数据的变化并判断数据变化是否符合数据库的要求 4级联和并行运行。
分类：DML 触发器 DDL触发器
DML触发器：data manipulation language 又可以分为 事后触发器（after触发器）替代触发器 （instead of）
After 触发器 先执行变得增删改操作后在触发触发器 。
Instead of 触发器 不执行表的增删改操作，它的这些操作只起到触发触发器的功能。
DDl 触发器用来执行数据库的管理任务。
注释：
触发器中两个临时表：inserted,deleted
这两个表是临时表，触发器执行完成后，会自动消失，再次触发会再次创建。
这两个表的结构与on后面的那表的结构是一样的（列名、列数、类型）。而且里面只有一条记录。
插入操作--把新增的数据放到inserted表中。
删除操作--把删除的数据放到deleted表中。
修改操作--把旧数据放到deleted表中，把新数据放到inserted表中。
对两个临时表的使用。
从两个临时表中把数据取出来放到变量中，以备后面的使用。
l 创建执行事后触发器
After 触发器注意事项：触发器名在同一数据库中是唯一的；可以利用with encryption 对触发器进行加密； 只能定义在数据表中不能定义在视图上；after触发器的动作只能是update delete insert 三种 其中的一种或者多种。
创建语法：
Create trigger 触发器名 on 表名 for insert[update delete]
As
Begin
命令
End
实例：运行触发器当数据表进行增删改时自动发送邮件
Create trigger 触发器名 on表名 for insert[update ，delete]
As begin
Execute xp_sendmail ‘邮箱地址’
end
l 创建与执行替代触发器
注意点：instead of触发器主要作用是使不可修改的视图能够支持修改。当然也可以对表进行操作。
构建语法： Create trigger 触发器名 on 表名或者视图名 instead of insert[delete update]
As
Begin
命令块
End
Go
l 查看触发器的属性以及功能代码信息
l 修改触发器的功能代码
l 重命名和删除触发器
Sp_rename 原名，新名 --重命名触发器
Drop trigger 触发器名【123】--可以同时删除多个触发器
l 禁用和启用触发器
Alter table 表名 disable trigger 触发器名 禁用触发器
Alter table 表名 enable trigger 触发器名 启用触发器
Alter table 表名 enable trigger 触发器名1，触发器名2 启用多个触发器触发器
Alter table 表名 enable trigger all 启用 这个表中的所有触发器
l 创建与执行ddl 触发器
DDL 触发器一般用于数据库的管理工作， 其触发事件为 create alter drop 开头的语句
创建DDl 触发器语法结构：
Create trigger 触发器名 on 服务器或者是数据库
For或者after create[drop alter ]
As begin
命令块
End
注意点： DDl 触发器没有替代触发器只有事后触发器，不同是作用于数据库还是服务器
l 修改和删除ddl 触发器
l 触发器的使用
五事务（begin trans rollback返回 commit确定）
