8. 视图

USE sql_invoicing;
CREATE VIEW sales_by_client AS
    SELECT 
        client_id,
        name,
        SUM(invoice_total) AS total_sales
    FROM clients c
    JOIN invoices i USING(client_id)
    GROUP BY client_id, name;

CREATE VIEW clients_balance AS 
    SELECT 
        client_id,
        name,
        SUM(invoice_total - payment_total) AS balance
    FROM clients c
    JOIN invoices i USING(client_id)
    GROUP BY client_id, name;

USE sql_invoicing;
DROP VIEW IF EXISTS clients_balance;
-- 若不存在这个视图，直接 DROP 会报错，所以要加上 IF EXISTS 先检测有没有这个视图
CREATE VIEW clients_balance AS 
    ……
    ORDER BY balance DESC

CREATE OR REPLACE VIEW ... AS

USE sql_invoicing;
CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT 
    /* 这里有个小技巧，要插入表中的多列列名时，
    可从左侧栏中连选并拖入相关列 */
    invoice_id, 
    number, 
    client_id, 
    invoice_total, 
    payment_total, 
    invoice_date,
    invoice_total - payment_total AS balance,  -- 新增列
    due_date, 
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0;
/* 这里不能用列别名balance，会报错说不存在，
必须用原列名的表达式，这还是执行顺序的问题
之前讲WHERE和HAVING作为事前筛选和事后筛选的区别时提到过 */
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

USE sql_invoicing;
CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT 
    *
FROM invoices
WHERE (invoice_total - payment_total) > 0
with check option;
delete from invoices_with_balance
where invoice_id = 2;    -- 失败，无法删除

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 3
-- 还是失败，因为这里修改了之后，这条语句不满足这个视图的条件会消失，所以这个条件可以阻止它运行
-- Error Code: 1369. CHECK OPTION failed 'sql_invoicing.invoices_with_balance'