视图的创建
- 视图用
create view语句来创建 - 使用
show create view viewname来查看创建视图的语句 - 用
drop view viewname删除视图 - 更新视图是，可以先用drop再用create，也可以直接用
create or replace view
利用视图简化复杂的联结
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;
//使用
select cust_name, cust_contact
from productcustomers
where prod_id = 'TNT2';
使用视图重新格式化检索出来的数据
\1.PNG
\2.PNG
使用视图过滤不想要的数据
create view customeremaillist as
select cust_id, cust_name, cust_email
from cutomers
where cust_email is not null;
使用视图与计算字段
create view orderitemsexpanded as
select order_num,
prod_id,
quantity,
item_price,
quantity*item_price as expanded_price
from orderitems;
//使用
select * from orderitemsexpanded
where order_num = 20005;
参考书籍：
- MySQL必知必会
