資料庫多對多關系示例 - 使用sqlite示範

多對多，可以了解為是一對多和多對一的組合；要實作多對多，一般都需要有一張中間表（也叫關聯表），将兩張表進行關聯，形成多對多的形式；

例如：

    老師表、班級表、課程表，中間表為：課程表；

如果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>