关于MySQL视图知识点总结

一、视图的概念

MySQL视图其实它是一种虚拟表，我们可以看作是它一个存储了特定查询结果的命名查询的结果显示。与物理表不同，视图并不实际存储数据，而是通过执行定义视图时查询的SQL语句获取查询数据。

以下通过案例来说明MySQL视图的概念和用法。

案例背景： 假设我们有一个简单的数据库，其中包含一个名为employees的表，用于存储员工信息，包含以下字段：

• employee_id: 员工ID（主键）
• first_name: 员工名字
• last_name: 员工姓氏
• department: 员工所属部门
• salary: 员工薪水

我们希望创建一个视图，用于展示部门为“Sales”的员工信息，只包含员工ID、姓名和薪水这三个字段。我们将通过案例来演示如何创建视图以及如何使用它。

步骤 1：创建员工表 首先，我们创建一个employees表并插入一些示例数据：

sqlCopy codeCREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES
  (1, 'John', 'Doe', 'Sales', 50000.00),
  (2, 'Jane', 'Smith', 'HR', 45000.00),
  (3, 'Mike', 'Johnson', 'Sales', 55000.00),
  (4, 'Emily', 'Williams', 'Finance', 60000.00);

步骤 2：创建视图 现在，我们创建一个名为sales_employees的视图，仅包含部门为“Sales”的员工信息：

sqlCopy codeCREATE VIEW sales_employees AS
SELECT employee_id, CONCAT(first_name, ' ', last_name) AS full_name, salary
FROM employees
WHERE department = 'Sales';

上述视图定义了一个查询，选择了employees表中部门为“Sales”的员工的employee_id、first_name和last_name字段，并使用CONCAT函数将名字和姓氏合并为full_name字段。该视图仅返回满足条件的行。

步骤 3：使用视图 现在，我们可以使用刚刚创建的sales_employees视图来查询部门为“Sales”的员工信息，如下所示：

sqlCopy codeSELECT * FROM sales_employees;

查询结果将显示如下：

diffCopy code+-------------+--------------+---------+
| employee_id | full_name    | salary  |
+-------------+--------------+---------+
| 1           | John Doe     | 50000.00|
| 3           | Mike Johnson | 55000.00|
+-------------+--------------+---------+

通过这个案例，我们可以看到使用MySQL视图可以将复杂的查询逻辑封装起来，并提供一个更加简洁方便的数据访问方式。视图还可以帮助我们隐藏底层数据表的细节，只暴露特定的信息给用户，提高了数据库的安全性和易用性。

二、视图的注意点

关于视图的更新，需要注意以下几点：

1. 可更新视图： 有些视图是可以进行更新的，也就是说，可以通过视图对基本表的数据进行修改、插入和删除操作。这类视图必须满足一定的条件：
1. 视图必须只涉及单个基本表（不涉及联接操作或子查询）。
2. 视图的查询中不能包含以下元素：聚合函数（如SUM、COUNT等）、DISTINCT关键字、GROUP BY子句、HAVING子句。
3. 视图的查询中不能使用子查询。

2. 不可更新视图： 某些情况下，视图是不可更新的，无法对其进行数据修改、插入和删除操作。这通常出现在涉及到复杂查询的视图或使用了不符合可更新视图条件的查询。

3. 更新视图的限制： 在对可更新视图进行更新时，需要注意以下限制：
1. 视图的更新仅能影响其基本表中的数据，而不能修改视图查询中涉及其他表的数据。
2. 更新视图时，视图的定义中必须包含基本表的主键，这样系统才能准确找到需要更新的行。
3. 视图的定义必须满足视图可更新的条件，否则更新操作将被拒绝。
4. 使用UPDATE、INSERT和DELETE进行更新： 可以使用与基本表相同的方式，使用UPDATE、INSERT和DELETE语句对视图进行更新操作。这些更新操作将反映在视图对应的基本表中，其实就是会影响到物理表中的结果。

三、可更新视图

可更新视图的情况：

案例：创建可更新的视图 假设我们有一个名为students的表，用于存储学生信息，包含以下字段：

• student_id: 学生ID（主键）
• first_name: 学生名字
• last_name: 学生姓氏
• age: 学生年龄

我们创建一个视图young_students，用于显示年龄小于等于25岁的学生信息，并且该视图是可更新的：

sqlCopy codeCREATE VIEW young_students AS
SELECT student_id, CONCAT(first_name, ' ', last_name) AS full_name, age
FROM students
WHERE age <= 25;

现在，我们可以使用该视图来进行更新操作：

sqlCopy code-- 更新视图中学生ID为1的学生的年龄
UPDATE young_students SET age = 26 WHERE student_id = 1;

-- 在视图中插入一条新的学生信息
INSERT INTO young_students (student_id, full_name, age)
VALUES (5, 'Alice Johnson', 23);

-- 从视图中删除学生ID为2的学生信息
DELETE FROM young_students WHERE student_id = 2;

上述更新操作将会修改students表中符合条件的数据，因为young_students视图只显示了年龄小于等于25岁的学生信息，所以更新也仅限于这部分数据。

需要再次强调，更新视图时必须满足可更新视图的条件，否则更新操作将会失败。对于不可更新视图，尝试执行更新操作时会产生错误提示。

四、不可更新视图

不可更新的视图，这类视图不支持对其进行数据修改、插入和删除操作。当尝试对不可更新的视图执行更新操作时，系统会产生错误提示，阻止这些操作的执行。

不可更新视图的主要原因是视图的定义涉及了某些复杂的查询操作或使用了不符合可更新视图条件的查询元素，例如：

1. 包含聚合函数的视图： 如果视图的查询包含了聚合函数（如SUM、COUNT等）或使用了GROUP BY子句，那么这个视图就是不可更新的。
2. DISTINCT关键字： 视图的查询中如果使用了DISTINCT关键字，该视图也会成为不可更新的视图。
3. 包含子查询的视图： 视图的查询中如果使用了子查询，特别是在SELECT列表或WHERE子句中，这样的视图通常也是不可更新的。
4. 涉及多个基本表的视图： 视图定义涉及多个基本表的联接操作，会使视图成为不可更新的。

下面是一个示例来演示一个不可更新的视图：

案例：创建不可更新的视图 假设我们有一个名为orders的表，用于存储订单信息，包含以下字段：

• order_id: 订单ID（主键）
• product_name: 商品名称
• quantity: 商品数量
• price: 商品单价

现在，我们尝试创建一个视图high_value_orders，用于显示订单金额大于1000的订单信息：

sqlCopy codeCREATE VIEW high_value_orders AS
SELECT order_id, product_name, quantity * price AS total_price
FROM orders
WHERE quantity * price > 1000;

上述视图的查询使用了quantity * price来计算订单的总金额，然后筛选出金额大于1000的订单。由于涉及了计算和筛选操作，这个视图是不可更新的视图。

因此，如果尝试对high_value_orders视图执行UPDATE、INSERT或DELETE操作，将会产生错误提示，告知视图不可更新的原因。

总结而言，不可更新的视图是基于复杂查询或包含特定元素的查询而定义的，它们主要用于提供只读的数据访问，无法直接修改基本表中的数据。如果需要对数据进行修改，需要使用可更新的视图，并满足视图可更新的条件。