视图

一、视图基础

1.1 视图概念

1.1.1 什么是视图

视图：基于SQL语句结果集的虚拟表，不存储实际数据。

东巴文理解：

视图 = 窗户
透过窗户看外面的风景
窗户本身不存储风景
视图不存储数据，只存储查询逻辑

1.1.2 视图的优缺点

优点：

• 简化复杂查询
• 提供数据独立性
• 保护数据安全
• 重用SQL逻辑

缺点：

• 性能有损耗
• 修改受限
• 增加维护成本

1.2 创建视图

1.2.1 基本语法

语法格式：

CREATE VIEW 视图名 AS
SELECT 语句;

1.2.2 创建简单视图

示例：

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- 插入数据
INSERT INTO employees (name, dept_id, salary, hire_date) VALUES
('张三', 1, 10000, '2020-01-15'),
('李四', 1, 15000, '2019-05-20'),
('王五', 2, 8000, '2021-03-10'),
('赵六', 2, 12000, '2020-08-25');

-- 创建视图
CREATE VIEW employee_view AS
SELECT id, name, salary FROM employees;

-- 查询视图
SELECT * FROM employee_view;

1.2.3 创建复杂视图

示例：

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50)
);

INSERT INTO departments (dept_name) VALUES ('技术部'), ('销售部');

-- 创建复杂视图
CREATE VIEW employee_detail_view AS
SELECT 
    e.id,
    e.name,
    d.dept_name,
    e.salary,
    e.hire_date,
    TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS work_years
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- 查询视图
SELECT * FROM employee_detail_view;

1.3 查看视图

1.3.1 查看视图列表

-- 查看所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 查看指定数据库的视图
SHOW FULL TABLES FROM database_name WHERE Table_type = 'VIEW';

1.3.2 查看视图定义

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

-- 从information_schema查询
SELECT * FROM information_schema.VIEWS 
WHERE TABLE_NAME = 'employee_view';

二、视图查询

2.1 基本查询

2.1.1 查询视图数据

示例：

-- 查询所有数据
SELECT * FROM employee_view;

-- 条件查询
SELECT * FROM employee_view WHERE salary > 10000;

-- 排序
SELECT * FROM employee_view ORDER BY salary DESC;

-- 分组
SELECT dept_name, COUNT(*) AS count, AVG(salary) AS avg_salary
FROM employee_detail_view
GROUP BY dept_name;

2.1.2 嵌套查询

示例：

-- 在子查询中使用视图
SELECT * FROM employee_view
WHERE salary > (SELECT AVG(salary) FROM employee_view);

-- 在JOIN中使用视图
SELECT 
    e.name,
    e.salary,
    d.avg_salary
FROM employee_view e
JOIN (
    SELECT dept_name, AVG(salary) AS avg_salary
    FROM employee_detail_view
    GROUP BY dept_name
) d ON e.dept_id = d.dept_id;

2.2 视图与表的区别

三、可更新视图

3.1 可更新视图条件

3.1.1 可更新视图要求

视图可更新的条件：

• 不包含聚合函数
• 不包含DISTINCT
• 不包含GROUP BY
• 不包含HAVING
• 不包含UNION
• 不包含子查询
• 不包含JOIN（部分情况）

3.1.2 不可更新视图示例

示例：

-- 包含聚合函数，不可更新
CREATE VIEW dept_stats_view AS
SELECT 
    dept_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

-- 尝试更新（会失败）
UPDATE dept_stats_view SET avg_salary = 10000;

3.2 更新视图数据

3.2.1 INSERT操作

示例：

-- 创建可更新视图
CREATE VIEW employee_insert_view AS
SELECT id, name, dept_id, salary FROM employees;

-- 通过视图插入数据
INSERT INTO employee_insert_view (name, dept_id, salary)
VALUES ('孙七', 1, 9000);

-- 验证
SELECT * FROM employees WHERE name = '孙七';

3.2.2 UPDATE操作

示例：

-- 通过视图更新数据
UPDATE employee_insert_view
SET salary = 9500
WHERE name = '孙七';

-- 验证
SELECT * FROM employees WHERE name = '孙七';

3.2.3 DELETE操作

示例：

-- 通过视图删除数据
DELETE FROM employee_insert_view WHERE name = '孙七';

-- 验证
SELECT * FROM employees WHERE name = '孙七';

3.3 WITH CHECK OPTION

3.3.1 WITH CHECK OPTION作用

WITH CHECK OPTION：确保通过视图修改的数据仍然满足视图定义条件。

3.3.2 使用示例

示例：

-- 创建带检查选项的视图
CREATE VIEW high_salary_view AS
SELECT id, name, salary
FROM employees
WHERE salary > 10000
WITH CHECK OPTION;

-- 尝试插入不满足条件的数据（会失败）
INSERT INTO high_salary_view (name, salary) VALUES ('测试', 5000);

-- 尝试更新为不满足条件的数据（会失败）
UPDATE high_salary_view SET salary = 5000 WHERE id = 1;

四、视图管理

4.1 修改视图

4.1.1 CREATE OR REPLACE VIEW

语法格式：

CREATE OR REPLACE VIEW 视图名 AS
SELECT 语句;

示例：

-- 修改视图
CREATE OR REPLACE VIEW employee_view AS
SELECT id, name, dept_id, salary, hire_date FROM employees;

-- 查询修改后的视图
SELECT * FROM employee_view;

4.1.2 ALTER VIEW

语法格式：

ALTER VIEW 视图名 AS
SELECT 语句;

示例：

-- 修改视图
ALTER VIEW employee_view AS
SELECT id, name, salary FROM employees WHERE salary > 8000;

-- 查询修改后的视图
SELECT * FROM employee_view;

4.2 删除视图

4.2.1 基本语法

语法格式：

DROP VIEW [IF EXISTS] 视图名 [, 视图名2, ...];

示例：

-- 删除单个视图
DROP VIEW IF EXISTS employee_view;

-- 删除多个视图
DROP VIEW IF EXISTS employee_view, employee_detail_view;

4.3 重命名视图

MySQL不支持直接重命名视图，需要删除后重新创建：

-- 删除旧视图
DROP VIEW IF EXISTS old_view_name;

-- 创建新视图
CREATE VIEW new_view_name AS
SELECT * FROM employees;

五、视图应用场景

5.1 简化复杂查询

5.1.1 多表连接视图

示例：

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 创建客户表
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 创建订单详情视图
CREATE VIEW order_detail_view AS
SELECT 
    o.id AS order_id,
    c.name AS customer_name,
    c.email AS customer_email,
    o.order_date,
    o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- 使用视图简化查询
SELECT * FROM order_detail_view WHERE customer_name = '张三';

5.2 数据安全

5.2.1 列级安全

示例：

-- 创建不包含敏感列的视图
CREATE VIEW employee_public_view AS
SELECT id, name, dept_id FROM employees;

-- 授权用户访问视图而非表
GRANT SELECT ON employee_public_view TO 'user1'@'localhost';

5.2.2 行级安全

示例：

-- 创建只包含特定部门数据的视图
CREATE VIEW tech_dept_view AS
SELECT * FROM employees WHERE dept_id = 1;

-- 授权技术部用户访问
GRANT SELECT ON tech_dept_view TO 'tech_user'@'localhost';

5.3 数据抽象

5.3.1 计算字段视图

示例：

-- 创建包含计算字段的视图
CREATE VIEW employee_salary_view AS
SELECT 
    id,
    name,
    salary,
    salary * 12 AS annual_salary,
    salary * 0.1 AS tax,
    salary * 0.9 AS net_salary
FROM employees;

-- 查询视图
SELECT * FROM employee_salary_view;

5.3.2 格式化视图

示例：

-- 创建格式化视图
CREATE VIEW employee_format_view AS
SELECT 
    id,
    CONCAT(name, ' (', dept_id, ')') AS employee_info,
    CONCAT('¥', FORMAT(salary, 2)) AS salary_format,
    DATE_FORMAT(hire_date, '%Y年%m月%d日') AS hire_date_format
FROM employees;

-- 查询视图
SELECT * FROM employee_format_view;

六、视图性能优化

6.1 视图性能问题

6.1.1 性能损耗

问题：

• 每次查询视图都要执行底层SQL
• 复杂视图可能导致性能问题
• 嵌套视图性能更差

6.1.2 性能分析

示例：

-- 使用EXPLAIN分析视图查询
EXPLAIN SELECT * FROM employee_detail_view WHERE salary > 10000;

-- 查看执行计划
SHOW PROFILE;

6.2 优化建议

6.2.1 避免嵌套视图

不推荐：

-- 嵌套视图
CREATE VIEW view1 AS SELECT * FROM employees;
CREATE VIEW view2 AS SELECT * FROM view1;
CREATE VIEW view3 AS SELECT * FROM view2;

推荐：

-- 直接创建视图
CREATE VIEW view3 AS SELECT * FROM employees;

6.2.2 使用索引

示例：

-- 为视图底层表创建索引
CREATE INDEX idx_dept_id ON employees(dept_id);
CREATE INDEX idx_salary ON employees(salary);

-- 查询视图时可以使用索引
SELECT * FROM employee_view WHERE dept_id = 1;

6.2.3 限制返回列

不推荐：

-- 返回所有列
CREATE VIEW all_columns_view AS
SELECT * FROM employees;

推荐：

-- 只返回需要的列
CREATE VIEW limited_columns_view AS
SELECT id, name, salary FROM employees;

七、实战案例

7.1 销售报表视图

-- 创建销售表
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    sale_date DATE
);

-- 创建产品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    category VARCHAR(50)
);

-- 创建销售报表视图
CREATE VIEW sales_report_view AS
SELECT 
    s.id AS sale_id,
    p.name AS product_name,
    p.category,
    s.quantity,
    s.price,
    s.quantity * s.price AS total_amount,
    s.sale_date,
    YEAR(s.sale_date) AS sale_year,
    MONTH(s.sale_date) AS sale_month
FROM sales s
JOIN products p ON s.product_id = p.id;

-- 查询月度销售报表
SELECT 
    sale_year,
    sale_month,
    category,
    COUNT(*) AS sale_count,
    SUM(total_amount) AS total_sales
FROM sales_report_view
GROUP BY sale_year, sale_month, category
ORDER BY sale_year DESC, sale_month DESC;

7.2 用户活跃度视图

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建登录日志表
CREATE TABLE login_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建用户活跃度视图
CREATE VIEW user_activity_view AS
SELECT 
    u.id,
    u.name,
    u.created_at,
    COUNT(l.id) AS login_count,
    MAX(l.login_time) AS last_login_time,
    DATEDIFF(NOW(), MAX(l.login_time)) AS days_since_last_login,
    CASE 
        WHEN COUNT(l.id) = 0 THEN '未激活'
        WHEN DATEDIFF(NOW(), MAX(l.login_time)) <= 7 THEN '活跃'
        WHEN DATEDIFF(NOW(), MAX(l.login_time)) <= 30 THEN '一般'
        ELSE '不活跃'
    END AS activity_level
FROM users u
LEFT JOIN login_logs l ON u.id = l.user_id
GROUP BY u.id, u.name, u.created_at;

-- 查询活跃用户
SELECT * FROM user_activity_view WHERE activity_level = '活跃';

7.3 库存预警视图

-- 创建库存表
CREATE TABLE inventory (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    stock INT,
    warning_stock INT DEFAULT 10,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建库存预警视图
CREATE VIEW inventory_warning_view AS
SELECT 
    i.id,
    p.name AS product_name,
    i.stock,
    i.warning_stock,
    CASE 
        WHEN i.stock = 0 THEN '缺货'
        WHEN i.stock < i.warning_stock THEN '低库存'
        ELSE '正常'
    END AS stock_status,
    i.updated_at
FROM inventory i
JOIN products p ON i.product_id = p.id;

-- 查询需要补货的商品
SELECT * FROM inventory_warning_view 
WHERE stock_status IN ('缺货', '低库存')
ORDER BY stock ASC;

八、本章小结

8.1 核心要点

✅ 视图是虚拟表，不存储实际数据 ✅ 视图可以简化复杂查询 ✅ 可更新视图有严格条件限制 ✅ WITH CHECK OPTION确保数据完整性 ✅ 视图可以提供数据安全保护 ✅ 注意视图性能优化

8.2 验证清单

完成本章学习后，请确认您能够：

• 创建简单和复杂视图
• 查询视图数据
• 更新可更新视图
• 使用WITH CHECK OPTION
• 管理视图（修改、删除）
• 应用视图解决实际问题