专栏首页机器学习/数据可视化MySQL经典50题:面试必备
原创

MySQL经典50题:面试必备

标题

MySQL经典50题解析及答案

作者

Peter

微信

756803877

公众号

尤而小屋

时间

2021-09-02

MySQL经典50题解析及答案

下面是网传经典的MySQL50题的习题及参考答案💪,供参考和学习,有更好的方法或者不恰当的地方,欢迎提出来

<!--MORE-->

题目1

题目要求

查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SQL实现

 -- 方法1
 select
   a.*
   ,b.s_score as 1_score
   ,c.s_score as 2_score
 from Student a
 left join Score b on a.s_id = b.s_id  and b.c_id = '01'   -- 两个表通过学号连接,指定01
 left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02,或者c中的c_id直接不存在
 -- 为NULL的条件可以不存在,因为左连接中会直接排除c表中不存在的数据,包含NULL
 where b.s_score > c.s_score;   -- 判断条件
 ​
 ​
 -- 方法2:直接使用where语句
 select
   a.*
   ,b.s_score as 1_score
   ,c.s_score as 2_score
 from Student a, Score b, Score c
 where a.s_id=b.s_id   -- 列出全部的条件
 and a.s_id=c.s_id
 and b.c_id='01'
 and c.c_id='02'
 and b.s_score > c.s_score;   -- 前者成绩高
 -- 方法1:通过连接方式实现
 select
   a.*
   ,b.s_score as 1_score
   ,c.s_score as 2_score
 from Student a
 left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL   -- 包含NULL的数据
 join score c on a.s_id=c.s_id and c.c_id='02'
 where b.s_score < c.s_score;
 ​
 -- 通过where子句实现
 select
   a.*
   ,b.s_score as 1_score
   ,c.s_score as 2_score
 from Student a, Score b, Score c
 where a.s_id=b.s_id
 and a.s_id=c.s_id
 and b.c_id='01'
 and c.c_id='02'
 and b.s_score < c.s_score;   -- 前者比较小
 -- 执行顺序:先执行分组,再执行avg平均操作
 select
   b.s_id
   ,b.s_name
   ,round(avg(a.s_score), 2) as avg_score
 from Student b
 join Score a
 on b.s_id = a.s_id
 group by b.s_id   -- 分组之后查询每个人的平均成绩
 having avg_score >= 60;
 ​
 -- 附加题:总分超过200分的同学
 select
   b.s_id
   ,b.s_name
   ,round(sum(a.s_score),2) as sum_score    -- sum求和
 from Student b
 join Score a
 on b.s_id=a.s_id
 group by b.s_id
 having sum_score > 200;
 select
   b.s_id
   ,b.s_name
   ,round(avg(a.s_score), 2) as avg_score   -- round四舍五入函数
 from Student b
 join Score a
 on b.s_id = a.s_id
 group by b.s_id   -- 分组之后查询每个人的平均成绩
 having avg_score < 60;
 select
   a.s_id
   ,a.s_name
   ,0 as avg_score
 from Student a
 where a.s_id not in (  -- 学生的学号不在给给定表的学号中
   select distinct s_id -- 查询出全部的学号
   from Score
 );
 select
   S.s_id
   ,S.s_name
   ,round(avg(ifnull(C.s_score,0)), 2) as avg_score   -- ifnull 函数:第一个参数存在则取它本身,不存在取第二个值0
 from Student S
 left join Score C
 on S.s_id = C.s_id
 group by s_id
 having avg_score < 60;
 select
   a.s_id
   ,a.s_name
   ,ROUND(AVG(b.s_score), 2) as avg_score
 from Student a
 left join Score b on a.s_id = b.s_id
 GROUP BY a.s_id
 HAVING avg_score < 60 or avg_score is null;   -- 最后的NULL判断
 select
   a.s_id
   ,a.s_name
   ,count(b.c_id) as course_number   -- 课程个数
   ,sum(b.s_score) as scores_sum  -- 成绩总和
 from Student a
 left join Score b
 on a.s_id = b.s_id
 group by a.s_id,a.s_name;
  1. 先从Score表中看看哪些人是满足要求的:01-05同学是满足的

通过自连接查询的语句如下:

查询出学号后再匹配出学生信息:

通过where语句实现:

方法3的实现:

题目10 题目需求 查询学过01课程,但是没有学过02课程的学生信息(注意和上面👆题目的区别) SQL实现 首先看看哪些同学是满足要求的:只有06号同学是满足的

错误思路1 直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08 select s1.* from Student s1 where s_id not in ( -- 直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08 select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01' and s3.c_id ='02' );

错误思路2 将上面题目中的02课程直接取反,导致同时修过01,02,03或者只修01,03的同学也会出现 select s1.* from Student s1 where s_id in ( select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01' and s3.c_id !='02' -- 直接取反是不行的,因为修改(01,02,03)的同学也会出现 );

正确思路 https://www.jianshu.com/p/9abffdd334fa -- 方法1:根据两种修课情况来判断 select s1.* from Student s1 where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程,要保留 and s1.s_id not in (select s_id from Score where c_id='02'); -- 哪些人修过02,需要排除

!!!!!方法2:先把06号学生找出来 select * from Student where s_id in ( select s_id from Score where c_id='01' -- 修过01课程的学号 and s_id not in (select s_id -- 同时学号不能在修过02课程中出现 from Score where c_id='02') );

题目11 题目需求 查询没有学完全部课程同学的信息 SQL实现 -- 自己的方法 select * -- 排除学号后得到的结果 from Student where s_id not in (select s_id from (select s_id, count(s_id) as number -- 3.最大课程数所在的学号需要排除 from Score group by s_id) s -- 取别名 where number=(select max(number) -- 2.保证最大的课程数 from( select s_id, count(s_id) as number -- 1.学号和个数统计(即修了几门课) from Score group by s_id)t)); -- 别名

自己的方法一开始在课程的最大数中没有使用Course表,导致多使用了一个临时表的结果,现在改成使用Course表的统计值(3)作为课程的总数: select s.* from Student s where s_id not in ( select s_id from Score s1 group by s_id having count(*) = (select count(*) from Course) );

-- 方法2:having select s.* from Student s -- 学生表 left join Score s1 -- 成绩表 on s1.s_id = s.s_id group by s.s_id -- 学号分组 having count(s1.c_id) < ( -- 分组后学生的课程数<3 select count(*) from Course -- 全部课程数=3 ) 题目12 题目需求 查询至少有一门课与学号为01的同学所学相同的同学的信息 SQL实现 首先看看结果的:因为01号同学修了全部课程,所以其他的同学都是满足要求,除了08号同学没有任何成绩,不符合

具体实现过程为: select * -- 3、求出学生信息 from Student where s_id in ( select distinct s_id -- 2、找出满足课程在01学生课程中的全部学号(学生),学号去重,同时将01自己排除 from Score where c_id in ( select c_id from Score where s_id=01 -- 1、找出学号01同学的全部课程 ) and s_id != 01);

-- 方法2 select s1.* from Student s1 join Score s2 on s1.s_id = s2.s_id -- 学生表和成绩表的关联 and c_id in (select c_id from Score where s_id=01) -- 对课程进行限制,只在01学生的课程内 group by s1.s_id; -- 根据学号分组

题目13 题目需求 查询和01同学学习的课程完全相同的同学的信息 SQL实现

  1. 自己的方法

select * from Student where s_id in (select s_id -- 3、步骤2中得到的学号是满足要求的 from(select distinct(s_id), count(c_id) number from Score group by s_id)t1 -- 1、学号和所修课程分组的结果t1 where number=3 -- 2、投机:选择出所修课程数是3(01修了3门)的学号 and s_id !=01 -- 01 本身排除 );

我们在上面的步骤2中不考虑直接指定3(where number=3),而是用01学生所修的课程数(虽然也是3)来代替: select * from Student where s_id in( select s_id -- 3、步骤2中得到的学号是满足要求的 from(select distinct(s_id), count(c_id) number from Score group by s_id)t1 -- 1、学号和所修课程分组的结果t1 where number=(select count(c_id) number from Score group by s_id having s_id=01) -- 2、改变的地方:使用学号01的课程数3来代替 and s_id !=01 -- 01 本身排除 );

  1. 使用group_concat函数

group_concat的使用方法为: group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) 我们将Score表中每个s_idc_id进行分组合并,实际的效果如下: select s_id ,group_concat(c_id order by c_id) -- 分组合并,同时排序 from Score group by s_id;

需要进行排序的原因是防止出现这种情况:01修的课程顺序是:01,02,03;如果有同学修课的顺序是02,03,01,虽然顺序不同,但是本质上他们修的课程是相同的 使用排序后都会变成:01,02,03,保证结果相同 那么之后,我们只需要判断合并后和01号同学相同的结果即可,取出学号: select * -- 3、查询信息 from Student where s_id in( select s_id from Score group by s_id having group_concat(c_id order by c_id)=( -- 2、找出和01号学生分组合并结果相同的学号s_id;也需要排序 select group_concat(c_id order by c_id) -- 1、找出01号学生分组合并的结果,同时排序;排序很重要 from Score group by s_id having s_id=01) and s_id != 01 -- 将自身排除 );

题目14 题目需求 查询没有修过张三老师讲授的任何一门课程的学生姓名 SQL实现 自己的方法,具体过程如下: select s_name -- 4、学号取反找到学生姓名 from Student where s_id not in( select distinct(s_id) -- 3、课程号找到对应的学号 from Score where c_id=( select c_id -- 2、教师编号找到对应的课程号 from Course where t_id=( select t_id -- 1、姓名找到教师编号 from Teacher where t_name='张三') ));

-- 修过张老师课程的学生的学号 select distinct(s_id) from Score where c_id=( select c_id from Course where t_id=( select t_id from Teacher where t_name='张三')); -- 修过张三老师课的学生

题目15 题目需求 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 SQL实现 首先看看哪些同学是满足两门或者两门以上是及格的

-- 2门及以上不及格的 select s_id ,round(avg(s_score)) avg_score from Score where s_score < 60 -- 小于60分,不及格 group by s_id having count(s_score) >= 2; -- 不及格的有2门以上 说明04,06是我们最终想要的结果

-- 自己的方法 select s.s_id ,s.s_name ,t.avg_score from Student s join (select s_id ,round(avg(s_score)) avg_score from Score where s_score < 60 group by s_id having count(s_score) >= 2)t on s.s_id=t.s_id

-- 参考方法1 select a.s_id, a.s_name, ROUND(AVG(b.s_score)) from Student a left join Score b on a.s_id = b.s_id where a.s_id in( select s_id from Score where s_score<60 group by s_id having count(1)>=2) group by a.s_id,a.s_name -- 参考方法2 select s.s_id ,s_name ,round(avg(s_score), 2) avg_score from Student s join Score sc on s.s_id=sc.s_id and sc.s_score < 60 -- 不及格 group by s.s_id -- 学号分组 having count(sc.c_id )>= 2; -- 2门课 改进点 上面的两种方法都没有考虑都08学生,3门都没有成绩,这个本题需要改进的地方。 题目16 题目需求 检索01课程分数小于60,按分数降序排列的学生信息 SQL实现 自己的方法如下: 首先从Score表中找出哪些学生是满足这个要求: select s_id ,s_score from Score where s_score < 60 and c_id = 01;

然后直接将上面的结果和Student表查询: select s.* from Student s where s.s_id in ( select s_id ,s_score from Score where s_score < 60 and c_id = 01 );

select s.* ,t.s_score from Student s join (select s_id,s_score -- 2、Student和t的连接查询 from Score where s_score < 60 and c_id = 01 )t -- 1、将第一步结果作为中间表t on s.s_id=t.s_id;

-- 自己的方法2:两个表的直接连接查询+where条件 select s.* ,sc.s_score from Student s join Score sc on s.s_id=sc.s_id where sc.c_id=01 and sc.s_score < 60 order by sc.s_score desc; -- 默认就是降序desc

题目17 题目需求 按平均成绩从高到低(降序)显示所有学生所有课程的成绩以及平均成绩 SQL实现 下面是自己的解法: 1、先求出每个同学的平均分,并降序排列 select s_id ,round(avg(s_score),2) avg_score from Score group by s_id order by 2 desc;

-- 自己的方法 select s.s_id ,s.c_id ,s.s_score ,t.avg_score from Score s join (select s_id ,round(avg(s_score),2) avg_score from Score group by s_id)t on s.s_id = t.s_id order by 4 desc; -- 指的是第4个字段

-- 参考方法1 select s.s_id ,(select s_score from Score where s_id=s.s_id and c_id='01') as 语文 ,(select s_score from Score where s_id=s.s_id and c_id='02') as 数学 ,(select s_score from Score where s_id=s.s_id and c_id='03') as 英语 ,round(avg(s_score),2) 平均分 from Score s group by s.s_id order by 5 desc;

select s.s_id ,max(case s.c_id when '01' then s.s_score end) 语文 ,max(case s.c_id when '02' then s.s_score end) 数学 ,max(case s.c_id when '03' then s.s_score end) 英语 ,avg(s.s_score) ,b.s_name -- 没有出现在group by子句中,导致报错 join Student b on s.s_id = b.s_id group by s.s_id order by 5 desc; 严格模式的报错: ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.b.s_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。 -- 参考方法2:将上面的b.s_name去掉 select s.s_id ,max(case s.c_id when '01' then s.s_score end) 语文 ,max(case s.c_id when '02' then s.s_score end) 数学 ,max(case s.c_id when '03' then s.s_score end) 英语 ,round(avg(s.s_score),2) avg_score from Score s join Student b on s.s_id = b.s_id group by s.s_id order by 5 desc;

题目18 题目需求 查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90 SQL实现 思路清晰:统计每个阶段的总人数,再除以总共的人数即可 将成绩表和课程表联合起来进行查询:

  • case 语句用于对每个分数贴标签
  • sum 语句对相应的语句中的1进行求和

select s.c_id ,c.c_name ,max(s.s_score) ,min(s.s_score) ,round(avg(s.s_score), 2) ,round(100 * (sum(case when s.s_score >= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 及格率 ,round(100 * (sum(case when s.s_score >= 70 and s.s_score <= 80 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 中等率 ,round(100 * (sum(case when s.s_score >= 80 and s.s_score <= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优良率 ,round(100 * (sum(case when s.s_score >= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优秀率 from Score s left join Course c on s.c_id = c.c_id group by s.c_id, c.c_name;

题目19 题目需求 按照各科成绩进行排序,并且显示排名 分析过程 题目的意思是:将每科的成绩单独进行排名,类似如下的效果: 课程名分数排名英语991英语922英语893数学881数学852……………...SQL实现 第一步:我们对Score表中的一门课程进行排名,比如01课程 select * from( select t1.c_id -- 课程号 ,t1.s_score -- 分数 ,(select count(distinct t2.s_score) -- 课程去重 from Score t2 where t2.s_score >= t1.s_score -- SQL实现排序 and t2.c_id = '01') rank from Score t1 -- 通过相同的表实现自连接 where t1.c_id = '01' order by t1.s_score desc )t1 上面是针对01课程,结果为:

第二步:我们将01、02、03课程全部连接起来,通过union实现

  • 表的自连接
  • SQL实现排序

-- 自己的方法 select * from( select t1.c_id -- 课程号 ,t1.s_score -- 分数 ,(select count(distinct t2.s_score) -- 课程去重 from Score t2 where t2.s_score >= t1.s_score -- SQL实现排序 and t2.c_id = '01') rank from Score t1 -- 通过相同的表实现自连接 where t1.c_id = '01' order by t1.s_score desc )t1 union select * from( select t1.c_id -- 课程号 ,t1.s_score -- 分数 ,(select count(distinct t2.s_score) -- 课程去重 from Score t2 where t2.s_score >= t1.s_score -- SQL实现排序 and t2.c_id = '02') rank from Score t1 -- 通过相同的表实现自连接 where t1.c_id = '02' order by t1.s_score desc )t2 union select * from( select t1.c_id -- 课程号 ,t1.s_score -- 分数 ,(select count(distinct t2.s_score) from Score t2 where t2.s_score >= t1.s_score and t2.c_id = '03') rank from Score t1 where t1.c_id = '03' order by t1.s_score desc )t3;

-- 参考代码 select * from (select t1.c_id, t1.s_score, (select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='01') rank from Score t1 where t1.c_id='01' order by t1.s_score desc) t1 union select * from (select t1.c_id ,t1.s_score ,(select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='02') rank from Score t1 where t1.c_id='02' order by t1.s_score desc) t2 union select * from (select t1.c_id, t1.s_score, (select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank from Score t1 where t1.c_id='03' order by t1.s_score desc) t3 题目20 题目需求 查询学生的总成绩,并进行排名 SQL实现 1、先查询每个学生的总成绩 select s_id ,sum(s_score) from Score group by s_id order by 2 desc;

将上面的结果和学生信息表进行关联查询: -- select s.s_name ,s.s_id ,t.score from Student s join(select s_id ,sum(s_score) score from Score group by s_id order by 2 desc )t on s.s_id = t.s_id;

-- 不使用中间表查询 select s.s_id ,s.s_name ,sum(sc.s_score) score from Student s join Score sc on s.s_id = sc.s_id group by s.s_id order by 3 desc;

如果想给排名加上一个排序号,参考之前的文章 -- 加上排序号 select t1.s_id ,t1.s_name, t1.score ,(select count(t2.score) from(select s.s_id, s.s_name, sum(sc.s_score) score from Student s join Score sc on s.s_id = sc.s_id group by s.s_id order by 3 desc)t2 -- t2和t1相同 where t2.score > t1.score) + 1 as rank from( select s.s_id ,s.s_name ,sum(sc.s_score) score from Student s join Score sc on s.s_id = sc.s_id group by s.s_id order by 3 desc)t1 -- t1 order by 3 desc;

题目21 题目需求 查询不同老师所教不同课程平均分从高到低显示 SQL实现 先找出每个老师教授了哪些课程: select c.c_name ,t.t_name from Course c left join Teacher t on c.t_id = t.t_id;

将上面的结果和成绩表连接起来: select c.c_name ,t.t_name ,round(avg(s.s_score),2) score -- 课程分组后再求均值 from Course c -- 主表,通过两次连接 left join Teacher t on c.t_id = t.t_id left join Score s on c.c_id = s.c_id group by c.c_id -- 课程分组 order by 3 desc; -- 降序

题目22 题目需求 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 SQL实现 自己的方法 1、课程表和成绩表连接起来,显示所有的课程和成绩信息 select s.s_id ,s.c_id ,s.s_score ,c.c_name from Score s join Course c on s.c_id = c.c_id

2、查出全部的语文成绩 select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc;

3、我们找出语文的第2、3的学生 select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc limit 1, 2;

4、同时求出语文、数学、英语的分数,并且通过union拼接 -- union连接 (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc limit 1, 2) union (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '数学' order by s.s_score desc limit 1, 2) union ((select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '英语' order by s.s_score desc limit 1, 2))

5、将上面的结果学生信息表进行连接即可 好歹是实现了😭 -- 最终脚本 -- !!!!真的需要好好优化下 select s.s_id ,s.s_name ,t.c_name ,t.s_score from Student s join (-- union连接 (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc limit 1, 2) union (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '数学' order by s.s_score desc limit 1, 2) union ((select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '英语' order by s.s_score desc limit 1, 2)))t -- 临时表t on s.s_id = t.s_id

和第25题相同的方法 1、以语文为例,首先我们找出前3名的成绩(包含相同的成绩) -- 语文 select a.s_id ,a.c_id ,a.s_score -- 3、此时a表的成绩就是我们找的 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号 and a.c_id="01" group by 1,2 having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形 order by 3 desc limit 1,2

-- 语文 select a.s_id ,a.c_id ,a.s_score -- 3、此时a表的成绩就是我们找的 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号 and a.c_id="01" group by 1,2 having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形 order by 3 desc limit 1,2; -- 取得第2、3名 在通过数学和英语的类似操作得到2、3名的成绩,再进行拼接即可 题目23 题目需求 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 SQL实现 自己的方法 1、如何对每个成绩进行分组展示:ABCD代表相应的等级 select c_id ,s_score ,case when s_score >= 85 and s_score<= 100 then 'A' -- 大小关系必须分两次写,一次写的话MySQL无法识别 when 70 <= s_score and s_score < 85 then 'B' when 60 <= s_score and s_score < 70 then 'C' when 0 <= s_score and s_score < 60 then 'D' else '其他' end as 'category' from Score s;

2、将两个表关联起来展示数据 -- 1、查看全部课程和成绩信息 select s.c_id ,c.c_name ,s.s_score ,case when s.s_score >= 85 and s.s_score<= 100 then 'A' -- 大小关系必须分两次写,一次写的话MySQL无法识别 when 70 <= s.s_score and s.s_score < 85 then 'B' when 60 <= s.s_score and s.s_score < 70 then 'C' when 0 <= s.s_score and s.s_score < 60 then 'D' else '其他' end as 'category' from Score s join Course c on s.c_id = c.c_id;

3、完整代码 select s.c_id 编号 ,c.c_name 科目 ,sum(case when s.s_score >= 85 and s.s_score<= 100 then 1 else 0 end) "[85,100]人数" ,round(100 * (sum(case when s.s_score >= 85 and s.s_score<= 100 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[85,100]百分比' ,sum(case when s.s_score >= 70 and s.s_score<= 85 then 1 else 0 end) "[70,85]人数" ,round(100 * (sum(case when s.s_score >= 70 and s.s_score<= 85 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[70,85]百分比' ,sum(case when s.s_score >= 60 and s.s_score<= 70 then 1 else 0 end) "[60,70]人数" ,round(100 * (sum(case when s.s_score >= 60 and s.s_score<= 70 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[60,70]百分比' ,sum(case when s.s_score >= 0 and s.s_score<= 60 then 1 else 0 end) "[0,60]人数" ,round(100 * (sum(case when s.s_score >= 0 and s.s_score<= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[0,60]百分比' from Score s left join Course c on s.c_id = c.c_id group by s.c_id, c.c_name

参考方法 1、先统计每个阶段的人数和占比 select c_id ,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100' ,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '占比' from Score group by c_id; -- 分课程统计总数和占比 -- 方式2 select c_id ,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100' ,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(case when s_score then 1 else 0 end)), 2) '占比' -- 不同count(*) from Score group by c_id;

注意对比:

2、我们将4种情况同时查出来 select c_id ,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100' ,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比' ,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85' ,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比' ,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70' ,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比' ,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60' ,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比' from Score group by c_id; -- 分课程统计总数和占比

3、将科目名称连接起来 -- 整体和自己的方法是类似的 select s.c_id ,c.c_name ,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100' ,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比' ,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85' ,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比' ,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70' ,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比' ,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60' ,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比' from Score s left join Course c on s.c_id = c.c_id group by s.c_id, c.c_name; -- 分课程统计总数和占比

题目24 题目需求 查询学生的平均成绩及名次 SQL实现 自己的方法 1、先求出每个人的平均分 -- 自己的方法 select sc.s_id ,s.s_name ,round(avg(sc.s_score),2) avg_score from Score sc join Student s on sc.s_id=s.s_id group by sc.s_id,s.s_name

2、我们对上面的结果进行排序 !!!MySQL5中是没有rank函数的,需要自己实现排序功能 -- MYSQL5.7中没有rank函数,所以通过自连接实现 select t1.s_id ,t1.s_name ,t1.avg_score ,(select count(distinct t2.avg_score) from (select sc.s_id ,s.s_name ,round(avg(sc.s_score),2) avg_score from Score sc join Student s on sc.s_id=s.s_id group by sc.s_id,s.s_name)t2 -- 临时表t2也是上面的结果 where t2.avg_score >= t1.avg_score ) rank from (select sc.s_id ,s.s_name ,round(avg(sc.s_score),2) avg_score from Score sc join Student s on sc.s_id=s.s_id group by sc.s_id,s.s_name)t1 -- 临时表t1就是上面的结果 order by t1.avg_score desc;

参考方法 select a.s_id -- 学号 ,@i:=@i+1 as '不保留空缺排名' -- 直接i的自加 ,@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名' ,@avg_score:=avg_s as '平均分' -- 表a中的值 from (select s_id ,round(avg(s_score), 2) as avg_s from Score group by s_id order by 2 desc)a -- 表a:平均成绩的排序和学号 ,(select @avg_score:=0, @i:=0, @k:=0)b -- 表b:通过变量设置初始值

实现rank函数 select s.s_name -- 姓名 ,s.s_score -- 成绩 ,(select count(distinct t2.s_score) from Score t2 where t2.s_score >= t1.s_score) rank -- 在t2分数大的情况下,统计t2的去重个数 from Score t1 order by t1.s_score desc; -- 分数降序排列 举例子来说明这个脚本: 姓名成绩张三89李四90王五78小明98小红60

  1. 当t1.s_score=89,满足t2.s_score > = t1.s_score的有98,90和89,此时count(distinct t2.s_score) 的个数就是3
  2. 当t1.s_score=90,满足t2.s_score > = t1.s_score的有98和90,此时count(distinct t2.s_score) 的个数就是2
  3. 当t1.s_score=78,满足t2.s_score > = t1.s_score的有98、90、89和78,此时count(distinct t2.s_score) 的个数就是4
  4. 当t1.s_score=98,满足t2.s_score > = t1.s_score的只有98,此时count(distinct t2.s_score) 的个数就是1
  5. 当t1.s_score=60,满足t2.s_score > = t1.s_score的有89、90、78、98、60,此时count(distinct t2.s_score) 的个数就是5

通过上面的步骤,我们发现:t1中每个分数对应的个数就是它的排名 题目25 题目需求 查询各科成绩前三名的记录 SQL实现 自己的方法 1、首先我们找出语文的前3名 select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc -- 降序之后取出前3条记录 limit 3;

2、通过同样的方法我们可以求出数学和英语的前3条记录,然后通过union进行联结,有待优化😭 -- 自己的脚本 (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc -- 降序之后取出前3条记录 limit 3) union (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '数学' order by s.s_score desc limit 3) union (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '英语' order by s.s_score desc limit 3)

参考方法 通过Score表的自连接,表a中的值小于表b中的值,排序之后我们取前3 select a.s_id ,a.c_id ,a.s_score -- 取出a中的成绩 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 表b中的成绩大 group by 1,2,3 having count(b.s_id) = 3 order by 2, 3 desc; 我们通过语文这个科目来理解上面的代码:前3名是80,80,76

-- 语文 select a.s_id ,a.c_id ,a.s_score -- 3、此时a表的成绩就是我们找的 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号 and a.c_id="01" group by 1,2 having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形 order by 3 desc;

-- 语文 select a.s_id ,a.c_id ,a.s_score -- a表的成绩 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号 group by 1,2,3 having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形 order by 2, 3 desc; -- 课程(2)的升序,成绩()3的降序

题目26 题目需求 查询每门课被选修的学生数 SQL实现 select c.c_id ,c.c_name ,count(s.s_id) from Course c join Score s on c.c_id = s.c_id group by c.c_id;

题目27 题目需求 查询出只有两门课程的全部学生的学号和姓名 SQL实现 having条件是分组之后在执行的,where语句是分组前先执行的 select s.s_id ,s.s_name from Student s join Score sc on s.s_id = sc.s_id group by 1,2 having count(sc.c_id) = 2;

题目28 题目需求 查询男女生人数 SQL实现 先看看数据:男女人数都是4

-- 自己的方法 select count(case when s_sex='男' then 1 end) as '男' ,count(case when s_sex='女' then 1 end) as '女' from Student; -- 参考方法 select s_sex ,count(s_sex) as `人数` from Student group by s_sex;

题目29 题目需求 查询名字中含有字的学生信息 SQL实现 先看看哪些同学的名字中有风

-- 模糊匹配:我们在两边都加上了%,考虑的是姓或者名字含有风,虽然风姓很少见 select * from Student where s_name like "%风%";

题目30 题目需求 查询同名同性的学生名单,并统计同名人数 SQL实现 1、先看看班级的学生信息

现有的数据中没有同名的学生,但是当班级人数增多的时候很有可能在班级上出现同名的学生 2、假设有同名同性的学生 select a.s_name ,a.s_sex ,count(*) from Student a -- 同一个表的自连接 join Student b on a.s_id != b.s_id -- 连接的时候不能是同一个人:学号保证,每个人的学号是唯一的,其他字段都可能重复 and a.s_sex = b.s_sex -- 性别相同 and a.s_name = b.s_name -- 名字相同 group by 1,2;

题目31 题目需求 查询1990年出生的学生信息 SQL实现 select * from Student where s_birth like '1990%'; -- 模糊匹配

题目32 题目需求 查询每门课程的平均成绩,结果按平均成绩降序排列;平均成绩相同时,按课程编号c_id升序排列 SQL实现 -- 自己的方法 select c_id ,round(avg(s_score),2) avg_score from Score group by 1 order by 2 desc, c_id; -- 指定字段和排序方法

如果想带上课程的名称,需要和Course表进行联结 -- 自己的方法 select c.c_id ,c.c_name ,round(avg(sc.s_score),2) avg_score from Score sc join Course c on sc.c_id = c.c_id group by 1,2 order by 3 desc, c.c_id; -- 指定字段和排序方法

题目33 题目需求 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 SQL实现 -- 自己的方法 select sc.s_id ,s.s_name ,round(avg(sc.s_score),2) avg_score from Score sc join Student s on sc.s_id = s.s_id group by sc.s_id,s.s_name having avg_score >= 85;

题目34 题目需求 查询课程名称为数学,且分数低于60的学生姓名和分数 SQL实现 select s.s_name ,sc.s_score from Score sc -- 成绩表 join Student s -- 学生信息表 on sc.s_id = s.s_id join Course c -- 课程表,指定数学 on sc.c_id = c.c_id where c.c_name = '数学' and sc.s_score < 60; -- 指定成绩

看看真正的数据,的确只有一个人满足

题目35 题目需求 查询所有学生的课程及分数情况 SQL实现 select s.s_id ,s.s_name ,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文' -- 语文分数 ,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学' ,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语' ,sum(sc.s_score) as '总分' -- 每个人的总分 from Student s left join Score sc on s.s_id = sc.s_id left join Course c on sc.c_id = c.c_id group by s.s_id, s.s_name; -- 学号和姓名的分组

题目36 题目需求 查询任何一门课程成绩在70分以上的姓名、课程名称和分数 SQL实现 select s.s_name ,c.c_name ,sc.s_score from Score sc -- 成绩表 join Student s -- 学生信息表 on sc.s_id = s.s_id join Course c -- 课程表 on sc.c_id = c.c_id where sc.s_score > 70 group by s.s_name, c.c_name, sc.s_score;

题目37 题目需求 查询不及格的课程 SQL实现 select sc.c_id ,c.c_name ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where sc.s_score < 60;

题目38 题目需求 查询课程编号为01且课程成绩大于等于80的学生的学号和姓名 SQL实现 select sc.s_id ,s.s_name ,sc.s_score from Score sc -- 成绩表 join Student s -- 学生信息表 on sc.s_id = s.s_id join Course c -- 课程表 on sc.c_id = c.c_id where c.c_id = 01 and sc.s_score >= 80;

题目39 题目需求 每门课程的学生人数 SQL实现 select c_id ,count(s_id) from Score group by c_id;

如果想连接到课程名称: -- 报错!!! select sc.c_id ,c.c_name ,count(sc.s_id) from Score sc join Course c group by sc.c_id; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.c.c_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决的方法是将我们之前的结果作为临时表和Course表来连接查询: select c.c_name 课程名称 ,c.c_id 课程编号 ,t.num 人数 from Course c join(select c_id ,count(s_id) num from Score group by c_id)t -- 上面结果的临时表 on c.c_id = t.c_id;

题目40 题目需求 查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩 SQL实现 1、我们先找出张三老师教了哪些课程 select c.c_id ,c.c_name from Course c join Teacher t on c.t_id = t.t_id where t.t_name = '张三';

2、找出哪些人修了数学 select sc.s_id ,sc.s_score from Score sc left join Course c on sc.c_id = c.c_id left join Teacher t on c.t_id = t.t_id where t.t_name = '张三';

3、通过max函数找出成绩的最高分 select max(sc.s_score) from Score sc left join Course c on sc.c_id = c.c_id left join Teacher t on c.t_id = t.t_id where t.t_name = '张三';

4、连接Student表,找出学生信息 select s.* ,sc.s_score ,sc.c_id ,c.c_name from Student s left join Score sc on s.s_id = sc.s_id left join Course c on sc.c_id = c.c_id where sc.s_score in (select max(sc.s_score) -- 最大值90分的确定 from Score sc left join Course c on sc.c_id = c.c_id left join Teacher t on c.t_id = t.t_id where t.t_name = '张三');

题目41 题目需求 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 SQL实现 select a.s_id ,a.c_id ,a.s_score from Score a join Score b on a.c_id != b.c_id and a.s_score = b.s_score and a.s_id != b.s_id;

我们对学号还需要去重下: select distinct a.s_id ,a.c_id ,a.s_score from Score a join Score b on a.c_id != b.c_id and a.s_score = b.s_score and a.s_id != b.s_id;

再看看原始的数据中是否符合要求:

题目42 题目需求 查询每门功成绩最好的前两名 SQL实现 自己的方法 还需要好好优化的😭 -- 先找出语文的前2名同学 select c.c_id ,sc.s_id ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where c.c_name = '语文' -- 改成数学和英语即可求出相应的信息 order by sc.s_score desc limit 2;

将3门学科的信息进行拼接即可求出答案: -- 最终脚本 (select c.c_id ,sc.s_id ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where c.c_name = '语文' order by sc.s_score desc limit 2) union (select c.c_id ,sc.s_id ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where c.c_name = '数学' order by sc.s_score desc limit 2) union (select c.c_id ,sc.s_id ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where c.c_name = '英语' order by sc.s_score desc limit 2)

参考方法(好方法) 如何解决前几名排序的问题🐂🍺🚗太牛了 select a.c_id ,a.s_id ,a.s_score from Score a where (select count(1) -- count(1)类似count(*):统计表b中分数大的数量 from Score b where b.c_id=a.c_id -- 课程相同 and b.s_score >= a.s_score) <= 2 -- 前2名 order by a.c_id;

首先我们看看真实的数据,我们以01课程来进行解释上面的代码:

符合要求count(1)<=2的只有两种情况

还需要好好理解下😭 题目43 题目需求 统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 SQL实现 select c_id ,count(s_score) num from Score group by c_id having num > 5 order by num desc, c_id;

题目44 题目需求 检索至少选修两门课程的学生学号 SQL实现 结果显示全部满足要求 select s_id ,count(*) num from Score group by s_id having num >= 2;

题目45 题目需求 查询选修了全部课程的学生信息 SQL实现 自己的方法 1、全部的课程数目num select count(*) from Course; -- 总共3门

2、从Score表分组统计每个人的课程数目,满足是3的学生信息 select s_id ,count(c_id) num -- 课程数目 from Score group by s_id having num in (select count(*) from Course); --满足全部课程

3、我们找出上面结果中的学生信息即可 select s.* ,count(c_id) num -- 课程数目 from Score sc join Student s on sc.s_id = s.s_id group by s.s_id having num in (select count(*) from Course); --满足全部课程

参考方法 select * -- 3、s_id对应的学生信息 from Student where s_id in(select s_id -- 2、最大课程数对应的s_id from Score group by s_id having count(*)=(select count(*) from Course) -- 1、全部课程数 ) 题目46 题目需求 查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减1 参考资料 SQL实现 自己的方法 -- 自己的方法 select * ,case when dayofyear(now()) >= dayofyear(s_birth) then year(now()) - year(s_birth) when dayofyear(now()) < dayofyear(s_birth) then year(now()) - year(s_birth) - 1 else 'other' end as 'age' from Student;

参考方法 select s_name ,s_birth ,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end) as age -- 当前日期大,说明已经过生了,年龄正常;反之说明今年还没有到年龄-1 from Student;

如何返回年份/日期 通过date_format函数能够指定返回的数据 -- 两个方法 select year(now()); select date_format(now(), '%Y');

返回具体的日期:

题目47 题目需求 查询本周过生日的学生 select week(now()); -- 47

  1. DAYOFWEEK(date)

返回日期date的星期索引(1=星期天,2=星期一, ……7=星期六),符合国内标准

  1. WEEKDAY(date)

返回date的星期索引(0=星期一,1=星期二, ……6= 星期天),国外标准 SQL实现 自己的方法 select * from Student where week(s_birth) = week(now());

参考方法 select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth); -- 方式1 select * from student where yearweek(s_birth) = yearweek(date_format(now(),'%Y%m%d')); -- 方式2 题目48 题目需求 查询下周过生日的学生 SQL实现 -- 自己的方法 select * from Student where week(s_birth) = week(now()) + 1; -- 往前推1周 -- 参考方法 select * from Student where week(date_format(now(),'%Y%m%d')) + 1= week(s_birth);

边界问题 如果现在刚好的是今年的最后一个周,那么下周就是明年的第一个周,我们如何解决这个问题呢??改进后的脚本: -- 自己的方法 select * from Student where mod(week(now()), 52) + 1 = week(s_birth); 当现在刚好是第52周,那么mod函数的结果是0,则说明出生的月份刚好是明年的第一周 题目49 题目需求 查询本月过生的同学

SQL实现 -- 自己的方法 select * from Student where month(s_birth) = month(now()); -- 参考方法 select * from Student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

返回的是空值,是因为数据本身就没有在11月份出生的同学

题目50 题目需求 查询下月过生日的同学 SQL实现 -- 自己的方法 select * from Student where month(s_birth) = month(now()) + 1; -- 推迟一个月 -- 参考方法 select * from Student where month(date_format(now(), '%Y%m%d')) + 1= month(s_birth);

边界问题 假设现在是12月份,那么下个月就是明年的1月份,我们如何解决???将上面的代码进行改进: select * from Student where mod(month(now()),12) + 1 = month(s_birth);

如果现在是12月份,则mod函数的结果是0,说明生日刚好是1月份

-- 自己的方法:通过自连接实现
select s1.*
from Student s1
where s_id in (
  select s2.s_id from Score s2
  join Score s3
  on s2.s_id=s3.s_id
  where s2.c_id='01' and s3.c_id='02'
);

-- 方法2:直接通过where语句实现
select s1.*
from Student s1, Score s2, Score s3
where s1.s_id=s2.s_id
and s1.s_id=s3.s_id
and s2.c_id=01 and s3.c_id=02;

-- 方法3:两个子查询
-- 1. 先查出学号
select sc1.s_id
from (select * from Score s1 where s1.c_id='01') sc1,
			(select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id;

-- 2.找出学生信息
select *
from Student
where s_id in (select sc1.s_id   -- 指定学号是符合要求的
               from (select * from Score s1 where s1.c_id='01') sc1,
               (select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id);

SQL实现

查询学过编号为01,并且学过编号为02课程的学生信息

题目需求

题目9

方法2:

select * -- 3. 通过学号找出全部学生信息
from Student
where s_id not in (  -- 2.通过学号取反:学号不在张三老师授课的学生的学号中
  select s_id
  from Score  S
  join Course C
  on S.c_id = C.c_id
  where C.t_id=(select t_id from Teacher where t_name ="张三")  -- 1.查询张三老师的课程
);

-- 方法2:
select *
from Student s1
where s1.s_id not in (
  select s2.s_id from Student s2 join Score s3 on s2.s_id=s3.s_id where s3.c_id in(
    select c.c_id from Course c join Teacher t on c.t_id=t.t_id where t_name="张三"
  )
);

-- 方法3
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id not in (
  select c_id from Course c where t_id=(  -- 1. 通过老师找出其对应的课程
    select t_id from Teacher t where t_name="张三"
  )
);

SQL实现

找出没有学过张三老师课程的学生

题目需求

题目8

方法3实现:

方法2来实现:

自己的方法:

-- 方法1:通过张三老师的课程的学生来查找;自己的方法
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id in (
  select s_id    -- 2.通过课程找出对应的学号
  from Score  S
  join Course C
  on S.c_id = C.c_id  -- 课程表和成绩表
  where C.t_id=(select t_id from Teacher where t_name="张三")  -- 1.查询张三老师的课程
);

-- 方法2:通过张三老师的课程来查询
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id in (
  select c_id from Course c where t_id=(  -- 1. 通过老师找出其对应的课程
    select t_id from Teacher t where t_name="张三"
  )
)

-- 方法3
select s.* from Teacher t
left join Course c on t.t_id=c.t_id  -- 教师表和课程表
left join Score sc on c.c_id=sc.c_id  -- 课程表和成绩表
left join Student s on s.s_id=sc.s_id  -- 成绩表和学生信息表
where t.t_name='张三';

SQL实现

查询学过张三老师授课的同学的信息

题目需求

题目7

这题怕是最简单的吧😭

select count(t_name) from Teacher where t_name like "李%";   -- 通配符

SQL实现

查询“李”姓老师的数量

题目需求

题目6

SQL实现

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

题目需求

题目5

使用null判断

使用ifnull函数

SQL实现2-ifnull函数判断

最后将两个部分的结果连起来即可:通过union方法

没有成绩的同学:

结果为:

平均分小于60

SQL实现1-两种情况连接

查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

题目要求

题目4

附加题:总分超过200分的同学

SQL实现

查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

题目需求

题目3

类比题目1的实现过程

SQL实现

查询"01"课程比"02"课程成绩低的学生的信息及课程分数(题目1是成绩高)

题目要求

题目2

第二种方法实现

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL练习(一)——MySQL之经典面试50题(上)

      我们通过前面的六篇文章给大家介绍了MySQL中的基本操作,包括增删改查等基本的操作,另外的MySQL的性能优化,我们之前通过十篇文章进行讲解,截止目前,我们...

    一计之长
  • MySQL练习(二)——MySQL之经典面试50题(下)

      我们在上篇文章开始介绍MySQL经典的面试题,我们接着上篇文章进行介绍剩下的25题。

    一计之长
  • 图解面试题:经典50题

    翻译成大白话:计算每个学号不及格分数个数,筛选出大于2个的学号并找出姓名,平均成绩,思路如图:

    猴子聊数据分析
  • 面试必备:Spring 面试问题 TOP 50

    Spring Framework 现在几乎已成为 Java Web 开发的标配框架。那么,作为 Java 程序员,你对 Spring 的主要技术点又掌握了多少呢...

    搜云库技术团队
  • Linux面试题基础宝典==面试必备

    另外,du 命令也可以做类似的事情,可以看看 《查找 Linux 系统中的占用磁盘空间最大的前 10 个文件或文件夹》 文章。

    心跳包
  • Java经典编程50题(面试笔试机试)

    https://blog.csdn.net/alias_fa/article/details/52985112

    林万程
  • 常见的SQL面试题:经典50例

    链接:https://blog.csdn.net/u010565545/article/details/100785261

    张俊红
  • 20个【MySQL】经典面试题

    一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

    lyb-geek
  • 10道经典MySQL面试题

    truncate和不带where子句的delete,以及drop都会删除表内的数据

    JAVA葵花宝典
  • 10 道经典 MySQL 面试题

    truncate和不带where子句的delete,以及drop都会删除表内的数据

    民工哥
  • 【收藏】SQL经典面试50题 | 附答案

    今天给大家分享一份星友对SQL经典面试50题的刷题记录,稍作了一些修改,以下是星友自述正文:

    木东居士
  • 平平无奇SQL面试题:经典50例

    组函数: 去重 distinct() 统计总数sum() 计算个数count() 平均数avg() 最大值max() 最小数min()

    大数据真好玩
  • 50道Java集合经典面试题(收藏版)

    Collection.sort是对list进行排序,Arrays.sort是对数组进行排序。

    捡田螺的小男孩
  • 面试必备!91 道 MySQL 面试题(含答案)

    结构化查询语言(Structured Query Language)简称SQL,是一种数据库查询语言。

    Python进击者
  • 测试人员必知必会经典性能面试题

    负载测试(Load Testing):负载测试是一种主要为了测试软件系统是否达到需求文档设计的目标,譬如软件在一定时期内,最大支持多少并发用户数,软件请求出错率...

    测试小兵
  • 十二条Linux运维面试必备经典笔试/面试题,来挑战一下!

    又到了一年一度的秋招,作为运维方向,看了一些面经,收集了一些笔试面试题,总结了一下,贴出来仅供参考,有错误的地方还请指出. 1.Linux设置环境变量 暂时的:...

    小小科
  • 面试必备!TCP协议经典十五连问!

    TCP协议是大厂面试必问的知识点。整理了15道非常经典的TCP面试题,希望大家都找到理想的offer呀

    捡田螺的小男孩
  • 经典面试题

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明...

    cwl_java
  • 经典面试题

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明...

    cwl_java

扫码关注云+社区

领取腾讯云代金券