利用视图简化SQL操作

视图

视图是虚拟的表。与包含数据的表不一样,其只包含使用时动态检索数据的查询。
作为一个视图,不包含表中应有的任何列或数据,其包含的是一个SQL查询

为什么使用视图

  • 重用SQL语句
  • 简化复杂的SQL操作
  • 使用表的组成部分而不是整个表
  • 保护数据,特定部分的访问权限而不是整个表
  • 更改数据和格式

需要重点知道的是视图仅仅是用来查看存储在别处的数据的一种设施。
视图本身不包含数据,其返回的数据是从其他表中检索出来的,在添加或更改这些表中的数据是,视图将返回改变过的数据

  • 性能
    每次使用视图时,都必须处理查询执行时所需的任一个检索,如果视图较复杂,则性能会下降。在部署使用了大量视图的应用前,应该进行测试。

视图的规则和限制

常见的规则和限制

  • 视图名必须唯一
  • 视图数目没有限制
  • 创建视图必须有足够的访问权限
  • 视图可以嵌套,即可以从其他数据检索数据来构造一个新的视图
  • ORDER BY可以用在视图中,但如果从该视图检索的SELECT语句中也含有ORDER BY,那么视图中的ORDER BY将被覆盖
  • 视图不能索引,也不能有关联的触发器或默认值
  • 视图可以和表一起使用

使用视图

  • 创建
    CREATE VIEW语句
  • 查看创建语句
    SHOW CREATE VIEW
  • 删除
    DROP VIEW
  • 更新
    1.使用DROP后再试哟CREATE
    2.直接使用CREATE OR REPLACE VIEW(如果更新视图存在则覆盖,如果不存在则新建)

利用视图简化复杂的联结

视图最常见的应用是隐藏复杂的SQL,通常都会设计联结。

创建视图

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的视图,联结3个表,返回订购了任意产品的所有客户的列表。
image.png

在视图中查询

现在可从productcustomers视图中检索信息
image.png

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

MySQL在处理此查询时,将指定的WHERE子句添加到视图查询中的已有WHERE子句中,以便正确过滤数据
image.png
视图极大简化了复杂SQL,利用视图,可一次性编写基础的SQL,然后根据需要多次使用。

创建可重用的视图

创建不受特定数据限制的视图是一种好办法。即扩展视图的范围,使得其能被重用,切不需要重建和维护多个类似视图。

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

视图的另一个常见用途是重新格式化检索出的数据。
如果经常需要使用某个检索的格式结果,不必在每次需要时执行联结。只需创建一个视图,每次需要时使用它即可

CREATE VIEW vendorlocations AS SELECT
CONCAT( RTRIM( vend_name ), "(", RTRIM( vend_country ), ")" ) AS vend_tittle 
FROM
	vendors 
ORDER BY
	vend_name;

image.png

SELECT
	* 
FROM
	vendorlocations;

image.png

用视图过滤不想要的数据

视图对于普通的WHERE子句也有效果,可以过滤数据

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

image.png

SELECT
	* 
FROM
	customeremaillist;

image.png

关于WHERE子句

如果从视图中检索数据是使用了一条WHERE子句,则两组子句(一组在视图中,另一组时船体给视图的)将自动组合。

使用视图与计算字段

视图对于简化计算字段特别有用。

CREATE VIEW orderitemsexpanded AS SELECT
order_num,
prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price 
FROM
	orderitems;

image.png
检索订单为20005的详细内容

SELECT
	* 
FROM
	orderitemsexpanded 
WHERE
	order_num = 20005;

image.png
视图非常容易创建,而且很好使用。正确使用视图,可极大简化复杂的数据处理。

更新视图

视图是可以更新的(INSERT、UPDATE、DELETE)。注意更新一个视图将更新其基表,因为视图本身并没有数据。

无法更新视图的情况

并非所有视图都是可更新的。
如果MySQL不能正确地确定被更新的基数据,则不允许更新(包括插入和删除)。
即如果视图定义中有以下操作则不能进行视图的更新

  • 分组(GROUP BY和HAVING)
  • 联结
  • 子查询
  • 聚集函数(MIN、COUNT、SUM)
  • DISTINCT
  • 导出(计算)列

视图不在于更新而在于检索

视图的存在是用于查询检索数据的,而不是用户跟新数据


这个家伙很懒,啥也没有留下😋