Sql Server-视图存储过程函数触发器索引

最新推荐文章于 2024-08-10 13:50:07 发布

csdn_aspnet

最新推荐文章于 2024-08-10 13:50:07 发布

阅读量868

点赞数

1.视图

[sql]view plaincopy 
   
 视图不是真实存在的基础表而是一个虚拟表,视图所对应的数据并不实际以视图结构存储在数据库中,而是存储在视图所引用的表中  
 视图的分类  
     标准视图:  
     索引视图:  
     分区视图:  
 视图的作用  
     集中数据,定制数据,合并分隔数据,安全机制  
 视图最多1024列  
   
 --创建视图    
 create view v_emp    
 as     
     select * from d_emp    
 --删除视图    
 drop view v_emp    
 ----------------------------------------------------------------------  
 --对视图的操作  
 insert into v_dept values(1,'开发部')  
 insert into v_dept values(2,'财务部')  
 update v_dept set dept_name='人事部' where dept_id=2  
 delete from v_dept where dept_id=2  

2.存储过程

[sql]view plaincopy 
   
 存储过程是存放在服务器端数据库中的子程序  
 存储过程在第一次执行时,进行语法检测和编译,执行后,他的执行计划就驻留在高速缓存中,用于后续调用  
 存储过程的分类  
     系统存储过程:由系统提供,存放在MASTER数据库中,前缀为SP  
     用户存储过程:由用户创建  
 系统存储过程主要包含  
     目录存储过程  
     复制类存储过程  
     安全管理类存储过程  
     分布式查询存储过程  
 自定义的存储过程,应避免使用SP_为前缀.  
 因为接收到SP_开头的存储过程,SQL SERVER 首先会在 MASTER 数据库中寻找存储过程,这会影响执行效率  
 存储过程最多可以有2100个参数,最大为128MB  
   
 --不带参数的存储过程  
 create proc p_dept  
 as  
 select * from d_dept  
 ----------------------------------------------------------------------  
 --输入参数的存储过程  
 create proc p_dept  
     @dept_id int  
 as  
     select * from d_dept where dept_id=@dept_id  
 ----------------------------------------------------------------------  
 --输出参数的存储过程  
 create proc p_dept  
     @dept_id int,  
     @dept_name varchar(50) output  
 as  
     select @dept_name=dept_name from d_dept where dept_id=@dept_id  
 ----------------------------------------------------------------------  
 --输入输出合体使用  
 create proc p_result  
     @dept_id int  
 as  
     declare @emp_name varchar(50)  
     exec p_dept @dept_id,@emp_name output  
     select @emp_name  
 ----------------------------------------------------------------------  
 --执行存储过程  
 execute p_result 1  

3.函数

[sql]view plaincopy 
   
 函数是由一个或者多个T-SQL语句组成的子程序,用于封装代码便于重新使用  
 自定义函数只能返回单一值或者表  
 函数不能对基表进行DML语句  
 根据函数返回类型不同,将用户自定义的函数分为三种类型  
     标量函数:函数返回一个确定类型的标量值  
     内联表函数:以表的形式返回一个返回值  
     多语句表值函数:标量函数和内联表函数的结合体  
 ----------------------------------------------------------------------  
 --增加标量函数  
 create function fn_getDeptName(@dept_id int)  
     returns varchar(50)  
 as  
 begin  
     declare @dept_name varchar(50)  
     select @dept_name=dept_name from d_dept where dept_id=@dept_id  
     return @dept_name  
 end  
 --执行标量函数  
 select dbo.fn_getDeptName(1)  
 --删除标量函数  
 drop function fn_getDeptName  
 ----------------------------------------------------------------------  
 --增加内联表函数  
 create function if_d_dept(@dept_id int)  
     returns @temp table(id int,name varchar(50))  
 as  
 begin  
     insert into @temp  
     select dept_id,dept_name from d_dept where dept_id=@dept_id  
  return  
 end  
 --执行内联表函数  
 select * from if_d_dept(1)  
 --删除内联表函数  
 drop function if_d_dept  
 ----------------------------------------------------------------------  
 --增加多语句表值函数  
 create function fnif_d_dept(@dept_name varchar(50))  
     returns @temp table(id int,name varchar(50))  
 as  
 begin  
     --函数中使用临时表  
     declare @result table(id int,name varchar(50))  
     insert into @result  
     select dept_id,dept_name from d_dept where dept_name=@dept_name  
   
     insert into @temp  
     select * from @result  
  return  
 end  
 --执行多语句表值函数  
 select * from fnif_d_dept('开发部')  
 --删除多语句表值函数  
 drop function fnif_d_dept  

4.触发器

[sql]view plaincopy 
   
 触发器是一种与数据表紧密关联的特殊存储过程  
 当数据表进行 insert delete update 事件发生时,所设置的触发器就会自动执行,保持数据库的完整性  
 触发器在数据库以独立的对象存储(存储过程通过其他程序启动),触发器不能直接调用,只能通过事件来启动  
 触发器不能传递或者接收参数  
   
 触发器的类型  
     DML触发器:在数据库数据发生操作语言(DML)事件时将调用 DML 触发器  
         after 触发器:在执行了 insert delete update 语句之后,after 触发器才会被激发  
             用于对变动数据进行检查,如果错误,将拒绝后者回滚  
             after 触发器只能在表上指定  
             每个触发操作可以包含多个触发器  
             插入和删除表中的 text,ntext,image 列引用,不能使用  
         instead of 触发器:数据变动之前被激活,转而去执行触发器定义的操作,并不再执行原来SQL操作  
             instead of 触发器可以指定在视图和表上  
             每个触发操作只能包含一个触发器   
               
     DDL触发器(2005):当服务器或者数据库发生数据定义语言(DDL)事件,将调用触发器  
         DLL触发器主要有,数据库作用域和服务器作用域  
         DLL触发器用来管理任务,例如审核和控制数据库操作  
   
 触发器的作用  
     DML触发器可以实现级联更改,保证数据的完整性  
   
 触发器两个特殊虚表  
     inserted和deleted表,系统在内存中创建两张表,不存储在数据库中,两张表是只读的  
     这两表被触发的表结构相同,当触发完成之后两张表被删除  
     inserted 表存放增加的记录和存放更新后的记录  
     deleted 表存放被删除的记录和存放更新之前的记录  
   
 测试特定列的 update 操作  
     update 对某一个列的 update  
     columns_update 对多个列的 update  
     返回一个布尔值  
   
 嵌套触发器  
     当某一个触发器执行,能够触发另外一个触发器执行,这种情况称为嵌套触发器  
     如果一个触发器修改一张表,而这张表已经有了其他触发器,这时就使用了嵌套触发器  
     由于触发器在事务中执行,如果在一组嵌套触发器的任意层发生错误,则整个事务将被取消,回滚  
     触发器最多嵌套32级  
   
 递归触发器  
     直接递归:  
         默认情况SQL server是禁止 after 触发器的直接递归  
         如果需要开启,sp_dboption 'itlwc','recursive_triggers',true  
     间接递归  
   
 触发器的激发顺序  
     execute sp_settriggerorder 'tigger_curd1','first','insert'  
     execute sp_settriggerorder 'tigger_curd2','last','insert'  
   
 --创建 after 触发器  
     create table d_dept(  
         dept_id int,  
         dept_name varchar(20)  
         constraint pk_d_dept primary key (dept_id)  
     )  
     create table d_deptBackup(  
         operate varchar(20),  
         operateTime smalldatetime,  
         dept_id int,  
         dept_name varchar(20)  
     )  
     --判断是否存在  
     if(object_id('tigger_curd','tr') is not null)  
         drop trigger tigger_curd  
     go  
     create trigger tigger_curd  
     on d_dept  
         after insert,delete,update  
     as  
         insert into d_deptBackup  
         select '插入数据',getDate(),dept_id,dept_name from inserted  
         insert into d_deptBackup  
         select '删除数据',getDate(),dept_id,dept_name from deleted  
   
 --创建 after delete 触发器  
     if(object_id('tigger_curd','tr') is not null)  
         drop trigger tigger_curd  
     go  
     create trigger tigger_curd  
     on d_dept  
         after delete  
     as  
         declare @rowcount int  
         select @rowcount=@@rowcount --当前删除操作影响的记录数        
         if @rowcount>1  
             begin  
                 rollback transaction    --回滚取消操作  
                 raiserror('一次只能删除一条记录',16,1)    --给出错误信息  
             end  
         declare @dept_name varchar(20)  
         set @dept_name = '开发部'  
         if(@dept_name in (select dept_name from deleted))  
         begin  
             rollback transaction    --回滚取消操作  
             raiserror('不能删除 开发部 ',16,1) --给出错误信息  
         end  
   
 --使用 update()  
     if(object_id('tigger_curd','tr') is not null)  
         drop trigger tigger_curd  
     go  
     create trigger tigger_curd  
     on d_dept  
         after update  
     as  
         if(update(dept_id) or update(dept_name))  
             begin  
                 rollback transaction    --回滚取消操作  
                 raiserror('不允许更新该列信息',16,1) --给出错误信息  
             end  
   
 --创建 instead of 触发器  
     create table emp2005(  
         emp_id varchar(10),  
         emp_name varchar(10)  
     )  
     create table emp2006(  
         emp_id varchar(10),  
         emp_name varchar(10)  
     )  
     create table emp2007(  
         emp_id varchar(10),  
         emp_name varchar(10)  
     )  
     create view v_emp  
     as  
         select * from emp2005  
         union all  
         select * from emp2006  
         union all     
         select * from emp2007  
               
     if(object_id('tigger_curd','tr') is not null)  
         drop trigger tigger_curd  
     go  
     create trigger tigger_curd  
     on v_emp  
         instead of insert  
     as  
         declare @emp_id char(4)  
         select @emp_id = substring(emp_id,1,4) from inserted  
         if @emp_id ='2005'  
             begin  
                 insert into emp2005  
                 select * from inserted  
             end  
         else if @emp_id ='2006'  
             begin  
                 insert into emp2006  
                 select * from inserted  
             end  
         else if @emp_id ='2007'  
             begin  
                 insert into emp2007  
                 select * from inserted  
             end  
         else  
             begin  
                 rollback transaction    --回滚取消操作  
                 raiserror('插入记录emp_id不正确',16,1) --给出错误信息  
             end  
   
 --创建 递归触发器  
     insert into d_dept values(1001,'开发部')  
     insert into d_dept values(1002,'开发部')  
     insert into d_dept values(1003,'开发部')  
     insert into d_dept values(1004,'开发部')  
     insert into d_dept values(2001,'开发部')  
     insert into d_dept values(2002,'开发部')  
     insert into d_dept values(3001,'开发部')  
   
     if(object_id('tigger_curd','tr') is not null)  
         drop trigger tigger_curd  
     go  
     --删除本身并且删除前一个和后一个  
     create trigger tigger_curd  
     on d_dept  
         after delete  
     as  
         declare @rowcount int  
         set @rowcount = @@rowcount  
         if @rowcount=1  
             begin  
                 declare @up_dept_id int  
                 declare @down_dept_id int  
                 select @up_dept_id=dept_id,@down_dept_id=dept_id from deleted  
                 set @up_dept_id=@up_dept_id-1  
                 set @down_dept_id=@down_dept_id+1  
                 delete from d_dept where dept_id=@up_dept_id  
                 delete from d_dept where dept_id=@down_dept_id  
             end  
     --测试:最后只剩下 2001,2002,3001  
     delete from d_dept where dept_id=1002  

5.索引

[sql]view plaincopy 
   
 知识点    
     索引是对数据库表中一个或多个列的值进行排序的结构  
     建立索引目的是对表中记录的查询或者排序  
     当表中有别设置为 unique 的字段时,sql server 会自动建立一个非聚集的唯一性索引    
     当表中有 primary key 的字段时,sql server 会自动建立一个聚集索引  
   
 索引的作用    
     索引相当与书的目录,无需对数据库进行整体扫描,加快数据检索速度    
     通过唯一索引,可以保证数据的唯一性    
     可以加速表与表之间的连接    
     在使用 order by 和 group by 子句进行检索数据,显著减少查询中排序和分组的时间    
     使用索引可以在检索数据的过程中使用优化隐藏器,提过系统的性能    
     
 使用索引  
     查询表中所有数据,使用索引是没有意义的    
     索引列应该在 where 子句中频繁使用的列  
     当用户要检索字段的数据包含很多数值或者很多控制 NULL 时,为该列创建索引会大大提高速度  
     经常排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序速度  
     索引占用数据库空间,在设计数据库应该把索引空间考虑进去  
     尽量把表和他的索引存在不同的磁盘上,这样会提高查询速度  
     不给数据较少和数据较大但不经常查询的表建立索引    
     多表连接查询的时候,最好能够为关联字段建立索引    
     当表数据以查询为主导,更新相对少,则要多采用索引,增加检索速度    
     当表数据更新为主导,查询相对少,则不要建立太多的索引,避免影响更新的速度  
     如果对表进行大量更新时,可以先销毁索引,等数据更新完成之后再创建索引    
     要在表的更新速度与查询速度之间寻求一个平衡点  
       
 索引分类  
     聚集索引:  
         是按照数据存放的物理位置为顺序的  
         也就是说改变了表中数据存放的物理位置  
         对于多行检索的检索很快  
         在插入新行或者更新聚集索引一部分列时,DBMS将自动重新排序  
     非聚集索引:  
         独立于数据行的结构  
         非聚集索引包含键值和行定位器  
         对于单行的检索很快  
         一个表中可以创建多个非聚集索引,SQL Server 查询优化器会自动决定使用哪个索引  
     
 --创建简单索引    
 create index index_d_dept    
 on d_dept(dept_name)    
 --创建唯一索引    
 create unique index index_d_dept    
 on d_dept(dept_name)    
 --创建多列索引    
 create unique index index_d_dept    
 on d_dept(dept_id,dept_name)    
 --删除索引    
 drop index d_dept.index_d_dept   
 --禁用索引(2005)  
 alter index index_d_dept  
 on d_dept disable  
 --启用索引(2005)  
 alter index index_d_dept  
 on d_dept rebuild  
 --重命名索引  
 execute sp_rename 'd_dept.index_d_dept','d_dept.index_d_dept_new','index'  
 --查看表中的索引  
 execute sp_helpindex 'd_dept'  