MySQL入门 (十) : Views

1 View的应用

在使用MySQL资料库的时候,你会使用各种不同的SQL叙述来执行查询与维护的工作。 资料库在运作一段时间后,你会发觉不论是查询与维护的叙述,都可能会出现一些类似、而且很常使用的SQL叙述:

mysql_11_snap_01

以上列的查询叙述来说,虽然它并不是很复杂,只是一个加入排序设定的一般查询而已。 可是如果常常会执行这样的查询,你每次都要输入这个查询叙述再执行它;就算你把这个查询叙述储存为文字档保存起来,需要的时候再开启档案使用,这样做的话是比较方便一些,不过还是很麻烦,而且比较没有灵活性。

如果在资料库的应用中,出现这种很常执行的查询叙述时,你可以在MySQL资料库中建立一种「View」元件,View元件用来保存一段你指定的查询叙述:

mysql_11_snap_02

建立好需要的View元件以后,除了有一些限制外,它使用起来就像是一个表格,所以当你需要执行这样的查询时,可以在查询叙述的「FROM」子句指定一个View元件:

mysql_11_snap_03

也有很多人称「View」元件是一种「虚拟表格」,因为它不是一个真正储存纪录资料的表格,可是它又跟表格的用法类似。 所以如果有需要的话,你也可以使用View元件回传的纪录资料,执行统计、分组与其它需要的处理:

mysql_11_snap_04

View元件就像是一个表格,大部份使用表格可以完成的工作,也可以套用在View元件。 所以把View元件和表格一起放在「FROM」子句中,执行需要的结合查询也是可以的:

mysql_11_snap_05

2 建立需要的View

不论是为了查询或维护,如果你很常需要使用到同一个查询叙述,你就可以考虑建立一个View元件把这个查询叙述储存起来。 下列是建立View元件基本的语法:

mysql_11_snap_06

如果你很常执行查询「每个地区GNP最高的国家」资料,这样的需求可以使用子查询来完成,为了不想要每次重复输入这个查询叙述,你可以建立一个名称「CountryMaxGNP」的View元件,这样以后要执行这个查询的时候就方便多了:

mysql_11_snap_07

在上列建立View元件的范例中,只有「Name」与「GNP」两个栏位,如果想要在已经建立好的「CountryMaxGNP」的View元件中,再加入新的「Code」栏位的话:

mysql_11_snap_08

如果需要修改一个已经建立好的View元件,你就要加入「OR REPLACE」的设定,这样才不会出现错误讯息:

mysql_11_snap_09

如果想要查询一个View元件中会传回哪些栏位的资料,可以使用「DESCRIBE」或是比较简短的「DESC」指令:

mysql_11_snap_10

下列是MySQL关于View元件的规定与限制:

  • 在同一个资料库中,View的名称不可以重复,也不可以跟表格名称一样
  • View不可以跟Triggers建立联结

储存在View中的查询叙述也有下列的规定:

  • 查询叙述中只能使用到已存在的表格或View
  • 「FROM」子句中不可以使用子查询
  • 不可以使用「TEMPORARY」表格
  • 不可以使用自行定义的变数、Procedure与Prepared statement参数

注:「TEMPORARY」表格在「表格与索引、建立表格、建立暂存表格」中讨论。 「Triggers」、定义变数、「Procedure」与「Prepared statement」在后面都会有章节详细的讨论。

结合查询在关联式资料库中几乎是必要的一种查询,以下列查询「国家与城市人口比例」的需求来说,就需要从「country」与「city」表格中查询必要的栏位资料:

mysql_11_snap_11

如果会经常执行这个结合查询的话,你应该会很希望把它储存为View元件:

mysql_11_snap_12

你不会在一个表格中,为不同的两个栏位取一样的名称;在使用查询叙述提供View元件的栏位时,也要注意名称重复的问题,虽然在单纯的结合查询回传的资料中,有一样的栏位名称并不会造成错误。 要解决这个错误有两种方式,第一种是在查询叙述的「SELECT」子句中,自己为名称重复的栏位取不同的栏位别名:

mysql_11_snap_13

另外一种方式可以在建立View元件的时候,另外指定View元件的栏位名称:

mysql_11_snap_14

这样的作法不用修改查询叙述,依照查询叙述回传的栏位顺序,另外指定View元件使用的栏位名称:

mysql_11_snap_15

3 修改View

使用「ALTER VIEW」叙述,可以让你修改一个已经建立好的View元件:

mysql_11_snap_16

下列的范例使用「ALTER VIEW」叙述修改已经存在的「CountryMaxGNP」View元件:

mysql_11_snap_17

上列范例执行的工作也可以使用「CREATE OR REPLACE VIEW」叙述来完成:

mysql_11_snap_18

如果以修改View元件的工作来说,使用「ALTER VIEW」或「CREATE OR REPLACE VIEW」叙述的效果是完全一样的。 唯一的差异是要修改View元件如果不存在的话,「CREATE OR REPLACE VIEW」叙述会直接建立新的View元件:

mysql_11_snap_19

4 删除View

下列的语法可以删除一个不需要的View元件:

mysql_11_snap_20

如果「DROP VIEW」叙述指定的View元件不存在的话,执行叙述以后会产生错误讯息:

mysql_11_snap_21

你可以在「DROP VIEW」叙述加入「IF EXISTS」,这样就可以防止产生View元件不存在的错误讯息:

mysql_11_snap_22

5 资料维护与View

View元件除了提供比较方便的查询方式外,你也可以使用View元件来执行资料维护的工作。 与View元件应用在查询资料时提供的方便性一样,不使用表格元件,而使用View元件来执行新增、修改或删除的工作,也可以增加资料维护的方便性。

要使用View元件来执行新增、修改或删除的工作,View元件所包含的查询叙述必须符合下列的规则:

  • 不可以包含计算或函式的栏位
  • 只允许一对一的结合查询
  • View元件的「ALGORITHM」不可以设定为「TEMPTABLE」

如果符合上列规定的View元件,就会称为「可修改的View元件、updattable views」。 只有可修改的View元件,可以使用在「INSERT」、「UPDATE」或「DELETE」叙述中执行资料维护的工作。

注:View元件的「ALGORITHM」设定在这一章后面的「View的演算法」中讨论。

5.1 使用View元件执行资料维护

下列是一个可以执行资料维户的View元件,它的栏位没有包含计算或函式,也没有使用结合查询:

mysql_11_snap_23

如果要修改员工编号「7844」的佣金为600的话,你除了可以在「UPDATE」叙述中指定修改的表格名称为「emp」外,也可以在「UPDATE」叙述中指定View元件「EmpDept30View」:

mysql_11_snap_24

在执行上列的「UPDATE」叙述以后,不论是查询View元件或表格,都可以确定资料已经修改了:

mysql_11_snap_25

使用「INSERT」叙述新增纪录时,也可以指定View元件「EmpDept30View」:

mysql_11_snap_26

在执行上列的「INSERT」叙述以后,查询View元件所得到的结果并没有刚才新增的员工资料,查询表格时才可以确定资料已经新增,这是因为新增纪录的部门编号栏位资料为「NULL」的关系:

mysql_11_snap_27

与「INSERT」和「UPDATE」叙述一样,「DELETE」叙述也可以指定View元件的纪录资料:

mysql_11_snap_49

不过执行上列的删除叙述后,千万不要以为你已经删除员工编号「9001」的员工纪录了:

mysql_11_snap_50

5.2 使用「WITH CHECK OPTION」

你可以使用View元件来执行资料维护的工作,可是在执行新增或修改的时候,又可能会造成一些有问题的资料。 如果你不希望产生这类的问题,你可以为View元件加入「WITH CHECK OPTION」的设定:

mysql_11_snap_28

加入「WITH CHECK OPTION」设定的View元件,在执行资料维护工作时,会先执行检查的工作,规则是一定要符合「View元件中WHERE设定的条件」:

mysql_11_snap_29

因为上列范例所新增的纪录资料,「deptno」栏位会储存「NULL」值,这样就违反View元件中「WHERE deptno = 30」的条件设定了,所以在执行以后会产生错误讯息。 下列的修改叙述就可以正确的执行:

mysql_11_snap_30

View元件中的「WITH CHECK OPTION」设定,还有额外的「CASCADE」和「LOCAL」两个控制检查范围的设定:

mysql_11_snap_31

会有「CASCADE」和「LOCAL」这两个设定的原因,是因为View元件的资料来源可以一个表格,也可以是一个View元件:

mysql_11_snap_32

查询「EmpDept20View」后,传回的纪录资料包含「deptno = 20」条件,与设定在「EmpSalaryView」的「salary >= 1500」条件:

mysql_11_snap_33

检查范围设定为「LOCAL」的View元件,在执行资料维护的时候,只会检查是否符合自己的条件设定:

mysql_11_snap_34

如果执行资料维护的叙述违反「EmpSalaryView」的条件设定,还是可以正确的执行:

mysql_11_snap_35

如果你希望所有的View元件在执行资料维护的时候,都不可以出现这类的问题,就应该把View元件的检查范围设定为「CASCADE」:

mysql_11_snap_36

检查范围设定为「CASCADE」的View元件,在执行资料维护的时候,就不能违反所有VIew元件的条件设定:

mysql_11_snap_37

6 View的演算法

View元件可以提供更方便的资料查询与维护方式,在你建立View元件的时候,除了指定的查询叙述要符合规定,还可以指定资料库执行View元件时所使用的「演算法、algorithm」:

mysql_11_snap_38

一般来说,你不需要特别指定View元件使用的演算法。 如 果在建立View元件的时候,没有指定使用的演算法为「MERGE」或「TEMPTABLE」,MySQL会设定为「UNDEFINED」,这个设定表示 MySQL会依照View元件中包含的叙述,自动选择一个适合的演算法,可能是「MERGE」或「TEMPTABLE」。

下列是一个演算法设定为「MERGE」的View元件,在MySQL资料库中的运作情形:

mysql_11_snap_39

下列是一个演算法设定为「TEMPTABLE」的View元件,在MySQL资料库中的运作情形:

mysql_11_snap_40

并不是所有的View元件都可以指定演算法设定为「MERGE」,以下列查询员工统计资讯的叙述来说:

mysql_11_snap_41

如果执行下列建立View元件的叙述,就会产生警告的讯息:

mysql_11_snap_42

如果View元件包含的查询叙述有下列的情况,MySQL都会自动把演算法设定为「UNDEFINED」:

  • 群组函式:SUM()、MIN()、MAX()、COUNT()
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION或UNION ALL
  • 「SELECT」子句中包含一个明确的值,而不是表格的栏位

7 View的维护与资讯

7.1 检验View的正确性

在你建立一个View元件的时候,MySQL会检查View元件包含的查询叙述是否正确,如果没有问题的话,才会储存View元件的设定。 不过以下列的范例来说:

mysql_11_snap_43

如果不小心删除「EmpSalaryView」这个View元件:

mysql_11_snap_44

执行查询「EmpDept20View」的时候,就会产生警告讯息了:

mysql_11_snap_45

这样的问题也可以经由使用检查表格或View元件的叙述发现:

mysql_11_snap_46

执行检查「EmpDept20View」的叙述可以发现这是一个有问题的View元件:

mysql_11_snap_47

7.2 取得View的相关资讯

MySQL资料库在启动以后,会有一个很特别的资料库,名称是「information_schema」,这个资料库通常会称为「系统资讯资料库」。 这个资料库中有一个表格叫作「VIEWS」,它储存所有MySQL资料库中View元件的相关资讯,「VIEWS」表格有下列主要的栏位:

栏位名称 型态 说明
TABLE_SCHEMA varchar(64) 资料库名称
TABLE_NAME varchar(64) 表格名称
VIEW_DEFINITION longtext 演算法定义与储存的查询叙述
CHECK_OPTION varchar(8) 检查范围设定
IS_UPDATABLE varchar(3) 是否可以执行资料维护

执行下列的叙述就可以查询资料库中的View元件资讯:

mysql_11_snap_48

 

更多参考:

MySQL 超新手入门教程系列

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (三) : 运算式与函式

MySQL入门 (四) : JOIN 与UNION 查询

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (六) : 字元集与资料库

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (八) : 表格与索引

MySQL入门 (九) : 子查询 Subquery

MySQL入门 (十一) : Prepared Statements

 

本文: MySQL入门 (十) : Views

One Comment

Leave a Reply