MySQL-多表查询 如何链接两个表 一对一 ，一对多， 多对多

一对一表的连接

在其中一个表上增加一个外键字段（需设置unique确保唯一），通过该外键链接另一表的主键。

 员工表和员工信息拓展表为一对一关系

 员工表

create table emp (
    id int primary key auto_increment comment 'id主键',
    name varchar(10) not null unique comment '姓名',
    age  int  comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '部门id'  -- 部门表的外键
)comment '员工表';
insert into emp(id, name, age,job,salary,entrydate,managerid,dept_id) values
(1,'张局',48,'局长',18,'2008-01-17',1,1),
(2,'杜城',32,'刑侦一队队长',12,'2011-01-17',1,1),
(3,'沈翊',28,'画像师',18,'2018-01-17',1,2),
(4,'李晗',26,'信息技术员',8,'2016-01-17',1,2),
(5,'蒋峰',27,'刑侦队员',9,'2016-01-17',1,1),
(6,'何容月',30,'法医',12,'2011-01-17',1,3);

员工信息拓展表，ranksid为链接外键

create table emprank(
    id int primary key auto_increment comment '主键id',
    ranks varchar(20) not null comment '级别',
    ranksid int unique comment '级别id', --员工表的外键
    constraint fk_ranksid foreign key (ranksid) references emp(id)
)comment '警衔';

一对多表的连接

一对多，在多的那张表上增加一个外键字段，通过该外键链接另一表的主键。

部门表与员工表为一对多的关系

部门表

create table dept(
    id int primary key  auto_increment comment 'ID',
    name varchar(50) not null  comment '部门名称'
)comment '部门表';

员工表

create table emp (
    id int primary key auto_increment comment 'id主键',
    name varchar(10) not null unique comment '姓名',
    age  int  comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '部门id'  -- 部门表的外键
)comment '员工表';
insert into emp(id, name, age,job,salary,entrydate,managerid,dept_id) values
(1,'张局',48,'局长',18,'2008-01-17',1,1),
(2,'杜城',32,'刑侦一队队长',12,'2011-01-17',1,1),
(3,'沈翊',28,'画像师',18,'2018-01-17',1,2),
(4,'李晗',26,'信息技术员',8,'2016-01-17',1,2),
(5,'蒋峰',27,'刑侦队员',9,'2016-01-17',1,1),
(6,'何容月',30,'法医',12,'2011-01-17',1,3);

 员工表和部门表建立连接

alter table emp add 
constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

多对多表的连接 

多对多表的连接需要建立一个新表，新表建立外键，与两表主键分别连接

 学生表与课程表为多对多关系

学生表

create table stu(
    id int primary key auto_increment comment '主键id',
    name varchar(20) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表';

课程表

create table course(
    id int primary key auto_increment comment '主键id',
    name varchar(20) comment '课程名称'
)comment '课程表';

 学生课程中间表

create table stu_course(
    id int primary key auto_increment comment '主键id',
    stuid int not null  comment '学生id',
    courid int not null comment '课程id',
    constraint  fk_courid foreign key (courid) references course(id),
    constraint fk_stuid foreign key (stuid) references stu(id)
)comment '学生课程中间表';