数据库原理与选型 / 系统课时
创建与使用视图
创建与使用视图
本课解决复杂查询重复书写、字段暴露过多和业务口径难以统一的问题。完成后,你可以用视图封装查询逻辑,并理解 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 触发器,把对视图的写操作转换为对底层表的操作。
按自己的节奏,确认真正理解。