【大数据开发】MySQL数据库——SQL查询语句练习高级进阶②day30

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值