当前位置：首页 > article >正文

数据库中存储树状关系的数据

article 2024/10/21 20:27:27

三张表的文字描述

表1：包含字段A1,字段A2,字段A3.

字段A1是主键

表2：包含字段B1，字段B2，字段A1

字段B1是主键，字段A1是其外键。

1个字段A1的值可以匹配多条表2的记录.

表3：包含字段C1，字段C2，字段B1

字段C1是主键，字段B1是其外键.

1个字段B1的值可以匹配多条表3的记录.

创建三张表的sql语句

-- 创建第一个表
CREATE TABLE Table1 (
    A1 INTEGER PRIMARY KEY,
    A2 TEXT,
    A3 TEXT
);

-- 创建第二个表
CREATE TABLE Table2 (
    B1 INTEGER PRIMARY KEY,
    B2 TEXT,
    A1 INTEGER,
    FOREIGN KEY (A1) REFERENCES Table1(A1)
);

-- 创建第三个表
CREATE TABLE Table3 (
    C1 INT PRIMARY KEY,
    C2 VARCHAR(255), -- 假设C2是字符串类型，长度为255字符
    B1 INT,
    FOREIGN KEY (B1) REFERENCES Table2(B1) -- 假设Table2中B1是主键或者有唯一约束
);

向三张表中插入数据

-- 插入 Table1 的数据
INSERT INTO Table1 (A1, A2, A3) VALUES (6, 'ValueA2_6', 'ValueA3_6');
INSERT INTO Table1 (A1, A2, A3) VALUES (7, 'ValueA2_7', 'ValueA3_7');
INSERT INTO Table1 (A1, A2, A3) VALUES (8, 'ValueA2_8', 'ValueA3_8');

-- 插入 Table2 的数据
-- 假设 A1 的值 6, 7, 8 在 Table1 中已经存在
INSERT INTO Table2 (B1, B2, A1) VALUES (6, 'ValueB2_6', 6);
INSERT INTO Table2 (B1, B2, A1) VALUES (7, 'ValueB2_7', 6);
INSERT INTO Table2 (B1, B2, A1) VALUES (8, 'ValueB2_8', 6);
INSERT INTO Table2 (B1, B2, A1) VALUES (9, 'ValueB2_9', 7);
INSERT INTO Table2 (B1, B2, A1) VALUES (10, 'ValueB2_10', 7);
INSERT INTO Table2 (B1, B2, A1) VALUES (11, 'ValueB2_11', 7);
INSERT INTO Table2 (B1, B2, A1) VALUES (12, 'ValueB2_12', 8);
INSERT INTO Table2 (B1, B2, A1) VALUES (13, 'ValueB2_13', 8);
INSERT INTO Table2 (B1, B2, A1) VALUES (14, 'ValueB2_14', 8);

-- 插入 Table3 的数据
INSERT INTO Table3 (C1, C2, B1) VALUES (1, 'Record1', 6);
INSERT INTO Table3 (C1, C2, B1) VALUES (2, 'Record2', 7);
INSERT INTO Table3 (C1, C2, B1) VALUES (3, 'Record3', 8);
INSERT INTO Table3 (C1, C2, B1) VALUES (4, 'Record4', 9);
INSERT INTO Table3 (C1, C2, B1) VALUES (5, 'Record5', 10);
INSERT INTO Table3 (C1, C2, B1) VALUES (6, 'Record6', 11);
INSERT INTO Table3 (C1, C2, B1) VALUES (7, 'Record7', 12);
INSERT INTO Table3 (C1, C2, B1) VALUES (8, 'Record8', 13);
INSERT INTO Table3 (C1, C2, B1) VALUES (9, 'Record9', 14);
INSERT INTO Table3 (C1, C2, B1) VALUES (10, 'Record10', 6);
INSERT INTO Table3 (C1, C2, B1) VALUES (11, 'Record11', 7);
INSERT INTO Table3 (C1, C2, B1) VALUES (12, 'Record12', 8);
INSERT INTO Table3 (C1, C2, B1) VALUES (13, 'Record13', 9);
INSERT INTO Table3 (C1, C2, B1) VALUES (14, 'Record14', 10);
INSERT INTO Table3 (C1, C2, B1) VALUES (15, 'Record15', 11);
INSERT INTO Table3 (C1, C2, B1) VALUES (16, 'Record16', 12);
INSERT INTO Table3 (C1, C2, B1) VALUES (17, 'Record17', 13);
INSERT INTO Table3 (C1, C2, B1) VALUES (18, 'Record18', 14);
INSERT INTO Table3 (C1, C2, B1) VALUES (19, 'Record19', 6);
INSERT INTO Table3 (C1, C2, B1) VALUES (20, 'Record20', 7);
INSERT INTO Table3 (C1, C2, B1) VALUES (21, 'Record21', 8);
INSERT INTO Table3 (C1, C2, B1) VALUES (22, 'Record22', 9);
INSERT INTO Table3 (C1, C2, B1) VALUES (23, 'Record23', 10);
INSERT INTO Table3 (C1, C2, B1) VALUES (24, 'Record24', 11);
INSERT INTO Table3 (C1, C2, B1) VALUES (25, 'Record25', 12);
INSERT INTO Table3 (C1, C2, B1) VALUES (26, 'Record26', 13);
INSERT INTO Table3 (C1, C2, B1) VALUES (27, 'Record27', 14);