作为数据分析人员心中犹如神明地位的数据库结构化查询语言,简单易懂、源代码开放、速度可靠及其适用性使得mysql毫无疑问是当之无愧的天之骄子。【Mysql经典例题讲解】系列共计46道题目,以‘增删改查’为目标、学生表、成绩表、课程表以及教师表数据为基础,包含各类简单查询以及基础函数实战。【Mysql经典例题讲解(五)】是此系列最后一讲,mysql基础篇暂时告一段落。但这仅仅是开端,小编会继续mysql深层学习。
36.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩select distinct a.* from sc as a left join sc as b on a.sid=b.sidwhere a.score=b.score and a.cid!=b.cid
37.查询每门功课成绩最好的前两名
select * from sc as t1where (select count(*) from sc as t2 where t1.cid = t2.cid and t2.score > t1.score) < 2ORDER BY t1.cid,score desc
38.统计每门课程的学生选修人数(超过 5 人的课程才统计)
select sc.cid as 课程编号,count(*) as 选修人数from sc GROUP BY sc.cidHAVING count(*) > 5
39.检索至少选修两门课程的学生学号
select DISTINCT t1.sidfrom sc as t1 where (select count(*) from sc where t1.sid=sc.sid) > 2
40. 查询选修了全部课程的学生信息
select student.*from student,scwhere sc.sid=student.sidGROUP BY sc.sidHAVING count(*)=(SELECT DISTINCT count(*) from course)
41.查询各学生的年龄,只按年份来算
select student.sid as 学生编号,student.sname as 学生姓名,TIMESTAMPDIFF(YEAR,student.sage,CURDATE()) as 学生年龄from student
#TIMESTAMPDIFF(unit,datetime1,datetime2)函数:unit为计算结果显示格式,对日期差值的计算方式为datetime2-datetime1的差值
42.按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
SELECT sname,sage,case when (DATE_FORMAT(NOW(),'%m-%d') < DATE_FORMAT(sage,'%m-%d')) then year(now()) - year(sage) - 1else year(now()) - year(sage) end as 年龄FROM student
#DATE_FORMAT(date,format)函数:date参数是合法的日期;format规定日期/时间的输出格式
43.查询本周过生日的学生
SELECT * FROM student where week(Sage)=week(now())
44. 查询下周过生日的学生
SELECT * FROM student where week(Sage)=week(now()) + 1
45.查询本月过生日的学生
SELECT * FROM studentwhere month(Sage)=month(now())
46.查询下月过生日的学生
SELECT * FROM studentwhere month(Sage)=month(now()) + 1