MySQL基础学习（视图）

我在山的这边
304 阅读8分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第7天，点击查看活动详情

1、什么是视图？

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

例子：

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

2、作用

关系型数据库中的数据是由一张一张的二维关系表所组成，简单的单表查询只需要遍历一个表，而复杂的多表查询需要将多个表连接起来进行查询任务。对于复杂的查询事件，每次查询都需要编写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, '小兰');

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);

6.1、创建视图

创建视图的SQL语法

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

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;

执行结果如下图所示

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

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

image.png

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

image.png

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;
​

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

image.png

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

6.4、查看视图

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

show create view v_blog;

image.png

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

image.png

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

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

执行结果如下图

image.png

6.5、更改视图

1、CREATE OR REPLACE VIEW语句：

create or replace view view_name as select语句;

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

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]

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

3、DML操作更新视图

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

SELECT * FROM v_author;

image.png

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

image.png

当然，视图的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]...

比如 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;

查询一下数据

SELECT * from v_blog;

image.png

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

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

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

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

avatar
文章
阅读
粉丝