Student表：
select * from student;
课程表Course：
select * from course;
教师表teacher：
select * from teacher;
成绩表Score：
select * from sc;
1、查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select c.*, a.score
from sc a, sc b, student c
where a.sid=b.sid and c.sid=a.sid and a.cid=1 and b.cid=2 and a.score>b.score; #笛卡尔积形式的连接，不能加上a.cid=b.cid
2、查询同时选" 01 "课程和" 02 "课程的学生
select a.sid
from sc a, sc b
where a.sid=b.sid and a.cid=1 and b.cid=2;
3、查询选修了" 01 "课程但可能没有选修" 02 "课程的学生情况(不存在时显示为 null )
方法一：
select * from
(select * from sc where cid=1) a
left join
(select * from sc where cid=2) b
on a.sid=b.sid;
方法二：
select *
from student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where b.score is not null;
运行结果：没有where子句时，中间表的连接结果如下图，题目要求选修了1号课程，可能没有选修2号课程，所以根据下图的结果，可以得知，只要把选修了1号课程的同学查询出来即可，即where子句中的条件为： b.score is not null
方法三： 这种方法和方法二相同，只是where 子句有所不同，如果想使用方法三，where子句的条件需要修改为b.score>= isnull(c.score)，
原因是b.score>isnull(c.score)，
假定c.score是null，isnull(c.score)返回的结果为1，那么当b.score=1时，此时b.score>isnull(c.score)不成立，那么这一条记录就会被过滤掉
select a.* , b.score b_score ,c.score c_score from Student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where b.score > = isnull(c.score); #,注意，是>=， 这一句，目的是为了筛选出b表中不为null的成绩，因为isnull(c.score)返回结果只有0和1两种情况
思路解析：
select a.* , b.score b_score ,c.score c_score from Student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02';
isnull( )使用说明：
4、查询不存在" 01 "课程但存在" 02 "课程的情况
思路：选修2课程的id号课程中，剔除选修了1课程的id 号
select distinct sid
from sc
where cid=2 and sid not in
(select sid
from sc
where cid=1
);
7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select student.sid, student.sname, count(sc.cid) as "选课总数", sum(sc.score) as "总成绩"
from student left join sc on student.sid=sc.sid
group by student.sid ;
8、查询「李」姓老师的数量
#（例子：查询以s开头的老师数量）
select count(*) from teacher where tname like 's%';
9、查询学过「张三」老师授课的同学的信息
（例子：查询学过 jane 老师授课的学生信息）
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='jane';
中间表：
select *
from student, sc, course, teacher
where student.sid=sc.sid and sc.cid=course.cid and course.tid=teacher.tid ;
10、查询没有学全所有课程的同学的信息
select student.*
from sc , student
where student.sid=sc.sid
group by sc.sid
having count(sc.cid)<(select count(*) from course); # 查询课程表course中有多少门课
11、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
ps:把1号同学排除
select distinct student.*
from student, sc
where student.sid=sc.sid and sc.sid!=1 and sc.cid in (
select distinct cid from sc where sid=1);
12、查询和" 01 "号同学学习的课程 完全相同的其他同学的信息
正确代码：
此时SC表如图：可以看到，将sid分组的情况下，除6号同学外，每个同学的成绩记录都是按照cid增序的顺序插入的；只有6号同学的课程插入顺序是1,3,2
上面这张表不太容易看，对sid按增序排序后，如下图，查看每个同学cid插入的顺序如何，注意：只对sid排序，对cid排序后就不能看出来每个同学cid插入的顺序如何了。
排序后，可以更清楚的看到只有6号同学的课程插入顺序是乱的，不是按照cid增序插入的。
完全正确的代码：
PS：不仅使用了group_concat( )函数，还对group_concat( )函数中的参数进行了排序。
select student.* from (select sid, group_concat(cid order by cid) as tt from sc where sid=1 group by sid order by cid ) a #1号同学选修的课程 #这里对cid进行了排序，因为group_concat()函数连接cid的值时，默认是按照该值在SC表中的插入顺序进行连接的 left join ( select sid, group_concat(cid order by cid) as rr from sc where sid!=1 group by sid order by cid ) b on a.tt=b.rr # #除1号同学外，其他同学选修的课程，将两张表进行连接，按照选修课程相同为条件进行连接 join student on b.sid=student.sid;
运行结果：
下面代码也有漏洞：group _concat( cid)中不对cid排序，则返回结果如下：
select student.* from (select sid, group_concat(cid ) as tt from sc where sid=1 group by sid order by cid ) a left join ( select sid, group_concat(cid ) as rr from sc where sid!=1 group by sid order by cid ) b on a.tt=b.rr join student on b.sid=student.sid;
运行结果：只查询出了4号同学
必须要排序的原因： 可以看到，1，4，6号同学选的课程都一样，只是6号同学课程插入时是乱序的，结果造成下面这种情况，连接的结果也是按插入顺序连接的，这样，系统认为1号同学和6号同学的课程是不一样的，所以group_concat( cid )中要对cid排序，即使用group_concat( cid order by cid)
select sid, group_concat(cid) as '选修课程' from sc group by sid;
运行结果：
总结：
按同学分组后，如果每个同学的课程插入记录是顺序增加或顺序降低的，则使用group_concat( )函数时不对参数进行排序，没有问题。
但如果每个同学的课程插入记录是乱序的，则使用group_concat( )函数时必须对参数进行排序。
错误代码： 下面的代码看似正确，其实有漏洞，很有迷惑性
分析： 假定一共有4门课，课程号cid=1,2,3,4； SC表如下，其中1同学、4同学、6同学都选修了1,2,3这三门课，不存在选修了全部课程的同学，这种情况下，使用下面代码解决这道题目没有问题，但是
假定表的情况按上述情况，运行下面代码，返回的结果看似是正确的：
select student.*
from sc ,student
where student.sid=sc.sid and sc.cid in ( select cid from sc where sid=1) and student.sid!=1
group by sc.sid
having count(*) in (select count(*) from sc where sid=1);
运行结果：
举一个例子揭示上述代码的漏洞：
在上面的那张表中，插入了一条记录，结果就是6号同学选修了全部课程，那么此时，只有4号同学和1号同学选修的课程完全相同
同样运行上面代码，查看运行结果：
select student.*
from sc ,student
where student.sid=sc.sid and sc.cid in ( select cid from sc where sid=1) and student.sid!=1
group by sc.sid
having count(*) in (select count(*) from sc where sid=1);
运行结果： 仍然把6号同学给选了出来，此时，正确的结果应当只有第一条
13、查询没学过"张三"老师讲授的任一门课程的学生姓名
（例子：smith老师）
思路：先把学过smith老师课程的学号选出来，然后从student表中剔除这些学号
select student.*
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='smith');
14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
A：查询出两门以上不及格的学号
select sid
from sc
where score<60
group by sid
having count(*)>=2;
A：完整结果
个人理解：我理解的平均成绩某个同学所有选修课程的平均成绩，而不是不及格课程的平均成绩。
例如，2号同学选了3门课，有2门不及格，平均成绩是指3门的平均成绩，而不是2门的。
select student.sid, student.sname, avg(sc.score)
from (student join sc on student.sid=sc.sid ) join
(
select sc.sid as esid
from sc
where sc.score<60
group by sc.sid
having count(*)>=2) a
on student.sid=a.esid
group by student.sid
order by student.sid;
运行结果：
15、检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select student.*
from sc,student
where cid=1 and score<60 and sc.sid=student.sid
order by score desc;
16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sc.*, a.avgscore
from sc left join (select sid, avg(score) as avgscore from sc group by sid) a on sc.sid=a.sid
order by a.avgscore desc;
17、查询各科成绩最高分、最低分和平均分
select cid, max(score), min(score), avg(score)
from sc
group by cid;
18、以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.cid, course.cname,count(sc.sid) as '选修人数', max(sc.score), min(score), avg(score),
(sum(case when score>=60 then 1 else 0 end)/count(sc.sid)) as '及格率',
(sum(case when score>=70 and score<80 then 1 else 0 end) /count(sc.sid)) as '中等率',
(sum(case when score>=80 and score<90 then 1 else 0 end)/count(sc.sid)) as '优良率',
(sum(case when score>=90 then 1 else 0 end)/count(sc.sid)) as '优秀率'
from sc, course
where sc.cid=course.cid
group by sc.cid
order by count(sc.sid) desc, cid ; #按选修人数降序排列，如果人数相同，按课程号cid升序排列
19、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
思路：这里不能使用a.score<=b.score
select a.sid ,a.cid, a.score, count(a.score<b.score)+1 as rank
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, a.score desc;
20、按各科成绩进行排序，并显示排名， Score 重复时合并名次
思路：看某个分数在所有分数中，小于等于的有几个
select a.sid ,a.cid, a.score, count( distinct b.score)+1 as rank
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, a.score desc;
或者 如果是a.score<=b.score，则count( distinct b.score) 即可，不用加1
select a.sid ,a.cid, a.score, count( distinct b.score) as rank
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, a.score desc;
21、查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select a.* ,count(a.cj<b.cj)+1 as rank
from
(select sid , sum(score) as cj from sc group by sid) a
left join
( select sid, sum(score) as cj from sc group by sid) b
on a.cj<b.cj
group by a.sid
order by a.cj desc
;
22、 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select a.* ,count(distinct b.cj)+1 as rank
from
(select sid , sum(score) as cj from sc group by sid) a
left join
( select sid, sum(score) as cj from sc group by sid) b
on a.cj<b.cj
group by a.sid
order by a.cj desc
;
23、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select sc.cid,cname,count(sid) as '总人数',
concat(round(sum(case when score>=0 and score<60 then 1 else 0 end) /count(sid)*100,2),'%') as '0-60',
concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(sid)*100,2),"%") as '60-70',
concat(round(sum(case when score>=70 and score<85 then 1 else 0 end)/count(sid)*100,2),"%") as '70-85',
concat(round(sum(case when score<=100 and score>=85 then 1 else 0 end )/count(*)*100,2),'%') as '85-100'
from sc join course on sc.cid=course.cid
group by cid;
24、查询各科成绩前三名的记录
思路：先将各科成绩进行排序（这里应该是不保留名次空缺），然后从成绩表SC中选出排名<=3的记录
select * from
(
select a.*, count(distinct b.score) +1 as rank
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, a.score desc) c
where c.rank<=3
;
25、查询每门课程被选修的学生数
select cid, count(*) as '选修人数'
from sc
group by cid;
26、查询出只选修两门课程的学生学号和姓名
select sc.sid, student.sname
from student, sc
where student.sid=sc.sid
group by sc.sid
having count(sc.cid)=2;
27、查询男生、女生人数
select ssex as '性别' , count(*) as '人数'
from student
group by ssex;
28、查询名字中含有「风」字的学生信息
select * from student where sname like '%m%'; #这个是查询姓名含有m的学生信息，m在开头或者中间位置都可以
select * from student where sname like 'm%'; #这个是查询姓名以m开头的学生信息
29、查询同名同姓学生名单，并统计同名人数
select sname , count(*) as '人数'
from student
group by sname;
30、查询 1990 年出生的学生名单
select * from student where sage like "1990%" ;
31、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid, avg(score) as '平均成绩'
from sc
group by cid
order by avg(score) desc, cid;
32、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select student.sid, student.sname, a.pj
from student join
(select sid, avg(score) as 'pj'
from sc
group by sid
having avg(score)>=85) a
on student.sid=a.sid ;
33、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select student.sname, score
from student, sc, course
where student.sid=sc.sid and sc.cid=course.cid and score<60 and cname='math';
34、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select student.sid,student.sname, sc.cid,sc.score
from student left join sc on student.sid=sc.sid
order by student.sid;
| 1 | mark | 1 | 80 |
| 1 | mark | 3 | 95 |
| 1 | mark | 2 | 90 |
| 2 | james | 2 | 39 |
| 2 | james | 3 | 39 |
| 2 | james | 4 | 94 |
| 3 | michael | 1 | 93 |
| 3 | michael | 3 | 87 |
| 4 | jack | 2 | 74 |
| 4 | jack | 1 | 89 |
| 4 | jack | 3 | 83 |
| 5 | mei | 1 | 76 |
| 5 | mei | 2 | 47 |
| 6 | lily | 4 | 44 |
| 6 | lily | 1 | 41 |
| 6 | lily | 3 | 83 |
| 7 | nana | 3 | 83 |
| 7 | nana | 2 | 89 |
| 8 | mifei | NULL | NULL |
| 9 | cry | NULL | NULL |
35、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select sname, cname ,score
from (sc join student on sc.sid=student.sid) join course on sc.cid=course.cid
where score>70
order by sc.sid;
36、查询不及格的课程
select course.cname, sc.score
from sc join course on sc.cid=course.cid
where sc.score<60;
37、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select student.sid, student.sname
from student ,sc
where student.sid=sc.sid and sc.cid=1 and sc.score>80
order by cid ;
38、求每门课程的学生人数
select cid, count(*) as '该课程的选修人数'
from sc
group by cid;
39、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
40、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
PS：这两道题，使用下面同样的代码都能运行出正确结果
select student.*, a.score from sc a left join sc b on a.cid=b.cid and a.score<b.score #对每门课程下的学生成绩排名 join course on a.cid=course.cid join teacher on course.tid=teacher.tid and tname='张三' #这一行和上一行，是为了筛选张三老师教的课 join student on a.sid=student.sid # 获取学生信息 group by a.cid, a.sid having count( a.score< b.score)=0 #筛选名次rank=1, count(a.score<b.score)+1=1，两端减去1，简化为count(a.score<b.score)=0 #上面这一行having 子句，这种格式也可以：having count( distinct b.score)=0 ,使用distinct ;
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select sc.cid , sc.sid, sc.score from sc join (select cid, score from sc group by cid, score having count(sid)>=2) a on sc.cid=a.cid and sc.score=a.score order by cid ;
或
select sc.sid, sc.cid , sc.score from sc join (select sid, score from sc group by sid, score having count(cid)>=2) a on sc.sid=a.sid and sc.score=a.score order by sid;
42、查询每门功成绩最好的前两名
思路：先对每门课程下的成绩排序，然后筛选排名<=2的
select a.cid, a.sid, sname, a.score, count(distinct b.score)+1 as 'rank'
from ( sc a left join sc b on a.cid=b.cid and a.score<b.score) join student on a.sid=student.sid
group by a.cid, a.sid
having rank<=2
order by cid
;
43、统计每门课程的学生选修人数（超过 5 人的课程才统计）
select sc.cid,cname,count(sid) as '选修人数'
from sc join course on sc.cid=course.cid
group by sc.cid
having count(sid)>4;
44、检索至少选修两门课程的学生学号
select sid
from sc
group by sid
having count(cid)>=2;
45、查询选修了全部课程的学生信息
select student.*
from sc join student on sc.sid=student.sid
group by sid
having count(*) in (
select count(*) from course);
计算年龄，只按年份来算和按月日来算的区别
46、查询各学生的年龄，只按年份来算
select student.sid, student.sname,student.ssex, year(now())-year(student.sage) as 'age'
from student;
47、按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select student.sid, student.sname,student.ssex, sage,
timestampdiff(year,sage,now()) as '按月日计算', # 出生月日< 当前日期的月日时，年龄会减一 ,该题目功能是通过这一句实现的，下一句只是为了对比说明两者之间的差别
year(now())-year(sage) as '按年份计算'
from student;
48、查询本周过生日的学生
select * from student where week(concat_ws('-',year(now()),date_format(sborn,'%m-%d')))=week(now());
49、查询下周过生日的学生
select * from student where week(concat_ws('-',year(now()),date_format(sborn,'%m-%d')))=week(now())+1;
50、查询本月过生日的学生
select *
from student
where month(student.sage)=month(now());
51、查询下月过生日的学生
select *
from student
where month(student.sage)=month(now())+1;