MySQL--经典题目综合汇总二（进阶）--建议先把之前的看了，难度较高

      更多关于数据库知识请加关注哟~~。若需联系博主请私信或者加博主联系方式:
      QQ:3327908431
      微信：ZDSL1542334210

        前言：今天将为大家带来MySQL综合训练题目，该部分是基于之前的各节内容，所以基础薄弱的伙伴建议先把之前的文章看了再来续读该篇文章----祝你好运！

1、表格创建

create table stu(s_id varchar(5),
   s_name varchar(5),
   s_sex varchar(1),
   s_age int(3),s_day date);

insert into stu values
("001","李华","男",23,'1996-8-16'),
("002","王二","男",24,'1997-3-16'),
("003","赵敏","女",23,'1990-5-26'),
("004","张莹莹","女",22,'1995-2-16'),
("005","朱亚军","男",25,'1999-8-16'),
("006","马云","男",28,'1993-12-16');

create table scores (s_id varchar(5),
    c_id varchar(3),score float);
    
insert into scores values 
("001","01",135),
("005","01",120),
("003","01",110),
("002","01",90),
("005","02",140),
("001","02",125.5),
("004","02",100),
("006","02",90),
("002","03",102),
("005","03",100.6),
("001","03",100),
("003","03",95.6),
("004","03",83),
("003","02",80),
("006","03",79.5);

create table coure(
c_id varchar(2),t_id char(1),c_name varchar(10));

insert into coure values 
("01","3","数据库原理"),
("02","2","统计学基础"),
("03","1","Python基础");

create table teacher(
 t_id char(1),t_name varchar(5));
 
insert into teacher values
  ("1","邓博"),
  ("2","崔博"),
  ("3","汪院长");

2、题目部分

题目一：求所有课程平均成绩排名在2到4名的同学信息(压轴)

        分析：该题目比较难，例为压轴题，没有好的基础的话很吃力，其难点在于求的是2到4名同学，并不是第一名或者是最后一名，而且还要求以平均分排名，那么必然会用到group by 但是这样会丢失信息，于是我们巧妙的利用转为二维表的方法，在保证排名顺序的情况下利用表的合并处理，代码为：

select stu.s_id,s_name,s_sex,s_age,s_bothday from   # 法一
  (select s_id, 
  round(sum(c_id='01')*score,2) '01',
  round(sum(c_id='02')*score,2) '02',
  round(sum(c_id='03')*score,2) '03',
  round(avg(score),2) 平均分
  from scores group by s_id order by 平均分 desc limit 1,3)aa 
  left join stu on aa.s_id=stu.s_id;

select stu.s_id,s_name,s_sex,s_age,s_bothday from    # 法二 博主的第二种方法，建议基础较好的朋友看，比较绕
    (select s_id,round(avg(score),2)平均分 from scores group by s_id order by 平均分 desc limit 1,3)a 
    left join stu on a.s_id=stu.s_id;
# 以上两种方法答案均为
001	李华	男	23	1996-8-16
002	王二	男	24	1997-3-16
003	赵敏	女	23	1990-5-26

题目二：查询不同老师所教不同课程平均分从高到低显示(压轴)

select t_name,a1.c_id,avg(score)平均分 from   # 法一
  (select c_id,score from scores)a1 left join
  (select c_id,teacher.t_id,t_name from coure left join teacher
  on coure.t_id=teacher.t_id)a2 on a1.c_id=a2.c_id group by 
  t_name,c_id order by 平均分 desc;
# 法二  适合用于一位老师教一门课程
select t_name,scores.c_id,avg(score)平均分 from scores left join 
  coure on scores.c_id=coure.c_id left join teacher on 
  coure.t_id=teacher.t_id group by scores.c_id order by 平均分 desc;
# 结果为：
汪院长	01	113.75
崔博	02	107.1
邓博	03	93.4499994913737

题目三：查询出只有两门课程的全部学生的学号和姓名

select stu.s_id,s_name from stu left join scores on 
   stu.s_id=scores.s_id group by scores.s_id having count(*)=2;
# 结果：
002	王二
004	张莹莹
006	马云

题目四：查询没有学全所有课程的同学的信息

       分析：没有学全的同学就是他的课程号少了，于是我们只要找到所有课程都满的然后分别和每个同学的课程比较就可以了。代码为：

select stu.* from stu left join scores on stu.s_id=scores.s_id # 法一 
  group by scores.s_id having count(*)< (select count(*)课程数 from coure);
 
select * from stu left join scores on stu.s_id=scores.s_id group by 
  scores.s_id having group_concat(scores.c_id order by scores.c_id)!=
  (select group_concat(c_id order by c_id) from coure); #法二 group_concat
# 结果：
002	王二	男	24	1997-3-16
004	张莹莹	女	22	1995-2-16
006	马云	男	28	1993-12-16

题目五：查询没学过“汪院长”老师授课的同学的信息

select stu.* from stu where s_id not in  #法一
  (select stu.s_id from stu left join scores on stu.s_id=scores.s_id    
  left join coure on scores.c_id=coure.c_id left join teacher on 
  coure.t_id=teacher.t_id where t_name="汪院长");
 
select stu.* from stu where s_id not in  # 法二
(select s_id from scores where c_id =
(select c_id from coure left join teacher on coure.t_id=teacher.t_id where t_name="汪院长")) group by stu.s_id;
# 结果
004	张莹莹	女	22	1995-2-16
006	马云	男	28	1993-12-16

题目六：查询至少有两门课与学号为“002”的同学所学课程相同的同学信息(压轴)

        该题目的难度较大，是我自己设置的题目，至少就是要么有两门课程与002相同，要么相同的多于两门课程。首先要用group_concat将002学的课程找到并，再将它去匹配其他同学，如果002在其他同学里面找到，那么就是答案了，真的可以实现吗？

select stu.* from stu left join scores on stu.s_id=scores.s_id group 
  by stu.s_id having group_concat(c_id order by c_id) in (select 
  group_concat(c_id) from scores where s_id = 002); # 此方法不对
# 答案是NO！这样是做不出来的，因为group_concat返回的结果是以逗号
# 分隔的字符串，如果先找到属于002的，它就是01,03，万一其他判断是01,02,03或者其他情况，就没法
# 判断，所有要单独将找到的01，03用括号列举出来正确答案为：

select c_id from scores where s_id = 002; # 先找到002的课程
select stu.* from stu where s_id in (select s_id from scores group by 
  s_id having group_concat(c_id order by c_id) in (01,03)) and s_id!=002; # 法一 子查询

select stu.* from stu left join scores on stu.s_id=scores.s_id where c_id in # 法二 多表连接
(select c_id from scores where s_id = 002) group by stu.s_id  having count(*)>=2 and stu.s_id！=002;
# 结果
001	李华	男	23	1996-8-16
003	赵敏	女	23	1990-5-26
005	朱亚军	男	25	1999-8-16

题目七：查询学过编号为“01”并且也学过编号为“02”的课程的同学信息

select stu.* from stu left join scores on stu.s_id=scores.s_id where c_id in (01,02)
  group by stu.s_id having count(*)>=2;
# 结果
001	李华	男	23	1996-8-16
003	赵敏	女	23	1990-5-26
005	朱亚军	男	25	1999-8-16

题目八：查询“01”课比“02”课程成绩高的学生的信息及分数(压轴)

        该题目难度也是比较高，因为c_id在scores里面是同一列没办法进行比较，于是得转为二维表进行比较筛选：

select stu.s_id,s_name,s_sex,s_bothday,课程1 from 
  (select s_id,
  round(sum(c_id='01')*score,2) 课程1,
  round(sum(c_id='02')*score,2) 课程2,
  round(sum(c_id='03')*score,2) 课程3
  from scores group by s_id)a1 left join stu on 
  a1.s_id=stu.s_id where 课程1>课程2 and 课程2!=0;
# 结果：可以看到并没有结果，说明不存在01课程比02课程分数高的同学，
# 所以02可能是语文，而01可能是数学或者英语~

题目九：查询学过编号为“01”但是没有学过编号为“02”的课程的同学的信息

select s_id,group_concat(c_id order by c_id)课程号 from scores 
  group by s_id having  '02' not in (课程号) and '01' in (课程号);
  # 等一下，看看我这个代码为什么查不出来，困扰我很久了。该方法不对！！
# 正确的代码
select * from stu where s_id in
  (select s_id from
  (select s_id,
  sum((c_id='01')*score) '一',
  sum((c_id='02')*score) '二',
  sum((c_id='03')*score) '三'
  from scores group by s_id having 一 !=0 and 二=0)a1);
# 结果
002	王二	男	24	1997-3-16

题目十：查询任何一门课程成绩在100分以上的姓名、课程名称和分数

select stu.s_name,c_name,score from stu left join scores on 
  stu.s_id=scores.s_id left join coure on scores.c_id=coure.c_id
  left join teacher on coure.t_id=teacher.t_id where score>100 order by stu.s_id;
# 结果为：
朱亚军	统计学基础	140
朱亚军	Python基础	100.6
朱亚军	数据库原理	120
李华	数据库原理	135
李华	统计学基础	125.5
王二	Python基础	102
赵敏	数据库原理	110

3、文末彩蛋–轻松一刻

        又到大家最喜欢的彩蛋环节了，今天还是带来我好朋友小熊的故事。就是高二的时候，小熊呢去一家牛奶场找到一份工作，是挤牛奶的，我和亮哥听说他第一天上班就去看他，结果当时老板给他一只桶和一条凳让他去牛奶棚挤奶，他快乐地领命而去。回来的时候满头大汗，见他被溅了满身的牛奶，而且那条凳子腿也断了，老板就问他：“怎么样，这活挺难吗？”他哭丧着脸答道：“挤奶倒不难，难的是让牛站到凳子上去…”然后当时我亮哥急眼了说到：“牛站在凳子上你怎么挤？你他妈的能不能动动脑子？你应该让牛坐在凳子上面，这样你才好操作嘛…”所以大家知道我为啥现在都找不到非常高薪的工作了吧？

       今天到这里就结束了哟//每篇文章都有文末彩蛋–轻松一刻哟~加关注学习更多MySQL知识！谢谢观看，我是Jetuser-data

链接: [https://blog.csdn.net/L1542334210]
CSND:L1542334210

祝大家工作顺利！阖家欢乐！