原文作者：shirely
首先，创建表。一共有4张表。分别是学生表，课程表，教师表，成绩表 。
学生表Student
4个字段，SId（学生ID）,Sname(学生姓名)，Sage(学生年龄)，Ssex(学生性别)
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10),PRIMARY key(SId));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
教师表Teacher
2个字段，TId（教师id），Tname（教师姓名）
create table Teacher(TId varchar(10),Tname varchar(10),PRIMARY key (Tid));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
课程表Course
3个字段，CId(课程id)，Cname（课程名称），TId（教师id）
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10),PRIMARY KEY(CId));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
成绩表SC
4个字段，skey（由于没有不重复的字段，因此创建了主键字段），SId(学生id)，CId(课程ID)，score（学生成绩）
create table SC(skey int ,SId varchar(10),CId varchar(10),score decimal(18,1),PRIMARY key(skey));
insert into SC values('1','01' , '01' , 80);
insert into SC values('2','01' , '02' , 90);
insert into SC values('3','01' , '03' , 99);
insert into SC values('4','02' , '01' , 70);
insert into SC values('5','02' , '02' , 60);
insert into SC values('6','02' , '03' , 80);
insert into SC values('7','03' , '01' , 80);
insert into SC values('8','03' , '02' , 80);
insert into SC values('9','03' , '03' , 80);
insert into SC values('10','04' , '01' , 50);
insert into SC values('11','04' , '02' , 30);
insert into SC values('12','04' , '03' , 20);
insert into SC values('13','05' , '01' , 76);
insert into SC values('14','05' , '02' , 87);
insert into SC values('15','06' , '01' , 31);
insert into SC values('16','06' , '03' , 34);
insert into SC values('17','07' , '02' , 89);
insert into SC values('18','07' , '03' , 98);
4个表建好了，那么开工。
1.求每门课程的学生人数。
select Course.cname'课程名称',count(*)'人数' from SC,Course where SC.CId=Course.CId
GROUP BY SC.CId
前面几道题都是蛮简单的，因此不多说。
2.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
select a.sid,a.sname from Student a ,SC b where a.sid=b.sid
and b.cid='01' and b.score >=80
3.统计每门课程的学生选修人数（超过 5 人的课程才统计）
select b.cname,count(*) from SC a ,Course b where a.cid = b.cid
group by a.cid
HAVING count(*)>5
考察的是having
4.检索至少选修两门课程的学生学号
select sid from sc
GROUP BY sid
having count(cid)>2
还是一样，还是having，而且还是一个表内，比较基础。
5.选修了全部课程的学生信息
select a.* from Student a ,sc b where a.sid = b.sid
GROUP BY a.sid
having count(cid) = (select count(*) from Course)
条件从第三张额外表取。
6 .查询存在不及格的课程
正确用法
select DISTINCT Course.Cname from sc ,Course where sc.cid = Course.cid
and sc.score < 60
在sql中DISTINCT只能对目标字段进行出重。
错误用法
select DISTINCT * from sc ,Course where sc.cid = Course.cid
and sc.score < 60
7.查询任何一门课程成绩在 70 分以上的学生姓名、课程名称和分数
select a.sname,b.cname,c.score from Student a ,Course b ,sc c
where a.sid = c.sid and c.cid = b.cid
and c.score>70
就是3张表联结，任意一门，就是只要有一门score>70分的就筛选出来。条件也蛮简单的。
8.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select a.sname,b.cname,c.score from student a left join sc c
on a.sid=c.sid
left join course b
on b.cid = c.cid
左联结即可。
9.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select a.sname,b.score from Student a ,sc b,course c where
a.sid = b.sid
and c.cid = b.cid
and b.score<60
and c.cname='数学'
三表联结，设置条件即可。
10.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select a.sid,a.sname,avg(b.score)'平均成绩'from Student a ,sc b
where a.sid = b.sid
GROUP BY a.sid
having avg(b.score)>=85
2表联结，增加了函数avg的用法 。
11.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid,avg(score) from sc
GROUP BY cid
order by avg(score) desc,cid asc
每门课程的平均成绩，就是按课程来groupby，同时增加排序，desc为降序，asc为升序
12.查询各科成绩最高分、最低分和平均分
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.cid,Course.Cname,max(sc.score),min(sc.score),AVG(sc.score),count(sc.SId),
sum(case when score <60 then 1 else 0 end)/count(sc.cid) '不合格率',
sum(case when score <80 and score >=60 then 1 else 0 end)/count(sc.cid) '合格率',
sum(case when score <90 and score >=80 then 1 else 0 end)/count(sc.cid) '优良率',
sum(case when score >= 90 then 1 else 0 end)/count(sc.cid) '优秀率'
from sc ,Course
where sc.cid = Course.cid
GROUP BY sc.cid
case when 的用法
13.查询男生、女生人数
select ssex ,count(*) from Student GROUP BY ssex
14.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select a.* from student a ,sc b where a.sid = b.sid
and b.cid='01'
and b.score<60
order by score desc;
2表关联+排序
15.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
错误的
select score,sid,avg(sid)from sc
GROUP BY sid
原来是这么写的，后来发现报错了，原因是sql_mode=only_full_group_by。不过仔细考虑一下，score有多个数据，而avg只有一个数字，匹配数就不一样。可以换个思路，整2个表联结就好了。
正确是：
select a.sid,a.score, b.`平均成绩` from sc a right join (select sid,avg(score) '平均成绩'from sc group by sid) b
on a.sid = b .sid
ORDER BY b.`平均成绩` desc
16.查询没学过"张三"老师讲授的任一门课程的学生姓名
错误的
select distinct a.sname from Student a ,Teacher b ,Course c ,sc d
where a.sid=d.sid
and d.cid = c.cid
and b.tid=c.tid
and b.Tname!='张三'
刚开始写成4表联结了，后来发现不大对，如果只是去掉tname不等于张三的，那么如果选2门课的同学（包含张三课程的），也会被包含进去。因此正确的思路是取not in。
正确的
select student.sname from Student where Student.sid not in
(select sc.sid from teacher,course ,sc
where sc.cid=Course.cid and
teacher.tid=course.tid and
Teacher.tname='张三')
17.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.* ,b.score from student a ,sc b ,teacher c,Course d
where a.sid=b.sid
and c.tid = d.tid
and d.cid = b.cid
and c.tname = '张三'
order by b.score desc
limit 1
4表联结，降序，取第一个。成绩不重复的情况下。
18.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
需要先更改下数据库的数据，因为目前没有相同成绩的同学。
UPDATE sc SET score=90
where skey=17
select a.* ,b.score from student a ,sc b ,teacher c,Course d
where a.sid=b.sid
and c.tid = d.tid
and d.cid = b.cid
and c.tname = '张三'
and b.score = (select max(b.score) from student a ,sc b ,teacher c,Course d
where a.sid=b.sid
and c.tid = d.tid
and d.cid = b.cid
and c.tname = '张三')
找到最高分数，然后分数等于这个数的人找出来即可。
感觉这题不大符合实际情况，一般会先查同个分数段的有多少人，然后再看你要哪个段位的学生人数。这比较方便。
19.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select any_value(a.cid), any_value(a.sid), any_value(a.score) from sc as a
inner join
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
GROUP BY a. cid, b.sid;
这道题目蛮有意思的。需要注意的有2点。
1.自联结。自己联结自己。
2.还是之前那个错误，sql_mode=only_full_groupby。我不知道是不是我电脑是mac的原因，搞了半天还是没解决错误。因此参考了网上另一种解决办法，就是用any_value（字段）来取代原先的字段。这样不会报错。
20.查询每门功成绩最好的前两名
select any_value(a.sid),any_value(a.cid),any_value(a.score) from sc a left join sc as b
on a.cid = b.cid
and a.score < b.score
group by a.cid,a.sid
having count(b.score)<2
order by a.cid
这题也是自己联结自己。这道题比较需要注意是，要注意思维的转化。当比你分数高的人数少于2个，就是只有一个人时，你就是第一名。同时需要注意的是，你以哪个表为主，就需要那个表作为主表，其他表去联结它。
21.查询每门课程被选修的学生数
select count(*),cid from sc group by cid
22.查询出只选修两门课程的学生学号和姓名
select a.SId,a.Sname from student a ,sc b where a.sid = b.sid
GROUP BY a.sid
having count(*)=2
2个表联结，然后count数量等于2 即可。
23.查询同名学生名单，并统计同名人数
select count(*) from Student group by Sname
having count(*)>1
24.查询 1990 年出生的学生名单
select * from Student where year(Sage)=1990
考察year的用法
25.查询各学生的年龄
select SId,Sname,TIMESTAMPDIFF(year,Sage,CURDATE()) from student
timestampdiff的用法。timestampdiff（参数1，参数2，参数3）。参数1 可以选择小时hour，秒second，月month，年year。参与2，比较的时间数据（小的哪一个），参数3，比较的时间数据（大的那一个）。
26.查询本周过生日的学生
select * from student where
week(curdate())=week(Sage)
27.查询本月过生日的学生
select * from student where
month(curdate())=month(Sage)
28.查询「李」姓老师的数量
select count(*) from Teacher where Tname like '李%'
29.查有成绩的学生信息
select * from Student where sid in (select sc.sid from sc)
30.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
select a.SId,a.Sname,count(b.cid),sum(b.score) from student a left join sc b on a.sid = b.sid
GROUP BY a.sid
这题要注意的是，需要把student表作为主表，sc去关联它，不然没有成绩的学生就不会出来。
31.查询在 SC 表存在成绩的学生信息
select * from student where sid in (select sc.sid from sc)
这题不是和29题一样？？
32.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.sid,a.Sname,AVG(b.score) from Student a ,SC b
where a.SId = b.sid
GROUP BY a.sid
having AVG(b.score)>60
跟前面有一些题目类似，不多讲。
33.查询不存在" 01 “课程但存在” 02 "课程的情况
select * from sc where cid = '02' and sid not in (select sid from sc where cid='01')
也是需要用到 not in 的思维。当cid=02时，并且sid 并不在cid=01是选取出来。
34.查询存在" 01 “课程但可能不存在” 02 "课程的情况
select * from sc where cid ='01'
可能不存在，那就是说，可能有02课程也可能没有，那只要把选则01课程的选中即可。
35.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select a.cid ,a.sid ,any_value(a.score),count(b.sid)+1 from sc a left join sc b
on a.cid=b.cid
and a.score<b.score
GROUP BY a.cid,a.sid
order by a.cid ,count(b.sid)+1
我觉得这题也有点绕。反正自己联结自己的都比较难以理解。
首先，再做这种比较题目的时候，要确定哪个作为主表。我把a表作为主表，然后b表去交他，从b表中找到有几个人比他的分数大，把人数count一下，加上1就是a表这个 学生的名次，顺次count下去 ，数据一个都不会漏。
36.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select c.* ,a.score,b.score from student c ,
(select score,sid from sc where cid = '01')a,
(select score,sid from sc where cid = '02')b
where a.sid = b.sid
and c.sid=b.sid
and a.score >b.score
就是建了2个新表，一个是01课程的表，一个是02课程的表。然后3张表关联。
37.查询学过「张三」老师授课的同学的信息
select a.* from Student a ,Teacher b ,Course c ,sc d
where a.sid=d.sid
and d.cid = c.cid
and b.tid=c.tid
and b.Tname='张三'
和前面有题没有学过张三老师课程的学生 相似。
38.查询没有学全所有课程的同学的信息
select a.* from Student a left join sc b
on a.sid = b.sid
group by a.SId
having count(b.cid)<(select count(cname) from Course)
这题的关键是 要 左联结，student作为主表， 不然那些没选课的同学要漏了。
39.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select a.* from student a ,sc b where a.sid = b.sid and b.cid in
(select cid from sc where sid ='01')
group by a.sid
首先找出 课程01 同学所学的课程，然后sc和 student 关联，找出其cid在01同学所在的一门课程里即可。
40.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select * from Student 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(cid) from sc where sid ='01')and sid<>'01')
这题是真绕。看了网上很多答案，感觉这个比较清晰，比较好理解。
首先，筛选出01同学的课程——选出有谁没有选择01同学课程的学生sid——再对上一层逻辑做否定，选择了01号同学的子集或者含有01号同学所选课程之外的含有其他课程的 同学。因此最后再加上判断条件，课程数量相等。就可以判断是和01号同学学习相同的课程。
2472
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
