MariaDB视图 #

视图概念
├── 虚拟表
│   └── 不存储数据，存储查询定义
├── 简化查询
│   └── 封装复杂查询
├── 安全性
│   └── 限制数据访问
└── 独立性
    └── 屏蔽表结构变化

视图特点
├── 不占用存储空间（只存储定义）
├── 动态生成数据
├── 可以像表一样查询
└── 可以嵌套使用

CREATE VIEW view_name AS
SELECT statement;

-- 创建简单视图
CREATE VIEW user_emails AS
SELECT id, name, email FROM users;

-- 使用视图
SELECT * FROM user_emails;

-- 创建复杂视图
CREATE VIEW user_order_summary AS
SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 使用视图
SELECT * FROM user_order_summary WHERE order_count > 0;

-- 创建时指定列名
CREATE VIEW user_info (user_id, user_name, user_email) AS
SELECT id, name, email FROM users;

SELECT user_id, user_name FROM user_info;

-- WITH CHECK OPTION：确保通过视图修改的数据仍然满足视图条件
CREATE VIEW active_users AS
SELECT id, name, email, status FROM users WHERE status = 1
WITH CHECK OPTION;

-- 插入数据（status必须为1）
INSERT INTO active_users (name, email, status) VALUES ('Test', 'test@example.com', 1);  -- OK
INSERT INTO active_users (name, email, status) VALUES ('Test2', 'test2@example.com', 0);  -- 错误

-- 更新数据（不能将status改为0）
UPDATE active_users SET status = 0 WHERE id = 1;  -- 错误

-- OR REPLACE：如果视图存在则替换
CREATE OR REPLACE VIEW user_emails AS
SELECT id, name, email, status FROM users;

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

-- 结果
+-------------+-----------------------------------------------------------------------------------+
| View        | Create View                                                                       |
+-------------+-----------------------------------------------------------------------------------+
| user_emails | CREATE VIEW `user_emails` AS select `users`.`id`,`users`.`name`,`users`.`email`  |
|             | from `users`                                                                      |
+-------------+-----------------------------------------------------------------------------------+

-- 从information_schema查询
SELECT 
    table_name,
    view_definition,
    check_option,
    is_updatable
FROM information_schema.views
WHERE table_schema = 'mydb';

-- 查看所有视图
SELECT table_name FROM information_schema.views WHERE table_schema = DATABASE();

-- 查看视图结构
DESC user_emails;

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   |     | 0       |       |
| name  | varchar(50)  | YES  |     | NULL    |       |
| email | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

-- 像表一样查询
SELECT * FROM user_emails WHERE name LIKE 'J%';

-- 使用WHERE条件
SELECT * FROM user_order_summary WHERE total_amount > 1000;

-- 使用ORDER BY和LIMIT
SELECT * FROM user_order_summary ORDER BY total_amount DESC LIMIT 5;

-- 使用聚合函数
SELECT AVG(total_amount) FROM user_order_summary;

-- 视图与其他表连接
SELECT 
    uos.name,
    uos.total_amount,
    d.name AS department
FROM user_order_summary uos
LEFT JOIN departments d ON uos.department_id = d.id;

-- 视图与视图连接
SELECT 
    u1.name,
    u2.order_count
FROM user_emails u1
JOIN user_order_summary u2 ON u1.id = u2.id;

-- 基于视图创建视图
CREATE VIEW high_value_customers AS
SELECT * FROM user_order_summary WHERE total_amount > 1000;

-- 使用嵌套视图
SELECT * FROM high_value_customers WHERE order_count > 5;

可更新视图条件
├── 不包含聚合函数
│   └── SUM, COUNT, AVG, MAX, MIN等
├── 不包含DISTINCT
├── 不包含GROUP BY
├── 不包含HAVING
├── 不包含UNION
├── 不包含子查询（某些情况）
└── 只引用一个表（某些情况）

-- 创建简单视图
CREATE VIEW user_basic AS
SELECT id, name, email FROM users;

-- 通过视图插入
INSERT INTO user_basic (name, email) VALUES ('NewUser', 'new@example.com');

-- 验证
SELECT * FROM users WHERE name = 'NewUser';

-- 通过视图更新
UPDATE user_basic SET email = 'updated@example.com' WHERE id = 1;

-- 验证
SELECT * FROM users WHERE id = 1;

-- 通过视图删除
DELETE FROM user_basic WHERE id = 1;

-- 验证
SELECT * FROM users WHERE id = 1;

-- MariaDB支持多表视图更新（有条件）
CREATE VIEW user_department AS
SELECT u.id, u.name, u.department_id, d.name AS department_name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;

-- 可以更新users表的列
UPDATE user_department SET name = 'Updated' WHERE id = 1;

-- 不能更新departments表的列（需要触发器或存储过程）

-- 替换视图定义
CREATE OR REPLACE VIEW user_emails AS
SELECT id, name, email, status, created_at FROM users;

-- 修改视图
ALTER VIEW user_emails AS
SELECT id, name, email FROM users WHERE status = 1;

-- 删除视图
DROP VIEW user_emails;

-- 如果存在则删除
DROP VIEW IF EXISTS user_emails;

-- 删除多个视图
DROP VIEW view1, view2, view3;

-- 复杂报表查询
CREATE VIEW monthly_sales_report AS
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

-- 使用视图
SELECT * FROM monthly_sales_report WHERE month = '2024-01';

-- 只暴露部分列
CREATE VIEW user_public_info AS
SELECT id, name FROM users;

-- 只暴露部分行
CREATE VIEW active_orders AS
SELECT * FROM orders WHERE status = 'active';

-- 授予视图权限
GRANT SELECT ON mydb.user_public_info TO 'readonly'@'localhost';

-- 格式化数据
CREATE VIEW user_display AS
SELECT 
    id,
    name,
    UPPER(name) AS name_upper,
    LOWER(email) AS email_lower,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS created_date,
    CASE status 
        WHEN 1 THEN 'Active' 
        ELSE 'Inactive' 
    END AS status_text
FROM users;

-- 表结构变化后，通过视图保持兼容
-- 原表: users (id, name, email)
-- 新表: users (id, first_name, last_name, email)

CREATE VIEW users_compat AS
SELECT 
    id,
    CONCAT(first_name, ' ', last_name) AS name,
    email
FROM users;

-- 视图不存储数据，每次查询都执行定义
-- 简单视图：性能影响小
-- 复杂视图：可能有性能问题

-- 查看执行计划
EXPLAIN SELECT * FROM user_order_summary WHERE id = 1;

-- 1. 使用索引
-- 基表的索引会自动用于视图查询

-- 2. 避免复杂嵌套
-- 减少视图嵌套层数

-- 3. 使用物化视图（MariaDB不支持原生物化视图）
-- 可以使用表替代

-- 4. 考虑使用存储过程替代复杂视图

-- 视图中可以使用用户变量
SET @min_amount = 1000;

CREATE VIEW high_value_orders AS
SELECT * FROM orders WHERE amount > @min_amount;

-- 注意：变量值在创建时确定

-- 视图中使用函数
CREATE VIEW recent_orders AS
SELECT * FROM orders 
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- MERGE：合并视图定义到查询
CREATE ALGORITHM = MERGE VIEW user_emails AS
SELECT id, name, email FROM users;

-- TEMPTABLE：创建临时表
CREATE ALGORITHM = TEMPTABLE VIEW user_summary AS
SELECT user_id, COUNT(*) AS count FROM orders GROUP BY user_id;

-- UNDEFINED：由优化器选择（默认）
CREATE ALGORITHM = UNDEFINED VIEW user_info AS
SELECT id, name FROM users;

-- 推荐命名
CREATE VIEW v_user_emails AS ...;      -- 前缀v_
CREATE VIEW view_user_emails AS ...;   -- 前缀view_
CREATE VIEW user_emails_view AS ...;   -- 后缀_view

-- 描述性命名
CREATE VIEW active_user_orders AS ...;
CREATE VIEW monthly_sales_summary AS ...;