视图：虚拟表与权限的守门人

-- 基础创建：封装 JOIN + 过滤
CREATE VIEW v_active_orders AS
SELECT o.order_id, o.amount, o.created_at, u.user_name, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'active';

-- 像查真表一样使用（语法完全一致）
SELECT user_name, amount FROM v_active_orders
WHERE created_at > '2026-09-01'
ORDER BY amount DESC;

-- 替换定义（省去 DROP 再建的权限与依赖麻烦）
CREATE OR REPLACE VIEW v_active_orders AS
SELECT o.order_id, o.amount, u.user_name
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'active';

-- 显式指定视图列名（对内暴露友好别名）
CREATE VIEW v_monthly_sales (月份, 销售额) AS
SELECT DATE_FORMAT(created_at, '%Y-%m'), SUM(amount)
FROM orders GROUP BY 月份;

-- 一、过滤视图：单表 + WHERE，最轻量
CREATE VIEW active_users AS
SELECT id, username, email, status
FROM users
WHERE status = 1;

-- 二、关联视图：把 JOIN 固化，报表口径统一
CREATE VIEW order_details AS
SELECT
  o.id AS order_id,
  o.order_no,
  u.username,
  u.email,
  o.total_amount,
  o.status,
  o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- 三、聚合视图：LEFT JOIN + GROUP BY，统计口径一次定型
CREATE VIEW user_stats AS
SELECT
  u.id,
  u.username,
  COUNT(o.id) AS order_count,
  IFNULL(SUM(o.total_amount), 0) AS total_spent,
  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.username;

SELECT * FROM active_users WHERE username LIKE '张%';

-- 视图当表用：user_stats 再和订单表连
SELECT v.username, v.order_count, o.order_no
FROM user_stats v
LEFT JOIN orders o ON v.id = o.user_id
WHERE o.created_at > '2024-01-01';

CREATE VIEW v_active_users AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;

-- 想借视图塞进一条 status='inactive' 的数据？被拒：
-- ERROR 1369: CHECK OPTION failed 'mydb.v_active_users'

-- DEFINER（默认）：视图以"定义者"的权限访问底表
--   → 使用者只需视图的 SELECT 权限，即可经视图触达自己无权访问的底表
-- INVOKER：以"调用者"的权限执行
--   → 调用者必须对底表也有权限，否则报错

ALTER VIEW v_user_public
SQL SECURITY INVOKER
AS SELECT id, name, masked_phone FROM users;

SHOW CREATE VIEW v_active_orders\G                       -- 查看定义
SELECT * FROM information_schema.VIEWS
WHERE table_name = 'v_active_orders';                    -- 元数据（含 check_option）

ALTER VIEW v_active_orders AS SELECT ...;                -- 修改定义
DROP VIEW [IF EXISTS] v_active_orders;                   -- 删除（不影响底表）

-- 限制一：定义里不能含 FROM 子查询（MySQL 特有，PostgreSQL 无此限制）
CREATE VIEW v_recent AS
SELECT * FROM (SELECT * FROM users WHERE status = 1) AS t;
-- ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
-- 变通：把子查询拆成另一层视图，或把过滤逻辑交给查询方

-- 限制二：视图不存数据，定义多复杂每次查询就重算多贵
CREATE VIEW v_user_stats AS
SELECT u.id, u.username, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;
SELECT * FROM v_user_stats;   -- 每次都是完整的两表 JOIN + 聚合

-- 限制三：外层 ORDER BY 会覆盖视图内部的 ORDER BY，视图不保证输出顺序
CREATE VIEW v_sorted AS
SELECT * FROM users ORDER BY created_at;
SELECT * FROM v_sorted;       -- 语义上无序，别依赖它

-- 限制四：视图不能带参数。要"按条件变化的视图"只能查询时自己加 WHERE，
-- 或改用存储过程（见 mysql/770-StoredProcedureAndFunction）

-- 1. 准备
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50), phone VARCHAR(20), salary DECIMAL(10,2), status VARCHAR(10)
);
INSERT INTO users VALUES
  (1,'张三','13800000001',12000,'active'),
  (2,'李四','13800000002',9000,'active'),
  (3,'王五','13800000003',7000,'inactive');

-- 2. 脱敏视图：隐藏薪资、掩码手机号
CREATE VIEW v_user_public AS
SELECT id, name, CONCAT(LEFT(phone,3),'****',RIGHT(phone,4)) AS phone
FROM users WHERE status = 'active';

SELECT * FROM v_user_public;    -- 张三/李四，薪资不可见

-- 3. CHECK OPTION 守卫
CREATE OR REPLACE VIEW v_user_public AS
SELECT id, name, status FROM users WHERE status = 'active'
WITH CHECK OPTION;
INSERT INTO v_user_public (id, name, status) VALUES (4, '赵六', 'inactive');
-- ERROR 1369 —— 守卫生效

-- 4. 实时性验证：底表改动立刻反映到视图
UPDATE users SET salary = 20000 WHERE id = 1;
SELECT * FROM v_user_public;    -- 视图无缓存，一切即时