前言：MySQL一系列操作猛如虎，代码易懂清晰，结构也简单，今天我们来谈一谈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);

2、题目部分

题目一：查询比学生编号为002的学生的所有课程成绩都高的学生信息
        分析：要比002所有课程成绩都高，意味着要别人的最低成绩都高于002的最高成绩。

select  stu.* from stu where s_id in 
    (select  s_id from scores group by score having min(score)> 
    (select max(score) from scores where s_id=002));
# 答案
001	李华	男	23	1996-8-16
003	赵敏	女	23	1990-5-26
005	朱亚军	男	25	1999-8-16

题目二：查询平均成绩大于等于100分的学生的成绩信息

select * from scores where s_id in 
    (select s_id from scores group by s_id having round(avg(score),2)>=100);
# 答案
001	01	135
005	01	120
005	02	140
001	02	125.5
005	03	100.6
001	03	100

题目三：查询每位同学的每科成绩的最高分

select * from scores  group by c_id,s_id having max(score) order by c_id,score desc  ;
# 答案
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
003	02	80
002	03	102
005	03	100.6
001	03	100
003	03	95.6
004	03	83
006	03	79.5

题目五：查询每科成绩的最高分

select max(score) from scores group by c_id;
# 答案
135
140
102