-- 第八章 1-创建视图
-- 视图不存储数据，数据存在表中
-- use sql_invoicing;
-- create view sales_by_client as
-- select
-- 		c.client_id,
-- 		c.name,
-- 		sum(invoice_total) as total_sale
-- from clients c
-- join invoices i using (client_id)
-- group by client_id,name

-- 习题
-- create view clients_balance as
-- select
-- 		c.client_id,
-- 		c.name,
-- 		sum(invoice_total - payment_total) as balance
-- from clients c
-- join invoices i using (client_id)
-- group by client_id,name

-- 更改或删除视图
-- drop view clients_balance
-- create or replace view clients_balance as ...不需要删除视图

-- 如果视图中没有distinct，aggregate function（min，max，sub...),group by,having,union则说明视图是可更新的
-- create or replace view invoice_with_balance as
-- select 
-- 		invoice_id,
-- 		number,
-- 		client_id,
-- 		invoice_total,
-- 		payment_total,
-- 		invoice_total - payment_total as balance,
-- 		invoice_date,
-- 		due_date,
-- 		payment_date
-- from invoices
-- where (invoice_total - payment_total) > 0

-- delete from invoice_with_balance
-- where invoice_id = 1

-- update invoice_with_balance
-- set due_date = date_add(due_date,interval 2 day)
-- where invoice_id = 2 -- 更新2号发票的到期日

-- with option check -- 产生错误提示
-- 在创建视图的末尾加上会防止update和delete语句将行从视图中删除，