视图与物化视图

CREATE VIEW v_student_score AS
SELECT s.studentno, s.studentname, c.coursename, m.score
FROM student  s
JOIN mark     m ON s.studentno = m.studentno
JOIN course   c ON m.courseno  = c.courseno
WHERE m.status = 'finished';

你写的：      SELECT studentname, score FROM v_student_score WHERE score >= 60
优化器看到：  SELECT studentname, score
              FROM student JOIN mark ... WHERE status = 'finished' AND score >= 60

-- 建议带 OR REPLACE（MySQL/PostgreSQL/Oracle 支持）：改定义不用先删后建
CREATE OR REPLACE VIEW v_student_score AS
SELECT s.studentno, s.studentname, c.coursename, m.score
FROM student s
JOIN mark m      ON s.studentno = m.studentno
JOIN course c    ON m.courseno  = c.courseno;

-- SQL Server 没有 OR REPLACE，等价写法是 ALTER VIEW
-- ALTER VIEW v_student_score AS SELECT ...

-- 查看视图定义
SHOW CREATE VIEW v_student_score;        -- MySQL
-- PostgreSQL: \d+ v_student_score（psql）或查询 pg_get_viewdef

DROP VIEW IF EXISTS v_student_score;

-- 可更新：单表投影
CREATE VIEW v_active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

UPDATE v_active_users SET email = 'new@ex.com' WHERE id = 1;   -- 实际改的是 users 表

-- 不可更新：含聚合，数据库直接报错
CREATE VIEW v_course_avg AS
SELECT courseno, AVG(score) AS avg_score FROM mark GROUP BY courseno;

UPDATE v_course_avg SET avg_score = 80 WHERE courseno = 'kc1001';
-- ERROR: cannot update view（聚合视图没有「一行」对应底表「一行」的映射）

CREATE VIEW v_active_users AS
SELECT id, name, email, status FROM users WHERE status = 'active'
WITH CHECK OPTION;

UPDATE v_active_users SET status = 'disabled' WHERE id = 1;
-- ERROR: 违反检查选项——写入结果若从视图中「看不见」，直接拒绝

-- 第 1 层：明细视图（单表投影）
CREATE VIEW v_student AS
SELECT studentno, studentname, sex FROM student;

-- 第 2 层：多表业务视图
CREATE VIEW v_student_score AS
SELECT s.studentno, s.studentname, m.courseno, m.score
FROM student s JOIN mark m ON s.studentno = m.studentno;

-- 第 3 层：面向场景的过滤视图
CREATE VIEW v_student_score_pass AS
SELECT * FROM v_student_score WHERE score >= 60;

-- PostgreSQL：创建后数据定格，需手动/定时刷新
CREATE MATERIALIZED VIEW mv_daily_plays AS
SELECT show_name, date_trunc('day', played_at) AS day, COUNT(*) AS cnt
FROM plays
GROUP BY show_name, date_trunc('day', played_at);

-- 刷新（阻塞读）：业务低峰期由定时任务调用
REFRESH MATERIALIZED VIEW mv_daily_plays;
-- 或并发刷新（要求视图上有唯一索引，刷新期间可读）
CREATE UNIQUE INDEX ON mv_daily_plays (show_name, day);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_plays;

CREATE TABLE mv_daily_plays LIKE 模板结构;             -- 手工按 SELECT 结果建表
-- 定时任务（事件或外部 cron）：
TRUNCATE mv_daily_plays;
INSERT INTO mv_daily_plays SELECT ... GROUP BY ...;    -- 整表重算

-- 列级：暴露视图而非底表
CREATE VIEW v_employee_public AS
SELECT emp_id, name, dept_id FROM employees;      -- 不含 salary、id_number

GRANT SELECT ON v_employee_public TO analyst_role;
-- analyst_role 对 employees 表本身无任何权限，salary 物理不可见

-- 行级：视图带 WHERE
CREATE VIEW v_sales_east AS
SELECT * FROM sales WHERE region = 'east';
GRANT SELECT ON v_sales_east TO mgr_east;

CREATE TABLE student (
    studentno   CHAR(8) PRIMARY KEY,
    studentname VARCHAR(20) NOT NULL,
    sex         CHAR(2)
);
CREATE TABLE course (
    courseno   CHAR(6) PRIMARY KEY,
    coursename VARCHAR(50) NOT NULL
);
CREATE TABLE mark (
    studentno CHAR(8),
    courseno  CHAR(6),
    score     DECIMAL(4,1),
    PRIMARY KEY (studentno, courseno),
    FOREIGN KEY (studentno) REFERENCES student(studentno),
    FOREIGN KEY (courseno)  REFERENCES course(courseno)
);

CREATE VIEW v_student_avg AS
SELECT s.studentno, s.studentname, AVG(m.score) AS avg_score
FROM student s JOIN mark m ON s.studentno = m.studentno
GROUP BY s.studentno, s.studentname;

CREATE VIEW v_honor_students AS
SELECT * FROM v_student_avg WHERE avg_score >= 85;

-- 等价单条 SQL（先自己写，再看这里）
SELECT s.studentno, s.studentname, AVG(m.score) AS avg_score
FROM student s JOIN mark m ON s.studentno = m.studentno
GROUP BY s.studentno, s.studentname
HAVING AVG(m.score) >= 85;

CREATE TABLE teacher (
    teacher_id VARCHAR(20) PRIMARY KEY,
    courseno   CHAR(6)
);

CREATE VIEW v_teacher_mark AS
SELECT m.studentno, m.courseno, m.score
FROM mark m
JOIN teacher t ON t.courseno = m.courseno
WHERE t.teacher_id = SUBSTRING_INDEX(CURRENT_USER(), '@', 1);   -- MySQL 示例

GRANT SELECT ON v_teacher_mark TO teacher_role;
-- teacher_role 未被授予 mark 表权限，只能通过视图看到自己的课程