MySQL数据库应用总结—MySQL数据库视图的操作

SQL语法预览：

创建视图：【create [or replace] [algorithm=] view 视图名称(属性列) as select 字段名称 from 表名[with[cascaded local] check option];】

查看视图：【describe 视图名;】【show table status like '视图名'\G;】【show create view 视图名;】【select * from information_schema.views;】

修改视图：【create [or replace] [algorithm=] view 视图名称(属性列) as select 字段名称 from 表名[with[cascaded local] check option];】【alter

[algorithm=] view 视图名称(属性列) as select 字段名称 from 表名[with[cascaded local] check option];】

更新视图：【update 视图名 set 视图字段名=值;】【insert into 表名 values(值，值…);】【delete from 视图名 where 视图字段=值;】

删除视图：【drop view if exists 视图名 [视图名2…] [restrict cascade];】

详解：

一、视图概述

视图是从一个或者多个表导出的，视图的行为与表非常相似，但视图是一个虚拟表。在视图中可以使用select语句查询数据，以及使用insert、update和delete语句修改记录。从MySQL5.0开始可以使用视图，视图可以使用户操作方便，而且可以保障数据库系统的安全。

1.视图的含义

视图是一个虚拟表是从数据库的一个或者多个表中导出来的表。视图还可以在已有的视图的基础上定义。

视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表发生变化，则这种变化可以自动地反映到视图中。

2.视图的作用

与直接从数据表中读取数据相比，视图有以下优点：

1.简单

视图中看到的就是需要的。视图不仅可以简化用户对大户家的理解，也可以简化他们的操作。经常使用的查询可以被定义为视图，从而可以免去为以后的操作每次指定全部条件的麻烦。

2.安全性高

通过视图用户只能查询和修改他们所看到的数据，数据库中的其他数据则即看不见也取不到。数据库授权命令可以将每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库的特定行和特定列上。通过视图，用户可以被在数据的不同子集上：

（1）使用权限可被限制在基本表的行的子集上。

（2）使用权限可被限制在基本表的列的子集上。

（3）使用权限可被限制在基本表的行和列的子集上。

（4）使用权限可被限制在多个基本表的连接所限制的行上。

（5）使用权限可被限制在基本表的数据的统计汇总上。

（6）使用权限可被限制在另一个视图的一个子集上，或是一些视图和基本表合并后的子集上。

3.逻辑数据独立

视图可以帮助用户屏蔽真是表的结构变化带来的影响。

二、创建视图

视图中包含select查询的结果，因此视图的创建基于select语句和已存在的数据表，视图可以建立在一张表上，也可以建立在多张表上。

1.创建视图的语法形式

创建视图使用create view语句，基本语法格式如下：

语法结构：create [orreplace][algorithm=]view视图名称(属性列) as select字段名称from表名[with[cascaded local]

check option];

其中，create表示创建新的视图；replace表示替换已经创建的视图；algorithm表示视图的算法；view_name表示视图的名称，column_list表示属性列；select_statement表示select语句：with[ cascaded local ]check option参数表示视图在更新时保证在视图的操作权限范围之内。

algorithm参数的取值有三个，分别是undefined、merge和temptable。undefined表示MySQL将自动选择算法；merge表示将使用的视图语句与视图定义合并，使得视图定义的某一部分取代语句对应的部分；temptable表示将视图的结果存入临时表，然后用临时表来执行语句。

cascaded与local为可选参数，cascaded为默认值。表示更新视图时要满足所有先关视图和表的条件；local表示更新视图时满足该视图本身定义的条件即可。

该语句要求具有针对视图的create view权限，以及针对由select语句选择的每一列是那个的某些权限。如果还有or replace子句，必须在视图上具有drop权限。

视图属于数据库。在默认情况下，将在当前数据库中创建新的视图。要想在给定的数据库中明确创建视图，创建时应将视图名称设置为db_name.view_name。

2.在单表上创建视图

在MySQL中，可以在单个数据表上创建视图。

案例：在数据库db_view中创建表t，在表t上创建一个名为view_t的视图。SQL语句如下：

命令语句：

create database db_view;

use db_view;

create table t(quantity int,price double);

insert into t values(3,50.0);

create view view_t as select quantity,price,quantity*price from t;

select * from view_t;

语句执行情况如下：

在默认情况下，创建的视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图。只需要在创建视图的时候指定视图字段就可以【create view view_t(quantity, price, total) as select quantity, price,quantity*price from t;】。这样字段不同，视图数据却相同，在使用视图的时候，可以在不了解基本表且不接触表中数据的情况下，查看所需要的数据，保证了数据库的安全。

3.在多表上创建视图

在MySQL中，也可以在两个或者两个以上的表上创建视图，可以使用create view语句实现。

案例：在表student和表stu_info上创建视图stu_glass。

首先向两个表中插入数据，输入的语句如下：

命令语句：

create table student (id int,name varchar(11));

create table stu_info(id int,glass varchar(11),location varchar(11));

insert into student values(1,'lifangyuan'),(2,'liumengyue'),(3,'lishemin');

insert into stu_info values(1,'wuban','yangling'),(2,'liuban','luonan'),(3,'qiban','luonan');

create view stu_glass(id,name,glass) as select student.id,student.name, stu_info.glass from student,stu_info where student.id=stu_info.id;

select * from stu_glass;

执行结果如下：

这个例子就解决了开始提出的问题，通过这个视图可以很好地保护基本表中的数据。这个视图中的信息很简单，包含了id、name和glass三个字段，id字段对应student表中的id字段，name字段对应student表中的name字段，glass字段对应stu_info表中的glass字段。

三、查看视图

查看视图是查看数据库中已有的视图的定义。查看视图必须要有show view的权限，MySQL数据库下的user表中保存着这个信息。查看视图的方法有describe、show table status和show create view，下面将介绍查看视图的各种方法。

1.用describe语句查看视图的基本信息

用describe语句查看视图的具体语法如下：

语法结构：describe视图名;

案例：通过describe语句查看视图view_t的定义，SQL语句如下：

命令语句：describe view_t;

执行结果：

执行结果显示出了视图的字段定义、字段的数据类型、是否为空、是否为主/外键、默认值和额外信息。

一般情况下，describe都可简写成desc，输入desc的执行结果和输入describe的执行结果是一样的。

2.用show table status语句查看视图的基本信息

用show table status语句查看视图的具体语法如下：

语法结构：show table status like '视图名'\G;

案例：用show table status语句查看视图view_t的信息，SQL语句如下：

命令语句：show table status like 'view_t';

执行结果：

执行结果显示，comment的值为view说明该表为视图，其他的信息也为null说明这是一个虚拟表。用同样的语句查看数据库表的信息，执行结果如下：

从查询结果来看，这里的信息包含存储引擎。创建时间等，comment的值为空，这就是视图和表的区别。

3.用show create view语句查看视图的详细信息

用show create view语句查看视图的详细定义，具体语法如下：

语法结构：show create view视图名;

案例：用show create view 语句查看视图view_t的信息，SQL语句如下：

命令语句：show create view view_t\G;

执行结果：

执行结果显示了视图的名称、创建视图的语句等信息。

4.在views表中查看视图的详细信息

在MySQL中，information_schema数据库下的view表中存储了所有视图的定义。通过对view表的查询，可以查看数据库中所有视图的详细信息，查询语句如下：

语法结构：select * from information_schema.views;

案例：在views表中查看视图详细定义，SQL语句如下：

命令语句：select * from information_schema.views\G;

执行结果：

查询的结果中显示了当前以及定义的所有视图的详细信息，在这里也可以看到前面定义的名为stu_glass和view_t的两个视图发的详细信息。

四、修改视图

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。在MySQL中，通过create or replace view语句和alter语句来修改视图。

1.用create or replace view 语句修改视图

在MySQL中如果要修改视图，可以使用create or replace view语句，语法格式如下：

语法结构：create [orreplace][algorithm=]view视图名称(属性列) as select字段名称from表名[with[cascaded local]

check option];

可以看到，修改视图的语句和创建视图的语句完全一样。当视图存在时，修改语句对视图进行修改；当视图不存在时，创建视图。下面通过一个实例来说明。

案例：修改视图view_t，SQL语句如下：

命令语句：create or replace view view_t as select * from t;

先用describe查看更改前的视图，以便于之后的视图进行对比，执行结果：

从执行的结果来看，相比原来的视图view_t，新的视图view_t少了一个字段。

2.用alter语句修改视图

用alter语句修改视图的语法格式如下：

语法结构：alter [algorithm=] view 视图名称(属性列) as select字段名称from表名[with[cascaded local] check option];

案例：使用alter语句修改视图，SQL语句如下：

命令语句：alter view view_t as select quantity from t;

执行结果如下：

通过alter语句同样可以达到修改视图view_t的目的，从上面的执行结果来看，视图view_t只剩下1个quantity字段，修改成功。

五、更新视图

更新视图是指通过视图来插入、个人能关心、删除表中的数据。视图是一个虚拟表，其中没有数据，通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。下面将介绍更新视图的三种方法：insert、update和delete。

1.使用update语句更新视图view_t

语法结构：update视图名set 视图字段名=值;

案例：使用update语句更新视图view_t，SQL语句如下：

命令语句：update view_t set quantity=5;

执行视图更新之前，查看基本表和视图的信息，执行结果如下：

对视图view_t更新后，基本表t的内容也更新了，并且基于表t的其他视图也会更新。

2.使用insert语句更新视图

语法结构：insert into 表名values(值，值…);

案例：使用insert语句在基本表t中插入一条记录，SQL语句如下：

命令语句：insert into t values(3,5);

执行结果如下：

向表t中插入一条记录，通过select查看表t和视图view_t，可以看到其中的内容也跟着更新了。

3.使用delete语句更新视图

语法结构：delete from 视图名where视图字段=值;

案例：使用delete语句删除视图view_t中的一条记录，SQL语句如下：

命令语句：delete from view_t where quantity=5;

执行结果如下：

从执行的结果来看，在视图view_t中删除了quantity=5的记录。因为视图中的删除操作最终是通过删除基本表中的相关记录实现的，所以查看删除操作之后的表t和视图view_t，可以看到通过视图删除了所依赖的基本表中的数据。

当视图中含有如下内容时，视图的更新操作将不能被执行：

（1）在定义视图的select语句后的字段列表中使用了数学表达式。

（2）在定义视图的select语句后的字段列表中使用了聚合函数。

（3）在定义视图的select语句中使用了distinct、union、top、group by或having。

六、删除视图

当视图不再需要时，可以将其删除，删除一个或多个需要删除的视图使用drop view语句，语法格式如下：

语法结构：drop view if exists视图名 [视图名2…] [restrict cascade];

其中，view_name是要删除的视图的名称，可以添加多个需要删除的视图名称，名称之间用逗号隔开。删除视图必须拥有drop权限。

案例：删除stu_glass视图，SQL语句如下：

命令语句：drop view if exists stu_glass;

如果名称为stu_glass的视图存在，该视图将被删除。使用show create view语句查看操作结果：

结果显示stu_glass视图不存在，删除成功。

SQL语法总结：

创建视图：【create [or replace] [algorithm=] view 视图名称(属性列) as select 字段名称 from 表名[with[cascaded local] check option];】

查看视图：【describe 视图名;】【show table status like'视图名'\G;】【show create view 视图名;】【select * from information_schema.views;】

修改视图：【create [or replace] [algorithm=] view 视图名称(属性列) as select 字段名称 from 表名[with[cascaded local]check option];】【alter[algorithm=] view 视图名称(属性列) as select 字段名称 from 表名[with[cascaded local] check option];】

更新视图：【update 视图名 set 视图字段名=值;】【insert into 表名 values(值，值…);】【delete from 视图名 where 视图字段=值;】

删除视图：【drop view if exists 视图名 [视图名2…][restrict cascade];】

-------------------《end》------------------