mysql数据库之联表查询

表准备:

这次我们用到5张表:

class表:

 

 

 

 

 

 

student表:

 

 

 

 

 

 

 

 

 

 

 

 

 

score表:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

course表:

 

 

 

 

 

 

teacher表:

 

 

 

 

 

 

表结构模型:

 

我们针对以下需求分析联表查询:

1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、 查询没有报李平老师课的学生姓名
4、 查询选修物理课程和体育课程其中一门的学生姓名
5、 查询挂科超过两门(包括两门)的学生姓名和班级

6、找出同时选了李平老师所有课的学生班级和姓名

 

1、查询所有的课程的名称以及对应的任课老师姓名

分析需求:我们需要用到course和teacher表:既需要得到课程名称又要拿到老师姓名,然后看表结构模型,我们可以知道course有外键字段teacher_id指向teacher表id,那么我们就可以用内连接inner join将两张表拼接起来然后取其字段course.cname和teacher.tname即可得到我们想要的数据,SQL语句如下:

SELECT
    cname,
    tname 
FROM
    teacher
    INNER JOIN course ON course.teacher_id = teacher.tid;

2、查询平均成绩大于八十分的同学的姓名和平均成绩

需求分析:我们需要用到score表和shtudent表,既要拿到学生姓名又要拿到成绩,我们理所当然需要将这两个表联表或者做子连接,然后需求中需要用到平均数,那么我们应想到用聚合函数avg(),但使用聚合函数的前提是分组(不人为分组时默认整个表就是一个组) group by,下面我们来写sql语句:

首先在联表或子连接前可以通过score表分组得到student_id和平均成绩:

select student_id,avg(num) as avg_score from score group by student_id having avg(num) >80;

然后在以上虚拟表的基础上通过student_id拼接student表,取student.sname和avg_score即可

SELECT
    student.sname,
    k.avg_score 
FROM
    student
    INNER JOIN ( SELECT student_id, avg( num ) AS avg_score FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS k ON k.student_id = student.sid;

 

3、 查询没有报李平老师课的学生姓名

需求分析:我们根据表结构得知我们需要用到student,score,course,teacher这4张表,直接得到没有报李平老师课程的学生比较困难,那么我们就反过来想,哪些是报了李平老师课程的,然后在学生表里剔除掉即可:

首先我们可以先得到李平老师教了哪几门课,用course和teacher联表:

select course.cid,course.cname from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师";

根据表结构我们可以知道,course表和score表通过外键连接,那么我们就可以把上面得到的虚拟表和score表子查询,取字段score.student_id即可得到所有选了李平老师课程的学生id,然后根据student_id分组或去重就可以得到不重名的学生id选了李平老师课程的虚拟表:

select score.student_id from score where course_id in (

select course.cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师"
) ;

然后将上面得到的虚拟表与student表做子连接的条件得到选了李平老师课程的学生姓名,然后我们not in即可得到需求

select sname from student where sid not  in (

select score.student_id from score where course_id in (

select course.cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师"
) 
);

 

 

 

4、 查询选修物理课程和体育课程的其中一门的学生姓名

需求分析:需要通过课程得到学生姓名,通过表结构我们可以得知,需要用到 student、score、course表,需要得到选了这两门课程的学生姓名,那我们就可以通过course表先拿到物理课和体育课对应的id,然后把这个id作为score表的查询条件查出对应的student_id,然后我们可以对student_id进行分组后用having过滤掉group_concat(student_id)大于等于2的部分,即可得到只选修了这两门课程中其中一门的学生id,然后我们可以拿这个结果去作为student表的子连接条件得到学生姓名,sql语句如下:

SELECT
    student.sname 
FROM
    student 
WHERE
    sid IN (
    SELECT
        student_id 
    FROM
        score 
    WHERE
        course_id IN ( SELECT cid FROM course WHERE cname IN ( "物理", "体育" ) ) 
    GROUP BY
        student_id 
    HAVING
        count( student_id ) = 1 
    );

第二种查询方法:

SELECT
    k.sname,
    k.course_id 
FROM
    course
    INNER JOIN ( SELECT student.sname, score.course_id FROM student INNER JOIN score ON score.student_id = student.sid ) AS k ON course.cid = k.course_id 
WHERE
    course.cname IN ( "物理", "体育" ) 
GROUP BY
    k.sname 
HAVING
    count( k.sname ) = 1;

 

 

5、 查询挂科超过两门(包括两门)的学生姓名和班级

需求分析:这个需求和第四个需求类似,我们可以先拿到所有挂科的学生id,然后进行分组,筛选出挂科数大于等于2的一部分学生id,然后把取到的id作为student表的查询条件去取学生姓名即可,sql语句如下:

SELECT
    caption,
    k.sname 
FROM
    class
    INNER JOIN (
    SELECT
        sname,
        class_id 
    FROM
        student 
    WHERE
        sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( student_id ) >= 2 ) 
    ) k ON k.class_id = class.cid;

 

6、找出同时选了李平老师所有课的学生班级和姓名

需求分析:需要用到班级和老师,那么5张表都要用到,我们还是套用前面的方法,先通过teacher表找到李平老师的id,通过这个id在course表里找到李平老师的课程表的id,然后拿这个id去score表里找选了李平老师课程的student_id,这时候我们要对student_id进行分组了,筛选出含2个以上的student_id,再通过得到的student_id去student_表里找到对应的姓名和class_id,然后将生成的虚拟表去和class表联表,取出class.cname和student.sname即是我们想要的数据,sql语句如下:

SELECT
    class.caption,
    n.sname 
FROM
    class
    INNER JOIN (
    SELECT
        class_id,
        sname 
    FROM
        student 
    WHERE
        sid IN (
        SELECT
            student_id 
        FROM
            (
            SELECT
                student_id,
                course_id 
            FROM
                score 
            WHERE
                course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON course.teacher_id = teacher.tid WHERE teacher.tid = 2 ) 
            ) AS k 
        GROUP BY
            student_id 
        HAVING
            count( student_id ) = 2 
        ) 
    ) AS n ON class.cid = n.class_id ORDER BY n.sname;

版权声明:
作者:babaaini
链接:https://reai.us/index.php/2021/09/21/mysqlsjkzlbby/
来源:热爱
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
打赏
< <上一篇
下一篇>>