MySQL与Oracle视图

概念

创建举例

举例一：

举例二：

创建一张视图view_cust_totalprice，可以通过cust_id条件查出该顾客的所有订单总额
MySQL:

create view view_cust_totalprice as select c.cust_id,sum(c.sum_money) sumMoney from (
	select o1.cust_id cust_id,o1.order_num order_num,sum(o2.quantity * o2.item_price) sum_money from orders o1 
	join orderitems o2 on o1.order_num = o2.order_num
	group by o1.order_num
	) c group by c.cust_id;

Oracle:

create view view_cust_totalprice as SELECT o1.cust_id cust_id,sum(o2.sum_money) sumMoney FROM orders o1 
JOIN (SELECT order_num,sum(QUANTITY * ITEM_PRICE ) sum_money FROM ORDERITEMS GROUP BY order_num) o2
ON o1.order_num = o2.order_num GROUP BY o1.cust_id;

select * from view_cust_totalprice where cust_id = '1000000001';

视图作用

视图限制