一、什么是视图
视图是一个虚拟表。也就是说,视图在外观和行为上都类似于表,但它不需要实际的物理存储,只保存了视图定义(查询语句)。
视图由select
查询所定义 —— 当创建一个视图时,实际上是在数据库里执行了一个select语句,它从一个或多个表中导出这个视图。
- 对视图的操作与对表的操作一样,可以对视图进行查询、更新、删除。
- 视图只是从表里引用数据,所以对视图的更新会反映到表中,对表的更新也会反映到视图中。
- 如果用于创建视图的表被删除了,该视图将不可访问。
视图的作用:
-
简化数据访问。当我们需要一个表中的某些数据时,我们不需要每次都进行查询,通过创建一个视图来包含我们所需的数据,然后从这个视图获取数据。
-
视图可以作为一种安全形式。如果一个表的某些字段的数据是保密的,那么就不能让用户直接访问这个表。可以在这个表的基础上创建一个视图,过滤掉保密的字段,然后授权用户访问这个视图。——通过视图用户只能查询和修改他们所能见到的数据。
-
逻辑数据独立。视图可帮助用户屏蔽真实表结构变化带来的影响,在一定程度上使应用程序和数据库表独立。
二、创建视图
创建视图使用CREATE VIEW
语句,我们可以从一个表、多个表或另一个视图来创建视图。
2.1 从一个表创建视图
- 1
- 2
- 3
- 4
- 1
- 2
- 3
- 4
例:假设有下面这样一个表workers
,分别保存了员工的名字,性别,工资,年龄,居住城市。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
Question 1:如果该公司有一个员工写报告时需要获取每个员工的姓名、年龄等信息,但工资不能被他看到。这时我们就可以创建一个视图,让他访问视图:
- 1
- 1
查看视图如下:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
Question 2:如果要维护对员工的一个统计报告,比如统计每个城市员工的平均年龄、平均薪水、男性总数、女性总数,我们可以基于这个表创建一个视图,之后只要对视图进行查询即可。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
查看统计视图如下:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
在 MySQL 中,information_schema
数据库下的views
表中存储了所有视图的定义。通过对views表的查询,可以查看数据库中所有视图的详细信息:
- 1
- 1
2.2 从多个表创建视图
通过将多个表进行连接(JOIN),我们可以从多个表创建视图。其语法也是:
- 1
- 1
视图本身不过是一个select语句而已,因此表在视图定义里的连接与在普通select语句里是一样的。
2.3 从视图创建视图
我们还可以从视图创建视图:
- 1
- 1
视图创建视图可以具有多个层次(视图的视图的视图,以此类推),如下图所示:
基于视图创建视图的唯一问题在于它们的可管理性。如上图中的视图,它们一层一层依赖,如果VIEW1
被删除了,VIEW3
和VIEW4
就无效了,如果TABLE
被删除了,这些视图就都无效了。
三、修改视图定义
修改视图是指修改已经存在的视图。当表的某些字段发生变化时,可以通过修改视图来保持与表的一致性。语法如下:
- 1
- 2
- 3
- 1
- 2
- 3
四、视图的执行算法
在创建或修改视图时,有一个可选的 Algorithm 选项,它表示视图的执行算法。
mysql中的视图存在两种执行算法:
- Merge:合并的执行方式,每当执行的时候,先将我们视图的sql语句与外部查询视图的sql语句,混合在一起,最终执行;
- Temptable:临时表模式,每当查询的时候,将视图所使用的select语句生成一个结果的临时表,再在当前的临时表内进行查询。
所以ALGORITHM可取三个值:MERGE、TEMPTABLE、UNDEFINED(默认值)。
- 对于MERGE,会将引用视图的语句的文本与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分。
- 对于TEMPTABLE,视图的结果将被置于临时表中,然后使用它执行语句。
- 对于UNDEFINED,MySQL自动选择所要使用的算法。如果可能,它倾向于MERGE而不是TEMPTABLE,这是因为MERGE通常更有效,而且
如果使用了临时表,视图是不可更新的
。
五、更新视图
更新视图是指对视图进行插入、更新、删除操作。因为视图是一个虚拟表,对视图更新都是转到了对表进行更新。以上面创建的 v1 视图进行示范:
5.1 Update
- 1
- 1
查看workers
表:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
5.2 Insert
若要通过视图插入数据,首先视图需要是可更新的,此外还需要满足一些要求:
- 不得有重复的视图列名称。
- 视图必须包含没有默认值的基表中的所有列。
- 视图列必须是简单的列引用而不是导出列。导出列不是简单的列引用,而是从表达式导出的。
- 混合了简单列引用和导出列的视图是不可插入的,但是,如果仅更新非导出列,视图是可更新的。
例1:
- 1
- 1
view_t
视图包含了基表中的所有列(id是自增的),而且都是简单的列引用,所以可以通过视图插入新数据:
- 1
- 2
- 1
- 2
例2:
- 1
- 1
现在view_t
视图包含了导出列salary/1000
,如果对导出列执行插入将会报错:
- 1
- 2
- 1
- 2
5.3 Delete
从视图中删除一条记录:
- 1
- 1
查看workers
表:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
5.4 不可更新的视图
若视图是可更新的,则可以在诸如UPDATE、DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。
但在下述几种情况下,视图是不可更新的:
- 视图中不包含基表中被定为非空的列;
- 在定义视图的SELECT语句后的字段列表中使用了数学表达式;
- 在定义视图的SELECT语句后的字段列表中使用了聚合函数COUNT、SUM、MAX、MIN、AVG;
- 在定义视图的SELECT语句中使用了DISTINCT,UNION,TOP,GROUP BY或HAVING子句。
- ALGORITHM = TEMPTABLE(使用临时表总会使视图成为不可更新的)。
另外,若视图是基于多个表使用联接操作而导出的,且不满足上述条件,那么对这个视图执行更新操作时,每次只能影响其中的一个表,否则,更新操作也不能执行。
这里尝试对视图v2
执行Delete操作,结果如下:
- 1
- 2
- 1
- 2
六、删除视图
删除视图使用DROP VIEW
语句:
- 1
- 1