Mysql-练习50题个人记录（1-17）

偶然搜索看到了大佬的训练题，我也跟着大佬思路过了一遍。
大佬链接：点这里
在这个链接里面有表以及数据，下面开始第一题
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select stu.*,sc1.s_score as '语文',sc2.s_score as '数学' 
from Student stu
left join Score sc1 on stu.s_id =sc1.s_id and sc1.c_id ='01'
left join Score sc2 on stu.s_id =sc2.s_id and sc2.c_id ='02'
where sc1.s_score >sc2.s_score ;

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

select stu.*,sc1.s_score as '语文',sc2.s_score as '数学'
from Student stu
left join Score sc1 on stu.s_id = sc1.s_id and sc1.c_id = '01'
left join Score sc2 on stu.s_id = sc2.s_id and sc2.c_id = '02'
where sc1.s_score < sc2.s_score ;

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

select stu.s_id,stu.s_name,round(avg(sc.s_score),2)
from Student stu 
left join Score sc on stu.s_id = sc.s_id 
group by stu.s_id having AVG(sc.s_score) >=60 

这儿的round函数是控制小数的，比如说小数后两位，则写2。

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

select stu.s_id,stu.s_name,round(avg(sc.s_score),2)
from Student stu 
left join Score sc on stu.s_id  = sc.s_id 
group by stu.s_id having AVG(sc.s_score)<60;

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

select stu.s_id ,stu.s_name ,count(sc.c_id) '课程总数', sum(sc.s_score) '总分' 
from Student stu
left join Score sc on stu.s_id = sc.s_id 
group by stu.s_id ;

6、查询"李"姓老师的数量

select count(*) as '李数量' from Teacher t where t_name like '李%';

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

select stu.* from Student stu
left join Score sc on stu.s_id = sc.s_id
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 = '张三';

就把所有表连在一起

8、查询没学过"张三"老师授课的同学的信息

select stu.* from Student stu where stu.s_id not in(
select sc.s_id from Score sc where sc.c_id in (
select c.c_id from Course c join Teacher t on c.t_id = t.t_id 
where t.t_name ='张三' ));

三步：第一步，找到张三教授的课程号。第二步，找到选了这些课程号的学生编号。第三步，反选。

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select stu1.* from Student stu1
inner join Score sc1 on stu1.s_id = sc1.s_id 
inner join Course c1 on sc1.c_id = c1.c_id and c1.c_id = '01'
where stu1.s_id in (
select stu2.s_id from Student stu2
inner join Score sc2 on stu2.s_id = sc2.s_id 
inner join Course c2 on sc2.c_id = c2.c_id and c2.c_id = '02'
);

查询02课程的学号，通过where查看到两门课程都有学的学生的学号

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select stu1.* from Student stu1
inner join Score sc1 on stu1.s_id = sc1.s_id 
inner join Course c1 on sc1.c_id = c1.c_id and c1.c_id = '01'
where stu1.s_id not in (
select stu2.s_id from Student stu2
inner join Score sc2 on stu2.s_id = sc2.s_id 
inner join Course c2 on sc2.c_id = c2.c_id and c2.c_id = '02'
);

11、查询没有学全所有课程的同学的信息

select stu.* from Student stu
left join Score sc on stu.s_id = sc.s_id 
group by stu.s_id 
having count(sc.c_id)<(select count(c.c_id) from Course c);

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct stu.* from Student stu
join Score sc on stu.s_id = sc.s_id 
where sc.c_id in (
select sc2.c_id from Student stu2 join Score sc2 on
stu2.s_id = sc2.s_id where sc2.s_id = '01');

首先查找到01同学所有所学的课程，而后在这里面查找课程号

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select distinct stu.* from Student stu
join Score sc on stu.s_id = sc.s_id 
group by stu.s_id 
having group_concat(sc.c_id) = (
select group_concat(sc2.c_id) from Score sc2 group by sc2.s_id 
having sc2.s_id='01');

这里使用到了group_concat函数，如下sql所示，我们希望按照s_id分组，并且分组后将c_id打印

select group_concat(c_id) from Score group by s_id;

如图所示：

这是一个比较有趣且实用的函数，是我个人首次认识。
14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select stu.s_name,stu.s_id  from Student stu
where stu.s_id not in (
select s_id from Score sc 
join Course c on sc.c_id = c.c_id 
join Teacher t on c.t_id = t.t_id 
where t.t_name = '张三'
);

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select stu.s_id,stu.s_name,round(AVG(sc.s_score),2)  
from Student stu
left join Score sc on stu.s_id = sc.s_id 
where stu.s_id in(
select sc2.s_id from Score sc2 
where sc2.s_score < 60
group by sc2.s_id having count(s_id)>=2
) group by stu.s_id 

16、检索"01"课程分数小于60，按分数降序排列的学生信息

select stu.*,sc.s_score,sc.c_id  from Student stu
join Score sc on stu.s_id = sc.s_id  
where sc.c_id  = '01' and sc.s_score<60
order by sc.s_score 

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select stu.s_id,stu.s_name,sc1.s_score as "语文",sc2.s_score as "数学",sc3.s_score as "英语",round(AVG(sc.s_score),2) as "平均成绩"
from Student stu
left join Score sc on stu.s_id = sc.s_id 
left join Score sc1 on stu.s_id = sc1.s_id and sc1.c_id = '01'
left join Score sc2 on stu.s_id = sc2.s_id and sc2.c_id = '02'
left join Score sc3 on stu.s_id = sc3.s_id and sc3.c_id = '03'
group by stu.s_id 
order by AVG(sc.s_score) desc