MySQL——视图的创建和使用

简介

什么是视图

视图（View）是一种虚拟存在的表。其内容与真实的表相似，包含一系列带有名称的列和行数据。但是视图并不在数据库中以存储的数据的形式存在。行和列的数据来自定义视图时查询所引用的基本表，并且在具体引用视图时动态生成。

视图的特点如下：

<1> 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系；

<2> 视图是由基本表（实表）产生的表（虚表）；

<3> 视图的建立和删除不影响基本表；

<4> 对视图内容的更新（增删改）直接影响基本表；

<5> 当视图来自多个基本表时，不允许添加和删除数据。

为什么使用视图

数据库中关于数据的查询有时候非常复杂，例如表连接、子查询等，逻辑复杂，编写语句比较多。当这种查询需要重复使用时，则不会次次都能编写正确，从而降低了数据库的实用性。

在具体操作表前，有时候要求只能操作部分字段，而不是全部字段。例如，在公司中员工的工资一般是保密的，如果因为程序员一时疏勿而向查询中多写入了“工资”的字段，则会让员工的“工资”显示给所有能够查看该查询结果的人，这时就需要限制程序员操作的字段。

视图是程序员只关心感兴趣的某些特定数据和他们所负责的特定任务。这样程序员只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库的安全性。

视图相对于普通的表的有时主要包括以下几项：

<1> 简单：使用视图的用户完全不需要关心视图中的数据是通过什么查询得到的，视图中的数据对用户来说已经是过滤好的符合条件的结果集；

<2> 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行或某个列，但是通过视图就可以简答地实现；

<3> 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，原表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

视图操作

视图的操作包括创建视图、查看视图、删除视图和修改视图。

创建视图

依然是使用帮助命令? CREATE VIEW 找到官网的帮助地址：

http://dev.mysql.com/doc/refman/5.6/en/create-view.html

创建视图的语法：

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]

提取出关键部分得到基本语法：

CREATE VIEW view_name AS select_statement;

意为“使用查询语句select_statement的查询结果创建视图view_name”。

CREATE VIEW代码段创建一个新的视图，或是在使用了OR REPLACE选项时尝试替换一个已经存在的视图。如果视图view_name不存在，CREATE OR REPLACE VIEW 等同于CREATE VIEW，如果存在则等同于ALTERVIEW。

示例1，创建并使用简单视图：

<1> 创建视图保存员工名和对应的部门名：

MySQL> CREATE OR REPLACE VIEW view_emp_with_deptname

    -> AS

    -> SELECT e.ename,d.deptname FROM emp e,dept d WHERE e.deptno=d.deptno;

Query OK, 0 rows affected (0.02 sec)

<2> 使用视图，查询出所有的数据：

mysql> SELECT * FROM view_emp_with_deptname;

+--------+-----------+

| ename  | deptname  |

+--------+-----------+

| 张三   |金融部    |

| 李四   |金融部    |

| 赵六   |金融部    |

| 王五   |人事部    |

| 周七   |事业部    |

+--------+-----------+

5 rows in set (0.00 sec)

<3> 向emp表中插入一条数据，再次使用视图：

mysql> SELECT * FROM view_emp_with_deptname;

+--------+-----------+

| ename  | deptname  |

+--------+-----------+

| 张三   |金融部    |

| 李四   |金融部    |

| 赵六   |金融部    |

| 王五   |人事部    |

| 周七   |事业部    |

| 测试   |事业部    |

+--------+-----------+

6 rows in set (0.00 sec)

可以发现查询结果已经更新了。

示例2，使用视图插入、修改、删除表中数据：

<1> 创建视图view_emp，插入数据，删除数据，更新数据，查询数据：

mysql> INSERT INTO view_emp VALUES('刘梅',22,3);

Query OK, 1 row affected (0.01 sec)

 

mysql> DELETE FROM view_emp WHERE ename='测试';

Query OK, 1 row affected (0.02 sec)

 

mysql> UPDATE view_emp SET ename='张三三' WHERE ename='张三';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> SELECT * FROM view_emp;

+-----------+------+--------+

| ename     | age  | deptno |

+-----------+------+--------+

| 张三三    |   21 |      1 |

| 李四      |   22 |      1 |

| 王五      |   27 |      2 |

| 赵六      |   31 |      1 |

| 周七      |   23 |      3 |

| 孙八      |   37 |      5 |

| 刘梅      |   22 |      3 |

+-----------+------+--------+

7 rows in set (0.00 sec)

可以看到，操作都正确执行，且视图中的数据也随之更新。

<2> 查看emp表中的数据是否更新;

mysql> SELECT ename,age,deptno FROM emp;

+-----------+------+--------+

| ename     | age  | deptno |

+-----------+------+--------+

| 张三三    |   21 |      1 |

| 李四      |   22 |      1 |

| 王五      |   27 |      2 |

| 赵六      |   31 |      1 |

| 周七      |   23 |      3 |

| 孙八      |   37 |      5 |

| 刘梅      |   22 |      3 |

+-----------+------+--------+

7 rows in set (0.00 sec)

同样，基表emp中的数据由于对视图的修改也得到了修改。

视图的限制

使用视图的限制信息请参考Section C.5, “Restrictions on Views”。整理为以下几点：

<1> 不能在视图上创建索引；

<2> 在视图的FROM子句中不能使用子查询；

<3> 以下情形中的视图是不可更新的（更新是指对基表中的数据进行更新，并不是指基表中数据修改后，视图中的数据不进行更新或是不能对视图的定义进行修改）：

         [1]包含以下关键字的SQL语句：聚合函数(SUM、MIN、MAX、COUNT等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL；

         [2]常量视图；

         [3]SELECT中包含子查询；

         [4]JOIN；

         [5]FROM一个不能更新的视图；

         [6]WHERE子句的子查询引用了FROM子句中的表。

注：

<1>不能在视图上创建索引

最开始解释视图的概念时有这样的描述“视图并不在数据库中以存储的数据的形式存在。行和列的数据来自定义视图时查询所引用的基本表，并且在具体引用视图时动态生成”，该描述结合索引“复制索引列值保存到内存中”的特点就可得到此限制的原因：视图不是表或是临时表，其中没有可供索引复制的值。

<2>在视图的FROM子句中不能使用子查询

先看一下是否真的不允许：

mysql> CREATE VIEW view_t2 AS SELECT e.* FROM (SELECT * FROM emp) AS e;

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

可以看到，确实明确的指出不允许在FROM子句中包含子查询。

变通

虽然FROM子句中不能使用子查询，但是可以先为子查询创建一个视图，然后再从视图中装载数据：

mysql> CREATE VIEW view_t3 AS SELECT * FROM emp;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CREATE VIEW view_t2 AS SELECT * FROM view_t3;

Query OK, 0 rows affected (0.02 sec)

 

mysql> SELECT * FROM view_t3;

#省略

mysql> INSERT INTO view_t2 VALUES(NULL,'张鑫',33,3,250,'ZHANGXIN');

Query OK, 1 row affected (0.02 sec)

无论是查询还是修改基表中的数据都是正确执行的。

谨慎使用SELECT * 创建视图

创建视图时如果使用语句SELECT * FROMt…，需要小心，为t增加一列，新增加的列不会反映到视图中；但是，从t中删除一列，使用视图时会报错。查看一下使用SELECT * FROM…创建的视图的创建SQL就可以发现原因：

mysql> SHOW CREATE VIEW view_t3;

    ->| view_t3 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 

SQL SECURITY DEFINER VIEW `view_t3` AS select

`emp`.`id` AS `id`,`emp`.`ename` AS `ename`,`emp`.`age` AS `age`,`emp`.`deptno` 

AS `deptno`,`emp`.`salary` AS `salary`,`emp`.`phoneticize` AS `phoneticize` 

from `emp` | utf8                 | utf8_general_ci      |

观察灰色部分可以看到，MySQL将*翻译成了创建视图时基表中的每一个列名，但是在基表中增加或删除列并不会更改视图的创建信息，所以在基表中增加列或删除列都不会反映到视图中，且如果是删除列，使用视图时会报错。

查看视图

SHOW TABLES;

查看视图的定义：

SHOW CREATE VIEW view_name;

删除视图

DROP VIEW [IF EXISTS] view_name [,…,view_name_n]…[RESTRICT|CASCADE];

修改视图

方式一：先删除再创建，使用语句CREATE OR REPLACE或是分步骤先DROP然后CREATE；

方式二：使用ALTER VIEW：

         ALTERVIEW view_name as select_statement;