0%

MySQL笔记(7)--视图(Views)

发表于 分类于 Valine：

1 创建视图

1
2
3
4
5
6
7
8
9
10
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

可以在”Views”看到这个视图.可以像使用table一样用视图.

2 更改或删除视图

删除视图

1
DROP VIEW sales_by_client

更改视图

1
2
3
4
5
6
7
8
9
10
USE sql_invoicing;

CREATE OR REPLACE VIEW sales_by_client AS 	-- create or replace
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

3 可更新视图

如果视图中没有:

DISTINCT
Aggregate Functions (MIN, MAX, SUM)
GROUP BY / HAVING
UNION

就是可更新视图,就可以增删改:

比如:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
    invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_total AS balance,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0

invoices_with_balance 就是可更新视图,可以增删改:

1
2
DELETE FROM invoices_with_balance
WHERE invoice_id = 1
1
2
3
UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 5 DAY)
WHERE invoice_id = 2

4 WITH OPTION CHECK 子句

上例中,进行如下更改:

1
2
3
UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2

该视图中 invoice_id = 2 的行消失了(因为不再满足(invoice_total - payment_total) > 0的条件)

如果不想 UPDATE 或 DELETE 语句使得某些行从视图中删除, 就这样:

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,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION			-- 增加这个子句,防止删除行

后面再执行这样的:

1
2
3
UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 3

就会报错:

Error Code: 1369. CHECK OPTION failed

5 视图的优点

Simplify queries
Reduce the impact of changes
Restrict access to the data