MySQL数据库操作补坑(五)视图

谁借莪１个温暖的怀抱￠ 2022-12-29 04:51 258阅读 0赞

# 视图简介 #

是有数据库中的一个表或多个表导出的虚拟表。作用是方便用户对数据的操作。

## 1）、视图的含义 ##

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

> 如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图。通过这个视图显示这些字段的数据。如果表中修改了与市图相关的字段的名称，可以通过修改试图爱解决可能引起的问题。

MySQL视图不支持输入参数的功能，因此交互性上还有欠缺。但对于变化大的操作，使用视图能很大程度上简化使用户操作。

## 2）、视图的作用 ##

是在原有表或者视图的基础上定义的一个虚拟表，对数据起筛选过滤的作用。

**作用**：

1.  使操作简单化
    
    视图需要达到的目的就是所见即所需。可以简化对数据的操作：可以对经常使用的查询定义一个视图，使用用户不必为同样的查询操作指定条件，方便用户操作
2.  增加数据安全性
    
    通过视图，用户只能查询和修改制定的数据。
    
    数据库授权命令可以限制用户的操作权限，但不能限制到特定行和列上。视图可以将用户的权限限制到特定的行和列上，可以保证一些机密信息的安全。
3.  提高表的逻辑独立性
    
    视图可以屏蔽原有表结构变化带来的影响，例如：原有表增加列和删除未被引用的列，对视图不会造成影响。同样，如果修改了表中的某些列，可以使用修改视图来解决这些列带来的影响。

# 创建视图 #

## 1）、创建视图的语法形式 ##

*  语法：
    
        CREATE [ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }]
        		 VIEW 视图名 [( 属性清单 )]
        		 AS SELECT 语句
        		 [ WITH [CASCADED | LOCAL] CHECK OPTION ];
    
     *  ALGORITHM：可选，表示视图选择的算法;包括三个选项：`UNDEFINED / MERGE / TEMPTABLE`
        
         *  UNDEFINED：MySQL将自动选择所要使用的算法；
         *  MERGE：将使用视图的语句与视图定义合并起来，使得视图按照定义的某一部分取代语句的对应部分；
         *  TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句
     *  “属性清单”：可选，指定了视图中各个属性的名词，默认与SELECT语句中的查询的属性相同；
     *  SELECT语句参数是一个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入到视图中；
     *  WITH CHECK OPTION：可选，表示更新视图时要保证在该视图的权限范围之内。
     *  CASCADED：可选，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；LOCAL表示更新时图时，要满足试图本身的定义的条件即可
    
    > 使用CREATE VIEW时最好加上WITH CHECK OPTION 参数。最好加上CASCADED参数。这样，从视图上派生出新视图后，更新新视图需要考虑其父视图的约束条件，这种比较严格的方式可以保证数据的安全性。
    
    创建视图时，需要有CREATE VIEW 的权限。同时应具有查询涉及的列的SELECT权限。在MySQL数据库下面的user表中保存这些权限信息，可以使用SELECT语句查询，查询方式如下：
    
    `SELECT Select_priv, Create_view_prive FROM mysql.user WHERE user='用户名'`
    
     *  Select\_priv：用户是否有SELECT权限，Y表示拥有SELECT权限，N表示没有。
     *  Create\_view\_prive：表示用户是否具有CREATE VIEW权限；
     *  mysql.user：表示mysql数据库下的user表
     *  用户名：表示要查询那个用户是否拥有Drop权限，该参数需要单引号引起来。

## 2）、在单表上创建视图 ##

MySQL中可以在单个表上创建视图。

CREATE VIEW 
    		department_view2 ( name, function, location )
    		AS SELECT d_name,function,address
    		 FROM department;

## 3）、在多表上创建视图 ##

CREATE ALGORITHM=MERGE VIEW
    		worker_view1 (name,department,sex,2009-birthday,address)
    		AS SELECT name,department.d_name,sex,2009-birthday,address
    			FROM worker,department WHERE worker.d_id=department_id
    			WITH LOCAL CHECK OPTION;

视图的属性名与属性列表中的属性名相同。

# 查看视图 #

数据库已存在视图的定义

有SHOW VIEW权限

MySQL数据库下的user表中保存着这个信息

## 1）、DESCRIBE 语句查看视图基本信息 ##

语法：`DESCRIBE 视图名;`

DESCRIBE可以缩写为DESC，可以使用DESC直接查看表的结构

## 2）、SHOW TABLE STATUS 语句查看视图基本信息 ##

语法：`SHOW TABLE STATUS LIKE '视图名'`

Like表示后面匹配的是字符串。视图名需要用单引号引起来。

**执行结果显示**

*  表的说明项的值为VIEW，说明该表为视图。
 *  存储引擎数据长度等信息显示为NULL，说明视图为虚拟表

## 3）、SHOW CREATE VIEW 语句查看视图详细信息 ##

语法：`SHOW CREATE VIEW 视图名`

## 4）、在views表中查看视图的详细信息 ##

在MySQL中，所有视图的定义都存在`information_schema.views`数据库的views表中。查询views表，可以查看到数据库中所有视图的详细信息。

语法：`SELECT * FROM information_schema.views;`

# 修改视图 #

## 1）、CREATE OR REPLACE VIEW 修改视图 ##

语法：

CREATE OR REPLACE [ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE}]
    			VIEW 视图名 [(属性清单)]
    			AS SELECT 语句
    			[WITH [CASCADED | LOCAL] CHECK OPTION];

该语句不仅可以修改已经存在的视图，也可以创建新的视图。

## 2）、ALERT 语句修改视图 ##

语法：

ALERT [ ALGORITHM = { UNIFINED | MERGE | TEMPTABLE } ]
    		VIEW 视图名 [( 属性清单 )]
    		AS SELECT 语句
    		[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

# 更新视图 #

指通过视图来插入，更新和删除表中的数据。

通过UPDATE更新视图时，实际上更新的是基本表

以下情况补不能更新视图：

*  视图中包含SUM()、COUNT()、MAX()、MIN()等函数
    
     *  `CREATE VIEW worker_view4( name,sex,total) AS SELECT name,sex,COUNT(name)FROM worker;`
 *  视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等关键字。
    
     *  `CREATE VIEW worker_view4( name,sex,total) AS SELECT name,sex,homeaddress FROM worker GROUP BY d_id;`
 *  常量视图（'Aric’为常量，使用UPDATE语句更新时会报错）
    
     *  `CREATE VIEW worker_view6 AS SELECT 'Aric' as name;`
 *  视图中的SELECT中包含子查询
    
     *  `CREATE VIEW worker_views7(name) AS SELECT ( SELECT name FROM worker);`
 *  由不可更新的视图导出的视图
    
     *  `CREATE VIEW worker_view8 AS SELECT * FROM worker_view7;`
 *  创建视图时，ALGORITHM为TEMPTABLE类型
    
     *  `CREATE ALGORITHM = TEMPTABLE VIEW AS SELECT * FROM worker;`
 *  视图对应的表上存在没有默认值的列，而且该列没有包含在视图里。

> 视图中虽然可以更新数据，但有很多限制，一般将视图作为查询数据的虚拟表，而不通过视图去更新数据。

# 删除视图 #

删除视图时，只能删除视图的定义，不会删除数据。

语法：`DROP VIEW [IF EXISTS] 视图列表名称 [RESTRICT | CASCADE]`