史上最简单的 MySQL 教程(三十四)「视图(下)」

温馨提示:本系列博文已经同步到 GitHub,地址为「mysql-tutorial」,欢迎感兴趣的童鞋StarFork,纠错。

视图数据操作

视图数据操作:虽然我们说视图可以称之为select语句的别名,但实际上,它和别名并不一样,因为视图是可以进行数据写操作的,只不过有很多限制而已。

新增数据

在这里,新增数据就是指通过视图直接对基表进行数据的新增操作。

  • 限制 1:多表视图不能进行新增数据。

执行如下 SQL 语句,进行测试:

-- 查询视图 my_v3 结构
desc my_v3;

-- 多表视图新增数据
insert into my_v3 values(7,'Gates','boy',2,170,'PM3.5','A315');
1
  • 限制 2:可以向单表视图新增数据,但视图中包含的字段必须有基表中所有不能为空的字段。

执行如下 SQL 语句,进行测试:

-- 查询 student 表结构
desc student;

-- 创建视图 my_v4
create view my_v4 as
select id,name,age,c_id from student;

-- 单表视图新增数据
insert into my_v4 values(7,'Gates',25,2);
2

如上图所示,在我们新建的视图my_v4中,没有包含不能为空的字段gender,因此在我们向my_v4中新增数据的时候,报错。其实,这也很好理解,试想,在 MySQL 尝试将视图中新增的数据(一条记录)插入到基表的时候,忽然发现一个本不能为null的字段的值为默认值null,自然就会报错啦!反之,如果单表视图中包含了基表中的全部非空字段,自然可以插入成功。执行如下 SQL 语句,进行测试:

-- 查询 class 表数据
select * from class;

-- 创建视图 my_v5
create view my_v5 as
select * from class;

-- 单表视图新增数据
insert into my_v5 values(2,'PM2016','A315');

-- 查询 class 表数据
select * from class;
3

如上图所示,显然我们通过单表视图向基表中插入数据成功啦!

删除数据

与新增数据类似,

  • 多表视图不能删除数据;
  • 单表视图可以删除数据。

执行如下 SQL 语句,进行测试:

-- 查询多表视图 my_v3
select * from my_v3;

-- 删除多表视图 my_v3 中记录
delete from my_v3 where id = 2;

-- 查询单表视图 my_v5
select * from my_v5;

-- 删除单表视图 my_v5 中记录
delete from my_v5 where id = 2;

-- 查询单表视图 my_v5
select * from my_v5;
4

更新数据

理论上,无论多表视图还是单表视图,都可以进行数据的更新。

执行如下 SQL 语句,进行测试:

-- 查询单表视图 my_v5
select * from my_v5;

-- 更新单表视图 my_v5
update my_v5 set grade = 'PM2014' where id = 5;

-- 查询单表视图 my_v5
select * from my_v5;-- 查询单表视图 my_v5
select * from my_v5;

-- 更新单表视图 my_v5
update my_v5 set grade = 'PM2014' where id = 5;

-- 查询单表视图 my_v5
select * from my_v5;
5

此外,更新视图数据并不总是成功的,这是因为有更新限制的存在。那么何为更新限制呢?

  • 更新限制:with check option,如果创建视图的时候,设置了某个字段的限制,那么对视图进行更新操作的时候,系统就会进行验证,要保证更新之后,数据依然可以被查出来,否则不让更新。

执行如下 SQL 语句,进行测试:

-- 创建单表视图 my_v6
create view my_v6 as
select * from student where height > 170 with check option;

-- 查询单表视图 my_v6
select * from my_v6;

-- 更新单表视图 my_v6
update my_v6 set height = 165 where id = 6;
6

如上图所示,在更新视图的时候,更新失败,这是因为其违反了我们设置的更新限制。那么,视图之外的数据,我们能不能修改呢?执行如下 SQL 语句,进行测试:

-- 查询单表视图 my_v6
select * from my_v6;

-- 更新单表视图 my_v6
update my_v6 set height = 188 where id = 4;

-- 查询单表视图 my_v6
select * from my_v6;
7

如上图所示,更新视图my_v6之外数据的时候,显示成功。但是,待我们重新查询视图my_v6数据的时候,发现并没有真正更新成功。这是为什么呢?原因就在于我们不能通过视图去操作视图之外的数据。举一个不太恰当的例子,我们不能用自己手去操作别人兜里的钱啊!

视图算法

视图算法,即系统对视图以及外部查询视图的select语句的一种解析方式。视图算法有三种,分别为:

  • undefined:未定义(默认的),这不是一种实际使用的算法,而是一个“推卸责任”的算法。在未定义的情况下,告诉系统,视图没有定义算法,请自己选择。
  • temptable:临时表算法,系统先执行视图的select语句,后执行外部查询语句。
  • merge:合并算法,系统先将视图对应的select语句与外部查询视图的select语句进行合并,然后再执行。此算法比较高效,且在未定义算法的时候,经常会默认选择此算法。

对于视图的算法,我们需要在创建视图的时候指定,

  • 基本语法create + [algorithm = temptable/merge/undefined] + view + 视图名 + as + select语句;

执行如下 SQL 语句,进行测试:

-- 查看视图 my_v2 的默认算法
show create view my_v2;

-- 指定视图 my_v7 算法为 temptable
create algorithm = temptable view my_v7 as
select * from student;

-- 查看视图 my_v7 的指定算法
show create view my_v7;
8

如上图所示,我们指定了视图my_v7的算法为temptable,但是对于算法的选择,我们该如何判断呢?答案是:如果视图的select语句中包含一个查询子句(五子句,包括wheregroup byorder byhavinglimit),而且很有可能查询子句的顺序比外部的查询语句的顺序要靠后(五子句的顺序),那么一定要使用temptable算法,其他情况可以不用指定,默认即可。

至此,我们已经将视图的内容大致讲完了。有一点需要我们特别注意,那就是:视图最重要的功能就是查询,其他如增、删、改的操作一般不会使用,也不建议通过视图来操作基表的数据


温馨提示:符号[]括起来的内容,表示可选项;符号+,则表示连接的意思。


———— ☆☆☆ —— 返回 -> 史上最简单的 MySQL 教程 <- 目录 —— ☆☆☆ ————

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JAVA后端开发

activiti explorer跨模板复制流程节点

最近的项目是用activiti explorer做流程前端。 功能做出来后,经常被用户反馈 ,说流程页面不好用。 画一个流程图后,再画一个,都要重头开始,如...

1061
来自专栏扎心了老铁

linux命令和awk

1、统计一下代码量 find . -name "*.py" | xargs wc -l | awk 'BEGIN {size = 0} { size+=$1} ...

2805
来自专栏WindCoder

Logstash6中grok插件的常用正则表达式

Logstash 内置了120种默认表达式,可以查看patterns,里面对表达式做了分组,每个文件为一组,文件内部有对应的表达式模式。下面只是部分常用的。

792
来自专栏PHP技术

php常用函数

/** * 对数据进行编码转换 * @param array/string $data 数组 * @param string $input ...

3045
来自专栏数据和云

impdp create index parallel-数据泵并行创建索引

题记:在数据泵导入时默认情况下创建索引是不使用并行的,所以这一步会消耗较长的时间,除了人工写脚本并行创建索引外,数据泵在导入时也可以并行创建索引,本文详细介绍了...

3204
来自专栏农夫安全

代码审计之SQL注入漏洞

SQL注入: 我的理解很简单,能代入到数据库查询,且没有过滤,或过滤不严谨,就可以造成SQL注入 演示环境:linux+apache+...

3357
来自专栏Python小屋

Python批量Excel文件数据导入SQLite数据库的优化方案

说明:1)需要安装扩展库openpyxl;2)随着数据库的增大,导入速度可能会有所下降;3)本文只考虑Python代码优化,没有涉及数据库的优化;4)本文要点在...

3607
来自专栏维C果糖

史上最简单的 MySQL 教程(四十二)「代码执行结构」

在 MySQL 中,没有for循环,仅有while循环、loop循环和repeat循环,呃,还有一种非标准的goto循环,在此我们仅介绍while循环,其基本语...

4147
来自专栏源哥的专栏

由于查询语句中日期的格式引起的问题

我这边有一个系统,在一个环境下运行完全正常,但迁到另外一个环境后,其中一个查询功能就莫名其妙的出现了问题,我通过检查,发现有一个很复杂的查询语句,在一个数据库环...

491
来自专栏mwangblog

bash中的变量

1188

扫码关注云+社区