MySQL数据库视图的创建和管理方法
当用SQL语言定义并执行数据查询时,查询的结果将直接输出到客户端,而不会在服务器端中保存。如果有多个用户或需要多次进行同样的数据查询,可以将数据查询的定义保存在服务器端的数据库中,这种操作称为创建视图。视图是一个虚表,视图中存储的是查询数据的SQL语句,它对应的数据来自基本表。对视图进行操作时,系统会根据视图的定义对与视图相关联的基本表进行操作;但对于用户来说,使用视图和使用基本表是一样的。我们将学习如何定义视图,以及如何通过视图查询、修改、删除和更新数据。
一、视图概述
视图是从一个或多个表(或视图)导出的表。视图是数据库的用户使用数据库的观点。视图与表[有时为与视图区别,也称表为基本表(Base Table)]不同,视图是一个虚表,即视图所对应的数据不进行实际存储,数据库中只存储视图的定义,对视图的数据进行操作时,系统根据视图的定义去操作与视图相关联的基本表。
视图一经定义以后,就可以像表一样被查询、修改、删除和更新。使用视图有下列优点:
1、为用户集中数据,简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中,定义视图可将它们集中在一起,从而方便用户的数据查询和处理。
2、屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构,并且数据库表的更改也不影响用户对数据库的使用。
3、简化用户权限的管理。只需授予用户使用视图的权限,而不必指定用户只能使用表的特定列,这样也增强了安全性。
4、便于数据共享。各用户不必都定义和存储自己所需的数据,可共享数据库的数据,这样同样的数据只需存储一次。
5、可以重新组织数据,以便输出到其他应用程序中。
二、创建与查询
如何创建视图?
语法格式如下:
CREATE VIEW[数据库名.]视图名
AS
SELECT语句
[WITH CHECK OPTION];
说明:
WITH CHECK OPTION:可选,将更新限制在视图范围内,
不可超出WHERE设定的条件范围;
视图命名常以V_或VIEW_开头,_V或_VIEW结尾。
实例:
假设我们有一个名为employees的表,包含员工的ID、姓名、部门和薪水等信息。我们可以创建一个只显示ID和姓名的视图:
CREATE VIEW view_employee_name_id AS
SELECT id, name
FROM employees;
根据视图所对应的子查询,视图可以分为以下几种类型:
1、简单视图:基于单表查询,且不包含任何函数运算、表达式或分组函数。
2、复杂视图:基于单表查询,但包含函数运算、表达式或分组函数。
3、连接视图:基于多表查询。
如以下例子:
使用视图时,要注意下列事项。
1,、在默认情况下,将在当前数据库创建新视图。要想在给定数据库中明确创建视图创建时应将名称指定为“库名.视图名”。
2、视图的命名必须遵循标识符命名规则,不能与表同名。对于每个用户,视图名必须是唯一的,即对不同用户,即使是定义相同的视图,也必须使用不同的名字。
3、不能把规则、默认值或触发器与视图相关联。
4、不能在视图上建立任何素引,包括全文索引。
5、视图中使用SELECT语句有以下限制。
定义视图的用户必须对所参照的表或视图有查询权限,即可执行SELECT语句
的权限;在定义中引用的表或视图必须存在。
不能包含FROM子句中的子查询,不能引用系统或用户变量,不能引用预处理
语句参数。
在视图定义中允许使用ORDERBY子句,但是,如果从特定视图进行了选择,而该视图使用了具有自己 ORDER BY子句的语句,则视图定义中的ORDER BY子句将被忽略。
查询视图的三种方法
语法1:DESCRIBE 视图名;或 DESC视图名;
功能:查看视图的字段信息。
示例:DESCv_book_1;
语法2:SHOW TABLE STATUS LIKE视图名
功能:查看视图的基本信息。
示例:SHOW TABLE STATUS LIKE'v_book_1'
语法3:SHOW CREATE VIEW视图名;
功能:不仅可以查看创建视图时的定义语句,还可以查看视图的字符编码。
示例:SHOW CREATE VIEW v_book_1
三、操作视图
1、更新视图
更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚拟表,其中没有数据,通过视图更新时,都是转换到基本表来更新。
当视图所依赖的基本表有多个时:
向该视图插入数据时,不能影响多个基本表,只能影响一个表。
一次修改该视图只能变动一个基本表的数据。
不能使用delete语句。
当视图中包含有如下内容时,视图的更新操作将不能被执行:
在定义视图的SELECT语句后的字段列表中使用聚合函数。
在定义视图的SELECT语句中使用了DISTINCT, UNION, LIMIT, GROUP
BY或HAVING子句。
插入数据(isert)
示例:
2、修改视图的基本语法
ALTER VIEW[数据库名.]视图名
AS
SELECT语句
[WITH CHECK OPTION];
示例:
说明:WITH CHECK OPTION:可选,将更新限制在视图范
围内,不可超出WHERE设定的条件范围;
3、删除视图
当视图不需要时,可以将其删除,删除视图时,只能删除视图的定义,
不会删除数据。
删除视图的基本语法格式如下所示:
DROP VIEW [IF EXISTS][数据库名.]视图名1[视图名2...]
示例:
DROP VIEW v_book_class;
四、总结
视图是根据用户的不同需求,在物理数据库上按用户观点定义的数据结构。视图是一个虚表,数据库中只存储视图的定义,不实际存储视图所对应的数据。对视图的数据进行操作时,系统根据视图的定义去操作与视图相关联的基本表。
视图一经定义,就可以像表一样被查询、修改、删除和更新,但对视图使用INSERT、UPDATE及DELETE语句操作时,有以下一些限制。
要通过视图更新基本表数据,必须保证视图是可更新视图。在创建视图的时候加上WITH CHECK OPTION子句,更新数据时会检查新数据是否符合视图定义中WHERE子句的条件。
对视图使用INSERT语句插入数据时,创建该视图的SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。当视图所依赖的基本表有多个时,不能向该视图插入数据,因为这将会影响多个基本表。
若一个视图依赖于多个基本表,则一次修改该视图只能变动一个基本表的数据。
对依赖于多个基本表的视图,不能使用DELETE语句。