U1S1--视图

最新推荐文章于 2025-03-28 16:55:33 发布

郭烊千玺

最新推荐文章于 2025-03-28 16:55:33 发布

阅读量274

点赞数

分类专栏： mysql视图等

本文链接：https://blog.csdn.net/weixin_42851042/article/details/118522156

版权

mysql视图等专栏收录该内容

1 篇文章

订阅专栏

视图知识

1定义

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

总结：视图是存放一个从已存在表中执行一个sql的结果集的虚拟表

2 语法

CREATE 【sql security ={ invoker|define r}】

[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]

VIEW [database_name].[view_name]

AS[SELECT statement]

sql security ：definer和invoker的区别

在创建视图是需要定义安全验证方式的(也就是安全性SQL SECURITY)，其值可以为definer或invoker，表示在执行过程中，使用谁的权限来执行。

definer:由definer(定义者)指定的用户的权限来执行

invoker:由调用这个视图(存储过程)的用户的权限来执行

ALGORITHM：算法属性，

允许我们控制mysql在创建视图时使用的机制，并且mysql提供了三种算法：

MERGE，TEMPTABLE和UNDEFINED。我们来分别看下：

MERGE算法

mysql首先将输入查询与定义视图的select语句组合成单个查询。然后mysql执行组合查询返回结果集。如果select语句包含集合函数(如min，max，sum，count，avg等)或distinct，group by，havaing，limit，union，union all，子查询，则不允许使用MERGE算法。如果select语句无引用表，则也不允许使用MERGE算法。如果不允许MERGE算法，mysql将算法更改为UNDEFINED。我们要注意，将视图定义中的输入查询和查询组合成一个查询称为视图分辨率。

TEMPTABLE算法

mysql首先根据定义视图的SELECT语句创建一个临时表，然后针对该临时表执行输入查询。因为mysql必须创建临时表来存储结果集并将数据从基表移动到临时表，所以TEMPTABLE算法的效率比MERGE算法效率低。另外，使用TEMPTABLE算法的视图是不可更新的

当我们创建视图而不指定显式算法时，UNDEFINED是默认算法。

UNDEFINED算法使mysql可以选择使用MERGE或TEMPTABLE算法。mysql优先使用MERGE算法进行TEMPTABLE算法，因为MERGE算法效率更高。

View

它就是名称的意思，在数据库中，视图和表共享相同的命名空间，因此视图和表不能具有相同的名称。 另外，视图的名称必须遵循表的命名规则。

SELECT语句。

在SELECT语句中，可以从数据库中存在的任何表或视图查询数据，同时SELECT语句必须遵循以下几个规则：

SELECT语句可以在where 语句中包含子查询，但FROM子句中的不能包含子查询。

SELECT语句不能引用任何变量，包括局部变量，用户变量和会话变量。

SELECT语句不能引用准备语句的参数。

3 mysql 视图相关操作

操作指令	代码
创建视图	CREATE VIEW 视图名(列1，列2…) AS SELECT (列1，列2…) FROM …;
使用视图	和普通表一样操作
修改视图	CREATE OR REPLACE VIEW 视图名 AS SELECT […] FROM […];
删除视图	DROP VIEW 视图名
查看数据库已有视图	SHOW TABLES [like…];（可以使用模糊查找）
查看视图详情	DESC 视图名或者SHOW FIELDS FROM 视图名
视图条件限制	[WITH CHECK OPTION]

4 应用场景

安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂，固化到视图，就易理解。

5 视图操作特点

一般只用于查，但也支持写。
修改视图数据后，源表一起跟着修改，修改源表，视图也跟着修改。

6视图权限

6.1 show视图权限

需show view 和select权限一起授权，没有select会报如下错误：

6.2 carete视图权限

需create view 和select权限一起授权，没有select会报如下错误：

6.3其他视图权限

和其他表一样相应加上对应权限即可，如，delete；

6.4权限最常遇到不可访问场景。

1创建表
CREATE TABLE t (qty INT, price INT);INSERT INTO t VALUES(3, 50), (5, 60);

2创建表t的一个sql视图，不指定安全模式，默认则为definer

CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

3创建表t的另一个sql视图，安全模式为invoker

create sql security invoker view u AS SELECT qty, price, qty*price AS value FROM t;

4查看下创建后的结构可以看到安全模式的区别：

5下面我们验证下这两种模式区别对用户权限的影响：

当definer的用户 testview@xx.xx.xx.8正常的时候，我们用一个testview 用户登录验证下对两个视图的访问权限：

6 当我们删掉definer的用户 testview对应xx.xx.xx.8的账号，我们用一个testview 1 用户登录验证下对两个视图的访问权限：

可以看到安全模式的definer模式的视图v已经不能访问了，所以验证了当视图的安全性为DEFINER时，数据库中存在DEFINER指定的用户，也就是图中的定义者所填写的。并且该用户拥有对应的权限，才能执行。与当前用户是否有权限无关。当视图的安全性为INVOKER时，只要执行者有执行权限，就可以成功执行。