【九，MySQL】基础篇--多表查询（多表关系：一对多/多对一，多对多，一对一）

一，多表关系
1，一对多（多对一）

2，多对多

# =====================================================多对多=====================================================
create table student(
    id int auto_increment primary key comment 'id，主键',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表' charset = utf8;

insert into student(id, name, no) VALUES (1,'殷天正','1001'),(2,'张无忌','1002'),(3,'周芷若','1003'),(4,'慕容复','1004'),(5,'乔峰','1005');

drop table score;

create table score(
    id int auto_increment primary key comment 'id，主键',
    name varchar(10) comment '课程名称'
)comment '课程表' charset = utf8;

insert into score(id, name) values (1,'JAVA'),(2,'HTML'),(3,'C#'),(4,'JavaScript'),(5,'Oracle');

create table student_score(
    id int auto_increment primary key comment 'id，主键',
    student_id int not null comment '学生id',
    score_id int not null comment '课程id',
    constraint fk_student_id foreign key (student_id) references student(id),
    constraint fk_score_id foreign key (score_id) references score(id)
)comment '学生课程中间表' charset = utf8;

insert into student_score values (null,1,1),(null,1,2),(null,1,3),(null,1,4),(null,1,5);

select * from student;
select * from score;
select * from student_score;

图形化界面展示多表之间的关系


3，一对一


通过添加unique约束保证其是一对一的关系

# =====================================================一对一=====================================================

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment'年龄',
    gender char(1) comment '1:男，2:女',
    phone char(11) comment '手机号'
) comment '用户基本信息表' charset = utf8;

create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    user_id int unique comment '用户ID，添加约束，保证其是一对一关系',
    constraint fk_userid foreign key (user_id) references tb_user(id)
) comment '用户教育信息表' charset = utf8;

insert into tb_user(id,name,age,gender,phone) values
(null,'黄渤',45,'1', '18800001111') ,
(null,'冰冰',35,'2', '18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, user_id) values
(null,'本科','软件开发','xx小学','xx第一中学','xxx科技学院',1),
(null,'专科','会计','xx小学','xx第一中学','xx职业学院',2),
(null,'本科','酒店管理','xx小学','xx第一中学','xxx管理学院',3),
(null,'本科','教育','xx小学','xx第一中学','xx师范学院',4);

select * from tb_user;
select * from tb_user_edu;