关系数据库与SQL语言计算机教学课件PPT.ppt

关系数据库与关系数据库与SQL语言语言目 录n第1章 数据库基础n第2章 关系库的基本理论n第3章 数据库设计n第4章 Transact-SQL语言和SQL SERVER 2008n第5章 数据库和表的创建n第6章 数据库的查询和视图n第7章 存储过程和触发器n第8章 数据库应用实战上堂回顾上堂回顾n6.2 数据更新数据更新 q6.2.1 数据插入数据插入q6.2.2 数据修改数据修改 q6.2.3 数据删除数据删除q6.2.4 数据更新小结数据更新小结本堂内容本堂内容n6.3 视图与索引视图与索引q视图的概念视图的概念q创建视图创建视图q管理视图管理视图6.3.1 视图的概念视图的概念n视图是从一个或几个基本表（视图）导出的虚表q数据库中只存放视图的定义，不存放视图的数据n基本表的数据若发生变化，从视图中查询出来的数据也随之改变，反之亦然n对视图的操作与对表的操作一样，可以进行查询、修改（有一定限制）和删除n可以在一个视图上再定义一个视图6.3.1 视图的概念视图的概念n视图的优点q视点集中q简化操作q定制数据q合并分割数据q安全性高6.3.2 创建视图创建视图创建视图以提供一个方便地从两个或多个联接表中查看信息的方法,示例:OrderIDOrderID1066310827104271045110515CustomerIDCustomerIDBONAP BONAP PICCO QUICKQUICK~~~ ~~~ ~~~ ~~~ ~~~RequiredDateRequiredDate1997-09-241998-01-261997-02-241997-03-051997-05-07ShippedDateShippedDate1997-10-031998-02-061997-03-031997-03-121997-05-23OrdersCustomersShipStatusViewUSE NorthwindGOCREATE VIEW dbo.ShipStatusViewASSELECT OrderID, ShippedDate,ContactNameFROM Customer c INNER JOIN Orders o ON c.CustomerID = O.CustomerIDCustomerIDCustomerIDBONAPPICCOQUICKCompanyNameCompanyNameBon app'Piccolo und mehrQUICK-StopContactNameContactNameLaurence LebihanGeorg PippsHorst KlossOrderIDOrderID10663108271042710451105151996-08-211996-08-291996-09-11ShippedDateShippedDate1997-10-031998-02-061997-03-031997-03-121997-05-23ContactNameContactNameLaurence LebihanLaurence LebihanGeorg PippsHorst KlossHorst Kloss6.3.2 创建视图创建视图n使用Management Studio 创建视图6.3.2 创建视图创建视图n 使用使用Transact-SQL 创建视图创建视图 create view view_name [ (列名 [，列名] …) ] as <查询表达式> [ with check option ] n组成视图的属性列名或者全部省略或者全部指定。

如果列名省略，视图的属性名默认为子查询结果中的属性名，但下述三种情况必须明确指定视图所有列名q1、某个目标列不是单纯的属性名，而是集函数或列表达式q2、多表连接时选出的几个同名列作为视图的字段q3、需要在视图中为某个列启用新的更合适的名字nwith check option 确保对数据进行的插入修改等操作都符合SELECT 语句中设置的准则6.3.2 创建视图创建视图n 在CREATE VIEW 语句中，对于SELECT 查询语句有如下限制：q创建视图的用户必须对该视图所参照或引用的表或视图具有适当的权限q查询表达式可以是任意复杂的select语句，但通常不可以包含 ORDER BY(如果要包含，则要用上TOP N [percent] )、COMPUTE 或 COMPUTE BY 子句，及 INTO 关键字q不能在临时表上定义视图6.3.2 创建视图创建视图nDBMS执行create view语句的结果只是把视图的定义存入数据库，并不执行其中的select语句，只是在对视图查询时，才按视图的定义查询数据q单表视图q带表达式的视图q使用聚集函数的视图q使用连接的视图q使用子查询的视图q嵌套视图6.3.2 创建视图创建视图--单表视图单表视图n若一个视图是从单个基本表导出，并且只是去掉了基本表的某些行和列，但保留了码，我们称这类视图为行列子集视图或单表视图。

如：建立信息系学生的视图create view is_student as select sno,sname,sage ,sdept from student where sdept=‘is’6.3.2 创建视图创建视图—带表达式的视图带表达式的视图n定义视图的时候可以根据应用的需要，设置一些派生属性列(或称虚拟列)n带有虚拟列的视图称为带表达式的视图定义一个反映学生出生年份的视图 create view sbith_s(sno,sname,sbith) as select sno,sname,2012-sage from student6.3.2 创建视图创建视图--使用聚集函数的视图使用聚集函数的视图n还可以用带有聚集函数和group by子句的查询来定义视图，称为分组视图（使用聚集函数的视图）将学生的学号及他的平均成绩定义为一个视图 create view avgscore_s (sno,avgScore) as select sno,avg(score) from sc group by sno6.3.2 创建视图创建视图--使用连接的视使用连接的视图图n我们还可以创建由多个表连接后产生的视图建立信息系选修了c01课程的学生的视图create view is_c01_s (sno,sname,score) as select student.sno,sname,score from student,sc where sdept='is' and student.sno=sc.sno and o='c01'6.3.2 创建视图创建视图--使用子查询的视图使用子查询的视图n我们还可以创建通过子查询产生的视图创建成绩在90分以上的学生的学号，姓名的视图create view s_score1(sno,sname) as select sno,sname from student where sno in（ select sno from sc where score>=90）6.3.2 创建视图创建视图--嵌套视图嵌套视图n 视图也可以建立在一个或多个已定义好的视图建立信息系选修了c01课程且成绩在90分以上的学生的视图create view is_c01_s2asselect sno,sname,scorefrom is_c01_swhere score>=906.3.3 管理视图管理视图n修改视图q修改视图：可修改一个视图（包括索引视图）的定义，但不影响相关的存储过程或触发器，也不更改权限q通过Management Studio 查看和修改视图n选中视图，选择鼠标右键“修改”选项。

q使用Transact-SQL语言修改视图 alter view view_name [ (列名 [，列名] …) ] as <查询表达式> [ with check option ] n使用新的 SELECT 语句和选项代替原来的定义6.3.3 管理视图管理视图n使用存储过程查看视图信息qsp_depends view_namen指出该视图所依赖的对象qsp_help view_name n返回该视图的详细信息qsp_helptext view_namen检索出该视图的定义文本n重命名视图q通过Management Studio 重命名视图n选中视图，选择鼠标右键“重命名”选项q使用存储过程重命名视图 sp_rename old_name , new_nameq注意：n只能重命名自己拥有的视图n数据库所有者可以更改任何用户视图的名称6.3.3 管理视图管理视图n删除视图q删除视图的格式： drop view view_nameq删除视图后视图的定义将从数据库中删除但是由该视图导出的其他视图仍定义在数据库中，不过这些视图已失效，使用时会出错。

q删除视图is_s1 drop view is_s16.3.3 管理视图管理视图6.3.4 使用视图管理数据表中的数据使用视图管理数据表中的数据n使用视图对基表中的数据进行插入、更新和删除等操作时要注意以下几点：q每次修改都只能影响一个基表q不能修改那些通过计算得到的列n若视图的字段来自聚集函数，则此视图不允许更新n若视图中含有group by子句或distinct子句，则此视图不允许更新n一个不允许更新的视图上定义的视图也不允许更新q执行UPDATE 和DELETE命令时，所更新或删除的数据必须包含在视图的结果集中q如果视图引用多个表，则n无法用DELETE命令删除数据n使用INSERT 或 UPDATE 语句对视图进行操作时，被插入或更新的列必须属于同一个表n查询视图q视图定义后，用户可以象对基本表一样对视图进行查询在信息系学生的视图中找出年龄小于20岁的学生 select Sno,Sage from is_student where Sage<20qDBMS执行对视图的查询时，首先进行有效性检查，检查查询的表，视图是否存在。

如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询q所以上述语句等价与： select Sno ,Sage from Student where Sdept='is' and Sage<206.3.4 使用视图管理数据表中的数据使用视图管理数据表中的数据 在S_G视图中查询平均成绩在70分以上的学生学号和平均成绩Create view s_g(sno,avgScore)asselect sno,avg(score)from scgroup by sno select * from s_g where avgScore>=70以上语句是否等价于： select sno,avg(score) from sc where avg(score)>=70 group by sno不等价：不等价：注意HAVING 的用法应等价于 select sno,avg(score) from sc group by sno having avg(score)>=706.3.4 使用视图管理数据表中的数据使用视图管理数据表中的数据n插入数据q向信息系学生视图插入一行新的学生数据，姓名为赵敏，年龄为20岁Insert into is_studentvalues(08010,'赵敏',20,'IS')q上述语句执行时转换为：Insert into student(sno,sname,sage,sdept)values(08010,'赵敏',20,'IS')6.3.4 使用视图管理数据表中的数据使用视图管理数据表中的数据n更新数据q将信息系学生视图is_student中学号为07003的学生姓名改为“刘辰” update is_student set sname='刘辰' where sno=‘08003'q上述语句执行时转换为：update studentset sname= '刘辰'where sno=‘08003' and sdept='is'6.3.4 使用视图管理数据表中的数据使用视图管理数据表中的数据n删除数据q删除信息系学生视图is_student中学号为08009的记录 delete from is_student where sno= ' 08009‘q上述语句执行时转换为：delete from studentwhere sno= ' 08009' and sdept='is'6.3.4 使用视图管理数据表中的数据使用视图管理数据表中的数据q在关系数据库中，并不是所有的视图都是可更新。

一般来说，行列子集视图都是可以更新的；而有些视图的更新不能唯一的转换成对相应基本表的更新，这类视图是不可更新的q例如上面所讲的s_g视图 update s_g set avgScore=80 where sno= 08009 ——此更新无法执行q目前各个关系数据库系统对视图的更新还有待进一步的规定，由于各系统实现方法上的差异，规定也不尽相同6.3.4 使用视图管理数据表中的数据使用视图管理数据表中的数据。