虚拟世界——MySQL视图

一、视图的概念

视图是一个虚拟表，是从数据库中一个表或多个表中导出的表，是一个逻辑表，本身不包含数据，作为一个select语句保存在数据字典中的。因此，使用视图查询数据时，数据库系统会从原来的表中取出对应的数据，所以视图中的数据是依赖于原来表中的数据的，一旦原来表中的数据发生变化，视图中的数据也会发生变化。

二、视图的优点

（1）简单：使用视图的用户完全不需要关心后面对应的表的结构，关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
（2）安全性：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管并不能限制到某个行或者某个列上，但是通过视图就可以简单实现。
（3）逻辑数据独立性：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表添加列对视图是没有影响的；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
总而言之：使用视图是为了保障数据的安全性，提高查询效率。

三、视图的基本操作

1、查看用户是否具有创建视图的权限

mysql> select Select_priv,Create_view_priv FROM mysql.user WHERE user = 'root';
+-------------+------------------+
| Select_priv | Create_view_priv |
+-------------+------------------+
| Y           | Y                |
+-------------+------------------+
1 row in set (0.03 sec)

Select_priv属性表明用户是否具有select权限；
Create_view_priv属性表明用户是否具有create view权限。

2、创建视图

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

参数详解：
OR REPLACE：表示替换已有视图
ALGORITHM：表示视图选择的算法，默认算法是UNDEFINED(未定义的)MySQL自动选择的算法；merge代表合并，temptable临时表

基本格式：

create view 
viewname[(column_list)]
as select语句
with check option

1、在单个表上创建视图

基础表

mysql> select * from user;
+--------+---------+
| userId | userAge |
+--------+---------+
|      1 |      11 |
|      2 |      22 |
|      3 |      33 |
+--------+---------+
3 rows in set (0.00 sec)

以上面这个表为基础，创建视图，选择出userAge为11的记录。

mysql> create view
    -> userview(user_id,user_age)
    -> as select userId,userAge
    -> from user where userAge = 11;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from userview;
+---------+----------+
| user_id | user_age |
+---------+----------+
|       1 |       11 |
+---------+----------+
1 row in set (0.00 sec)

2、在多个表上创建视图

mysql> create view productCustomers
    -> as select cust_name,cust_contact,prod_id
    -> from Customers,Orders,OrderItems
    -> where Customers.cust_id = Orders.cust_id
    -> and OrderItems.order_num = Orders.order_num;
Query OK, 0 rows affected (0.20 sec)

mysql> select cust_name,cust_contact
    ->
    -> from productCustomers
    -> where prod_id = 'RGAN01';
+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set (0.00 sec)

三、查看视图（3种方式）

mysql> describe userview;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| userId  | smallint(6) | NO   |     | 0       |       |
| userAge | smallint(6) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)

1、简单的列出了视图中各个字段的信息

mysql> show table status like 'userview'\G;
*************************** 1. row ***************************
           Name: userview
         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
1 row in set (0.00 sec)

2、可以查看存储引擎，数据长度等信息，并且这些信息都显示为null，则说明视图是虚拟表

mysql> show create view userview;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View     | Create View                                                                                                                                                                                              | character_set_client | collation_connection |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| userview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `userview` AS select `user`.`userId` AS `userId`,`user`.`userAge` AS `userAge` from `user` where (`user`.`userAge` = 11) | utf8                 | utf8_general_ci      |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

可以查看视图的详细定义

视图一旦创建完毕，就可以像一个普通表那样使用，视图主要用来查询

mysql> select * from userview;
+--------+---------+
| userId | userAge |
+--------+---------+
|      1 |      11 |
+--------+---------+
1 row in set (0.00 sec)

3、有关视图的信息记录在information_schema数据库的views表中

mysql> select * from information_schema.views where table_name = 'userview'\G;
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: imooc
          TABLE_NAME: userview
     VIEW_DEFINITION: select `imooc`.`user`.`userId` AS `userId`,`imooc`.`user`.`userAge` AS `userAge` from `imooc`.`user` where (`imooc`.`user`.`userAge` = 11)
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.11 sec)

四、修改视图

修改视图是指修改数据库中已存在的表的定义，当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间的一致。

1、create or replace view

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

mysql> create or replace view userview(u_id,u_age) as select userId,userAge from user;
Query OK, 0 rows affected (0.42 sec)

mysql> select * from userview;
+------+-------+
| u_id | u_age |
+------+-------+
|    1 |    11 |
|    2 |    22 |
|    3 |    33 |
+------+-------+
3 rows in set (0.00 sec)

修改视图的列名为 u_id 和 u_age;

2、alter语句修改视图

ALTER
    [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]

修改视图是指修改数据库中已存在的表的定义，当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致

五、更新视图

对视图的更新其实就是对表的更新，更新视图是指通过视图来插入（insert）、更新（update）和删除（delete）表中的数据。因为视图没有数据，因此对视图进行的DML操作最终都会体现在基表中。

mysql> select * from userview;
+------+-------+
| u_id | u_age |
+------+-------+
|    1 |    11 |
|    2 |    22 |
|    3 |    33 |
+------+-------+
3 rows in set (0.00 sec)

mysql> update userview set u_age = 10 where u_id = 3;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+--------+---------+--------+
| userId | userAge | u_name |
+--------+---------+--------+
|      1 |      11 |        |
|      2 |      22 |        |
|      3 |      10 |        |
+--------+---------+--------+
3 rows in set (0.00 sec)

在视图上所做的修改，同时也在基本表上有所体现。

六、删除视图

删除视图是指删除数据库中已存在的视图，删除视图时，只能删除视图的定义，不会删除数据，也就是说不会修改基本表

mysql> drop view if exists userview;
Query OK, 0 rows affected (0.06 sec)

mysql> desc userview;
ERROR 1146 (42S02): Table 'imooc.userview' doesn't exist