创建数据库:

CREATE DATABASE IF NOT EXISTS `school_database` character set utf8 collate utf8_general_ci;

进入数据库:

use `school_database`;

建立学生表:

CREATE TABLE IF NOT EXISTS `students`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `student_id` bigint(20) NOT NULL COMMENT 'student_id',
  `name` varchar(10) NOT NULL COMMENT 'name',
  `age` int(2) NOT NULL COMMENT 'age',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='学生表';

建立课程表

CREATE TABLE IF NOT EXISTS `courses`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `name` varchar(10) NOT NULL COMMENT 'name',
  `course_id` bigint(20) NOT NULL COMMENT 'course_id',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `course_id` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='课程表';

建立成绩表

CREATE TABLE IF NOT EXISTS `scores`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `student_id` bigint(20) NOT NULL COMMENT 'student_id',
  `course_id` bigint(20) NOT NULL COMMENT 'course_id',
  `score` int(10) NOT NULL COMMENT 'score',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `student_course_id` (`student_id`,`course_id`),
  FOREIGN KEY(student_id) REFERENCES students(student_id),
  FOREIGN KEY(course_id) REFERENCES courses(course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='学生课程分数表';

插入学生数据:

insert into students (`student_id`, `name`, `age`) values (123, "张三", 18);
insert into students (`student_id`, `name`, `age`) values (124, "李四", 19);

插入课程数据:

insert into courses (`course_id`, `name`) values (1, "英语");
insert into courses (`course_id`, `name`) values (2, "数学");

插入成绩数据:

insert into scores (`student_id`, `course_id`, `score`) values (123, 1, 68);
insert into scores (`student_id`, `course_id`, `score`) values (123, 2, 89);

查询学生-张三的成绩

select 
        students.name, 
        courses.name as course, 
        scores.score
    from scores  
    left join students on students.student_id = scores.student_id
    left join courses on scores.course_id = courses.course_id
where students.name = "张三" ;