SQL视图View的总结和使用

古城微笑少年丶 2022-12-14 12:18 107阅读 0赞

实际工作当中，数据交互查询返回结果，SQL你是没办法找其他的完美替代的，但有的时候还是会遇到一些很头痛的问题需要视图view来解决，比如以下场景：

## view日常使用场景 ##

场景一：  
有的时候，多个表并表条件查询，尤其是好几张表那种一起查询的那种，结果集来自“五湖四海”，也看不出那张表更“主要”一些，即使可能写出来的SQL相当的痛苦，各种select包一层select套了好几层，不说维护理解，就是修改起来也很难受，感觉本来改了一个地方，突然发现结果集又有别的问题，就是那种按起了葫芦有起了瓢那种，处处都能给你带来“惊喜”。

场景二：  
主体大多数字段大概来自一张表，但是关联好几个表，这也没什么，这时候业务同学会跟你谈，要加很多限制，比如说某个类型订单我们只要从某个时间段的，我们查找范围要限制在什么几个状态下的几种类型。这种如果是确定一个功能，经常会用的话，尽量单独拿出来维护。

这时候不管是多表视图还是单表视图都可能解决一些问题。

## 视图是什么，有啥优缺点？ ##

视图咱不咬文嚼字也不去别的博客那边去拷贝复制，用大白话来解释，他其实可以看作一个表，一个“虚”表。“虚”到什么程度，别看他也有字段，但是它实际并没有储存字段数据，他的数据更多是来自组成他的其他的表（这些表也可以说是这个视图的基表）来存储的，微信公众号：我是坑货，徒有一个类似表的数据结构。

但从我们使用，甚至创建语句都和表非常相似，既然和我们之前使用的表差不多，他到底有啥过人之处呢？

### 视图的优点 ###

使用视图可以定制化一些数据，限制范围，过滤条件，从这个视图得到的数据因为都限制过滤定制化处理了，方便你去分析

聚合函数，多个表连接展示

合并分离的数据，分区试图  
这个我要多说一下，类似于多个表并称一个表。赤壁里面的铁索连舟，主要是集中即使关键词union。  
尤其是很多表因为设计的问题，尤其是实际上很多结构相似表，比如说有的系统租聘订单，赠品单，销售订单虽然意义不同，但是里面很多字段差不多都是一个意思，比如交易时间，订单编号，交易客户等。但是呢。像比较有特色的字段，比如租聘的时间长短，赠品关联的订单等一大堆多余出来也可以会根据需求在视图展示出来。

保证数据安全  
这个涉及到试图能不能修改和怎么修改，限制是什么。

### 视图的缺点 ###

首先视图是一个虚表，很多在表上性能优化的手法可能就没有，而且数据库引擎要构建视图有一个过程，类似一个查询，视图的性能以及优化你就不要又太高的要求。你可以把他看作一个查询，而不是一个表真的优化也最好基于查询的优化来优化可能好一些。

修改限制  
视图不好修改，我建议最好别对视图修改，有什么问题最好还是对组成视图的基表来操作，而且有的视图在某些数据库或者某些条件下是不能修改的。微信公众号：我是坑货

视图能不能修改  
这个问题其实没什么，但是很多博客抄来抄去，只提了一嘴某系条件下不能修改，到底是什么情况“才是某些条件呢”？甚至有的直接说不能，实际上是不严谨的。

首先视图是可以改的，但是在某些条件下是不能做某些更新操作，实际情况我整理搜集的如下：

l可以在简单视图中执行DML 操作
    l当视图定义中包含以下元素之一时不能使用delete:
    •组函数
    •GROUPBY子句
    •DISTINCT 关键字
    ROWNUM 伪列
    
    
    当视图定义中包含以下元素之一时不能使用update :
    
    l组函数
    lGROUP BY子句
    lDISTINCT 关键字
    lROWNUM 伪列
    l列的定义为表达式
    
    当视图定义中包含以下元素之一时不能使用insert :
    
    l组函数
    lGROUP BY 子句
    lDISTINCT 关键字
    lROWNUM 伪列
    l列的定义为表达式
    l表中非空的列在视图定义中未包括

这个是我从某个大牛博客中，他在他当时用的Oracle文档搞出来的，真的要使用，不同的数据库限制也是有的，这都是要考虑，反正最好别在视图上更新，对基表操作，毕竟基表才是相关数据的存储主体。微信公众号：我是坑货

### 视图的使用 ###

实际上视图的使用，对我来说我一般只用来在上面做查询，所以创建视图才是问题主要的。  
举个例子如果是union连接的视图，注意连接的几个表除了对应关系外，数量也有对上不要A表8个字段B表10个字段，那肯定会出问题。微信公众号：我是坑货  
talk is cheap ，show me code，这是我一个项目用到的限制了每个表的数据时间范围，几个表合并成一个视图来展示。

CREATE VIEW `ams_temp_order_detail` AS SELECT
    	`acod`.`id` AS `detail_id`,
    	`acod`.`merchant_id` AS `merchant_id`,
    	`acod`.`collected_order_id` AS `order_id`,
    	`acod`.`collected_order_no` AS `order_no`,
    	`acod`.`asset_id` AS `asset_id`,
    	`acod`.`asset_code` AS `asset_code`,
    	`acod`.`asset_name` AS `asset_name`,
    	`acod`.`brand` AS `brand`,
    	`acod`.`asset_category_id` AS `asset_category_id`,
    	`acod`.`assets_category_name` AS `assets_category_name`,
    	`acod`.`manager_user_id` AS `manager_user_id`,
    	`acod`.`manager_user_name` AS `manager_user_name`,
    	`acod`.`place_id` AS `place_id`,
    	`acod`.`place_name` AS `place_name`,
    	`acod`.`belong_company_id` AS `belong_company_id`,
    	`acod`.`belong_company_name` AS `belong_company_name`,
    	`acod`.`company_id` AS `company_id`,
    	`acod`.`company_name` AS `company_name`,
    	`acod`.`employee_id` AS `employee_id`,
    	`acod`.`employee_name` AS `employee_name`,
    	`acod`.`model` AS `model`,
    	`acod`.`asset_sequence_no` AS `asset_sequence_no`,
    	`acod`.`department_id` AS `department_id`,
    	`acod`.`amount` AS `amount`,
    	`acod`.`department_name` AS `department_name`,
    	`acod`.`data_status` AS `data_status`,
    	`acod`.`version` AS `version`,
    	`acod`.`remark` AS `remark`,
    	`acod`.`create_time` AS `create_time`,
    	`acod`.`create_user` AS `create_user`,
    	`acod`.`update_time` AS `update_time`,
    	`acod`.`update_user` AS `update_user`,
    	`acod`.`add_asset_type` AS `add_asset_type`,
    	`acod`.`supplier_name` AS `supplier_name`,
    	`acod`.`out_order_no` AS `out_order_no`,
    	1 AS `order_type`,
    	`aco`.`use_time` AS `operate_time`,
    	NULL AS `handover_order_department_id`,
    	`aco`.`use_remark` AS `order_remark`,
    	NULL AS `back_place_id`,
    	NULL AS `back_company_id`,
    	NULL AS `handover_employee_id`,
    	NULL AS `handover_department_id`,
    	NULL AS `handover_company_id`,
    	NULL AS `handover_employee_name`,
    	NULL AS `handover_department_name`,
    	NULL AS `handover_company_name`
    FROM
    	(
    		`ams_collected_order_detail` `acod`
    		JOIN `ams_collected_order` `aco` ON (
    			(
    				`acod`.`collected_order_id` = `aco`.`id`
    			)
    		)
    	)
    WHERE
    	(
    		`aco`.`use_time` > '2020-05-01 00:00:00'
    	)
    UNION
    	SELECT
    		`ahod`.`id` AS `detail_id`,
    		`ahod`.`merchant_id` AS `merchant_id`,
    		`ahod`.`handover_order_id` AS `order_id`,
    		`ahod`.`handover_order_no` AS `order_no`,
    		`ahod`.`asset_id` AS `asset_id`,
    		`ahod`.`asset_code` AS `asset_code`,
    		`ahod`.`asset_name` AS `asset_name`,
    		`ahod`.`brand` AS `brand`,
    		`ahod`.`asset_category_id` AS `asset_category_id`,
    		`ahod`.`assets_category_name` AS `assets_category_name`,
    		`ahod`.`manager_user_id` AS `manager_user_id`,
    		`ahod`.`manager_user_name` AS `manager_user_name`,
    		`ahod`.`place_id` AS `place_id`,
    		`ahod`.`place_name` AS `place_name`,
    		`ahod`.`belong_company_id` AS `belong_company_id`,
    		`ahod`.`belong_company_name` AS `belong_company_name`,
    		`ahod`.`use_company_id` AS `use_company_id`,
    		`ahod`.`use_company_name` AS `use_company_name`,
    		`ahod`.`employee_id` AS `employee_id`,
    		`ahod`.`employee_name` AS `employee_name`,
    		`ahod`.`model` AS `model`,
    		`ahod`.`asset_sequence_no` AS `asset_sequence_no`,
    		`ahod`.`department_id` AS `department_id`,
    		`ahod`.`amount` AS `amount`,
    		`ahod`.`department_name` AS `department_name`,
    		`ahod`.`data_status` AS `data_status`,
    		`ahod`.`version` AS `version`,
    		`ahod`.`remark` AS `remark`,
    		`ahod`.`create_time` AS `create_time`,
    		`ahod`.`create_user` AS `create_user`,
    		`ahod`.`update_time` AS `update_time`,
    		`ahod`.`update_user` AS `update_user`,
    		`ahod`.`add_asset_type` AS `add_asset_type`,
    		`ahod`.`supplier_name` AS `supplier_name`,
    		`ahod`.`out_order_no` AS `out_order_no`,
    		2 AS `order_type`,
    		`aho`.`handover_time` AS `operate_time`,
    		`aho`.`department_id` AS `handover_order_department_id`,
    		`aho`.`handover_remark` AS `order_remark`,
    		NULL AS `back_place_id`,
    		NULL AS `back_company_id`,
    		`aho`.`use_employee_id` AS `handover_employee_id`,
    		`aho`.`department_id` AS `handover_department_id`,
    		`aho`.`company_id` AS `handover_company_id`,
    		`ae`.`employee_name` AS `handover_employee_name`,
    		`ae`.`department_name` AS `handover_department_name`,
    		`ae`.`company_name` AS `handover_company_name`
    	FROM
    		(
    			(
    				`ams_handover_order_detail` `ahod`
    				JOIN `ams_handover_order` `aho` ON (
    					(
    						`ahod`.`handover_order_id` = `aho`.`id`
    					)
    				)
    			)
    			LEFT JOIN `ams_employee` `ae` ON (
    				(
    					`aho`.`use_employee_id` = `ae`.`id`
    				)
    			)
    		)
    	WHERE
    		(
    			`aho`.`handover_time` > '2020-05-01 00:00:00'
    		)
    	UNION
    		SELECT
    			`abod`.`id` AS `detail_id`,
    			`abod`.`merchant_id` AS `merchant_id`,
    			`abod`.`back_order_id` AS `order_id`,
    			`abod`.`back_order_no` AS `order_no`,
    			`abod`.`asset_id` AS `asset_id`,
    			`abod`.`asset_code` AS `asset_code`,
    			`abod`.`asset_name` AS `asset_name`,
    			`abod`.`brand` AS `brand`,
    			`abod`.`asset_category_id` AS `asset_category_id`,
    			`abod`.`assets_category_name` AS `assets_category_name`,
    			`abod`.`manager_user_id` AS `manager_user_id`,
    			`abod`.`manager_user_name` AS `manager_user_name`,
    			`abod`.`place_id` AS `place_id`,
    			`abod`.`place_name` AS `place_name`,
    			`abod`.`belong_company_id` AS `belong_company_id`,
    			`abod`.`belong_company_name` AS `belong_company_name`,
    			`abod`.`use_company_id` AS `use_company_id`,
    			`abod`.`use_company_name` AS `use_company_name`,
    			`abod`.`employee_id` AS `employee_id`,
    			`abod`.`employee_name` AS `employee_name`,
    			`abod`.`model` AS `model`,
    			`abod`.`asset_sequence_no` AS `asset_sequence_no`,
    			`abod`.`department_id` AS `department_id`,
    			`abod`.`amount` AS `amount`,
    			`abod`.`department_name` AS `department_name`,
    			`abod`.`data_status` AS `data_status`,
    			`abod`.`version` AS `version`,
    			`abod`.`remark` AS `remark`,
    			`abod`.`create_time` AS `create_time`,
    			`abod`.`create_user` AS `create_user`,
    			`abod`.`update_time` AS `update_time`,
    			`abod`.`update_user` AS `update_user`,
    			NULL AS `add_asset_type`,
    			NULL AS `supplier_name`,
    			NULL AS `out_order_no`,
    			3 AS `order_type`,
    			`abo`.`return_time` AS `operate_time`,
    			NULL AS `handover_order_department_id`,
    			`abo`.`return_remark` AS `order_remark`,
    			`abo`.`place_id` AS `back_place_id`,
    			`abo`.`use_company_id` AS `back_company_id`,
    			`abod`.`employee_id` AS `handover_employee_id`,
    			`abod`.`department_id` AS `handover_department_id`,
    			`abod`.`use_company_id` AS `handover_company_id`,
    			NULL AS `handover_employee_name`,
    			NULL AS `handover_department_name`,
    			NULL AS `handover_company_name`
    		FROM
    			(
    				(
    					`ams_back_order_detail` `abod`
    					JOIN `ams_back_order` `abo` ON (
    						(
    							`abod`.`back_order_id` = `abo`.`id`
    						)
    					)
    				)
    				LEFT JOIN `ams_employee` `ae` ON (
    					(
    						`abod`.`employee_id` = `ae`.`id`
    					)
    				)
    			)
    		WHERE
    			(
    				`abo`.`return_time` > '2020-05-01 00:00:00'
    			)
    		ORDER BY
    			`update_time` DESC

微信公众号：我是坑货