多对多,可以理解为是一对多和多对一的组合;要实现多对多,一般都需要有一张中间表(也叫关联表),将两张表进行关联,形成多对多的形式;
例如:
老师表、班级表、课程表,中间表为:课程表;
如果sqlite数据库不显示列头;见此;
https://blog.csdn.net/bcbobo21cn/article/details/111122445
张老师教一班和二班的语文;
赵老师教一班的物理和二班的数学;
这样就形成多对多;
Microsoft Windows [版本 6.1.7601]
Copyright (c) 2010 Microsoft Corporation. All rights reserved.
S:\sqlite>sqlite3 test.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main S:\sqlite\test.db
sqlite> create table teachers(tid int, tname varchar(50));
sqlite> insert into teachers values (1, '张老师'), (2, '赵老师');
sqlite> select * from teachers;
1|张老师
2|赵老师
sqlite> create table classes (cid int, cname varchar(50));
sqlite> insert into classes values (1, '一班'), (2, '二班');
sqlite> .header on
sqlite> select * from classes;
cid|cname
1|一班
2|二班
sqlite> create table courses (courid integer PRIMARY KEY autoincrement, tid int,
cid int, courname varchar(50));
sqlite> insert into courses values (1, 1, '语文');
Error: table courses has 4 columns but 3 values were supplied
sqlite> insert into courses values (null, 1, 1, '语文');
sqlite> insert into courses values (null, 1, 2, '语文')_;
Error: near "_": syntax error
sqlite> insert into courses values (null, 1, 2, '语文');
sqlite> insert into courses values (null, 2, 1, '物理');
sqlite> insert into courses values (null, 2, 2, '数学');
sqlite> select * from courses;
courid|tid|cid|courname
1|1|1|语文
2|1|2|语文
3|2|1|物理
4|2|2|数学
sqlite> SELECT T1.courid, T2.tname, T1.cid, T3.cname, T1.courname FROM courses A
S T1, teachers AS T2, classes AS T3 WHERE T1.tid=T2.tid AND T1.cid=T3.cid;
courid|tname|cid|cname|courname
1|张老师|1|一班|语文
2|张老师|2|二班|语文
3|赵老师|1|一班|物理
4|赵老师|2|二班|数学
sqlite> SELECT T1.tid, T2.tname, T1.cid, T3.cname, T1.courname FROM courses AS T
1, teachers AS T2, classes AS T3 WHERE T1.tid=T2.tid AND T1.cid=T3.cid;
tid|tname|cid|cname|courname
1|张老师|1|一班|语文
1|张老师|2|二班|语文
2|赵老师|1|一班|物理
2|赵老师|2|二班|数学
sqlite>