视图

创建视图

视图的作用相当于一张虚拟表，视图不保存数据

{
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
} 

更改或删除视图

{
DROP VIEW sales_by_client

CREATE OR REPLACE 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
} 

可更新视图

如果不包含DISTINCT、聚合函数、GROUP BY/HAVING、UNION就是可更新视图，可以用它来修改数据。

{
CREATE OR REPLACE VIEW invoices_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 -- 因为语句执行顺序是FROM WHERE SELECT，所以此处不能直接用balance别名

DELETE FROM invoices_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 CHECK OPTION

放在尾部，防止UPDATE或者DELETE语句将行从视图删除（如果修改结果是会删除，则会报错）

{
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
WITH CHECK OPTION 
}