MySQL第八章:视图

2023-12-31 05:57:53

创建视图

就是创建虚拟表,自动化一些重复性的查询模块,简化各种复杂操作(包括复杂的子查询和连接等),可以把部分查询或子查询存储在视图里,后期可以始终这些视图,就不再需要再写一次查询了。

注意:

视图虽然可以像一张表一样进行各种操作,但并没有真正储存数据,数据仍然储存在原始表中,视图只是储存起来的模块化的查询结果(类似于变量,给变量赋值为表格中的数据,同时变量指向了表格中的数据),是为了方便和简化后续进一步操作而储存起来的虚拟表。

案例

题目:

创建 sales_by_client 视图,包括client_id、name和每人的发票总额。

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;
    -- 虽然实际上这里加不加上name都一样

注意:

  • 运行上述代码后,在左边的SCHEMAS(模式)栏里点击刷新,此时sql_invoicing数据库的Views下拉菜单里就会出现sales_by_client视图,我们同样可以对其进行查看数据(点击右边的闪电按钮),把它当作表格来使用;

  • 若要删掉该视图用 DROP VIEW sales_by_client 或通过右键菜单;

  • 后期如果需要筛选出最佳客户(发票总额最高)的名单,可以直接from这个视图上进行操作。

创建视图后可就当作 sql_invoicing 数据库下一张表一样进行各种操作

操作要求:

从sales_by_client 视图中筛选出发票总额大于500的客户名字、发票总额和手机号码。

USE sql_invoicing;

SELECT 
    s.name,
    s.total_sales,
    phone
FROM sales_by_client s
JOIN clients c USING(client_id)
WHERE s.total_sales > 500

练习

题目:

创建一个客户差额表视图,可以看到客户的id,名字以及差额(发票总额-支付总额)

USE sql_invoicing;

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

更新或删除视图

修改视图可以先DROP在CREATE(也可以用CREATE OR REPLACE)

注意:

视图的查询语句可以在编辑模式下查看和修改,但最好是保存为sql文件并放在源码控制妥善管理

案例

题目:

在上一节的顾客差额视图的查询语句最后加上按差额降序排列,并删除视图再重建。(两种方法来实现)

-- 法1. 先删除再重建

USE sql_invoicing;

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

CREATE VIEW clients_balance AS 
    SELECT 
        client_id,
        c.name,
        SUM(invoice_total - payment_total) AS balance
    FROM clients c
    JOIN invoices USING(client_id)
    GROUP BY client_id
    ORDER BY balance DESC
-- 法2. 用REPLACE关键字
USE sql_invoicing;

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

注意:

  • 法一:先删除再重建 用的多一点;
  • 法二不需要先删除视图。

如何保存视图的原始查询语句?

  • 法一(推荐方法)

    将原始查询语句保存为 views 文件夹下的和与视图同名的 clients_balance.sql 文件,然后将这个文件夹放在源码控制下, 通常放在 git repository(仓库)里与其它人共享,团队其他人因此能在自己的电脑上重建这个数据库。

  • 法二

    若丢失了原始查询语句,要修改的话可点击视图的扳手按钮(三个图案中的第一个图案)打开编辑模式,可看到如下被MySQL处理了的查询语句

    d545a64faf5c58b53f4d2189a647f3c

    MySQL在前面加了些莫名其妙的东西(line2-line4),这些是MySQL自己自动加上去的。并且在所有库名表名字段名外套上反引号(` `),防止名称冲突(如果当对象名和MySQL里的关键字相同时确保被当作对象名而不是关键字,会有反引号(` `)来防止名称冲突),但这都不影响。如果想要在编辑模式的窗口上进行修改代码,比如order by total_sales desc,可以点击右下角的apply,可以发现apply后自己增加的代码格式会和上面代码格式一样,变为 order by `total_sales` desc。

可更新视图

视图作为虚拟表/衍生表,除了可用在查询语句SELECT中,也可以用在增删改(INSERT DELETE UPDATE)语句中,但后者有一定的前提条件。

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

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

则该视图是可更新视图(Updatable Views),可以增删改,否则只能查。

另外,增(INSERT)还要满足附加条件:视图必须包含底层原表的所有必须字段

注意:

对视图做的变化会映射到原表上(也会对原表进行相应的变化)

案例

题目:

创建一个视图(新虚拟表)invoices_with_balance,可以看到invoice的所有字段以及差额(发票总额-支付总额),并筛选出差额>0的包含以上字段的记录。

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

该视图满足条件,是可更新视图,故可以增删改(修改视图的数据也会更新原表)

  • 删:删掉id为1的发票记录
DELETE FROM invoices_with_balance
WHERE invoice_id = 1
  • 改:将2号发票记录的期限延后两天
UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id = 2

在视图中用INSERT新增记录的话还有另一个前提,即视图必须包含其底层所有原始表的所有必须字段。

例如,若这个 invoices_with_balance 视图里没有 invoice_date 字段(invoices 中的必须字段,,也就是NN字段,字段值不能为空),那就无法通过该视图向 invoices 表新增记录,因为 invoices 表不会接受 invoice_date 字段为空的记录

WITH CHECK OPTION 子句

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

案例

题目新invoices_with_balance 视图与原始的 orders 表相比增加了balance(invoice_total - payment_total) 列,现要更新视窗,让invoice_id为2 的payment_total = invoice_total.

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2

注意:

  • 执行完上述语句后,需要点击左边SCHEMAS模式的更新按钮,更新后会发现invoices_with_balance视图里2号订单消失;
  • 在视图原始查询语句(非关键字两边会有``)的最后加入 WITH CHECK OPTION 后,对3号订单执行类似上面的语句后会报错:Error Code: 1369. CHECK OPTION failed ‘sql_invoicing.invoices_with_balance’`;
  • 若执行不了,点击偏好设定–SQL editor–将最下面的safe updates打开。

视图的其他优点

三大优点:

简化查询、增加抽象层和减少变化的影响、数据安全性

具体来讲:

  1. (首要优点)简化查询

  2. 增加抽象层,减少变化的影响 :视图给表增加了一个抽象层(模块化),这样如果数据库设计改变了(如一个字段需要重新命名),只需修改视图的查询语句使该字段重新命名为新的别名,不需要在多个sql文件中修改使用涉及到原表(from和join)的那几十个查询。相反,如果没有视图这一层的话,所有查询将直接使用指向原表的原始查询语句,这样一旦更改原表设计,就要相应地更改所有的这些查询。

    详细解释:

    原表结构变动的挑战

    在没有使用视图的情况下,如果原表的结构发生变化,如更改列名、数据类型,或者重新组织数据,所有直接依赖于这个表的SQL查询都需要相应地进行修改。这是因为这些查询直接引用了原表中的具体列名和结构。

    视图带来的独立性和维护简化

    当使用视图时,这些查询不再直接依赖于原表的具体结构,而是依赖于视图。视图充当了原表和查询之间的中间层,它向查询提供了一致的接口,即使底层的原表结构发生变化。

    例子

    假设有一个原表 Employee,它有一个列 Name。许多查询都是基于这个列进行的。现在,由于某种原因,需要将这个列更名为 EmployeeName。如果直接在原表上进行这个更改,所有引用了 Name 列的查询都需要修改。

    但是,如果有一个视图 View_Employee,它定义如下:

    CREATE VIEW View_Employee AS
    SELECT Name, Department
    FROM Employee;
    

    所有查询都是基于这个视图进行的,而不是直接基于 Employee 表。当 Employee 表中的 Name 列更名为 EmployeeName 时,你只需要更新这个视图的定义:

    CREATE OR REPLACE VIEW View_Employee AS
    SELECT EmployeeName AS Name, Department
    FROM Employee;
    

    这个新定义中,EmployeeName 列被重新命名为 Name,这样所有基于 View_Employee 视图的查询都不需要做任何修改。它们仍然可以按照之前的方式运行,因为视图对它们隐藏了底层原表结构的变化。

  3. 限制对原数据的访问权限 :在视图中可以对原表的行和列进行筛选,这样如果你禁止了对原始表的访问权限,用户只能通过视图来修改数据,他们就无法修改视图中未返回的那些字段和记录。但注意这通常并不简单,需要良好的规划,否则最后可能搞得一团乱,

文章来源:https://blog.csdn.net/Alex_sdhjkb/article/details/135278834
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。