课程表
CREATE TABLE `course` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`teacher_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '英语', '3');
学生表
CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`number` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('1', '学生1', '20200001', '18');
INSERT INTO `student` VALUES ('2', '学生2', '20200002', '19');
INSERT INTO `student` VALUES ('3', '学生3', '20200003', '20');
INSERT INTO `student` VALUES ('4', '学生4', '20200004', '11');
INSERT INTO `student` VALUES ('5', '学生5', '20200005', null);
成绩表
CREATE TABLE `score` (
`student_no` int(11) DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
`score` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `score` VALUES ('20200001', '1', '60');
INSERT INTO `score` VALUES ('20200001', '2', '40');
INSERT INTO `score` VALUES ('20200001', '3', '99');
INSERT INTO `score` VALUES ('20200002', '3', '99');
INSERT INTO `score` VALUES ('20200002', '2', '80');
INSERT INTO `score` VALUES ('20200002', '1', '60');
INSERT INTO `score` VALUES ('20200003', '1', '50');
INSERT INTO `score` VALUES ('20200003', '2', '59');
INSERT INTO `score` VALUES ('20200004', '2', '80');
INSERT INTO `score` VALUES ('20200004', '1', '50');
教师表
CREATE TABLE `teacher` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`number` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `teacher` VALUES ('1', '老师1', '30201');
INSERT INTO `teacher` VALUES ('2', '老师2', '30202');
INSERT INTO `teacher` VALUES ('3', '老师3', '30203');
问题及答案
https://blog.csdn.net/qq_42327755/article/details/109006251