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

三张表的文字描述

表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);

查询语句

1.查询table2中A1 = '6'的记录

select * from table2 where A1 = '6';

2.查询table3中B1 = '6'的记录 

select * from table3 where B1 = '6';

3.如果要查询table3中对应的A1=‘6’的记录呢？ 

select * from table3 where B1 in (select B1 from table2 where A1 = '6');

9条记录 

4.思考：

select table3.*,table2.A1 from table3,table2 where table2.A1 = '6'

81条记录 

这样写为什么不对？ 

因为缺少了条件table2.B1 = table3.B1

5.

select table3.*,table2.A1 from table3,table2 where table2.A1 = '6' and table2.B1 = table3.B1

这样就对了 

同义语句：

select table3.*,table2.A1 from table3 inner join table2 on table2.A1 = '6' and table2.B1 = table3.B1

6.观察：

select table3.*,table2.* from table3,table2 

Table2和Table3联合起来（多表联查）：

共有243条记录 

table2有9条，table3有27条。

所以，实际上是9*27 = 243条

(笛卡尔积是指两个集合中所有可能的组合)（也就是上面这种情况）

table2中的每条记录都与table3中的所有记录进行组合。