第8章视图电子课件 MySQL数据库管理与应用.pptx

《第8章视图电子课件 MySQL数据库管理与应用.pptx》由会员分享，可在线阅读，更多相关《第8章视图电子课件 MySQL数据库管理与应用.pptx（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第8章视图电子课件 MySQL数据库管理与应用第8章 视图主要内容8.1 视图概述8.2 创建视图8.3 管理视图8.4 使用视图操作数据8.5 本章小结8.1 视图概述视图是从一个或多个表导出的虚拟表，为用户提供了一种查看数据库中数据的方式。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，用户可以不用看到整个数据表中的数据而只关心对自己有用的数据。视图可以方便用户操作数据，还可以保证数据库系统的安全性。8.1 视图概述8.1.1 视图的概念视图是一种数据库对象，视图在数据库中保存的是对一个或多个表（或其他视图）进行查询的SELECT语句，因此视图可以看作是从一个或多个表（或其他视图

2、）导出的虚拟表，这些表称为视图的基表。和真实的表一样，视图在显示时也包含一系列带有名称的列和多行数据，这些列和行由视图定义的SELECT语句决定。通过视图看到的数据可以来自于一个基表、两个或多个基表的连接查询、对基表中数据的统计汇总、其它视图定义的数据、基表和视图的混合等。8.1 视图概述视图与基表的对应关系8.1 视图概述8.1.1 视图的概念需要注意的是，数据库中只保存了视图的定义，并没有保存视图的数据，这些数据保存在视图所引用的基表中。使用视图查询数据时，数据库管理系统会从基表中取出对应的数据，因此当基表中的数据发生改变时，显示在视图中的数据也会随之改变。通过视图修改数据时，系统会转化为

3、对基表中的数据的修改。8.1 视图概述8.1.2 视图的作用简化操作，屏蔽数据库的复杂性增加数据的安全性保证数据逻辑独立性8.2 创建视图8.2.1 使用CREATE VIEW语句创建视图语法格式：CREATE OR REPLACEALGORITHM=UNDEFINED|MERGE|TEMPTABLEDEFINER=userSQL SECURITY DEFINER|INVOKER VIEW view_name(column_list)AS select_statementWITH CASCADED|LOCAL CHECK OPTION8.2 创建视图8.2.1 使用CREATE VIEW语句创

4、建视图REPLACE：如果视图不存在，则“CREATE OR REPLACE VIEW”与“CREATE VIEW”相 同。如 果 视 图 存 在，“CREATE VIEW”会 出 错 而 无 法 执 行，“CREATE OR REPLACE VIEW”会执行并替换视图原来的定义。ALGORITHM：MySQL处理视图的方式。MERGE会将引用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。TEMPTABLE将视图的结果置于临时表中，然后使用它执行语句。如果选用UNDEFINED，MySQL自己选择所要使用的算法。8.2 创建视图8.2.1 使用CREATE VIEW语

5、句创建视图DEFINER：视 图 的 定 义 者，user可 以 是user_namehost_name、CURRENT_USER或CURRENT_USER()，有关用户的介绍参见第14章。如果省略了DEFINER子句，则默认的DEFINER是执行CREATE VIEW语句的用户，这与显式指定DEFINER=CURRENT_USER相同。SQL SECURITY：在 执 行 引 用 视 图 的 语 句 时，SQL SECURITY子句确定在检查视图的访问权限时要使用哪个MySQL帐户。DEFINER为默认值，表明所需的权限由定义视图的用户持有，INVOKER表明所需的权限由调用视图的用户持有。

6、8.2 创建视图8.2.1 使用CREATE VIEW语句创建视图view_name：要创建的视图的名称。默认情况下，将在当前数据库中创建新视图。要在给定数据库中显式创建视图，可以用db_name.view_name。在数据库中，基表和视图共享相同的命名空间，因此基表和视图不能具有相同的名称。column_list：视图必须具有唯一的列名，不存在重复项，就像基表一样。默认情况下，视图列名为SELECT语句检索到的列的名称。可以在视图名后用(column_list)子句以逗号分隔的名称列表显式定义视图列的名称。列名表中的名称数量必须与SELECT语句检索到的列数相同。8.2 创建视图8.2.1

7、使用CREATE VIEW语句创建视图select_statement：视图定义的SELECT语句。可以引用基表或其他视图，可以使用连接查询、UNION联合查询和子查询。SELECT语句检索的列可以是对表列的简单引用，也可以是使用函数、常量值、运算符的表达式。WITH CASCADED|LOCAL CHECK OPTION：限制通过视 图 更 新 数 据 时，插 入 或 更 新 的 数 据 必 须 满 足select_statement中设置的WHERE条件。如果视图基于其他视图创建，CASCADED|LOCAL限制检查的范围，LOCAL关 键 字 将 CHECK选 项 仅 限 于 所 定 义

8、 的 视 图。CASCADED会导致对底层视图的检查进行评估。默认为CASCADED。8.2 创建视图8.2.1 使用CREATE VIEW语句创建视图【例8.1】创建视图view_computer，从student表查询计算机科学与技术专业的学生的信息，保证通过该视图操作的数据都要满足专业编号为0101这个条件。在MySQL命令行客户端输入命令：CREATE VIEW view_computerAS SELECT*FROM student WHERE mno=0101 WITH CHECK OPTION;8.2 创建视图8.2.1 使用CREATE VIEW语句创建视图【例8.2】创建视图v

9、iew_score，从student、score和course表查询学生的成绩信息，包含学号、姓名、课程名和成绩。在MySQL命令行客户端输入命令：CREATE VIEW view_scoreASSELECT student.sno,sname,cname,gradeFROM student,score,courseWHERE student.sno=score.sno and o=o;8.2 创建视图8.2.1 使用CREATE VIEW语句创建视图【例8.3】基于视图view_score创建视图，包含学生的学号、姓名和平均成绩。在MySQL命令行客户端输入命令：CREATE VIEW vi

10、ew_avggrade(sno,sname,avggrade)AS SELECT sno,sname,avg(grade)FROM view_score GROUP BY sno,sname;8.2 创建视图8.2.1 使用CREATE VIEW语句创建视图CREATE VIEW语句需要视图的CREATE VIEW权限，以及SELECT语句选择的每一列的权限。如果使用了OR REPLACE，则还必须具有该视图的DROP权限。SELECT语句不能引用系统变量或用户定义的变量。视图定义中引用的任何基表或视图都必须存在。如果在创建视图后，删除了定义所引用的表或视图，则使用该视图会导致错误。视图定义不

11、能引用临时表，也不能创建临时视图。MySQL中不能将触发器与视图关联。8.2 创建视图8.2.1 使用CREATE VIEW语句创建视图SELECT语句中列名的别名不能超过64个字符（而不是256个字符的最大别名长度）。视图定义中允许使用ORDER BY，但如果使用具有自己ORDER BY的语句从视图中选择，则将忽略ORDER BY。对于定义中的其他选项或子句，它们将添加到引用视图的语句的选项或子句中，但效果未定义。例如，如果一个视图定义包含LIMIT子句，并且使用一个有自己LIMIT子句的语句从视图中选择数据，则应该用哪个LIMIT子句是未定义的。同样的原则也适用于SELECT关键字后面的A

12、LL、DISTINCT等选项。8.2 创建视图8.2.2 使用WorkBench创建视图打开WorkBench工具，连接到MySQL服务器。在左侧的“SCHEMAS”导航栏中找到jwgl数据库，单击左边的箭头，在下面的“Views”节点上单击鼠标右键，在出现的菜单中选择“Create View”，右侧将打开“new_view”对话框。8.2 创建视图8.2.2 使用WorkBench创建视图8.2 创建视图8.2.2 使用WorkBench创建视图在“Name”文本框中输入要创建的视图名称，“DDL”文本框中输入CREATE VIEW语句8.2 创建视图8.2.2 使用WorkBench创建视

13、图单击“Apply”按钮，弹出“Review SQL Script”对话框。8.2 创建视图8.2.2 使用WorkBench创建视图单击“Apply”按钮，系统将创建视图，如果视图定义语句有错误，将给出错误提示，如果没有错误则完成视图的创建。单击“Finish”按钮，关闭对话框。8.3 管理视图8.3.1 查看视图1.查看当前数据库中的视图lSHOW TABLES命令不仅会显示当前数据库中已有的表，还会显示数据库中的视图的名称。【例8.4】查看jwgl数据库中的表和视图。在MySQL命令行客户端输入命令：USE jwglSHOW TABLES;8.3 管理视图8.3.1 查看视图2.查看视图

14、的结构l可以使用DESCRIBE语句或SHOW COLUMNS|FIELDS语句查看视图的结构。【例8.5】使用DESCRIBE查看视图view_computer的结构。在MySQL命令行客户端输入命令：DESC view_computer;【例8.6】使用SHOW COLUMNS查看视图view_score的结构。在MySQL命令行客户端输入命令：SHOW COLUMNS FROM view_score;8.3 管理视图8.3.1 查看视图3.查看视图的定义lSHOW CREATE VIEW语句可以用来查看创建视图的CREATE VIEW语句，其语法格式为：SHOW CREATE VIEW

15、view_name【例8.7】使用SHOW CREATE VIEW语句查看视图view_score的定义。在MySQL命令行客户端输入命令：SHOW CREATE VIEW view_score G8.3 管理视图8.3.2 修改视图1.利用CREATE OR REPLACE VIEW语句修改视图lCREATE OR REPLACE VIEW不仅可以创建视图，当视图已经存在时还可以对其进行修改【例8.8】修改视图view_avggrade，列名改为学号、姓名、平均成绩。在MySQL命令行客户端输入命令：CREATE OR REPLACE VIEW view_avggradeAS SELECT

16、sno AS 学号,sname AS 姓名,avg(grade)AS 平均成绩 FROM view_score GROUP BY sno,sname;8.3 管理视图8.3.2 修改视图2.利用ALTER VIEW语句修改视图l语法格式为：ALTER ALGORITHM=UNDEFINED|MERGE|TEMPTABLEDEFINER=userSQL SECURITY DEFINER|INVOKER VIEW view_name(column_list)AS select_statementWITH CASCADED|LOCAL CHECK OPTION8.3 管理视图8.3.2 修改视图2.

17、利用ALTER VIEW语句修改视图【例8.9】使用ALTER VIEW修改视图view_avggrade，按平均成绩从高到低排序。在MySQL命令行客户端输入命令：ALTER VIEW view_avggradeASSELECT sno AS 学号,sname AS 姓名,avg(grade)AS 平均成绩FROM view_scoreGROUP BY sno,snameORDER BY 平均成绩 DESC;8.3 管理视图8.3.3 删除视图语法格式为：DROP VIEW IF EXISTS view_name,view_name.说明：l该语句可以删除一个或多个视图。lIF EXISTS

18、：防止因为视图不存在出现的错误。如果参数列表中的某个视图不存在，则该语句将失败，并出现一个错误，通过名称指示它无法删除哪些不存在的视图，并且不会进行任何更改。为了防止出现此种错误，可以使用IF EXISTS。8.3 管理视图8.3.3 删除视图【例8.10】删除视图view_avggrade。在MySQL命令行客户端输入命令：DROP VIEW view_avggrade;8.3 管理视图8.3.4 使用Workbench管理视图打开WorkBench工具，连接到MySQL服务器。在左侧的“SCHEMAS”导航栏中找到jwgl数据库，单击左边的箭头，在下面的“Views”节点下找到“view_

19、student”，单击左边的箭头，可以看到视图view_student包含的列。8.3 管理视图8.3.4 使用Workbench管理视图在“view_student”节点上，单击鼠标右键，选择“Alter View”菜单，打开视图的定义窗口。如果要修改视图，在DDL文本框中直接修改视图的定义语句，然后单击“Apply”按钮完成修改。8.3 管理视图8.3.4 使用Workbench管理视图如果要删除视图，在“view_student”节点上，单击鼠标右键，选择“Drop View”菜单，打开删除视图对话框。单击“Drop Now”完成删除。8.4 使用视图操作数据视图创建之后，可以利用INS

20、ERT、UPDATE和DELETE语句通过视图管理数据库中的数据。需要注意的是，因为视图是一个虚拟表并不保存数据，所以通过视图操作数据最终都转换为对基表中数据的操作。8.4 使用视图操作数据8.4.1 使用视图查询数据使用视图查询数据是视图最常见的用法，使用SELECT语句对视图进行查询和用SELECT语句对表进行查询类似。【例8.11】使用视图view_score查询刘丽各门课程的成绩。在MySQL命令行客户端输入命令：SELECT*FROM view_score WHERE sname=刘丽;8.4 使用视图操作数据8.4.2 使用视图插入数据可以使用INSERT语句通过视图向基表中插入数

21、据。【例8.12】使用视图view_computer插入一个学生记录。在MySQL命令行客户端输入命令：INSERT INTO view_computerVALUES(20200101010,张三,男,2002-07-20,汉族,0101);8.4 使用视图操作数据8.4.2 使用视图插入数据如果创建视图时使用了WITH CHECK OPTION，则插入的数据必须满足视图定义中SELECT语句的WHERE条件。【例8.13】使用视图view_computer插入一个专业为0102的学生。在MySQL命令行客户端输入命令：INSERT INTO view_computerVALUES(20200

22、101011,李四,男,2002-07-20,汉族,0102);8.4 使用视图操作数据8.4.2 使用视图插入数据因为数据最终是插入到基表中的，所以使用视图插入数据时必须满足基表上定义的约束条件，否则插入失败。8.4 使用视图操作数据8.4.3 使用视图修改数据可以利用UPDATE语句通过视图修改数据，语法与修改表中数据相同，只不过UPDATE后的表名换成了视图名。【例8.14】使用视图view_computer修改学生20200101001的出生日期为2002-08-20。在MySQL命令行客户端输入命令：UPDATE view_computerSET sbirthday=2002-08-

23、20WHERE sno=20200101001;8.4 使用视图操作数据8.4.3 使用视图修改数据如果创建视图时使用了WITH CHECK OPTION，则修改后的数据必须满足视图定义中的WHERE条件，并且必须满足基表上定义的约束条件。【例8.15】使用视图view_computer修改学生20200101001的专业为0102。8.4 使用视图操作数据8.4.4 使用视图删除数据可以利用DELETE语句通过视图删除基表中的数据。【例8.16】使用视图view_computer删除学号为20200101001的学生记录。在MySQL命令行客户端输入命令：DELETE FROM view_c

24、omputer WHERE sno=20200101010;8.4 使用视图操作数据8.4.5 使用视图操作数据的限制不是所有的视图都是可更新的，以下情况视图不可更新：l创建视图时，ALGORITHM为TEMPTABLE；l视图中包含COUNT()、SUM()、AVG()、MAX()和MIN()等聚合函数；l视图中包含DISTINCT、UNION、UNION ALL、GROUP BY、HAVING等关键字；l常量视图，例如CREATE VIEW view_time AS SELECT NOW()；l视图中的SELECT语句包含子查询；l视图的基表中有些列不允许空值也没有默认值，并且视图中不包含

25、这些列；l由不可更新视图导出的视图。8.4 使用视图操作数据8.4.5 使用视图操作数据的限制如果视图基于多表连接创建，则通过视图更新数据时一条语句只能影响到一个基表中的数据：l插入数据时，视图名后必须给出列名列表，且只能是一个基表中的列。8.4 使用视图操作数据8.4.5 使用视图操作数据的限制如果视图基于多表连接创建，则通过视图更新数据时一条语句只能影响到一个基表中的数据：l修改数据时，SET后面的列只能是一个基表中的列8.4 使用视图操作数据8.4.5 使用视图操作数据的限制如果视图基于多表连接创建，则通过视图更新数据时一条语句只能影响到一个基表中的数据：l不能通过视图删除数据8.4 使

26、用视图操作数据8.4.5 使用视图操作数据的限制当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在插入、更新或删除每个行，以使其符合视图的定义。如果视图基于另一个视图创建，系统还会检查依赖视图中的规则以保持一致性。LOCAL表示更新视图时只需满足视图自身定义的条件即可，CASCADE为默认选项，表示更新视图时要满足所有相关视图的条件。8.5 本章小结视图是从基表或其他视图中导出的虚拟的表，使用视图可以简化查询、掩盖数据库设计的复杂性、增强数据的安全性、提高数据独立性。本章主要介绍了视图的相关知识，内容包括视图的概念和作用、视图的创建、修改和删除以及使用视图管理数据。通过本章的学习，读者应该掌握视图的概念和相关操作，能够在具体的数据库应用中灵活使用视图。