MySQL数据库教程（第2版）-课件 第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）视图定义中引用的基本表或视图必须是存在的，并且定义视图的用户对所涉及的基本表或视图有查询的权限（权限的相关问题将在第10章中介绍）。【例6-1】创建名为vw_infstudent的视图，包含学生表student中信息工程学院的学生的学号stuno、姓名stuname、性别gender、出生日期birthdate和入学总分enterscore。mysql>CREATEORREPLACEVIEWvw_infstudentAS->SELECTstuno,stuname,gender,birthdate,enterscore->FROMdepartmentdJOINstudentsONd.deptno=s.deptnoWHEREdeptname='信息工程学院';stunostunamegenderbirthdateenterscore20241060104王刚男2007-01-1257820241060111李淑子女2006-06-1448920241060126刘丽女2005-11-17520教师姓名课程名称班级杨丽大学英语电气2401杨丽大学英语财务2401周家罗大学英语计算2401周家罗大学英语风电2401林达风力发电技术风电2401宋江科自动控制原理电气2401赵晓丽经济学基础英语2401赵晓丽经济学基础英语2402赵晓丽经济学基础财务2401孟凯彦人工智能导论英语2401孟凯彦人工智能导论英语2402孟凯彦人工智能导论计算2401孟凯彦人工智能导论财务2401孟凯彦人工智能导论风电2401王平程序设计基础电气2401王平数据库原理及应用电气2401王平程序设计基础计算2401王平数据库原理及应用财务2401马丽程序设计基础英语2401马丽数据库原理及应用英语2401马丽程序设计基础英语2402马丽数据库原理及应用英语2402马丽数据库原理及应用计算2401李亚明机器学习电气2401李亚明机器学习计算2401【例6-2】创建名为vw_tclass的视图，包含教师表teacher中的教师姓名tchname，课程表course中的课程名称crsname和讲授安排表teaching中的班级classno，列名称分别为教师姓名、课程名称和班级。mysql>CREATEVIEWvw_tclassAS->SELECTtchnameAS教师姓名,crsnameAS课程名称,classnoAS班级

->FROMteachertJOINteachingtgONt.tchno=tg.tchnoJOINcoursecONtg.crsno=c.crsno;mysql>CREATEVIEWvw_tclass(教师姓名,课程名称,班级)AS->SELECTtchname,crsname,classno->FROMteachertJOINteachingtgONt.tchno=tg.tchnoJOINcoursecONtg.crsno=c.crsno;【例6-3】创建名为vw_score的视图，列名称分别为学号、姓名、平均成绩（保留2位小数）、最高分、最低分和选课数量。mysql>CREATEVIEWvw_score(学号,姓名,平均成绩,最高分,最低分,选课数量)AS->SELECTs.stuno,stuname,ROUND(AVG(grade),2),MAX(grade),MIN(grade),COUNT(*)->FROMstudentsJOINscorescONs.stuno=sc.stunoGROUPBYs.stuno,stuname;学号姓名平均成绩最高分最低分选课数量20241010103宋洪博88.679385320241010105刘向志82.339068320241010230李媛媛74.0010034420241020110王琦88.009580320241040102李华79.409170520241050101侯明斌84.009869420241050101张函84.259565420241050102唐明卿77.009061420241060104王刚79.509545420241060111李淑子75.50856626.2.2查询视图创建视图后，对视图的查询与对基本表的查询类似，不同的是FROM子句中用的是视图名。【例6-4】查询已创建的视图vw_infstudent，按照下列要求完成查询。（1）显示所有字段。mysql>SELECT*FROMvw_infstudent;（2）查询女同学的学号stuno、姓名stuname和入学总分enterscore。mysql>SELECTstunoAS学号,stunameAS姓名,enterscoreAS入学总分

->FROMvw_infstudentWHEREgender='女';【例6-5】查询视图vw_tclass，按照下列要求完成查询。（1）查询电气2401班开设的课程名称和教师姓名。mysql>SELECTcrsname,tchnameFROMvw_tclassWHEREclassno='电气2401';ERROR1054(42S22):Unknowncolumn'crsname'in'fieldlist'mysql>SELECT课程名称,教师姓名FROMvw_tclassWHERE班级='电气2401';（2）统计每位教师授课班级的数量，要求显示教师姓名和班级数量，并按照班级数量降序排序。mysql>SELECT教师姓名,COUNT(*)AS班级数量FROMvw_tclassGROUPBY教师姓名ORDERBY2DESC;（3）查询王平和杨丽都授课的班级。mysql>SELECTDISTINCTa.班级FROMvw_tclassaJOINvw_tclassbONa.班级=b.班级

->WHEREa.教师姓名='杨丽'ANDb.教师姓名='王平';（4）查询王平或杨丽授课的班级。mysql>SELECTDISTINCT(班级)FROMvw_tclassWHERE教师姓名='杨丽'OR教师姓名='王平';（5）查询王平授课但杨丽没授课的班级。mysql>SELECTDISTINCT(班级)FROMvw_tclass

->WHERE教师姓名='王平'AND班级NOTIN(SELECT班级FROMvw_tclassWHERE教师姓名='杨丽');【例6-6】查询视图vw_score，按照下列要求完成查询。（1）查询刘向志同学的学号、姓名、平均成绩、最高分、最低分和选课数量。mysql>SELECT学号,姓名,平均成绩,最高分,最低分,选课数量FROMvw_scoreWHERE姓名='刘向志';（2）统计所有学生的平均成绩（保留2位小数）和平均选课数量（保留1位小数）。mysql>SELECTROUND(AVG(平均成绩),2)AS平均成绩,ROUND(AVG(选课数量),1)AS平均选课数量FROMvw_score;（3）查询平均成绩高于所有学生的平均成绩的学生的学号、姓名、平均成绩和班级。mysql>SELECT学号,姓名,平均成绩,classnoAS班级FROMvw_scoreJOINstudentON学号=stuno

->WHERE平均成绩>(SELECTAVG(平均成绩)FROMvw_score);任务6.3操作视图操作视图包括更新视图、修改视图定义和删除视图。视图可以为基本表提供保护，用户不直接对基本表进行插入、删除和修改操作，而是针对视图来实现操作。因为视图是不存储数据的虚拟表，所以对视图的更新就是对基本表的更新。更新视图可以实现对基本表的插入、删除、修改等操作。6.3.1更新视图视图包含下述结构中的任意一种，则该视图不可以更新。（1）聚合函数。（2）DISTINCT关键字。（3）GROUPBY子句。（4）ORDERBY子句。（5）HAVING子句。（6）UNION联合查询。（7）FROM子句指定了多张表。（8）引用了不可更新的视图。（9）WHERE子句中的子查询引用了FROM子句指定的表。【例6-7】创建名为vw_professor的可更新视图，包含教师表teacher中全部教授的教工号tchno、教师姓名tchname，性别gender、职称title和院系代码deptno。mysql>CREATEORREPLACEVIEWvw_professor(教工号,教师姓名,性别,职称,院系代码)AS

->SELECTtchno,tchname,gender,title,deptnoFROMteacherWHEREtitle='教授'

->WITHCHECKOPTION;mysql>SELECT*FROMvw_professor;1.插入数据使用INSERT语句向视图插入数据时，如果在创建视图时包含WITHCHECKOPTION子句，则在更新数据时检查新数据是否符合视图定义中WHERE子句的条件。如果视图是基于多个基本表创建的，则不能向视图插入数据。【例6-8】通过可更新的视图vw_professor中向教师表teacher中插入一条数据（10100888，王京华，女，教授，101）。mysql>INSERTINTOvw_professor

->VALUES('10100888','王京华','女','教授','101');查询基本表teacher。mysql>SELECT*FROMteacherWHEREtchno='10100888';mysql>INSERTINTOvw_professor->VALUES('10200333','刘文涛','男','讲师','102');ERROR1369(HY000):CHECKOPTIONfailed'scoredb.vw_professor'2．修改数据使用UPDATE语句可以实现通过视图修改基本表中的数据。【例6-9】通过可更新的视图vw_professor将教师表teacher中王京华的院系代码deptno字段值修改为106。mysql>UPDATEvw_professorSET院系代码='106'WHERE教师姓名='王京华';3．删除数据使用DELETE语句可以实现通过视图删除基本表中的数据。【例6-10】通过可更新的视图vw_professor删除教师表teacher中姓名为王京华的记录。mysql>DELETEFROMvw_professorWHERE教师姓名='王京华';6.3.2修改视图定义使用ALTERVIEW语句可以修改已有视图的定义。ALTERVIEW视图名[(名称列表)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];【例6-11】修改视图vw_professor，使其只包含院系代码为106的教授的教工号、教师姓名、性别、职称和院系代码。mysql>ALTERVIEWvw_professor(教工号,教师姓名,性别,职称,院系代码)AS->SELECTtchno,tchname,gender,title,deptnoFROMteacherWHEREtitle='教授'ANDdeptno='106'->WITHCHECKOPTION;6.3.3删除视图可以删除不需要的视图，删除视图对该视图关联的基本表没有任何影响。删除视图使用DROPVIEW语句，其语法格式如下。DROPVIEW[IFEXISTS]视图名1[,视图名2…];说明如下。（1）如果包含IFEXISTS，当视图不存在时，就不会出现错误提示信息。（2）可以一次性删除多个视图。【例6-12】删除视图vw_tclass。mysql>DROPVIEWvw_tclass;【课堂案例】学生成绩管理数据库的视图1．创建和查询视图（1）创建名为vw_stugrade的视图，