mysql查询实践题
-- students表 SID, Sname, sage,ssex
-- teacher表 TID,Tname
-- course表 CID,Cname,TID
-- sc表 SID,CID,Score
-- 1.查询课程C01比C02课程成绩高的所有学生的学号 select sc1.SID from (select SID,CID,Score from sc where CID='C01') as sc1 join (select SID,CID,Score from sc where CID='C02') as sc2 on sc1.SID=SC2.SID where sc1.Score>sc2.SCore; insert into sc values('S003','C03','100'); -- 2.查询平均成绩大于60分的学号和姓名alter select a.SID,students.Sname,a.avg_score from (select SID,avg(Score) as avg_score from sc group by sid having avg(score)>=60) as a join students on a.SID=students.SID; -- 3.查询所有同学的学号、姓名、选课数、总成绩 select stud.SID,stud.Sname,a.count_cid,a.sum_sc from (select SID,Sname from students) as stud left join (select SID,count(CID) as count_cid,sum(score) as sum_sc from sc group by SID) AS a on stud.SID=a.SID; -- 4 查询姓“李”的老师的个数,不能重复 select count(distinct(TID)) from teacher where TName like "李%"; -- 5 查询没有学过张三老师课的学号和姓名 select SID from sc where CID not in( select a.CID from (select CID,TID from course) as a join (select TID from teacher where Tname='张三') as b on a.TID=b.TID); -- 6 查询两门以上不及格课程的同学的学号及其平均成绩 select SID, avg(score) from sc where SID in (select SID from sc where score<60 group by SID having count(cid)>=2); select count(distinct(SID)) as stu_num from students; select count(distinct(sid)) from sc group by CID; -- 7全部学生都选修的课程 select CID from sc group by CID having count(distinct(SID))= (select count(distinct(SID)) as stu_num from students); -- 8统计每门学生的选修人数,超过2名的才统计 select CID,count(distinct(SID)) from sc group by CID having count(distinct(SID))>=2; -- 9查询每门功课最好的前两名,输出学号和课程ID,成绩,按课程号升序排列,成绩按降序排列 select SID,CID,score from sc AS A where SID in (select SID from (select SID from sc where CID=A.CID order by score desc limit 0,2) as a) order by CID,score desc; -- 10 查询选修张三老师课程的学生中,成绩最高的学生姓名以及成绩 select b.SID,b.sname,a.CID,max(a.score) from (select SID,Sname from students) as b join (select CID,SID,score from sc where CID in (select CID from course where TID= (select TID from teacher where TName="张三"))) as a on b.SID=a.SID; -- 11 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程号升序排列alter select CID,avg(score) from sc group by CID order by avg(score) desc,CID ASC; -- 12 查询学生总成绩以及名次 -- 13统计各科成绩·,各分段人数,结果包括课程ID、课程名称,[100-85],[85-70],[70-60],[ <60] select sc.CID,a.Cname,SUM(CASE WHEN Score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS "[100 - 85]", SUM(CASE WHEN Score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS "[85 - 70]", SUM(CASE WHEN Score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS "[70 - 60]", SUM(CASE WHEN Score < 60 THEN 1 ELSE 0 END) AS "[60 -]" from (select Cname,CID from course) as a join sc on a.CID=sc.CID group by sc.CID; -- 14 查询各科的及格率 -- C02没有查询到,因为C02满足socre>60的记录为0 select CID,count(distinct(SID)) as num from sc where score>60 group by CID; select a.CID,a.num,b.sum_num from (select CID,count(distinct(SID)) as num from sc where score>60 group by CID) as a left join (select CID,count(distinct(SID)) as sum_num from sc group by CID) as b on a.CID=B.CID; -- 正解 select b.CID,a.num/b.sum_num from (select CID,count(SID) as sum_num from sc group by CID) as b join (select CID,sum(case when score>=60 then 1 else 0 end) as num from sc group by CID)as a on b.CID=a.CID; -- 15 求各科的最低分和最高分 select CID,max(score),min(score) from sc group by CID; -- 16 查询出生2000年之前的学生名单 select SID,Sname from students where extract(year from now())-sage<=2000; -- 17 查询选课少于两门课程的学生名单 select SID,Sname from students where SID not in(select SID from sc group by SID having count(CID)>=2); -- 18 查询英语成绩第三名的学生成绩单 select * from sc where SID=(select SID from sc where CID= (select CID from course where Cname="英语") order by score limit 2,1);
mysql时间函数
1.curtime() 返回当前时间 eg. 23:26:30
2. curdate() 返回当前日期 eg. 2020-6-11
3.now() 返回当前日期时间 eg.2020-6-11 23:26:30
4.date() 提取日期部分 eg.date(now()) 2020-6-11
5.datediff() 返回两个日期之前的天数 eg.datediff('2020-6-11','2020-5-11') 输出30
6.extract(unit from date) 提取日期时间的单独部分
unit值
Unit 值 |
---|
MICROSECOND |
SECOND |
MINUTE |
HOUR |
DAY |
WEEK |
MONTH |
QUARTER |
YEAR |
SECOND_MICROSECOND |
MINUTE_MICROSECOND |
MINUTE_SECOND |
HOUR_MICROSECOND |
HOUR_SECOND |
HOUR_MINUTE |
DAY_MICROSECOND |
DAY_SECOND |
DAY_MINUTE |
DAY_HOUR |
YEAR_MONTH |