数据库中多对多的关系

create table employee(
id int auto_increment,
name varchar(20) not null,
dep_id int not null,
primary key(id)
);
insert into employee (name,dep_id) values ("刘备",1),("张飞",2),("关羽",2)

create table department (
id int auto_increment,
name varchar(20),
primary key(id)
);
insert into department (name) values("总经办"),("市场部");
alter table employee add foreign key (dep_id) references department (id);//插入外键

select e.id,e.name,d.name from employee e inner join department d on e.dep_id=d.id;

create table student(
id int auto_increment,
name varchar(20) not null,
primary key(id)
);
insert into student (name) values("张三"),("李四"),("王五"),("赵六")

create table teacher(
id int auto_increment,
name varchar(20) not null,
primary key(id)
);
insert into teacher (name) values("赵四"),("李武"),("王九"),("刘师");

create table relation(
stu_id int not null,
tea_id int not null
);
insert into relation (stu_id,tea_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,2),(4,1),(4,2);
//添加外键
alter table relation add foreign key(stu_id) references student (id);
alter table relation add foreign key(tea_id) references teacher (id);

select s.name ,t.name from 
student s inner join relation r 
on s.id=r.stu_id inner join teacher t
on t.id=r.tea_id

select s.name ,group_concat(t.name) from 
student s inner join relation r 
on s.id=r.stu_id inner join teacher t
on t.id=r.tea_id
group by s.name;