【MySQL】视图

创建视图

CREAT VIEW ... AS

USE sql_invoicing;

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

 执行后该数据库下的view中会有该视图

 视图和表一样，可以执行表的任何操作

SELECT *
FROM sales_by_client
ORDER BY total_sales DESC

 

 注意：视图是虚拟表，并不存储数据（数据存储在表中）

更改或删除视图

删除视图

DROP VIEW sales_by_client

创建或更改 

没有该视图则创建一个新的视图，若已存在则替换原有的视图。

USE sql_invoicing;

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

 为了防止丢失，可以将视图保存在sql文件中，并把文件放在源码控制中：File- Save Script- Create New File- save

另一种更改方式：点击相应视图中间的的设置，然后更改，再apply

可更新视图

若视图中没有distinct、aggregate functions( MIN, MAX, SUM, AVG...)、GROUP BY、UNION这些则为可更新视图，如下所示：

USE sql_invoicing;

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

这类视图可以在INSERT、UPDATE、DELETE语句更新

UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id = 2

DELETE FROM invoices_with_balance
WHERE invoice_id = 1 

WITH OPTION　CHECK

USE sql_invoicing;

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

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2

执行此代码时，invoice_id=2那行消失了，因为更新视图后一些行可能不满足建立视图时的条件（在此例中更新后invoice_id=2的那行invoice_total - payment_total = 0，不满足大于0），为了避免此种情况发生，在创建视图的最后加上WITH CHECK OPTION，会检查更新的结果是否符合视图创建的条件，若符合则更新，不符合则报错，不会出现行消失的情况

USE sql_invoicing;

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