MySQL基础-视图

视图是什么

MySQL 视图（View） 是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中 。行和列的数据来自于定义视图的查询中所使用的表（基表），并且还是在使用视图时动态生成的。数据库中只存放了视图的定义（.frm文件），并没有存放视图中的数据 ，这些数据都存放在定义视图查询所引用的基表中。使用视图查询数据时，数据库会从基表表中取出对应的数据。因此，视图中的数据是依赖于基表表中的数据的。一旦基表中的数据发生改变，显示在视图中的数据也会发生改变；在视图中修改数据，基表中的数据也会发生改变。
从下图可见，创建视图后，只生成了视图的 .frm 文件，没有 .ibd 文件，这是因为 customer_info.frm 与 customer_view.frm 的数据都是由 customer_info.ibd 提供。

视图的作用

保密
看这样一个需求：公司职员表的信息很多（姓名、薪水、部门、上级、工号、电话等），而其中有些信息属于个人隐私（薪水、电话），我们希望将此表下放到某管理员时，他只能看到其中的部分信息（姓名、部门、上级、工号），此时，就需要生成原表的一张视图，视图中只包含这一部分信息，然后再将此视图下放给该管理员。不恰当地说，“视图相对于对原表的封装”，对用户有部分不可见性。
性能
关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起,就可以避免使用JOIN查询数据。
灵活性
如果系统中有一张旧的表,这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

值得一提的是，长期以来，大多数互联网公司的《MySQL开发规范》中都有一条规范：在MySQL中禁止（或建议不要）使用视图。究其原因，主要是由于在MySQL中视图的查询性能不好，同时带来了管理维护上的高成本。

视图的用法

创建视图

1	CREATE VIEW <视图名> AS <SELECT语句>

创建视图前，先给出下面基本数据：

mysql> SELECT * FROM room_info;
+--------------+----------+-------+---------+
| type         | location | state | room_id |
+--------------+----------+-------+---------+
| 标准间       | 10-1     |     1 | 001     |
| 标准间       | 10-2     |     1 | 002     |
| 标准间       | 10-3     |     0 | 003     |
| 标准间       | 10-4     |     0 | 004     |
| 情侣房       | 10-5     |     0 | 005     |
| 情侣房       | 11-6     |     0 | 006     |
| 大床房       | 11-7     |     0 | 007     |
| 大床房       | 12-8     |     0 | 008     |
| 家庭套间     | 12-9     |     0 | 009     |
| 家庭套间     | 13-10    |     0 | 010     |
| 总统套房     | 10-11    |     1 | 011     |
+--------------+----------+-------+---------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM type_info;
+------+--------------+-------+----------+
| id   | type         | price | capacity |
+------+--------------+-------+----------+
|    1 | 标准间       |   150 | 2        |
|    2 | 大床房       |   200 | 2        |
|    3 | 情侣房       |   210 | 2        |
|    4 | 家庭套间     |   400 | 4        |
|    5 | 总统套房     |   600 | 4        |
+------+--------------+-------+----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM customer_info;
+--------+--------+------+-------------+---------+
| name   | ID     | age  | tel         | room_id |
+--------+--------+------+-------------+---------+
| 李华   | 511323 |   18 | 18990889826 | 002     |
| 周建   | 511345 |   20 | 17390199497 | 011     |
| 王霸   | 532435 |   45 | 18932981949 | 002     |
| 李斯   | 343234 |   34 | 14342324323 | 001     |
| 巴嘎   | 343323 |   23 | 32232123343 | 002     |
+--------+--------+------+-------------+---------+
5 rows in set (0.00 sec)

1）创建基于单表的视图：

mysql> CREATE VIEW customer_view
    -> AS
    -> SELECT `name`, room_id FROM customer_info;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM customer_view;
+--------+---------+
| name   | room_id |
+--------+---------+
| 李华   | 002     |
| 周建   | 011     |
| 王霸   | 002     |
| 李斯   | 001     |
| 巴嘎   | 002     |
+--------+---------+
5 rows in set (0.00 sec)

2）创建基于多表的视图：

mysql> CREATE VIEW customer_view2
    -> AS
    -> SELECT name, room_info.room_id, room_info.type
    -> FROM customer_info,  room_info
    -> WHERE customer_info.room_id=room_info.room_id;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM customer_view2;
+--------+---------+--------------+
| name   | room_id | type         |
+--------+---------+--------------+
| 李斯   | 001     | 标准间         |
| 李华   | 002     | 标准间         |
| 王霸   | 002     | 标准间         |
| 巴嘎   | 002     | 标准间         |
| 周建   | 011     | 总统套房       |
+--------+---------+--------------+
5 rows in set (0.00 sec)

3）基于视图创建视图（视图嵌套）

mysql> CREATE VIEW customer_view3
    -> AS
    -> SELECT name
    -> FROM customer_view2;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM customer_view3;
+--------+
| name   |
+--------+
| 李斯   |
| 李华   |
| 王霸   |
| 巴嘎   |
| 周建   |
+--------+

不建议视图嵌套创建，不仅影响效率，也会提高后期维护成本。 视图定义中不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。

另外，对于创建视图中的 SELECT 语句的指定存在以下限制：

用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基表和其他视图的相关权限。
SELECT 语句不能引用系统或用户变量。
SELECT 语句不能包含 FROM 子句中的子查询。
SELECT 语句不能引用预处理语句参数。

删除视图

1	DROP VIEW <视图名1> [ , <视图名2> …]

1 2	mysql> DROP VIEW customer_view2; Query OK, 0 rows affected (0.00 sec)

注意，删除视图后，建立在本视图上的视图也将不可用；比如将 customer_view2 删掉，customer_view3 将不可用：

1 2	mysql> SELECT * FROM customer_view3; ERROR 1356 (HY000): View 'custom_info.customer_view3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

可以使用 CHECK TABLE 来检查此类问题：

mysql> CHECK TABLE customer_view3 \G;
*************************** 1. row ***************************
   Table: custom_info.customer_view3
      Op: check
Msg_type: Error
Msg_text: Table 'custom_info.customer_view2' doesn't exist
*************************** 2. row ***************************
   Table: custom_info.customer_view3
      Op: check
Msg_type: Error
Msg_text: View 'custom_info.customer_view3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
   Table: custom_info.customer_view3
      Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.00 sec)

第 6 行表明，其基表 customer_view2 不存在。

查看视图

1）查看结构

mysql> DESC demo_view1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(32) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2）查看定义

mysql> SHOW CREATE TABLE demo_view1 \G;
*************************** 1. row ***************************
                View: demo_view1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `demo_view1` AS select `stuinfo`.`name` AS `name`,`stuinfo`.`age` AS `age` from `stuinfo` order by `stuinfo`.`age`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
######或者
mysql> SHOW CREATE VIEW demo_view1 \G;
*************************** 1. row ***************************
                View: demo_view1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `demo_view1` AS select `stuinfo`.`name` AS `name`,`stuinfo`.`age` AS `age` from `stuinfo` order by `stuinfo`.`age`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

3）查看状态

mysql> SHOW TABLE STATUS \G;
*************************** 2. row ***************************
           Name: demo_view1
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW

修改视图

修改结构

mysql> DESC demo_view1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(32) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER VIEW demo_view1
    -> AS
    -> SELECT age
    -> FROM stuinfo;
Query OK, 0 rows affected (0.00 sec)

mysql> DESC demo_view1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

注意，只能从头修改视图的结构，无法仅添加列。

修改内容

mysql> SELECT * FROM customer_view;
+--------+---------+
| name   | room_id |
+--------+---------+
| 李华   | 002     |
| 周建   | 011     |
| 王霸   | 002     |
| 李斯   | 001     |
| 巴嘎   | 002     |
+--------+---------+
5 rows in set (0.00 sec)

mysql> UPDATE customer_view
    -> SET room_id='011'
    -> WHERE name='李华';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM customer_view;
+--------+---------+
| name   | room_id |
+--------+---------+
| 李华   | 011     |
| 周建   | 011     |
| 王霸   | 002     |
| 李斯   | 001     |
| 巴嘎   | 002     |
+--------+---------+
5 rows in set (0.00 sec)