mosh数据库——第八章

1.创建视图

使用这个视图，所以我们不需要重新写这些查询

USE sql_invoicing;

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

我们可能需要我们的顶级客户名单,或者现在有销售名单的客户,而不是每次都写这个选择语句，并为每个查询稍微改变它,我们可以将此查询保存在视图中，以供在其他地方使用

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

因为我们在这里执行create view语句，所以该语句不会像select语句那样返回结果，而是创造了一个视图对象

让我在导航仪面板上展示给你看，在我们的 sql发票数据库中，我们需要扩展视图文件夹，但首先让我们现在刷新这个视图，让我们看看视图文件夹内部

使用视图就像使用一张表,视图功能非常强大,并且可以大大简化我们以后要用的查询

SELECT *
FROM sales_by_client

每当我们要使用客户销售额情况,就可以再使用视图,我们不必写再写这个查询,从头写这个选择语句

视图的作用就行一张虚拟表，但是要记住！！视图不储存数据，我们的数据储存在了表中。视图只是提供了一张虚拟基础表，这就是为什么我们称它们为视图。

CREATE VIEW clients_balance AS
SELECT
	c.client_id,
	c. name,
	SUM(invoice_total - payment_total) AS balance
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name

2.更改或删除视图

创建视图后，你可能会意识到您的查询有某个问题，因此你需要回去修改视图。

两种解决方法：一种方法是删除视图并重新创建，另一种方法是使用REPLACE关键字

删除并重建：

DROP VIEW sales_by_client -- 我们的视图就没了，再重新执行创建语句

 REPLACE 关键词：

CREATE OR REPLACE VIEW .....AS

CREATE OR REPLACE VIEW sales_by_client AS
SELECT
	c.client_id,
	c. name,
	SUM(invoice_total - payment_total) AS balance
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name

最好把视图储存在SQL文件中，并放入源码控制

3.可更新视图

你已经看到我们可以在选择语句中使用视图，就像表格一样，而且我们也可以在插入，更新和删除子句中使用它们，但仅限于特定情况

如果视图没有DISTINCT关键字，或者任何聚合函数 比如MIN、MAX、SUM什么的，GROUP BY或者HAVING子句,这些通常会一起出现,因为大多数时候,我们使用聚合函数的话也要用到GROUP BY子句，最后还有UNION运算符。

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

如果我们的视图没有以上任何东西，我们就说那个视图是可更新视图（我们可以在上面更新数据），所以我们可以在INSERT、UPDATE和DELETE语句中使用这类视图

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

我们可以在视图中删除记录 就像普通的表一样

DELETE FROM invoices_with_balance
WHERE invoice_id = 1

运行之后，回到视图刷新结果，发现id为1的发票经没有了，以下是更新

UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id = 2 -- 往后更新两天

我们还可以通过此视图插入新的发票，但这个有点棘手。因为，这只有在视图在基础表中拥有所有所需的列的情况下才有效，例如，如果我们的视图中没有这个发票日期列，我们无法在这个视图中插入任何行，因为我们的表不能接受插入日期列的空值（插入不能为空值）某一列必须全部有数值

4.WITH OPTION CHECK子句

通过视图更新或删除数据时，有些行可能会消失（这是视图的默认行为： 通过视图更新数据，修改的那行可能不会再留在视图里了）

想防止这种情况，不希望UPDATE或者DELETE语句将行从视图中删除

我们在选择语句的最后，加上WITH CHECK OPTION，这条子句会防止UPDATE或者DELETE语句将行从视图中删除（防止行消失）

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

5.视图的其他优点

视图可以减小数据库设计改动的影响

视图为我们的数据库表提供了一种抽象化，这种抽象化减少了变动带来的影响

我们可以使用视图限制基础表访问

视图可以帮助您简化查询，但也要记住，您可以使用它们在您的表上放置一个抽象层减少变化的影响，最后你可以用它们来保证数据安全