数据库视图的简介:
数据库视图是虚拟表或者逻辑表,它被定义为具有 连接(JOIN) 属性的SQL SELECT语句.视图和数据库类似,由行和列组成,可以对数据库进行操作,所以也可以把视图看作经过封装的SQL语句.大多数数据库操作系统(MySQL等)都允许你通过一些先决条件(像赋予权限等)对数据库进行操作.
视图操作也是直接对数据库进行的,因此数据发生变化时视图也会进行变化.
数据库视图的优缺点:
优点:
-
视图可以让复杂查询简单化(进过封装).通过将多个表相关联的SQL语句设置为一个视图来隐藏上级用户和复杂的基础表.通过视图你可以使用简单的SQL语句去执行原本需要很复杂的SQL语句(当然简便的同时也会有部分限制,防止数据无故丢失).
-
视图可以限制特定用户的数据访问(对数据进行隐藏).如果你不希望将自己敏感数据给所有用户查看,那么你可以通过视图将非敏感数据(自己所希望给该用户的数据)给特定用户查看.
-
视图可以提供额外的安全层(对视图进行权限限制).视图允许你创建只读视图,你可以将视图的数据公开给特定用户,而不需要担心用户对数据进行修改的威胁.
-
视图可以添加计算列(对数据进行额外计算).规范的数据表中不应该有计算列(指多列数据进行相加,相乘等操作),但是视图中可以有.假设在数据表中有 产品价格 和 产品数量 这两列,但是表中并没有对 产品总额 的列,视图在创建的时候可以添加该列,最终 产品总额 列会在查询的时候自动计算出来.
-
视图实现向后兼容(可替代数据表).如果有一个数据库,有很多应用程序正在使用它.有一次你决定对数据库进行重新设计,将里面的一些数据表删除和修改,但是不希望对那么多应用程序进行修改.在这种情况下你可以将数据表格式替换为视图(当然,视图的列内容你需要重新从新设计的表中获取).
缺点:
- 性能 : 从视图中查询数据可能会很慢(隐藏SQL语句过于复杂,视图构建不合理,数据量过大等),特别是该视图是基于其他视图创建的情况(相当于特别复杂的SQL语句中又添加同样复杂的子查询).
- 表依赖关系 : 每当更改和视图有关的数据表的结构时,视图都需要重新进行构建.
数据库视图的实现:
(仅MySQL)
- 第一种方式,MySQL会根据视图定义语句创建一个临时表,并在此临时表上传入查询数据.
- 第二种方式,MySQL将传入查询和查询定义为一个查询,然后将这俩种查询组合起来.
而文档中说每次 视图更改和替换 时,视图的副本将会在特定的数据库文件夹arc
(archive) 中备份(没找到该文件夹,不知道是不是真的).
数据库视图的限制:
- 不能在视图上创建索引.当视图使用查询时,MySQL会自动调用创建视图的表进行索引操作.
- 在MySQL中如果删除或重命名创建视图的基础表,MySQL不会进行报错,只会使视图操作无效.使用 CHECK TABLE 检查视图是否有效.
- 一个简单的视图可以更新表中数据,而具有 链表 , 子查询 等操作的视图比较复杂,MySQL无法区分,所以不能进行修改.
数据库视图的语句:
MySQL创建视图的语法:
CREATE
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name]
AS
[SELECT statement]
//由于手贱Ctrl + z进行了撤回,导致心态爆炸,以下为复制.
算法:
算法属性允许您控制MySQL在创建视图时使用的机制,MySQL提供了三种算法:MERGE
,TEMPLATE
和 UNDEFINED
。
-
使用
MERGE
算法,MySQL首先将输入查询与定义视图的SELECT语句组合成单个查询。 然后MySQL执行组合查询返回结果集。 如果SELECT语句包含集合函数(如MIN,MAX,SUM,COUNT,AVG等)或DISTINCT,GROUP BY,HAVING,LIMIT,UNION,UNION ALL,子查询,则不允许使用MERGE
算法。 如果SELECT语句无引用表,则也不允许使用MERGE
算法。 如果不允许MERGE
算法,MySQL将算法更改为UNDEFINED
。请注意,将视图定义中的输入查询和查询组合成一个查询称为视图分辨率。 -
使用
TEMPTABLE
算法,MySQL首先根据定义视图的SELECT
语句创建一个临时表,然后针对该临时表执行输入查询。因为MySQL必须创建临时表来存储结果集并将数据从基表移动到临时表,所以TEMPTABLE
算法的效率比MERGE
算法效率低。 另外,使用TEMPTABLE
算法的视图是 不可更新 的。 -
当您创建视图而不指定显式算法时,
UNDEFINED
是默认算法。UNDEFINED
算法使MySQL可以选择使用MERGE
或TEMPTABLE
算法。MySQL优先使用MERGE
算法进行TEMPTABLE
算法,因为MERGE
算法效率更高。
名称:
在数据库中,视图和表共享相同的命名空间,因此视图和表不能具有相同的名称。 另外,视图的名称必须遵循表的命名规则。
SELECT语句
在SELECT
语句中,可以从数据库中存在的任何表或视图查询数据。SELECT
语句必须遵循以下几个规则:
请注意,
SELECT
语句不需要引用任何表。
更新:
在MySQL中,视图不仅是可查询的,而且是可更新的。这意味着您可以使用INSERT或UPDATE语句通过可更新视图插入或更新基表的行。 另外,您可以使用DELETE语句通过视图删除底层表的行。
但是,要创建可更新视图,定义视图的SELECT语句不能包含以下任何元素:
- 聚合函数,如:MIN,MAX,SUM,AVG,COUNT等。
- DISTINCT子句
- GROUP BY子句
- HAVING子句
- UNION或
UNION ALL
子句 - 左连接或外连接。
- SELECT子句中的子查询或引用该表的WHERE子句中的子查询出现在
FROM
子句中。 - 引用
FROM
子句中的不可更新视图 - 仅引用文字值
- 对基表的任何列的多次引用
WITH CHECK OPTION
确保视图的一致性:
有时候,创建一个视图来显示表的部分数据。然而,简单视图是可更新的,因此可以更新通过视图不可见的数据。此更新使视图不一致。为了确保视图的一致性,在创建或修改视图时使用WITH CHECK OPTION
子句。
下面说明了WITH CHECK OPTION
子句的语法
CREATE OR REPLACE VIEW view_name
AS
select_statement
WITH CHECK OPTION;
LOCAL&CASCADED检查范围:
当使用WITH CHECK OPTION
子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其符合视图的定义。因为MySQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。
为了确定检查的范围,MySQL提供了两个选项:LOCAL
和CASCADED
。如果您没有在WITH CHECK OPTION
子句中显式指定关键字,则MySQL默认使用CASCADED
。
- 当视图使用
WITH CASCADED CHECK OPTION
时,MySQL会循环检查视图的规则以及底层视图的规则。 - 当视图使用WITH LOCAL CHECK OPTION时,MySQL会检查
WITH LOCAL CHECK OPTION
和WITH CASCADED CHECK OPTION
选项的视图规则。而只有WITH LOCAL CHECK OPTION时
MySQL只会检查当前视图的规则,并且不会检查底层视图的规则。两种规则都存在时MySQL检查所有依赖视图的规则
查看视图定义:
MySQL提供了用于显示视图定义的SHOW CREATE VIEW
语句。
以下是SHOW CREATE VIEW
语句的语法:
SHOW CREATE VIEW [database_name].[view_ name];
修改视图:
MySQL提供两个语句,允许您修改现有视图:ALTER VIEW
和CREATE OR REPLACE VIEW
。
ALTER VIEW:
ALTER VIEW
语句的语法类似于CREATE VIEW
语句,除了CREATE
关键字被ALTER
关键字替换外,其它都一样。
ALTER
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name]. [view_name]
AS
[SELECT statement]
CREATE OR REPLACE VIEW:
除ALTER VIEW
语句外,还可以使用CREATE OR REPLACE VIEW
语句来创建或替换现有视图。如果一个视图已经存在,MySQL只会修改视图。如果视图不存在,MySQL将创建一个新的视图。
以下语句使用CREATE OR REPLACE VIEW
语法根据employees
表创建一个名称为v_contacts
的视图:
CREATE OR REPLACE VIEW v_contacts AS
SELECT
firstName, lastName, extension, email
FROM
employees;
-- 查询视图数据
SELECT * FROM v_contacts;