数据库的视图操作

Creating Views

-- 收支平衡表:客户id,名字以及收支平衡
-- 视图就像是虚拟的表，但是请记住，视图不保存数据

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`

Altering or Dropping Views

CREATE OR REPLACE VIEW clients_balance AS
SELECT ...

DROP VIEW clients_balance AS

-- 将视图保存在sql文件中,然后放到版本管理工具里面(如:git)

Updatable Views

如果表中没有下面这些数据

DISTINCT
Aggregate Functions (MIN,MAX,SUM)	-- 聚合函数
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, I
    invoice_date,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0

修改

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

删除

DELETE FROM invoices_with_balance 
WHERE invoice_id=1

The WITH OPTION CHECK Clause

CREATE OR REPLACE VIEW invoices_ with_ balance AS
SELECT
    invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_ total AS balance, I
    invoice_date,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION
-- 这个视图就会防止更新或者删除已有的视图数据

Other Benefits of Views

视图可以简化查询

视图可以防止表设计修改带来的影响，如果所有的查询都是基于视图的，他们就不会因为源表的修改而受到影响

视图为表提供了一个抽象层，这种抽象方式减少了修改带来的影响

我们可以用视图来防止对源数据的访问