跳至主要內容

MySQL视图

大约 8 分钟约 2356 字

视图是从一个表或多个表中导出的表,是一种虚拟的表。视图就像一个窗口,通过这个窗口就可以看到系统专门提供的数据。这样,用户就不需要查看整个数据表中的数据,而只关心对自己有用的数据。视图可以使用户的操作更加方便,而且可以保障数据库系统的安全性。

视图概述

视图是一个虚拟表,是从数据库中的一个或多个表中导出来的表,其内容由查询语句定义。同真实的表一样,视图包含一系列的行列数据。但是,数据库中只存放了视图的定义,而没有存放视图中的数据。这些数据还存在原来的表中。使用视图查询数据时,数据库系统会在原来的表中取出对应的数据。因此,视图的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变,显示在视图中的数据也会发生改变。

视图是保存在数据库中的查询SQL语句,它的存在主要出于两个原因:首先是安全原因,视图可以隐藏一些数据,例如,它可以用视图显示员工信息表中的姓名、工龄、地址而不显示社会保险号和工资等;另一个原因是它可使复杂的查询易于理解和使用。

对所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表,或者其他视图。通过视图进行查询没有任何限制,通过视图修改数据时的限制也很少,视图的作用可以总结为以下几点:

1. 简单性

看到的就是需要的。视图不仅可以简化用户对数据的理解,也可以简化他们的操作。可以将经常使用的查询定义为视图,这样用户就不必为以后的操作每次都指定全部的条件。

2. 安全性

视图的安全性可以防止未授权用户查看特定的行或列,使有权限的用户只能看到表中特定行的方法如下:

(1) 在表中增加一个标志用户名的列。

(2) 建立视图,使用户只能看到标有自己用户名的行。

(3) 把视图授权给其他用户。

3. 逻辑数据安全性

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图,程序一定是建立在表上的。有了视图之后,程序可以建立在试图之上,从而使程序与数据库表被视图分割开来。视图可以在以下几个方面与数据独立。

(1) 如果应用建立在数据库表上,当数据库表发生变化时,可以在表上建立视图,通过视图屏蔽表的变化,从而使应用程序不动。

(2) 如果应用建立在数据库表上,当应用发生变化时,可以在表上创建视图,通过视图屏蔽应用的变化,从而使数据库表不动。

(3) 如果应用建立在视图上,当数据库表发生变化时,可以在表上修改视图,通过视图屏蔽表的变化,从而使应用程序不动。

(4) 如果应用建立在视图上,当数据库表发生变化时,可以在表上修改视图,通过视图屏蔽表的变化,从而使应用程序不动。

创建视图

查看创建视图的权限

查询是否具有操作视图的权限由Create View语句和select语句实现,代码如下:

SELECT Selete_priv,Create_view_priv FROM mysql.user WHERE user = '用户名';

其中Selete_priv代表用户是否具有SELECT权限,Y表示具有SELECT权限,N表示没有。

Create_view_priv代表是否具有CREATE VIEW的权限,mysql.user表示查看mysql下的user表。

如我们查看管理员root是否具有这两个权限:

select select_priv,create_view_priv from mysql.user where user = 'root';
运行结果
运行结果

结果中select_priv和create_view_priv权限都为Y,表示root具有这两个权限。

创建视图

创建视图的语法如下:

CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION];

ALGORITHM:表示视图选择的算法。

(属性清单):可选参数,指定视图中各个属性的名词,默认情况下与SELECT语句中查询的属性相同。

SELECT语句:一个完整的查询语句,表示从某个表中查出满足条件的记录,将这些记录导入视图中。

WITH...:可选参数,表示更新视图时要保证在该视图的权限范围之内。

创建视图的注意事项:

  • 运行创建视图的语句需要用户具有CREATE_VIEW的权限
  • SELECT语句不能包含FROM字句中的子查询
  • 在定义中引用的表或者视图必须存在
  • 在定义中不能使用多个temporary表,不能创建temporary视图
  • 在视图中允许定义ORDER BY,但是若从特定视图中选择,而该视图中使用了具有自己定义的ORDER BY的语句,它将被忽略

如我们为一个员工信息表创建视图。

在创建视图之前先创建数据表。

第一个数据表为员工信息表,此表中的id代表编号,name代表员工姓名,sex代表员工性别,salary代表员工工资,section代表员工部门,telephone代表员工电话号码,number代表员工编号。

运行结果
运行结果

在此数据表中,我们设定工资、部门、员工编号为隐私信息,那么我们就可以创建一个只显示字段id、name、sex、telephone的视图。

create view v_employees (id,name,sex,tele) as select id,name,sex,telephone from employees;
运行结果
运行结果

到这里视图创建成功。

视图操作

查看视图

查看视图指查看数据库中存在的视图,需要具有SHOW VIEW权限。查看视图的方法如下。

DESCRIBE 视图名;
DESC 视图名;
SHOW TABLE STATUS LIKE '视图名';
SHOW CREATE VIEW 视图名;

以上四种方法都可以查看已创建的视图,下面都进行演示。

desc v_employees;
运行结果
运行结果
show table status like 'v_employees';
运行结果
运行结果
show create view v_employees;
运行结果
运行结果

修改视图

修改视图指的是修改数据库中已存在的表的定义。当基本的表字段发生改变时,可以通过修改视图来保持视图和表保持一致。修改视图的语法如下:

CREATE OR REPLACE VIEW [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图[(属性清单)] AS SELECT语句 [WITH [CASCADE|LOCAL] CHECK OPTION];
ALTER [algorithm={undefined|merge|template}] VIEW view_name [(column_list)] AS select_statement [with [cascaded|local] CHECK OPTION];

若在创建视图时使用了WITH CHECK OPTION、WITH ENCRYPTION、WITH SCHEMABING或VIEW_METADATA选项,修改视图时项保存这些选项提供的功能,必须在ALTER VIEW语句中将它们包括进去。

下面分别使用这两个语句修改上文创建的视图,这里我们先使用CREATE OR REPLACE VIEW语句删除字段tele。

create or replace view v_employees (id,name,sex) as select id,name,sex from employees;
运行结果
运行结果

再使用ALTER VIEW重新添加字段tele。

alter view v_employees (id,name,sex,tele) as select id,name,sex,telephone from employees;
运行结果
运行结果

更新视图

更新视图指对表的更新。但是视图有很多限制条件所以我们一般不更新视图。更新视图的语法如下:

UPDATE view_name SET column_name=value ... [WHERE子句];

下面演示如何通过视图更改表的数据,首先看一下视图和表的数据。

运行结果
运行结果
运行结果
运行结果

然后我们输入下面的语句,然后再查看视图和表的数据。

update v_employees set tele='30003' where id='3';
运行结果
运行结果

可见数据被成功修改了。

但是若有以下几种情况是不能更新视图的:

  1. 视图中包含COUNT()、SUM()、MAX()和MIN()等函数;
  2. 视图中包含UNION、UNION ALL、DINTINCT、GROUP BY、HAVIG等关键字;
  3. 常量视图,如create view view_book as select 'ansheng' as ansheng;
  4. 视图中SELECT包含子查询;
  5. 由不可更新的视图导出的视图,如create view view_book as select * from book_view1;
  6. 创建视图时ALGORITHM为TMPLTABLE类型;
  7. 视图对应的表存在没有默认值的列,而且该列没有包含在视图里。

删除视图

语法如下:

DROP VIEW IF EXISTS <视图名> [RESTRICT|CASCADE];

该语句从数据字典中删除指定的视图定义;若该视图导出了其他视图,则使用CASCADE级联删除,或者先显式删除导出的视图,再删除该视图;删除基表时,由该基表导出的所有视图必须显式删除。

如我们删除上面创建的v_employees视图。

drop view id exists v_employees;
运行结果
运行结果

由于视图是虚拟表,因此查询视图的语法和查询普通表相同,如下图。

运行结果
运行结果
上次编辑于:
贡献者: 棋.