信息时代，数据是驱动业务决策和创新的核心资源。数据库技术的不断发展使得我们能够存储、管理和检索大量的数据，为企业和组织提供了强大的支持。然而，随着数据量的不断增加，有效地处理和整合数据的过程变得愈发复杂。
这时，多表连接视图悄然走进数据库世界，为我们带来了便利，它不仅能够将多个表中的数据整合在一起，同时还以其可更新的特性，为数据管理带来了全新的可能性。
本文将带您深入探索 KingbaseES 数据库中的多表连接视图及其可更新性。从基本概念到实际应用，我们将解密这项高效技术，揭示其背后的原理和优势，帮助您更好地运用多表连接视图来优化数据库操作，释放数据魔法的潜力！
关于多表连接视图
多表连接视图是指构建视图的顶层查询引用了多个数据表，通过支持多表连接视图的更新操作，可以实现基于连接视图进行数据表的更新操作（DML），包括插入（INSERT）、更新（UPDATE）和删除（DELETE）。
理解多表连接与关系
视图(View)：视图是一种虚拟的表，是从一个或多个基本表中派生的，可以像表一样进行查询和更新操作。对于可更新视图，用户可以通过视图来更新基本表中的数据。
1. 单表视图，是指视图的查询只涉及单个数据表，如下例子中视图 v1 的查询中只包含表 t1。
create table t1 (col1 int, col2 int);
create view v1 as select * from t1;
复制代码
2. 多表视图，是指视图数据映射到多个基表。如下例子中视图 v1 是由表 t1 和 t2 组成，视图 v1 的数据与 t1 和 t2 中数据都存在映射关系。
create table t1 (c11 int primary key, c12 int);
create table t2 (c21 int primary key, c22 int);
create view v1 as select * from t1 join t2 on (c11 = c21) where c22 > 0;
复制代码
键保留表（Key-Preserved Table）：如果一个表的主键，是连接结果集的键，则称该表是键值保护的，也可以简单理解为存在一个单向的映射，视图中的每一行数据能唯一映射到基表中的一行数据。键保留表是理解多表连接视图是否可更新的基础，当多表连接视图的基表中存在一个或多个键保留表时，则该视图为可更新视图。
目标基表（target base table）：对于多表连接视图进行更新操作时，会转换为对其中某一个基表的更新操作，实际更新的表称为目标基表。
目标基表与键保留表的关系：目标基表必须为键保留表。
多表连接视图的幕后机制
1、视图的更新操作
用户可以通多对视图进行更新操作实现对表数据的更新，可以减少编程的工作量，同时隐藏数据表的结构，实现数据对用户间不同的可见性，保证数据安全。对视图执行更新操作，最终会将更新操作(Insert/Update/Delete)重定向到某一个基表，从而实现对于基表的数据更新操作，视图中可能存在多个可以更新的基表（键保留表），每一条 DML 语句只能作用于一个数据表，本文后续内容主要讨论多表连接视图的更新问题。
2、视图的可更新性判断
前文已经讲到对于多表连接视图，当存在一个或多个键保留表时，我们认为这个视图中的键保留表是可以更新的。
如何找到视图中的键保留表？
简单的等值连接的两表 JOIN 会存在以下几种情况：
1）两个表的连接列都不是主键：t1 和 t2 组成视图 v1 且都不存在主键，则对于视图而言无键保留表。
create table t1 (col_a int, col_b char(10));
insert into t1 values (1,'aaa');
insert into t1 values (1,'aaa');
create table t2 (col_c int, col_d char(10));
insert into t2 values (1,'xxx');
insert into t2 values (1,'yyy');
create or replace view v1 as select * from t1 join t2 on (col_a = col_c);
select * from v1;
col_a | col_b | col_c | col_d
-------+------------+-------+------------
1 | aaa | 1 | xxx
1 | aaa | 1 | yyy
1 | aaa | 1 | xxx
1 | aaa | 1 | yyy
(4 rows)
复制代码
2）两个表的连接列都是主键列：
（1）Join 类型是全外连接时不存在键保留表。
（2）若为左连接，左侧表可能是键保留表，右侧表不是键保留表。如下例子：
create table t1 (col_a int primary key, col_b char(10));
insert into t1 values (1,'aaa');
insert into t1 values (2,'bbb');
insert into t1 values (3,'ccc');
insert into t1 values (4,'ddd');
create table t2 (col_c int primary key, col_d char(10));
insert into t2 values (1,'xxx');
insert into t2 values (2,'yyy');
create or replace view v1 as select * from t1 left join t2 on (col_a = col_c);
select * from v1;
select * from v1;
col_a | col_b | col_c | col_d
-------+------------+-------+------------
1 | aaa | 1 | xxx
2 | bbb | 2 | yyy
3 | ccc | |
4 | ddd | |
(4 rows)
复制代码
（3）若为右连接，同理，只是结论完全相反。
（4）若为内连接，视图的每一行能映射到与两个基表中的唯一一行，所以两个表都为键保留表。
create table t1 (col_a int primary key, col_b char(10));
insert into t1 values (1,'aaa');
insert into t1 values (2,'bbb');
insert into t1 values (3,'ccc');
create table t2 (col_c int primary key, col_d char(10));
insert into t2 values (1,'xxx');
insert into t2 values (2,'yyy');
insert into t2 values (3,'zzz');
create or replace view v1 as select * from t1 join t2 on (col_a = col_c);
select * from v1;
col_a | col_b | col_c | col_d
-------+------------+-------+------------
1 | aaa | 1 | xxx
2 | bbb | 2 | yyy
3 | ccc | 3 | zzz
(3 rows)
复制代码
3）两个表的连接列只有一个是主键列:
（1）Join 类型是全外连接时不存在键保留表。
（2）Join 类型如果为左连接，表 T1 的连接列不是主键，T2 的连接列为主键，如图中组成的视图 v1 在 T1 中是可以找到唯一映射行的，满足键保留表的关系，所以 T1 为键保留表。同理右连接相反。
create table t1 (col_a int, col_b char(10));
insert into t1 values (1,'aaa');
insert into t1 values (2,'bbb');
insert into t1 values (3,'ccc');
insert into t1 values (3,'ddd');
create table t2 (col_c int primary key, col_d char(10));
insert into t2 values (1,'xxx');
insert into t2 values (2,'yyy');
insert into t2 values (3,'zzz');
create or replace view v1 as select * from t1 left join t2 on (col_a = col_c);
select * from v1;
col_a | col_b | col_c | col_d
-------+------------+-------+------------
1 | aaa | 1 | xxx
2 | bbb | 2 | yyy
3 | ccc | 3 | zzz
3 | ddd | 3 | zzz
(4 rows)
复制代码
如图：
外连接与可更新视图
前文讲到了，多表连接视图的可更新性和其查询中的连接方式有很大关系，因此为了更加准确的判断一个多表连接视图是否支持更新，需要对定义视图的查询进行必要的逻辑等价变换，在消除外连接后判断视图的可更新性。
如何判断视图列是否可以更新？
3、目标基表的选择
当用户对一个视图进行更新（Insert/Update/Delete）时，我们如何进行能知道用户想要操作的真正目标基表呢？
对于单表视图来说只有一个表，所以目标基表是明确的。
对于多表视图而言此过程需要根据用户的 DML 语句的输入来识别目标基表。
根据 DML 的目标列确认目标基表，目标列不能涉及多个表。
选择第一个键保留表作为目标基表。
4、视图重写过程
视图的更新实质上是对于基表的更新，在重写视图过程中涉及到哪些部分需要重写呢？
关于视图 with check option 的处理
对于单表视图的 with check option，可以合并到 WHERE 条件中；而对于多表视图，非目标基表的条件对更新操作而言并无作用，所以只需要提取目标基表的条件即可。
多表连接视图的实用示例与优势
在以下示例中视图 emp_dept 由表 emp 和 dep 组成，列 deptno 为表 dep 的主键，所以且 emp 为键保留表，此视图为可更新的视图。
--测试表
create table dep (
deptno INT not null,
dname VARCHAR(100) not null,
loc VARCHAR(100) not null,
Primary key (deptno)
);
insert into dep values(10, 'Accounting', 'New York');
insert into dep values(20, 'Research', 'Dallas');
insert into dep values(30, 'Sailing', 'Boston');
CREATE TABLE emp (
empno number(4) PRIMARY KEY,
ename varchar2(10) not null,
job varchar2(10),
mgr number(4),
sal number(7,2),
comm number(7,2),
deptno int,
FOREIGN KEY (deptno) REFERENCES dep(deptno)
);
INSERT INTO emp (empno, ename, deptno) VALUES (7787, 'ford', 20);
INSERT INTO emp (empno, ename, deptno) VALUES (7788, 'wade', 30);
INSERT INTO emp (empno, ename, deptno) VALUES (7789, 'jones', 30);
INSERT INTO emp (empno, ename, deptno) VALUES (7790, 'jamie', 30);
CREATE OR REPLACE VIEW emp_dept AS
SELECT empno, ename, emp.deptno, sal, dname, loc
FROM emp JOIN dep
ON (emp.deptno = dep.deptno AND dep.deptno IN (10, 20, 30));
--更新视图
update emp_dept set sal = 2000 where ename = 'ford' and dname = 'Research';
select * from emp_dept;
empno | ename | deptno | sal | dname | loc
-------+-------+--------+---------+----------+--------
7788 | wade | 30 | | Sailing | Boston
7789 | jones | 30 | | Sailing | Boston
7790 | jamie | 30 | | Sailing | Boston
7787 | ford | 20 | 2000.00 | Research | Dallas
(4 rows)
--更新emp表
update emp set sal = 3000 from dep where ename = 'ford' and dep.deptno = emp.deptno and dname = 'Research';
select * from emp_dept;
empno | ename | deptno | sal | dname | loc
-------+-------+--------+---------+----------+--------
7788 | wade | 30 | | Sailing | Boston
7789 | jones | 30 | | Sailing | Boston
7790 | jamie | 30 | | Sailing | Boston
7787 | ford | 20 | 3000.00 | Research | Dallas
(4 rows)
复制代码
总结
多表连接视图支持可更新是一种非常有用的技术，可以屏蔽复杂的数据操作逻辑，使得数据管理变得更为简单和高效，不会因 DML 行为而暴露基础表，同时也限制了用户的访问范围和权限，保护数据的安全性和隐私性。因此也被广泛应用于各种数据库系统和数据管理应用中。
正如我们在本文中所探讨的，多表连接视图不仅是数据的魔法，更是我们应对数据复杂性的有力工具。作为数据库领域国家队，金仓数据库将积极探索更高效、更智能的数据管理方法，释放数据的无限力量！
评论