概述

本章，您将学习 MySQL 中的视图。

视图（View）：MySQL 中的虚拟表，其内容由 DQL 组成。视图本身不实际存储数据，而是随一个或多个基础表(或视图)的查询结果动态生成数据。

视图的主要特性有：

• 虚拟性 - 不实际存储数据，仅保存定义视图的 SQL 查询语句
• 动态性 - 视图的数据会随基表数据的变化而自动更新。当基表中的数据被修改后，通过视图查询到的数据也会相应变化
• 逻辑独立性 - 将数据的逻辑结构与物理结构分离。即使底层表结构发生变化，只需修改视图定义而不影响依赖视图的应用程序

视图的主要作用有：

• 简化查询 - 将复制的查询语句封装在视图中以便调用
• 数据安全 - 通过视图限制用户只能访问特定列或行，隐藏敏感数据

管理视图

创建视图

创建视图的语法为：

create view 视图名称
    as
    select ...;

语法中的 create view 等同于 create or replace view 。

在前面的文章，我们导入到过 world.sql 这个示例数据库，且还在 DQL 的内容中说明过嵌套查询：

use world;

# 查询 GNP 大于 Aruba 的亚洲国家名称并列出关联的城市名
select
  a.name as `城市名`,
  b.name as `国家名`
from
  city as a
  inner join (
    select
      name,
      code
    from
      country
    where
      GNP > (
        select
          GNP
        from
          country
        where
          name = 'Aruba'
      )
      and Continent = 'Asia'
  ) as b on a.countrycode = b.code;

假设需要把「查询 GNP 大于 Aruba 的亚洲国家名称」（因为该视图需要连接多个表，所以查询列表除了 name 字段之外，还需要有一个 code 字段：）作为一个视图：

create view gnpview
as
select name,code from country where gnp > (select gnp from country where name='Aruba') and continent='Asia';

可通过调用视图来简化 DQL ：

select
  a.name as `城市名`,
  b.name as `国家名`
from
  city as a
  inner join gnpview as b on a.countrycode = b.code;

查看视图

可通过以下的语句或语法查看视图的信息：

# 查看视图的字段信息
desc 视图名;

# 查看视图的创建语句
show create view 视图名;

# 查看所有的视图（系统视图以及自定义视图）
select * from information_schema.views;

修改视图

可使用以下的语法更新已存在的视图：

alter view 视图名 as 查询语句; 

删除视图

可使用以下的语法删除一个或多个已存在的视图：

drop view 视图1, 视图2, 视图3...;

权限相关

创建视图、修改视图、删除视图都需要具体的用户权限，如下表所示：

我们还没有系统了解 MySQL 中的用户以及用户管理，这里只是提醒读者在使用视图时需要注意用户权限相关的内容。

使用视图

查询视图包含的结果集

select * from world.gnpview;

这是最常规的用法。

对视图执行 DML 操作

虽然允许对创建的视图执行 DML 操作，但是其绝大部分都用在与其他表一起使用的 DQL 中。不建议在生产环境中对视图执行 DML 操作。

需要注意！若创建视图的语句中包含了以下内容，则对视图执行 DML 操作不生效：

• 聚合函数（SUM()、MIN()、MAX()、COUNT()等）
• DISTINCT
• GROUP BY
• HAVING
• UNION 或 UNION ALL
• ORDER BY
• WHERE 关键字后面包含相关子查询（select ... from ... where ... (select ...);）
• FROM 关键字后面使用多个表（select ... from (selct ...); ，多表的内连接或外连接）
• 通过函数或表达式生成的列
• 表中不能被更新的数据

不能对 gnpview 这个视图执行 DML 操作，因为出现了以下情况：

WHERE 关键字后面包含相关子查询

对视图执行 DML 操作如下：

use home;

# 创建钱包类型的主表
create table if not exists wallettype(
  code char(10) primary key,
  name varchar(20) not null
);

# 创建包含家庭资产的钱包
create table if not exists homewallet(
  walletcode char(10),
  ownname varchar(40) not null,
  amount decimal(10,3) not null default '0.000',
  foreign key (walletcode) references `wallettype`(code)
);

# 在主表中插入数据
insert into wallettype values('vw','virtual wallet'),('bw','bank wallet'),('cw','cash wallet');

# 在从表中插入数据
insert into homewallet values('bk', 'jack', 1000);

# 假设需要创建这样一个隐藏掉金额列的简单视图：
create view homeview
as
select walletcode,ownname from homewallet;

# 查看视图结构
desc home.homeview;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| walletcode | char(10)    | YES  |     | NULL    |       |
| ownname    | varchar(40) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 对 homeview 这个视图执行数据的插入：
insert into home.homeview values('bw', 'clinton');

# 查询 homewallet 表的数据
+------------+---------+----------+
| walletcode | ownname | amount   |
+------------+---------+----------+
| bw         | jack    | 1000.000 |
| bw         | clinton |    0.000 |
+------------+---------+----------+
2 rows in set (0.00 sec)

若对复杂视图执行 DML 操作，会常遇到隐藏掉不可见列的错误提示，这需要读者注意。

赞赏