mysql经典45道题笔记

mysql经典45道题 笔记解答

库：mytest

表：student、teacher、course、sc

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

思路：先查出学生信息，再加入一张表sc作为字段课程01，再加入一张表sc作为课程02

• select * from student a
  inner join sc b on a.sid=b.sid
  inner join sc c on a.sid=c.sid
  and b.cid=01
  and c.cid =02
  where b.score>c.score;

查询同时存在01课程和02课程的情况

1. select * from (select * from sc where cid = ‘01’) a inner join (select * from sc where cid =‘02’) b on a.sid =b.sid;
2. select * from sc a inner join sc b on a.sid =b.sid where a.cid = ‘01’ and b.cid =‘02’; #自关联

查询不存在01课程但存在02课程的情况

1. select * from
   (select * from sc where cid=‘01’) a
   left join sc b on a.sid=b.sid and b.cid=‘02’;
2. select * from
   sc a
   left join sc b on a.sid=b.sid and b.cid=‘02’
   where a.cid = ‘01’;

查询不存在01课程但存在02课程的情况

1. select * from
   (select*from sc where sid not in(select sid from sc where cid=‘01’)) a inner join sc b on a.sid = b.sid and b.cid =‘02’;
2. select *from sc a
   where sid not in(select sid from sc where cid = ‘01’) and cid = ‘02’;

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

1. select a.sid,a.sname,b.avg_score from student a
   inner join

   (select sid,avg(score) as avg_score from sc group by sid having avg(score)>=60) b

   on a.sid =b.sid;

查询在sc表存在成绩的学生信息

group by语法：只能查询分组字段，其他字段要以聚合形式被查询出来

1. select * from sc a left join student b on a.sid = b.sid group by b.sid;
2. select * from student a inner join (select sid from sc group by sid) b on a.sid = b.sid;

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为null）

1. select a.sid,a.sname ,b.cons,b.sum_score
   from student a
   left join (select sid,count(cid) as cons,sum(score) as sum_score from sc group by sid) b
   on a.sid = b.sid;
2. select a.sid,a.sname,count(b.cid) as cons ,sum(b.score) as sum_score from student a
   left join sc b
   on a.sid =b.sid group by a.sid;

查询李姓老师的数量

1. select count(*) from teacher where tname like ‘李%’;

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

1. select a.*,b.cid,b.score,c.tid,d.tname from student a inner join sc b on a.sid=b.sid inner join course c on c.cid = b.cid inner join teacher d on d.tid=c.tid where d.tname=‘张三’;

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

1. select a.,b.
   from sc a
   inner join student b on a.sid = b.sid
   group by a.sid
   having count(1)<(select count(*) from course);

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

思路：查看学号01同学学的课程

一：select cid from sc where sid = '01';

二：select b.* from sc a

	inner join student b

	on a.sid = b.sid 

	where a.cid 

	in (select cid from sc where sid = '01');

加上去重distinct

1. select distinct b.* from sc a
   inner join student b
   on a.sid = b.sid
   where a.cid
   in (select cid from sc where sid = ‘01’);
2. select * from student
   inner join (select distinct a.sid from sc a
   where a.cid
   in (select cid from sc where sid = ‘01’))as d
   on student.sid = d.sid;

查询和01号同学学习的课程完全相同的其它同学的信息

思路：要没有学习1号同学外的其它课程的同学

查询1号同学所学的课程

一号同学学的课程外的同学

然后再排除学习一号学生课程外的同学

得到的数字在进行数量对比

相等那就一样，不相等就是少学了

1. select b.* from sc a inner join student b on a.sid = b.sid where a.sid not in (select sid from sc where cid not in(select cid from sc where sid = ‘01’))group by a.sid having count(1) = (select count(*)from sc where sid = ‘01’);

查询没学过张三老师讲授的任意门课程的学生姓名

思路：可以先找出学习过张三老师的学生信息，再进行where not in 操作

1. select sname from student
   where sid
   in(select distinct sid from sc
   inner join (select * from course where cid
   not in (select a.cid from course a inner join teacher b on a.tid = b.tid where b.tname = ‘张三’)) as co
   on co.cid = sc.cid);
   2.select sname from student
   where sid
   not in (select distinct sid
   from course as a
   inner join teacher
   on teacher.tid = a.tid inner join sc b
   on b.cid = a.cid);

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

提示：学生表、成绩表、分组聚合

首先可以先求平均值

1. select a.sid,b.sname,avg(score)as avg_score
   from sc a left join student b on a.sid= b.sid inner join (select sid,sname from student where sid in(select sid from sc where score < 60 group by sid having count(1)>1)) as c on a.sid = c.sid group by a.sid;

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

提示：：成绩表、学生表

1. select a.sid,a.score,b.*
   from (select * from sc where score < 60 and cid = ‘01’) a
   left join student b on a.sid = b.sid
   order by score desc;

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

提示：成绩表、学生表

求学生平均成绩:select sid,avg(score) as avg_score from sc group by sid;

1. select a.*,avg_score
   from sc a
   left join (select sid,avg(score)as avg_score from sc group by sid) b
   on a.sid = b.sid
   order by avg_score desc;

查询各科成绩最高分、最低分和平均分

提示：分组聚合、这里考察一个条件计数的技巧

1.计算各科的平均分、最高分、及格率

select 

cid,

max(score) as 最高分,

min(score) as 最低分,

avg(score) as 平均分,

count(1) as 选修人数,

sum(case when score>=60 then 1 else 0 end)/count(1) as 及格率,

sum(case when score>=70 and score <80 then 1 else 0 end)/count(1) as 中等率,

sum(case when score>=80 and score <90 then 1 else 0 end)/count(1) as 优良率,

sum(case when score>=90  then 1 else 0 end)/count(1) as 优秀率

from sc group by cid order by 选修人数 desc,cid asc;

按各科成绩进行排序，并显示排名，score重复时继续排序

提示：这里要用到MySQL的变量 select @rank:=1;

1. select sid,cid,score,@rank:=@rank+1 as rn
   from sc ,(select @rank:=0) as t
   order by score desc;

按各科成绩进行排序，并显示排名，score重复时合并名次

select *,

case when(@sco = score) then @rank else @rank:=@rank+1

end as rn,@sco:=score

from sc,(select @rank:=0,@sco:=null) as t

order by score desc;

1. select sid,cid,score,case when @sco=score then @rank when @sco:=score then @rank:=@rank+1 end as rn from sc ,(select @rank:=0,@sco:=null) as t order by score desc;

查询学生的总成绩，并进行排名，总分重复时保留名次空缺

提示：自定义变量

set @a:=2;#定义了变量 方法一

select @b:=4 #定义了变量 方法二

重新赋值就是修改值

1. select sid, sum(score),@rank:=@rank+1 as rn from sc,(select @rank:=0)as t group by sid order by sum(score) desc;
2. select a.*,@rank:=if(@sco=sco,’’,@rank+1),@sco:=sco from (select sid,sum(score) as sco from sc group by sid order by sco desc) a ,(select @sco:=null,@rank:=0)b;

统计各科成绩各分段人数：课程编号，课程名称，[100-85),[85-70),[70-60),[60-0)及所占百分比

提示：分组统计数据，判断各分段的人数情况

== concat()连接函数==

1.直接进行分组统计

1. select cid,
   concat (sum(case when 0<=score and score <=60 then 1 else 0 end)/count(1)*100,’%’ )as ‘(0-60]’,
   concat(sum(case when 60<score and score <=70 then 1 else 0 end)/count(1)*100 ,’%’)as ‘(60-70]’ ,
   concat(sum(case when 70<score and score <=85 then 1 else 0 end)/count(1)*100 ,’%’)as ‘(70-85]’ ,
   concat(sum(case when 85<score and score <=100 then 1 else 0 end)/count(1)*100,’%’ )as ‘(85-100]’
   from sc group by cid;

查询各科成绩前三名的记录

1. select a.* from sc a
   where (select count(1) from sc b where b.cid =a.cid and b.score >a.score)❤️ order by cid desc,score desc;

查询每门课程被选修的学生数

提示：按照课程id分组统计数据量

1. select count(sid),cid from sc group by cid ;

查询只选修两门课程的学生学号和姓名

注意：where和分组group by一起使用时，where放在前面先筛选再进行分组

而要先分组再筛选要使用having 且having要放在group by后面

提示：分组查询，根据分组情况进行筛选

1. select a.sid,a.sname,b.count_cid from student as a left join (select count(cid)as count_cid,sid from sc group by sid) as b on a.sid=b.sid where count_cid=2;
   统计每个学生的成绩个数等于两门
2. select count(score),sid from sc group by sid having count(score)=2;

查询男生女生人数

1. select ssex,count(1) from student group by ssex;

查询名字中含有风字的学生信息

1. select *from student where sname like ‘%风%’;

查询同名同姓学生名单，并统计同名同姓人数

思路：根据姓名进行分组，计算个数，然后排除个数为1的字段，剩下的就是重名的（但是不精确）select sname,count(1) ,sid from student group by sname having count(1)!=1;

提示：自关联查询选出符合条件的记录

1. select a.sname,a.ssex,count(1) as cons
   from student a
   inner join student b
   on a.sname = b.sname and a.ssex=b.ssex and a.sid!=b.sid
   group by a.sname,a.ssex;

查询1990年出生的学生名单

提示：关键字段“出生日期“Sage”,year函数

select year(‘2020-02-01’);#验证year函数的使用 获取年份

1. select * from student where year(sage)=‘1990’;

查询每门课程的平均成绩，结果按照平均成绩降序排列，平均成绩相同时，按课程编号升序排列

提示：分组聚合然后排序

1. select avg(score),cid from sc group by cid order by avg(score) desc ,cid asc;

查询平均成绩大于等于85的所有学生的学号，姓名和平均成绩

提示：需要分组聚合求平均值，然后筛选学生信息

1. select a.sname,b.sid,avg_score
   from student a
   inner join
   (select avg(score)as avg_score,sid from sc group by sid having avg(score)>=85) as b
   on a.sid= b.sid;

查询课程名为数学，且分数低于60分的学生姓名和分数

提示：条件关联查询

1. 先查询数学的课程id
2. 求分数低于60分的成绩记录

• select * from (select * from course where cname = ‘数学’) as a inner join (select * from sc where score<60)as b inner join student as c on a.cid=b.cid and b.sid=c.sid;

查询所有的学生的课程及分数情况（存在学生没成绩，没选课的情况）

提示：给学生信息加上课程情况

1. select * from student as a left join sc as b on a.sid = b.sid;

查询任何一门课程成绩在70分以上的姓名，课程名称和分数

1. select sname,cname,score from student inner join (select * from sc where score > 70)as b on student.sid=b.sid inner join course on b.cid = course.cid;

查询存在不及格的课程

提示：条件查询

1. select a.* from course a where a.cid in (select distinct cid from sc where score<60); ## 用到in 语句
2. select c.* from course c inner join(select distinct cid from sc where score<60) a on c.cid=a.cid; ## 用到inner join on

查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

提示：关联查询

1. select * from student where sid in(select distinct sid from sc where cid = '01’and score>=80);

求每门课程的学生人数

提示：分组聚合

1. select count(sid),cid from sc group by cid;

假设成绩不重复，查询选修【张三】老师所授课程的学生中，成绩最高的学生信息及其成绩

提示：条件查询筛选，只需要一个人

1. select b.*,a.score
   from sc a
   left join student b
   on a.sid = b.sid
   left join course c
   on a.cid = c.cid
   left join teacher d
   on c.tid = d.tid
   where d.tname = ‘张三’
   order by a.score desc
   limit 0,1

成绩有重复的情况下，查询选修【张三】老师所授课程的学生中，成绩最高的学生信息及其成绩

1. 这题太复杂了不做了

查询不同课程成绩相同的学生的学生编号，课程编号，学生成绩

提示：条件关联筛选

1. select * from sc a inner join sc b on a.sid = b.sid where a.cid !=b.cid and a.score = b.score group by a.sid ,a.cid;

查询每门科目成绩最好的前两名

提示：用户变量排序

1. select
   sid,cid,score,rank
   from (select
   sc.*,
   @rank:=if(@c_cid=cid,if(@sco=score,@rank,@rank+1),1) as rank,
   @sco:=score,
   @c_cid:=cid
   from sc,(select @sco:=null,@rank:=0,@c_cid:=null) b
   order by cid,score desc) a where a.rank<3;

统计每门课程的学生选修人数（超过5人的课程才统计）

提示：分组统计

1. select count(1),cid from sc group by cid having count(1)>5;

检索至少选修两门课程的学生学号

1. select sid,count(1) from sc group by sid having count(1)>2;

查询选修了全部课程的学生信息

1. select * from student as a inner join (select count(1),sid from sc group by sid having count(1)=(select count(1) from course)) as b on a.sid = b.sid;
2. select a.*
   from student a
   where(select count(1) from sc b where a.sid = b.sid) = (select count(1) from course);# 查询 学生信息 条件是：课程分数数量等于课程数量

查询各学生的年龄，只按年份来算

提示：使用日期函数进行相减

select now()   2021-07-03 10:51:25

select curdate() 2021-07-03

1. select *,year(now())-year(sage)as age from student

按照出生日期来算年龄，当前月日<出生年月的月日则年龄减一

– timestampdiff 日期相减函数

select timestampdiff(year,‘2002-05-01’,‘2003-06-01’); -1

select timestampdiff(day,‘2002-05-01’,‘2001-01-01’); -485

select timestampdiff(hour,‘2008-08-08 12:00:00’,‘2008-08-08 00:00:00’); -12

– 获取当前时间

select now();

select curdate();

1. select *,timestampdiff(year,sage,now()) as age from student;

查询本周过生日的学生

– 查询一年中的第多少周

select week(‘2004-06-03’);

1. select * ,week(sage),week(now()) from student where week(sage)=week(now());

查询下周过生日的学生

1. select * ,week(sage),week(now()) from student where week(sage)=(week(now())+1);

查询本月过生日的学生

1. select *,month(sage),month(now()) from student where month(sage)=month(now());

查询下月过生日的学生

1. select *,month(sage),month(now()) from student where month(sage)=(month(now())+1);