MySQL的视图

最新推荐文章于 2023-04-25 13:58:28 发布

学无止境LL

最新推荐文章于 2023-04-25 13:58:28 发布

阅读量1.5k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_44572041/article/details/109123822

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本篇文章所涉及的视图的概念

------------------------------------参考mysql实用教程第二版

视图的概念
创建视图
查询视图
更新视图
修改视图
删除视图

1.视图的概念

视图使用一个表或多个表（或视图）导出的表，他是用户使用数据库的观点，例如，对于一个学校，其学生的情况存于数据库的一个或多个表中，而作为学校的不同职能部门，所关心的学生数据的内容是不同而。即使是同样的数据，也可能有不同的操作要求，于是就可以根据他们的不同需求，在物理的数据库上定义对他们对数据库所要求的数据结构，这中根据用户观点所定义的数据结构就是视图。

视图与表（有时为与视图区别，也称表为基本表-Base Table）不同，视图是一个虚表，即它所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

视图一经定义以后，就可以像表一样被查询、修改、删除和更新。使用视图有下列优点：

为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。
屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。
简化用户权限的管理。只需用户使用视图的权限而不必制定用户只能使用表的特定列，也增加了安全性。
便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。
可以重新组织数据以便输出到其他应用程序中。

2.创建视图

视图在数据库中是作为一个对象来存储的。用户创建视图前，要保证自己已被数据库所有者授权可以使用CREATE VIEW语句，并且有权操作视图所涉及的表或其他视图。

语法格式：

CREATE 
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE }]
[DEFINER = {USER | CURRENT_USER}]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL ] CHECK OPTION ]

说明
OR REPLACE: 给定了OR REPLEASE子句，语句能够替换已有的同名视图。
ALGOTIRHM子句：可选的ALGORITHM子句是对标准SQL的MySQL扩展，规定了MySQL的算法，算法会影响MySQL处理视图的方式。ALGOTITHM可取三个值：MERGE、TMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED未定义的。指定了MERGE选项，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。MERGE算法要求视图中的行和基表中的行具有一对一的关系，如果不具有该关系，必须使用临时表取而代之。指定了TEMPTABLE选项，视图的结果将被置于临时表中，然后使用它执行语句。
view_name:视图名。
column_list:要想为视图的列定义明确的名称，可使用可选的column_list子句，列出由逗号隔开的列名。column_list中的名称数据必须等于SELECT语句检索的列数。若使用与源表或视图中相同的列名时可省略column_list。
select_statement:用来创建视图的SELECT语句，可在SELECT语句中查询多个表或视图。但对SELECT语句有一下的限制：

定义视图的用户必须对所参照的表或视图即可执行SELECT语句权限。
不能包含FROM子句中的子查询。
不能引用系统或用户变量。
不能引用预处理语句参数。
在定义中引用的表或视图必须存在。
若引用不是当前数据库的表或视图时，要在表或视图前加上数据库的名称。
在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而改视图使用了具有自己ORDER BY的语句，则视图定义中的ORDER BY将被忽略。
对于SELECT语句中的其它选项或子句，若视图中也包含了这些选项，则效果未定义。例如，如果在视图定义中包含了LIMIT子句，而SELECT语句使用了自己的LIMIT子句，MySQL对于使用哪个LIMIT未做定义。
WITH CHECK OPTIOn：指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。当视图是根据另一个视图定义跌时候，WITH CHECK OPTION给出两个参数：LOCAL和CASCADED。它们决定了检查测试的范围。LOCAL关键字使CHECK OPTION只对定义的视图进行检查，CASCADED则会对所有视图进行检查。如果未给定任一关键字，默认值为CASCADED。

使用视图时，要注意以下事项：
10. 在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为db_name.view_name。
11. 视图的命名必须遵循标志命名规则，不能与表同名，且对每个用户视图必须是惟一的，即对不同用户，及时是定义相同的视图，也必须使用不同的名字。
12. 不能把规则、默认值或触发器与视图相关联。
13. 不能在视图上建立任何索引，包括全文索引。
例子
假设当前数据库是test，创建xscj数据库上的cs_kc视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都符合专业名为计算机的这个条件。
create or replace view xscj.cs_kc
as
select xs.学号,课程号,成绩
from xcsj.xs，xscj.xs_kc
where xs.学号=xs_kc.学号 and xs.专业名=‘计算机’
with check option;
例子
创建xscj数据库上的计算机专业学生的平均成绩视图cs_kc_avg，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg)
use sxcj
create view cs_kc_avg(num, score_avg)
as
select 学号, avg(成绩)
from cs_kc
group by 学号；

3. 查询视图

视图定义后，就可以如同查询基本表那样对视图进行查询。
例子
在视图cs_kc中查找计算机专业的学生学号和选修的课程号
select 学号,课程号
from cs_kc;
例子
查找平均成绩在80分以上的学生的学号和平均成绩
本例子首先创建学生平均成绩视图xs_kc_avg，包括学号（在视图中列名为num)和平均成绩(在视图中列名为score_avg)。
创建学生平均成绩视图xs_kc_avg；
create view xs_kc_avg(num, score_avg)
as
select 学号,avg(成绩)
from xs_kc
group by 学号；
再对xs_kc_avg视图进行查询
select *
from xs_kc_avg
where score_avg>=80;
创建视图可以向最终用户隐藏复杂的表连接，简化了用户的SQL程序设计
注意
使用视图查询时，若其关联的基本表中添加了新字段，则该视图将不包含新字段。例如，视图cs_kc中的列关联了xs表中所有列，若xs表新增了”籍贯“字段，那么cs_xs视图中将查询不到”籍贯”字段的数据。
如果与视图相关联的表或视图被删除，则该视图将不能再使用。
查询视图也可以在MySQL Query Browser工具中进行，方法与查询表类似。
4. 更新视图
由于视图是一个虚拟表，所以更新视图（包括插入、修改和删除）数据也就等于在更新与其关联的基本表的数据。但并不是所有的视图都可以更新，只有对满足可更新条件的视图才能进进行更新。更新视图的时候要特别小心，这可能导致不可预期的后果。
4.1可更新的视图
要通过视图更新基本表的数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句当中使用他们。对于可更新的视图，在视图中的行和基本表的行之间必须具有一对一的关系。还有一些特殊的其他结构，这类结构会使得视图不可更新。如果视图包含下述结构中的任何一种，那么他就是不可更新的：

聚合函数；
DISTINCT关键字；
GROUP BY子句；
ORDER BY子句；
HAVING子句；
UNION运算符；
位于选择列表中的子查询；
FROM子句中包含多个表；
SELECT语句中引用了不可更新视图；
WHERE子句中的子查询，引用FROM子句中的表；
ALGORITHM选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）

4.2 插入数据
使用insert语句通过视图向基本表插入数据
例子
创建视图cs_kc视图中包含计算机专业的学生信息，并向cs_xs视图中插入一条记录，（‘081255’，‘李牧’,‘计算机’,1,‘1994-10-21’, 50, NULL,NULL）
首先创建视图cs_xs:
create or replace view cs_xs
as
select * from xs
where 专业名=‘计算机’
witch check option;
注意
在创建视图的时候加上WITH CHECK OPTION子句，是因为WITH CHECK OPTION子句会在更新数据的时候检查新数据是否符合视图定义中WHERE子句的条件。WITH CEHCK OPTION子句只能和可更新视图一起使用。
insert into cs_xs
values(‘081255’，‘李牧’,‘计算机’,1,‘1994-10-21’, 50, NULL,NULL);
注意
这里插入纪录时专业名只能为计算机
这时，使用SELECT语句查询cs_xs视图和基本表xs就可以发现xs表中该记录已经被添加进去。

当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这会影响过个基本表。例如，不能像视图cs_kc插入数据，因为cs_kc依赖两个基本表：xs和xs_kc。
对insert 语句还有一个限制：select 语句中必须包含from子句中指定表的所有不能为空的列。例如，若cs_xs视图定义的时候不加上姓名字段，则插入数据的时候会出错。

4.3修改数据
例子将cs_xs视图中所有学生的总学分增加8分。
update cs_xs
set 总学分=总学分+8；
该语句实际上是将cs_xs视图所依赖的基本表xs中，所有记录的总学分字段值在原来的基础上增加8。
若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。
将cs_kc视图中学号为081101的学生的101课程成绩改为90分。
update cs_kc
set 成绩+90
where 学号=‘081101’ and 课程号=‘101’
本例中，视图cs_kc依赖于两个基本表；xs和xs_kc，对cs_kc视图的一次修改只能改变学号（源于xs表）或者课程号和成绩（源于xs_kc表).
比如，以下的修改就是错误的：
update cs_kc
set 学号=’081101’,课程号=‘208‘
where 成绩-90；
4.4删除数据
使用delete语句可以通过视图删除基本表的数据。
例子
删除cs_xs中李牧同学（学号=‘081255’）
delete from cs_xs
where 学号=‘081255’
注意对依赖于多个基本表的视图，不能使用delete语句。例如不能通过对cs_kc视图执行delete语句而删除与之相关的基本表xs及xs_kc表的数据。
修改视图
使用alter语句可以对已有的视图的定义进行修改。
语法格式：
alter
[algorithm= {undefined | merge | temptable }]
[definer = {user | current_user }]
[sql security {definer | invoker}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
alter view 语句的语法和create view类似。
将cs_xs视图修改为只包含计算机专业学生的学号，姓名和总学分三列。
alter view cs_xs
as
select 学号,姓名,总学分
from xs
where 专业名=’计算机’;

删除视图
drop view [if exists]
view_name [,view_name]…
[restrict | cascade]
其中，view_name是视图名，声明了if exists，若视图不存在的话，也不会出现错误信息。也可以声明restrict和cascade，但他们没什么影响。

使用drop view一次可删除多个视图，例如：
drop view cs_xs；