MySQL 视图

核心目标： 学习如何创建和使用视图，以简化复杂的查询、提供数据访问控制、实现逻辑数据独立性，并通过 WITH CHECK OPTION 保证数据一致性。

什么是视图？
视图（View）是一种虚拟表，其内容由一个 SQL 查询定义。它本身不包含任何数据，而是像一个窗口，通过它可以查看或操作一个或多个基表（实际存储数据的表）中的数据。视图存储的是 SELECT 语句，而不是数据本身。视图有助于简化复杂性并可以增强安全性。

为什么使用视图？

1. 简化复杂查询： 封装复杂的 JOIN 或 WHERE 子句。
2. 数据访问控制： 只暴露允许用户查看的数据列或行。
3. 逻辑数据独立性： 基表结构变化对视图查询的影响较小。
4. 数据一致性: (通过 ALTER VIEW 添加 WITH CHECK OPTION) 确保通过视图修改的数据仍然符合视图的定义标准。

1. 创建视图

作用：定义并创建一个新的视图。

语法：

create [or replace] view view_name [(column_list)]
as
select_statement;

说明：

• or replace: 若视图存在则替换。
• view_name: 视图名称。
• (column_list): (可选) 视图列名。
• select_statement: 定义视图的 SELECT 查询。

示例:

-- 创建只显示计算机科学专业学生的视图
create view cs_students_view as
select student_id, student_name, gpa
from students
where major = '计算机科学';

-- 创建显示员工姓名和部门名称的视图 (基于 JOIN)
create view employee_department_view as
select e.emp_name, d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id;

-- 创建视图并指定列名
create view product_summary_view (id, name, available_stock) as
select product_id, product_name, stock_quantity
from products
where stock_quantity > 0;

2. 查询视图

作用：像查询普通表一样查询视图。

语法：

select column_list from view_name [where condition ...];

示例:

-- 查询所有计算机科学专业的学生信息
select * from cs_students_view;

-- 查询特定员工的部门信息
select dept_name from employee_department_view where emp_name = 'Alice';

3. 修改视图

作用：更改现有视图的定义，可以修改 SELECT 语句，也可以添加或移除 WITH CHECK OPTION。

语法：

alter view view_name [(column_list)]
as
new_select_statement
[with [cascaded | local] check option];

说明：

• 参数与 CREATE VIEW 类似，但用于修改已存在的视图。
• WITH [CASCADED | LOCAL] CHECK OPTION: (可选) 此子句用于可更新视图。它强制检查通过视图进行的 INSERT 或 UPDATE 操作，确保结果行仍然满足视图定义中的 WHERE 条件。如果操作违反此条件，将被拒绝。
  • LOCAL: 只检查当前视图的 WHERE 条件。
  • CASCADED (默认): 检查当前视图及其所有基础视图的 WHERE 条件。

示例:

-- 示例 1: 修改 cs_students_view，增加显示 email 列
alter view cs_students_view as
select student_id, student_name, email, gpa
from students
where major = '计算机科学';

-- 示例 2: 给一个已存在的视图 high_stock_products_view 添加 WITH CHECK OPTION
alter view high_stock_products_view as
select product_id, product_name, stock
from products
where stock > 100
with check option; -- 添加检查选项 (默认为 CASCADED)

-- 示例 3: 演示 CASCADED 检查选项
-- 假设已存在视图 v_expensive_products (price > 100)
-- create view v_expensive_products as select * from products where price > 100;
-- 再假设已存在视图 v_expensive_electronics 基于 v_expensive_products
-- create view v_expensive_electronics as select * from v_expensive_products where category = 'Electronics';

-- 现在修改 v_expensive_electronics 添加 CASCADED 检查
alter view v_expensive_electronics as
select product_id, product_name, category, price
from v_expensive_products
where category = 'Electronics'
with cascaded check option; -- 明确使用 CASCADED

-- 后续如果尝试通过 v_expensive_electronics 更新某产品价格低于 100
-- (如: update v_expensive_electronics set price = 80 where product_id = 1;)
-- 该操作将会失败，因为 CASCADED 会检查基础视图 v_expensive_products 的 price > 100 条件。

4. 删除视图

作用：永久删除一个或多个视图。不影响基表数据。

语法：

drop view [if exists] view_name1, [view_name2, ...];

示例:

-- 删除视图
drop view if exists product_summary_view;

5. 视图的可更新性

并非所有视图都可更新（支持 INSERT, UPDATE, DELETE）。通常需要满足：

• 无聚合函数、DISTINCT、GROUP BY、HAVING、UNION 等。
• 基于单个基表（或特定类型 JOIN）。
• INSERT 时，视图包含基表所有 NOT NULL 列。

简单判断：视图的行能唯一映射回基表的一行，则通常可更新。

6. 视图的优点与注意点

优点:

• 简化性
• 安全性
• 一致性
• 逻辑独立性

注意点:

• 性能: 视图是虚拟的，每次查询都执行其定义。复杂视图需关注性能。
• 更新限制: 不是所有视图都可更新。
• 嵌套视图: 避免过多嵌套。

---

练习题

假设使用之前的 employees 和 departments 表。

1. 创建一个名为 it_employees_view 的视图，显示所有 IT 部门（dept_id=2）员工的 ID (emp_id)、姓名 (emp_name) 和工资 (salary)。
   答案:

   create view it_employees_view as
   select emp_id, emp_name, salary
   from employees
   where dept_id = 2;
   

2. 查询 it_employees_view 视图，找出工资高于 78000 的 IT 部门员工。
   答案:

   select * from it_employees_view where salary > 78000;
   

3. 创建一个名为 dept_summary_view 的视图，显示每个部门的名称 (dept_name) 以及该部门的员工总数（列名为 employee_count）。
   答案:

   create view dept_summary_view as
   select d.dept_name, count(e.emp_id) as employee_count
   from departments d
   left join employees e on d.dept_id = e.dept_id
   group by d.dept_name;
   

4. 查询 dept_summary_view 视图，找出员工人数为 0 的部门。
   答案:

   select * from dept_summary_view where employee_count = 0;
   

5. 修改 it_employees_view 视图，为其添加 WITH CHECK OPTION，以确保将来通过此视图进行的任何更新或插入操作都必须保持员工的 dept_id 为 2。
   答案:

   alter view it_employees_view as
   select emp_id, emp_name, salary
   from employees
   where dept_id = 2
   with check option;
   

6. 删除 dept_summary_view 视图。
   答案:

   drop view if exists dept_summary_view;
   

---