mysql 视图

        学习了mysql 存储过程和函数，继续学习mysql视图

1，视图概述

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

1，视图的含义

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

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

        下面有个student 表和 stu_info表，在 student表中包含了学生的id号和姓名，stu_info表中包含了学生的id号、班级和家庭住址，而现在公布分班信息，只需要id号、姓名和班级，这该如何解决?通过学习后面的内容就可以找到完美的解决方案。

表设计如下：

CREATE TABLE student(
  s_id INT,
  name VARCHAR (40)
);
 
 
CREATE TABLE stu_info(
  s_id INT,
  glass VARCHAR(40),
  addr VARCHAR(90)
);

        通过DESC命令可以查看表的设计，可以获得字段、字段的定义、是否为主键、是否为空、默认值和扩展信息。

        视图提供了一个很好的解决方法，创建视图的信息来自表的部分信息，只取需要的信息。这样既能满足要求也不破坏表原来的结构。

2，视图的作用

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

1，简单化

        看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2，安全性

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

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

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

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

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

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

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

3，逻辑数据独立性

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

2，创建视图

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

1，创建视图的语法形式

        创建视图使用CREATE VIEW语句,基本语法格式如下：

CREATE [OR  REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

        其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；ALGORITHM表示视图选择的算法；view_name为视图的名称，column_list 为属性列;SELECT_statement表示SELECT 语句；WITH [CASCADED|LOCAL] CHECK OPTION参数表示视图在更新时保证在视图的权限范围之内。

        ALGORITHM的取值有3个，分别是UNDEFINED |MERGE | TEMPTABLE,UNDEFINED表示MySQL将自动选择算法；MERGE 表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句。

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

        该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT 语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。

        视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确

创建视图，创建时应将名称指定为db_name.view_name.

2，在单表上创建视图

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

【例1】在t表上创建一个名为view_t的视图。首先创建基本表并插入数据，语句如下：

CREATE TABLE t(quantity INT, price INT);
INSERT INTO t VALUES (3,50);

创建视图语句为：

CREATE VIEW view_t AS SELECT quantity, price, quantity *price FROM t; 

查询语句：

 SELECT * FROM view_t;

        默认情况下创建的视图和基本表的字段是一样的也可以通过指定视图字段的名称来创建视图。

        【例2】在t表格上创建一个名为view_t2的视图，代码如下：

CREATE VIEW  view_t2(qty, price,total) AS 
SELECT quantity,price,quantity*price FROM t;

语句执行成功,查看view_t2视图中的数据：

SELECT * FROM view_t2;

        可以看到，view_t2和 view_t两个视图中字段名称不同，但数据却是相同的。因此，在使用视图的时候，可能用户根本就不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。

3，在多表上创建视图

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

        【例3】在表student和表stu_info上创建视图stu_glass，代码如下：

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

 INSERT INTO student VALUES
 (1, 'wanglin1'),
 (2, 'gaoli'),
 (3,'zhanghai');
 
INSERT INTO stu_info VALUES 
(1, 'wuban', 'henan '), 
(2, 'liuban', 'hebei'),
(3, 'qiban', 'shandong');

创建视图stu_glass，语句如下：

CREATE VIEW stu_glass(id, NAME,glass) AS SELECT student.s_id, 
student.name, stu_info.glass
FROM student, stu_info WHERE student.s_id=stu_info.s_id;

代码的执行如下：

 SELECT * FROM stu_glass;

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

3，查看视图

        查看视图是查看数据库中已存在的视图的定义。查看视图必须要有SHOW VIEW的权限，MySQL 数据库下的user表中保存着这个信息。查看视图的方法包括：DESCRIBE、SHOWTABLE STATUS和 SHOW CREATE VIEW。

1，使用DESCRIBE语句查看视图基本信息

        DESCRIBE可以用来查看视图,具体的语法如下：

DESCRIBE 视图名;

        【例4】通过DESCRIBE语句查看视图view_t的定义，代码如下：

DESCRIBE view_t;

        代码执行结果如下：

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

        DESCRIBE一般情况下都简写成 DESC，输入这个命令的执行结果和输入DESCRIBE的执行结果是一样的。

2，使用SHOW TABLE STATUS语句查看视图基本信息

        查看视图的信息可以通过SHOW TABLE STATUS的方法，具体的语法如下：

SHOW TABLE STATUS LIKE '视图名';

        【例5】下面将通过一个例子来学习使用SHOW TABLE STATUS命令查看视图信息，代码如下：

SHOW TABLE STATUS LIKE 'view_t';

执行结果如下：

        执行结果显示，表的说明Comment的值为VIEW说明该表为视图，其他的信息为NULL说明这是一个虚表。用同样的语句来查看一下数据表t 的信息，执行结果如下：

SHOW TABLE STATUS LIKE 't';

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

3，使用SHOW CREATE VIEW语句查看视图详细信息

        使用SHOW CREATE VIEW语句可以查看视图详细定义，语法如下：

SHOW CREATE VIEW 视图名;

        【例6】SHOW CREATE VIEW查看视图的详细定义，代码如下：

SHOW  CREATE VIEW view_t;

        执行结果如下：

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

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

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

SELECT * FROM information_schema.views;

        【例7】在views表中查看视图的详细定义，代码如下：

 SELECT * FROM information_schema.views;

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

4，修改视图

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

1，使用CREATE OR REPLACE VIEW语句修改视图

MySQL中如果要修改视图，使用CREATE OR REPLACE VIEW语句，语法如下：

CREATE [OR REPLACE] [ALGORITHM =(UNDEFINED | MERGE |TEMPTABLE}]
VIEW view_name [(column_list)]
AS SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OFTION]

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

        【例8】修改视图view_t，代码如下：

CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;

执行的结果如下：

DESC view_t;

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

2，使用ALTER语句修改视图

        ALTER语句是MySQL提供的另外一种修改视图的方法，语法如下：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE }]
VIEW view_name [(column_list)]
AS SELECT statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

        这个语法中的关键字和前面视图的关键字是一样的，这里就不再介绍。

        【例9】使用ALTER语句修改视图view_t，代码如下：

ALTER VIEW view_t AS SELECT quantity FROM t;

        执行结果如下：

DESC view_t;

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

5，更新视图

        更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。视图更新的3种方法：INSERT、UPDATE和DELETE。

        【例10】使用UPDATE语句更新视图view_t，代码如下：

-- UPDATE view_t SET quantity = 5;

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

SELECT * FROM t;

SELECT * FROM view_t;

SELECT* FROM view_t2;

        使用UPDATE语句更新视图view_t，执行过程如下：

UPDATE view_t SET quantity=5;

        查看视图更新之后，基本表的内容：

SELECT * FROM t;

SELECT * FROM view_t;

SELECT* FROM view_t2;

        对视图view_t更新后，基本表t的内容也更新了，同样当对基本表t更新后，另外一个视图view_t2中的内容也会更新。

        【例11】使用INSERT语句在基本表t中插入一条记录，代码如下：

INSERT INTO t VALUES (3,5);

        执行结果如下：

SELECT * FROM t;

SELECT * FROM view_t2;

        向表t中插入一条记录，通过SELECT查看表t和视图view_t2，可以看到其中的内容也跟着更新，视图更新的不仅仅是数量和单价，总价也会更新。

        【例12】使用DELETE语句删除视图view_t2中的一条记录，代码如下：

DELETE FROM view_t2 WHERE price = 5;

执行结果如下：

SELECT * FROM t;

SELECT * FROM view_t2;

        在视图view_t2中删除 price=5的记录，视图中的删除操作最终是通过删除基本表中相关的记录实现的，查看删除操作之后的表t和视图 view_t2，可以看到通过视图删除其所依赖的基本表中的数据。

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

        （1）视图中不包含基表中被定义为非空的列。

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

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

        （4）在定义视图的SELECT语句中使用了DISTINCT，UNION，TOP，GROUP BY或HAVING子句。

6，删除视图

        当视图不再需要时，可以将其删除，删除一个或多个视图可以使用DROP VIEW语句,语法如下：

DROP VIEW [IF EXISTS]
view_name [, view__name][RESTRICT | CASCADE]

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

        【例13】删除stu_glass视图，代码如下：

DROP VIEW IF EXISTS stu_glass;

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

 SHOW CREATE VIEW stuglass;

ERROR 1146(42502)：Table 'chapter11db.stu_glass' doesn't exist可以看到，stu _glass视图已经不存在,删除成功。

7，疑问解答

疑问：MySQL中视图和表的区别以及联系是什么?

1，两者的区别

（1）视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。

（2）视图没有实际的物理记录，而表有。

（3）表是内容，视图是窗口。

（4）表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改。

（5）视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL

语句的集合。从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。

（6）表属于全局模式中的表，是实表;视图属于局部模式的表，是虚表。

（7）视图的建立和删除只影响视图本身，不影响对应的基本表。

2，两者的联系

        视图(view)是在基本表之上建立的表，它的结构（即所定义的列)和内容(即所有记录)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

总结：

        视图这个，了解用法和注意的地方:

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

        （1）视图中不包含基表中被定义为非空的列。

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

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

        （4）在定义视图的SELECT语句中使用了DISTINCT，UNION，TOP，GROUP BY或HAVING子句。

        感觉平时用视图的地方不多，很多都是用报表去处理。

        上一篇： 《mysql 存储过程和函数》

        下一篇： 《》