视图与存储过程

这一课你将能做到

1. 创建、查询、替换、删除视图，并说清哪些视图可更新、哪些不行（含聚合的视图不可 DML）
2. 编写一个带参数校验、含事务的存储过程并用 CALL 执行
3. 用条件聚合（CASE + SUM）在 MySQL 里实现行转列报表

为什么要学

「用户 AC 统计」这条 SQL 你在前面几课已经写了四遍。报表类查询的特点是反复出现、略有变化、复杂易错——视图就是给这类查询起名字；而「给达标的用户批量升级」这种多步写入，封装成存储过程后一条 CALL 搞定，还天然在数据库侧保证原子性。更重要的是，面试常问「视图和表的区别」「什么场景用存储过程」，答不好会显得没碰过真实系统。

人话：视图是一段「保存下来的 SELECT」，每次查询它都实时重算；存储过程是「存在数据库里的函数」，适合定时任务和批量修正。视图不存数据、过程不返回结果集才是常态——理解这两点就不会用歪。

先建立图像

         ┌─────────────── 虚拟表（视图）───────────────┐
         │  v_user_ac_stat                            │
         │  username │ total_sub │ ac_cnt │ ac_rate   │
         └────────────────────┬───────────────────────┘
                              │ 每次查询时实时展开执行
              ┌───────────────┴───────────────┐
              ▼                               ▼
         cf_user（基表）                 cf_submission（基表）
         真实存储数据                    真实存储数据

存储过程：CALL sp_promote_users(2)
         ┌─ 参数校验 ─► 开事务 ─► UPDATE ─► 提交 ─┐
         └─ 整个过程在数据库侧一次完成 ──────────┘

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT，恢复初始数据

步骤 1：引入课程目录表（本课新角色）

CREATE TABLE IF NOT EXISTS cf_track (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug     VARCHAR(80)  NOT NULL,
  title    VARCHAR(200) NOT NULL,
  category VARCHAR(40)  NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_slug (slug)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cf_lesson (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  track_id    INT UNSIGNED NOT NULL,
  slug        VARCHAR(80)  NOT NULL,
  title       VARCHAR(200) NOT NULL,
  order_index INT NOT NULL,
  duration    INT NOT NULL DEFAULT 20 COMMENT '时长(分钟)',
  PRIMARY KEY (id),
  UNIQUE KEY uk_track_slug (track_id, slug),
  CONSTRAINT fk_lesson_track FOREIGN KEY (track_id) REFERENCES cf_track (id)
) ENGINE=InnoDB;

INSERT INTO cf_track (id, slug, title, category) VALUES
  (1, 'sql-advanced-essentials', 'SQL 进阶必备', 'database');

INSERT INTO cf_lesson (id, track_id, slug, title, order_index, duration) VALUES
  (1, 1, 'sqa-intro',       'SQL 进阶地图',      1, 25),
  (2, 1, 'sqa-ddl',         'DDL 与表结构',      2, 30),
  (3, 1, 'sqa-dml',         'DML 操作',          3, 25),
  (4, 1, 'sqa-query',       '查询与聚合',        4, 30),
  (5, 1, 'sqa-functions',   '内置函数',          5, 25),
  (6, 1, 'sqa-subqueries',  '子查询',            6, 30),
  (7, 1, 'sqa-window',      '窗口函数与 CTE',    7, 40),
  (8, 1, 'sqa-views',       '视图与存储过程',    8, 30),
  (9, 1, 'sqa-constraints', '约束与完整性',      9, 30),
  (10,1, 'sqa-transactions','事务与安全',       10, 35),
  (11,1, 'sqa-performance', '性能优化',         11, 40),
  (12,1, 'sqa-practice',    '综合练习',         12, 45);