MySQL学习之使用视图

  视图是虚拟的表,在数据库中并不真实存在,它只包含使用时动态检索数据的查询。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文档,并在MySQL中执行创建好表格。关于这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';

结果如下:
MySQL学习之使用视图
   可以看出,视图极大地简化了复杂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;

MySQL学习之使用视图

例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;

MySQL学习之使用视图

例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';

执行结果如下:
MySQL学习之使用视图
  另外,需要指出的是,视图的可更新性和视图中查询的定义有关系,以下类型的视图是不可更新的。

  • 包含以下关键字的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;

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

show create view orderitems_view;

MySQL学习之使用视图



  本次关于MySQL视图使用就写到这儿了~~如有不足之处,还请批评指正,欢迎交流^o^

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

上一篇:Python爬虫——漫画下载


下一篇:MySQL学习之流程函数