数据库原理及应用（MySQL项目式版）- 课件 项目6 视图

项目6

视图【项目目标】视图是数据库中主要的对象之一，视图对应于数据库系统内部体系结构三级模式中的外模式，反映的是一张或多张基本表中的局部数据，视图是一种虚拟表。理解视图的概念。掌握创建和查询视图的方法。掌握操作视图的方法。任务6.1视图概述视图是从一张或多张基本表（或视图）中导出的虚拟表。视图与基本表不同，视图中的内容由SELECT语句定义，在数据库中只存储视图的定义，不存储视图对应的数据，这些数据存储在基本表中，直到用户使用视图时才查找出对应的数据。当基本表中数据发生变化时与之关联的视图也会随之发生变化。视图优点（1）隐藏了数据的复杂性。用户不必了解数据库中详细的表结构和复杂的表间联系。用户可以像使用基本表一样使用视图。（2）方便用户查询数据。当用户需要查询的数据来自于不同表时，通过视图可以将它们集中在一起方便用户进行查询。（3）提高安全性。用户使用视图只能查询和修改视图中的数据，同时保证了数据的逻辑独立性。（4）便于数据共享。各个用户不必重复定义和存储自己所需的数据，可以共享数据库中的数据，因此同样的数据只需存储一次。6.2.1创建视图CREATE[ORREPLACE]VIEW视图名[(名称列表)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];说明如下。（1）ORREPLACE：可选项，在创建视图时，如果存在同名的视图，则可以用新建的视图替换已有的视图。（2）视图名：指定视图的名称。（3）名称列表：需要为视图的各个列指定名称时，可使用这个选项，各名称之间用英文逗号分隔。名称列表中的名称个数必须与SELECT语句中的字段个数一致。省略名称列表时，视图的列名称与SELECT语句中的字段名称相同。（4）SELECT语句：用来创建视图，可以在SELECT语句中查询一张表、多张表或视图中的数据。（5）WITHCHECKOPTION：指出在视图上进行的修改要符合SELECT语句指定的WHERE限制条件，这样在修改数据后，仍然可以通过视图看到修改的数据。使用视图时需要注意以下规则（1）新创建的视图默认存储在当前数据库中。如果需要在指定的数据库中创建视图，则使用“数据库名.视图名”形式。（2）视图名必须是唯一的，且不能与表名相同。（3）视图定义中引用的基本表或视图必须是存在的，并且定义视图的用户对所涉及的基本表或视图有查询的权限。（4）不能引用系统变量或用户变量，不能引用预处理语句参数。（5）不能包含子查询。（6）不能在视图上建立任何索引。【例6-1】创建名为v_reader的视图，包含读者表reader中的读者编号reader_id、姓名name，读者类型表readertype中的类型名称type、最大借阅天数maxdays。mysql>CREATEORREPLACEVIEWv_reader

->AS

->SELECTreader_id,name,type,maxdays

->FROMreaderJOINreadertypeONreader.type_id=readertype.type_id;reader_idnametypemaxdays1101003091杨丽教师901105010561赵晓丽教师901105071058李俊教师902101240103侯明斌学生602106240104王刚学生602106240206王晓红学生602107240101李淑子学生602107240106刘丽华学生60【例6-2】创建名为v_price的视图，包含图书表book中的book_id、title、publisher和price*0.7，在视图中，列名称分别为图书编号、书名、出版社和优惠价格。mysql>CREATEORREPLACEVIEWv_price->AS->SELECTbook_idAS图书编号,titleAS书名,publisherAS出版社,price*0.7AS优惠价格->FROMbook;mysql>CREATEORREPLACEVIEWv_price(图书编号,书名,出版社,优惠价格)->AS->SELECTbook_id,title,publisher,price*0.7FROMbook;图书编号书名出版社优惠价格100023计算机科学导论博达教育出版社34.86100582大学物理博达教育出版社30.73110001平面设计基础职帆启航出版社36.40110301导游情景英语职帆启航出版社31.50110705空间数据库原理职帆启航出版社45.50120034学术英语写作知行联合出版社32.83120062大数据技术基础知行联合出版社38.50120087程序设计基础知行联合出版社29.40121006大学计算机基础知行联合出版社26.46123208数据库系统概论知行联合出版社41.86【例6-3】创建名为v_rborrow的视图，包含读者编号reader_id、姓名name、馆藏编号stock_id、书名title、出版社publisher和归还时间returntime。mysql>CREATEORREPLACEVIEWv_rborrow->AS->SELECTreader.reader_id,name,stock.stock_id,title,publisher,returntime->FROMreaderJOINborrowONreader.reader_id=borrow.reader_idJOINstockONborrow.stock_id=stock.stock_idJOINbookONstock.book_id=book.book_id;6.2.2查询视图创建视图后，对视图的查询与对基本表的查询类似，不同的是FROM子句中用的是视图名。【例6-4】查询已创建的视图v_reader，按照下列要求完成查询。（1）显示所有字段。mysql>SELECT*FROMv_reader;（2）显示出最大借阅天数maxdays大于等于90天的读者编号reader_id和姓名name。mysql>SELECTreader_idAS读者编号,nameAS姓名

->FROMv_reader

->WHEREmaxdays>=90;【例6-5】查询视图v_price，按照下列要求完成查询。（1）显示知行联合出版社的图书编号、书名和优惠价格。mysql>SELECTbook_id,title,priceFROMv_priceWHEREpublisher='知行联合出版社';ERROR1054(42S22):Unknowncolumn'book_id'in'fieldlist'mysql>SELECT图书编号,书名,优惠价格->FROMv_price->WHERE出版社='知行联合出版社';（2）统计各个出版社的图书数量。mysql>SELECT出版社,COUNT(*)AS图书数量FROMv_priceGROUPBY出版社;【例6-6】查询视图v_rborrow，按照下列要求完成查询。（1）显示杨丽借阅过的图书的馆藏编号stock_id、书名title和出版社publisher。mysql>SELECTstock_id,title,publisherFROMv_rborrow

WHEREname='杨丽';（2）显示王晓红借阅过的图书的馆藏编号stock_id、书名title和出版社publisher。mysql>SELECTstock_id,title,publisherFROMv_rborrow

WHEREname='王晓红';（3）显示杨丽和王晓红都借阅过的图书的馆藏编号stock_id、书名title和出版社publishermysql>SELECTa.stock_id,a.title,a.publisherFROMv_rborrowa,v_rborrowb

->WHEREa.stock_id=b.stock_idAND='杨丽'AND='王晓红';逻辑视图a和b进行等值连接，a视图的name为杨丽，并且b视图的name为王晓红（4）显示杨丽或王晓红借阅过的图书的馆藏编号stock_id、书名title和出版社publishermysql>SELECTstock_id,title,publisherFROMv_rborrow

->WHEREname='杨丽'ORname='王晓红';（5）显示杨丽借阅过但王晓红没借阅过的图书的馆藏编号stock_id、书名title和出版社publishermysql>SELECTstock_id,title,publisherFROMv_rborrow->WHEREname='杨丽'ANDtitleNOTIN(SELECTtitleFROMv_rborrowWHEREname='王晓红');（6）显示尚未还书的读者编号reader_id、姓名name和书名title。mysql>SELECTreader_id,name,titleFROMv_rborrowWHEREreturntimeISNULL;任务6.3操作视图操作视图包括更新视图、修改视图定义和删除视图。视图可以为基本表提供保护，用户不直接对基本表进行插入、删除和修改操作，而是针对视图来实现操作。因为视图是不存储数据的虚拟表，所以对视图的更新就是对基本表的更新。更新视图可以实现对基本表的插入、删除、修改等操作。6.3.1更新视图视图包含下述结构中的任意一种，则该视图不可以更新。（1）聚合函数。（2）DISTINCT关键字。（3）GROUPBY子句。（4）ORDERBY子句。（5）HAVING子句。（6）UNION联合查询。（7）FROM子句中包含多个表。（8）引用了不可更新视图。（9）WHERE子句中的子查询引用了FROM子句中的表。【例6-7】创建名为v_femalereader的视图，包含读者表reader中所有女性读者的读者编号reader_id、类型编号type_id、姓名name、性别gender和所属院系dept。mysql>CREATEORREPLACEVIEWv_femalereader

->AS

->SELECTreader_id,type_id,name,gender,deptFROMreaderWHEREgender='女'

->WITHCHECKOPTION;mysql>SELECT*FROMv_femalereader;1.插入数据使用INSERT语句向视图插入数据时，如果在创建视图时包含WITHCHECKOPTION子句，则在更新数据时检查新数据是否符合视图定义中WHERE子句的条件。如果视图是基于多个基本表创建的，则不能向视图插入数据。【例6-8】向视图v_femalereader中插入一条数据（1202110101，1，王明明，女，NULL）。mysql>INSERTINTOv_femalereader

->VALUES('1202110101',1,'王明明','女',NULL);查询基本表reader。mysql>SELECT*FROMreaderWHEREreader_id='1202110101';mysql>INSERTINTOv_femalereader

->VALUES('1802110101',1,'王建国','男',NULL);ERROR1369(HY000):CHECKOPTIONfailed'librarydb.v_femalereader'2．修改数据使用UPDATE语句可以实现通过视图修改基本表中的数据。【例6-9】通过视图v_femalereader将读者王明明的所属院系dept字段修改为人工智能学院。mysql>UPDATEv_femalereaderSETdept='人工智能学院'WHEREname='王明明';3．删除数据使用DELETE语句可以实现通过视图删除基本表中的数据。【例6-10】通过视图v_femalereader，删除姓名name为“王明明”的记录。mysql>DELETEFROMv_femalereaderWHEREname='王明明';6.3.2修改视图定义使用ALTERVIEW语句可以修改已有视图的定义。ALTERVIEW视图名[(名称列表)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];【例6-11】修改视图v_femalereader，使其只包含人工智能学院的女性读者的读者编号reader_id、类型编号type_id、姓名name、性别gender和所属院系dept。mysql>ALTERVIEWv_femalereader->AS->SELECTreader_id,type_id,name,gender,dept->FROMreaderWHEREgender='女'ANDdept='人工智能学院'->WITHCHECKOPTION;6.3.3删除视图可以删除不需要的视图，删除视图对该视图关联的基本表没有任何影响。删除视图使用DROPVIEW语句，其语法格式如下。DROPVIEW[IFEXISTS]视图名1[,视图名2…];说明如下。（1）如果包含IFEXISTS，当视图不存在时，就不会出现错误提示信息。（2）可以一次性删除多个视图。【例6-12】删除视图v_reader和v_price。批量删除视图v_reader和v_price。mysql>DROPVIEWv_reader,v_price;【项目案例】图书馆借还书管理数据库的视图1．创建和查询视图（1）创建名为v_bborrow的视图，包含书名title、馆藏编号stock_id、姓名name、存放位置location、图书状态status、借出时间borrowtime和归还时间returntime。mysql>CREATEORREPLACEVIEWv_bborrow

->AS

->SELECTtitle,stock.stock_id,name,location,status,borrowtime,returntime

->FROMreaderJOINborrowONreader.reader_id=borrow.reader_idJOINstockONborrow.stock_id=stock.stock_idJOINbookONstock.book_id=book.book_id;查询读者李淑子的借阅情况。mysql>SELECTnameAS姓名,titleAS书名,borrowtimeAS借出时间,returntimeAS归还时间

->FROMv_bborrowWHEREname='李淑子';（2）创建名为v_count的视图，统计图书表book中各个出版社的总书目数量和总馆藏数量。mysql>CREATEORREPLACEVIEWv_count(出版社,总书目数量,总馆藏数量)

->AS

->SELECTpublisher,COUNT(*),SUM(stocknum)FROMbookGROUPBYpublisher;查询