码上言全栈学习

# 1、什么是视图？

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典 (opens new window)中的。视图只包含使用时动态检索数据的查询。

例子：

朕想要了解皇宫的国库的相关情况，想知道酒窖有什么酒，剩多少，窖藏多少年，于是派最信任的高公公去清点，高公公去国库清点后报给了朕；朕又想知道藏书情况，于是又派高公公去清点并回来报告给朕，又想知道金银珠宝如何，又派高公公清点。。。过一段时间又想知道藏书情况，高公公还得重新再去清点，皇上问一次，高公公就得跑一次路。

后来皇上觉得高公公不容易，就成立了国库管理部门，小邓子负责酒窖，小卓子负责藏书，而小六子负责金库的清点。。。后来皇上每次想了解国库就直接问话负责人，负责人就按照职责要求进行汇报。

安排专人管理后，每次皇上想要了解国库情况，就不必让高公公每次都跑一趟，而是指定的人员按照指定的任务完成指定的汇报工作就可以了。

和数据库相对应，每次进行查询工作，都需要编写查询代码进行查询；而视图的作用就是不必每次都重新编写查询的SQL代码，而是通过视图直接查询即可。因此：

视图是虚拟表，本身不存储数据，而是按照指定的方式进行查询。

# 2、作用

关系型数据库 (opens new window)中的数据是由一张一张的二维关系表所组成，简单的单表查询只需要遍历一个表，而复杂的多表查询需要将多个表连接起来进行查询任务。对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图（view）功能。

  • 通过视图，可以展现基表的部分数据；
  • 视图数据来自定义视图的查询中使用的表，使用视图动态生成。
  • 基表：用来创建视图的表叫做基表

# 3、为什么要使用视图？

因为视图的诸多优点，如下

1）**简单：**使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

2）**安全：**使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

3）**数据独立：**一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视 图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚 至能添加和更新数据。

# 4、视图的规则和限制

  • 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相 同的名字）。
  • 对于可以创建的视图数目没有限制。
  • 为了创建视图，必须具有足够的访问权限。这些限制通常由数据 库管理人员授予。
  • 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造 一个视图。
  • ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也 含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
  • 视图不能索引，也不能有关联的触发器或默认值。
  • 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT 语句。

# 5、视图相关的MySQL指令

操作指令 代码
创建视图 CREATE VIEW 视图名(列1，列2...) AS SELECT (列1，列2...) FROM ...;
使用视图 当成表使用就好
修改视图 CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
查看数据库已有视图 >SHOW TABLES [like...];（可以使用模糊查找）
查看视图详情 DESC 视图名或者SHOW FIELDS FROM 视图名
视图条件限制 [WITH CHECK OPTION]
  • 视图用CREATE VIEW语句来创建。
  • 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
  • 用DROP删除视图，其语法为DROP VIEW viewname。
  • 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创 建一个视图；如果要更新的视图存在，则第2条更新语句会替换原 有视图。

# 6、MySQL中的视图操作

为了测试，我们先建两张表用于测试。

DROP TABLE IF EXISTS `author`;
CREATE TABLE `author`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `author_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `author` VALUES (1, '小花');
INSERT INTO `author` VALUES (2, '小明');
INSERT INTO `author` VALUES (3, '小兰');
1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `author_id` int(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk-author`(`author_id`) USING BTREE,
  CONSTRAINT `fk-author` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `blog` VALUES (1, '测试1', '测试内容', 1);
INSERT INTO `blog` VALUES (2, '测试2', '测试内容', 2);
1
2
3
4
5
6
7
8
9
10
11
12
13

# 6.1、创建视图

创建视图的SQL语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW view_name (列1，列2...)
AS SELECT (列1，列2...)
[WITH [CASCADED | LOCAL] CHECK OPTION]
1
2
3
4

OR REPLACE：表示在创建视图时候会替换已有视图 ALGORITHM：表示视图选择算法,将在文章的后面详细讲解 SELECT：表示select语句 [WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内，详情将在后面讲解 注意：推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项，可以保证数据的安全性，所以建议加上它。

可以看到，创建视图和查询相比，增加了前面的CREATE VIEW 视图名 AS

# 6.2、创建单表视图

create view v_author(编号,姓名)
as 
SELECT * from author
with check option;
1
2
3
4

执行结果如下图所示

create view v_author(编号,姓名)
as 
SELECT * from author
with check option
OK
时间: 0.011s
1
2
3
4
5
6

使用desc v_author命令查看视图信息，执行结果如下图所示

​ 20220926152958

然后执行select * from v_author查看视图里面显示的数据，执行结果如下图所示

20220926153028

# 6.3、创建多表视图

执行下面的SQL语句创建一个多表视图

CREATE view v_blog(编号,标题,内容,作者)
as
SELECT b.id, b.title, b.content, a.author_name from author a, blog b
where a.id = b.author_id
with check option;
1
2
3
4
5
6

然后执行select * from v_blog查看多表视图中的数据，下图是执行结果

20220926152919

视图将我们不需要的数据过滤掉，将相关的列名用我们自定义的列名替换。视图作为一个访问接口，不管基表的表结构和表名有多复杂。 如果创建视图时不明确指定视图的列名，那么列名就和定义视图的select子句中的列名完全相同； 如果显式的指定视图的列名就按照指定的列名。 注意：显示指定视图列名，要求视图名后面的列的数量必须匹配select子句中的列的数量。

# 6.4、查看视图

使用show create view语句查看视图信息，比如

show create view v_blog;
1

20220926153343

视图一旦创建完毕，就可以像一个普通表那样使用，视图主要用来查询，比如 select * from v_blog where 编号=1;，执行结果如下图

20220926153539

有关视图的信息记录在information_schema数据库中的views表中，我们可以通过SQL语句来查看，比如

select * from information_schema.views where TABLE_NAME='v_blog'\G;
1

执行结果如下图

20220927170543

# 6.5、更改视图

1、CREATE OR REPLACE VIEW语句：

create or replace view view_name as select语句;
1

在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图。

2、ALTER语句修改视图

ALTER
	[ALGORITHM = {UNDEFINED| MERGE| TEMPTABLE}]
	[DEFINER = { user | CURRENT USER } ]
	[SQL SECURITY { DEFINER| INVOKER } ]
VIEW view_name [(column_list)]
AS select_statement
	[WITH [ CASCADED| LOCAL ] CHECK OPTION]
1
2
3
4
5
6
7

注意：修改视图是指修改数据库中已存在的表的定义，当基表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致

3、DML操作更新视图

因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中，比如我们执行以下操作

SELECT * FROM v_author;
1

20220927171453

UPDATE v_author set 姓名 = 'Naaman' where 编号 = '1';
SELECT * from author;
1
2

20220927171931

当然，视图的DML操作，不是所有的视图都可以做DML操作。 有下列内容之一，视图不能做DML操作：

  • select子句中包含distinct
  • select子句中包含组函数
  • select语句中包含group by子句
  • select语句中包含order by子句
  • select语句中包含union 、union all等集合运算符
  • where子句中包含相关子查询
  • from子句中包含多个表
  • 如果视图中有计算列，则不能更新
  • 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

# 6.6、drop删除视图

删除视图是指删除数据库中已存在的视图，删除视图时，只能删除视图的定义，不会删除数据，也就是说不会影响基表：

DROP VIEW [IF EXISTS]
view_name [, view_name]...
1
2

比如 drop view if exists v_student;

# 6.7、使用WITH CHECK OPTION约束

对于可以执行DML操作的视图，定义时可以带上WITH CHECK OPTION约束 作用：对视图所做的DML操作的结果，不能违反视图的WHERE条件的限制。

CREATE view v_blog(编号,标题,内容,作者编号)
as
SELECT id,title,content,author_id FROM blog
WHERE author_id = 1
with check option;
1
2
3
4
5

查询一下数据

SELECT * from v_blog;
1

20220927173319

再使用update对视图进行修改：

UPDATE v_blog set 作者编号=2 where 编号=1;

报错信息：
UPDATE v_blog set 作者编号=2 where 编号=1
1369 - CHECK OPTION failed 'school.v_blog'
时间: 0.001s
1
2
3
4
5
6

因为违反了视图中的where author_id = 1子句，所以抛出异常； 利用with check option约束限制，保证更新视图是在该视图的权限范围之内。

使用WITH CHECK OPTION约束时，(不指定选项则默认是CASCADED) 可以使用CASCADED或者LOCAL选项指定检查的程度： CASCADED：检查所有的视图，会检查嵌套视图及其底层的视图 LOCAL：只检查将要更新的视图本身，嵌套视图不检查其底层的视图

完善页面 (opens new window)
最近更新: 1/8/2024, 11:36:09 AM

← 事务 →

视图
Copyright © - 码上言   |