2021-11-15 MySQL 查询练习

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

-- 思路：查询sid相等且'01'课程>'02'课程的信息，然后与student表联结
select * from student RIGHT JOin(
   select t1.sid, score1,score2 from
      (select sid, cid, score as score1 from sc where cid='01') as t1,
      (select sid, cid, score as score2 from sc where cid='02') as t2
   where t1.sid=t2.sid and score1>score2) as r
on student.sid=r.sid;

-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
-- 思路：分别查询课程号='01'和'02'表，然后做内联结
select * from
(select * from sc where cid='01') as t1,
(select * from sc where Cid='02') as t2
where t1.sid=t2.sid;

-- 等价于
select * from
(select * from sc where cid='01') as t1 inner join
(select * from sc where Cid='02') as t2
on t1.sid=t2.sid;


-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
-- 思路：分别查询课程号='01'和'02'表，然后做左联结
select * from
(select * from sc where cid='01') as t1 left join
(select * from sc where Cid='02') as t2
on t1.sid=t2.sid;


-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
-- 思路1：分别查询课程号='01'和'02'表，然后做有右联结，再用where is NULL
select * from
   (select * from sc where cid='01') as t1 RIGHT JOin
   (select * from sc where Cid='02') as t2
on t1.sid=t2.sid
where t1.cid is null;

-- 思路2：先查询课程号='01'的学生Id，然后用where判断学生Id不在这里面的，并且课程号='02'
select * from sc
where sc.sid not in (
   select sid from sc where sc.cid = '01'
)
and sc.cid= '02';

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

-- 思路：使用group by之后再having判断平均成绩，与student表联结得到学生姓名
select t1.sid,t2.sname,avg(score) as average  from sc as t1
left join student as t2
on t1.sid=t2.sid
group by t1.sid,t2.sname
having average>=60;

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

-- 思路：查询成绩表中成绩不为0的学生sid，然后where判断学生表中的sid在其中
select * from student
where sid in (select distinct(sid) from sc where score is not NULL);

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

-- 思路：先对成绩表进行group by然后用学生表left join
select student.sid,student.sname,t1.cc,t1.ss from student left join
   (select sc.Sid,count(sc.cid) as cc,sum(sc.score) as ss from sc
   group by sc.sid) as t1
on student.sid=t1.sid;

-- 4.1 查有成绩的学生信息
-- in()适用于student表大于score表的情况
select * from student
where student.sid in
   (select sid from sc where score is not NULL);

-- exists()适用于score表大于student表的情况
select * from student
where exists (select sc.sid from sc where student.sid=sc.sid);

查询「李」姓老师的数量

select '李老师' as name,count(tname) as num from teacher
where tname like '李%';

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

-- 思路1：使用子查询
select * from student
where  sid in (
   select sid from sc where cid in(
      select cid from course,teacher where course.tid=teacher.tid and teacher.tname='张三'));
-- 思路2：多表联查
select student.* from student,sc,course,teacher
where student.sid=sc.sid
         and sc.cid=course.cid
      and course.tid=teacher.tid
      and teacher.tname='张三';

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

select * from student
where sid not in(
   select sc.sid from sc
   group by sc.sid
   having count(sc.cid)=(select count(cid) from course)
);

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

-- 思路：先求出学号为'01'同学的课程，然后成绩表sc中的课程cid在里面
select * from student
where student.sid in (
   select sc.sid from sc
   where sc.cid in(
      select sc.cid from sc
      where sc.sid = '01'
   )
);

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

-- 思路：否定之否定，等于肯定。先查询 只要学过的课程不在"01"同学课程中 的sid，那么其它的sid对应课程肯定都在01同学的课程中，最后限制数量相等
select * from sc
where sid in(
   select sid from sc
   where sid  not in(
      select sid from sc where cid not in (select cid from sc where sid='01')
   )
   group by sid
   having count(*)=(select count(*) from sc where sid='01')
);

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

-- 思路：查询有学过"张三"老师的课的学生sid，然后排除这些sid
select sid,sname from student
where sid not in(
   select sid from sc,course,teacher
   where sc.cid=course.cid
         and course.tid=teacher.tid
         and teacher.tname='张三'
);

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

-- 先查询大于2门不及格的学生sid，再联查其平均成绩和姓名
select t1.sid,t2.avgs,student.sname from
   (select sid from sc
   where score<60
   group by sid
   having count(*)>=2) as t1
left join(
   select sid,avg(score)as avgs from sc group by sid) as t2
on t1.sid=T2.sid
left join student
on t1.sid=student.sid;

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

select student.*,sc.score from sc,student
where sc.sid=student.sid and sc.cid='01' and sc.score<60
order by score DESC;

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

-- 思路：使用开窗函数over()
select sid,cid,score,
avg(score) over(partition by sid) as avgs
from sc;

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

-- 思路：直接使用max()/min()/avg()/sum()和case语句
/*
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，
优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
*/
select sc.cid,course.cname,max(score) as maxs,min(score)as mins,avg(score)as avgs,
sum(case when score>=60 then 1
      else 0 end)/count(*) as '及格率',
sum(case when score>=70 and score<80 then 1
      else 0 end)/count(*) as '中等率',
sum(case when score>=80 and score<90 then 1
      else 0 end)/count(*) as '优良率',
sum(case when score>=90 then 1
      else 0 end)/count(*) as '优秀率',
count(*) as '选修人数'
from sc left join course
on sc.cid=course.cid
group by sc.cid,course.cname
order by '选修人数' desc,sc.cid ;

按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select sc.*,rank() over(partition by cid order by score desc) as 'rank' from sc;
-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select sc.*,dense_rank() over(partition by cid order by score desc) as 'rank' from sc;
-- 15.2 按各科成绩进行排序，并显示排名， Score 重复时随机排名
select sc.*,row_number() over(partition by cid order by score desc) as 'rank' from sc;

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

select sc.sid,sum(score) as 'sums',rank() over(order by sum(score) DESC) as 'rank'
from sc
group by sid;
-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select sc.sid,sum(score) as 'sums',dense_rank() over(order by sum(score) DESC) as 'rank'
from sc
group by sid;

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

select sc.cid,course.cname,
sum(case when score<=60 then 1 else 0 end) as '[0-60]',
concat(round(sum(case when score<60 then 1 else 0 end)/count(*)*100,2),'%') as '[0-60]%',
sum(case when score>=60 and score<70 then 1 else 0 end) as '[70-60]',
concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(*)*100,2),'%') as '[70-60]%',
sum(case when score>=70 and score<85 then 1 else 0 end) as '[85-70]',
concat(round(sum(case when score>=70 and score<85 then 1 else 0 end)/count(*)*100,2),'%') as '[85-70]%',
sum(case when score>=85 then 1 else 0 end) as '[100-85]',
concat(round(sum(case when score>=85 then 1 else 0 end)/count(*)*100,2),'%') as '[100-85]%'
from sc left join course
on sc.cid=course.cid
group by sc.cid,course.cname;

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

select * from sc as a
where 3>(select count(*) from sc as b where a.cid=b.cid and a.score<b.score)
order by a.cid,a.score desc;
-- 采用rank()/dense_rank()/row_number()和over()
select * from
(select *,
row_number() over(partition by cid order by score DESC) as 'rank'
from sc)as t1 where t1.rank<4;

select * from
(select *,
rank() over(partition by cid order by score DESC) as 'rank'
from sc) as t1 where t1.rank<4;

select * from
(select *,
dense_rank() over(partition by cid order by score DESC) as 'rank'
from sc)as t1 where t1.rank<4;

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

select cid,count(*) as num
from sc
group by cid;

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

select sc.sid, student.sname
from sc left join student
on sc.sid=student.sid
group by sid,sname
having count(*)=2;

男生、女生人数

select ssex,count(*) as num
from student
group by ssex;

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

select * from student
where sname LIKE '%风%';

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

select * from student
where sname in(
   select sname from student group by sname having count(*)>1
);

查询 1990 年出生的学生名单

select * from student
where YEAR(Sage)=1990;

查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
```
select cid,avg(score) as avgs
from sc
group by cid
order by avg(score) DESC,cid;
```

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

select sc.sid,student.sname,avg(score) as 'avgs'
from sc left join student
on sc.sid=student.sid
group by sc.sid,student.sname
having avg(score)>=85;

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

select student.sname,sc.score,course.cname
from student,sc,course
where course.cname='数学'
      and course.cid=sc.cid
      and sc.sid=student.sid
      and sc.score<60;

查询所有学生的课程及分数情况

select * from student
left join sc
on student.sid=sc.sid;

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

-- 思路：有分数低于70的学生sid就排除
select student.sname,course.cname,sc.score
from   sc left join student
on sc.sid=student.sid
left join course
on sc.cid=course.cid
where sc.sid not in(select sid from sc where score<=70)
order by student.sname;

查询存在不及格的课程
```
select * from sc where score<60;
```

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

select sc.sid,student.sname
from sc left join student
on sc.sid=student.sid
where sc.cid='01' and sc.score>=80;

每门课程的学生人数

select cid,count(*) as 'Snum'
from sc
group by
cid;

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

select student.*,sc.score from sc,student,course,teacher
where teacher.tname='张三'
         and teacher.tid=course.tid
      and course.cid=sc.cid
         and sc.sid=student.sid
order by sc.score DESC
limit 1;

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

select student.*,sc.score,
rank() over(order by score DESC) as 'rank'
from sc,student,course,teacher
where teacher.tname='张三'
         and teacher.tid=course.tid
      and course.cid=sc.cid
         and sc.sid=student.sid;

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

select distinct t1.sid,t1.cid,t1.score from sc as t1
inner join sc as t2
on t1.sid=t2.sid
where t1.score=t2.score and t1.cid<>t2.cid;

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

-- 感觉用dense_rank()更公平一些，并列算一个人，排名连续。
select * from(
   select *,
   dense_rank() over(partition by cid order by score DESC) as 'rank'
   from sc) as t1
where t1.rank<3;

select * from(
   select *,
   rank() over(partition by cid order by score DESC) as 'rank'
   from sc) as t1
where t1.rank<3;

统计每门课程的学生选修人数（超过 5 人的课程才统计）
```
select cid,count(*) as 'Snum'
from sc
group by cid
having count(*)>5;
```

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

select sid,count(*) as 'Cnum'
from sc
group by sid
having count(*)>2;

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

select * from student
where sid in(
   select sid from sc
   group by sid
   having count(sc.cid)=(select count(course.cid) from course)
   );

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

select student.*,YEAR(NOW())-YEAR(Sage) as 'age' from student;

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

select student.*,
(
CASE WHEN MonTH(NOW())<MonTH(Sage) OR(MonTH(NOW())=MonTH(Sage) and DAY(NOW())<MonTH(Sage)) THEN YEAR(NOW())-YEAR(Sage)-1
       ELSE YEAR(NOW())-YEAR(Sage)
END
)as 'age'
from student;

查询本周过生日的学生

select * from student
where WEEKOFYEAR(NOW())=WEEKOFYEAR(Sage);

查询下周过生日的学生

select * from student
where WEEKOFYEAR(NOW())+1=WEEKOFYEAR(Sage);

查询本月过生日的学生

select * from student
where MonTH(NOW())=MonTH(Sage);

查询下月过生日的学生

select * from student
where MonTH(NOW())+1=MonTH(Sage);

最后编辑于：2021.11.15 14:36:56

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,445评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,889评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,047评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,760评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,745评论 5赞 367
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,638评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,011评论 3赞 398
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,669评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,923评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,655评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,740评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,406评论 4赞 320
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,995评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,961评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,197评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,023评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,483评论 2赞 342

2021-11-15 MySQL 查询练习

推荐阅读更多精彩内容