第一部分：Creating Views - 创建视图

--- 当使用了好几个Join和Subquery时 查询会变得非常复杂
--- 这个时候就可以使用视图来拯救繁琐的查询了
--- 我们可以把这些查询或者子查询存到视图里
--- 这会大大简化选择语句
--- 然后这些视图也可以被复用，而不需要再写一次查询

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

--- Views中就可以看到 新建的视图了
--- 从此 和表格一样 我们可以从这个视图中选择数据
--- 例如

SELECT *
FROM sales_by_client
ORDER BY total_sales DESC

SELECT *
FROM sales_by_client
WHERE total_sales > 500

--- 还可以把它和任何有client_id列的表做连接
SELECT *
FROM sales_by_client
JOIN clients USING (client_id)

EXERCISE：

Part1 - Creating Views - 创建视图
-- create a view to see the balance for each client.
-- 
-- clients_balance

-- client_id
-- name
-- balance
-- 结余的计算方法是用发票表中的发票总额减去支付总额

SOLUTION:

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

第二部分：Altering or Dropping Views - 更改或删除视图

--- 一种方法是删除视图并重新创建
DROP VIEW sales_by_client

--- 另一种方法就是使用REPLACE关键字
--- 更推荐这种方式，因为你不需要先删除视图
--- 想执行这个语句多少次都行
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文件里，并把这些文件放到源码控制
--- 把文件放入Git储存库并和别人分享储存库是很常见的做法
--- 所以别人也可以在他们的电脑上重建这个数据库

--- 第二种方法时点击中间的图标，这样视图就会以编辑模式打开
--- 但是会看到很多不是我们输入的代码
--- 这些是MySQL自己加上去的
--- MySQL在你的表和列名称上打上这个反引符号，是为了防止名称冲突
--- 所以如果你们使用特定的SQL语言中有涵义的关键字
--- MySQL就会以表或者视图或者列名称去处理它们
--- 直接在编辑模式下 写SQL -》apply -》apply
--- 更推荐保存sql文件

第三部分：Updatable Views - 可更新视图

--- 可以在SELECT语句中使用Views
--- 也可以在INSERT、UPDATE、DELETE语句中使用它们，但是仅限于特定情况

--- 如果视图没有DISTINCT关键字，
--- Aggregate Functions聚合函数，比如MIN、MAX、SUM什么的
--- GROUP BY / HAVING 子句
--- UNION Operator

--- Aggregate Functions 和 GROUP BY / HAVING通常会一起出现
--- 因为大多数时候，我们使用聚合函数的话也要用到GROUP BY子句
--- 所以如果我们的视图里没有以上提到的任何东西
--- 我们就说那个视图是可更新视图，也就是说，我们可以在这上面更新数据
--- 所以我们可以在INSERT、UPDATE和DELETE语句中使用这类视图

--- invoices表中有两列：invoice_total、payment_total
--- 但是没有balance这列
--- 要用invoice_total减去payment_total
--- 现在我们可以为发票表创建视图，视图里有每张发票的结余

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
--- WHERE这里我们不能直接用balance
--- 如果使用的话 MySQL会告诉你说 这里没有叫'balance'的列
--- 在这个View里，我们没有用到DISTINCT关键字，
--- 没用到任何聚合函数或者GROUP BY，也没用UNION运算符
--- 所以这是一个可更新视图，我们可以用它来修改数据

DELETE FROM invoices_with_balance
WHERE invoice_id = 1
--- 我们可以在视图中删除记录，就像普通的表一样

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

--- 我们还可以在视图中插入新发票
--- 但 只有当视图有所有基础表中要用到的列 插入新发票才会生效
--- 比如 如果视图里没有发票日期列
--- 就不能往视图里插入任何行了
--- 因为我们的表不允许插入的日期列为空值

--- 大多数情况我们在表中更新数据
--- 但是处于安全原因 有时候可能并没有某张表的直接权限
--- 所以我们只能通过视图修改数据
--- 那样的情况下可以通过使用视图插入、更新或者删除数据
--- 前提是：视图是可更新的

第四部分：The WITH OPTION CHECK Clause - WITH OPTION CHECK子句

--- 沿用上节课的一个例子 来看看更新其中一张发票的付款总计会发生什么
UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2

--- invoice_id = 2的发票消失了，这是视图的默认行为
--- 当你通过视图更新或者删除数据，一些行可能会消失
--- 有时候你不想让行消失
--- 不希望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
--- 这条子句将会防止UPDATE或者DELETE语句将行从视图中删除
--- 先执行这段语句，来重新创建这个视图

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 3
--- 输出窗口的错误显示‘检查视图失败’
--- 所以默认情况下，通过视图更新数据
--- 修改的那行可能不会留在视图里了

--- 我们可以使用WHIT CHECK OPTION子句防止行消失
--- 所以如果你试图修改一行 且结果可能导致行从视图里被删除
--- 你就会得到错误提示

第五部分：Other Benefits of Views - 视图的其他优点

--- 视图可以帮助我们简化查询
--- 视图可以减小数据库设计改动的影响

--- 假设 在发票表的最上面有10段查询
--- 第二天你想给这张表做些变动
--- 你可能想把表或者其中一列重命名
--- 或者可能你想把这张表的某列移动到其他表
--- 在这种情况下
--- 你必须回去修改所有涉及到这张表的查询
--- 现在 如果我告诉你 你不能对该表写任何查询
--- 只能使用之前创建的invoices_with_balance视图 你该怎么办？
--- 这个视图 看起来和我们的invoices表几乎没差别
--- 但是它有额外的一列:balance

--- 现在 如果我们要对基础表做改动 也就是这张invoices
--- 我们只用修改视图代码 对变动列使用别名 来找回它们原来的名称
--- 这样设计的话 我们只需要改动视图就可以了
--- 它们不会受基础表（invoices table）的影响

--- 所以 视图为我们的数据库表提供了一种抽象化
--- 这种抽象化减少了变动带来的影响
--- 使用视图还有另外一个优点
--- 我们可以使用视图限制基础表访问
--- 例如 在视图中可能会使用WHERE子句筛选记录
--- 或者 可能从基础表汇总删除一些列
--- 如果你删除表的直接访问
--- 用户就只能通过视图更新数据
--- 他们就无法修改某列的值或者修改非视图返回的行