-- 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 |
看完点赞 ~养成好习惯,以上内容希望对你有帮助,如果对软件测试、接口测试、自动化测试、面试经验交流感兴趣可以加入我们。642830685,免费领取最新软件测试大厂面试资料和Python自动化、接口、框架搭建学习资料!技术大牛解惑答疑,同行一起交流。