创建视图
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