mysql详解7：视图

简化操作  ，控制访问

创建视图
create view sales_by_client as
select c.client_id,
c.name,
SUM(invoice_total) as total_sales
from clients c
join invoices i using(client_id)
group by client_id,name
使用视图
select *
form sales_by_client
join clients using(client_id)
where total_sales>500

更改或删除视图
DROP view sales_by_client
把视图存储在sql文件中

可更新视图
如果视图中没有DISTINCT 聚合函数 group by union ,那就是可更新视图
create or replace view invoice_with_balance

 

select
invoice_id,
number,
client_id,
invoice_total,
payment_total
from invoices
where (invoice_total -payment_total)>0
这是一个可更新视图
delete from invoice_with_balance
where invoice_id=1

update invoices_with_balance
set due_date =DATE_ADD(due_date,INTERVAL 2 DAY)
WHERE invoice_id =2

 

 

 

WITH CHCEK OPTION 创建视图末尾添加 以防更新视图会让某些行消失

视图的其它优点
视图的流动性
可以通过视图限制基础表的访问