MySQL笔记-09 视图

视图是从一个或多个表中导出的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。

1 视图概念

1.1 概念

视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

视图是存储在数据库中的查询的SQL语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。

1.2 作用

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。下面将视图的作用归纳为如下几点：

• 简单性 :视图不仅可以简化用户对数据的理解，也可以简化他们的操作。
• 安全性 :视图的安全性可以防止未授权用户查看特定的行或列，使有权限用户只能看到表中特定行
• 逻辑数据独立性 :可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

2 创建视图

创建视图是指在已经存在的数据库表上建立视图，视图可以建立在一张表中，也可以建立在多张表中。

2.1 查看创建视图的权限

创建视图需要具有CREATE VIEW的权限，同时应该具有查询涉及的列的SELECT权限。可以使用SELECT语句来查询这些权限信息，查询语法如下：

SELECT Selete_priv，Create_view_priv FROM mysql.user WHERE user='用户名';

• （1）Selete_priv属性表示用户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。
• （2）Create_view_priv属性表示用户是否具有CREATE VIEW权限；mysql.user表示MySQL数据库下面的user表。
• （3）“用户名”参数表示要查询是否拥有DROP权限的用户，该参数需要用单引号引起来。

2.2 创建视图的步骤

MySQL中，创建视图是通过CREATE VIEW语句实现的，其语法如下:

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

• （1）ALGORITHM是可选参数，表示视图选择的算法；
• （2）“视图名”参数表示要创建的视图名称；
• （3）“属性清单”是可选参数，指定视图中各个属性的名词，默认情况下与SELECT语句中查询的属性相同；
• （4）SELECT语句参数是一个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中；
• （5）WITH CHECK OPTION是可选参数，决定是否允许更新数据使记录不再满足视图的条件：
  • LOCAL 是只要满足本视图的条件就可以更新；
  • CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新，此选项是默认值；

示例：

CREATE VIEW
book_view1(a_sort，a_talk，a_books)
AS SELECT sort，talk，books
FROM tb_book;

2.3 创建视图的注意事项

创建视图时需要注意以下几点:

• （1）运行创建视图的语句需要用户具有创建视图（create view）的权限，若加了[or replace]时，还需要用户具有删除视图（drop view）的权限。
• （2）SELECT语句不能包含FROM子句中的子查询。
• （3）SELECT语句不能引用系统或用户变量。
• （4）SELECT语句不能引用预处理语句参数。
• （5）在存储子程序内，定义不能引用子程序参数或局部变量。
• （6）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。
• （7）在定义中不能引用temporary表，不能创建temporary视图。
• （8）在视图定义中命名的表必须已存在。
• （9）不能将触发程序与视图关联在一起。
• （10）在视图定义中允许使用order by，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。

3 视图操作

3.1 查看视图

查看视图是指查看数据库中已存在的视图。查看视图必须要有SHOW VIEW的权限。查看视图的方法主要包括DESCRIBE语句、SHOW TABLE STATUS语句、SHOW CREATE VIEW语句等。

3.1.1 DESCRIBE语句

DESCRIBE可以缩写成DESC，其语法格式如下:

DESCRIBE 视图名;

如果只需了解视图中的各个字段的简单信息，可以使用DESCRIBE语句。DESCRIBE语句查看视图的方式与查看普通表的方式是相同的，结果显示的方式也相同。通常情况下，都是使用DESC代替DESCRIBE。

3.1.2 SHOW TABLE STATUS语句

在MySQL中，可以使用SHOW TABLE STATUS语句查看视图的信息，其语法格式如下:

SHOW TABLE STATUS LIKE '视图名';

• （1）“LIKE”表示后面匹配的是字符串；
• （2）“视图名”参数指要查看的视图名称，需要用单引号定义。

3.1.3 SHOW CREATE VIEW语句

在MySQL中，SHOW CREATE VIEW语句可以查看视图的详细定义，其语法格式如下:

SHOW CREATE VIEW 视图名;

3.2 修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER语句来修改视图。

3.2.1 CREATE OR REPLACE VIEW

在MySQL中，CREATE OR REPLACE VIEW语句可以用来修改视图。该语句的使用非常灵活。在视图已经存在的情况下，对视图进行修改；视图不存在时，可以创建视图。

CREATE OR REPLACE VIEW语句的语法如下：

CREATE OR REPLACE [ALGORITHM={UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图[(属性清单)]
AS SELECT 语句
[WITH [CASCADED | LOCAL] CHECK OPTION];

3.2.2 ALTER

ALTER VIEW语句改变了视图的定义，包括被索引视图，但不影响所依赖的存储过程或触发器。该语句与CREATE VIEW语句有着同样的限制，如果删除并重建了一个视图，就必须重新为它分配权限。

ALTER VIEW语句的语法如下:

alter view [algorithm={merge | temptable | undefined} ]view view_name [(column_list)] as select_statement[with
[cascaded | local] check option]

• （1）algorithm：该参数已经在创建视图中做了介绍，这里不再赘述。
• （2）view_name：视图的名称。
• （3）select_statement：SQL语句用于限定视图。

在创建视图时，在使用了WITH CHECK OPTION， WITH ENCRYPTION，WITH SCHEMABING或VIEW_METADATA选项时，如果想保留这些选项提供的功能，必须在ALTER VIEW语句中将它们包括进去。

3.3 更新视图

对视图的更新其实就是对表的更新，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据。通过视图更新时，都是转换到基本表来更新。更新视图时，只能更新权限范围内的数据，超出了范围，就不能更新。

3.3.1 更新视图的限制

并不是所有的视图都可以更新，以下几种情况是不能更新视图的:

• （1）视图中包含COUNT()、SUM()、MAX()和MIN()等函数。例如：

CREATE VIEW book_view1(a_sort，a_book)
AS SELECT sort，books， COUNT(name) FROM tb_book;

• （2）视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVIG等关键字。例如：

CREATE VIEW book_view1(a_sort，a_book)
AS SELECT sort，books， FROM tb_book GROUP BY id;

• （3）常量视图。例如：

CREATE VIEW book_view1
AS SELECT 'Aric' as a_book;

• （4）视图中的SELECT中包含子查询。例如：

CREATE VIEW book_view1(a_sort)
AS SELECT (SELECT name FROM tb_book);

• （5）由不可更新的视图导出的视图。例如：

CREATE VIEW book_view1
AS SELECT * FROM book_view2;

• （6）创建视图时，ALGORITHM为TEMPTABLE类型。例如：

CREATE ALGORITHM=TEMPTABLE
VIEW book_view1
AS SELECT * FROM tb_book;

• （7）视图对应的表上存在没有默认值的列，而且该列没有包含在视图里。例如，表中包含的name字段没有默认值，但是视图中不包括该字段，那么这个视图是不能更新的。因为，在更新视图时，这个没有默认值的记录将没有值插入，也没有NULL值插入。数据库系统是不会允许这样的情况出现的，其会阻止这个视图更新。

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，可能会造成数据更新失败。

3.4 删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用DROP VIEW语句来删除视图。但是，用户必须拥有DROP权限。

DROP VIEW语句的语法如下。

DROP VIEW IF EXISTS <视图名> [RESTRICT | CASCADE]

• （1）IF EXISTS参数指判断视图是否存在，如果存在则执行，不存在则不执行。
• （2）“视图名”列表参数表示要删除的视图的名称和列表，各个视图名称之间用逗号隔开。

该语句从数据字典中删除指定的视图定义；如果该视图导出了其他视图，则使用CASCADE级联删除，或者先显式删除导出的视图，再删除该视图；删除基表时，由该基表导出的所有视图定义都必须显式删除。