一、创建答题环境
- 创建名为 school 数据库,然后运行SQL程序
- 下载地址:免费的直接下载导入到数据库
二、经典题目
-
查询至少参加 01 和 02 两门课程考试的相关信息(查出 Sid 相关成绩 即可)
-- 按照上面的描述我们只需要查 SC 这张表即可,查询出两门成绩即可 select sc1.SId, sc1.score, sc2.score from (select * from sc where cid = '01') sc1 inner join (select * from sc where cid = '02') sc2 on sc1.sid = sc2.sid;
SId score score 01 80.0 90.0 02 70.0 60.0 03 80.0 80.0 04 50.0 30.0 05 76.0 87.0 扩展:查询参加 01 02 03 三门课程的考试成绩和学生 id
select sc1.SId, sc1.score as score1, sc2.score as score2, sc3.score as score2 from (select * from sc where cid = '01') sc1 join (select * from sc where cid = '02') sc2 join (select * from sc where cid = '03') sc3 on sc1.sid = sc2.sid and sc2.SId = sc3.SId;
SId score1 score2 score2 01 80.0 90.0 99.0 02 70.0 60.0 80.0 03 80.0 80.0 80.0 04 50.0 30.0 20.0 -
查询参加 01 课程但是可能没有参加 02 课程的情况(不存在时显示null)
-- 典型的连接查询,使用左连接即可 select sc1.SId, sc1.score, sc2.score from (select * from sc where cid = '01') sc1 left join (select * from sc where cid = '02') sc2 on sc1.sid = sc2.sid;
SId score score 01 80.0 90.0 02 70.0 60.0 03 80.0 80.0 04 50.0 30.0 05 76.0 87.0 06 31.0 NULL -
查询可能没有参加 01 课程但是肯定参加 02 课程的情况(不存在时显示null)
-- 使用右连接查询即可 select sc2.SId, sc1.score, sc2.score from (select * from sc where cid = '01') sc1 right join (select * from sc where cid = '02') sc2 on sc1.sid = sc2.sid;
SId score score 01 80.0 90.0 02 70.0 60.0 03 80.0 80.0 04 50.0 30.0 05 76.0 87.0 07 NULL 89.0 -
查询编号为的 01 课程比编号为 02 课程分数高的学生信息和课程分数
-- 思路:首先要查出 01 和 02 课程的信息,然后使用学生 id 连接起来 -- 这样就得到课程 01 比较 02 分数高的学生的 id 和相关的成绩 select sc1.SId, sc1.score as sorce1, sc2.score as sorce2 from (select SId, CId, score from SC where CId = '01') as sc1 inner join (select SId, CId, score from SC where CId = '02') as sc2 on sc1.SId = sc2.SId where sc1.score > sc2.score;
SId sorce1 sorce2 2 70.0 60.0 4 50.0 30.0 将上一步查询出的结果做子集使用 sid 与 student 表进行连接然后提取出相关信息即可
Id Sname Sage Ssex sorce1 sorce2 2 钱电 1990-12-21 00:00:00 男 70.0 60.0 4 李云 1990-12-06 00:00:00 男 50.0 30.0 -
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- 连接以后使用 group by 分组即可 select Sc.SId, s.Sname, avg(score) avg_s from SC inner join Student S on SC.SId = S.SId group by S.Sname, Sc.SId having avg_s > 60;
Id Sname avg_s 5 周梅 81.50000 3 孙风 80.00000 1 赵雷 89.66667 7 郑竹 93.50000 2 钱电 70.00000 -
查询在 SC 表存在成绩的学生信息
-- 两边连接一起后使用 distinct 学生信息去重 select distinct student.* from student join SC S on Student.SId = S.SId;
SId Sname Sage Ssex 01 赵雷 1990-01-01 00:00:00 男 02 钱电 1990-12-21 00:00:00 男 03 孙风 1990-12-20 00:00:00 男 04 李云 1990-12-06 00:00:00 男 05 周梅 1991-12-01 00:00:00 女 06 吴兰 1992-01-01 00:00:00 女 07 郑竹 1989-01-01 00:00:00 女 -
查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
select Sname, count(SC.CId), sum(score) from SC join Student S on SC.SId = S.SId group by SC.SId, S.Sname;
Sname count(SC.CId) sum(score) 赵雷 3 269.0 钱电 3 210.0 孙风 3 240.0 李云 3 100.0 周梅 2 163.0 吴兰 2 65.0 郑竹 2 187.0 -
查询 李 姓老师的数量
select count(1) from Teacher where Tname like '李%';
count(1) 1 -
查询 张三 老师授课的同学信息
首先查出张三老师授课的信息 Cid Cname
select CId, Cname from Course join Teacher T on Course.TId = T.TId where Tname = '张三';
Id Cname 2 数学 再通过 SC 表查出学生信息
select SId, Cname from SC join (select CId, Cname from Course join Teacher T on Course.TId = T.TId where Tname = '张三') as CS on CS.CId = SC.CId;
Id Cname 1 数学 2 数学 3 数学 4 数学 5 数学 7 数学 最后再通过 Student 查询相关的学生信息即可
select * from Student where SId in ( select SId from SC join (select CId, Cname from Course join Teacher T on Course.TId = T.TId where Tname = '张三') as cs on SC.CId = cs.CId);
Id Sname Sage Ssex 1 赵雷 1990-01-01 00:00:00 男 2 钱电 1990-12-21 00:00:00 男 3 孙风 1990-12-20 00:00:00 男 4 李云 1990-12-06 00:00:00 男 5 周梅 1991-12-01 00:00:00 女 7 郑竹 1989-01-01 00:00:00 女 解法二:inner join 将三张表连接起来然后查询
-
查询没有参与所有考试的学生的信息
首先从 SC 表中查询出每名同学参考考试的总数
select SId, count(CId) from SC group by SC.SId;
Id count(CId) 1 3 2 3 3 3 4 3 5 2 6 2 7 2 组合一下查询出参与了所有考试的学生的 Sid
select SId from SC group by SC.SId having count(CId) = (select count(1) from Course);
SId 1 2 3 4 查询出的全是参加过所有考试的学生 Sid 现在只需要结合 Student 表 not in 即可
SELECT Student.* FROM Student WHERE SId NOT IN (SELECT SId FROM SC GROUP BY SC.SId having count(CId) = (select count(1) from Course));
Id Sname Sage Ssex 5 周梅 1991-12-01 00:00:00 女 6 吴兰 1992-01-01 00:00:00 女 7 郑竹 1989-01-01 00:00:00 女 9 张三 2017-12-20 00:00:00 女 0 李四 2017-12-25 00:00:00 女 1 李四 2012-06-06 00:00:00 女 2 赵六 2013-06-13 00:00:00 女 3 孙七 2014-06-01 00:00:00 女 -
查询至少一门参加的考试课程与学号为 01 的同学所参加的考试课程相同的同学信息
这道题相对比较简单,首先查出 01 号同学参与的所有课程的 Cid
select CId from SC where SId = 01;
CId 1 2 3 接下来从 SC 表中查出参与考试与 01 号至少有一门相同的 Sid 使用 distinct 去重再使用 Student 表查出学生详细信息
select * from Student where SId in ( select distinct SId from SC where CId in ( select CId from SC where SId = 01));
Id Sname Sage Ssex 1 赵雷 1990-01-01 00:00:00 男 2 钱电 1990-12-21 00:00:00 男 3 孙风 1990-12-20 00:00:00 男 4 李云 1990-12-06 00:00:00 男 5 周梅 1991-12-01 00:00:00 女 6 吴兰 1992-01-01 00:00:00 女 7 郑竹 1989-01-01 00:00:00 女 -
查询与 “01” 号的同学参加的考试课程完全相同的其他同学的信息
首先从课程数量上过滤一部分学生,比如 01 参加 5 门考试,找一样的话优先考虑也参加 5 门的否则何谈相同
SELECT Student.* FROM Student WHERE sid IN (SELECT sid FROM sc GROUP BY sid HAVING COUNT(sid) = (SELECT COUNT(cid) FROM sc WHERE sid = '01'));
Id Sname Sage Ssex 1 赵雷 1990-01-01 00:00:00 男 2 钱电 1990-12-21 00:00:00 男 3 孙风 1990-12-20 00:00:00 男 4 李云 1990-12-06 00:00:00 男 SELECT Student.* FROM Student WHERE sid IN (SELECT sid FROM sc GROUP BY sid HAVING COUNT(sid) = (SELECT COUNT(cid) FROM sc WHERE sid = '01') ) AND sid NOT IN (SELECT sid FROM sc WHERE cid IN (SELECT DISTINCT cid FROM sc WHERE cid NOT IN (SELECT cid FROM sc WHERE sid = '01')) GROUP BY sid ) AND sid NOT IN ('01');
-
查询没有参加 “张三” 老师讲授的任一门课程考试的学生的名字
首先需要查询出张三老师授课 Cid 使用 Course 和 Teacher 表
select cid from Teacher join Course C on Teacher.TId = C.TId where Tname = '张三'; select * # from SC # left join ( # select cid # from Teacher # join Course C on Teacher.TId = C.TId # where Tname = '张三') as SR # on SC.CId = SR.CId # where SR.cid is not null;;
cid 2 将 SC 表和张三老师讲授表进行 左 连接
select * from SC left join ( select cid from Teacher join Course C on Teacher.TId = C.TId where Tname = '张三') as SR on SC.CId = SR.CId;
SId CId score cid 1 02 90.0 02 2 02 60.0 02 3 02 80.0 02 4 02 30.0 02 5 02 87.0 02 7 02 89.0 02 1 01 80.0 NULL 1 03 99.0 NULL 2 01 70.0 NULL 2 03 80.0 NULL 3 01 80.0 NULL 3 03 80.0 NULL 4 01 50.0 NULL 4 03 20.0 NULL 5 01 76.0 NULL 6 01 31.0 NULL 6 03 34.0 NULL 7 03 98.0 NULL 我们可以看到没有参加张三老师课程的位置都为 NULL 直接使用 join 即可
select * from SC join ( select cid from Teacher join Course C on Teacher.TId = C.TId where Tname = '张三') as SR on SC.CId = SR.CId;
SId CId score cid 1 02 90.0 02 2 02 60.0 02 3 02 80.0 02 4 02 30.0 02 5 02 87.0 02 7 02 89.0 02 目前 Sid 就已经参加 张三老师课程 的学生 Sid 目前只需要从 Student 中 not in 即可
select * from Student where SId not in ( select SId from SC join ( select cid from Teacher join Course C on Teacher.TId = C.TId where Tname = '张三') as SR on SC.CId = SR.CId);
Id Sname Sage Ssex 6 吴兰 1992-01-01 00:00:00 女 9 张三 2017-12-20 00:00:00 女 0 李四 2017-12-25 00:00:00 女 1 李四 2012-06-06 00:00:00 女 2 赵六 2013-06-13 00:00:00 女 3 孙七 2014-06-01 00:00:00 女 -
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
首先从 SC 表中查出 60 分以下并且大于等于 2 学生信息 使用分组即可
select SId, count(*) as num, avg(score) as avg_num from SC where score < 60 group by Sc.SId having num >= 2;
SId num avg_num 4 3 33.33333 6 2 32.50000 现在 Sid 和平均分已经有了,只需要和学生表进行连接即可
select Si.Sid, Sname, avg_num from Student join (select SId, count(*) as num, avg(score) as avg_num from SC where score < 60 group by Sc.SId having count(1) >= 2) as Si on Si.SId = Student.SId;
id Sname avg_num 4 李云 33.33333 6 吴兰 32.50000 -
检索" 01 "课程分数小于 60,按分数降序排列的学生信息
这道题相对容易 SC 查询到相关信息然后连接 Student 即可
select Student.*, score from Student join (select SId, score from SC where CId = '01' and score < 60) as Si on Student.SId = Si.SId order by score desc;
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
先查询出平均分然后与 SC 表进行连接即可
select SC.SId, score, avg_num.avg_score from SC join (select sid, avg(score) as avg_score from sc group by sid) avg_num on SC.SId = avg_num.SId order by avg_score desc ;
Id score avg_score 7 89.0 93.50000 7 98.0 93.50000 1 80.0 89.66667 1 90.0 89.66667 1 99.0 89.66667 5 76.0 81.50000 5 87.0 81.50000 3 80.0 80.00000 3 80.0 80.00000 3 80.0 80.00000 2 60.0 70.00000 2 80.0 70.00000 2 70.0 70.00000 4 50.0 33.33333 4 30.0 33.33333 4 20.0 33.33333 6 31.0 32.50000 6 34.0 32.50000 -
查询各科成绩最高分、最低分和平均分
直接按照 Cid 进行分组即可然后连接 Course 即可
select Course.Cname, max_score, min_score, avg_score from Course join (select CId, max(score) max_score, min(score) min_score, avg(score) avg_score from SC group by CId) as Si on Si.CId = Course.CId;
Cname max_score min_score avg_score 语文 80.0 31.0 64.50000 数学 90.0 30.0 72.66667 英语 99.0 20.0 68.50000 扩展:继上题目 课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 (及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90)
name max_score min_score avg_score p_pate d_rate hd_rate 文 80.0 31.0 64.50000 0.6667 0.5000 0.0000 学 90.0 30.0 72.66667 0.6667 0.5000 0.1667 语 99.0 20.0 68.50000 0.6667 0.3333 0.3333 -
查询出只参加两门课程考试的学生学号和姓名
通过 SC 表查出参与两门课程的同学信息然后连接 Student 表即可
select S.SId, S.Sname from Student as S join (select SId, count(SC.CId) as num from SC group by SC.SId) as Si on S.SId = Si.SId where num = 2;
id sname 5 周梅 6 吴兰 7 郑竹 -
查询男生、女生人数
select Ssex, count(1) from Student group by Student.Ssex;
sex count(1) 8 4 -
查询名字中含有 风 字的学生信息
select * from student where sname like '%风%';
Id Sname Sage Ssex 3 孙风 1990-12-20 00:00:00 男 -
查询同名同姓学生名单,并统计人数
select Sname,count(*) as num from Student group by Student.Sname having num > 1;
Sname num 李四 2 -
查询1990年出生的学生名单
select * from Student where date_format(Sage, '%Y') = 1990;
Id Sname Sage Ssex 1 赵雷 1990-01-01 00:00:00 男 2 钱电 1990-12-21 00:00:00 男 3 孙风 1990-12-20 00:00:00 男 4 李云 1990-12-06 00:00:00 男 -
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号排序
select cid, avg(score) as avgScore from sc group by cid order by avgScore desc, cid asc ;
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select Si.SId, Student.Sname, Si.avg_score from Student join (select sid, avg(score) as avg_score from SC group by SC.SId having avg_score > 85) as Si on Student.SId = Si.SId;
Id Sname avg_score 1 赵雷 89.66667 7 郑竹 93.50000 -
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
select S2.SId, S2.Sname, Course.Cname, S.score from Course join SC S on Course.CId = S.CId join Student S2 on S.SId = S2.SId where Course.Cname = '数学' and score < 60;
Id Sname Cname score 4 李云 数学 30.0 -
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
select Student.SId, Student.Sname, Course.Cname, S.score from Student left join SC S on Student.SId = S.SId left join Course on S.CId = Course.CId;
Id Sname Cname score 1 赵雷 语文 80.0 2 钱电 语文 70.0 3 孙风 语文 80.0 4 李云 语文 50.0 5 周梅 语文 76.0 6 吴兰 语文 31.0 1 赵雷 数学 90.0 2 钱电 数学 60.0 3 孙风 数学 80.0 4 李云 数学 30.0 5 周梅 数学 87.0 7 郑竹 数学 89.0 1 赵雷 英语 99.0 2 钱电 英语 80.0 3 孙风 英语 80.0 4 李云 英语 20.0 6 吴兰 英语 34.0 7 郑竹 英语 98.0 9 张三 NULL NULL 0 李四 NULL NULL 1 李四 NULL NULL 2 赵六 NULL NULL 3 孙七 NULL NULL -
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select S2.SId, S2.Sname, C.Cname, S.score from Course C join SC S on C.CId = S.CId join Student S2 on S.SId = S2.SId where S.score > 70;
Id Sname Cname score 1 赵雷 语文 80.0 1 赵雷 数学 90.0 1 赵雷 英语 99.0 2 钱电 英语 80.0 3 孙风 语文 80.0 3 孙风 数学 80.0 3 孙风 英语 80.0 5 周梅 语文 76.0 5 周梅 数学 87.0 7 郑竹 数学 89.0 7 郑竹 英语 98.0 -
查询每门课程不及格的人数
select Course.CId, Course.Cname, TempC.sn from Course join (select Cid, count(*) as sn from SC where score < 60 group by CId) as TempC on TempC.CId = Course.CId;
Id Cname sn 1 语文 2 2 数学 1 3 英语 2 -
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select S.SId, S.Sname, SC.score from SC join Student S on SC.SId = S.SId where SC.CId = 01 and SC.score >= 80;
Id Sname score 1 赵雷 80.0 3 孙风 80.0 -
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
不想使用子查询就直接将三张表连接在一起即可
select Student.*, S.score, T.Tname from Student join SC S on Student.SId = S.SId join Course C on S.CId = C.CId join Teacher T on C.TId = T.TId where T.Tname = '张三' order by S.score desc limit 1;
Id Sname Sage Ssex score Tname 1 赵雷 1990-01-01 00:00:00 男 90.0 张三 -
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct a.* from sc a inner join sc b on a.score = b.score where a.CId != b.CId;
Id CId score 2 03 80.0 3 02 80.0 3 03 80.0 1 01 80.0 3 01 80.0 -
统计每门课程的学生选修人数(超过 5 人的课程才统计)
select Cname, count(*) as num from SC join Course C on SC.CId = C.CId group by C.Cname having num > 5;
name num 学 6 语 6 文 6 -
统计每门课程的学生选修人数(超过 5 人的课程才统计)
select S.Sname, count(SC.CId) as num from SC join Student S on SC.SId = S.SId group by S.Sname having num >= 2;
name num 吴兰 2 周梅 2 孙风 3 李云 3 赵雷 3 郑竹 2 钱电 3 -
查询选修了全部课程的学生信息
select Sname, count(CId) selection_num from SC join Student S on SC.SId = S.SId group by S.Sname having selection_num = (select count(1) from Course);
name selection_num 孙风 3 李云 3 赵雷 3 钱电 3 -
查询各学生的年龄,只按年份来算
-- 两种方法都可以 select sname, timestampdiff(year, sage, now()) as sage from student; select sname, year(now()) - year(sage) as sage from student;
name sage 赵雷 30 钱电 30 孙风 30 李云 30 周梅 29 吴兰 28 郑竹 31 张三 3 李四 3 李四 8 赵六 7 孙七 6 -
查询本周过生日的学生
select sname from student where week(Sage) = week(now());
name 孙七