MySQL视图的使用

本文详细介绍了MySQL视图的概念、创建规则、使用场景,包括重用SQL、数据格式转换、权限控制和不同算法的运用。讲解了视图的可更新性及其限制,以及如何查看和管理视图的创建信息。
摘要由CSDN通过智能技术生成

什么是视图

视图是一个虚拟表,不存放任何数据。在使用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如果使用了该选项,则在对视图执行INSERTUPDATE操作时,会检查新行或更新的行是否符合视图的定义,并阻止不符合条件的数据被插入或更新。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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值