常见mysql视图_MySql 视图

一、MySql视图概述

1、什么是视图？

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

2、为什么要使用视图？

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

3、视图有哪些特点？

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

视图是由基本表(实表)产生的表(虚表)。

视图的建立和删除不影响基本表。

对视图内容的更新(添加，删除和修改)直接影响基本表。

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

4、视图的使用场景有哪些？

视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。下面是视图的常见使用场景：

重用SQL语句；

简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；

使用表的组成部分而不是整个表；

保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；

更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

5、视图的优点

视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时，视图具有如下优点：

定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

提高数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

6、视图的缺点

性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。

修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

二、创建视图

1、基本语法

可以使用 CREATE VIEW 语句来创建视图。语法格式如下：

CREATE VIEW AS

语法说明如下：

：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

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

用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。

SELECT 语句不能引用系统或用户变量。

SELECT 语句不能包含 FROM 子句中的子查询。

SELECT 语句不能引用预处理语句参数。

注意事项：

视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。

视图定义中允许使用 ORDER BY 语句，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

视图定义中不能引用 TEMPORARY 表(临时表)，不能创建 TEMPORARY 视图。

WITH CHECK OPTION 的意思是，修改视图时，检查插入的数据是否符合 WHERE 设置的条件。

2、创建基于单表的视图

查看下表信息：

mysql> SELECT *FROM user_info;+----+------+-----+

| id | name | age |

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

| 1 | xys | 20 |

| 2 | a | 21 |

| 3 | b | 23 |

| 4 | c | 50 |

| 5 | d | 15 |

| 6 | e | 20 |

| 7 | f | 21 |

| 8 | g | 23 |

| 9 | h | 50 |

| 10 | i | 15 |

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

10 rows in set

在user_info表上创建view_user_info视图：

mysql> create view view_user_info as select *from user_info;

mysql> select *fromview_user_info;+----+------+-----+

| id | name | age |

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

| 1 | xys | 20 |

| 2 | a | 21 |

| 3 | b | 23 |

| 4 | c | 50 |

| 5 | d | 15 |

| 6 | e | 20 |

| 7 | f | 21 |

| 8 | g | 23 |

| 9 | h | 50 |

| 10 | i | 15 |

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

10 rows in set

3、创建基于多表的视图

在表 user_info和表 order_info上创建视图 view_user_order_info

mysql>CREATE VIEW view_user_order_info(name,product_name)-> as

-> select u.name,o.product_name from user_info u,order_info o where u.id=o.user_id;

Query OK,0 rows affected

mysql> select *fromview_user_order_info;+------+--------------+

| name | product_name |

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

| xys | p1 |

| xys | p1 |

| xys | p2 |

| a | p1 |

| a | p5 |

| b | p3 |

| c | p1 |

| e | p1 |

| h | p8 |

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

9 rows in set

三、查看视图

1、查看视图的字段信息

查看视图的字段信息与查看数据表的字段信息一样，都是使用 DESCRIBE 关键字来查看的。具体语法如下：

DESCRIBE 视图名;

或简写成：

DESC 视图名;

mysql>desc view_user_order_info;+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| name | varchar(50) | NO | | | |

| product_name | varchar(50) | NO | | | |

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

2 rows in set

2、查看视图的详细信息

在 MySQL 中，SHOW CREATE VIEW 语句可以查看视图的详细定义。其语法如下所示：

SHOW CREATE VIEW 视图名;

mysql>SHOW CREATE VIEW view_user_order_info;+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

| View | Create View | character_set_client | collation_connection |

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

| view_user_order_info | CREATE ALGORITHM=UNDEFINED DEFINER=`devtest`@`%` SQL SECURITY DEFINER VIEW `view_user_order_info` (`name`,`product_name`) AS select `u`.`name` AS `name`,`o`.`product_name` AS `product_name` from (`user_info` `u` join `order_info` `o`) where (`u`.`id` = `o`.`user_id`) | utf8 | utf8_general_ci |

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

1 row in set

3、拓展

所有视图的定义都是存储在 information_schema 数据库下的 views 表中，也可以在这个表中查看所有视图的详细信息，SQL 语句如下：

SELECT * FROM information_schema.views;

不过，通常情况下都是使用 SHOW CREATE VIEW 语句。

四、修改视图

修改视图是指修改 MySQL 数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致性。

1、基本语法

可以使用 ALTER VIEW 语句来对已有的视图进行修改。语法格式如下：

ALTER VIEW AS

语法说明如下：

：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

需要注意的是，对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。

修改视图的定义，除了可以通过 ALTER VIEW 外，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

2、修改视图内容

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。

注意：对视图的修改就是对基本表的修改，因此在修改时，要满足基本表的数据定义。

某些视图是可更新的。也就是说，可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

DISTINCT 关键字。

GROUP BY 子句。

HAVING 子句。

UNION 或 UNION ALL 运算符。

位于选择列表中的子查询。

FROM 子句中的不可更新视图或包含多个表。

WHERE 子句中的子查询，引用 FROM 子句中的表。

ALGORITHM 选项为 TEMPTABLE(使用临时表总会使视图成为不可更新的)的时候。

(1)使用 ALTER 语句修改视图view_user_info ，输入的 SQL 语句和执行结果如下所示。

mysql>ALTER VIEW view_user_info AS SELECT id,name,age FROM user_info;

Query OK,0 rows affected

mysql>desc view_user_info;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| id | bigint(20) | NO | | 0 | |

| name | varchar(50) | NO | | | |

| age | int(11) | YES | | NULL | |

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

3 rows in set

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

(2)使用 UPDATE 语句更新视图 view_user_info ，输入的 SQL 语句和执行结果如下所示。

mysql>update view_user_info-> set name='qxh' where id=1;

Query OK,1row affected

Rows matched:1 Changed: 1 Warnings: 0

mysql> select *from view_user_info where id=1;+----+------+-----+

| id | name | age |

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

| 1 | qxh | 20 |

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

1 row in set

3、修改视图名称

修改视图的名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称。

五、删除视图

删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。

基本语法

可以使用 DROP VIEW 语句来删除视图。语法格式如下：

DROP VIEW [ , …]

其中：指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限。

mysql>DROP VIEW IF EXISTS view_user_info,view_user_order_info;

Query OK,0 rows affected

SHOW CREATE VIEW view_user_info;1146 - Table 'qxhfx.view_user_info' doesn't exist

可以看到，view_user_info视图已不存在，将其成功删除