1.创建视图
例子:
CREATE VIEW IF NOT EXISTS sy320.orders_products_items_view AS
SELECT
oi.order_item_id,
o.order_date,
o.order_status,
p.product_name,
p.product_price
FROM
orders o
JOIN
order_items oi ON o.order_id = oi.order_item_id
JOIN
products p ON oi.order_item_product_id = p.product_id;
其中sy320是数据库名字,orders_products_items_view是你要创建的视图名字,
- order_item_id,
- order_date,
- order_status,
- product_name,
- product_price
这些都是来源不同的表,用join根据每个表的id进行连接。
2.查看视图
show tables;
3.查看视图详情信息
desc formatted orders_products_items_view;
4.修改视图属性
alter view orders_products_items_view set tblproperties("term"="three month","user_group"="IT department");
5.删除视图
drop view orders_products_items_view;