mysql多对多关系演示

前言：

数据库中的多对多关联关系一般需采用中间表的方式处理，将多对多转化为两个一对多。

这里以学生表和课程表，一个学生可以选多个课程，一个课程可以被多个学生选择。

 

案例一

1. 创建学生表

create table students (
id int not null primary key auto_increment,
name varchar(45) not null
)engine=innodb default charset=utf8;

2. 创建课程表

reate table courses (
id int not null primary key auto_increment,
name varchar(45) not null 
)engine=innodb default charset=utf8;

3. 创建中间表

create table stu_cour (
id int not null primary key auto_increment,
course_id int not null,
stu_id int not null,
constraint cour foreign key(course_id) references courses(id),
constraint stu foreign key(stu_id) references students(id)  
)engine=innodb default charset=utf8;

4. 插入数据

insert into students values (0,"小王");
insert into students values (0,"小宋");
insert into students values (0,"小李");
 
 
insert into courses values (0,"语文");
insert into courses values (0,"数学");
insert into courses values (0,"英语");
 
 
insert into stu_cour values (0,1,1);
insert into stu_cour values (0,1,2);
insert into stu_cour values (0,1,3);
insert into stu_cour values (0,2,1);
insert into stu_cour values (0,2,3);
insert into stu_cour values (0,3,2);
insert into stu_cour values (0,3,3);

 

5. 查询学生1选了那些课程

SELECT courses.id,courses.name FROM courses 
INNER JOIN stu_cour ON stu_cour.course_id=courses.id
INNER JOIN students ON students.id= 1 
and students.id = stu_cour.stu_id;

 

6. 查询课程2被哪些学生选了

SELECT students.name FROM students 
INNER JOIN stu_cour ON stu_cour.stu_id =students.id
INNER JOIN courses ON courses.id= 2 
and stu_cour.course_id = courses.id;

 

案例二

1. 创建三张数据表students ,course,stu_cour

/**学生表*/
CREATE TABLE students (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*学生课程表*/
CREATE TABLE course(
cour_id INT AUTO_INCREMENT,
NAME VARCHAR(50),
CODE VARCHAR(30),
PRIMARY KEY(cour_id)
)
/**学生课程关联表*/
CREATE TABLE stu_cour(
sc_id INT AUTO_INCREMENT,
stu_id INT ,
cour_id INT,
PRIMARY KEY(sc_id)
)

2. 为Stu_Cour表添加外键

/*添加外键约束*/
ALTER TABLE stu_cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES students(stu_id)
ALTER TABLE stu_cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cour_id) REFERENCES course(cour_id)