MySQL-22使用视图-必知必会

视图

视图是虚拟的表，与包含数据的表不同，视图只包含使用时 动态检索数据的查询 / MYSQL5及之后支持视图

#进行一次联结查询,需要知道相关表的结构才能知道如何创建查询和对表进行连接，当检索其他的数据时要修改WHERE子句
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.id = orders.cust_id,
	AND orderitems.order_num = order.order_num
	AND prod_id = 'TNT2';

如果能把整个查询包装成一个 productcustomers 的虚拟表，就能很轻松的检索出相同的数据

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

其中productcustomers就是一个视图，不包含表中任何列和数据，包含的是一个SQL查询。

• 重用SQL语句

• 简化复杂的SQL操作

• 使用表的组成部分，而不是整个表

• 保护数据，只给用户部分表的访问权限

• 更改数据格式和表示

• 在视图创建之后，还可以用与表基本相同的方式利用它们：进行SELECY操作、过滤和排序数据、将视图联结到其他视图/表。添加和更新数据

• 视图必须有唯一命名、可以创建任意个视图、必须给与足够的访问权限、视图可以嵌套、PRDER BY可以用在视图中、视图不能索引、视图可以和表一起使用。

使用视图

• 一些语句

  使用CREATE VIEW语句创建

  使用SHOW CREATE VIEW viewname来查看创建视图

  使用DROP VIEW　viewname删除视图

  使用DROP再CREATE更新视图，或使用CREATE OR DEPLACE VIEW

• 使用视图简化联结

  如上面的代码示例，productcustomers视图是如何创建的呢？

  CREATE VIEW productcustomers AS
  SELECT cust_name,cust_contact
  FROM customers,orders,orderitems
  WHERE customers.id = orders.cust_id,
  	AND orderitems.order_num = order.order_num;
  

  创建视图之后就能重复使用了

  SELECT cust_name,cust_contact
  FROM productcustomers
  WHERE prod_id = 'TNT2';
  

  SELECT cust_name,cust_contact
  FROM productcustomers
  WHERE prod_id = 'TNT3';
  

• 用视图重新格式化检索出的数据

  SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title 
  	FROM vendors ORDER BY vend_name;
  

  如要经常使用这个格式的结果，不必每次都执行联结，可以创建一个视图每次使用视图即可

  CREATE VIEW vendorlocations AS
  SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title 
  	FROM vendors
  	ORDER BY vend_name;
  

  #这样使用
  SELECT * FROM vendorlocations;
  

• 使用视图过滤不想要的数据
  比如过滤没有电子邮箱的客户

  CREATE VIEW customeremaillist AS
  SELECT cust_id,cust_name,cust_email
  FROM customers
  WHERE cust_email IS NOT NULL;
  

  SELECT * FROM customeremaillist;
  

• 使用视图与计算字段

  SELECT id,
  	   quantity,
  	   price,
  	   quantity*price AS expanded_price 
  	   FROM orderitems 
  	   WHERE id = 2005;
  

  #使用视图
  CREATE VIEW orderitemsexpanded AS
  SELECT id,
  	   quantity,
  	   price,
  	   quantity*price AS expanded_price 
  	   FROM orderitems;
  

  SELECT * FROM orderitemsexpanded WHERE id = 1001; 
  

• 更新视图

  可以使用INSERT/UADATE/DELETE更新视图/基表，有如下操作的视图不能更新：

  有分组、联结、子查询、并、聚集函数、DISTINCT、导出/计算列的