一道MySql面试题-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_50514173/article/details/128825578

文章讲述了如何使用SQL查询解决三个问题：1)找出没选过黎明老师的所有学生；2)列出选修2门以上不及格课程的学生及其平均成绩；3)查询选修1号和2号课程的所有学生。解答涉及到了多表连接，子查询以及条件筛选等SQL技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近自学中遇到了一道面试题，不知道写的对不对，求各位大佬看一下，给点意见

有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
+------+--------+
| SNO | SNAME |
+------+--------+
| 1 | 学生 1 |
| 2 | 学生 2 |
| 3 | 学生 3 |
| 4 | 学生 4 |
+------+--------+

C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

+------+-------+----------+
| CNO | CNAME | CTEACHER |
+------+-------+----------+
| 1 | 语文 | 张 |
| 2 | 政治 | 王 |
| 3 | 英语 | 李 |
| 4 | 数学 | 赵 |
| 5 | 物理 | 黎明 |
+------+-------+----------+

SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

+------+------+---------+
| SNO | CNO | SCGRADE |
+------+------+---------+
| 1 | 1 | 40 |
| 1 | 2 | 30 |
| 1 | 3 | 20 |
| 1 | 4 | 80 |
| 1 | 5 | 60 |
| 2 | 1 | 60 |
| 2 | 2 | 60 |
| 2 | 3 | 60 |
| 2 | 4 | 60 |
| 2 | 5 | 40 |
| 3 | 1 | 60 |
| 3 | 3 | 80 |
+------+------+---------+
问题1：找出没选过“黎明”老师的所有学生姓名。

实现思路：
首先可以先查询出所有学生所选修的课程
select s.sname ,sc.cno from s join sc on s.sno = sc.sno;
+--------+------+
| sname | cno |
+--------+------+
| 学生 1 | 1 |
| 学生 1 | 2 |
| 学生 1 | 3 |
| 学生 1 | 4 |
| 学生 1 | 5 |
| 学生 2 | 1 |
| 学生 2 | 2 |
| 学生 2 | 3 |
| 学生 2 | 4 |
| 学生 2 | 5 |
| 学生 3 | 1 |
| 学生 3 | 3 |
+--------+------+

然后再将上述表作为一张临时表，和c表进行等值连接，来查询出对应的任课老师
select t.sname,c.cteacher from c
join (select s.sname ,sc.cno from s join sc on s.sno = sc.sno) t
on t.cno = c.cno;
+--------+----------+
| sname | cteacher |
+--------+----------+
| 学生 3 | 张 |
| 学生 2 | 张 |
| 学生 1 | 张 |
| 学生 2 | 王 |
| 学生 1 | 王 |
| 学生 3 | 李 |
| 学生 2 | 李 |
| 学生 1 | 李 |
| 学生 2 | 赵 |
| 学生 1 | 赵 |
| 学生 2 | 黎明 |
| 学生 1 | 黎明 |
+--------+----------+

其次将上述表作为一张临时表，查询出选修课程中老师是黎明的学生
select t1.sname from ( select t.sname,c.cteacher from c
join (select s.sname ,sc.cno from s join sc on s.sno = sc.sno) t
on t.cno = c.cno) t1 where t1.cteacher like '%黎明%';

+--------+
| sname |
+--------+
| 学生 1 |
| 学生 2 |
+--------+

最后一步就是查询出没选过黎明老师的学生，
上一步已经将选了黎明老师的学生查询出来了
那么只要在s表中查询所有学生名字，但是不在上面范围内就可以了

select s.sname from s where s.sname not in (select t1.sname from ( select t.sname,c.cteacher from c
join (select s.sname ,sc.cno from s join sc on s.sno = sc.sno) t
on t.cno = c.cno) t1 where t1.cteacher like '%黎明%');

+--------+
| sname |
+--------+
| 学生 3 |
| 学生 4 |
+--------+


问题2:列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
第一小问：
       实现思路：
           第一步：首先通过多表连接来查询出每个学生没有及格的课程成绩

select s.sname,sc.scgrade from s join sc on s.sno = sc.sno where sc.scgrade<60;
               +--------+---------+
               | sname | scgrade |
               +--------+---------+
               | 学生 1 | 20 |
               | 学生 1 | 30 |
               | 学生 1 | 40 |
               | 学生 2 | 40 |
               +--------+---------+
           第二步：将上述查询的结果当作一张临时表，然后统计每个学生不及格的科目数量
               select
                   t.sname,count(t.scgrade) as num
               from
                   ( select s.sname,sc.scgrade from s join sc on s.sno = sc.sno where sc.scgrade<60) t
               group by
                   t.sname;
               +--------+------------------+
               | sname | num   |
               +--------+------------------+
               | 学生 1 | 3 |
               | 学生 2 | 1 |
               +--------+------------------+
           第三步：然后再将上述查询结果作为一张临时表，然后通过该临时表筛选出未及格课程数量大于等于2的学生
               select t1.sname from (select
                   t.sname,count(t.scgrade) as num
               from
                   ( select s.sname,sc.scgrade from s join sc on s.sno = sc.sno where sc.scgrade<60) t
                       group by
                           t.sname) t1
               where t1.num>=2;

                   +--------+
                   | sname |
                   +--------+
                   | 学生 1 |
                   +--------+

   第二小问：
       分析：根据题意，也就是查询出未及格课程数量大于等于2的学生所有课程的平均成绩
       实现思路：
           第一步：首先查询出该学生的所有课程成绩，以上述查询出的结果作为一个筛选条件

               select s.sname ,sc.scgrade from s join sc on s.sno = sc.sno where s.sname =(select t1.sname from (select
                   t.sname,count(t.scgrade) as num
               from
                   ( select s.sname,sc.scgrade from s join sc on s.sno = sc.sno where sc.scgrade<60) t
                       group by
                           t.sname) t1
               where t1.num>=2);

               +--------+---------+
               | sname | scgrade |
               +--------+---------+
               | 学生 1 | 40 |
               | 学生 1 | 30 |
               | 学生 1 | 20 |
               | 学生 1 | 80 |
               | 学生 1 | 60 |
               +--------+---------

第二步：将上述查询结果作为一个临时表，然后直接求出该学生的所有课程平均成绩
select

t2.sname ,avg(t2.scgrade) as avgGrade

from

                                (select s.sname ,sc.scgrade from s join sc on s.sno = sc.sno where s.sname                        =(select t1.sname from (select
                   t.sname,count(t.scgrade) as num
                        from ( select s.sname,sc.scgrade from s join sc on s.sno = sc.sno where                                 sc.scgrade<60) t
                       group by
                           t.sname) t1
               where t1.num>=2)) t2;

               +--------+----------+
               | sname | avgGrade |
               +--------+----------+
               | 学生 1 | 46 |
               +--------+----------+

问题3：查询出既选修1号课程和2号课程的所有学生的姓名

   解题思路：
   第一步：可以先查询出所有学生选修了哪些课程，将该查询结果当作一个临时表

select s.sname,sc.cno from s join sc on s.sno = sc.sno order by s.sname;

第二步：筛选思路：筛选上面临时表，首先可以先查询出选修了课程1的所有学生，
然后再查询出选修了课程2的所有学生，但是题目要求查询出既选修了课程1又选修了课程2的学生
那么只需要看两个查询结果中都出现的学生姓名，这时需要进行等值连接，用姓名字段来进行等值连接

select t1.sname from (select t.sname from (select s.sname,sc.cno from s join sc on s.sno = sc.sno order by s.sname) t
where t.cno = 1) t1
join ( select t.sname from (select s.sname,sc.cno from s join sc on s.sno = sc.sno order by s.sname) t
where t.cno =2) t2
on t1.sname = t2.sname;

查询结果为：

+--------+
| sname |
+--------+
| 学生 1 |
| 学生 2 |
+--------+