一对一关系：
create table customer( id int primary key auto_increment, name varchar(20) not null, qq varchar(10) not null, phone char(16) not null ); create table student( id int primary key auto_increment, class_name varchar(20) not null, customer_id int unique, #该字段一定要是唯一的 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique on delete cascade on update cascade );
多对一关系：
create table press( id int primary key auto_increment, name varchar(20) ); create table book( id int primary key auto_increment, name varchar(20), press_id int not null, foreign key(press_id) references press(id) on delete cascade on update cascade ); insert into press(name) values (‘北京工业地雷出版社‘), (‘人民音乐不好听出版社‘), (‘知识产权没有用出版社‘) ; insert into book(name,press_id) values (‘九阳神功‘,1), (‘九阴真经‘,2), (‘九阴白骨爪‘,2), (‘独孤九剑‘,3), (‘降龙十巴掌‘,2), (‘葵花宝典‘,3) ;
多对多：
create table author( id int primary key auto_increment, name varchar(20) ); #这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了 create table author2book( id int not null unique auto_increment, author_id int not null, book_id int not null, constraint fk_author foreign key(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) ); #插入四个作者，id依次排开 insert into author(name) values(‘egon‘),(‘alex‘),(‘yuanhao‘),(‘wpq‘); #每个作者与自己的代表作如下 egon: 九阳神功 九阴真经 九阴白骨爪 独孤九剑 降龙十巴掌 葵花宝典 alex: 九阳神功 葵花宝典 yuanhao: 独孤九剑 降龙十巴掌 葵花宝典 wpq: 九阳神功 insert into author2book(author_id,book_id) values (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,1), (2,6), (3,4), (3,5), (3,6), (4,1) ;
原文：https://www.cnblogs.com/zhujing666/p/12313172.html