MYSQL---视图

1、概述:

视图是从一个或者多个表中导出的,视图的行为与表非常类似,但视图是一个虚拟表.在视图中用户可以使用SELECT语句查询数据,以及使用INSERT、UPDATE和DELETE修改记录。从MYSQL5.0开始可以使用视图，视图可以使用户操作方便，而且可以保障数据库系统的安全。

1.1、视图的含义

视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。

视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

下面有个student表和stu_info表，在student表中包含了学生的id号和姓名，stu_info表中包含了学生的id号、班级和家庭住址，而现在公布分班信息，只需要id号、姓名和班级，这该如何解决？

表设计如下：

 CREATE TABLE student (
    s_id INT,
    name VARCHAR(40)
    );
  CREATE TABLE stu_info(
    s_id INT,
    glass VARCHAR(40),
    addr  VARCHAR(90)
    );

通过DESC+表名命令可以查看表的设计，可以获得字段、字段的定义、是否为主键、是否为空、默认值和扩展信息。

视图提供了一个很好的解决方法，创建视图的信息来自表的部分信息，只取需要的信息。这样既能满足要求也不破坏表原来的结构。

1.2、视图的作用

与直接从数据表中读取相比，视图有以下优点：

1.简单化

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2.安全性

通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能被授权到数据库特定的行和特定的列上。通过视图，用户可以被限制在数据的不同子集上；

使用权限可被限制在基表的行的子集上。
使用权限可被限制在基表的列的子集上。
使用权限可被限制在基表的行和列的子集上。
使用权限可被限制在多个基表的连接所限定的行上。
使用权限可被限制在基表中的数据的统计汇总上。
使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。

3.逻辑数据的独立性

视图可帮助用户屏蔽真实表结构变化带来的额影响。

2、创建视图

视图中包含了SELECT 查询的结果,因此视图的创建基于SELECT语句和已存在的数据表,视图可以建立在一张表上,也可以建立在多张表上.

2.1、创建视图的语法形式

创建视图使用CREATE VIEW 语句，基本语法格式如下：

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

其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；ALGORITHM表示视图选择的算法；view_name为视图的名称，column_list为属性列；SELECT_statement表示SELECT语句；WITH[CASCADED|LOCAL]CHECKOPTION参数表示视图在更新时保证在视图的权限范围内。

ALGORITHM的取值有3个，分别是UNDEFINED|MERGE|TEMPTABLE,UNDEFINED表示MYSQL将自动选择算法；MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句。

CASCADED与LOCAL为可选参数，CASCADED为默认值，表示更新视图时要满足所有相关视图的和表的条件;LOCAL表示更新视图时满足该视图本身定义的条件即可。

该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用到的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。

视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时应将名称指定为db_name.view_name。

2.2、在单表创建视图

MYSQL可以在单个数据表上创建视图

例1：在t表上创建一个名为view_t的视图。

首先创建基本表并插入数据，语句如下：

  create table t (quantity int,price int);
  insert into t values(3,50);

创建视图语句如下：

 create view view_t as select quantity,price,quantity * price from t;

语句执行如下：

mysql> create view view_t as select quantity,price,quantity * price from t;
  Query OK, 0 rows affected (0.59 sec)
  mysql> select * from view_t;
  +----------+-------+------------------+
  | quantity | price | quantity * price |
  +----------+-------+------------------+
  |        3 |    50 |              150 |
  +----------+-------+------------------+
  1 row in set (0.04 sec)

默认情况下创建的视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图。

例2：在t表格上创建一个名为view_t2的视图，代码如下：

  mysql> create view view_t2(qty,price,total) as select quantity,price,quantity * price from t;
  Query OK, 0 rows affected (0.06 sec)
  
  mysql> select * from view_t2;
  +------+-------+-------+
  | qty  | price | total |
  +------+-------+-------+
  |    3 |    50 |   150 |
  +------+-------+-------+
  1 row in set (0.00 sec)

可以看到，view_t2和view_t两个视图中字段名称不同，但数据却是相同的。因此，在使用视图的时候，可能用户根本就不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。

2.3、在多表上创建视图

MYSQL中也可以在两个或者两个以上的表上创建视图，可以使用CREATE VIEW语句实现。

例1：在表student和表stu_info上创建视图stu_glass,代码如下：

首先向两个表中插入数据，输入语句如下：

mysql> insert into student values(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai');
  Query OK, 3 rows affected (0.25 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  mysql> insert into stu_info values(1,'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong');
  Query OK, 3 rows affected (0.15 sec)
  Records: 3  Duplicates: 0  Warnings: 0

创建视图stu_class,语句如下:

 create view stu_glass (id ,name ,glass) as select student.s_id,student.name,stu_info.glass
   from student,stu_info where student.s_id=stu_info.s_id;

代码的执行如下：

mysql> create view stu_glass (id ,name ,glass) as select student.s_id,student.name,stu_info.glass
      -> from student,stu_info where student.s_id=stu_info.s_id;
  Query OK, 0 rows affected (0.08 sec)
  
  mysql> select * from stu_glass;
  +------+----------+--------+
  | id   | name     | glass  |
  +------+----------+--------+
  |    1 | wanglin1 | wuban  |
  |    2 | gaoli    | liuban |
  |    3 | zhanghai | qiban  |
  +------+----------+--------+
  3 rows in set (0.00 sec)

这个例子就解决了刚开始提出的问题，通过这个视图可以很好地保护基础表中的数据。这个视图中的信息很简单，只包含了id、姓名和班级，id字段对应student表中的s_id字段，name字段对应student表中的name字段，glass字段对应stu_info表中的glass字段。

3、查看视图

查看视图是查看数据库中已存在的视图的定义。查看视图必须要有show view的权限，MYSQL数据库下的user表中保存着这个信息。查看视图的方法包括：describe、show table status 和show create view。

3.1、使用describe语句查看视图基本信息

describe可以用来查看视图，具体语法如下：

 describe 视图名

例1：通过describe语句查看视图view_t的定义，代码如下：

 describe view_t

代码执行如下：

  mysql> describe view_t;
  +------------------+------------+------+-----+---------+-------+
  | Field            | Type       | Null | Key | Default | Extra |
  +------------------+------------+------+-----+---------+-------+
  | quantity         | int(11)    | YES  |     | NULL    |       |
  | price            | int(11)    | YES  |     | NULL    |       |
  | quantity * price | bigint(21) | YES  |     | NULL    |       |
  +------------------+------------+------+-----+---------+-------+
  3 rows in set (0.24 sec)

结果显示出了视图的字段定义、字段的数据类型、是否为空、是否为主/外键、默认值和额外信息。

describe 一般情况下都简写成desc，输入这个命令的执行结果和输入describe的执行结果是一样的。

3.2、使用show table status语句查看视图基本信息

查看视图的信息可以通过show table status的语法，具体的语法如下：

 show table status like '视图名'；

例1：通过下面的例子来学习使用show table status 命令查看视图信息。

 show table status like 'view_t' \G

执行结果如下：

 mysql> show table status like 'view_t' \G
  *************************** 1. row ***************************
             Name: view_t
           Engine: NULL
          Version: NULL
       Row_format: NULL
             Rows: 0
   Avg_row_length: 0
      Data_length: 0
  Max_data_length: 0
     Index_length: 0
        Data_free: 0
   Auto_increment: NULL
      Create_time: 2019-06-16 21:06:38
      Update_time: NULL
       Check_time: NULL
        Collation: NULL
         Checksum: NULL
   Create_options: NULL
          Comment: VIEW
  1 row in set (0.00 sec)

执行结果显示，表的说明Comment的值为VIEW说明该表为视图，其他的信息为null，说明这是一个虚表。用同样的语句来查看一下数据表t的信息，执行结果如下：

  mysql> show table status like 't' \G
  *************************** 1. row ***************************
             Name: t
           Engine: InnoDB
          Version: 10
       Row_format: Dynamic
             Rows: 1
   Avg_row_length: 16384
      Data_length: 16384
  Max_data_length: 0
     Index_length: 0
        Data_free: 0
   Auto_increment: NULL
      Create_time: 2019-06-16 20:06:20
      Update_time: 2019-06-16 20:06:43
       Check_time: NULL
        Collation: utf8_general_ci
         Checksum: NULL
   Create_options:
          Comment:
  1 row in set (0.11 sec)

从查询的结果来看，这里的信息包含了存储引擎、创建时间等，Comment 信息为空，这就是视图和表的区别。

3.3、使用show create view 语句查看视图详细信息

使用show create view 语句可以查看视图的详细定义，语法如下：

 show create view 视图名

例1：show create view 查看视图的详细定义，代码如下：

show create view view_t \G

执行结果如下：

 mysql> show create view view_t \G
  *************************** 1. row ***************************
                  View: view_t
           Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t` AS select `t`.`quantity` AS `quantity`,`t`.`price` AS `price`,(`t`.`quantity` * `t`.`price`) AS `quantity * price` from `t`
  character_set_client: gbk
  collation_connection: gbk_chinese_ci
  1 row in set (0.00 sec)

执行结果显示视图的名称、创建视图的语句等信息。

3.4、在views表中查看视图详细信息

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

select * from information_schema.views\G

例1：在views表中查看视图的详细定义，代码如下：

  *************************** 101. row ***************************
         TABLE_CATALOG: def
          TABLE_SCHEMA: mysql_study
            TABLE_NAME: view_t
       VIEW_DEFINITION: select `mysql_study`.`t`.`quantity` AS `quantity`,`mysql_study`.`t`.`price` AS `price`,(`mysql_study`.`t`.`quantity` * `mysql_study`.`t`.`price`) AS `quantity * price` from `mysql_study`.`t`
          CHECK_OPTION: NONE
          IS_UPDATABLE: YES
               DEFINER: root@localhost
         SECURITY_TYPE: DEFINER
  CHARACTER_SET_CLIENT: gbk
  COLLATION_CONNECTION: gbk_chinese_ci
  *************************** 102. row ***************************
         TABLE_CATALOG: def
          TABLE_SCHEMA: mysql_study
            TABLE_NAME: view_t2
       VIEW_DEFINITION: select `mysql_study`.`t`.`quantity` AS `quantity`,`mysql_study`.`t`.`price` AS `price`,(`mysql_study`.`t`.`quantity` * `mysql_study`.`t`.`price`) AS `quantity * price` from `mysql_study`.`t`
          CHECK_OPTION: NONE
          IS_UPDATABLE: YES
               DEFINER: root@localhost
         SECURITY_TYPE: DEFINER
  CHARACTER_SET_CLIENT: gbk
  COLLATION_CONNECTION: gbk_chinese_ci
  *************************** 103. row ***************************
         TABLE_CATALOG: def
          TABLE_SCHEMA: mysql_study
            TABLE_NAME: stu_glass
       VIEW_DEFINITION: select `mysql_study`.`student`.`s_id` AS `s_id`,`mysql_study`.`student`.`name` AS `name`,`mysql_study`.`stu_info`.`glass` AS `glass` from `mysql_study`.`student` join `mysql_study`.`stu_info` where (`mysql_study`.`student`.`s_id` = `mysql_study`.`stu_info`.`s_id`)
          CHECK_OPTION: NONE
          IS_UPDATABLE: YES
               DEFINER: root@localhost
         SECURITY_TYPE: DEFINER
  CHARACTER_SET_CLIENT: gbk
  COLLATION_CONNECTION: gbk_chinese_ci
  103 rows in set (1.46 sec)

查询的结果显示当前以及定义的所有视图的详细信息。

4、修改视图

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。MYSQL中通过create or replace view 语句和alter语句来修改视图。

4.1、使用create or replace view 语句修改试图

MYSQL中如果要修改视图，使用create or replace view 语句，语法如下：

create [or replace] [algorithm = {undefined | merge | temptable}]
      view view_name [(column_list)]
      as select statement
      [with [cascaded | local] check option]

可以看到，修改视图的语句和创建视图的语句是完全一样的。当视图已经存在时，修改语句对视图进行修改；当视图不存在时，创建视图。

例1：修改视图view_t,代码如下：

 create or replace view view_t as select * from t;

首先通过desc查看一下更改之前的视图，以便与更改之后的视图进行对比。执行结果如下：

mysql> desc view_t;
  +------------------+------------+------+-----+---------+-------+
  | Field            | Type       | Null | Key | Default | Extra |
  +------------------+------------+------+-----+---------+-------+
  | quantity         | int(11)    | YES  |     | NULL    |       |
  | price            | int(11)    | YES  |     | NULL    |       |
  | quantity * price | bigint(21) | YES  |     | NULL    |       |
  +------------------+------------+------+-----+---------+-------+
  3 rows in set (0.07 sec)
  mysql> create or replace view view_t as select * from t;
  Query OK, 0 rows affected (0.42 sec)
  
  mysql> desc view_t;
  +----------+---------+------+-----+---------+-------+
  | Field    | Type    | Null | Key | Default | Extra |
  +----------+---------+------+-----+---------+-------+
  | quantity | int(11) | YES  |     | NULL    |       |
  | price    | int(11) | YES  |     | NULL    |       |
  +----------+---------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

从执行的结果来看。相比原来的视图view_t,新的视图view_t少了一个字段。

4.2、使用alter语句修改视图

alter语句是MYSQL提供的另外一种修改视图的方法，语法如下：

alter [algorithm = {undefined | merge | temptable}]
      view view_name [(column_list)]
      as select statement
      [wwith [cascaded | local] check option]

这个语法中的关键字和前面视图的关键字是一样的。

例1:使用alter语句修改视图view_t,代码如下:

alter view view_t as select quantity from t;

执行结果如下:


  mysql> desc view_t;
  +----------+---------+------+-----+---------+-------+
  | Field    | Type    | Null | Key | Default | Extra |
  +----------+---------+------+-----+---------+-------+
  | quantity | int(11) | YES  |     | NULL    |       |
  | price    | int(11) | YES  |     | NULL    |       |
  +----------+---------+------+-----+---------+-------+
  2 rows in set (0.07 sec)
  
  mysql> alter view view_t as select quantity from t;
  Query OK, 0 rows affected (0.21 sec)
  
  mysql> desc view_t;
  +----------+---------+------+-----+---------+-------+
  | Field    | Type    | Null | Key | Default | Extra |
  +----------+---------+------+-----+---------+-------+
  | quantity | int(11) | YES  |     | NULL    |       |
  +----------+---------+------+-----+---------+-------+
  1 row in set (0.01 sec)

通过alter语句同样可以达到修改视图view_t的目的,从上面的执行过程来看,视图view_t只剩下1个quantity字段,修改成功。

5、更新视图

更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

例1：使用update语句更新视图view_t,代码如下：

update view_t set quantity=5;

执行视图更新之前，查看基本表和视图的信息，执行结果如下：

mysql> select * from view_t;
  +----------+
  | quantity |
  +----------+
  |        3 |
  +----------+
  1 row in set (0.03 sec)
  
  mysql> select * from t;
  +----------+-------+
  | quantity | price |
  +----------+-------+
  |        3 |    50 |
  +----------+-------+
  1 row in set (0.00 sec)
  
  mysql> update view_t set quantity=5;
  Query OK, 1 row affected (0.14 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> select * from t;
  +----------+-------+
  | quantity | price |
  +----------+-------+
  |        5 |    50 |
  +----------+-------+
  1 row in set (0.00 sec)
  
  mysql> select * from view_t;
  +----------+
  | quantity |
  +----------+
  |        5 |
  +----------+
  1 row in set (0.00 sec)
  mysql> select * from view_t2;
  +------+-------+-------+
  | qty  | price | total |
  +------+-------+-------+
  |    5 |    50 |   250 |
  +------+-------+-------+
  1 row in set (0.07 sec)

对视图view_t更新后，基本表t的内容也更新了，同样当对基本表t更新后，另外一个视图view_t2中的内容也会更新。

例2：使用insert语句在基本表t中插入一条记录，代码如下：

  insert into t values(3,5);

执行结果如下：

mysql> insert into t values(3,5);
  Query OK, 1 row affected (0.05 sec)
  
  mysql> select * from t;
  +----------+-------+
  | quantity | price |
  +----------+-------+
  |        5 |    50 |
  |        3 |     5 |
  +----------+-------+
  2 rows in set (0.00 sec)
  
  mysql> select * from view_t2;
  +------+-------+-------+
  | qty  | price | total |
  +------+-------+-------+
  |    5 |    50 |   250 |
  |    3 |     5 |    15 |
  +------+-------+-------+
  2 rows in set (0.00 sec)
  
  mysql> select * from view_t;
  +----------+
  | quantity |
  +----------+
  |        5 |
  |        3 |
  +----------+
  2 rows in set (0.00 sec)

向表t中插入一条记录，通过select 查看表t和视图view_t2,可以看到其中的内容也随之更新，视图更新的不仅仅是数量和单价，总价也会更新。

例3：使用delete语句删除视图view_t2中的一条记录，代码如下：

mysql> delete from view_t2 where price=5;
  Query OK, 1 row affected (0.10 sec)
  
  mysql> select * from view_t2;
  +------+-------+-------+
  | qty  | price | total |
  +------+-------+-------+
  |    5 |    50 |   250 |
  +------+-------+-------+
  1 row in set (0.00 sec)
  
  mysql> select * from view_t;
  +----------+
  | quantity |
  +----------+
  |        5 |
  +----------+
  1 row in set (0.00 sec)
  
  mysql> select * from t;
  +----------+-------+
  | quantity | price |
  +----------+-------+
  |        5 |    50 |
  +----------+-------+
  1 row in set (0.00 sec)

在视图view_t2中删除price=5的记录，视图中的删除操作最终是通过删除基本表中相关的记录实现的，查看删除操作之后的表t和视图view_t2，可以看到通过视图删除其所依赖的基本表的数据。

当视图中包含有如下内容时，视图的更新操作将不能被执行：

视图中不包含基表中被定义为非空的列。
在定义视图的select语句后的字段列表中使用了数学表达式。
在定义视图的select语句后的字段列表中使用了聚合函数。
在定义视图的select语句中使用了distinct，union，top，group by或having子句。

6、删除视图

当视图不再需要时，可以将其删除，删除一个或多个视图可以使用drop view 语句，语法如下：

  drop view [if exists]
      view_name [, view_name] ...
      [restrict | cascade]

其中，view_name是要删除的视图名称，可以添加多个需要删除的视图名称，各个名称之间用逗号分隔开。删除视图必须拥有drop权限。

例1：删除stu_glass视图，代码如下：

drop view if exists stu_glass;

执行结果：

mysql> show create view stu_glass;
  +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  | View      | Create View                                                                                                                                                                                                                                                                                   | character_set_client | collation_connection |
  +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  | stu_glass | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_glass` (`id`,`name`,`glass`) AS select `student`.`s_id` AS `s_id`,`student`.`name` AS `name`,`stu_info`.`glass` AS `glass` from (`student` join `stu_info`) where (`student`.`s_id` = `stu_info`.`s_id`) | gbk                  | gbk_chinese_ci       |
  +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  1 row in set (0.01 sec)
  
  mysql> drop view if exists stu_glass;
  Query OK, 0 rows affected (0.05 sec)
  
  mysql> show create view stu_glass;
  ERROR 1146 (42S02): Table 'mysql_study.stu_glass' doesn't exist

可以看出，stu_glass视图已经不存在，删除成功。

7、答疑解惑

疑问：MySQL中视图和表的区别以及联系是什么？

1.两者的区别：

视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。
视图没有实际的物理记录，而表有。
表是内容，视图是窗口。
表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改。. 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。
表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
视图的建立和删除只影响视图本身，不影响对应的基本表。

2.两者的联系：

视图是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有记录）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

塞北的天堂

MySQL之视图学习

MYSQL---视图

1、概述:

1.1、视图的含义

1.2、视图的作用

2、创建视图

2.1、创建视图的语法形式

2.2、在单表创建视图

2.3、在多表上创建视图

3、查看视图

3.1、使用describe语句查看视图基本信息

3.2、使用show table status语句查看视图基本信息

3.3、使用show create view 语句查看视图详细信息

3.4、在views表中查看视图详细信息

4、修改视图

4.1、使用create or replace view 语句修改试图

4.2、使用alter语句修改视图

5、更新视图

6、删除视图

7、答疑解惑