MySQL--视图

目录

1 认识视图

1.1 视图的定义

1.1 创建视图

1.2 查询

1.3 修改

1.4 删除

1.5 视图的优缺点

1.5.1 优点

1.5.2 缺点

1.6 视图的类型

1.7 视图与物化视图

2 视图检查选项

2.1 CASCADED

2.2 LOCAL

3 视图更新及作用

3.1 视图案列结合

3.1.1 屏蔽敏感数据

3.1.2 简化多表联查操作

3.2 MySQL 视图的作用

3.2.1 简化复杂查询

3.2.2  提高数据安全性

3.2.3 提供数据隔离

3.2.4 实现数据抽象

3.2.5  提高查询效率（某些场景）

3.2.6 支持逻辑分层

3.2.7 提高数据一致性

3.2.8 数据共享的便利性

---

1 认识视图

MySQL 视图（View）是一个虚拟表，它基于 SQL 查询的结果集。视图并不存储实际的数据，而是存储查询的定义。视图是一个强大的工具，可以简化复杂查询、提高数据安全性和逻辑数据独立性。理解视图的创建、使用和维护是有效管理数据库的重要部分。通过合理使用视图，可以提升数据库的灵活性和可用性。

  1.1 视图的定义

视图是一个 SQL 查询的命名结果集，可以像表一样进行查询。视图可以简化复杂的查询，使得数据访问更为方便。

  1.1 创建视图

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

  1.2 查询

查看创建视图语句

SHOW CREATE VIEW 视图名称;

查看视图数据：SELECT * FROM 视图名称；

  1.3 修改

某些视图是可更新的，可以通过视图直接更新底层表的数据。然而，并非所有视图都是可更新的。可更新视图的条件包括：

• 视图基于单一表。
• 视图不包含聚合函数、DISTINCT、GROUP BY、HAVING、UNION 等。

方式一：CREATE [OR REPLACE] VIEW 视图名称（列名列表)] AS SELECT 语句 [WITH [CASCADED I LOCAL] CHECK OPTION]

方式二：ALTER VIEW视图名称（列名列表）]AS SELECT语句[WITH[CASCADED LOCAL]CHECK OPTION]

  1.4 删除

DROP VIEW [[IF EXISTS ] 视图名称

  1.5 视图的优缺点

  1.5.1 优点

• 简化查询：可以将复杂的查询封装成简单的视图，便于使用。
• 数据安全：可以限制用户对底层表的访问，只暴露必要的数据。
• 逻辑数据独立性：底层表的结构变化不会影响视图的使用，除非视图的 SQL 查询本身也需要更改。

  1.5.2 缺点

• 性能开销：查询视图时，实际执行的是视图定义的 SQL 查询，可能会导致性能下降，尤其是对于复杂视图。
• 更新限制：并非所有视图都可以更新，限制了某些操作。
• 维护复杂性：视图的数量增多可能导致数据库结构变得复杂，影响维护。

  1.6 视图的类型

• 简单视图：基于单一表的视图，通常可以直接更新。
• 复杂视图：基于多个表的视图，包含连接、聚合等操作，通常不可更新。

  1.7 视图与物化视图

MySQL 不直接支持物化视图（Materialized Views），但可以使用临时表等方式模拟物化视图的效果。物化视图将查询结果存储在磁盘上，可以提高查询性能，但需要手动刷新数据。

2 视图检查选项

当创建视图时没有指定检查选项，则在插入，更新，删除数据时会出现不符合规定的出现

当使用检查选项时，会进行检查

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL提供了两个选项：CASCADED和LOCAL,默认值为CASCADED。

  2.1 CASCADED

不仅会检查本视图的条件，还会检查所依赖视图的条件，一直往下传

满足视图三的检查约束，但不满足视图一的，插入数据失败

  2.2 LOCAL

会检查本视图的条件，如果依赖视图有检查约束则会进行检查约束，反之则不会检查

3 视图更新及作用

  3.1 视图案列结合

  3.1.1 屏蔽敏感数据

为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。

create view tb_user_v_1 as select id,name.age from tb_user

  3.1.2 简化多表联查操作

查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。

select s.,c.* from student s,course c,student_course sc where s.id= sc.student_id and c.id =sc.course_id

create view tb_stu_course_view as select s.,c.* from student s,course c,student_course sc where s.id= sc.student_id and c.id =sc.course_id

  3.2 MySQL 视图的作用

视图（View）是 MySQL 中的一种虚拟表，它并不存储数据，而是基于 SQL 查询结果的逻辑表示。视图的作用主要体现在以下几个方面：

1. 简化查询：封装复杂查询逻辑，提升可读性和维护性。
2. 提高安全性：限制用户访问底层表的数据。
3. 数据隔离：为不同用户或场景定制数据表示。
4. 隐藏复杂性：提供数据抽象层，隐藏底层表结构的变化。
5. 逻辑分层：支持分层设计，分离业务逻辑与底层数据。
6. 数据一致性：封装复杂的业务规则，统一计算逻辑。
7. 共享数据：方便数据共享和复用。

---

  3.2.1 简化复杂查询

当查询逻辑非常复杂时，可以将复杂的 SQL 查询封装成一个视图。这样用户只需查询视图，而无需重复编写复杂的 SQL。

示例：

CREATE VIEW employee_info AS
SELECT e.id, e.name, d.department_name, s.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON e.id = s.employee_id;

使用视图后：

SELECT * FROM employee_info WHERE salary > 5000;

作用：

• 简化了复杂的多表联接查询。
• 提高了查询的可读性和维护性。

---

  3.2.2  提高数据安全性

视图可以限制用户对底层表的访问权限。通过视图，用户只能看到特定的列或行，而无法直接访问整个表的数据。

示例：

假设 employees 表包含敏感的工资信息，但只希望普通用户看到员工的姓名和部门：

CREATE VIEW public_employee_info AS
SELECT name, department_id
FROM employees;

• 给用户只授予访问视图的权限： sql复制

GRANT SELECT ON public_employee_info TO 'user';

作用：

• 底层表中的敏感信息（如工资、身份证号等）不会暴露。
• 提高了数据访问的安全性。

---

  3.2.3 提供数据隔离

视图可以为不同的用户或应用程序提供不同的数据表示。开发人员可以通过视图为不同场景定制数据，而无需修改底层表结构。

示例：

• 一个视图专门为财务部门提供工资数据。
• 另一个视图专门为人力资源部门提供员工信息。

CREATE VIEW finance_view AS
SELECT id, name, salary FROM employees;CREATE VIEW hr_view AS
SELECT id, name, department_id FROM employees;

作用：

• 为不同的用户或场景提供个性化的数据表示。
• 保持底层表结构统一，避免重复创建表。

---

3.2.4 实现数据抽象

视图可以为表的底层结构提供一个抽象层，隐藏底层表的复杂性。即使底层表发生变化，只需要修改视图，而不需要修改依赖视图的查询。

示例：

假设原始表结构更改：

• 将 employees 表拆分成 employee_detail 和 employee_job 两张表，但应用程序仍然需要访问 employees 的数据。

可以通过视图模拟原始表结构：

CREATE VIEW employees AS
SELECT d.id, d.name, j.department_id, j.salary
FROM employee_detail d
JOIN employee_job j ON d.id = j.employee_id;

作用：

• 隐藏底层表的复杂变化。
• 为应用程序提供稳定的接口，减少代码改动。

---

  3.2.5  提高查询效率（某些场景）

虽然视图本身不会直接提高性能，但在某些场景下，通过预定义的视图，可减少查询的复杂性，提高开发效率。同时，结合 物化视图（MySQL 8.0 并不原生支持，需要手动实现），可以显著提升查询性能。

示例：

如果某个复杂查询经常被重复使用，可以创建视图代替：

CREATE VIEW sales_summary AS
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;

查询时直接使用视图：

SELECT * FROM sales_summary WHERE total_sales > 10000;

作用：

• 减少重复计算，降低开发工作量。

---

  3.2.6 支持逻辑分层

视图可以用于设计数据逻辑层，分离业务逻辑与底层数据，使系统结构更加清晰。

示例：

• 基础视图：提供原始数据的抽象。
• 中间视图：进行业务逻辑加工。
• 高级视图：为最终用户提供数据。

-- 基础视图
CREATE VIEW base_view AS
SELECT * FROM employees;-- 中间视图
CREATE VIEW processed_view AS
SELECT id, name, department_id, salary * 1.1 AS adjusted_salary
FROM base_view;-- 高级视图
CREATE VIEW final_view AS
SELECT name, department_id, adjusted_salary
FROM processed_view
WHERE adjusted_salary > 5000;

作用：

• 分层设计，逻辑更清晰。
• 更容易扩展和维护。

---

  3.2.7 提高数据一致性

视图可以封装复杂的业务规则，确保查询逻辑的一致性。例如，计算某些字段的值时，视图可以统一处理逻辑，减少人为错误。

示例：

假设工资总额需要动态计算：

CREATE VIEW total_salary_view AS
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

通过视图，所有查询都会动态计算总工资，保证数据一致性：

SELECT * FROM total_salary_view;

作用：

• 避免重复实现业务逻辑。
• 保证查询结果的一致性。

---

  3.2.8 数据共享的便利性

在团队协作场景中，视图可以用于共享数据。一旦创建视图，其他用户或开发者可以直接使用，不需要了解底层表的复杂结构。