数据库原理与应用教程－第五章(第七课） .ppt

《数据库原理与应用教程－第五章(第七课） .ppt》由会员分享，可在线阅读，更多相关《数据库原理与应用教程－第五章(第七课） .ppt（43页珍藏版）》请在三一办公上搜索。

1、第5章 视图,5.1 视图概念 5.2 定义视图 5.3 通过视图查询数据5.4 删除视图 5.5 视图的作用,5.1 视图概念,视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口，视图是一个虚表。在数据库中只存放视图的定义，不存放视图包含的数据，这些数据仍存放在原来的基本表中。视图可以建立在基本表上，也可以建立在其他的视图上，即可以在一个视图之上再定义视图。但对视图数据的操作最终都会转换为对基本表的操作。,5.2 定义视图,定义视图的SQL语句为CREATE VIEW，其一般格 式为：CREATE VIEW（视图列名表）AS 子查询语句 其中子查询可以是任意的SELECT语句，但要注 意

2、以下几点：1、子查询中通常不包含ORDER BY和DISTINCT子句 2、在定义视图时要么指定全部视图列，要么全部省略不写。如果省略了视图的属性列名，则视图的列名与子查询列名相同。但在如下三种情况下必须明确指定组成视图的所有列名：某个目标列是计算函数或列表达式；多表连接时选出了几个同名列作为视图的 字段；需要在视图中为某个列选用新的更合适的 列名。,1.定义单源表视图 单源表视图指的是数据取自一个基本表的部分行、列。这样定义的视图可以进行查询和修改数据操作。例1建立信息系学生的视图。CREATE VIEW IS_Student ASSELECT Sno,Sname,SageFROM Stud

3、ent WHERE Sdept=信息系 DBMS执行CREATE VIEW语句的结果只是保存视图的定义，只有在对视图执行查询时，才按视图的定义从相应基本表中查询数据。视图的名称存储在系统表sysobjects中 创建视图过程的文本存储在syscomments中,2定义多源表视图 多源表视图指的是定义视图的子查询的源表可以有多个，这样定义的视图一般只用于查询，不用于修改数据。例2建立信息系选修了c02号课程的学生的视图，列出学生的学号、姓名和成绩。CREATE VIEW V_IS_S1(Sno,Sname,grade)AS SELECT Student.Sno,Sname,grade FROM

4、Student JOIN SC ON Student.Sno=SC.Sno WHERE Sdept=信息系 AND SC.Cno=c01,3在已有视图上定义新视图 在视图上建立视图表示视图的数据源中有视图。作为数据源的视图必须是已经建立好的。例3建立信息系选修了c02号课程且成绩在90分以上的学生的视图。CREATE VIEW V_IS_S2 AS SELECT Sno,Sname,Grade FROM V_IS_S1 WHERE Grade=90,例4利用例1所建的视图，建立查询信息系VB考试成绩大于等于80分的学生的姓名和成绩的视图。CREATE VIEW V_IS_VB AS SELEC

5、T Sname,Grade FROM IS_Student v join sc v.sno=sc.sno join course c on o=o WHERE Grade=80 and cname=VB,4定义带表达式的视图 在定义基本表时，为减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据需要设置一些派生属性列，在这些派生属性列中保存经过计算的值。例5定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno,Sname,BirthYear)AS SELECT Sno,Sname

6、,2011-Sage FROM Student,5含分组统计信息的视图 含分组统计信息的视图是指视图的子查询中含有GROUP BY子句，这样的视图只能用于查询，不能用于修改数据。例6定义一个存放每个学生的学号及平均成绩的视图。CREATE VIEW S_G(Sno,AverageGrade)AS SELECT Sno,AVG(Grade)FROM SC GROUP BY Sno 注意：如果子查询的选择列表包含表达式或统计函数，而且在子查询中也没有为这样的列指定列标题，则在定义视图的语句中必须要指定视图属性列的名字。,从Student表、SC表和Course表中产生一个视图Grade_table

7、，包括学生姓名、课程名和成绩,Create view grade_table As Select sname,cname,grade From student,course,sc Where Student.sno=SC.sno And Co=SC.cno,5.3通过视图查询数据,例7 利用例1建立的视图，查询信息系年龄小于20岁的学生的学号、姓名和年龄Select*from IS_student where sage=20例8.查询信息系选了“C02”号课程的学生的学号号、姓名和年龄Select s.sno,sname,sage from V_IS_s1 join student s on

8、s.sno=v_is_s1.sno,例9.查询信息系学生的学号、姓名、所选课程名Select v.sno,v.sname,cname from is_student v join sc on v.sno=sc.sno join on course c on o=o 例10.利用例6建立的视图，查询考试成绩80分以上的学生的学号和平均成绩Select*from s_g where avggrade80,5.4 修改和删除视图,1.修改视图Alter view 视图名(列名,n)As查询语句例11.修改例6定义的视图，使其统计每个学生的考试平均成绩和修课总门数Alter view s_g(sno,

9、avgGrade,Count_cno)AsSelect sno,avg(grade),count(*)from sc Group by sno,2.删除视图的SQL语句的格式为：DROP VIEW 例12 删除IS_Student视图。DROP VIEW IS_Student 删除视图时需要注意的是，如果被删除的视图是作为其他视图的数据源，则导出视图将无法再使用了。同样，如果作为视图的基本表被删除了，则视图也将无法使用。,1、简化数据查询语句可以使用户将注意力集中在所关心的数据上定义视图可以将表与表之间的复杂的连接操作和搜索条件对用户隐藏起来。当多次执行相同的数据查询操作时使用视图尤为有用。2

10、、使用户能从多角度看到同一数据使不同的用户以不同的方式看待同一数据，当许多用户共享同一个数据库时，这种灵活性非常重要。3、提高了数据的安全性可以定制用户能查看哪些数据并屏蔽掉敏感的数据。4.提供了一定程度的逻辑独立性与数据库的外模式对应,5.5 视图的作用,补充知识：存储过程 1、什么是存储过程？存储过程是存储在服务器上的一组预编译的SQL语句，它可以接受参数、返回状态和参数值，并能嵌套。使用存储过程能够改变SQL语句的运行性能，提高其执行效率。、使用存储过程的优点：1）由于存储过程在第一次执行后，其执行规划就驻存在高速缓存中，在以后的操作中，只需从高速缓存中调用，提高了系统性能。2）提供一种

11、安全机制，存储过程作为一种安全机制，是用户通过它访问未直接授权的表或视图。,、当创建存储过程时，系统检查其中的语句的正确性存储过程的名称存储在系统表sysobjects中 创建存储过程的文本存储在syscomments中 在存储过程的创建中，允许参考还不存在的对象。但是在执行存储过程时，这些对象必须存在。、存储过程的类型 系统存储过程：系统提供，作为各种命令使用，存储在master数据库中，前缀为sp_。系统存储过程可分为九类。本地存储过程：创建在每个用户数据库中的存储过程。,5、创建存储过程 CREATE PROCEDURE procedure_name；number parameter d

12、ata_typeVARYING=default OUTPUT,.WITH RECOMPILE|ENRYPTION|RECOMPILE,ENCRYPTIONAS sql_statement n,参数：procedure_name：符合标识符规则，对于数据库及其所有者必须唯一。要创建临时存储过程，需在procedure_name前加#.Number整数，标识同名存储过程的不同对象，例如 myproc;1、myproc;2等。用一条DROP PROCEDURE myproc语句可将同名存储过程删除。VARYING：指定作为输出参数支持的结果集。Default：参数的默认值。如果定义了默认值，不必指定

13、该参数的值即可执行过程。可以使用通配符（、_、和）。,parameter：过程中的参数 默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。RECOMPLILE：表明系统执行存储过程时重新编译该存储过程。ENCRYPTION：表示系统加密syscommengs表中包含 CREATE PROCEDURE 语句文本的条目。AS：指定过程要执行的操作 存储过程的最大大小为128MB,A.使用带有复杂SELECT语句的简单过程 下面的存储过程从四个表的连接中返回所有作者、出版的书籍以及出版社。USE pubs IF EXISTS(SELECT name FROM sysobj

14、ectsWHERE name=au_info_all AND type=P)DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allAS SELECT au_lname,au_fname,title,pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id=ta.au_id INNER JOIN titles t ON t.title_id=ta.title_id INNER JOIN publishers p ON t.pub_id=p.pub_id 执行au_info_a

15、ll存储过程的方法 EXEC au_info_all,例如：从stu数据库的三个查询表中，返回学生学号、姓名、课程名、成绩。该存储过程不使用任何参数。Use stuIF EXISTS(SELECT name FROM sysobjects WHERE name=student_info and type=P)DROP PROCEDURE student_info GO CREATE PROCEDURE student_info AS SELECT s.sno,sname,cname,grade FROM student s join sc on s.sno=sc.sno join course

16、 c on o=o GO执行 EXEC student_info,B.使用带参数的简单存储过程下面的存储过过程从四个表的连接中只返回指定的作者、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值CREATE PROCEDURE au_info lastname varchar(40),firstname varchar(20)AS SELECT au_lname,au_fname,title,pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id=ta.au_id INNER JOIN titles t ON t.ti

17、tle_id=ta.title_id INNER JOIN publishers p ON t.pub_id=p.pub_id WHERE au_fname=firstname AND au_lname=lastname EXEC au_info Dull,AnnEXEC au_info lastname=Dull,firstname=AnnEXEC au_info firstname-Ann,lastname=Dull,例如：从stu数据库中的三个表中查询某人指定课程的成绩。Use stuIf EXISTS(SELECT name FROM sysobjects WHERE name=stu

18、dent_info1 and type=P)DROP PROCEDURE student_info1 GO CREATE PROCEDURE student_info1 sname char(8),cname char(20)AS SELECT s.sno,s.sname,ame,sc.grade FROM student s join sc on s.sno=sc.sno join course c on o=o WHERE s.sname=sname and ame=Cname GO执行存储过程的方法：Execute student_info1 李勇,计算机文化学Execute stude

19、nt_info1 sname=李勇,cname=计算机文化学,C、使用带有通配符参数的存储过程 下面的存储过过程从四个表的连接中只返回指定的作者、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值CREATE PROCEDURE au_info2 lastname varchar(30)=D%,firstname varchar(20)=%AS SELECT au_lname,au_fname,title,pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id=ta.au_id INN

20、ER JOIN titles t ON t.title_id=ta.title_id INNER JOIN publishers p ON t.pub_id=p.pub_id WHERE au_fname LIKE firstname AND au_lname LIKE lastname EXEC au_info2EXEC au_info2 Wh%EXEC au_info2 firstname=A%EXEC au_info2 Hunter,shery1EXEC au_info2 H%,S%,例如：从三个表中的连接表中返回指定学生的学号、姓名、所选课程名称几该课程的成绩。该存储过程在参数中使用了

21、模式匹配，如果没有提供参数，则使用预设的默认值。Use stuIF EXISTS(SELECT name FROM sysobjects WHERE name=st_info and type=P)DROP PROCEDURE st_info GO Create procedure st_info sname varchar(10)=刘%ASSELECT s.sno,s.sname,ame,sc.grade FROM student s join sc on s.sno=sc.sno join course s on o=o where sname like sname GO,执行存储过程的方

22、法Exectue st_infoExectue st_info 王%Exectue st_info 王张%D、使用OUTPUT参数使用一个可选的输入参数和一个输出参数 USE pubs GO CREATE PROCEDURE titles_sum title varchar(40)=%,sum money OUTPUT AS SELECT sum=sum(price)FROM titles WHERE title LIKE TITLE执行存储过程方法如下：declare total money execute titles_sum t%,total output select t%,total

23、 zong,例如：用于计算指定学生的总成绩，存储过程中使用一个输入参数和一个输出参数Use stu go if EXISTS(SELECT name FROM sysobjects WHERE name=totalg AND type=P)DROP PROCEDURE totalg goCreate procedure totalg sname char(10),total int OUTPUT AS SELECT total=sum(grade)FROM sc,student where sname=sname And Sc.sno=student.sno group by student.

24、sno执行存储过程的方法Declare total int exec totalg 李勇,total outputselect 李勇,total,使用带扩展存储过程的EXECUTE语句 下列使用xp_cmdshell扩展存储过程列出文件扩展名为.exe的所有文件的目录.USE maste EXECUTE xp_cmdshell dir*.exe,.触发器是一种特殊类型的存储过程,用于保护表中的数据。当有操作影响到触发器保护的数据时，触发器自动执行，通过触发器实现多个表间的一致性。触发器可分为三类：INSERT类型、UPDATE类型、DELETE类型。一个表可以有多种类型的多个触发器。例如：对于

25、stu数据库中的student、sc、和course 表，当插入某一学号的学生的某一课程的成绩时，该学号应该是student表中已经存在的，课程号应该是course表中已存在的，可以定义insert触发器实现上述功能 2.使用触发器的优点 触发器可以一连串地修改数据库相关表中的数据。触发器可以比约束强制更加复杂的数据完整性 这些约束比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其它表中的列。触发器可以实现数据参考的完整性,后触发器CREATE TRIGGER trigger_name ON table FOR|AFTER INSERT,UPDATE,DELETE A

26、S IF UPDATE(column)AND|OR UPDATE(column)n|IF(COLUMNS_UPDATED()bitwise_operatorupdate_bitmask)comparison_operator column_bitmask nsql_statement n,AFTER:指定触发器在触发SQL语句所有操作成功后才激发。IF子句进一步限制触发器被触发的条件：IF UPDATE(column)：测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE类型。可以指定多列。IF(COLUMNS_UPDATE()：测试是否插入或更新了提及的列，仅用于 IN

27、SERT或UPDATE触发器中。COLUMNS_UPDATE()返回varbinary位模，表示插入或更新了表中的哪些列。COLUMNS_UPDATE函数以从左到右的顺序返回位，最左边的位表示表中的第一列，向右的下一位表示第二列，依此类推。Updated_bitmask:：是整型位掩码，表示实际更新或插入的列。例如：表t1包含列C1、C2、C3、C4和C5。假定表t1上有UPDATE触发器，若要检查C2、C3、和C4是否都有更新，指定14（2+4+8）,其测试的IF子句为：IF(COLUMNS_UPDATE()&(2+4+8)0),例如：创建一个触发器，在插入、修改和删除记录时，都会显示表中的

28、内容Use test1Go Create table table1(c1 int,C2 char(30)GoCreate tigger trig1 on table1For insert,update,deleteASSelect*from tableGO执行下列语句时：insert table1 values(1,zhangsan)Insert table1 values(2,lisi),Insert表和delete表 执行触发器时，系统创建了两个特殊的逻辑表：inserted表 和deleted表 inserted 逻辑表：向表中插入数据时，insert触发器触发执行，新的记录插入到触发器

29、表和inserted表中 deleted逻辑表：用于保存已从表中删除的记录，当触发一个delete触发器时，被删除的记录存放到deleted表中。修改一条记录等于插入一个新记录，同时删除旧记录。对定义了update触发器的表记录修改时，表中原记录移到deleted 表中，修改过的记录插入到inserted表中，触发器可检查deleted表、inserted表及被修改的表。,1）Use testGoIf exists(select name from sysobjects where name=trig1 and type=tr DROP TRIGGER trig1GoCreate trigge

30、r trig1 on table1 for insert,update,detele asPrint insert表：Select*from insertedPrint deleted 表Select*from deletedGoInsert table1 values(2,张三)Update table1 set c2=李四 where c1=2Delete table1 where c1=2,（2）向worker表中添加一记录时，该记录的bmh值在depart表中是否存在，若不存在，则取消插入或修改Use factoryGoCreate trigger workerins on worke

31、r for insert,updateAsBegin if(select ins.bmh from insert ins)not in(select bmh from depart)rollbackend,（3）修改depart表的bmh字段时，该字段在worker表中的对应值也应做相应的修改 use factory go create trigger departupdate on depart for update as Begin if(column_update()01)0 update worker set bmh=(select ins.bmh from inserted ins)

32、where bmh=(select bmh from deleted)Endgo,(4)删除depart表中一记录的同时删除该记录bmh字段值在worker表中对应的记录 use factory go create trigger departdelete on depart for delete as begin delete from worker where bmh=(select bmh from deleted)Endgo,替代触发器CREATE TRIGGER trigger_name ON table INSTEAD OF INSERT,UPDATE,DELETE AS IF U

33、PDATE(column)AND|OR UPDATE(column)n|IF(COLUMNS_UPDATED()bitwise_operatorupdate_bitmask)comparison_operator column_bitmask nsql_statement n 替代触发器可用于视图，但视图创建时使用了 WITH CHECK OPTION选项时，该视图不能建替代触发器。每个表只能有一个替代触发器。,触发器举例：1、对stu数据库，如果在student表中添加或更改数据，则向客户端显示一条信息。Use stuIf exists(select name from sysobjects

34、 where name=reminder and type=TR)DROP TRIGGER reminderGo CREATE TRIGGER reminder on student FOR INSERT,UPDATE AS RAISERROR(4008,16,10)GO,小结,本章介绍了实现数据完整性的方法，这部分基本上没有新的概念，只是对第3章介绍的实现方法进行了补充。数据完整性可以在定义表的同时定义，也可以在定义完表之后添加。尤其当约束有变化时，我们就需要使用添加数据约束的技术。,视图是基于数据库基本表的虚表，它实际不包含数据，它的数据全部来自于基本表。视图提供了数据库的逻辑独立性，并增加了数据的安全，封装了复杂的查询，为用户提供了从不同的角度看数据的方法。,