阿里Java学习路线：阶段 2：数据库开发－SQL进阶及查询练习：课时10：多对多关系

2 多对多关系
在表中建立多对多关系需要使用中间表，即需要三张表，在中间表中使用两个外键，分别引用其他两个表的主键。

CREATE TABLE student(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(50)
);

CREATE TABLE teacher(
	tid INT PRIMARY KEY AUTO_INCREMENT,
	tname VARCHAR(50)
);

CREATE TABLE student_teacher(
	sid INT,
	tid INT,
	CONSTRAINT fk_student_teacher_sid FOREIGN KEY(sid) REFERENCES student(sid),
	CONSTRAINT fk_student_teacher_tid FOREIGN KEY(tid) REFERENCES teacher(tid)
);

INSERT INTO student VALUES(NULL,'刘德华');
INSERT INTO student VALUES(NULL,'梁朝伟');
INSERT INTO student VALUES(NULL,'黄日华');
INSERT INTO student VALUES(NULL,'苗侨伟');
INSERT INTO student VALUES(NULL,'汤镇业');

INSERT INTO teacher VALUES(NULL,'崔老师');
INSERT INTO teacher VALUES(NULL,'刘老师');
INSERT INTO teacher VALUES(NULL,'石老师');

SELECT * FROM student;
SELECT * FROM teacher;

INSERT INTO student_teacher VALUES(1,1);
INSERT INTO student_teacher VALUES(2,1);
INSERT INTO student_teacher VALUES(3,1);
INSERT INTO student_teacher VALUES(4,1);
INSERT INTO student_teacher VALUES(5,1);

INSERT INTO student_teacher VALUES(2,2);
INSERT INTO student_teacher VALUES(3,2);
INSERT INTO student_teacher VALUES(4,2);

INSERT INTO student_teacher VALUES(3,3);
INSERT INTO student_teacher VALUES(4,3);
INSERT INTO student_teacher VALUES(5,3);

SELECT * FROM student_teacher;

这时在student_teacher这个中间表中的每条记录都是来说明student和teacher表的关系。