专业做财务公司网站,中企动力主要是干嘛的,网站乱码解决办法,wordpress4.8.1三张表的文字描述 表1#xff1a;包含字段A1,字段A2,字段A3. 字段A1是主键 表2#xff1a;包含字段B1#xff0c;字段B2#xff0c;字段A1 字段B1是主键#xff0c;字段A1是其外键。 1个字段A1的值可以匹配多条表2的记录. 表3#xff1a;包含字段C1#xff0c;字段C2包含字段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中的所有记录进行组合。
