mysql经典例题

目录

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

2.查询学过「张三」老师授课的同学的信息

3.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列

4.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

5.查询下⽉过生日的学生

---

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

select 
	t1.*
	,t2.CId as t2_cid
	,t2.score as t2_score  -- 指定了查询的返回结果，包括了从表 t1 和表 t2 中选取的所有列，并且为表 t2 中的课程ID和分数指定了别名 t2_cid 和 t2_score
from SC t1,SC t2   -- 表自连接，表 t1 和表 t2 都来自于同一张表 SC。这种自连接的方式意味着会从同一张表中检索不同的数据
where t1.SId=t2.SId  and t1.cid='01' and t2.cid='02'  and t1.score > t2.score  -- 查询的条件部分，条件确保只检索那些在课程 '01' 中的成绩高于课程 '02' 的学生数据

2.查询学过「张三」老师授课的同学的信息

select * from Student where sid in (
	select sid from SC where cid = (
		select cid from Course where tid = 
			(select tid from Teacher where tname='张三')
	)
)
-- 四张表联合查询

3.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列

select 
	t1.*
	,t2.cname
from 
	(select 
		cid
		,count(*) cnt
		,max(score) mac_sco
		,min(score) min_sco
		,avg(score) avg_sco
		,sum(if(score>=60,1,0))/count(*) jigelv
-- if(score>=60,1,0)条件语句会检查每个学生的分数是否大于等于60。如果是，返回1；否则返回0。这样就将及格的学生分数转换为1，不及格的转换为0。
-- sum(if(score>=60,1,0)):计算了所有及格学生的数量，因为之前将及格的分数都转换成了1，所以直接求和即可得到及格的学生数目。
		,sum(if(score>=70 and score <80,1,0))/count(*) zhongdelv
		,sum(if(score>=80 and score <90,1,0))/count(*) youlianglv
		,sum(if(score>=90,1,0))/count(*) youxiulv
	from SC 
	group by cid) t1
join Course t2
on t1.cid=t2.cid
ORDER BY t1.cnt desc , t1.cid asc

4.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select 
	t2.sid
	,t2.sname
	,t1.cnt
	,t1.sum_sco
from 
	(select 
		sid
		,count(cid) as cnt
		,sum(score) as sum_sco 
	from SC 
	group by sid) t1
right join Student t2  -- right join实现没学过的显示为null
on t1.sid=t2.sid;

5.查询下⽉过生日的学生

select 
	* 
	,month(Sage)
	,month(DATE_ADD(CURRENT_DATE,INTERVAL 1 month))
from Student
where month(Sage) = month(DATE_ADD(CURRENT_DATE,INTERVAL 1 month))
-- 注意不能写month+1，否则月份为12时会异常