【MySQL数据库】面试常见习题：多表练习案例3—学生课程

学生_课程多表练习

数据准备

 --完成学员和老师表,课程,选课表设计
    create table student(
    	id int primary key auto_increment,
    	name varchar(20),
    	city varchar(10),
    	age int
    );
      create table teacher(
    	id int primary key auto_increment,
    	name varchar(20)
    );
    
    create table course(
    	id int primary key auto_increment,
    	name varchar(20),
    	teacher_id int,
    	foreign key (teacher_id) references teacher(id)
    );
    create table student_course(
    	student_id int,
    	course_id int,
    	score int,
    	foreign key (student_id) references student(id),
    	foreign key (course_id) references course(id)
    );

	--插入数据
    insert into teacher values(null,'关羽');
    insert into teacher values(null,'张飞');
    insert into teacher values(null,'赵云');
    
    insert into student values(null,'小王','北京',20);
    insert into student values(null,'小李','上海',18);
    insert into student values(null,'小周','北京',22);
    insert into student values(null,'小刘','北京',21);
    insert into student values(null,'小张','上海',22);
    insert into student values(null,'小赵','北京',17);
    insert into student values(null,'小蒋','上海',23);
    insert into student values(null,'小韩','北京',25);
    insert into student values(null,'小魏','上海',25);
    insert into student values(null,'小明','北京',20);
    
    insert into course values(null,'语文',1);
    insert into course values(null,'数学',1);
    insert into course values(null,'生物',2);
    insert into course values(null,'化学',2);
    insert into course values(null,'物理',2);
    insert into course values(null,'英语',3);
    
    insert into student_course values(1,1,80);
    insert into student_course values(1,2,90);
    insert into student_course values(1,3,85);
    insert into student_course values(1,4,78);
    insert into student_course values(2,2,53);
    insert into student_course values(2,3,77);
    insert into student_course values(2,5,80);
    insert into student_course values(3,1,71);
    insert into student_course values(3,2,70);
    insert into student_course values(3,4,80);
    insert into student_course values(3,5,65);
    insert into student_course values(3,6,75);
    insert into student_course values(4,2,90);
    insert into student_course values(4,3,80);
    insert into student_course values(4,4,70);
    insert into student_course values(4,6,95);
    insert into student_course values(5,1,60);
    insert into student_course values(5,2,70);
    insert into student_course values(5,5,80);
    insert into student_course values(5,6,69);
    insert into student_course values(6,1,76);
    insert into student_course values(6,2,88);
    insert into student_course values(6,3,87);
    insert into student_course values(7,4,80);
    insert into student_course values(8,2,71);
    insert into student_course values(8,3,58);
    insert into student_course values(8,5,68);
    insert into student_course values(9,2,88);
    insert into student_course values(10,1,77);
    insert into student_course values(10,2,76);
    insert into student_course values(10,3,80);
    insert into student_course values(10,4,85);
    insert into student_course values(10,5,83);

需求

– 选课表:学生外键,课程外键,分数
– 课程表:课程编号,课程名,老师外键
– 学生表:编号,姓名,城市,年龄
– 老师表:编号,老师姓名

– 1、查询平均成绩大于70分的同学的学号和平均成绩

    SELECT
       stu.`id`,AVG(score)
    FROM
       student stu,student_course sc
    WHERE
       sc.`student_id` = stu.`id`
    GROUP BY stu.`id`
    HAVING AVG(score)>70;

– 2、查询所有同学的学号、姓名、选课数、总成绩

    SELECT 
       stu.`id`,
       stu.`name`,
       COUNT(sc.`course_id`),
       SUM(score)
    FROM
       student stu,student_course sc
    WHERE
       stu.`id` = sc.`student_id`
    GROUP BY stu.`id`

– 3、查询学过赵云老师所教课的同学的学号、姓名

    SELECT 
       stu.`id`,
       stu.`name`
    FROM
       student stu,student_course sc,course co
    WHERE
       stu.`id` = sc.`student_id`
    AND
       sc.`course_id` IN (SELECT co.`id`
       					 FROM course 
       					 WHERE co.`id` IN(SELECT id 
       					 FROM teacher 
       					 WHERE NAME = '赵云'));

– 4、查询没学过关羽老师课的同学的学号、姓名

    SELECT DISTINCT
       stu.`id`,
       stu.`name`
    FROM
       student stu
    WHERE stu.`id` NOT IN(  --  不在后面查询结果中
       SELECT stu.`id`
       FROM student stu,student_course sc,course co 
       WHERE 
          stu.`id` = sc.`student_id`
          AND
          sc.`course_id` IN (-- 查询选课的课程id 是老师带的课程的id
          SELECT co.`id` FROM course WHERE co.`id` IN( -- 查询老师带的课程的课程id
          SELECT id FROM teacher WHERE NAME = '关羽')));-- 查询老师的编号

– 5、查询没有学三门课以上的同学的学号、姓名

    SELECT 
       stu.`id`,
       stu.`name`,
       COUNT(sc.`course_id`)
    FROM
       student stu,student_course sc
    WHERE
       stu.`id` = sc.`student_id`
    GROUP BY stu.`id`
    HAVING COUNT(sc.`course_id`)<=3;

– 6、查询各科成绩最高和最低的分

    SELECT
       co.`name`,
       MAX(score),
       MIN(score)
    FROM
       student_course sc,course co
    WHERE
       sc.`course_id` = co.`id`
    GROUP BY co.`id`;

注：以上代码都是亲手手打，本人初学者，难免有一些低级错误出现，希望大佬们指正！ 分享自己练习的案例，一起进步一起学习！