MySQL视图的使用

什么是视图

视图是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是 MySQL 从表中生成的。视图和表是在同一个命名空间，MySOL 在很多地方对于视图和表是同样对待的。不过视图和表也有不同，例如，不能对视图创建触发器，也不能使用 DROP TABLE命令删除视图。

视图能做什么

1. 重用SQL语句。
2. 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
3. 使用表的组成部分而不是整个表。
4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图
 

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW 视图名 [(column_list)]
    AS 查询语句
    [WITH [CASCADED | LOCAL] CHECK OPTION]

1. 如果给定了OR REPLACE子句，该语句会替换掉已有的视图
2. ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。默认使用UNDEFINED，ALGORITHM会影响MySQL处理视图的方式。

1. [DEFINER = user]: 可选项，指定视图的创建者。
2. [SQL SECURITY { DEFINER | INVOKER }] ：可选项，表示该用户使用该视图时使用的角色权限，如果设置为DEFINER,则表示使用该视图的用户必须是创建视图的用户指定的用户，并且这个指定的用户还要具有相关的权限才能使用；如果设置为INVOKER则只要用户有相关的权限就能使用。
3. WITH CHECK [LOCAL] OPTION如果使用了该选项，则在对视图执行INSERT或UPDATE操作时，会检查新行或更新的行是否符合视图的定义，并阻止不符合条件的数据被插入或更新。LOCAL可加可不加
4. WITH CASCADED CHECK OPTION：如果使用了该选项，则除了执行WITH CHECK OPTION的检查外，还会对基础表也进行相同的检查，确保视图和基础表的数据一致性。

示例程序使用的数据库为官方sakila数据库

示例程序：

CREATE 
	OR REPLACE VIEW pg_rating AS SELECT
	film_id,
	title,
	rating 
FROM
	`film` 
WHERE
	rating = "PG" WITH CHECK OPTION

创建视图的规则

1. 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
2. 对于可以创建的视图数目没有限制。
3. 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。CREATE VIEW 语句要求 用户需要有 CREATE VIEW 权限，以及 SELECT 语句选择的每列的一些特权。 如果存在OR PEPLACE，则必须 还具有 DROP 权限 风景。

 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造

一个视图。

 ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也

含有ORDER BY，那么该视图中的ORDER BY将被覆盖。

 视图不能索引，也不能有关联的触发器或默认值。

 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT

语句。

使用视图

一般来说直接将视图作为一张表来使用即可

SELECT * FROM pg_rating WHERE film_id <50

视图的使用场景

1. 使用视图隐藏复杂的sql查询

复杂查询(一般这种查询都包含了多表的连接)，

考虑到这里有一个需求，我们要根据演员的姓名来查询这个演员所参演过的电影，如果直接使用原始查询。可以使用下面的查询语句

SELECT a.first_name,a.last_name,f.title FROM actor a
INNER JOIN film_actor fa
ON a.actor_id=fa.actor_id
INNER JOIN film f
ON fa.film_id=f.film_id
WHERE a.first_name="ADAM" AND a.last_name="HOPPER"

如果使用视图，可以是这样的形式：

1. 创建视图

CREATE VIEW actor_participated_film AS
SELECT a.first_name,a.last_name,f.title FROM actor a
INNER JOIN film_actor fa
ON a.actor_id=fa.actor_id
INNER JOIN film f
ON fa.film_id=f.film_id

1. 直接使用视图来查询

SELECT * FROM actor_participated_film 
WHERE first_name="ADAM" AND last_name="HOPPER"

这里注意：我们直接使用first_name和last_name即可，不需要使用创建视图时我们为联接表创建的别名，可以把视图当作一张表，这样我们在使用视图来做查询时，直接使用视图上的列命名即可，不再需要考虑视图的构造细节

可以看出使用视图的查询逻辑上确实更加清晰

2. 用视图重新格式化表的数据

有时我们希望能修改表的显示和查询方式，但是又不希望直接修改表的结构，就可以在视图中修改表的格式。

还是上面的查询，我们认为需要分开来查询并显示first_name,last_name太过麻烦，我们希望能直接显示全名，并且在查询时能使用全名来查询：

所以我们可以使用这样的查询

CREATE VIEW actor_participated_film_name AS
SELECT CONCAT(a.first_name," ",a.last_name) AS name,f.title 
FROM actor a
INNER JOIN film_actor fa
ON a.actor_id=fa.actor_id
INNER JOIN film f
ON fa.film_id=f.film_id

接下来我们直接使用下面这种查询即可

SELECT * 
FROM actor_participated_film_name 
WHERE name="ADAM HOPPER"

3. 在视图中过滤掉不需要的数据

有时我们想要先从表中过滤掉不需要的数据，然后再进行查询。

考虑下面的应用场景：我们需要film中选择时长大于90分钟的电影作为一个分类，然后再从其中做查询：

SELECT * FROM film WHERE length<90

4. 使用视图重新计算字段

直接查询表的数据不符合我们的要求时，就可以使用视图来先修改表的显示数据，然后再做处理。

例如：我们需要将账单中的支付数额从美元转为人民币（当前汇率为1 美元 ≈ 7.1304 人民币），就可以使用视图来查询：

CREATE VIEW dollar_payment AS
SELECT
	amount * 7.1304 AS dollar_amount ,
	payment_date 
FROM
	`payment`

然后我们可以再在该视图的基础上，将美元转为英镑（1 英镑 ≈ 1.2686 美元）来显示

SELECT
	dollar_amount * 1.2686 AS pound_amount ,
	payment_date
FROM
	dollar_payment

详解MERGE、TEMPTABLE两种视图算法对视图的影响

如果把视图想象为一张临时表，那么可以创建下面这种"伪视图"：

CREATE TEMPORARY TABLE tmp_pg_rating AS 
SELECT film_id,title,rating FROM film WHERE rating="PG";

SELECT * FROM tmp_pg_rating WHERE film_id<50;

这种方式也能完成查询

如果把使用视图的查询条件合并到创建视图的查询语句中，也可以完成查询：

SELECT film_id,title,rating FROM film WHERE rating="PG" AND film_id<50;

视图的两种实现方式MERGE、TEMPTABLE就是基于上面这两种形式来完成的，一般来说我们任务MERGE的性能会更高,mysql也会优先使用MERGE算法。当视图中包含GROUY BY、DISTINCT、任何聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，MySQL 都将使用临时表算法来实现视图

如何判断mysql执行查询时会使用哪种算法

EXPLAIN SELECT * FROM 视图名

如果返回的结果select_type为DERVIDE,则说明使用了临时表算法

修改视图

通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其使用的表（视图本身没有数据）。如果你对视图增加或删除行，实际上是对表增加或删除行

UPDATE pg_rating SET title="hello world" WHERE title="ACADEMY DINOSAUR"
> Affected rows: 1

不能修改视图的情况

1. 如果视图定义中有以下操作，则不能进行视图的更新：

1. 分组（使用GROUP BY和HAVING）；
2. 联结；
3. 子查询；
4. 并；
5. 聚集函数（Min()、Count()、Sum()等）；
6. DISTINCT；
7. 导出（计算）列。

2. 创建视图时使用了CHECK OPTION 的视图只能修改视图中的列，不包括WHERE中使用的列

UPDATE pg_rating SET length=50
> 1054 - Unknown column 'length' in 'field list'

UPDATE pg_rating SET rating="PG-13" WHERE title="hello world"
> 1369 - CHECK OPTION failed 'sakila.pg_rating'

基于这样的形式，我们可以通过给用户设置基于列的操作权限

GRANT [SELECT/INSERT/UPDATE/DELETE] ON 视图名 WITH 用户

删除视图

DROP 视图名

查询视图

1. 查询视图构造语句

SHOW CREATE VIEW 视图名

示例程序

SHOW CREATE VIEW actor_info

1. 查询表上的视图

SELECT TABLE_NAME
FROM information_schema.views
WHERE table_schema = '数据库名';

示例程序

SELECT TABLE_NAME
FROM information_schema.views
WHERE table_schema = 'sakila';

PDF版本

可以下载pdf来查看本文档：

链接：百度网盘 请输入提取码

提取码：a1a5

示例数据库

链接：百度网盘 请输入提取码

提取码：a1a5