MySQL之数据视图

视图概述：

        视图是从一个或多个表（或视图）导出的表。视图与表（有时为与视图区别，也称表为基本表）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

        视图一经定义，就可以像表一样被查询、修改、删除和更新。使用视图有下列优点：

1、为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。

2、屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。

3、简化用户权限的管理。只须授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。

4、便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。

5、可以重新组织数据以便输出到其他应用程序中。

使用视图时，要注意下列事项：

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

2、视图的命名必须遵循标志符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。

3、不能把规则、默认值或触发器与视图相关联

4、不能在视图上建立任何索引，包括全文索引

5、视图中使用SELECT语句有以下的限制：

        5.1、定义视图的用户必须对所参照的表或视图有查询（即可执行SELECT语句）的权限，在定义中引用的表或视图必须存在

        5.2、不能包含FROM子句中的子查询，不能引用系统或用户变量，不能引用预处理语句参数

        5.3、在视图定义中允许使用ORDER BY子句，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，则视图定义中的ORDER BY将被忽略。

1、创建视图

 视图的创建语法格式：

CREATE [ OR REPLACE ] VIEW 视图名 [ ( 列名列表 ) ]

AS SELECT 语句

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

例：创建Bookstore数据库上的jsj_sell视图，包括计算机类图书销售的订单号、图书编号、书名等情况。其中要保证对该视图的订单修改都要符合计算机类这个条件 

CREATE OR REPLACE VIEW jsj_sell
AS
SELECT 订单号,Sell.图书编号,书名
FROM Book,Sell
WHERE Book.图书编号=Sell.图书编号
AND Book.图书类别='计算机'
WITH CHECK OPTION;

例：创建Bookstore数据库中计算机类图书销售视图sale_avg，包括书名（在视图中列名为name）和该图书的平均订购册数（在视图中列名为sale_avg） 

CREATE VIEW sale_avg(name,sale_avg)
AS 
SELECT 书名,avg(订购册数)
FROM jsj_sell
GROUP BY 书名;

 上面创建了计算机类图书销售视图jsj_sell，可以直接从jsj_sell视图中查询信息生成新视图。

1.2、视图的查询

视图定义后，就可以像查询基本表那样对视图进行查询

例：在视图jsj_sell中查找计算机类图书的订单号和订购册数

SELECT 订单号,订购册数
FROM jsj_sell;

 例：查找平均订购册数大于5本的订购客户的身份证号和平均订购册数

1、创建客户平均订购视图kh_avg，包括客户身份证号和平均订购册数

CREATE VIEW kh_avg(sfz,order_avg)
AS
SELECT 身份证号,AVG(订购册数)
FROM Sell
GROUP BY 身份证号;

2、对kh_avg视图进行查询

SELECT *
FROM kh_avg
WHERE order_avg>5;

 注意：使用视图查询时，若其关联的基本表中添加了新字段，则该视图将不包含新字段。如果与视图相关联的表或视图被删除，则该视图将不能再使用。

 2、操作视图

        2.1、通过视图操作数据

2.1.1、可更新视图

​​​​​​​        要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。有一些特定的其他结构，这类结构会使得视图不可更新。若视图包含以下结构中的任何一种，其就是不可更新的：

1、聚合函数

2、DISTINCT关键字

3、GROUP BY子句

4、ORDER BY子句

5、HAVING子句

6、UNION运算符

7、位于选择列表中的子查询

8、FROM子句中包含多个表

9、SELECT语句中引用了不可更新视图

10、WHERE子句中的子查询，引用FROM子句中的表

 2.1.2、插入数据

        当使用视图插入数据时，如果在创建视图时加上WITH CHECK OPTION子句，WITH CHECK OPTION子句会在更新数据时检查新数据是否符合视图定义中WHERE子句的条件。

        WITH CHECK OPTION子句只能和可更新视图一起使用。

例：创建视图jsj_book，视图中包含计算机类图书的信息，并向jsj_book视图中插入一条记录：（'IS-01'，'计算机'，'计算机基础'）

1、创建视图jsj_book 

CREATE OR REPLACE VIEW jsj_book
AS
SELECT *
FROM Book
WHERE 图书类别='计算机'
WITH CHECK OPTION;

2、插入记录 

INSERT INTO jsj_book
VALUES('IS-01','计算机','计算机基础');

当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。

对INSERT语句还有一个限制：SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。例如，若jsj_book视图定义时不加上“书名”字段，则插入数据时会出错。

 2.1.3、修改数据

        使用UPDATE语句可以实现通过视图修改基本表数据。

例：将jsj_sell视图中的图书编号为IS-01的书名改为“MySQL基础”

UPDATE jsj_sell
SET 书名='MySQL基础'
WHERE 图书编号='IS-01';

 2.1.4、删除数据

        如果视图来源于单个基本表，可以使用DELETE语句通过视图来删除基本表数据，对于依赖多个基本表的视图，不能使用DELETE语句。

例：删除jsj_book中“中国青年出版社”的记录

DELETE FROM jsj_book
WHERE 出版社='中国青年出版社';

 2.2、修改视图定义

可以使用ALTER VIEW语句对已有视图的定义进行修改

语法格式：ALTER VIEW 视图名[ ( 列名列表 ) ]

AS select语句

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

例：将jsj_book视图修改为只包含计算机类图书的图书编号、书名和单价

ALTER VIEW jsj_book
AS
SELECT 图书编号,书名,单价
FROM Book
WHERE 图书类别='计算机';

 2.3、删除视图

语法格式：DROP VIEW [ IF EXISTS ] 视图名1 [, 视图名2 ]...

若声明了IF EXISTS，则视图不存在的话也不会报错。使用DROP VIEW 一次可以删除多个视图。例：一次性删除jsj_book和jsj_sell

DROP VIEW jsj_book,jsj_sell;