Log：2020-6-24 23:18 三刷，并重新整理了下题目
PS：练习过程中，为了更好地查看查询效果，会对数据做一些修改符合题目，请注意
建表的过程
create table student(
sid int not null primary key,
sname varchar(20) not null,
sborn date,
ssex varchar(20) not null);
create table course(
cid int not null primary key,
cname varchar(20) not null,
tid int not null);
create table teacher(
tid int not null primary key,
tname varchar(20));
create table sc(
sid int not null,
cid int not null,
score int not null,
primary key( sid, cid) );
插入数据到student表
insert into Student values(1 , '赵雷' , '1990-01-01' , '男');
insert into Student values(2 , '钱电' , '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('08' , '张三' , '2017-12-20' , '女');
insert into Student values('9' , '李四' , '2017-12-25' , '女');
insert into Student values('10' , '李四' , '2012-06-06' , '女');
insert into Student values('11' , '赵六' , '2013-06-13' , '女');
insert into Student values('12' , '孙七' , '2014-06-01' , '女');
insert into Course values('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03'),(4,'物理',4);
insert into Teacher values('01' , '张三'),('02' , '李四'),('03' , '王五'),(4,'孙杨');
insert into SC values('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
(1,4,46),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
(2,4,76),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
(4,4,87),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
(6,4,93),
('07' , '02' , 89),
('07' , '03' , 98);
1、查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select student.*,a.score
from (sc a join sc b on a.sid=b.sid
and a.cid=1
and b.cid=2
and a.score>b.score)
join student on a.sid=student.sid;
或
select student.* , a.score from (sc a left join sc b on a.sid=b.sid) inner join student on a.sid=student.sid where a.cid=1 and b.cid=2 and a.score>b.score;
2、查询同时选修" 01 "课程和" 02 "课程的学生情况
select student.* from sc left join student on sc.sid=student.sid where cid=1 or cid=2 #注意这里只能用or，不能用and group by sc.sid having count(cid)=2;
或
select student.*
from (sc a join sc b on a.sid=b.sid and a.cid=1 and b.cid=2) join student on a.sid=student.sid;
3、查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
#思路:题目的意思是选修1号课程的学生必须全部列出来，同时查看一下选修了1号课程的学生中有哪些选修了2号课程
select * from
(select * from sc where cid=1 ) a left join (select * from sc where cid=2) b on a.sid=b.sid ;
4、查询不存在" 01 "课程但存在" 02 "课程的情况
select a.sid from
(select sid from sc where cid=2 ) a left join (select sid from sc where cid=1) b on a.sid=b.sid
where b.sid is null;
5、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select distinct sid
from sc
where cid in (select cid from sc where sid=1) and sid!=1;
OR
select distinct t2.sid
from
(select * from sc where sid=1 ) t1 left join (select * from sc where sid<>1) t2
on t1.cid=t2.cid;
6、查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
PS：使用了group_concat( )函数，将分组后指定字段的值连接起来，感觉使用时最好对连接的字段排序，以免出错
select student.*
from
(select sid, group_concat(cid order by cid) as tt from sc where sid=1 group by sid) a #1号同学选修的课程
#为了避免cid插入顺序引起的group_concat()结果有差异，所以对cid进行了排序
left join
( select sid, group_concat(cid order by cid) as rr from sc where sid!=1 group by sid) b on a.tt=b.rr
#除1号同学外，其他同学选修的课程，将两张表进行连接，按照选修课程相同为条件进行连接
join student on b.sid=student.sid;
中间过程解析：
select sid, group_concat(cid order by cid) as '1号同学选课情况' from sc where sid=1 group by sid
运行结果：
select sid, group_concat(cid order by cid) as '除1号同学的选课情况' from sc where sid!=1 group by sid;
运行结果：
7、检索" 01 "课程分数小于 60的学生，按分数降序排列学生
select student.*
from sc join student on sc.sid=student.sid
where sc.cid=1 and sc.score<60
order by sc.score desc;
8、按平均成绩降序查询所有学生的课程成绩，按如下形式显示：学号、姓名、课程名、总成绩、课程数、平均成绩
select sid, sum(score) as '总成绩', avg(score) as '平均成绩' from sc group by sid order by 平均成绩 desc; select student.sid as '学号',student.sname as '姓名', max(case when cname='语文' then sc.score else NULL end) as '语文', #已经按学号分组了，所以每组就是每个同学的所有课程成绩 max(case when cname='数学' then sc.score else NULL end ) as '数学', max(case when cname='英语' then sc.score else NULL end) as '英语', max(case when cname='物理' then sc.score else NULL end) as '物理', max(case when cname='音乐' then sc.score else NULL end) as '音乐', sum(score) as '总成绩', count(sc.cid) as '选课数', round(avg(score),2) as '平均成绩' from (student left join sc on student.sid=sc.sid) left join course on sc.cid=course.cid group by sc.sid ,student.sname order by avg(score) desc;
9、查询「李」姓老师的数量
select count(tid) as '姓李的老师个数'
from teacher
where tname like '李%' ;
PS：这里的%可以指代多个字符
10、查询名字中含有「风」字的学生信息
select *
from student
where sname like '%风%';
或使用正则表达式 REGEXP
select *
from student
where sname regexp '风';
运行结果：
11、查询学过「张三」老师授课的同学的信息
select student.* from (( sc left join course on sc.cid=course.cid ) left join teacher on course.tid=teacher.tid) left join student on sc.sid=student.sid where tname="张三" group by sc.sid
12、查询没学过"张三"老师讲授的任一门课程的学生姓名
#差集, SC表里的所有学生-选修了张三老师课程的学生,即为没有选修过张三老师任一课程的学生
select student.*
from
(select distinct sid from sc ) a left join #SC表全部的学号，去重
(select sc.sid from ((sc left join course on sc.cid=course.cid) left join teacher on course.tid=teacher.tid)
where teacher.tname='张三') b
on a.sid=b.sid
left join student on a.sid=student.sid
where b.sid is null;
或
select distinct sid
from sc
where sc.sid not in
(select sid
from ( sc left join course on sc.cid=course.cid ) left join teacher on course.tid=teacher.tid
where tname="张三") ;
13、查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select sid,score from sc where score= (select max(sc.score) from ( sc left join course on sc.cid=course.cid) left join teacher on course.tid=teacher.tid where tname="张三") ;
14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
PS：不明白它的这个平均成绩，假如1号同学选了4门课，其中有3门不及格，那么这个平均成绩是指这4门的，还是指这3门不及格的平均成绩
下面两种方法都可以选出不及格的同学，但是计算的平均成绩有所不同
一：不及格同学的所有选修课平均成绩
#这种方法计算的是每个同学所有选修课的平均成绩 select student.sid, student.sname, avg(sc.score) as '平均分' from sc join student on sc.sid=student.sid group by sc.sid having sum(sc.score<60)>=2; # 注意:使用的是sum()函数，不能使用count()函数
如果想用count，括号里需写成形式为 count(case when score<60 then 1 else null end)
二：不及格同学的选修课中，不及格部分课程的平均成绩
#这种方法计算的是不及格同学中所选课程，不及格部分的平均成绩 select student.sid, student.sname, avg(sc.score) as '平均分' from sc join student on sc.sid=student.sid where sc.score<60 # 先筛选出不及格的 group by sc.sid having count(sc.cid)>=2; #这里sum， count都可以
15、以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
PS：round( )函数将小数保留2位，concat( )函数连接字符串
select sc.cid, course.cname, max(sc.score) as '最高分', min(sc.score) as '最低分', round(avg(sc.score),2) as '平均分', count(sc.sid) as '人数', concat(round(sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end) /count(sc.sid)*100,2),'%') as '及格率', concat(round(sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end)/count(sc.sid)*100,2),'%') as '中等率', concat(round(sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count(sc.sid)*100,2),'%') as '优良率', concat(round(sum(case when sc.score>=90 then 1 else 0 end )/count(sc.sid)*100,2),'%') as '优秀率' from sc join course on sc.cid=course.cid group by sc.cid;
结果：
附加：
select sc.cid, course.cname,count(sc.sid) as '该课程的总人数',
sum(case when score>=0 and score<60 then 1 else 0 end) as '[0-60]人数',
concat(round(sum(case when score>=0 and score<60 then 1 else 0 end)/count(sc.sid)*100,2),'%') as '[0-60]',
sum(case when score>=60 and score<70 then 1 else 0 end) as '[60-70]人数',
concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(sc.sid)*100,2),'%') as '[60-70]',
sum(case when score>=70 and score<85 then 1 else 0 end) as '[70-85]人数',
concat(round((sum(case when score>=70 and score<85 then 1 else 0 end)/count(sc.sid))*100,2),'%') as '[70-85]',
sum(case when score>=85 and score<100 then 1 else 0 end) as '[85-100]人数',
concat(round((sum(case when score>=85 and score<100 then 1 else 0 end)/count(sc.sid))*100,2),'%') as '[85-100]'
from sc join course on sc.cid=course.cid
group by sc.cid;
运行结果：
16、要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cid, count(sid) as '选修人数' from sc group by cid order by 选修人数 desc,cid; #默认升序
运行结果：
17、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺rank1、不保留名次空缺rank2
如果MySQL是8以上版本，可以用这种方式：
select cid,sid ,score, rank()over(partition by cid order by score desc) as rank1, dense_rank()over (partition by cid order by score desc) as dense_rank2 from sc;
不使用系统自带函数的解决方法：
#rank1保留名次空缺， rank2不保留名次空缺 select a.cid, a.sid, a.score , count(a.score<b.score)+1 as rank1, count(distinct b.score)+1 as rank2 from sc a left join sc b on a.cid=b.cid and a.score<b.score #笛卡尔积连接, 然后筛选满足a.score<b.score的 group by a.cid, a.sid order by a.cid, a.score desc;
结果：
18、查询各科成绩前三名的记录
PS：这道题目就是在21,22题的基础上添加一个having子句，筛选名次rank<=3，即前三名
A：名次不连续时的前三名
select a.cid, a.sid, 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 #笛卡尔积连接, 然后筛选满足a.score<b.score的 group by a.cid, a.sid having rank<=3 order by a.cid, a.score desc;
运行结果：
B：名次连续时的前三名
select a.cid, a.sid, a.score , count(distinct b.score)+1 as rank #这里使用的是distinct b.score from sc a left join sc b on a.cid=b.cid and a.score<b.score #笛卡尔积连接, 然后筛选满足a.score<b.score的 group by a.cid, a.sid having rank<=3 order by a.cid, a.score desc;
运行结果：
19、查询学生的总成绩，并进行排名，总分重复时名次空缺保留和不保留的两种形式
#rank1保留名次空缺， rank2不保留名次空缺 select a.sid, a.tt,count(a.tt<b.rr)+1 as rank1, count(distinct b.rr)+1 as rank2 from (select sid, sum(score) as tt from sc group by sid ) a left join (select sid, sum(score) as rr from sc group by sid ) b on a.tt<b.rr group by a.sid order by rank1;
结果：
20、查询出只选修两门课程的学生学号和姓名
select student.sid, student.sname from sc join student on sc.sid=student.sid group by sc.sid having count(sc.cid)=2;
21、检索至少选修两门课程的学生学号
select sid from sc group by sid having count(cid)>=2;
22、查询没有学全所有课程的同学的信息
#有一些同学一门课都没有选,所以成绩表SC里面没有这些学生的任何信息，
#现在题目的要求是没有学全，个人理解是SC表里的同学
select student.*
from sc join student on sc.sid=student.sid
group by sc.sid
having count(sc.cid)<(select count(course.cid) from course);
23、查询选修了全部课程的学生信息
select student.* from sc join student on sc.sid=student.sid group by sc.sid having count(cid)=(select count(cid) from course);
24、统计每门课程的学生选修人数（超过 5 人的课程才统计）
select cid, count(sid) as '选修人数'
from sc
group by cid
having count(sid)>5;
25、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
# 先查询出有课程小于70的学号，再把这些学号排除 select sc.sid, student.sname,course.cname, sc.score from sc join student on sc.sid=student.sid join course on sc.cid= course.cid where sc.sid not in (select distinct sid from sc where score<70)
26、查询每门课程成绩相同的学生信息、查询每个同学成绩相同的课程信息
A：每门课程下成绩相同的学生信息
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 ;
运行结果：
B：不同课程下成绩相同的学生信息
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;
运行结果：
或
# 每位同学有相同成绩的课程信息 select a.sid, a.cid, a.score from sc a left join sc b on a.cid<>b.cid and a.score=b.score and a.sid=b.sid where b.sid is not null; # 每门课程下有相同成绩的学生信息 select a.sid, a.cid, a.score from sc a left join sc b on a.cid=b.cid and a.score=b.score and a.sid<>b.sid where b.sid is not null;
27、查询 1990 年出生的学生名单
select sid, sname
from student
where year(sborn)='1990';
28、查询各学生的年龄，只按年份来算
select sid , sname, year(now())-year(sborn) as age from student;
29、按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一
timestampdiff函数：日期或日期时间表达式之间的整数差。
语法：TIMESTAMPDIFF(interval，datetime1，datetime2) datetime2-datetime1
select sid, sname ,sborn, timestampdiff(year,sborn,date_format(now(),'%Y-%m-%d')) as '题目要求计算的age', year(now())-year(sborn) as '上一题的年龄' from student;
运行结果：
30、查询本周过生日的学生
特别注意： 本周过生日是指，出生日期放到现在的年份下，是哪一周过生日，而不是简单地出生日期的周数等于现在日期的周数就可以了，这一点要注意，很容易混淆。
PS：
a. 在where子句中两端使用的函数要相同，因为week( )函数的周数是从0开始，weekofyear( )函数是从1开始
b. concat( )函数、concat_ws( )函数都可以，
c. 连接中间的年月日时，有两种形式：一种是使用函数date_format( )，另一种是整理出年、月、日，然后进行连接
d. 中间的连接符：-, /, #, * , $，这几个试了都可以
下面的where 子句除了最后一行，任选一行都能运行出正确的结果
select * from student where weekofyear(concat(year(now()),'-',date_format(sborn,'%m-%d')))=weekofyear(now()); #成功 where weekofyear(concat(year(now()),'/',month(sborn),'/',day(sborn)))=weekofyear(now()); where week(concat(year(now()),'-',month(sborn),'-',day(sborn)))=week(now()); where week(concat_ws('',year(now()),month(sborn),day(sborn)))=week(now()); where week(concat_ws('-',year(now()),date_format(sborn,'%m-%d')))=week(now()); #where weekofyear(year(now())&"-"&month(sborn)&"-"&day(sborn))=weekofyear(now()); #这种形式的连接报错
31、查询下周过生日的学生
select * from student where weekofyear(concat_ws('-',year(now()),date_format(sborn,'%m-%d')))=weekofyear(now())+1;
32、查询本月过生日的学生
select sid , sname, sborn from student where month(sborn)=month(now());
33、查询下月过生日的学生
select sid , sname, sborn from student where month(sborn)=month(now())+1;