数据库多对多关系示例 - 使用sqlite演示

骑猪看日落 2022-12-30 10:48 107阅读 0赞

多对多，可以理解为是一对多和多对一的组合；要实现多对多，一般都需要有一张中间表（也叫关联表），将两张表进行关联，形成多对多的形式；  
例如：  
    老师表、班级表、课程表，中间表为：课程表；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70][]

如果sqlite数据库不显示列头；见此；

[https://blog.csdn.net/bcbobo21cn/article/details/111122445][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>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70]: /images/20221120/ecfb91b6f86d4897a113ccba3805353d.png
[https_blog.csdn.net_bcbobo21cn_article_details_111122445]: https://blog.csdn.net/bcbobo21cn/article/details/111122445