45题SQL练习
创建表
create table if not exists student (
sid int(10) comment '学生学号',
sname varchar(5) comment '学生姓名',
sage datetime comment '学生出生日期',
sgender char(1) comment '学生性别'
)engine=innodb default charset=utf8;
insert into student values (01,'赵雷','1990-01-01','男');
insert into student values (02,'钱电','1990-12-21','男'),
(03,'孙风','1990-12-20','男'),
(04,'李云','1990-12-06','男'),
(05,'周梅','1991-12-01','女'),
(06,'吴兰','1992-01-01','女'),
(07,'郑竹','1989-01-01','女'),
(08,'张三','2017-12-20','女'),
(09,'李四','2017-12-25','女'),
(10,'李四','2012-06-06','女'),
(11,'赵六','2013-06-13','女'),
(12,'孙七','2014-06-01','女');
-- 科目表
create table course (
cid int(10) comment '科目号',
cname varchar(10) comment '科目名称',
tid int(10) comment '授课老师'
)engine=innodb default charset=utf8;
insert into course values (01,'语文',02),(02,'数学',01),(03,'英语',03);
--教师表
create table teacher(
tid int(10) comment '教师学号',
tname varchar(10) comment '教师姓名'
)engine=innodb default charset=utf8;
insert into teacher values(01,'张三'),(02,'李四'),(03,'王五');
--考试成绩表
create table score(
sid int(10) comment '学生学号',
cid int(10) comment '科目号',
grade decimal(18,1)
)engine=innodb default charset=utf8;
insert into score values (01,01,80),
(01,02,90),
(01,03,99),
(02,01,70),
(02,02,60),
(02,03,80),
(03,01,90),
(03,02,80),
(03,03,80),
(04,01,50),
(04,02,30),
(05,01,76),
(05,02,87),
(06,01,31),
(06,03,34),
(07,02,89),
(07,03,98);
Q1:查询 01课程比02课程成绩高的学生的信息及课程分数
步骤1、首先在score表中查询出有01和02课程的学生以及对应的成绩
select
*
from score a
inner join score b
on a.sid=b.sid
where a.cid=01 and b.cid=02;
步骤2:由结果可知,我们只需要sid=2/3/4的学生,因为这些学生的01课程分数高于02课程
要查询的有:sid,01课程的grade,02课程的grade
select a.sid,a.grade,b.grade
from score a
inner join score b
on a.sid=b.sid
where a.cid=01 and b.cid=02 and a.grade>b.grade;
步骤3、连接学生表,查询学生信息
要查询的有:sid,sname,sage,sgender,01课程grade,02课程grade
哪些表:student、上图2显示的表
思路:采用子查询方式,将上图显示的表定义成新的表,然后与student表进行连接
select s.sid,sname,sage,sgender,class1,class2
from student s
inner join (
select a.sid, a.grade as class1, b.grade as class2
from score a
inner join score b
on a.sid=b.sid
where a.cid=01 and b.cid=02 and a.grade>b.grade;
) r
on s.sid=r.sid;
方法2:先查询01课程的sid和grade,在查询02的sid和grade
将这2个表连接查询出共同的sid,与grade
在将student表与上表结合
select s.sid,sname,sage,sgender,class1,class2 from student s
inner join(
select a.sid,class1,class2 from
(select sid,grade as class1 from score where score.cid=01) a,
(select sid,grade as class2 from score where score.cid=02) b
where a.sid=b.sid and class1>class2
) r
on s.sid=r.sid;
Q1.1:查询同时存在01课程和02课程的情况
select * from
(select * from score where score.cid=01) a,
(select * from score where score.cid=02) b
where a.sid=b.sid;
Q1.2:查询存在01课程可能但可能不存在02课程的情况
select * from
(select * from score where score.cid=01) a
left join
(select * from score where score.cid=02) b
on a.sid=b.sid;
Q1.3:查询不存在01但存在02的课程的情况
select * from score
where score.sid not in(
select sid from score where score.cid=01
)
and score.cid=02;
Q2:查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
步骤1:根据sid求每个学生的平均成绩
select sid,avg(grade) as agrade
from score
group by sid;
步骤2:查询平均成绩大于60分的
select sid,avg(grade) as agrade
from score
group by sid
having agrade>60;
步骤3:结合student表,查询sname
select s.sid,sname,avg_grade
from student s
inner join(
select sid,avg(grade) as avg_grade from score
group by sid
having avg_grade>60
) r
on s.sid=r.sid;
Q3:查询在 score 表存在成绩的学生信息
select s.sid,sname,sage,sgender from student s
inner join (
select distinct sid from score
) r
on s.sid=r.sid;
Q4:查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
步骤1:查询所有学生的选课总数和课程总成绩
select sid, count(cid) as count_cid, sum(grade) as sum_grade
from score
group by sid;
步骤2:连表student查询学生姓名
select s.sid,sname,count_cid,sum_grade
from student s
left join (
select sid, count(cid) as count_cid, sum(grade) as sum_grade
from score
group by sid
) r
on s.sid=r.sid;
Q4.1:查有成绩的学生信息
select s.sid,sname,sage,sgender from student s,(
select distinct sid from score
) r
where s.sid=r.sid;
Q5:查询 [李] 姓老师的数量
select count(*)
from teacher where tname like '李%';
Q6:查询学过「张三」老师授课的同学的信息
分析:先在teacher表中查找【张三】老师的tid,然后根据tid在course表中查到教的科目cid,然后根据cid在score表中找到有这个科目的学生sid,根据sid在student中查询学生信息
select student.*
from student,teacher,course,score
where teacher.tname ='张三'
and teacher.tid=course.tid
and course.cid=score.cid
and score.sid=student.sid;
Q7:查询没有学全所有课程的同学的信息
分析:反向思考,查询所有课程都选了学生,除掉这些学生之外的学生就是要求的
步骤1:查询所有课程个数
select count(cid) from course
步骤2:查询所有课程都选的学生sid
select sid from score
group by sid
having count(cid)=(select count(cid) from course)
步骤3:连接student表,查询学生信息
select student.* from student
where student.sid not in(
select sid from score
group by sid
having count(cid)=(select count(cid) from course)
);
Q8:查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
思路:① 从score表中,查询sid=01的学生所有的选课cid
② 从score表中,查询所有学生的sid,如果这个学生的cid出现在前面的结果中
③ 从student表中,查询所有学生的信息,如果这个学生的sid出现在前面的结果中
select * from student
where sid in(
select distinct sid from score
where cid in(
select cid from score
where sid=01
)
);
Q9:查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
思路:满足2个条件
- 没有学01号同学没有学的课程
select sid from score
where cid in(
select cid from score where sid=01
)
group by sid
- 和01号同学学的课程数量相同
select sid from score
group by sid
having count(cid)=(
select count(cid) from score
where sid=01
)
在student表中,查询所有学生的信息,如果这个学生的sid满足上面2个条件(即sid出现在前面两个的结果中)
select * from student
where sid in(
select sid from score
where cid in(
select cid from score where sid=01
)
group by sid
) and sid in(
select sid from score
group by sid
having count(cid)=(
select count(cid) from score
where sid=01
)
)
Q10:查询没学过"张三"老师讲授的任一门课程的学生姓名
思路:① 在teacher表中,查询张三老师的tid
② 在course表中,查询科目cid,如果这个科目的tid出现在前面的结果中
③ 在score表中,查询学生sid,如果这个学生的cid出现在之前的结果中
④ 在student表中,查询学生信息,如果学生的sid不出现在前面的结果中
-- 方法1:嵌套查询
select * from student where sid not in(
select sid from score where cid in(
select cid from course where tid in(
select tid from teacher where tname='张三'
)
)
);
-- 方法2:多表联合查询
select * from student where sid not in(
select sid from score,course,teacher
where teacher.tname='张三'
and teacher.tid=course.tid
and score.cid=course.cid
);
Q11:查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
思路:① 在score表中,选择grade<60,并group by sid,having count(cid)>1
② 在student表中,查询sname
select s.sid,sname,avg_grade from student s
inner join (
select sid, avg(grade) as avg_grade from score
where grade<60
group by sid
having count(cid)>1
) r
on s.sid=r.sid
Q12:检索" 01 "课程分数小于 60,按分数降序排列的学生信息
-- 方法1:先查询 01 课程分数小于 60且按照分数降序排列的sid,grade
-- 在联合student表,查询学生信息
select s.sid,sname,sage,sgender,grade from student s
inner join(
select sid,grade from score where grade<60 and cid=01
order by grade desc
) r
on s.sid=r.sid;
-- 方法2:多表查询
select student.*,grade from student, score
where score.cid=01 and score.grade<60
and student.sid=score.sid
order by score.grade desc
Q13:按平均成绩从高到底显示所有学生的所有课程的成绩以及平均成绩
思路:① 在score中,查询sid,avg(grade)
② score表和上表联合查询
select score.sid,grade,avg_grade from score
left join(
select sid,avg(grade) as avg_grade from score
group by sid
) r
on score.sid=r.sid
order by avg_grade desc
Q14:查询各科成绩最高分、最低分和平均分
select cid,max(grade) as max_grade, min(grade) as min_grade, avg(grade) as avg_grade
from score
group by cid
Q14.1:将各科的科目名称、选课人数也查询出来
select r.*,cname from course c inner join (
select cid,
max(grade) as 最高分,
min(grade) as 最低分,
avg(grade) as 平均分,
count(sid) as 选课人数
from score
group by cid
) r
on c.cid=r.cid;
Q15:按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
不会,只能根据cid值来进行排序
set @rank=0;
select cid,sid,grade,@rank:=@rank+1 as 排名 from (
select cid,sid,grade from score
where cid=01
order by grade desc
) r;
网上找到另外的一个答案如下:但是看不懂
select a.cid, a.sid, a.grade, count(b.grade)+1 as rank
from score as a
left join score as b
on a.grade<b.grade and a.cid = b.cid
group by a.cid, a.sid,a.grade
order by a.cid, rank ASC;
Q16:查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
set @rank=0;
select sid,总分,@rank:=@rank+1 as 排名 from (
select sid,sum(grade) as 总分 from score
group by sid
order by 总分 desc
) r;
Q17:统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
select s.cid,cname,
sum(case when grade<=100 and grade>85 then 1 else 0 end) as '[100-85]',
sum(case when grade<=85 and grade>70 then 1 else 0 end) as '[85-70]',
sum(case when grade<=70 and grade>60 then 1 else 0 end) as '[70-60]',
sum(case when grade<=60 and grade>0 then 1 else 0 end) as '[60-0]'
from score s
left join course c
on s.cid=c.cid
group by s.cid;
Q18:查询各科成绩前三名的记录
不会,只能做出一部分。以下代码从score表中按照cid将每一个学生的成绩从高到底进行了排序
select cid,sid,grade from score
group by cid,sid
order by cid,grade desc
网上看到一种解法:使用partiton by函数
-- partition by cid order by grade desc:按照cid将每一个grade进行排序,然后选取其中的前三个
-- 但是注意:只能在mysql8.0以上版本才支持partition by的写法
select * from (
select *,row_number() over(partition by cid order by grade desc) as rank
from score
) r
where rank<4
Q19:查询每门课程被选修的学生数
select cid,count(sid) from score
group by cid;
Q20:查询出只选修两门课程的学生学号和姓名
思路:① 在score表中,查找只选择了2门课的学生sid
② 在student表中,查找学生的sname,如果学生的sid在前面的结果中、
select sid,sname from student
where sid in(
select sid from score
group by sid
having count(cid)=2
);
Q21:查询男生、女生人数
select sgender,count(*) from student
group by sgender;
Q22:查询名字中含有 [风] 字的学生信息
-- %:多个字符 _:一个字符
select * from student
where sname like '%风%';
Q23:查询同名学生名单,并统计同名人数
-- 统计同名人数
select sname,count(sname) as 同名人数 from student
group by sname
having count(sname)>1
-- 查询同名学生信息
select * from student s
where sname in (
select sname from student
group by sname
having count(sname)>1
);
Q24:查询 1990 年出生的学生名单
select * from student where year(sage)='1990';
Q25:查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select score.cid,cname,avg(grade) as 平均分 from course,score
where course.cid=score.cid
group by course.cid
order by 平均分 desc,course.cid asc
Q26:查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
思路:① 在score表中,查询平均分大于85的学生sid,平均成绩
② 在student表中,查询sname
嵌套查询
select s.sid,sname,平均成绩 from student s
inner join (
select sid,avg(grade) as 平均成绩
from score
group by sid
having 平均成绩>=85
) r
on s.sid=r.sid;
连表查询
select a.sid,sname,avg(grade) as 平均成绩 from student a, score b
where a.sid=b.sid
group by b.sid
having 平均成绩>=85;
Q27:查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
思路:① 在course表中,查询【数学】的cid
② 在score表中,查询分数低于60的sid,如果cid在前面的结果中
③ 连表查询sname
select sname,grade from student s
inner join (
select sid,grade from score where cid in (
select cid from course where cname='数学'
)
and grade<60
) r
on s.sid=r.sid;
Q28:查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
select s.sid,sname,cid,grade from student s
left join score c
on s.sid=c.sid;
Q29:查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-- 方法1
select sname,cname,grade from student a,course b,(
select * from score
where grade>70
group by sid,cid
) r
where a.sid=r.sid and b.cid=r.cid
-- 方法2
select sname,cname,grade from student a,course b,score c
where c.grade>70
and a.sid=c.sid
and b.cid=c.cid
Q30:查询存在不及格的课程
select cid from score
where grade<60
group by cid
Q31:查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
select a.sid,sname from student a,score b
where b.cid=01
and grade>80
and a.sid=b.sid
Q32:求每门课程的学生人数
select cid,count(*) as 学生人数 from score
group by cid
Q33:成绩不重复,查询选修【张三】老师所授课程的学生中,成绩最高的学生信息及其成绩
思路:① 在teacher表中,查询【张三】老师的tid
② 在course表中,根据tid查询cid
③ 在score表中,根据cid查询所有学生的成绩,然后按照成绩降序排序,最后选择第一条记录,就是第一名
select student.sid,sname,grade from student,score where cid=(
select cid from course where tid=(
select tid from teacher where tname='张三'
)
)
and student.sid=score.sid
order by grade desc
limit 1;
Q34:成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
思路:前面已经找到了最高记录的分数grade,那么只要在score表中,查询所有的grade等于这个最高分的学生信息就可以了
为了查询结果,先修改表
update score set grade=90 where sid=07 and cid=02;
查询:先查询最高是分数,然后在查询等于这个最高分数的学生信息
select a.sid,sname,grade from student a,teacher b,course c,score d
where b.tname='张三'
and b.tid=c.tid
and c.cid=d.cid
and d.sid=a.sid
and d.grade=(
select max(grade) from student,teacher,course,score
where teacher.tname='张三'
and teacher.tid=course.tid
and course.cid=score.cid
and score.sid=student.sid
);
Q35:查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.sid,a.cid,a.grade from score a
inner join score b
on a.sid=b.sid
and a.cid!=b.cid
and a.grade=b.grade
Q36:查询每门课成绩最好的前两名
与Q18相同,网上找到一种解答方案,但是看不懂
select a.sid,a.cid,a.grade from score as a
left join score as b
on a.cid = b.cid and a.grade<b.grade
group by a.cid, a.sid
having count(b.cid)<2
order by a.cid;
Q37:统计每门课程的学生选修人数(超过 5 人的课程才统计)
select cid,count(sid)
from score
group by cid
having count(sid)>5;
Q38:检索至少选修两门课程的学生学号
select sid from score
group by sid
having count(cid)>=2;
Q39:查询选修了全部课程的学生信息
思路:① 查询course表,统计一共有几门课
② 从score表中,查询选修了全部课程的学生sid
③ 从student表中,查询学生信息,如果这个学生的sid出现在前面的结果中
select * from student
where sid in(
select sid from score
group by sid
having count(cid)=(
select count(cid) from course
)
)
Q40:查询各学生的年龄,只按年份来算
补充一个知识点:mysql中日期范围搜索的三种方式
方式一:between and语句
select * from student where sage between '2018-01-01' and '2018-12-31';
以上语句查询的实际时间范围是:2018-01-01 00:00:00 ~ 2018-12-31 00:00:00,而2018-12-31这一天的数据无法显示
方式二:datediff函数
语法:datediff(date1,date2) 该函数是返回两个日期之间的天数
select datadiff('2018-01-01','2018-01-05'); -- 结果:-4
方式三:timestampdiff函数
语法:timestampdiff (interval,datetime1,datetime2),比较的单位interval可以为以下数值
该函数按照interval计算datetime2和datetime1之间的差值
FRAC_SECOND 表示间隔是毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年
select timestampdiff(day,'2018-07-01 09:00:00','2018-07-04 12:00:00'); -- 结果:3
select timestampdiff(Year,'2018-07-01 09:00:00','2019-01-04 12:00:00'); -- 结果:0
CURDATE() 函数:返回当前的日期。
一般采用的是41中的:按照出生日期来算
Q41:查询各学生的年龄,按照出生日期来算
思路:只要计算sage和当前日前之间差多少年即可
select sid,sname,timestampdiff(year,sage,curdate()) as 学生年纪
from student;
Q42:查询本周过生日的学生
weekofyear(datetime):计算datatime所在的周数
select weekofyear('2020-01-09'); -- 结果:2
-- 1月9号在2020年的第二周内
思路:学生sage所在的周数与当前日期所在的周数,如果相等,就说明这个学生在这周过生日
select * from student
where weekofyear(sage)=weekofyear(curdate());
Q43:查询下周过生日的学生
select * from student
where weekofyear(sage)=weekofyear(curdate())+1;
Q44:查询本月过生日的学生
month(datatime):查询datatime所在的月份
select * from student
where month(sage)=month(curdate());
Q45:查询下月过生日的学生
select * from student
where month(sage)=month(curdate())+1;