一.一对一
在数据的一对一关系中，可以在任意一个数据表中添加对另一个表的外键约束，但是该外键约束必须是unique的，否则无法实现一对一关系。
create table tb_user(id int unsigned primary key auto_increment comment 'ID',name varchar(10) not null comment '姓名',gender tinyint unsigned not null comment '性别, 1 男 2 女',phone char(11) comment '手机号',degree varchar(10) comment '学历'
) comment '用户信息表';insert into tb_user values (1,'白眉鹰王',1,'18812340001','初中'),(2,'青翼蝠王',1,'18812340002','大专'),(3,'金毛狮王',1,'18812340003','初中'),(4,'紫衫龙王',2,'18812340004','硕士');create table tb_user_card(id int unsigned primary key auto_increment comment 'ID',nationality varchar(10) not null comment '民族',birthday date not null comment '生日',idcard char(18) not null comment '身份证号',issued varchar(20) not null comment '签发机关',expire_begin date not null comment '有效期限-开始',expire_end date comment '有效期限-结束',user_id int unsigned not null unique comment '用户ID',constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户信息表';insert into tb_user_card values (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1),(2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),(3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3),(4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);
二.多对多
create table tb_student(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',no varchar(10) comment '学号'
) comment '学生表';
insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');create table tb_course(id int auto_increment primary key comment '主键ID',name varchar(10) comment '课程名称'
) comment '课程表';
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');create table tb_student_course(id int auto_increment comment '主键' primary key,student_id int not null comment '学生ID',course_id int not null comment '课程ID',constraint fk_courseid foreign key (course_id) references tb_course (id),constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '学生课程中间表';insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);
