day26里已经发过数据库脚本,直接导入即可
21、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select s1.sid,s2.sid,sc1.cid,sc1.score,sc2.score
from student s1,student s2,sc sc1,sc sc2
where s1.sid!=s2.sid and s1.sid=sc1.sid and s2.sid=sc2.sid and sc1.cid!=sc2.cid and sc1.score=sc2.score;
22、查询每门功成绩最好的前两名
SELECT st.*,sc.c_id,sc.s_score
FROM student st JOIN score sc
ON st.s_id=sc.s_id
ORDER BY sc.s_score DESC
LIMIT 0,2
23、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT co.c_id,COUNT(co.c_id) counts
FROM student st JOIN score sc
ON st.s_id=sc.s_id
JOIN course co
ON sc.c_id=co.c_id
GROUP BY co.c_id
HAVING counts>5
ORDER BY counts DESC, co.c_id
24、检索至少选修两门课程的学生学号
SELECT st.*,COUNT(st.s_id)
FROM student st JOIN score sc
ON st.s_id=sc.s_id
JOIN course co
ON sc.c_id=co.c_id
GROUP BY st.s_id
HAVING COUNT(st.s_id)>=2
25、查询选修了全部课程的学生信息
SELECT st.*
FROM student st JOIN score sc
ON st.s_id=sc.s_id
JOIN course co
ON sc.c_id=co.c_id
GROUP BY st.s_id
HAVING COUNT(st.s_id) =(
SELECT COUNT(c_id)
FROM course
)
26、查询各学生的年龄,按照出生日期来算。如果当前月日 < 出生年月的月日,则年龄减一
-- 草稿
SELECT *,DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth age,
(
SELECT DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth-1
WHERE DATE_FORMAT(NOW(),"%m-%d")<DATE_FORMAT(st.s_birth,"%m-%d")
)newAge
FROM student st
-- 写法一
SELECT *,DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth age,
(
CASE
WHEN DATE_FORMAT(NOW(),"%m-%d")<DATE_FORMAT(st.s_birth,"%m-%d") THEN DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth-1
ELSE DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth
END
)newAge
FROM student st
-- 写法二
SELECT *,DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth age,
(
IF(
(DATE_FORMAT(NOW(),"%m-%d")<DATE_FORMAT(st.s_birth,"%m-%d")),
DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth-1,
DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth
)
)newAge
FROM student st
27、查询本周过生日的学生
-- 草稿
SELECT 8-DAYOFWEEK(NOW())
SELECT DATE_FORMAT(NOW(),"%m%d")-(DAYOFWEEK(NOW())-1)
SELECT DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")-(DAYOFWEEK(NOW())-1),"%m-%d")
SELECT DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")+(8-DAYOFWEEK(NOW())),"%m-%d")
-- 答案
SELECT *
FROM student
WHERE DATE_FORMAT(s_birth,"%m-%d")
BETWEEN DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")-(DAYOFWEEK(NOW())-1),"%m-%d")
AND DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")+(8-DAYOFWEEK(NOW())),"%m-%d")
28、查询下周过生日的学生
SELECT *
FROM student
WHERE DATE_FORMAT(s_birth,"%m-%d")
BETWEEN DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")-(DAYOFWEEK(NOW())-1) + 7,"%m-%d")
AND DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")+(8-DAYOFWEEK(NOW())) + 7,"%m-%d")
29、查询本月过生日的学生
SELECT *
FROM student
WHERE DATE_FORMAT(s_birth,"%m")=DATE_FORMAT(NOW(),"%m")
30、查询下月过生日的学生
SELECT *
FROM student
WHERE DATE_FORMAT(s_birth,"%m")=DATE_FORMAT(NOW(),"%m")+1