MySQL视图

什么是视图

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

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

视图的作用

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

  1. 简单性

    看到的就是需要的。视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。

  2. 安全性

    视图的安全性可以防止未授权用户查看特定的行或列,使有权限用户只能看到表中特定行的方法,如下:

    • 在表中增加一个标志用户名的列。
    • 建立视图,使用户只能看到标有自己用户名的行。
    • 把视图授权给其他用户。
  3. 逻辑数据独立性

    视图可以使应用程序和数据库表在一定程度上独立。如果没有视图,程序一定是建立在表上的。有了视图之后,程序可以建立在视图之上,从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立。

    • 如果应用建立在数据库表上,当数据库表发生变化时,可以在表上建立视图,通过视图屏蔽表的变化,从而使应用程序可以不动。
    • 如果应用建立在数据库表上,当应用发生变化时,可以在表上建立视图,通过视图屏蔽应用的变化,从而使数据库表不动。
    • 如果应用建立在视图上,当数据库表发生变化时,可以在表上修改视图,通过视图屏蔽表的变化,从而使应用程序可以不动。
    • 如果应用建立在视图上,当应用发生变化时,可以在表上修改视图,通过视图屏蔽应用的变化,从而使数据库可以不动。

视图使用场景

  1. 计算列的需要,数据库设计范式要求我们减少冗余字段,因此现在很多数据表都没有计算列字段,如采购单:有价格、数量、税率、含税金额,多半没有不含税金额、税额,而这些字段在很多报表中有都会用到,所以我们可以创建一个含有计算列字段的视图来解决这个问题。
  2. 不同表字段聚合,信息重组,如:经销商通常有业务员,业务员通常有上下级关系(客户经理、区域经理、大区经理等),因此查看经销商业务员时我们需要看到直管业务员是谁?该业务员对应的区域经理、大区经理是谁(可能 NULL)?因此我们可以联合经销商表、业务员信息表、业务员上下级关系表定义一个经销商视图。
  3. 安全性需要,主要是早期遗留系统集成需要,如:需要 xx 系统数据,又没接口,怎么办?直接操作数据库,这时就涉及数据安全性,合理利用视图则可以减少很多授权工作和保证数据安全性。当下新构建的系统几乎都是暴露api接口,因此数据安全性更多关注在接口的身份认证和数据粒度方面。

创建视图

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

查看创建视图的权限

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

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名';

参数说明:

参数 描述
Select_priv 属性表示用户是否具有 SELECT 权限,Y 表示拥有 SELECT 权限,N 表示没有。
Create_view_priv 属性表示用户是否具有 CREATE VIEW 权限。
mysql.user 表示 MySQL 数据库下面的 user 表。
用户名 参数表示要查询是否拥有权限的用户,该参数需要用单引号引起来。

示例:查询 MySQL 中 root 用户是否具有创建视图的权限:

SELECT * FROM mysql.user WHERE user='root';

创建视图

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

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

参数说明:

参数 描述
ALGORITHM 可选项,表示视图选择的算法。
视图名 表示要创建的视图名称。
属性清单 可选项,指定视图中各个属性的名词,默认情况下与 SELECT 语句中的查询的属性相同。
SELECT语句 表示一个完整的查询语句,将查询记录导入视图中。
WITH CHECK OPTION 可选项,表示更新视图时要保证在该视图的权限范围之内。

示例:创建视图:

CREATE OR REPLACE VIEW view_user AS SELECT id,name FROM tb_user;

示例:创建视图同时,指定属性清单:

CREATE OR REPLACE VIEW view_user (a_id,a_name) AS SELECT id,name FROM tb_user;

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

  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 的语句,它将被忽略。

修改视图

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

ALTER VIEW view_user AS SELECT id,name FROM tb_user where id in (select id from tb_user);

说明:ALTER VIEW 语句改变了视图的定义,该语句与 CREATE OR REPLACE VIEW 语句有着同样的限制,如果删除并重新创建一个视图,就必须重新为它分配权限。

删除视图

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

DROP VIEW IF EXISTS view_user;