目录
3.2 SHOW TABLE STATUS语句查看视图基本信息
3.3 SHOW CREATE VIEW语句查看视图详细信息
4.1 CREATE OR REPLACE VIEW语句修改视图
视图是从一个或多个表中导出来的表,是一种虚拟存在的表。视图就像一个窗口,通过这个窗口可以看到系统专门提供的数据。这样,用户可以不用看到整个数据库表中的数据,而只关心对自己有用的数据。视图可以使用户的操作更方便,而且可以保障数据库系统的安全性。
1 、视图简介
视图由数据库中的一个表或多个表导出的虚拟表。其作用是方便用户对数据的操作。
1.1 视图的含义
视图是一种虚拟的表,是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。数据库中只存放了视图的定义,而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。因此,视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变,显示在视图中的数据也会发生改变。
下面用一个例子来具体讲解视图的含义。
下面一个公司的数据库中有一张公司部门表department。表中包括部门号(d_id)、部门名称(d_name)、功能(function)和办公地址( address)。department表结构如下:
还有一张员工表worker。表中包含了员工的工作号(num)、部门号(d_id)、姓名(name)、性别(sex)、出生日期(birthday)和家庭住址( homeaddress)。worker表结构如下:
由于各部门的领导的权力范围不同。因此,各部门的领导只能看到该部门的员工的信息。而且,领导可能不关心员工的生日和家庭住址。为了达到这个目的,可以为各部门的领导建立一个视图。通过该视图,领导只能看到本部门的员工的指定信息。
例如,为生产部门建立一个名为product_view 的视图。通过视图product_view,生产部门的领导只能看到生产部门员工的工作号、姓名和性别等信息。这些department 表的信息和worker表的信息依然存在于各自的表中,而视图product_view中不保存任何数据信息。当department表和 worker表的信息发生改变时,视图product_view展示的信息也发生相应的变化。
如果经常需要从多个表查询指定字段的数据,可以在这些表上建立一个视图。通过这个视图显示这些字段的数据。如果表中修改了与视图相关的字段的名称,可以通过修改视图来解决可能引起的问题。
MySQL的视图不支持输入参数的功能,因此交互性上还有欠缺。但对于变化不是很大的操作,使用视图可以很大程度上简化用户的操作。
1.2 视图的作用
视图是在原有的表或者视图的基础上重新定义的虚拟表,这可以从原有的表上选取对用户有用的信息。那些对用户没有用,或者用户没有权限了解的信息,都可以直接屏蔽掉。这样做既使应用简单化,也保证了系统的安全。视图起着类似于筛选的作用。视图的作用归纳为如下几点:
1.使操作简单化
视图需要达到的目的就是所见即所需。也就是说,从视图看到的信息就是所需要了解的信息。视图可以简化对数据的操作。例如,可以为经常使用的查询定义一个视图,使用户不必为同样的查询操作指定条件。这样可以很大程度上方便用户的操作。
2.增加数据的安全性
通过视图,用户只能查询和修改指定的数据。指定数据以外的信息,用户根本接触不到。数据库授权命令可以限制用户的操作权限,但不能限制到特定行和列上。使用视图后,可以简单方便地将用户的权限限制到特定的行和列上。这样可以保证敏感信息不会被没有权限的人看到,可以保证一些机密信息的安全。
3.提高表的逻辑独立性
视图可以屏蔽原有表结构变化带来的影响。例如,原有表增加列和删除未被引用的列,对视图不会造成影响。同样,如果修改了表中的某些列,可以使用修改视图来解决这些列带来的影响。
2、创建视图
创建视图是指在已存在的数据库表上建立视图。视图可以建立在一张表中,也可以建立在多张表中。
2.1 创建视图的语法形式
MySQL中,创建视图是通过SQL语句CREATE VIEW实现的。其语法形式如下:
create [algorithm = {undefined | merge | temptable}]
view 视图名 [(属性清单)]
as select 语句
[with [cascaded | local] check option];
其中,ALGORITHM是可选参数,表示视图选择的算法;“视图名”参数表示要创建的视图的名称;“属性清单”是可选参数,其指定了视图中各个属性的名词,默认情况下与SELECT语句中查询的属性相同;SELECT语句参数是一个完整的查询语句,表示从某个表中查出某些满足条件的记录,将这些记录导入视图中;WITH CHECK OPTION是可选参数,表示更新视图时要保证在该视图的权限范围之内。
ALGORITHM包括3个选项UNDEFINED、MERGE和 TEMPTABLE。其中,UNDEFINED选项表示 MySQL将自动选择所要使用的算法;MERGE选项表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分;TEMPTABLE选项表示将视图的结果存入临时表,然后使用临时表执行语句。
CASCADED是可选参数,表示更新视图时要满足所有相关视图和表的条件,该参数为默认值;LOCAL表示更新视图时,要满足该视图本身的定义的条件即可。
使用CREATE VIEW语句创建视图时,最好加上 WITH CHECK OPTION参数。而且,最好加上CASCADED参数。这样,从视图上派生出来的新视图后,更新新视图需要考虑其父视图的约束条件。这种方式比较严格,可以保证数据的安全性。
创建视图时,需要有CREATE VIEW的权限。同时,应该具有查询涉及的列的SELECT权限。在 MySQL 数据库下面的user表中保存这些权限信息,可以使用SELECT 语句查询。
SELECT语句查询的方式如下:
其中,Select _priv属性表示用户是否具有SELECT权限,Y表示拥有SELECT权限,N表示没有;Create_view_priv属性表示用户是否具有CREATE VIEW权限;mysql.user表示 MySQL数据库下面的user表;“用户名”参数表示要查询哪个用户是否拥有 DROP权限,该参数需要单引号引起来。因为该数据库系统中只有root用户,所以查询出来的结果只有root用户的权限。
2.2 在单表上创建视图
MySQL中可以在单个表上创建视图。
下面在 department表上创建一个简单的视图,视图名称为department_view1。创建视图的代码如下:
执行结果显示Query OK,表示代码执行成功;0 rows affected表示创建视图并不影响以前的数据,因为视图只是一个虚拟表。使用DESC语句查询表的结构,结果显示如下:
下面在 department表上创建一个名为 department_view2的视图。创建视图的代码如下:
结果显示,视图department_view2的属性分别为name、fuction和 location。因为,在创建视图时指定了属性列表。视图的属性名与属性列表中的属性名相同。该示例中的SELECT语句查询出了department 表的d_name、function和 address 这3列。那么,视图department_view2中的列就分别对应着这3列。使用视图时,用户接触不到实际操作的表和字段。这样可以保证数据库的安全。
2.3 在多表上创建视图
MySQL中也可以在两个或两个以上的表上创建视图,也是使用CREATE VIEW语句实现的。
下面在 department表和worker表上创建一个名为 worker_view1 的视图。创建视图的代码如下:
结果显示,视图 worker_view1 的属性分别为name、department、sex、age和 location。视图指定的属性列表对应着两个不同的表的属性列。视图的属性名与属性列表中的属性名相同。该示例中的SELECT 语句查询出了department 表的d_name字段,还有worker表的name、sex、birthday和 address。其中,department表的d_name字段对应视图的department字段; worker表的 birthday 字段进行减法操作后,对应视图的age字段。而且,视图worker_view1的ALGORITHM的值指定为MERGE。还增加了WITH LOCAL CHECKOPTION约束。本实例说明,视图可以将多个表上的操作简洁的表示出来。
同时在多个表上创建视图是非常有用的。比如,系统中有student表、department表、score表和grade表,分别存储学生的信息、院系信息、课程信息和成绩信息。可以在这4个表上创建一个视图,用来显示学生姓名、学号、班级、院系、所选课程和课程成绩。
3、查看视图
查看视图是指查看数据库中已存在的视图的定义。查看视图必须要有SHOW VIEW的权限。MySQL 数据库下的user表中保存着这个信息。查看视图的方法包括DESCRIBE 语句、SHOW TABLE STATUS语句、SHOW CREATE VIEW语句和查询information_schema数据库下的views表等。
3.1 DESCRIBE语句查看视图基本信息
因为,视图也是一张表。只是这张表比较特殊,是一张虚拟的表。因此,同样可以使用DESCRIBE语句可以用来查看视图的基本定义。DESCRIBE 语句查看视图的基本形式与查看表的形式是一样的。基本形式如下:
describe 视图名
下面是用DESCRIBE语句查看视图worker_view1的定义,代码如下:
结果中显示了字段的名称(Field)、数据类型(Type)、是否为空(Null)、是否为主外键(Key)、默认值(Default)和额外信息(Extra) 。
DESCRIBE可以缩写成DESC。可以直接使用DESC查看worker_view1表的结构。
如果只需要了解视图中的各个字段的简单信息,可以使用 DESCRIBE语句。DESCRIBE语句查看视图的方式与查看普通表的方式是一样的,结果显示的方式也是一样的。通常情况下,都是使用DESC代替DESCRIBE。
3.2 SHOW TABLE STATUS语句查看视图基本信息
在MySQL 中,可以使用SHOW TABLE STATUS语句来查看视图的信息。其语法形式如下:
show table status like '视图名'
下面是用SHOW TABLE STATUS语句查看视图'worker_view1"的信息,代码如下:
执行结果显示,表的说明(Comment〉项的值为VIEW,说明该表为视图。存储引擎、数据长度等信息都显示为NULL,说明视图是虚拟表,与普通表是有差异的。同样使用SHOW TABLE STATUS语句来查看department表的信息。查询结果如下:
从结果可以看出,department表的基本信息都显示出来,包括存储引擎、创建时间等。但是Comment项没有信息。这就是视图和普通表最直接的区别。
!!!SHOW TABLE STATUS语句虽然也可以查看视图的基本信息,但是通常很少使用。因为,使用SHOW TABLE STATUS语句查询视图信息时,各个属性显示的值都是NULL。只有Comment属性显示值为VIEW。
3.3 SHOW CREATE VIEW语句查看视图详细信息
在 MySQL中,SHOW CREATE VIEW语句可以查看视图的详细定义。其语法形式如下:
show create view 视图名
下面是用SHOW CREATE VIEW语句查看视图worker_view1的信息,代码如下:
执行结果显示了详细的信息。包括视图的各个属性、WITH LOCAL CHECK OPTION条件和字符编码(character_set_client)等信息。通过SHOW CREATE VIEW语句,可以查看视图的所有信息。
3.4 在views表中查看视图详细信息
在MySQL中,所有视图的定义都存在information_schema数据库下的views表中。查询views表,可以查看到数据库中所有视图的详细信息。查询的语句如下:
SHOW CREATE VIEW语句可以查看视图的详细信息,如果读者希望了解详细信息可以使用这个语句。所有视图的定义都是存储在information_schema数据库下的views表中,也可以在这个表中查看视图的定义。不过,通常情况下都是使用SHOW CREATE VIEW语句。
4、修改视图
修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时,可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACEVIEW语句和ALTER语句来修改视图。
4.1 CREATE OR REPLACE VIEW语句修改视图
在MySQL 中,CREATE OR REPLACE VIEW语句可以用来修改视图。该语句的使用非常灵活。在视图已经存在的情况下,对视图进行修改;视图不存在时,可以创建视图。CREATE OR REPLACE VIEW语句的语法形式如下:
create or replace [algorithm = { undefine | merge | temptable }]
view 视图名 [(属性清单)]
as select 语句
[with [cascaded | local] check option];
下面是用CREATE OR REPLACE VIEW语句修改视图 department_view1。代码如下:
CREATE OR REPLACE VIEW语句不仅可以修改已经存在的视图,也可以创建新的视图。不过ALTER语句只能修改已经存在的视图。通常情况下,最好选择CREATE OR REPLACE VIEW语句修改视图。
4.2 ALTER语句修改视图
在 MySQL中,ALTER 语句可以修改表的定义,可以创建索引。不仅如此,ALTER语句还可以用来修改视图。ALTER语句修改视图的语法格式如下:
alter [algorithm = { undefine | merge | temptable }]
view 视图名 [(属性清单)]
as select 语句
[with [cascaded | local] check option];
下面是用ALTER语句修改视图 department_view2。代码如下:
5、更新视图
更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚拟表,其中没有数据。通过视图更新时,都是转换到基本表来更新。更新视图时,只能更新权限范围内的数据。超出了范围,就不能更新。
下面在视图 department_view3中对视图进行更新。department_view3是department表的视图。department表的记录如下:
在更新之前,先创建视图 department_view3。代码如下:
向视图department_view3中更新一条记录。新记录的name的值为“人事部”,function的值为“管理公司人事变动”,address 的值为“2号楼3层”。更新语句执行如下:
结果显示,视图已经更新成功。再查询department表的记录。记录显示如下:
结果显示,d_id为1001的记录已经更新。虽然,UPDATE语句更新的是视图department_view3。但实际上更新的是department表。上面的UPDATE语句可以等价为:
update department set d_name='人事部',function='管理公司人事变动',address=‘2号楼3层’
where d_id = 1001;
由上面可以看出,对视图的更新最后都是实现在基本表上的。更新视图时,实际上更新的是基本表上的记录。但是,并不是所有的视图都可以更新的。
以下这几种情况是不能更新视图的:
(1) 视图中包含 SUM()、COUNT()、MAX()和MIN()等函数。
下面视图 worker_view4创建视图的代码如下:
因为该视图包含COUNT(),所以该视图是不能更新的。
(2) 视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVIG等关键字。
下面视图 worker_view5创建视图的代码如下:
因为该视图包含GROUP BY,所以该视图也是不能更新的。
(3) 常量视图。
下面视图 worker_view6创建视图的代码如下:
因为该视图的name字段是个字符串常量“Aric”,所以该视图也是不能更新的。使用UPDATE语句更新时,会出现系统报错。
(4) 视图中的SELECT中包含子查询。
下面视图worker_view7创建视图的代码如下:
该视图包含了子查询,因此也是不能更新的。
(5) 由不可更新的视图导出的视图。
下面视图 worker_view8创建视图的代码如下:
因为 worker_view7是不可更新的视图,所以 worker_view8也是不可以更新的视图。使用UPDATE语句更新时,会出现系统报错.
(6) 创建视图时,ALGORITHM为TEMPTABLE类型。
下面视图worker_view9创建视图的代码如下:
因为该视图的ALGORITHM为TEMPTABLE类型,所以worker_view9为不可以更新的视图。TEMPTABLE类型就是临时表类型。系统默认临时表是不能更新的。
(7)视图对应的表上存在没有默认值的列,而且该列没有包含在视图里。
例如,表中包含的 name字段没有默认值,但是视图中不包括该字段。那么这个视图是不能更新的。因为,在更新视图时,这个没有默认值的记录将没有值插入,也没有NULL值插入。数据库系统是不会允许这样的情况出现的,其会阻止这个视图更新。
!!!视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据。因为,使用视图更新数据时,如果没有全面考虑在视图中更新数据的限制,可能会造成数据更新失败。
除了上述条件不能更新视图以外,WITH [ CASCADED| LOCAL]CHECK OPTION也将决定视图能否更新。LOCAL参数表示更新视图时要满足该视图本身的定义的条件即可;CASCADED 参数表示更新视图时要满足所有相关视图和表的条件。没有指明时,默认为CASCADED。
6、删除视图
删除视图是指删除数据库中已存在的视图。删除视图时,只能删除视图的定义,不会删除数据。MySQL 中,使用DROP VIEW语句来删除视图。但是,用户必须拥有DROP权限。
对需要删除的视图,使用DROP VIEW语句进行删除。基本形式如下:
drop view if exists worker_view1;
其中,IF EXISTS参数指判断视图存在,如果存在则执行,不存在则不执行;“视图名列表”参数表示要删除的视图的名称的列表,各个视图名称之间用逗号隔开。
下面将删除视图worker_view1。代码如下:
下面将同时删除 department_view1和 department_view2这两个视图。代码如下:
结果显示,department_view1 和 department_view2这两个视图已经不存在了。该示例说明,DROP语句可以同时删除多个视图。
用户必须拥有DROP权限才可以删除视图。MySQL 中,MySQL数据库下的user表中可以查询到是否存在 DROP权限。查看DROP权限的语句如下:
SELECT Drop_priv FROM mysql.user WHERE user='用户名;
其中,“Drop_priv”属性表示用户是否具有DROP权限,Y表示拥有DROP权限,N表示没有;“用户名”参数表示要查询哪个用户是否拥有DROP权限,该参数需要单引号引起来。因为该数据库系统中只有root用户,所以查询出来的结果只有root用户的权限。
该语句的执行结果如下:
结果显示,“Drop_priv”属性的值为Y,表示具有DROP权限。
7、MySQL中视图和表的区别及联系是什么?
两者的区别:
- 视图是按照SQL语句生成的一个虚拟的表。
- 视图不占实际的物理空间。而表中的记录需要占物理空间。
- 建立和删除视图只影响视图本身,不会影响实际的记录。而建立和删除表会影响实际的记录。
两者的联系:
- 视图是在基本表之上建立的表,其字段和记录都来自基本表,其依赖基本表而存在。
- 一个视图可以对应一个基本表,也可以对应多个基本表。
- 视图是基本表的抽象,在逻辑意义上建立的新关系。