四、MYSQL数据练习题（一）
我的MYSQL版本是mysql-5.7.24-winx64，每天练习1道习题。
如果有错误或者更优的解决方法，欢迎大家指出，谢谢！！
一、测试表格
--1.学生表
Student(Sid,Sname,Sage,Ssex)
--Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
CREATE TABLE `student` (
`Sid` varchar(20) DEFAULT NULL,
`Sname` varchar(20) DEFAULT NULL,
`Sage` datetime DEFAULT NULL,
`Ssex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--2.课程表
Course(Cid,Cname,Tno)
--Cid --课程编号,Cname 课程名称,Tid教师编号
CREATE TABLE `course` (
`Cid` varchar(12) DEFAULT NULL,
`Cname` varchar(120) DEFAULT NULL,
`Tid` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--3.教师表
Teacher(Tid,Tname)
--Tid 教师编号,Tname 教师姓名
CREATE TABLE `teacher` (
`Tid` varchar(12) DEFAULT NULL,
`Tname` varchar(120) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--4.成绩表
SC(Sid,Cid,score)
--Sid 学生编号,Cid 课程编号,score 分数
CREATE TABLE `sc` (
`Sid` varchar(12) DEFAULT NULL,
`Cid` varchar(20) DEFAULT NULL,
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二、插入测试数据
2.1 Student学生表
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男'); INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男'); INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男'); INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男'); INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女'); INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女'); INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女'); INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');
2.2 Course课程表
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
2.3 Teacher教师表
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');
2.4 SC成绩表
INSERT INTO `sc` VALUES ('01', '01', '80'); INSERT INTO `sc` VALUES ('01', '02', '90'); INSERT INTO `sc` VALUES ('01', '03', '99'); INSERT INTO `sc` VALUES ('02', '01', '70'); INSERT INTO `sc` VALUES ('02', '02', '60'); INSERT INTO `sc` VALUES ('02', '03', '80'); INSERT INTO `sc` VALUES ('03', '01', '80'); INSERT INTO `sc` VALUES ('03', '02', '80'); INSERT INTO `sc` VALUES ('03', '03', '80'); INSERT INTO `sc` VALUES ('04', '01', '50'); INSERT INTO `sc` VALUES ('04', '02', '30'); INSERT INTO `sc` VALUES ('04', '03', '20'); INSERT INTO `sc` VALUES ('05', '01', '76'); INSERT INTO `sc` VALUES ('05', '02', '87'); INSERT INTO `sc` VALUES ('06', '01', '31'); INSERT INTO `sc` VALUES ('06', '03', '34'); INSERT INTO `sc` VALUES ('07', '02', '89'); INSERT INTO `sc` VALUES ('07', '03', '98');
三、练习题
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
-- 思路：分为4步： -- 1、可以用两个sc表进行比较，一个表查询01课程的分数，然后另外一个表查询02课程的分数。 -- 2、将两表使用join on 进行联查，查询Sid相等的数据，就得到了这个学生同时有01、02课程的数据； -- 3、 再根据步骤2查询01课程比02课程成绩高的数据。 -- 4、再结合studnet表查询出学生信息 -- 步骤1：select * from sc as A where A.Cid = '01';select * from sc as B where B.Cid = '02'; -- 步骤2、3：SELECT A.Sid,A.Cid,A.score from (select * from sc where Cid = '01') as A JOIN (select * from sc where Cid = '02') as B ON B.Sid = A.Sid WHERE A.score > B.score; -- 步骤4： select student.*,C.Cid,C.score from student JOIN (SELECT A.Sid,A.Cid,A.score from (select * from sc where Cid = '01') as A JOIN (select * from sc where Cid = '02') as B ON B.Sid = A.Sid WHERE A.score > B.score) as C on C.Sid = student.Sid;
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
-- 思路：分为2步： -- 1、可以用两个sc表进行比较，一个表查询01课程的分数，然后另外一个表查询02课程的分数。 -- 2、将两表使用join on 进行联查，查询Sid相等的数据，就得到了这个学生同时有01、02课程的数据； -- 步骤1：select * from sc where Cid='01';select * from sc where Cid='02'; -- 步骤2： select * from (select * from sc where Cid ='01' ) as A JOIN (select * from sc where Cid ='02') as B on B.Sid = A.Sid;
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
-- 思路：分为2步： -- 1、可以用两个sc表进行比较，一个表查询01课程的分数，然后另外一个表查询02课程的分数。 -- 2、将两表使用left join on 进行联查，查询Sid相等的数据，就得到了这个学生有01,但可能不存在02课程的数据； -- 步骤1：select * from sc where Cid='01';select * from sc where Cid='02'; -- 步骤2： select * from (select * from sc where Cid ='01' ) as A left JOIN (select * from sc where Cid ='02') as B on B.Sid = A.Sid;
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
-- 思路：分为2步： -- 1、可以用两个sc表进行比较，一个表查询不存在01课程的分数，然后另外一个表查询02课程的分数。 -- 2、将两表使用 join on 进行联查，查询Sid相等的数据，就得到了这个学生不存在01,但存在02课程的数据； -- 步骤1：select * from sc where Cid='01';select * from sc where Cid='02'; -- 步骤2： select * from (select * from sc where Cid !='01' ) as A JOIN (select * from sc where Cid ='02') as B on B.Sid = A.Sid;
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- 思路：分为两步， -- 1、先根据Sid对sc表进行分类汇总，使用AVG函数和限定条件得到学生平均分； -- 2、结合Student表，使用join on 得到学生姓名、编号和平均成绩。 -- 步骤1：select AVG(score) as '平均分' from sc GROUP BY Sid HAVING AVG(score)>=60; -- 步骤2： select student.Sid,Sname,A.`平均分` from student join (select Sid,AVG(score) as '平均分' from sc GROUP BY Sid HAVING AVG(score)>=60) as A on A.Sid = student.Sid;
3. 查询在 SC 表存在成绩的学生信息
-- 思路： -- 1、先查询sc表的Sid，去重后作为限定条件，在student表中查询数据。 select * from student where Sid in (select DISTINCT Sid from sc);
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 思路： -- 1、在sc表中先查询选课总数、所有课程总成绩； -- 2、结合student表、sc表根据Sid使用left join on 进行查询（因为没成绩的显示为null，所以是left）。 -- 步骤1：select Sid,COUNT(Cid),SUM(score) from sc GROUP BY Sid; -- 步骤2： select Sname,B.Sid,B.Totla1,B.Total2 from student LEFT JOIN (select Sid,COUNT(Cid) as Totla1,SUM(score) as Total2 from sc GROUP BY Sid) AS B ON B.Sid = student.Sid;
4.1 查有成绩的学生信息
-- 思路： -- 1、在sc表中先查询选课总数、所有课程总成绩； -- 2、结合student表、sc表根据Sid使用right join on 进行查询（因为没成绩的显示为null，所以是right）。 -- 步骤1：select Sid,COUNT(Cid),SUM(score) from sc GROUP BY Sid; -- 步骤2： select * from student RIGHT JOIN (select Sid,COUNT(Cid) as Totla1,SUM(score) as Total2 from sc GROUP BY Sid) AS B ON B.Sid = student.Sid;
5. 查询「李」姓老师的数量
-- 思路： -- 1、使用like方法和count函数在teacher表中查询即可； -- 步骤1： select count(*) from teacher where Tname like '李%';
6. 查询学过「张三」老师授课的同学的信息
-- 思路： -- 1、根据teacher表查询出张三的Tid； select * from teacher where Tname = '张三' ; -- 2、根据步骤1的Tid在Course表中查出Cid； select * from course where Tid in (select Tid from teacher where Tname = '张三'); -- 3、根据步骤2的到的Cid作为限定条件，在sc表中查学生的Sid select Sid from sc where Cid in (select Cid from course where Tid in (select Tid from teacher where Tname = '张三')); -- 4、根据步骤3得到的Sid，在student表中查询出学生信息； select * from student where Sid in (select Sid from sc where Cid in (select Cid from course where Tid in (select Tid from teacher where Tname = '张三')));
7. 查询没有学全所有课程的同学的信息
-- 简单思路：涉及到的表课程表course、学生信息表student、通过成绩表sc来判断是否学全所有课程的学生。 -- 思路描述：先统计课程表course的总课程数，然后在成绩表sc中统计各个学生的课程数，找到与课程表course的总课程数不相等的Sid（这里要考虑到1门都没有的情况），然后根据Sid在学生信息表student中查询学生信息。 -- 1、统计课程表course的总课程数； select COUNT(Cid) FROM course; -- 2、统计成绩表sc中统计各个学生的课程数； select Sid,COUNT(Cid) FROM sc GROUP BY Sid; -- 3、以步骤1的查询结果作为限定条件，查询与课程表course的总课程数不相等的Sid select Sid,COUNT(Cid)as TotalCourse FROM sc GROUP BY Sid HAVING TotalCourse not in (select COUNT(Cid) as S FROM course); -- 4、根据步骤3查询出来的Sid结合student表查询学生信息； select * from student where Sid in (select Sid from (select Sid,COUNT(Cid)as TotalCourse FROM sc GROUP BY Sid HAVING TotalCourse not in (select COUNT(Cid) as S FROM course))As A); -- 5、结合sc表和student表确定没有成绩的同学的信息； select * from student WHERE Sid not in (select Sid FROM sc GROUP BY Sid); -- 使用union连接4、5查出结果 select * from student where Sid in (select Sid from (select Sid,COUNT(Cid)as TotalCourse FROM sc GROUP BY Sid HAVING TotalCourse not in (select COUNT(Cid) as S FROM course))As A) UNION select * from student WHERE Sid not in (select Sid FROM sc GROUP BY Sid);
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 简单思路：涉及到的表课程表course、学生信息表student、通过成绩表sc来判断是否所学相同。 -- 思路描述： -- 1、在成绩表sc中查询学号为01的学生Cid； select Cid from sc where Sid = '01'; -- 2、将步骤1的结果作为限定条件再在sc表中使用 IN 查询Cid在步骤1的结果中的学生的Sid; SELECT DISTINCT Sid from sc where Cid in (select Cid from sc where Sid = '01'); -- 在学生信息表student中根据步骤2的查询结果作为限定条件查询学生信息。 select * from student where Sid in (SELECT DISTINCT Sid from sc where Cid in (select Cid from sc where Sid = '01'));
9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
-- 涉及到的表：成绩表sc，学生信息表student -- 思路：要查询和01学号同学学习完全相同的课程，统计sc表中各个学生的课程总数，和01学生相同的学生的Sid，然后根据查询到的Sid在student表中查询即可。 -- 步骤1：查询01号学生的课程总数 select COUNT(Cid) from sc where Sid='01'; -- 步骤2：查询和01号学生的课程总数相同的其他学生的Sid； select Sid from sc GROUP BY Sid HAVING Sid!='01' and COUNT(Cid) = (select COUNT(Cid) from sc where Sid='01'); -- 步骤3：根据步骤2得到的Sid，在student表中查询学生信息 select * from student where Sid in (select Sid from sc GROUP BY Sid HAVING Sid!='01' and COUNT(Cid) = (select COUNT(Cid) from sc where Sid='01'));
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 -- 涉及到的表：老师表teacher，课程表course,成绩表sc,学生信息表student -- 思路：通过老师姓名在老师表teacher中查询课程号Tid，在课程表course中根据Tid查询Cid，然后在成绩表sc中根据Cid，查询没有学过老师课程的学生Sid，再根据查询到的Sid在学生信息表中查询学生姓名。 -- 步骤1：通过老师姓名在老师表teacher中查询课程号Tid； select Tid from teacher where Tname ='张三'; -- 步骤2：在课程表course中根据Tid查询Cid； select Cid from course WHERE Tid in (select Tid from teacher where Tname ='张三'); -- 步骤3：通过查询到的课程号Cid，在成绩表sc中查询没有学过老师课程的学生Sid； select DISTINCT Sid from sc where Cid not in (select Cid from course WHERE Tid in (select Tid from teacher where Tname ='张三')); -- 步骤4：查询到的Sid在学生信息表student中查询学生姓名. SELECT * from student where Sid in (select DISTINCT Sid from sc where Cid not in (select Cid from course WHERE Tid in (select Tid from teacher where Tname ='张三')));
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 10. 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 -- 涉及到的表：成绩表sc,学生信息表student -- 思路：在成绩表sc中先查出成绩不合格的学生信息，然后按照Sid分组统计次数大于等于2次的学生的Sid。再根据查询到的Sid在学生信息表student中查询学生的姓名。 -- 步骤1：在成绩表sc中先查出成绩不合格的学生信息； select * from sc where score <60; -- 步骤2：按照Sid分组统计次数大于等于2次的学生的Sid； select A.Sid from (select Sid from sc where score <60) as A GROUP BY A.Sid HAVING COUNT(A.Sid)>=2 ; -- 步骤3：根据查询到的Sid在学生信息表student中查询学生的姓名。 select student.Sid,Sname from student JOIN (select A.Sid from (select * from sc where score <60) as A GROUP BY A.Sid HAVING COUNT(A.Sid)>=2) AS B on B.Sid = student.Sid; -- 步骤4：根据前面查询到的Sid，在成绩表sc中查询这些学生的平均成绩； SELECT sc.Sid,AVG(score) FROM sc join (select A.Sid from (select * from sc where score <60) as A GROUP BY A.Sid HAVING COUNT(A.Sid)>=2 ) AS C on C.Sid = sc.Sid GROUP BY Sid; -- 步骤5：信息拼接，将平均分拼接到步骤3中 select E.*,F.avgscore from (select student.Sid,Sname from student JOIN (select A.Sid from (select * from sc where score <60) as A GROUP BY A.Sid HAVING COUNT(A.Sid)>=2) AS B on B.Sid = student.Sid) as E JOIN (SELECT sc.Sid,AVG(score) as avgscore FROM sc join (select A.Sid from (select * from sc where score <60) as A GROUP BY A.Sid HAVING COUNT(A.Sid)>=2 ) AS C on C.Sid = sc.Sid GROUP BY Sid) as F on F.Sid = E.Sid;
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
-- 12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息 -- 涉及到的表：成绩表sc,学生信息表student -- 思路：先在sc表中根据课程号检索出‘01’课程的信息，然后去除60分及以上的信息，在进行降序。然后根据查询出的信息中的Sid，结合student表查询学生信息。 -- 步骤1：先在sc表中根据课程号检索出‘01’课程的信息； select Sid from sc where Cid= '01' and score < 60 ORDER BY score DESC; -- 步骤2：根据查询出的信息中的Sid，结合student表查询学生信息。 select student.Sid,Sname,Sage from student JOIN (select Sid from sc where Cid= '01' and score < 60 ORDER BY score DESC) AS A on A.Sid = student.Sid; ;
13. 查询每门课程被选修的学生数
-- 求每门课程的学生人数 -- 思路：根据课程号分组统计 select Cid,COUNT(Sid) from sc GROUP BY Cid;
14. 查询出只选修两门课程的学生学号和姓名
-- 查询出只选修两门课程的学生学号和姓名 -- 思路：在成绩表sc中查询只选修两门课程的学生Sid，再根据Sid在学生信息表中进行查询。 -- 步骤1：成绩表sc中查询只选修两门课程的学生Sid(根据Sid出现的次数进行统计) select Sid from sc GROUP BY Sid HAVING COUNT(Sid)=2; -- 步骤2：将获取到的Sid在student表中查询。 select * from student where Sid in (select Sid from sc GROUP BY Sid HAVING COUNT(Sid)=2);
15. 查询男生、女生人数
-- 查询男生、女生人数 -- 思路：在学生信息表student中根据性别统计 select Ssex,COUNT(Sid) from student GROUP BY Ssex;
16. 查询名字中含有「风」字的学生信息
-- 查询名字中含有「风」字的学生信息 -- 思路：在学生信息表student中使用模糊查询 select * from student where Sname like '%风%';
17. 查询同名同性学生名单，并统计同名人数
-- 查询同名同性学生名单，并统计同名人数 -- 思路：在学生信息表student中根据姓名进行分组，统计出现2次及以上的学生姓名。 -- 步骤1：学生信息表student中根据姓名进行分组，查询同名学生。 select * from student GROUP BY Sname HAVING COUNT(Sname)>1; -- 步骤2：根据查询到的学生姓名在student表中查询这些学生姓名的学生信息。 select * from student where Sname in (select Sname from student GROUP BY Sname HAVING COUNT(Sname)>1) ; -- 根据性别和姓名进行分组，将姓名和性别相同的分到一组，姓名性别相同的即位同名同姓； select *,COUNT(A.Ssex) as '同名人数' from (select * from student where Sname in (select Sname from student GROUP BY Sname HAVING COUNT(Sname)>1))as A GROUP BY A.Ssex,A.Sname HAVING COUNT(A.Ssex)>1;
18. 查询 1990 年出生的学生名单
-- 查询 1990 年出生的学生名单 -- 思路：学生信息表student中根据Sage查询1990年出生的学生。 select * from student where DATE_FORMAT(Sage,'%Y')='1990' ;
19. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
-- 根据课程进行分组查询平均成绩，再排序 select Cid,AVG(score) as '平均成绩' from sc GROUP BY Cid ORDER BY AVG(score) DESC,Cid ASC;
20. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-- 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 -- 在成绩表sc中根据Sid查询每个学生的平均成绩，筛选得到平均成绩大于85的学生的Sid，平均成绩。然后根据得到的学生Sid采用join on 或者两表联查的办法在学生信息表student中得到学生姓名。 -- 方法一：join on -- 步骤一 在成绩表sc中根据Sid查询每个学生的平均成绩，筛选得到平均成绩大于85的学生的Sid，平均成绩。 select Sid,AVG(score) as '平均成绩' from sc GROUP BY Sid HAVING AVG(score) >85; -- 步骤二 根据得到的学生Sid采用join on在学生信息表student中得到学生姓名。 select student.Sid,Sname,A.`平均成绩` from student join (select Sid,AVG(score) as '平均成绩' from sc GROUP BY Sid HAVING AVG(score) >85) as A on A.Sid = student.Sid; -- 方法二：嵌套子查询 -- 步骤一 在成绩表sc中根据Sid查询每个学生的平均成绩，筛选得到平均成绩大于85的学生的Sid，平均成绩。 select Sid,AVG(score) as '平均成绩' from sc GROUP BY Sid HAVING AVG(score) >85; -- 步骤二 根据得到的学生Sid采用两表联查在学生信息表student中得到学生姓名。 select Sname,A.Sid,A.`平均成绩` from student,(select Sid,AVG(score) as '平均成绩' from sc GROUP BY Sid HAVING AVG(score) >85) as A where A.Sid=student.Sid ;
21. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
2021-04-09 09:23:47 星期五 打卡
-- 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 -- 涉及表课程表course，成绩表sc，学生信息表student -- 思路：将课程表和成绩表根据Cid进行两表联查，查出分数低于60分的数学成绩的学生的Sid，再将的到的数据和学生信息表两表联查得到学生姓名。 select student.Sid,Sname,Cid,score from student JOIN (select Sid,course.Cid,score from sc JOIN course on course.Cid = sc.Cid where course.Cname='数学' and sc.score<60) as A on A.Sid = student.Sid;
22. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
2021-04-10 12:15:04 星期六 打卡
-- 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） -- 涉及表： 学生信息表student，课程表course，成绩表sc -- 思路：根据student表得到所有学生的Sid，然后与sc表进行左连接查询，得到Cid，再与课程表左连接。 select A.Sid,A.Sname,course.Cid,Cname,A.score from course right JOIN (select student.Sid,Sname,Cid,score from student LEFT JOIN sc on sc.Sid=student.Sid)as A on A.Cid=course.Cid ORDER BY Sid,Cid;
23. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
2021-04-11 23:16:03 星期天 打卡
-- 涉及表：学生信息表student、成绩表sc、课程表course -- 思路:在成绩表中先查询出成绩在70分以上的Sid、Cid和score，再与学生表进行联查得到姓名，再和成绩表进行联查得到课程名称。 select Sname,course.Cid,score from course join (select student.Sid,Cid,Sname,score from student join (select Sid,Cid,score from sc where score>70) as A on A.Sid =student.Sid) as B on B.Cid=course.Cid;
24. 查询不及格的课程
2021-04-12 09:40:36 星期一 打卡
-- 涉及表：成绩表sc，课程表course -- 思路：根据成绩表查询出不及格的Cid，然后与课程表进行联查。 -- 方法一 select Cid,Cname from course where Cid in (select DISTINCT Cid from sc where score<60); -- 方法二 select course.Cid,Cname from course join (select DISTINCT Cid from sc where score<60) as A on A.Cid=course.Cid;
25. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
2021-04-13 09:20:03 星期二 打卡
-- 涉及表：成绩表sc，学生信息表student -- 思路：现根据课程编号在成绩表中查询出课程编号为01的学生，在筛选出成绩大于80分以上的学生Sid，在根据Sid在学生信息表中查询 -- 方法一 select Sid ,Sname from student where Sid in (select Sid from sc where Cid=01 and score>80); -- 方法二 select student.Sid,Sname from student join (select Sid,Cid,score from sc where Cid=01 and score>80) as A on A.Sid=student.Sid;
26. 求每门课程的学生人数
2021-04-14 09:24:24 星期三 打卡
-- 涉及表：成绩表sc，课程表student（考虑到有的课程一个学生都没有选） -- 思路：在sc表中根据课程号进行分类求和，得出该门课程的学生人数，再和student表进行联查，得出每门课程的学生人数。 SELECT course.Cid,Cname,A.totals from course LEFT JOIN (SELECT Cid,count(Sid) as totals from sc GROUP BY Cid) as A on A.Cid=course.Cid;
27. 查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
2021-04-15 09:37:13 星期四 打卡
-- 涉及表：成绩表sc，教师表teacher，学生信息表student -- 思路：根据teacher表查询张三老师得Cid，根据Cid在成绩表中查询学生得Sid和成绩，最后根据Sid在学生信息表中查询学生信息。 select student.Sid,Sname,Sage,Ssex,A.Cid,A.score from student join (select Cid,Sid,score from sc where Cid in (select Cid from teacher where Tname='张三')) as A on A.Sid =student.Sid;
28. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
2021-04-16 09:23:40 周五 打卡
-- 涉及表：成绩表sc，教师表teacher,课程表，学生信息表student -- 思路：根据老师姓名在教师表中查询出老师的Tid，根据查询出来的Tid在课程表中查询出老师教授课程的Cid，再根据查询出来的Cid在成绩表中查询出成绩最高的学生Sid，再根据Sid在学生信息表中查询出学生信息。 select * from student join (SELECT Sid,score from sc JOIN (select Cid,MAX(score) as '最高分' from sc where Cid in (select Cid from course where Cid in (select Tid from teacher where Tname='张三'))) as A on A.Cid=sc.Cid and A.`最高分`=sc.score) as B on B.Sid = student.Sid;
29. 分别查询各个课程，成绩相同的学生的学生编号、课程编号、学生成绩
2021-04-17 22:34:48 星期六 打卡
-- 涉及表：成绩表sc ，学生信息表student select * from student join (SELECT DISTINCT Sid, C.Cid,C.score from sc as C ,(select Cid,score,COUNT(score) from (select Cid,score from sc ORDER BY Cid ASC) as A GROUP BY A.score,A.Cid HAVING COUNT(score)>=2)as D WHERE C.Cid=D.Cid and C.score=D.score)as E on E.Sid=student.Sid;
30. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
2021-04-18 12:53:37 周天 打卡
-- 涉及表：成绩表sc select Cid,count(Sid) as '选修人数' from sc GROUP BY Cid HAVING count(Sid)>5;
31. 检索至少选修两门课程的学生学号
2021-04-19 16:17:46 周一 打卡
-- 涉及表：成绩表sc 、学生信息表student select * from student JOIN (select Sid,COUNT(Cid) from sc GROUP BY Sid HAVING COUNT(Cid)>=2)as A on A.Sid=student.Sid;
32. 查询选修了全部课程的学生信息
2021-04-20 09:35:07 周二 打卡
-- 涉及表：成绩表sc，课程表course,学生信息表student -- 思路：根据课程表得知所有的课程的Cid，然后根据Cid在成绩表中查询出选修了全部Cid的学生得Sid，再根据Sid在student表中查询相关信息。 select * from student join (select Sid from sc GROUP BY Sid HAVING count(Cid)=(select COUNT(Cid) from course)) as A on A.Sid=student.Sid;
33. 查询各学生的年龄，只按年份来算
2021-04-21 09:16:13 周三 打卡
-- 涉及表：学生信息表student -- 思路：将当前时间的年份减去student表中Sage的年份即可。 select *,DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(Sage,'%Y') as '年龄' from student;
34. 查询本周过生日的学生
2021-04-24 13:30:57 周六 打卡
-- 涉及表：学生信息表student -- 思路：查询Sage在本周的学生信息即可。 select * from student where Sage between current_date()-7 and sysdate();
-- 补充：https://www.cnblogs.com/Luouy/p/7590812.html
35. 查询下周过生日的学生
2021-04-28 10:01:09 周三 打卡
-- 涉及表：学生信息表student select * from student where YEARWEEK(DATE_FORMAT(Sage,'%Y-%m-%d')) = YEARWEEK(CURDATE()) + 1; -- 参考地址：https://blog.csdn.net/weixin_30951389/article/details/95963715
36. 查询本月过生日的学生
2021-04-25 09:51:08 周日 打卡
-- 涉及表：学生信息表student -- 思路：月初 CURDATE() 月末 LAST_DAY(CURDATE()) select * from student where Sage BETWEEN DATE_FORMAT( CURDATE(), ' %Y-%m-01 00:00:00 ' ) and DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59');
37. 查询下月过生日的学生
2021-04-26 09:50:57 周一 打卡
-- 涉及表：成绩表sc -- 思路：PERIOD_DIFF()函数返回两日期之间的差异。现在的时间减去时间字段=-1，即4月减去时间字段=-1，时间字段就等于5月，即下个月。结果以月份计算。PERIOD_DIFF(period1, period2)注意： period1 和 period2 应采用相同的格式。格式：YYMM或YYYYMM -- 下月 SELECT * FROM student WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( Sage, '%Y%m' ) ) =-1; -- 本月 SELECT * FROM student WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( Sage, '%Y%m' ) ) =0; -- 上月 SELECT * FROM student WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( Sage, '%Y%m' ) ) =1;
-- 参考链接：https://www.begtut.com/sql/func-mysql-period-diff.html
38. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
2021-04-22 11:10:58 周四 打卡
-- 涉及表：成绩表sc -- 思路：先根据Sid，统计每个学生的选修课程数，再统计每个学生的总成绩，然后总成绩除以选修课程数得到平均成绩，再与sc表联查，得到所有学生所有课程的成绩。 select sc.*,D.`平均成绩` from sc,(select A.Sid,A.`总成绩`/ B.`选修科目数` as '平均成绩' from (select Sid,SUM(score) as '总成绩' from sc GROUP BY Sid)as A join (select Sid,COUNT(Cid) as '选修科目数' from sc GROUP BY Sid) as B on B.Sid=A.Sid ORDER BY A.`总成绩`/ B.`选修科目数` DESC )as D;
练习题来源：https://blog.csdn.net/flycat296/article/details/63681089
浙公网安备 33010602011771号