[教育学]情景8 视图、索引、存储过程及

情景8 视图、索引、存储过程及触发器设计,SQL Server数据库技术及应用 ,目录,任务1 掌握视图与索引任务2 掌握存储过程任务3 掌握触发器,任务1 掌握视图与索引,视图可以使用户只关心自己感兴趣的某些特定数据和自己所负责的特定任务，大大地简化了用户对数据的操作。视图提供了一个简单而有效的安全机制。建立索引是提高查询速度的有效手段，在数据库中，索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据。,8.1 视图的概念及创建方法,8.1.1 视图的概念1视图的定义视图是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。视图是一个虚拟表，数据库中只存视图定义而不存视图对应的数据，数据仍存在原基本表中。 2使用视图的优点简化操作、视点集中、定制数据、合并分割数据、安全性,注意：使用视图时，要注意以下事项：（1）视图一经定义以后，就可以像表一样被查询、修改、删除和更新。（2）只有在当前数据库中才能创建视图。视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。（3）如果视图引用的基本表或者视图被删除，则该视图不能再使用，直到创建新的基表或者视图。（4）不能把规则、默认值或触发器与视图相关联。（5）不能在视图上建立任何索引、包括全文索引。,8.1.2 视图的创建方法,1使用CREATE VIEW语句创建视图 USE webshopgo -此处的go不能省略CREATE VIEW goods_view AS SELECT * FROM goods WHERE g_Status=热点go -此处的go也不能省略select * from goods_view,语法格式：CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION ; 参数说明：schema_name：视图所属架构的名称。view_name：视图的名称。视图名称必须符合有关标识符的规则。可以选择是否指定视图所有者名称。,column ：视图中的列使用的名称。WITH ：指出视图的属性。view_attribute可取以下值：ENCRYPTION：说明在系统表 syscomments 中存储 CREATE VIEW 语句时进行加密。SCHEMABINDING：说明将视图与其所依赖的表或视图结构相关联。VIEW_METADATA：指定为引用视图的查询请求浏览模式的元数据时，向 DBLIB，ODBC或 OLEDB API 返回有关视图的元数据信息，而不是返回给基表或其他表。,AS ：指定视图要执行的操作。select_statement ：定义视图的 SELECT 语句。该语句可以使用多个表和其他视图。CHECK OPTION：指定在视图执行的所有数据修改语句都必须符合在 select_statement 中设置的条件。,【例8-1】运行分析下列语句段USE webshop-select * from goods-select * from orderdetailsgocreate view goods_orderdetails_view asselect distinct goods.g_ID 商品编号,g_name 商品名称,d_price 进价,g_price 零售价from goods,orderdetails where goods.g_ID=orderdetails.g_IDgoselect * from goods_orderdetails_view注意：CREATE VIEW 必须是批命令的第一条语句。Select 语句中不能使用 ORDER BY、INTO 等子句。,2在 SSMS 中创建视图,一、利用SSMS创建上列两个视图，命令和操作相结合，调试执行，最后注意保存。二、演示操作过程。,图8-1 创建视图,8.2 索引的概念及创建方法,8.2.1 索引的概念1索引的定义索引是一个单独的、物理的数据结构，是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引依赖于表建立的，提供了编排表中数据的内部方法。索引可以快速找到表或索引视图中的特定信息。但创建索引要花费时间和占用存储空间，也降低了数据修改的速度。,2索引的分类,索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。 表或视图可以包含以下类型的索引：（1）聚集索引。 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。（2）非聚集索引。 非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。,注意：,（1）索引可以是唯一的，这意味着不会有两行记录相同的索引键值，这样的索引称为唯一索引。如果索引是根据多列组合创建的，这样的索引称为复合索引。（2）索引是非显示的，查询时自动调用。（3）当表中创建主键约束（PRIMARY KEY）时 SQL Server 将自动创建唯一性聚集索引；当表中创建唯一性约束（UNIQUE）时，SQL Server自动创建一个唯一性非聚集索引。若表中已有数据，那么在创建索引时，SQL Server会检查数据的合法性，若有不合法数据，则创建索引失败。,8.2.2 索引的创建方法,1使用CREATE INDEX语句创建索引（1）选择创建索引的列。 如果在一个列上创建索引，该列就称为索引列。通常使用的索引列有如下这些：表的主键列。连接中频繁使用的列。在某一范围内频繁搜索的列和按排列顺序频繁检索的列。 建立索引需要产生一定的存储开销，在进行插入和更新数据的操作时，维护索引也要花费时间和空间，因此没有必要对表中的所有列都建立索引。一般来所，下面这些列不考虑建立索引：很少被查询的列。只有几个值的列，如“性别”只有两个值，“男”和“女”。以 bit、text、image 数据类型定义的列。 另外，数据行数很少的小表一般也没有必要建立索引。,（2）语法格式：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON ( column ASC | DESC ,.n ) 参数说明：UNIQUE：为表或视图创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。CLUSTERED：表示创建聚集索引。创建索引时，键值的逻辑顺序决定表中对应行的物理顺序。聚集索引的底层（或称叶级别）包含该表的实际数据行。一个表或视图只允许同时有一个聚集索引。具有唯一聚集索引的视图称为索引视图。NONCLUSTERED：表示创建的索引为聚集索引。ASC | DESC：指定索引值的排列顺序，ASC 表示升序，DESC 表示降序，缺省值为 ASC。,8.2.2 索引的创建方法,【例8-2】在Student_info表中学号列创建一个名 STU_INDEX 的唯一聚集索引。 CREATE UNIQUE CLUSTERED INDEX STU_INDEX ON Student_info（Sid）2在 SSMS 中创建索引 （1）启动SQL Server Management Studio，在对象资源管理器中依次展开“数据库”节点、Student节点和表节点，在 Student_info 表上单击鼠标右键，在弹出的快捷选单上选择“设计”，如图8-5 所示的表设计器界面。,8.2.2 索引的创建方法,图8-5 表设计器界面,（2）在表设计器界面上右击，出现如图8-6 所示的快捷选单，在快捷选单中选择“索引/键”选单项，出现如图8-7 所示的“索引/键”对话框。,图8-7 “索引/键”对话框,图8-6 快捷选单,（3）在如图8-7 所示的“索引/键”对话框中单击“添加”按钮，进行索引的设置，如图8-8 所示，可以设置索引的类型、选择索引列、是否唯一设置、名称设置等。,图8-8 “索引/键”设置,8.2.2 索引的创建方法,8.3 Student数据库中视图与索引的创建,8.3.1 视图的创建 下面用 T-SQL 语句来创建 Student 数据库中的视图。【例8-3】建立视图 VIEW1，输出所有学生的年龄。-use student-select * from student_infocreate view view1asselect sid, Sname, YEAR(GETDATE()-YEAR(Sbirth) AS sagefrom Student_infogoselect * from view1,【例8-4】建立视图 VIEW2，查找总数量在50分以上的商品的编号和数量。use webshopgocreate view view2asselect t_ID 商品编号,sum(g_Number) 总数量from goods group by t_ID having sum(g_Number)=50go select * from view2,8.3.2 索引的创建,假设 Student 数据库中的课程表信息，成绩表信息经常会被用户查询，为了加快查询速度，我们分别为经常查询的字段建立索引。 【例8-6】为Course_info 表的课程名列创建索引。use studentif exists(select name from sysindexes where name=Course_index) drop index Course_info.Course_index /*删除重复索引文件*/gocreate index Course_index on Course_info(Cname)go,【例8-7】为Course_info 表的课程号列创建唯一聚集索引。因指定了 CLUSTERED 子句，该索引将对磁盘上的数据进行物理排序。use studentif exists(select name from sysindexes where name=Course_index_id) drop index Course_info.Course_index_idgocreate clustered index Course_index_id on Course_info(Cid)go,【例8-8】为 SC 表的学号列和课程列创建复合索引。use studentif exists(select name from sysindexes where name=SC_index) drop index SC.SC_indexgocreate index SC_index on SC(Sid,Cid)Go通常，在唯一列、非空列或标识列上创建聚集索引可获较佳性能，在查询经常用到的所有列上创建非聚集索引可获较佳性能。,任务2 掌握存储过程,存储过程是一组为了完成特定功能的 SQL 语句集，该语句集经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。从而实现模块化编程，加快程序的运行速度。,8.4 存储过程的概念,1存储过程的概念存储过程是是一组为了完成特定功能的 SQL 语句集，经编译和优化后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果存储过程带有参数）来执行它。存储过程是独立存在于表之外的数据库对象。可以由客户调用它，也可以从另一个过程或触发器调用它，它的参数可以被传递和返回，它的出错代码也可以被检验。存储过程有如下优点：（1）存储过程在服务器端运行，执行速度快。（2）存储过程执行一次后，就生成了执行计划，驻留在高速缓冲存储器中，以后每次调用即可，提高了系统的性能。,（3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可以通过编程方式控制上述操作对数据库信息访问的权限。（4）自动完成需要预先执行的任务或者预定的功能。存储过程可以在系统启动时自动执行，完成一些需要预先执行的任务，而不必在系统启动后再进行手工操作，大大方便了用户的使用。2存储过程的类型 在 SQL Server 2008 中，存储过程有两种类型：Transact-SQL 或 CLR。（1）Transact-SQL:指保存的 Transact-SQL 语句集合，可以接受和返回用户提供的参数。我们主要学习Transact-SQL存储过程。（2）CLR。CLR 存储过程是指对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用，可以接受和返回用户提供的参数。,8.4 存储过程的概念,Transact-SQL存储过程分为两类：系统提供的存储过程（系统存储过程）和用户自定义的存储过程。,系统存储过程定义在master数据库中并以sp_为前缀。例如常用的显示系统对象信息的sp_help存储过程，它们为检索系统表的信息方便、快捷的方法。尽管这些系统存储过程被放在master数据库中，但是仍可以在其它数据库中对其进行调用，调用时不必在存储过程名前加上数据库名，而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建的，并能完成一定的功能，如查询用户所需要数据信息、数据统计分析等。用户自定义存储过程只能定义在当前数据库中，默认情况下，用户自定义存储过程归数据库所有者所有，数据库所有者可以把许可授权给其它用户同。,8.5 存储过程的创建方法,1使用 T-SQL 语句创建存储过程 存储过程定义包括两个主要内容：过程名和参数的说明以及过程体（包含执行存储过程操作的 SQL 语句）。创建存储过程前，应该注意以下事项：不能将 CREATE PROCEDURE 语句与其他 SQL 语句组合到单个批处理中。存储过程是数据库对象，其名称必须遵循标识符的命名规则。只能在当前数据库中创建存储过程每个存储过程最多可以使用1024个参数。存储过程最多支持32层嵌套。,语法格式：CREATE PROC | PROCEDURE procedure_name ; number /*定义过程名 parameter type_schema_name. data_type /*定义参数类型 VARYING = default OUT | OUTPUT READONLY /*定义参数的属性 ,.n WITH ,.n /*定义存储过程的处理方式 FOR REPLICATION /*说明不能在订阅服务器上执行为复制创建的存储过程AS ; .n /*执行的操作 := ENCRYPTION RECOMPILE EXECUTE AS Clause := BEGIN statements END ,8.5 存储过程的创建方法,参数说明：number ：是可选整数，用于对同名的过程分组。VARYING：指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变。default OUT | OUTPUT READONLY：default ，参数的默认值。如果定义了 default 值，则无需指定此参数的值即可执行过程；OUTPUT， 指示参数是输出参数。此选项的值可以返回给调用 EXECUTE 的语句。使用 OUTPUT 参数将值返回给过程的调用方；READONLY ，指示不能在过程的主体中更新或修改参数。ENCRYPTION：表示 SQL SERVER 加密 syscomments 表中包含 CREATE PROC 语句文本的条目，可防止将过程作为 SQL SERVER 复制的一部分发布，防止用户使用系统存储过程读取存储过程的定义文本。RECOMPILE：表明 SQL SERVER 每次运行该过程时，将对其重新编译。EXECUTE AS ：指定在其中执行存储过程的安全上下文,【例8-9】创建一个存储过程 goods_sp ：use webshopgocreate proc goods_sp tid char(10)asselect g_Name 名称,g_Price 价格,g_Number 数量,g_Status 状态from goods where t_id=tidgoexec goods_sp 01,2在 SSMS 中创建存储过程,（1）启动SQL Server Management Studio，在对象资源管理器中依次展开“数据库”节点、Student节点和可编程性节点，在“存储过程”上单击鼠标右键，在弹出的快捷选单上选择“新建存储过程”，如图8-9 所示。,图8-9 “新建存储过程”选择,（2）在弹出的存储过程编辑器语法模版中直接修改编辑，也可以删除模版重新编辑，如图8-10 所示。,图8-10 存储过程编辑器界面,（3）编辑完成后，单击编辑器窗口右上角的关闭按钮，出现如图8-11 所示的是否需要保存文件对话框。,图8-11 “保存更改”对话框,（4）选择“是”按钮，出现出现如图8-12 所示的“另存文件”对话框，在其中输入保存路径、文件名，并单击“保存”按钮，便完成了存储过程的创建。,图8-12 “另存文件”对话框,8.6 存储过程的运行、删除和查看,存储过程的运行:Exec goods_sp 01或者Execute goods_sp 01goods_sp 01 Exec goods_sp tid =01或者Execute goods_sp tid =01存储过程的删除:Drop procedure 存储过程名例如： Drop procedure goods_sp存储过程的查看：Sp_help 存储过程名 /Sp_helptext 存储过程名例如： Sp_help goods_sp/Sp_helptext goods_sp,任务3 掌握触发器,8.7 触发器的概念 1触发器的概念触发器是一种特殊类型的存储过程，它是通过事件触发而被执行的。触发器基于一个表创建并和一个或多个数据修改操作（插入数据、更新数据或删除数据）相关联。当出现一次这样的操作时，触发器就会自动激活。2触发器的类型 SQL Server 包括三种常规类型的触发器：DML 触发器、DDL 触发器和登录触发器。,（1）当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用 DDL 触发器。（2）登录触发器将为响应 LOGON 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。（3）当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML事件包括在指定表或视图中修改数据的insert语句、update语句或delete语句。触发器主要有以下优点：利用触发器可以方便地实现数据库中数据的完整性。触发器是自动的，当对表中的数据进行了任何修改操作之后立即被激活。触发器可以进行强制限制，这些限制比用 CHECK 约束所定义的更复杂。,8.7 触发器的概念,8.8 触发器的工作机制,1触发器中使用的特殊表执行触发器时，系统自动创建了两个特殊的逻辑表：inserted 表和deleted 表。用户不能对这两个表进行修改，它们存在内存而不是数据库中。触发器执行完成后，这两个表也被自动删除。inserted逻辑表：当向表中插入数据时，INSERT 触发器触发执行，新的记录插入到触发器表和inserted逻辑表中。deleted 逻辑表：用于保存已从表中删除的记录，当触发一个 DELETE 触发器时，被删除的记录存放到 deleted 逻辑表中。 修改一条记录等于删除一条旧记录，同时插入一条新记录。当对update触发器的表记录修改时，表中原记录移到deleted逻辑表中，修改过的记录插入到inserted逻辑表中。,2触发器的工作机制（1）INSERT 触发器工作原理。 当一个记录插入到表中时，INSERT 触发器自动触发执行，系统创建一个 inserted 表，新的记录被增加到该触发器表和 inserted 表中。（2）DELETE 触发器工作原理。 当从表中删除一条记录时，DELETE 触发器自动触发执行，系统创建一个deleted 表，被删除的记录移到 deleted 表中。 （3）UPDATE 触发器工作原理。 数据更新可以看成是由删除一条旧记录的 DELETE 语句和插入一条新记录的 INSERT 语句组成的。,8.8 触发器的工作机制,8.9 触发器的创建方法,创建触发器前，应该注意以下事项：创建触发器的权限默认授予定义触发器表的所有者、并且该权限不可以转让。触发器不返回任何结果，所以不要在触发器定义中包含 SELECT 语句或者变量赋值，如果必须在触发器中进行变量赋值，则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。触发器为数据库对象，其名称必须遵循标识符的命名规则。只能在当前数据库中创建触发器。,语法格式：CREATE TRIGGER trigger_name /*创建触发器关键字和触发器名称ON table | view /*触发器表或触发器视图 WITH ,.n FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS sql_statement /*触发条件和操作 := ENCRYPTION EXECUTE AS Clause ,参数说明：WITH ENCRYPTION：对 CREATE TRIGGER 语句的文本进行模糊处理。使用 WITH ENCRYPTION 可以防止将触发器作为 SQL Server 复制的一部分进行发布。EXECUTE AS： 指定用于执行该触发器的安全上下文。FOR | AFTER ：AFTER 指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被触发。所有的