31、查询课程编号为 01 且课程成绩在 80 分以其上的学生的学号和姓名
方式一、
SELECT t1.Sname,t1.SId,t2.score
from student as t1
INNER JOIN sc as t2
on t1.SId=t2.SId
where t2.CId='01' and t2.score>=80;
方式二、
SELECT Sname,SId
from student
WHERE SId in(
SELECT SId
from sc
where CId='01' and score>=80)
32、求每门课程的学生人数
SELECT cid,count(sid)
FROM sc
GROUP BY CId
33、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
知识点：limit的用法
select student.*,sc.score
from student ,course ,teacher ,sc
where course.CId=sc.CId
and course.TId=teacher.TId
and teacher.Tname='张三'
and student.SId =sc.SId
ORDER BY sc.score DESC
limit 0,1
书写要注意：teacher.Tname开始写成了teacher.Tid
SELECT student.*,sc.score
from student
INNER JOIN sc on student.SId=sc.SId
INNER JOIN course on course.CId=sc.CId
INNER JOIN teacher on teacher.TId=course.TId
where teacher.Tname = "张三"
LIMIT 1
34、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
and sc.score = (
select Max(sc.score)
from sc,student, teacher, course
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
);
35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
知识点： exists？,同23题一样类型
select *
from sc as t1
where exists(
select * from sc as t2 where t1.SId=t2.SId and t1.CId!=t2.CId and t1.score =t2.score )
36、查询每门功成绩最好的前两名
存在疑问？？
select *
from sc as t1
where (select count(*) from sc as t2 where t1.CId=t2.CId and t2.score >t1.score)<2
ORDER BY t1.CId
37、统计每门课程的学生选修人数（超过 5 人的课程才统计）
思考：假如其中一张关联表有分组过滤等限制条件怎么使用关联语句
select sc.CId as 课程编号,count(*) as 选修人数
from sc
GROUP BY sc.CId
HAVING count(*)>5
可以再完善把课程表关联进来
38、检索至少选修两门课程的学生学号
SELECT SId
from sc
GROUP BY SId
HAVING count(*)>2
39、查询选修了全部课程的学生信息
SELECT *
FROM student
WHERE SId in(
SELECT SId from sc
GROUP BY SId HAVING count(*)=(select DISTINCT count(*) from course ))
答案：select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )
40、查询各学生的年龄，只按年份来算
知识点：时间函数，年等的运算
MYSQL中 datediff、timestampdiff函数的使用
SELECT SId,Sname,Ssex,year(now())-YEAR(Sage) as "年龄"
from student
答案：timestampdiff函数
select SId,Sname,TIMESTAMPDIFF(YEAR,Sage,now()) 年龄 from student;
41、按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
知识点：DATE_FORMAT() 函数
MySQL DATE_FORMAT() 函数
select SId,Sname,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(Sage,'%Y')
-(case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(Sage,'%m%d') then 0 else 1 end)) 年龄
from student;
42、查询本周过生日的学生
select *
from student
where YEARWEEK(student.Sage)=YEARWEEK(CURDATE())
43、查询下周过生日的学生
select *
from student
where YEARWEEK(student.Sage)=CONCAT(YEAR(CURDATE()),week(CURDATE())+1)
44、查询本月过生日的学生
select *
from student
where EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM CURDATE())
45、查询下月过生日的学生
select *
from student
where EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
