# 视图语法
CREATE VIEW view_name AS select_statement;

# 删除视图
DROP VIEW [IF EXISTS] view_name;

# 查看视图
SHOW CREATE VIEW view_name;

# 创建视图示例

1. 创建简单视图
CREATE VIEW user_order_view AS
SELECT
u.id AS user_id,
u.user_name,
u.email,
o.id AS order_id,
o.order_no,
o.amount,
o.status,
o.created_at AS order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

输出示例：
Query OK, 0 rows affected (0.02 sec)

2. 查询视图
SELECT * FROM user_order_view WHERE user_id = 1 LIMIT 5;

输出示例：
+———+———–+——————+———-+—————-+———+——–+———————+
| user_id | user_name | email | order_id | order_no | amount | status | order_time |
+———+———–+——————+———-+—————-+———+——–+———————+
| 1 | user001 | user001@test.com | 1 | ORD202604040001| 100.00 | 0 | 2026-04-04 10:00:00 |
| 1 | user001 | user001@test.com | 2 | ORD202604040002| 200.00 | 1 | 2026-04-04 10:05:00 |
+———+———–+——————+———-+—————-+———+——–+———————+
2 rows in set (0.00 sec)

3. 创建带过滤条件的视图
CREATE VIEW active_user_view AS
SELECT
id,
user_name,
email,
created_at
FROM users
WHERE status = 1;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 查询过滤视图
SELECT * FROM active_user_view LIMIT 3;

输出示例：
+—-+———–+——————-+———————+
| id | user_name | email | created_at |
+—-+———–+——————-+———————+
| 1 | user001 | user001@test.com | 2026-04-01 10:00:00 |
| 2 | user002 | user002@test.com | 2026-04-01 10:01:00 |
| 3 | user003 | user003@test.com | 2026-04-01 10:02:00 |
+—-+———–+——————-+———————+
3 rows in set (0.00 sec)

5. 创建聚合视图
CREATE VIEW user_statistics_view AS
SELECT
u.id AS user_id,
u.user_name,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount,
MAX(o.created_at) AS last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.user_name;

输出示例：
Query OK, 0 rows affected (0.01 sec)

6. 查询聚合视图
SELECT * FROM user_statistics_view WHERE order_count > 10 LIMIT 3;

输出示例：
+———+———–+————-+————–+———————+
| user_id | user_name | order_count | total_amount | last_order_time |
+———+———–+————-+————–+———————+
| 1 | user001 | 50 | 5000.00 | 2026-04-04 10:00:00 |
| 2 | user002 | 30 | 3000.00 | 2026-04-04 09:55:00 |
+———+———–+————-+————–+———————+
2 rows in set (0.00 sec)

# 视图管理操作

1. 查看所有视图
SHOW FULL TABLES WHERE TABLE_TYPE = ‘VIEW’;

输出示例：
+————————-+————+
| Tables_in_test | Table_type |
+————————-+————+
| user_order_view | VIEW |
| active_user_view | VIEW |
| user_statistics_view | VIEW |
+————————-+————+

2. 查看视图定义
SHOW CREATE VIEW user_order_view\G

输出示例：
*************************** 1. row ***************************
View: user_order_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_order_view` AS select `u`.`id` AS `user_id`,`u`.`user_name` AS `user_name`,`u`.`email` AS `email`,`o`.`id` AS `order_id`,`o`.`order_no` AS `order_no`,`o`.`amount` AS `amount`,`o`.`status` AS `status`,`o`.`created_at` AS `order_time` from (`users` `u` left join `orders` `o` on((`u`.`id` = `o`.`user_id`)))
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci

3. 修改视图
CREATE OR REPLACE VIEW user_order_view AS
SELECT
u.id AS user_id,
u.user_name,
u.email,
o.id AS order_id,
o.order_no,
o.amount,
CASE o.status
WHEN 0 THEN ‘待支付’
WHEN 1 THEN ‘已支付’
WHEN 2 THEN ‘已发货’
WHEN 3 THEN ‘已完成’
WHEN 4 THEN ‘已取消’
ELSE ‘未知’
END AS status_text,
o.created_at AS order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 删除视图
DROP VIEW IF EXISTS active_user_view;

输出示例：
Query OK, 0 rows affected (0.00 sec)

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

输出示例：
+———————-+——————————————————————-+
| TABLE_NAME | VIEW_DEFINITION |
+———————-+——————————————————————-+
| user_order_view | select `u`.`id` AS `user_id`… |
| user_statistics_view | select `u`.`id` AS `user_id`… |
+———————-+——————————————————————-+

# 视图优势

1. 简化复杂查询
– 将复杂查询封装为视图
– 简化应用层代码
– 提高查询可读性

2. 数据安全
– 限制用户访问特定列
– 隐藏敏感数据
– 实现行级安全

3. 逻辑独立
– 表结构变化不影响应用
– 视图可以重新定义
– 保持应用层稳定

4. 性能优化
– 可以创建视图索引
– 优化常用查询
– 减少重复代码

# 视图使用最佳实践

1. 设计原则
– 视图名称清晰明确
– 列名具有描述性
– 保持视图逻辑简单
– 避免过度嵌套

2. 性能考虑
– 避免在视图中使用子查询
– 避免视图嵌套过深
– 考虑物化视图
– 定期检查性能

3. 安全考虑
– 使用视图限制数据访问
– 避免在视图中暴露敏感数据
– 合理设置视图权限
– 审计视图使用

4. 维护建议
– 定期检查视图定义
– 更新相关文档
– 监控视图性能
– 及时清理无用视图