8. 视图

俄罗斯刺沙蓬
62 阅读3分钟

创建视图

USE sql_invoicing;

CREATE VIEW sales_by_client AS
    SELECT 
        client_id,
        name,
        SUM(invoice_total) AS total_sales
    FROM clients c
    JOIN invoices i USING(client_id)
    GROUP BY client_id, name;

使用create view ... as可以创建一个视图

注意：视图不存储数据，数据存储在原本的表中表中。可以完全当成一张表的执行各种操作，但是和一张存储数据的表不等价

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;
2Myworks50.89
5Topiclounge767.63
3Yadel481.35
1Vinte728.34

更改或者删除视图

  1. 先删除再重建
USE sql_invoicing;

DROP VIEW IF EXISTS clients_balance;
-- 若不存在这个视图，直接 DROP 会报错，所以要加上 IF EXISTS 先检测有没有这个视图

CREATE VIEW clients_balance AS 
    ……
    ORDER BY balance DESC
  1. 通过工具修改，workbench 或者保存好视图的 sql 文件

可更新视图

CREATE OR REPLACE VIEW ... AS

如果一个视图的原始查询语句中没有如下元素：

  1. DISTINCT 去重
  2. GROUP BY/HAVING/聚合函数 (后两个通常是伴随着 GROUP BY 分组出现的)
  3. UNION 纵向连接

则该视图是可更新视图（Updatable Views），可以增删改，否则只能查。

另外，增（INSERT）还要满足附加条件：视图必须包含底层原表的所有必须字段

总之，一般通过原表修改数据，但当出于安全考虑或其他原因没有某表的直接权限时，可以通过视图来修改底层数据（？），前提是视图是可更新的。

USE sql_invoicing;

CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT 
    /* 这里有个小技巧，要插入表中的多列列名时，
    可从左侧栏中连选并拖入相关列 */
    invoice_id, 
    number, 
    client_id, 
    invoice_total, 
    payment_total, 
    invoice_date,
    invoice_total - payment_total AS balance,  -- 新增列
    due_date, 
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0;
/* 这里不能用列别名balance，会报错说不存在，
必须用原列名的表达式，这还是执行顺序的问题
之前讲WHERE和HAVING作为事前筛选和事后筛选的区别时提到过 */

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

WITH OPTION CHECK

在视图的原始查询语句最后加上 WITH CHECK OPTION 可以防止执行那些会让视图中某些行（记录）消失的修改语句。

USE sql_invoicing;

CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT 
    *
FROM invoices
WHERE (invoice_total - payment_total) > 0
with check option;

delete from invoices_with_balance
where invoice_id = 2;    -- 失败，无法删除



UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 3
-- 还是失败，因为这里修改了之后，这条语句不满足这个视图的条件会消失，所以这个条件可以阻止它运行
-- Error Code: 1369. CHECK OPTION failed 'sql_invoicing.invoices_with_balance'

视图优点总结

  1. 简化查询
  2. 增加抽象层，减少数据库设计改动的影响 视图给表增加了一个抽象层（模块化），这样如果数据库设计改变了（如一个字段从一个表转移到了另一个表），只需修改视图的查询语句使其能保持原有查询结果即可，不需要修改使用这个视图的查询。 相反，如果没有视图这一层的话，所有查询将直接使用指向原表的原始查询语句，这样一旦更改原表设计，就要相应地更改所有的这些查询。
  3. 限制对基础表的访问，加强数据安全性 在视图中可以对原表的行和列进行筛选，这样如果你禁止了对原始表的访问权限，用户只能通过视图来修改数据，他们就无法修改视图中未返回的那些字段和记录。但注意这通常并不简单，需要良好的规划，否则最后可能搞得一团乱。
avatar
文章
阅读
粉丝