MySQL数据库入门(九)多表复杂查询练习及讲解-中

建表与数据准备

建库和建表的操作请参考“多表复杂查询练习及讲解-上”的内容，在此不再赘述。

use day5;  # 切换day5为当前数据库

show tables;
+----------------+
| Tables_in_day5 |
+----------------+
| course         |
| score          |
| student        |
| teacher        |
+----------------+

练习题

第六题：

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分：
这题很简单，不需要连表，只要课程id、最高分和最低分，用分组函数就能搞定：

select course_id 课程id,max(num) 最高分,min(num) 最低分
    from score
    group by course_id;

第七题：
查询至少有一门课与学号1所学课程相同的学生学号和姓名：

1.从socre表中查出学号1学的全部课程id：

select course_id
    from score
    where student_id=1;

2.再从score表中找含有学号1课程的记录，记得要去重而且要去掉学号1：

select  distinct student_id
    from score
    where student_id!=1 and course_id in(
        select course_id
            from score
            where student_id=1);

3.最后与student表拼接，显示学号和姓名，注意要right join(因为右表记录不能遗漏的)，另外可以加上order by学号更加美观：

select sid 学号,sname 姓名
    from student
    as a right join
        (select distinct student_id
            from score
            where student_id!=1 and course_id in(
                select course_id
                    from score
                    where student_id=1))
    as b on a.sid=b.student_id
    order by 学号;

第八题：
查询语文成绩比数学成绩好的学生学号及名字：

这道题这里讲一个新东西，用变量保存查询结果及调用变量保存的查询结果。请注意变量的用法——@变量名字：
1.首先查询course表中语文的课程id和数学课程的id：

select @course1:=cid  # 查询语文课的id，保存到变量course1中
    from course
    where cname='语文';

select @course2:=cid  # 查询数学课的id，保存到变量course2中
    from course
    where cname='数学';

2.做一个学号、语文课成绩、数学课成绩的连表：

select a.学号,a.课程1,b.课程2
    from(
        select student_id 学号,num 课程1
            from score
            where course_id=@course1) a
            inner join(    
                select student_id 学号,num 课程2
                    from score
                    where course_id=@course2) b
            on a.学号=b.学号
            where 课程1>课程2;

3.最后与student表拼接，显示学号和姓名，注意要right join(因为右表记录不能遗漏的)，另外可以加上order by学号更加美观：

select sid 学号,sname 姓名
    from student
    right join(
        select a.学号,a.课程1,b.课程2
            from(
                select student_id 学号,num 课程1
                    from score
                    where course_id=@course1) a
                    inner join(    
                        select student_id 学号,num 课程2
                            from score
                            where course_id=@course2) b
                    on a.学号=b.学号
                    where 课程1>课程2) c
            on student.sid=c.学号
            order by 学号;

注意：这一题运用了一个新技巧，临时变量保存临时查询结果。为了提高代码的复用率，在经常变更查询内容但不变更查询条件的情况下，用临时变量单独保存查询内容会很方便。另外就是如果发现某些查询的结果需要反复用到，那么可以先将该结果保存到临时变量中，这样可以减少查表次数从而提高查询效率。

第九题：
查平均成绩大于60分的学生学号、姓名、平均成绩，结果按成绩从高到低排序：

这一题写SQL代码难度不大，难的是要找出题中隐含条件。在这道题中要找出平均成绩大于60分的学生，但在数据库存在有些学生部分课程缺考的情况，缺考的课程应该按0计算！所以不能直接用avg(num)>60来作为having条件。
1.首先查course表得出总共有几门课程：

select @total:=count(*)*60  # 计算平均成绩大于60考分的总数
    from course;

2.再从score表查总成绩大于@total的学生，获取学号和平均成绩：

select student_id 学号,avg(num) 平均成绩
    from score
    group by student_id
    having sum(num)>@total
    order by 平均成绩 desc;

3.最后连接student表，显示学生学号、姓名、平均成绩：

select 学号,sname 姓名,平均成绩
    from student a
    inner join(
        select student_id 学号,avg(num) 平均成绩
            from score
            group by student_id
            having sum(num)>@total
            order by 平均成绩 desc) b
        on a.sid=b.学号;

第十题：
查询所有学生的学号、姓名、总成绩、课程数，按总成绩从高到低排序：

这一题写SQL代码难度不大，但还是有很多人会做错。请注意题目要求是查询所有学生的信息，连表的时候必须使用student左连接右查询子表，这样才能查到所有学生的信息！
请实际运行一下代码，就会发现有一个学生是没成绩的。假如用的是inner join那么答题结果就不会包含没成绩的学生。

select student.sid 学号,student.sname 姓名,b.总成绩,b.课程数
    from student
    left join
        (select student_id 学号,count(num) 课程数,sum(num) 总成绩
            from score
            group by student_id)
    as b on student.sid=b.学号
    order by 总成绩 desc;

第十一题：
查询没上过“江成”老师课的学生学号和姓名；

这一题有难度，要避开两个坑：一是要想到一名老师可能教多门课；二是要会用逆向思维，从score表中找出所有上过“江成”老师课的学生，然后再从studeng表中排除那些学生，剩下的就是没上过“江成”老师课的学生。
1.从teacher表中找出“江成”老师的id，再去course表中找出“江成”老师教的全部课程：

select cid
    from course
    where teacher_id=(
        select tid
            from teacher
            where tname='江成');

2.从score表中找出所有学过“江成”老师’的学生id，另外要记得用distinct去重：

select distinct student_id
    from score
    where course_id in (
        select cid
            from course
            where teacher_id=(
                select tid
                    from teacher
                    where tname='江成'));

3.从student表中找出所有不在第二步表中的学生：

select sid 学号,sname 姓名
    from student
    where sid not in(
        select distinct student_id
            from score
            where course_id in (
                select cid
                    from course
                    where teacher_id=(
                        select tid
                            from teacher
                            where tname='江成')));

第十二题：
查询学过“江成“老师所有课程的学生学号和姓名：

这一题比上一题还要难，解题思路是首先要找出这位老师教的所有课程(可能不止一门课)，其次计算这位老师教了几门课；然后找出学过这位老师课程的学生另外排除掉课程数量不足的学生；最后再拼接学生表列出学号和姓名。
1.找出“江成”老师教的全部课程：

select cid
    from course
    where teacher_id=(
        select tid
            from teacher
            where tname='江成');

2.算出“江成”老师教几门课：

select @total:=count(*)
    from course
    where teacher_id=(
        select tid
            from teacher
            where tname='江成');

3.找出学过“江成”老师课的学生，只保留成绩数量等于老师教的课程数的学生：

select student_id
    from score c
    inner join(
        select cid
            from course a
            inner join(
                select tid
                    from teacher
                    where tname='江成') b
            on a.teacher_id=b.tid) d
    on c.course_id=d.cid
    group by student_id
    having count(student_id)=@total;

4.再将第三步的表与student表拼接，最终显示学过“江成“老师所有课程的学生学号和姓名：

select sid 学号,sname 姓名
    from student e
    inner join(
        select student_id
            from score c
            inner join(
                select cid
                    from course a
                    inner join(
                        select tid
                            from teacher
                            where tname='江成') b
                    on a.teacher_id=b.tid) d
            on c.course_id=d.cid
            group by student_id
            having count(student_id)=@total) f
    on e.sid=f.student_id
    order by 学号;