数据库中的视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的行和列数据，行和列数据来自定义视图查询所引用的表，并且在引用视图时动态生成。但视图并不提供数据

创建视图

格式：CREATE VIEW 【视图名】 AS + 查询语句

1
2
3
4
5
6
7
8
9
CREATE VIEW clients_balance AS
SELECT 
	i.client_id,
    name,
    SUM(invoice_total-payment_total) balance
FROM invoices i
JOIN clients c USING(client_id)
GROUP BY i.client_id,name
ORDER BY i.client_id;

删除视图

格式：DROP VIEW 【视图名】

1
DROP VIEW clients_balance

更改视图

格式：CREATE OR REPLACE VIEW 【视图名】 AS + 查询语句

1
2
3
4
5
6
7
8
CREATE OR REPLACE VIEW sales_by_clients AS  -- 创建或更新sales_by_clients视图
SELECT 
	c.client_id,
	c.name,
	SUM(invoice_total) total_sales
FROM clients c
JOIN invoices i USING(client_id)
GROUP BY client_id, name

可更新视图

可以使用INSERT、UPDATE、DELETE语句的视图
成立条件：没有使用DISTINCT，聚合函数，GROUP BY子句/HAVING子句，UNION的视图
示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
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子句

添加在创建视图语句的最后，在使用UPDATE语句或DELETE语句更新视图时，假如该行为会使视图中的某行消失，系统会返回错误提示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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；

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 1
-- 查询结果：#1369 - CHECK OPTION failed 'sql_invoicing.invoices_with_balance'