MySQL基础笔记（五）视图

以你之姓@ 2022-08-02 00:16 208阅读 0赞

# 一、什么是视图 #

视图是一个**虚拟表**。也就是说，视图在外观和行为上都类似于表，但它不需要实际的物理存储，只保存了视图定义（查询语句）。

视图由`select`查询所定义 —— 当创建一个视图时，实际上是在数据库里执行了一个select语句，它从一个或多个表中导出这个视图。

*  对视图的操作与对表的操作一样，可以对视图进行查询、更新、删除。
 *  视图只是从表里引用数据，所以对视图的更新会反映到表中，对表的更新也会反映到视图中。
 *  如果用于创建视图的表被删除了，该视图将不可访问。

视图的作用：

1.  **简化数据访问。**当我们需要一个表中的某些数据时，我们不需要每次都进行查询，通过创建一个视图来包含我们所需的数据，然后从这个视图获取数据。
2.  **视图可以作为一种安全形式。**如果一个表的某些字段的数据是保密的，那么就不能让用户直接访问这个表。可以在这个表的基础上创建一个视图，过滤掉保密的字段，然后授权用户访问这个视图。——通过视图用户只能查询和修改他们所能见到的数据。
3.  **逻辑数据独立。**视图可帮助用户屏蔽真实表结构变化带来的影响，在一定程度上使应用程序和数据库表独立。

# 二、创建视图 #

创建视图使用`CREATE VIEW`语句，我们可以从一个表、多个表或另一个视图来创建视图。

## 2.1 从一个表创建视图 ##

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

**例：**假设有下面这样一个表`workers`，分别保存了员工的名字，性别，工资，年龄，居住城市。

+----+--------+--------+--------+------+---------+
    | id | name   | sex    | salary | age  | city    |
    +----+--------+--------+--------+------+---------+
    |  1 | Oliver | male   |   3450 |   25 | Chicago |
    |  2 | Paul   | male   |   5170 |   23 | Boston  |
    |  3 | Sherry | female |   3050 |   30 | Boston  |
    |  4 | Robin  | male   |   8350 |   40 | Beijing |
    |  5 | Nina   | female |   6700 |   27 | Chicago |
    |  6 | Jacky  | male   |   9000 |   35 | Beijing |
    |  7 | Tim    | male   |   5600 |   29 | Chicago |
    +----+--------+--------+--------+------+---------+

**Question 1：**如果该公司有一个员工写报告时需要获取每个员工的姓名、年龄等信息，但工资不能被他看到。这时我们就可以创建一个视图，让他访问视图：

CREATE VIEW v1 AS SELECT name,sex,age,city FROM workers;

查看视图如下：

**Question 2：**如果要维护对员工的一个统计报告，比如统计每个城市员工的平均年龄、平均薪水、男性总数、女性总数，我们可以基于这个表创建一个视图，之后只要对视图进行查询即可。

CREATE VIEW v2 AS
    SELECT city,
           AVG(age),
           AVG(salary),
           (SELECT COUNT(sex) FROM workers w1 WHERE w1.city=w2.city AND sex='male') AS num_male,
           (SELECT COUNT(sex) FROM workers w1 WHERE w1.city=w2.city AND sex='female') AS num_female
    FROM workers w2
    GROUP BY city;

查看统计视图如下：

mysql> SELECT * FROM v2;
    +---------+----------+-------------+----------+------------+
    | city    | AVG(age) | AVG(salary) | num_male | num_female |
    +---------+----------+-------------+----------+------------+
    | Beijing |  37.5000 |   8675.0000 |        2 |          0 |
    | Boston  |  26.5000 |   4110.0000 |        1 |          1 |
    | Chicago |  27.0000 |   5250.0000 |        2 |          1 |
    +---------+----------+-------------+----------+------------+

在 MySQL 中，`information_schema`数据库下的`views`表中存储了所有视图的定义。通过对views表的查询，可以查看数据库中所有视图的详细信息：

SELECT * FROM information_schema.views;

## 2.2 从多个表创建视图 ##

通过将多个表进行连接（JOIN），我们可以从多个表创建视图。其语法也是：

CREATE VIEW view_name AS SELECT...

视图本身不过是一个select语句而已，因此表在视图定义里的连接与在普通select语句里是一样的。

## 2.3 从视图创建视图 ##

我们还可以从视图创建视图：

CREATE VIEW v3 AS SELECT * FROM v2;

视图创建视图可以具有多个层次（视图的视图的视图，以此类推），如下图所示：

![20150702231013310][]

基于视图创建视图的唯一问题在于它们的可管理性。如上图中的视图，它们一层一层依赖，如果`VIEW1`被删除了，`VIEW3`和`VIEW4`就无效了，如果`TABLE`被删除了，这些视图就都无效了。

# 三、修改视图定义 #

修改视图是指修改已经存在的视图。当表的某些字段发生变化时，可以通过修改视图来保持与表的一致性。语法如下：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
        VIEW view_name [(column_list)] AS 
        SELECT...

# 四、视图的执行算法 #

在创建或修改视图时，有一个可选的 Algorithm 选项，它表示视图的执行算法。

MySQL中的视图存在两种执行算法：

1.  Merge：合并的执行方式，每当执行的时候，先将我们视图的sql语句与外部查询视图的sql语句，混合在一起，最终执行；
2.  Temptable：临时表模式，每当查询的时候，将视图所使用的select语句生成一个结果的临时表，再在当前的临时表内进行查询。

所以ALGORITHM可取三个值：MERGE、TEMPTABLE、UNDEFINED（默认值）。

*  **对于MERGE**，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
 *  **对于TEMPTABLE**，视图的结果将被置于临时表中，然后使用它执行语句。
 *  **对于UNDEFINED**，MySQL自动选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且`如果使用了临时表，视图是不可更新的`。

# 五、更新视图 #

更新视图是指对视图进行插入、更新、删除操作。因为视图是一个虚拟表，对视图更新都是转到了对表进行更新。以上面创建的 v1 视图进行示范：

## 5.1 Update ##

UPDATE v1 SET city='Los Angeles' WHERE name='Oliver';

查看`workers`表:

mysql> SELECT * FROM workers;
    +----+--------+--------+--------+------+-------------+
    | id | name   | sex    | salary | age  | city        |
    +----+--------+--------+--------+------+-------------+
    |  1 | Oliver | male   |   3450 |   25 | Los Angeles |
    |  2 | Paul   | male   |   5170 |   23 | Boston      |
    |  3 | Sherry | female |   3050 |   30 | Boston      |
    |  4 | Robin  | male   |   8350 |   40 | Beijing     |
    |  5 | Nina   | female |   6700 |   27 | Chicago     |
    |  6 | Jacky  | male   |   9000 |   35 | Beijing     |
    |  7 | Tim    | male   |   5600 |   29 | Chicago     |
    +----+--------+--------+--------+------+-------------+

## 5.2 Insert ##

若要通过视图插入数据，首先视图需要是可更新的，此外还需要满足一些要求：

*  不得有重复的视图列名称。
 *  视图必须包含没有默认值的基表中的所有列。
 *  视图列必须是简单的列引用而不是导出列。导出列不是简单的列引用，而是从表达式导出的。
 *  混合了简单列引用和导出列的视图是不可插入的，但是，如果仅更新非导出列，视图是可更新的。

例1：

CREATE VIEW view_t AS SELECT name,sex,salary,age,city FROM workers;

`view_t`视图包含了基表中的所有列（id是自增的），而且都是简单的列引用，所以可以通过视图插入新数据：

mysql> INSERT INTO v1 VALUES('Kobe','male',8500,36,'Los Angeles');
    Query OK, 1 row affected (0.28 sec)

例2：

CREATE VIEW view_t AS SELECT name,sex,salary/1000,age,city FROM workers;

现在`view_t`视图包含了导出列`salary/1000`，如果对导出列执行插入将会报错：

mysql> INSERT INTO v1 VALUES('Kobe','male',8.500,36,'Los Angeles');
    ERROR 1471 (HY000): The target table view_t of the INSERT is not insertable-into

## 5.3 Delete ##

从视图中删除一条记录：

DELETE FROM v1 WHERE name='Oliver';

查看`workers`表:

mysql> SELECT * FROM workers;
    +----+-----------+--------+--------+------+----------------+
    | id | name      | sex    | salary | age  | city           |
    +----+-----------+--------+--------+------+----------------+
    |  2 | Paul      | male   |   5170 |   23 | Boston         |
    |  3 | Sherry    | female |   3050 |   30 | Boston         |
    |  4 | Robin     | male   |   8350 |   40 | Beijing        |
    |  5 | Nina      | female |   6700 |   27 | Chicago        |
    |  6 | Jacky     | male   |   9000 |   35 | Beijing        |
    |  7 | Tim       | male   |   5600 |   29 | Chicago        |
    |  8 | Katherine | female |   7000 |   32 | Washington D.C |
    +----+-----------+--------+--------+------+----------------+

## 5.4 不可更新的视图 ##

若视图是可更新的，则可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。

但在下述几种情况下，视图是不可更新的：

*  视图中不包含基表中被定为非空的列；
 *  在定义视图的SELECT语句后的字段列表中使用了数学表达式；
 *  在定义视图的SELECT语句后的字段列表中使用了聚合函数COUNT、SUM、MAX、MIN、AVG；
 *  在定义视图的SELECT语句中使用了DISTINCT，UNION，TOP，GROUP BY或HAVING子句。
 *  ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

另外，若视图是基于多个表使用联接操作而导出的，且不满足上述条件，那么对这个视图执行更新操作时，每次只能影响其中的一个表，否则，更新操作也不能执行。

这里尝试对视图`v2`执行Delete操作，结果如下：

mysql> DELETE FROM v2 WHERE city='Beijing';
    ERROR 1288 (HY000): The target table v2 of the DELETE is not updatable

# 六、删除视图 #

删除视图使用`DROP VIEW`语句：

DROP VIEW [IF EXISTS] v1;

个人站点：[http://songlee24.github.com][http_songlee24.github.com]

[20150702231013310]: /images/20220731/c757911e4b4c41f2a15e77e202fbf30f.png
[http_songlee24.github.com]: http://songlee24.github.io/