文章目录

  • MySQL 创建视图
  • Creating views 创建视图
  • Altering or Dropping Views 更改或删除视图
  • Updatable Views 可更新的视图
  • WITH OPTION CHECK Clause
  • WITH CHECK OPTION 的作用
  • WITH CHECK OPTION 的用法
  • Benefits of Views 视图的好处


MySQL 创建视图

Creating views 创建视图

-- 创建视图
CREATE VIEW sales_by_clients AS
SELECT
	c.client_id,
	c.name,
	SUM(invoice_total) AS total_sales,
	(SELECT AVG(invoice_total) FROM invoices) AS average
FROM clients c
JOIN invoices i on c.client_id = i.client_id
GROUP BY c.client_id, name;

-- 之后只需要这样使用
SELECT *
FROM sales_by_clients
ORDER BY total_sales;

Altering or Dropping Views 更改或删除视图

-- 若要修改可以先删除原有视图后再创建新视图
DROP VIEW IF EXISTS sales_by_clients;

CREATE VIEW sales_by_clients AS
SELECT
	c.client_id,
	c.name,
	SUM(invoice_total) AS total_sales,
	(SELECT AVG(invoice_total) FROM invoices) AS average
FROM clients c
JOIN invoices i on c.client_id = i.client_id
GROUP BY c.client_id, name;

Updatable Views 可更新的视图

当一张视图中不存在以下函数或关键词时，我们称这张视图为可更新视图。

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

确定一张视图是可更新视图后，就可以使用 REPLACE / UPDATE / DELETE 对其直接修改。

-- Using REPLACE
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;

-- Using UPDATE
UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY )
WHERE client_id = 2;

-- Using DELETE
DELETE FROM invoices_with_balance
WHERE invoice_id = 1;

WITH OPTION CHECK Clause

WITH CHECK OPTION 的作用

首先视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。 WITH CHECK OPTION 主要是在创建视图的时候使用，用来保证数据的完整性。

  1. 对于UPDATE：有WITH OPTION CHECK，要保证UPDATE后，数据要被视图查询出来；
  2. 对于DELETE：有无WITH OPTION CHECK都一样；
  3. 对于INSERT：有WITH OPTION CHECK，要保证INSERT后，数据要被视图查询出来；
  4. 对于没有WHERE 子句的视图，使用WITH OPTION CHECK是多余的。
  5. 如果修改后的条件不满足在创建VIEW的时候给出的条件，那么就会报错。 否则就随便改了，不符合我们的条件。

WITH CHECK OPTION 的用法

CREATE VIEW sales_by_clients AS
SELECT
	c.client_id,
	c.name,
	SUM(invoice_total) AS total_sales,
	(SELECT AVG(invoice_total) FROM invoices) AS average
FROM clients c
JOIN invoices i on c.client_id = i.client_id
GROUP BY c.client_id, name
WITH CHECK OPTION; -- <=

Benefits of Views 视图的好处

Simplify queries

Reduce the impact of changes

Restrict access to the data

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询；

视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。

  1. 数据库视图允许简化复杂查询。数据库视图由与许多基础表相关联的SQL语句定义。您可以使用数据库视图来隐藏最终用户和外部应用程序的基础表的复杂性。通过数据库视图，您只需使用简单的SQL 语句，而不是使用具有多个连接的复杂的SQL语句。
  2. 数据库视图有助于限制对特定用户的数据访问。您可能不希望所有用户都可以查询敏感数据的子集。可以使用数据库视图将非敏感数据仅显示给特定用户组。
  3. 数据库视图提供额外的安全层。安全是任何关系数据库管理系统的重要组成部分。数据库视图为数据库管理系统提供了额外的安全性。数据库视图允许您创建只读视图，以将只读数据公开给特定用户。用户只能以只读视图检索数据，但无法更新。
  4. 数据库视图启用计算列。 数据库表不应该具有计算列，但数据库视图可以这样。 假设在 order_details 表中有 quantity_order (产品的数量) 和 price_each (产品的价格) 列。但是， order_details 表没有一个列用来存储订单的每个订单项的总销售额。如果有，数据库模式不是一个好的设计。在这种情况下，您可以创建一个名为 total 的计算列，该列是 quantity_order 和 price_each 的乘积，以表示计算结果。当您从数据库视图中查询数据时，计算列的数据将随机计算产生。
  5. 数据库视图实现向后兼容。假设你有一个中央数据库，许多应用程序正在使用它。有一天，您决定重新设计数据库以适应新的业务需求。删除一些表并创建新的表，并且不希望更改影响其他应用程序。在这种情况下，可以创建与将要删除的旧表相同的模式的数据库视图。