MySQL学习之使用视图

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

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

视图的基本操作有&＃xff1a;创建或修改视图、删除视图&＃xff0c;以及查看视图定义。

一、创建或修改视图操作

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

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

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

• LOCAL 是只要满足本视图的条件就可以更新&＃xff1b;
• CASCADED &＃xff08;默认&＃xff09;则是必须满足所有针对该视图的所有视图的条件才可以更新。

MySQL 创建或使用视图常见的规则与限制有&＃xff1a;

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

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

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

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

select cust_name, cust_contact from ProductCustomers where prod_id &＃61; &＃39;RGAN01&＃39;;

结果如下&＃xff1a;

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

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

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

select * from CustomerEmailList;

例3.使用视图与计算字段
　　检索某个订单中的物品&＃xff0c;计算每种物品的总价格&＃xff1a;

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

　　我们检索订单20008的详细内容&＃xff0c;操作如下&＃xff1a;

select * from OrderItemsExpanded where order_num &＃61; 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;

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

update orderitems_view set quantity &＃61; 300 where prod_id &＃61; &＃39;BR03&＃39;;
update orderitems_view2 set quantity &＃61; 300 where prod_id &＃61; &＃39;BR03&＃39;;

执行结果如下&＃xff1a;

　　另外&＃xff0c;需要指出的是&＃xff0c;视图的可更新性和视图中查询的定义有关系&＃xff0c;以下类型的视图是不可更新的。

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

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

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

例如&＃xff1a;

drop view OrderItemsExpanded;

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

show tables;

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

show create view orderitems_view;

参考书目&＃xff1a;
1.SQL 必知必会(第4版)&＃xff0c; Ben Forta&＃xff0c; 人民邮电出版社
2.深入浅出MySQL