MySQL数据库建立多对多的数据表关系-学生课程表例子(附源码)

新建查询并执行下面代码：

image.png

# 创建学生表课程表sql
DROP TABLE IF EXISTS student_course_relation;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS course;

#学生表
CREATE TABLE student(
student_id  INT PRIMARY KEY AUTO_INCREMENT, -- 学生id 主键 自增长
NAME        VARCHAR(20),-- 姓名
age         INT,-- 年龄
id_no       CHAR(18),-- 身份证号码
sex         CHAR(1) -- 性别
);

#课程表
CREATE TABLE course(
course_id INT PRIMARY KEY AUTO_INCREMENT, -- 课程id 主键 自增长
NAME VARCHAR(20) -- 课程名称
);

#学生课程中间表
CREATE TABLE student_course_relation(
student_id INT,
course_id INT,

CONSTRAINT pk_sc_id PRIMARY KEY(student_id, course_id), -- 设置为联合id
CONSTRAINT fk_student_id FOREIGN KEY(student_id) REFERENCES student(student_id), -- 设置外键
CONSTRAINT fk_course_id FOREIGN KEY(course_id) REFERENCES course(course_id)
);


#学生表插入数据
INSERT INTO student(NAME,age,id_no,sex) VALUES('步惊云',20,'632323190605262000','男');
INSERT INTO student(NAME,age,id_no,sex) VALUES('聂风',22,'632323190605264501','男');
INSERT INTO student(NAME,age,id_no,sex) VALUES('赵敏',18,'632323190605261001','女');
INSERT INTO student(NAME,age,id_no,sex) VALUES('张三丰',40,'632323190605268000','男');
INSERT INTO student(NAME,age,id_no,sex) VALUES('张曼玉',25,'632323190605260447','女');
INSERT INTO student(NAME,age,id_no,sex) VALUES('猪八戒',50,'632323190605265002','男');
INSERT INTO student(NAME,age,id_no,sex) VALUES('孙悟空',500,'632323190605268780','男');
INSERT INTO student(NAME,age,id_no,sex) VALUES('任盈盈',20,'632323190605263007','女');
INSERT INTO student(NAME,age,id_no,sex) VALUES('赵灵儿',18,'632323190605260922','女');
INSERT INTO student(NAME,age,id_no,sex) VALUES('李逍遥',20,'632323190605267585','男');
INSERT INTO student(NAME,age,id_no,sex) VALUES('小白龙',200,'632323190605264085','男');
INSERT INTO student(NAME,age,id_no,sex) VALUES('叶凡',30,'632323190605266160','男');
INSERT INTO student(NAME,age,id_no,sex) VALUES('姬紫月',17,'632323190605265000','女');

#课程表插入数据
INSERT INTO course(NAME) VALUES('高等数学');
INSERT INTO course(NAME) VALUES('线性代数');
INSERT INTO course(NAME) VALUES('数据结构');
INSERT INTO course(NAME) VALUES('大学英语');
INSERT INTO course(NAME) VALUES('大学计算机基础');
INSERT INTO course(NAME) VALUES('概率论');
INSERT INTO course(NAME) VALUES('C语言');
INSERT INTO course(NAME) VALUES('c++');
INSERT INTO course(NAME) VALUES('高等代数');
INSERT INTO course(NAME) VALUES('插花艺术欣赏');
INSERT INTO course(NAME) VALUES('语文');

#课程-学生关联表插入数据
INSERT INTO student_course_relation(student_id,course_id) VALUES(1,1);
INSERT INTO student_course_relation(student_id,course_id) VALUES(1,5);
INSERT INTO student_course_relation(student_id,course_id) VALUES(1,7);
INSERT INTO student_course_relation(student_id,course_id) VALUES(2,2);
INSERT INTO student_course_relation(student_id,course_id) VALUES(2,3);
INSERT INTO student_course_relation(student_id,course_id) VALUES(2,10);
INSERT INTO student_course_relation(student_id,course_id) VALUES(3,5);
INSERT INTO student_course_relation(student_id,course_id) VALUES(3,8);
INSERT INTO student_course_relation(student_id,course_id) VALUES(3,6);
INSERT INTO student_course_relation(student_id,course_id) VALUES(4,2);
INSERT INTO student_course_relation(student_id,course_id) VALUES(4,11);
INSERT INTO student_course_relation(student_id,course_id) VALUES(5,1);
INSERT INTO student_course_relation(student_id,course_id) VALUES(5,2);
INSERT INTO student_course_relation(student_id,course_id) VALUES(5,3);
INSERT INTO student_course_relation(student_id,course_id) VALUES(6,1);
INSERT INTO student_course_relation(student_id,course_id) VALUES(6,9);
INSERT INTO student_course_relation(student_id,course_id) VALUES(6,10);
INSERT INTO student_course_relation(student_id,course_id) VALUES(7,3);
INSERT INTO student_course_relation(student_id,course_id) VALUES(7,5);
INSERT INTO student_course_relation(student_id,course_id) VALUES(7,7);
INSERT INTO student_course_relation(student_id,course_id) VALUES(8,2);
INSERT INTO student_course_relation(student_id,course_id) VALUES(8,5);
INSERT INTO student_course_relation(student_id,course_id) VALUES(8,4);
INSERT INTO student_course_relation(student_id,course_id) VALUES(9,9);
INSERT INTO student_course_relation(student_id,course_id) VALUES(9,5);
INSERT INTO student_course_relation(student_id,course_id) VALUES(9,4);
INSERT INTO student_course_relation(student_id,course_id) VALUES(10,5);
INSERT INTO student_course_relation(student_id,course_id) VALUES(11,3);
INSERT INTO student_course_relation(student_id,course_id) VALUES(12,3);
INSERT INTO student_course_relation(student_id,course_id) VALUES(13,9);

效果如下：

image.png

然后就可以愉快的练习sql语句了。