【MySQL】练习题解答（一）

 

题目列表	https://blog.csdn.net/sinat_21791203/article/details/115819065
参考答案	https://www.cnblogs.com/wupeiqi/articles/5748496.html
讲解视频	https://www.bilibili.com/video/BV1DE411n7fU?t=14&p=29

 

预热题：

成绩大于60分的

select * from score where number>60;

每个老师任课的个数，在课程表中查

select teacher_id,count(cname) from course group by teacher_id;

列出课程表中的课程名和任课老师的名字

select cname,tname from course left join teacher on course.teacher_id=teacher.tid;

列出学生和其所在的班级名称

select sname,caption from student left join class on  student.class_id=class.cid;

求学生表中男生和女生的个数

select gender,count(gender) from student group by gender;

select gender,count(sid) from student group by gender;

 

临时表：在内存中暂时存在而不存在与内存中的表

比如下面，成绩大于60数据，作为表B，这个表B就是一个临时表

(select * from score where number>60) as B

select sid from (select * from score where number>60) as B;

 

1.

创建表的顺序：优先创建无外键的表

班级表class、老师表teacher、学生表student、课程表course、成绩表score

2.x

select student_id from score left join course on score.course_id = course.cid

3.v

select student_id, avg(num) from score group by student_id having avg(num) > 60;

如果同时要显示出学生的姓名

解法一：先将score和student表进行左连接之后再查询操作

select student_id,student.sname,avg(num) from score left join student on score.student_id=student.sid group by student_id having avg(num)>60;

解法二：

（1）查找出临时表

（2）将临时表和student表进行左连接获取结果

select 
    B.student_id,student.sname,B.avg_num 
from 
    (select 
        student_id,avg(num) as avg_num 
    from 
        score 
    group by 
        student_id 
    having 
        avg_num >60) 
    as B 
left join 
    student 
on 
    B.student_id=student.sid;

思考：这两种写法在查询效率上会有什么区别？

4.

思路：根据题意首先要连接score表和student表，需要查询选课数和总成绩，这里需要进行分组，在查询选课数时按照学生id进行分组，使用count计数，在查询总成绩时使用sum求得总和

为什么不用学生姓名？考虑姓名可能会重名

select score.student_id,student.sname,count(student_id),sum(num) from score left join student on score.student_id=student.sid group by student_id;

连表查询的时候记得想清楚应该以那张表为主表

5.

select count(tid) from teacher where tname like '李%' group by tname;   错误答案，在思考的时候，没梳理明白什么时候才需要用到group by

select count(tid) from teacher where tname like '李%'; 正确答案

思考：什么情况下才需要用到分组group by？

6.x

思路：连接course与teacher表，用where查找李平老师任教的课程id

select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname='李平老师';

学生和课程的关系在score表中，已经查出来李平老师任教的课程id是2和4：：

 select * from score where course_id not in (2,4);

将上述查询过程写在一个SQL语句中，in中可以加SQL语句

select * from score where course_id not in (select cid from course left join teacher on course.teacher_id=teacher.tid where tname='李平老师');

要获取到学生姓名则将上述表与student连表即可

select student.sid,student.sname from student left join (select * from score where course_id not in (select cid from course left join teacher on course.teacher_id=teacher.tid where tname='李平老师')) as B on B.student_id=student.sid group by student.sid;

在连接course与teacher表，用where查找李平老师任教的课程id时，按照student_id进行分组操作

只取student_id，此时获取到的是：没有选李平老师课程的所有学生的id

 

思路梳理：

-李平老师任课的课程id可以获取到2,4

-题目要获取没有选过他的课程的所有人

-目前思路是在score表中找，获取到没有选李平老师课程的学生

-注：比如方少伟选择了李平老师的课程，同时选了 老师a、老师b以及老师c的课程，由秦兵只选择了李平老师的课程2和4

取没有选过李平课程的学生，此时会将方少伟取出来，这种筛选出来有问题？？？？？？我没理解。。。

====================================================上面解题思路有问题=================================================

转换思路：

找学过李平老师课程的同学，方少伟1门和由秦兵2门

 

score表中所有选过李平老师课程的人如下：

对student_id进行group by，就是所有选过李平老师课程的 学生 id

select student_id from score where course_id in (

    select

        course.cid

    from

        course

    left join teacher on course.teacher_id = teacher.tid

    where

        teacher.tname = '李平老师'

)

要找没选过的id，那么只需要找学生id不在上面查询到 的id里面即可

思路：去student表 中取id不在上述查询结果中的id即为：没有选过李平老师课程的学生id

 

7.

10.v

select score.student_id,student.sname from score left join student on score.student_id=student.sid where num<60 group by student_id;

22.v

select course.cid,course.cname,count(cid) from student left join course on course.cid=student.class_id group by cid;

23.

select student.sid,student.sname from student where sid=(select student_id from score group by student_id having count(student_id)=1);

解题思路：

（1）先在score表中按照学生id分组使用聚合函数count，随后利用having进行条件查找，找出只有一门课成绩的学生id；

（2）在student表中找出学生id 等于上面id的学生id和姓名

24.v

select gender,count(gender) from student group by gender;

25.v

select sname from student where sname like '张%';

28.

select student_id,student.sname,B.avg_num from (select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 85) as B left join student on B.student_id=student.sid;

29.

select student.sname,num from (select student_id,num from score where score.course_id=(select cid from course where cname='生物') and num <60) as B left join student on B.student_id=student.sid;

注：表中没有数学，用生物代替了，其余查询条件不变

30.

select student_id,sname from score left join student on score.student_id=student.sid where course_id=3 and num>80;

32.为啥我感觉这一题，对 题目理解不同，答案不同

比如要考虑的几点：

（1）如果杨艳老师带了两门或两门以上课程，也是只选一个成绩最高的吗？

（2）如果有好多个学生在这几门学科中都拿到了100分，只取第一行的学生信息吗？

（3）但是要注意，在筛选course_id的时候要用条件in，因为如上所述，一位老师有可能会带多门课程

 

33.

select course_id,count(course_id) from score group by course_id;

34.

35.

36.

37.

38.

39.

40.

select student_id from score where course_id=4 and num >60 order by num desc;

注：这里分数小于60的只有一个人，不好查看结果，所以我改成了大于 60，便于理解

41.