MySQL学习之使用视图-阿里云开发者社区

MySQL学习之使用视图

2017-08-26 1065

简介： 　　视图是虚拟的表，在数据库中并不真实存在，它只包含使用时动态检索数据的查询。MySQL从5.0.1 版本开始提供视图功能。　　视图相对于普通表的优点有：重用SQL语句，简化复杂的SQL操作。

+关注继续查看

　　视图是虚拟的表，在数据库中并不真实存在，它只包含使用时动态检索数据的查询。MySQL从5.0.1 版本开始提供视图功能。
　　视图相对于普通表的优点有：

重用SQL语句，简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
数据独立。一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

视图的基本操作有：创建或修改视图、删除视图，以及查看视图定义。

一、创建或修改视图操作

　　创建视图需要有CREATE VIEW 的权限，并且对于查询涉及的列有SELECT 权限。如果使用CREATE OR REPLACE 或者ALTER修改视图，那么还需要该视图的DROP 权限。创建或修改视图的语法为：

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

其中WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件：

LOCAL 是只要满足本视图的条件就可以更新；
CASCADED （默认）则是必须满足所有针对该视图的所有视图的条件才可以更新。

MySQL 创建或使用视图常见的规则与限制有：

必须唯一命名；
视图数目没有限制；
视图可以嵌套；
ORDER BY可以在视图中使用，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图的ORDER BY将被覆盖；
视图不能索引，不能有关联的触发器或默认值；
视图可以和表一起使用，如编写一条联接表和视图的SELECT语句；
在FROM 关键字后面不能包含子查询。

　　最重要的还是例子！我们的数据来源于http://www.forta.com/books/0672336073/　创建的数据表，下载该网页的MySQL (and MariaDB) SQL scripts文档，并在ＭｙSQL中执行创建好表格。关于这5张表的说明，可参看样例表说明。
　　我们将结合实例讲述视图的实际操作及其作用。
　　
例1.利用视图简化复杂的联接

create view ProductCustomers as
select cust_name, cust_contact, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;

这样我们就创建了一个名为ProductCustomers的视图，它联接了三个表，返回已订购了任意产品的所有顾客的列表。如果我们想检索出订购了产品RGAN01的顾客，可进行如下查询：

select cust_name, cust_contact from ProductCustomers where prod_id = 'RGAN01';

结果如下：
VIEW1
　　　可以看出，视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。
　　　
例2.用视图过滤不想要的数据
　　可以定义CustomerEmailList视图，过滤掉没有电子邮件的顾客，代码如下：

create view CustomerEmailList AS
select cust_id, cust_name, cust_email
from customers
where cust_email is not null;

　　现在可以像使用普通表一样使用视图CustomerEmailList.

select * from CustomerEmailList;

例3.使用视图与计算字段
　　检索某个订单中的物品，计算每种物品的总价格：

create view OrderItemsExpanded AS
select order_num, prod_id, quantity, item_price, quantity*item_price as expanded_price
from orderitems;

　　我们检索订单20008的详细内容，操作如下：

select * from OrderItemsExpanded  where order_num = 20008;

例4. WITH [CASCADED | LOCAL] CHECK OPTION的使用

#WITH LOCAL CHECK OPTION orderitems_view
create view orderitems_view as
select * from orderitems
where quantity>50 WITH LOCAL CHECK OPTION;
#WITH CHECK OPTION orderitems_view1
create view orderitems_view1 as
select * from orderitems
where quantity>50 WITH CHECK OPTION;
#orderitems_view2 from orderitems_view1
create view orderitems_view2 as
select * from orderitems_view1
where quantity<250 WITH CHECK OPTION;

该段代码创建了三个视图，其中orderitems_view是WITH LOCAL CHECK OPTION的，而orderitems_view1，orderitems_view是WITH CHECK OPTION的。我们执行更新操作：

update orderitems_view set quantity = 300 where prod_id = 'BR03';
update orderitems_view2 set quantity = 300 where prod_id = 'BR03';

执行结果如下：
VIEW4
　　另外，需要指出的是，视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的。

包含以下关键字的SQL 语句：聚合函数（SUM,MIN,MAX,COUNT 等）,DISTINCT,GROUP BY,HAVING,JOIN,UNION ,UNION ALL;
常量视图;
SELECT 中包含子查询
FROM 一个不能更新的视图;
WHERE 字句的子查询引用了FROM 字句中的表.

　　
二、删除视图操作
用户可以一次删除一个或者多个视图，前提是必须有该视图的DROP 权限。其语法如下：

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

例如：

drop view OrderItemsExpanded;

三、查看视图操作
　　从MySQL 5.1 版本开始，使用SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的SHOW VIEWS 命令。
例如：

show tables;

view5
　　在使用SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。如果需要查询某个视图的定义，可以使用SHOW CREATE VIEW 命令进行查看。最后，通过查看系统表information_schema.views 也可以查看视图的相关信息。
例如：