视图

视图（View）是一个虚拟表，其内容由查询定义。本章将介绍MySQL视图的创建、修改和使用方法。

视图概述

/*
 * 视图的特点：
 * 1. 虚拟表，不存储实际数据
 * 2. 数据来自定义视图的查询
 * 3. 简化复杂查询
 * 4. 提供数据安全性
 * 5. 数据独立性
 * 
 * 视图的优点：
 * 1. 简化复杂查询
 * 2. 保护敏感数据
 * 3. 提供向后兼容性
 * 4. 重用SQL语句
 * 
 * 视图的限制：
 * 1. 不能创建临时表上的视图
 * 2. 不能在视图上创建触发器
 * 3. 某些视图不可更新
 */

创建视图

基本语法

-- 创建视图的基本语法
/*
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名 [(列名列表)]
AS 查询语句
[WITH [CASCADED | LOCAL] CHECK OPTION];
*/

-- 准备示例数据
USE company;

-- 创建简单视图
CREATE VIEW v_employees AS
SELECT id, name, department_id, salary
FROM employees;

-- 查询视图
SELECT * FROM v_employees;

-- 创建带列别名的视图
CREATE VIEW v_employee_info (员工ID, 姓名, 部门ID, 工资) AS
SELECT id, name, department_id, salary
FROM employees;

SELECT * FROM v_employee_info;

复杂视图

-- 创建多表连接视图
CREATE VIEW v_employee_dept AS
SELECT 
    e.id,
    e.name AS employee_name,
    d.name AS department_name,
    e.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

SELECT * FROM v_employee_dept;

-- 创建聚合视图
CREATE VIEW v_dept_stats AS
SELECT 
    d.name AS department_name,
    COUNT(e.id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name;

SELECT * FROM v_dept_stats;

-- 创建子查询视图
CREATE VIEW v_high_salary_employees AS
SELECT 
    e.name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees
);

SELECT * FROM v_high_salary_employees;

OR REPLACE

-- 使用OR REPLACE创建或替换视图
CREATE OR REPLACE VIEW v_employees AS
SELECT 
    id, 
    name, 
    department_id, 
    salary,
    hire_date
FROM employees;

-- 等价于先删除再创建
DROP VIEW IF EXISTS v_employees;
CREATE VIEW v_employees AS
SELECT id, name, department_id, salary, hire_date
FROM employees;

查看视图

-- 查看数据库中的视图
SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';

-- 查看视图结构
DESC v_employees;

-- 查看视图定义
SHOW CREATE VIEW v_employees;

-- 从information_schema查询视图
SELECT 
    TABLE_NAME,
    VIEW_DEFINITION,
    CHECK_OPTION,
    IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

修改视图

-- 使用ALTER VIEW修改视图
ALTER VIEW v_employees AS
SELECT 
    id, 
    name, 
    department_id, 
    salary,
    hire_date,
    status
FROM employees;

-- 或使用CREATE OR REPLACE
CREATE OR REPLACE VIEW v_employees AS
SELECT 
    id, 
    name, 
    department_id, 
    salary
FROM employees
WHERE status = 'active';

可更新视图

/*
 * 可更新视图的条件：
 * 1. 没有聚合函数（SUM、COUNT等）
 * 2. 没有DISTINCT
 * 3. 没有GROUP BY、HAVING
 * 4. 没有UNION
 * 5. 没有子查询
 * 6. 视图中的列来自单个表
 */

-- 创建可更新视图
CREATE VIEW v_active_employees AS
SELECT id, name, salary, department_id
FROM employees
WHERE status = 'active';

-- 通过视图插入数据
INSERT INTO v_active_employees (name, salary, department_id)
VALUES ('新员工', 12000, 1);

-- 通过视图更新数据
UPDATE v_active_employees 
SET salary = 13000 
WHERE name = '新员工';

-- 通过视图删除数据
DELETE FROM v_active_employees WHERE name = '新员工';

-- 使用WITH CHECK OPTION
CREATE VIEW v_tech_employees AS
SELECT id, name, salary, department_id
FROM employees
WHERE department_id = 1
WITH CHECK OPTION;

-- 插入数据时检查条件
INSERT INTO v_tech_employees (name, salary, department_id)
VALUES ('技术员', 15000, 1);  -- 成功

-- INSERT INTO v_tech_employees (name, salary, department_id)
-- VALUES ('销售员', 15000, 2);  -- 失败，不满足department_id = 1

删除视图

-- 删除视图
DROP VIEW v_employees;

-- 删除多个视图
DROP VIEW v_employee_dept, v_dept_stats;

-- 安全删除（如果存在）
DROP VIEW IF EXISTS v_employees;

视图应用场景

-- 场景1：简化复杂查询
CREATE VIEW v_order_details AS
SELECT 
    o.order_no,
    u.username,
    p.name AS product_name,
    oi.quantity,
    oi.price,
    oi.quantity * oi.price AS subtotal
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 使用视图简化查询
SELECT * FROM v_order_details WHERE username = 'user1';

-- 场景2：数据安全（隐藏敏感列）
CREATE VIEW v_public_employees AS
SELECT id, name, department_id
FROM employees;
-- 不暴露salary等敏感信息

-- 场景3：数据格式化
CREATE VIEW v_formatted_data AS
SELECT 
    id,
    name,
    DATE_FORMAT(hire_date, '%Y年%m月%d日') AS 入职日期,
    FORMAT(salary, 2) AS 工资
FROM employees;

-- 场景4：业务逻辑封装
CREATE VIEW v_monthly_report AS
SELECT 
    DATE_FORMAT(hire_date, '%Y-%m') AS 月份,
    COUNT(*) AS 入职人数,
    SUM(salary) AS 工资总额
FROM employees
GROUP BY DATE_FORMAT(hire_date, '%Y-%m');

视图算法

/*
 * 视图算法：
 * 
 * MERGE：将视图定义的SQL与查询SQL合并
 *        - 性能好
 *        - 可以使用索引
 * 
 * TEMPTABLE：将视图结果存入临时表
 *            - 性能较差
 *            - 不能使用视图上的索引
 * 
 * UNDEFINED：MySQL自动选择（默认）
 */

-- 指定算法创建视图
CREATE ALGORITHM = MERGE VIEW v_merge_test AS
SELECT id, name FROM employees;

CREATE ALGORITHM = TEMPTABLE VIEW v_temptable_test AS
SELECT id, name FROM employees GROUP BY department_id;

本章小结

本章学习了：

• 视图概念：虚拟表，不存储数据
• 创建视图：CREATE VIEW语法
• 查看视图：DESC、SHOW CREATE VIEW
• 修改视图：ALTER VIEW、OR REPLACE
• 可更新视图：通过视图操作数据
• 视图应用：简化查询、数据安全、格式化

下一章，我们将学习存储过程，了解如何创建和使用存储过程。