MySQL表與表之間有什么關系？表與表的多種關系

表1 foreign key 表2 則表1的多條記錄對應表2的一條記錄，即多對一  利用foreign key的原理我們可以制作兩張表的多對多，一對一關系多對多：     表1的多條記錄可以對應表2的一條記錄     表2的多條記錄也可以對應表1的一條記錄  一對一：     表1的一條記錄唯一對應表2的一條記錄，反之亦然  分析時，我們先從按照上面的基本原理去套，然后再翻譯成真實的意義，就很好理解了

書要關聯出版社 被關聯的表 create??table?press(id?int?primary?key?auto_increment,?name?char(20));  關聯的表 create?table?book( book_id?int?primary?key?auto_increment, book_name?varchar(20), book_price?int, press_id?int, constraint?Fk_pressid_id?foreign?key(press_id)?references?press(id) on?delete?cascade on?update?cascade );

insert?into?press(name)?values('新華出版社'),?('海燕出版社'),?('擺渡出版社'),?('大眾出版社');  insert?into?book(book_name,book_price,press_id)?values('Python爬蟲',100,1),?('Linux',80,1),?('操作系統',70,2),?('數學',50,2),?('英語',103,3),?('網頁設計',22,3);

先建被關聯的表 create?table?user( id?int?primary?key?auto_increment,?#主鍵自增name?char(10) );  再建關聯表 create?table?admin( id?int?primary?key?auto_increment, user_id?int?unique, password?varchar(16), foreign?key(user_id)?references?user(id) on?delete?cascade on?update?cascade );

insert?into?user(name)?values('susan1'),('susan2'),('susan3'),('susan4')('susan5'),('susan6');  insert?into?admin(user_id,password)?values(4,'sds156'),(2,'531561'),(6,'f3swe');

create?table?customer( id?int?primary?key?auto_increment, name?varchar(10), qq?int?unique, phone?int?unique );  create?table?student1( sid?int?primary?key?auto_increment, course?char(20), class_time?time, cid?int?unique, foreign?key(cid)?references?customer(id) on?delete?cascade on?update?cascade );

insert?into?customer(name,qq,phone)?values('小小',13564521,11111111),('嘻哈',14758254,22222222),('王維',44545522,33333333),('胡軍',545875212,4444444),('李希',145578543,5555555),('李迪',754254653,8888888),('艾哈',74545145,8712547),('嘖嘖',11147752,7777777);  insert?into?student1(course,class_time,cid)?values('python','08:30:00',3),('python','08:30:00',4),('linux','08:30:00',1),('linux','08:30:00',7);

========書和作者，另外在建一張表來存書和作者的關系 #被關聯的 create?table?book1( id?int?primary?key?auto_increment, name?varchar(10), price?float(3,2) ); #========被關聯的 create?table?author( id?int?primary?key?auto_increment, name?char(5) ); #========關聯的 create?table?author2book( id?int?primary?key?auto_increment, book_id?int?not?null, author_id?int?not?null, unique(book_id,author_id), foreign?key(book_id)?references?book1(id) on?delete?cascade on?update?cascade, foreign?key(author_id)?references?author(id) on?delete?cascade on?update?cascade );

insert?into?book1(name,price)?values('九陽神功',9.9),?('葵花寶典',9.5),?('辟邪劍譜',5),??('降龍十巴掌',7.3);  insert?into?author(name)?values('egon'),('e1'),('e2'),('e3'),('e4');  insert?into?author2book(book_id,author_id)?values(1,1),(1,4),(2,1),(2,5),(3,2),(3,3),(3,4),(4,5);

--?用戶表 create?table?user?(id?int?primary?key?auto_increment,username?varchar(20)?not?null,password?varchar(50)?not?null);  insert?into?user(username,password)?values('egon','123'),('root',147),('alex',123),('haiyan',123),('yan',123);   --?用戶組表 create?table?usergroup(id?int?primary?key?auto_increment,groupname?varchar(20)??not?null?unique);  insert?into?usergroup(groupname)?values('IT'),('Sale'),('Finance'),('boss');   --?主機表 CREATE?TABLE?host(id?int?primary?key?auto_increment,ip?CHAR(15)?not?NULL?UNIQUE?DEFAULT?'127.0.0.1');  insert?into?host(ip)?values('172.16.45.2'),('172.16.31.10'),('172.16.45.3'),('172.16.31.11'),('172.10.45.3'),?('172.10.45.4'),('172.10.45.5'),('192.168.1.20'),('192.168.1.21'),('192.168.1.22'),('192.168.2.23'),('192.168.2.223'), ('192.168.2.24'),('192.168.3.22'),('192.168.3.23'),('192.168.3.24');

--?建立user和usergroup的關系表 create?table?user2usergroup( id?int?not?NULL?UNIQUE?auto_increment, user_id?int?not?null, group_id?int?not?NULL, PRIMARY?KEY(user_id,group_id), foreign?key(user_id)?references?user(id) ON?DELETE?CASCADE on?UPDATE?CASCADE?, foreign?key(group_id)?references?usergroup(id) ON?DELETE?CASCADE on?UPDATE?CASCADE );  insert?into?user2usergroup(user_id,group_id)?values(1,1),(1,2),(1,3),(1,4),(2,3),(2,4),(3,4);   --?建立user和host的關系 create?table?user2host( id?int?not?null?unique?auto_increment, user_id?int?not?null, host_id?int?not?null, primary?key(user_id,host_id), foreign?key(user_id)?references?user(id), foreign?key(host_id)?references?host(id) );  insert?into?user2host(user_id,host_id)?values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),(1,14),(1,15),(1,16),(2,2),(2,3),(2,4),(2,5),(3,10),(3,11),(3,12);