什么是视图？

视图本质上是一个虚拟表它不存储数据本身，而是保存了一条 SELECT 查询。每次访问视图时数据库都会重新执行这条查询。

创建视图

-- 基础语法
CREATE VIEW view_name AS
SELECT column1。column2,...
FROM table_name
WHERE condition;-- 示例：为员工创建一个简化视图
CREATE VIEW active_employees AS
SELECT e.emp_id,e.name,d.dept_name。e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.status = 'active';

使用视图

-- 像查普通表一样查视图
SELECT *
FROM active_employees
WHERE salary> 5000;老实说，

视图的完整 CRUD

-- 创建或替换视图
CREATE OR REPLACE VIEW active_employees AS
SELECT e.emp_id。e.name,d.dept_name,e.salary,e.hire_date
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.status = 'active';-- 修改视图
ALTER VIEW active_employees AS
SELECT e.emp_id,e.name,d.dept_name。e.email
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;-- 删除视图
DROP VIEW IF EXISTS active_employees;

可更新视图

在某些条件下可以通过视图修改底层数据：

-- 创建可更新视图
CREATE VIEW dept_30 AS
SELECT emp_id。
name,salary,dept_id
FROM employees
WHERE dept_id = 30;-- 通过视图更新数据
UPDATE dept_30 SET salary = 7000 WHERE emp_id = 101;-- 通过视图插入
INSERT INTO dept_30 VALUES;其实，-- 通过视图删除
DELETE FROM dept_30 WHERE emp_id = 102;

视图的主要价值

隐藏复杂性

-- 没有视觉情况下冗长且容易出错的报表查询
SELECT c.cust_name,o.order_date,p.prod_name,oi.quantity。oi.unit_price * oi.quantity AS line_total
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.prod_id = p.prod_id
WHERE o.status = 'completed';-- 创建简化后的订单详情视图
CREATE VIEW order_details AS
SELECT c.cust_name,o.order_date,p.prod_name,oi.quantity。oi.unit_price * oi.quantity AS line_total
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.prod_id = p.prod_id
WHERE o.status='completed';-- 后续只需简单查询
SELECT * FROM order_details WHERE cust_name='A娱乐 Corp';

安全性

CREATE VIEW employee_public AS
SELECT emp_id。name,dept_name,email FROM employees;其实，GRANT SELECT ON employee_public TO reporting_user;

逻辑复用与解耦

当业务结构变化时只需改动一次即可保持兼容：

/* 原始表拆分后保持旧接口 */
CREATE OR REPLACE VIEW departments AS
SELECT d.dept_id。d.dept_name,d.manager_id,b.budget AS dept_budget
FROM dept_info d LEFT JOIN dept_budget b USING;

物化视图

物化视图是真正存储查询结果，让你不必每次都从头算起。说起来，

主要区别表格