4.2 视图 (1) 视图是一个虚拟表，其内容来自对表查询的基础上。

Presentation on theme: "4.2 视图 (1) 视图是一个虚拟表，其内容来自对表查询的基础上。"— Presentation transcript:

1 4.2 视图 (1) 视图是一个虚拟表，其内容来自对表查询的基础上。
(2) 视图的行和列数据来自于定义视图的查询所引用的表，并在引用表时动态生成。 (3) 视图的结构和显示的数据行并没有实际地以视图结构存储在数据库中，而是存储在视图所引用的基本表中。 (4)视图集中、简化和自定义每个用户对数据库的不同认识,是数据库的外模式。用户可以将数据库中自己感兴趣的特定数据展现在视图中。 (5) 视图也可作为一种安全机制，允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。

2 4.2 视图 数据库的三级模式结构

3 4.2 视图 视图示例：

4 4.2 视图 4.2.1创建视图 创建视图的方法： 1）在SSMS中使用向导创建； 2）使用T-SQL的CREATE VIEW语句。
创建视图时必须遵循以下原则： 只能在当前数据库中创建视图。 视图在数据库中作为一个对象存储，视图名称不得与数据库中的表重名。

5 4.2.1 创建视图 1．在SSMS中使用向导创建视图 【例4-45】在数据库School中，建视图V_StudentGrade，查询学生成绩情况。 1）展开School数据库文件夹，右击“视图”，在右键菜单上选择“新建视图”命令，打开新建视图对话框。 2）在添加表对话框中选择与视图有关的表、视图或函数（通过Ctrl或Shift键可选择多项），然后单击【添加】按钮；或者直接双击要添加的表等，即可将其添加到视图的查询中。这里选择表：Student、Course和Grade。单击“关闭”按钮，进入视图设计窗口。

6 4.2.1 创建视图 3）选择的表添加到窗口的上格，单击每个表字段前的复选框，可将该字段添加到视图中。也可在所示窗口的第二个子窗口选择视图字段，并可指定列的别名、排序方式和规则等。在选择过程中，第三个子窗口中的SELECT语句也会随之自动改变。也可直接在该子窗口中输入SELECT语句。这里选择StudentName、CourseName和Grade三个字段。 4）单击工具栏上的　　按钮执行，结果显示窗口的第三个子窗口。 5）单击工具栏上的　　按钮，在弹出的另存为对话框中为视图命名，本例输入“V_StudentGrade”，单击【确定】按钮保存视图，从而完成视图创建。

7 4.2.1 创建视图 【例4-46】在数据库School中，建立视图V_StudentAvgGradExcellent, 选拔优培生（平均成绩大于等于90的学生），并要求按平均成绩从高到低排列。 1) 在新建视图窗口中“视图”标签中选择视图V_StudentGrade 2) 在视图设计窗口中，选择“姓名”和“成绩”字段 3) 在第二个窗口中右击“姓名”，在右键菜单中“添加分组依据”；右击“成绩”，在“分组依据”中选择“Avg”在“筛选器”列中输入“>=90”，“排序类型”选“降序” 4) 运行查看视图结果，保存该视图为V_StudentAvgGradExcellent 。

8 4.2.1 创建视图 2. 使用SQL语句创建视图 在查询编辑窗口中执行CREATE VIEW语句创建视图 语法：
AS SQL语句 【例4.47】创建一个性别为“男”的学生视图V_StudentSexMale，显示男学生的学号、姓名、班号和性别

9 4.2 视图 4.2.2 使用视图 创建后的视图跟的表的用法相同，可以通过视图查询和修改数据库。
【例4-48】在数据库School中，从视图V_StudentGrade查询陈佳迪的成绩。 SELECT * FROM V_StudentGrade WHERE 姓名='张三'

10 4.2 视图 4.2.3修改和删除视图 （1）修改视图 （2）删除视图
1）在SSMS中展开相应数据库和视图，右单击要修改的视图，在弹出的快捷菜单上选择“设计视图” 。 2）在弹出的“设计视图”窗口中，可以用在SSMS中创建视图相同的方法修改已定义好的视图。修改完毕存盘。　　 （2）删除视图 1）在SSMS中展开相应数据库和视图，右单击要删除的视图，在弹出的快捷菜单上选择“删除”。 2）单击【确定】按钮，即可删除选择的视图。

11 4.3 存储过程 (1)存储过程是T-SQL语句的集合，它作为数据库对象之一被存储在数据库中。
(2)存储过程的作用和使用方式类似于一些编程语言中的过程。 使用存储过程有以下优点： 1)可以在一个存储过程中执行多条SQL语句； 2)可通过输入参数的变化调用存储过程进行动态执行； 3)存储过程在创建时就在服务器端进行了编译，节省SQL语句的运行时间； 4)提供了安全机制，它限制了用户访问SQL语句的权利，只为特定用户开放存储过程。

12 4.3.1 创建存储过程 1．使用T-SQL语句创建存储过程 可以直接在SQL查询分析器中定义和调用存储过程。 常用存储过程的语法格式：
CREATE PROC[DURE] 存储过程名 数据类型}[VARYING][=默认值][OUTPUT] AS SQL语句1 … SQL语句n 说明： “形式参数”名称必须符合标识符规则；OUTPUT表示该参数是可以返回的，可将信息返回调用者；如果有多个参数，可以依次按以上参数定义规则列出，用逗号“,”隔开。

13 4.3.1 创建存储过程 【例4-49】在School中创建一个的存储过程proc_Student,查询所有学生的信息。
CREATE PROC proc_Student AS SELECT * FROM Student 执行存储过程 EXEC proc_Student

14 4.3.1 创建存储过程 2．使用SSMS模板创建视图 【例4-50】在School数据库中，创建带输入参数的存储过程proc_SearchStudent， 查询指定学生姓名的学生信息。 1）打开SSMS，展开School数据库文件夹，右单击“存储过程”，在弹出的快捷菜单上选择“新建存储过程”命令，打开新建存储过程对话框。 2）将“<Procedure_Name, sysname, ProcedureName>” 参数替换成存储过程存储过程的名称。 3）在“—Add Parameters”行下添加输入参数。 4）在BEGIN…END中输入查询语句后，运行SQL语句。 5）执行存储过程：EXEC proc_SearchStudent '张三'

15 4.3.1 创建存储过程 【例4-51】在School中创建存储过程proc_CountStudent，根据课程编号统计选修该课程的学生人数。 CREATE PROC proc_CountStudent @ccode varchar(20), -- 课程号，输入参数 @number int OUTPUT -- 选课人数，输出参数 AS FROM Grade WHERE 执行方法： 在SSMS中右击存储过程proc_CountStudent，在右键菜单中点击“执行存储过程”

16 4.3.1 创建存储过程 【例4-52】在School中创建存储过程proc_StuAvgGrade，查询某个学生的选课数目和平均成绩。
CREATE PROC proc_StuAvgGrade @stcode varchar(20), @stcount int int OUTPUT AS SELECT FROM Grade WHERE 调用存储过程proc_StuAvgGrade，查询‘ ’学生的选课数目和平均成绩。 int int EXEC proc_StuAvgGrade ' ', @scount OUTPUT PRINT AS char(2))+'门' PRINT

17 4.3.2 查看、修改和删除存储过程 1.查看存储过程 2.修改存储过程 修改存储过程的T-SQL语句 3.删除存储过程
在SSMS中点击展开School数据库->“可编程性”->“存储过程”，右击要查看的存储过程，在右键菜单中选择“编写存储过程脚本为”->“Create到”->“新查询编辑窗口”命令 2.修改存储过程 右击存储过程，选择“修改”命令，然后与创建存储过程类似，可修改存储过程 修改存储过程的T-SQL语句 ALTER PROC 存储过程名 变量定义 AS SQL语句 3.删除存储过程 SSMS中点击存储过程，在右键菜单中选“删除”命令 DROP PROC 存储过程名

18 4.4 触发器 4.4.1 DML触发器 触发器是一种特殊的存储过程。
SQL Server提供了两类DML触发器： （1）AFTER触发器：是在表中数据被修改之后才被触发，触发器对变动的数据进行检查，如果发现错误，将拒绝或回滚变动的数据。 （2）INSTEAD OF触发器：在数据修改以前被触发，并取代修改数据的操作，转去执行触发器定义的操作。

19 4.4.1 DML触发器 1. 应用范围 （1）实现由主键、外键不能保证的复杂的一致性约束 （2）级联修改数据库中相关的表
（3）防止恶意或错误的Insert、Update、Delete操作 （4）根据数据表修改前后的状态采取相应的操作 2. 临时触发事件表 （1）deleted表：存储被删除的数据 （2）inserted表：存储被插入到表中的数据 （3）触发器执行结束后，deleted表和inserted表自动被删除

20 4.4.1 DML触发器 3. 创建DML触发器 CREATE TIGGER 触发器名 ON 表名|视图名
FOR [INSERT], [UPDATE], [DELETE] AS SQL 语句 【例4.53】在School数据库中创建一个简单的触发器tri_StudentInsDel，当用户插入或删除T_Student表中学生记录时，能自动显示表中的内容。 CREATE TRIGGER tri_StudentInsDel ON Student FOR INSERT, DELETE AS SELECT * FROM Student

21 4.4.1 DML触发器 【例4-54】在School数据库的Student表上创建一个触发器tri_StudentCodeUpdate，当对学号列进行修改时，给出提示信息并取消修改操作。 CREATE TRIGGER tri_StudentCodeUpdate ON Student FOR UPDATE AS varchar(50) IF UPDATE(StudentCode) BEGIN SET @text='学生数据被修改!!!' ROLLBACK TRANSACTION END 返回用户定义的错误信息,并给出与信息关联的严重级别(1～18)和有关错误调用状态的信息(1～127 ) 。 取消修改操作

22 【例4-54】在School数据库的Grade表上创建一个触发器tri_Grade，当向Grade表中插入一条记录时，检查该记录的学号是否在Student表中，如果不存在则取消插入操作,否则显示“插入操作成功完成”。 CREATE TRIGGER tri_Grade ON Grade FOR INSERT AS varchar(50) IF EXISTS(SELECT * FROM inserted WHERE inserted.StudentCode NOT IN (SELECT StudentCode FROM Student)) BEGIN ' ROLLBACK TRANSACTION END Else

23 4.4.1 DML触发器 4. 使用模板创建触发器 1）在SSMS中，展开“表”文件夹，右击触发器所属的表，在弹出的快捷菜单上选择“新建触发器”命令。 2）在打开的查询编辑容器中，修改代码，然后执行代码，即将创建触发器。 【例4.55】在School数据库的Course表上创建触发器tri_UpdateCourse，当修改Course表LeftSeats时，检查LeftSeats是否等于TotalSeats减去统计Grade所得的该课程选课人数。相等，则确认修改，否则取消修改 1）在SSMS中，展开“数据库”->“School”->“表”->“Course”->“触发器” ，右击“触发器”，在弹出的快捷菜单上选择“新建触发器”命令。 2）在打开的查询编辑容器中，修改代码，然后执行代码，即创建触发器tri_UpdateCourse。

24 4.4.2 DDL触发器 1. DDL触发器简介 2. DDL触发器的创建 当数据库中发生DDL事件（如修改表结构）时触发
用于审核、控制对数据库的操作，存储于数据库中 主要用途： 防止对数据库结构和对象进行某些更改 执行某些操作以响应数据库结构中的更改 记录数据库架构中更改或事件 2. DDL触发器的创建 CREATE TRIGGER 触发器名 ON {ALL SERVER|DATABASE} {FOR|AFTER} {event_type|event_group}[,…] AS SQL语句

25 4.4.2 DDL触发器 【例4.56】在School数据库中创建DDL触发器，防止数据表结构被修改或删除
CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT '本触发器禁止修改表结构或删除表' ROLLBACK

26 4.4.3 查看、修改和删除触发器 1）在SSMS中，展开触发器所在表的“触发器”文件夹，点击要修改的触发器，在弹出的右键快捷菜单上选择“修改”命令，显示触发器修改代码。修改方法同使用SSMS创建触发器方法相同。 2）如要删除，单击【删除】按钮，在弹出的确认对话框中单击【是】，即删除了所选择的触发器。