数据库原理与选型 / 系统课时

创建与使用视图

创建与使用视图

本课解决复杂查询重复书写、字段暴露过多和业务口径难以统一的问题。完成后,你可以用视图封装查询逻辑,并理解 SQLite 普通视图为何通常只能读取、不能直接修改。

学习目标

  • 使用 CREATE VIEW 封装查询
  • 通过视图执行筛选和联表查询
  • 理解视图的只读特性与维护边界

创建基础表

先准备部门和员工数据,后续视图将隐藏联表细节。

sql
DROP VIEW IF EXISTS employee_summary;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER NOT NULL,
    salary INTEGER NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT INTO departments VALUES
(1, '研发部'),
(2, '市场部');

INSERT INTO employees VALUES
(1, '张三', 1, 12000),
(2, '李四', 1, 15000),
(3, '赵六', 2, 10000);

预期结果

text
成功创建 2 张表并插入 5 行数据。

创建并查询视图

视图保存的是查询定义,不会单独复制一份结果数据。查询视图时,SQLite 会根据定义读取底层表。

sql
CREATE VIEW employee_summary AS
SELECT
    e.id,
    e.name,
    d.name AS department_name,
    e.salary
FROM employees AS e
JOIN departments AS d
    ON d.id = e.department_id;

SELECT
    name,
    department_name,
    salary
FROM employee_summary
WHERE salary >= 12000
ORDER BY salary DESC;

预期输出

text
name  department_name  salary
----  ---------------  ------
李四  研发部            15000
张三  研发部            12000

业务查询只需面对 employee_summary,不必重复书写连接条件。视图也可以限制对外暴露的列,但它不是完整的权限系统,访问控制仍应由应用或数据库文件权限承担。

修改与删除视图

SQLite 不支持直接用 CREATE OR REPLACE VIEW 替换普通视图。需要变更定义时,先删除再重新创建。

sql
DROP VIEW IF EXISTS employee_summary;

CREATE VIEW employee_summary AS
SELECT
    e.id,
    e.name,
    d.name AS department_name,
    e.salary,
    CASE
        WHEN e.salary >= 14000 THEN '高'
        ELSE '常规'
    END AS salary_level
FROM employees AS e
JOIN departments AS d
    ON d.id = e.department_id;

预期结果

text
employee_summary 已按新定义重新创建。

删除视图不会删除底层表数据,但依赖该视图的查询会立即失效,因此生产环境应先检查应用代码和其他视图的依赖关系。

理解只读边界

SQLite 普通视图默认不能直接执行 INSERT、UPDATE 或 DELETE。下面的语句会失败。

sql
UPDATE employee_summary
SET salary = 16000
WHERE id = 1;

预期输出

text
Error: cannot modify employee_summary because it is a view

需要通过视图写入时,可以在后续课程中使用 INSTEAD OF 触发器,把对视图的写操作转换为对底层表的操作。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建与使用视图

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通