SELECTs.s_sex,count(1)as 人数
FROMstudent s
groupbys.s_sex
2.3 查询名字中含有"风"字的学生信息
SELECT*FROMstudent
WHEREs_name like"%风%"
3. 日期相关例题(6题)
3.1 查询各学生的年龄
(按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一)
-- if函数selecta.*,year(NOW())-year(a.s_brith)-if(DATE_FORMAT(now(),"%m%d")>DATE_FORMAT(a.s_brith,"%m%d"),0,1)as age
FROMstudent a-- case函数select s_brith,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_brith,'%Y')-(casewhen DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_brith,'%m%d')then0else1end))as age
from student;
selecta.*,avg(a.s_score)over(PARTITIONby a.s_id)as avg_score
FROMscore a
方法二:临时表连接
SELECTa.*,t.avg_score
FROMscore a,(SELECTa.s_id,round(avg(a.s_score),2)as avg_scoreFROMscore agroupbya.s_id) t
WHEREa.s_id=t.s_id
orderbyt.avg_score desc
方法三:长型数据转为宽型数据
SELECTa.s_id,ifnull((select s_score from score where s_id=a.s_id and c_id="01"),0)as"语文",ifnull((select s_score from score where s_id=a.s_id and c_id="02"),0)as"数学",ifnull((select s_score from score where s_id=a.s_id and c_id="03"),0)as"英语",ifnull(round(avg(a.s_score),2),0)as avg_score
FROMscore a
groupbya.s_id
orderbyifnull(round(avg(a.s_score),2),0)desc
SELECT a.*,(selectcount(s_score)from score b where a.c_id=b.c_id and a.s_score<b.s_score)+1 rk,(selectcount(distinct s_score)from score b where a.c_id=b.c_id and a.s_score<=b.s_score) den_rk
FROMscore a
orderbyc_id,s_score desc
4.3 查询学生的总成绩并进行排名
方法一:开窗函数
SELECTt.*,rank()over(orderby sum_score desc) rank排名
FROM(SELECTs_id,sum(s_score)as sum_scoreFROMscoregroupbys_id) t
4.4 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
方法一:子查询+开窗函数
SELECTa.*,t.c_id,t.rk,t.s_score
FROMstudent a,(SELECTa.s_id,a.c_id,a.s_score,dense_rank()over(PARTITIONby a.c_id orderby a.s_score desc)as rkFROMscore a) t
WHEREt.rk in(2,3)ANDa.s_id=t.s_id
4.5 查询学生平均成绩及其名次
方法一: 开窗函数
SELECTt.*,rank()over(orderby t.avg_score desc) 排名
FROM(SELECTa.s_id,round(avg(a.s_score),2)as avg_scoreFROMscore agroupbya.s_id) t
4.6 查询各科成绩前三名的记录
方法一:开窗函数
SELECTt.*from(SELECTa.c_id,a.c_name,b.s_score,rank()over(PARTITIONby a.c_id orderby b.s_score desc) rkFROMcourse aLEFTJOINscore bONa.c_id=b.c_id) t
WHEREt.rk<=3;
方法二:子查询
SELECT*from(SELECTa.c_id,a.c_name,b.s_score,(selectcount(c.s_score)from score c where a.c_id=c.c_id and b.s_score<c.s_score)+1as rkFROMcourse aLEFTJOINscore bONa.c_id=b.c_id) t
WHEREt.rk<=3orderbyt.c_name,t.rk asc;
4.7 查询每门功成绩最好的前两名
方法一:开窗函数
SELECTt.s_id,t.c_id,t.s_score
FROM(SELECT*,rank()over(PARTITIONby b.c_id orderby b.s_score desc) rkFROMscore b) t
WHEREt.rk<=2;
方法二:自连接
SELECTt.s_id,t.c_id,t.s_score
FROM(SELECTa.*,(selectcount(1)from score b where b.c_id=a.c_id and a.s_score<b.s_score)+1as rkFROMscore aorderbya.c_id,rk) t
WHERE t.rk<=2
方法三:条件查询+子查询
SELECTa.*FROMscore a
WHERE(selectcount(1)from score b where b.c_id=a.c_id and a.s_score<b.s_score)+1<=2orderbya.c_id
5. 表连接+子查询+聚合函数查询(34题)
5.1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
方法一:自连接,同列比较,使用自查询
思路:先找出查询条件的学生信息及分数,根据子查询得到最终结果
SELECT
st.*,t1.sc1,t1.sc2
FROMstudent st,(SELECTs1.s_id,s1.s_score as sc1,s2.s_score as sc2FROMscore s1,score s2WHERE s1.c_id="01"AND s2.c_id="02"ANDs1.s_id=s2.s_idANDs1.s_score>s2.s_score) t1
WHEREst.s_id=t1.s_id;
方法二:表连接
SELECT
st.*,s1.s_score as sc1,s2.s_score as sc2
FROMstudent st
leftJOINscore s1
ONs1.s_id=st.s_id
leftJOINscore s2
ONs2.s_id=st.s_id
WHERE s1.c_id="01"AND s2.c_id="02"ANDs1.s_id=s2.s_id
ANDs1.s_score>s2.s_score
数据长型数据变为宽型数据
-- IF函数或case函数SELECTa.*,t.s01,t.s02
fromstudent a,(SELECTa.s_id,max(casewhen a.c_id="01"then a.s_score end)as s01,max(casewhen a.c_id="02"then a.s_score end)as s02
-- max(if(a.c_id="01",a.s_score,null)) as s01,-- max(if(a.c_id="02",a.s_score,null)) as s02fromscore agroupbya.s_id) t
WHEREa.s_id=t.s_id
ANDt.s01>t.s02
5.2 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
与上一题思路一致,条件大于变小于
方法一:自连接
SELECT
st.*,t1.sc1,t1.sc2
FROMstudent st,(SELECTs1.s_id,s1.s_score as sc1,s2.s_score as sc2FROMscore s1,score s2WHERE s1.c_id="01"AND s2.c_id="02"ANDs1.s_id=s2.s_idANDs1.s_score<s2.s_score) t1
WHEREst.s_id=t1.s_id;
方法二:表连接
SELECTst.*,s1.s_score as sc1,s2.s_score as sc2
FROMstudent st
leftJOINscore s1
ONs1.s_id=st.s_id
leftJOINscore s2
ONs2.s_id=st.s_id
WHERE s1.c_id="01"AND s2.c_id="02"ANDs1.s_id=s2.s_id
ANDs1.s_score<s2.s_score-- 方法二SELECTst.*,s1.s_score as sc1,s2.s_score as sc2
FROMstudent st
leftJOINscore s1
ONs1.s_id=st.s_id
ANDs1.c_id="01"leftJOINscore s2
ONs2.s_id=st.s_id
ANDs2.c_id="02"ANDs1.s_id=s2.s_id
WHEREs1.s_score<s2.s_score
方法三:数据长型数据变为宽型数据
-- IF函数或case函数SELECTa.*,t.s01,t.s02
fromstudent a,(SELECTa.s_id,max(casewhen a.c_id="01"then a.s_score end)as s01,max(casewhen a.c_id="02"then a.s_score end)as s02
-- max(if(a.c_id="01",a.s_score,null)) as s01,-- max(if(a.c_id="02",a.s_score,null)) as s02fromscore agroupbya.s_id) t
WHEREt.s01<t.s02
ANDa.s_id=t.s_id
5.3 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
方法一:子查询
-- 子查询一SELECT
st.s_id,st.s_name,t.avg_s
FROMstudent ST,(SELECTs.s_id,round(avg(s.s_score),2)as avg_sFROM score sGROUPBYs.s_idHAVINGround(avg(s.s_score),2)>=60) t
WHEREst.s_id=t.s_id-- 方法二:子查询二SELECTs.s_id,(select s_name from student where s_id=s.s_id)as s_name,round(avg(s.s_score),2)as avg_s
FROM score s
GROUPBYs.s_id
HAVINGavg_s>=60
方法二:表连接
SELECTa.s_id,a.s_name,round(avg(b.s_score),2)as avg_score
FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
GROUPBYa.s_id
HAVINGround(avg(b.s_score),2)>=60;
5.4 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
方法一:子查询
-- 有成绩的SELECTa.s_id,a.s_name,t.avg_acore
FROMstudent a,(SELECTa.s_id,round(avg(a.s_score),2)as avg_acoreFROMscore aGROUPBYa.s_idHAVINGround(avg(a.s_score),2)<60) t
WHEREa.s_id=t.s_idUNION-- 没有成绩的:没有成绩的s_id不存在SELECTa.s_id,a.s_name,0as avg_acore
FROMstudent a
WHEREa.s_id notin(SELECTDISTINCT s_id FROM score);
方法二:表连接
SELECTa.s_id,a.s_name,ifnull(round(avg(b.s_score),2),0)as avg_score
FROMstudent a
LEFTJOINscore b
on a.s_id=b.s_id
GROUPBYa.s_id
HAVINGavg_score<60
5.5 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECTa.s_id,a.s_name,count(b.c_id)as cnt_course,ifnull(sum(b.s_score),0)as sum_score
FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
groupbya.s_id
5.6 查询学过"张三"老师授课的同学的信息
方法一:表连接+子查询单层嵌套
SELECTa.*FROMstudent a
LEFTJOINscore b
on a.s_id=b.s_id
LEFTJOINcourse c
ONb.c_id=c.c_id
where c.t_id in(SELECT t_id FROM teacher WHERE t_name ="张三")
方法二:表连接+子查询多层嵌套
SELECTa.*FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
WHEREb.c_id in(SELECTc_id
FROMcourse where t_id in(SELECT t_id from teacher where t_name="张三"));
方法三:多表连接
selecta.*fromstudent a,score b,course c,teacher d
WHEREa.s_id=b.s_id
ANDb.c_id=c.c_id
ANDc.t_id=d.t_id
ANDd.t_name="张三"
SELECTs.*FROMstudent s
WHEREs.s_id NOTIN(-- 查找学的学生SELECTDISTINCTa.s_idFROMstudent aLEFTJOIN score b ON a.s_id = b.s_idWHEREb.c_id IN(-- 查找学过的课程SELECT c_idFROM courseWHERE t_id IN(SELECT t_id FROM teacher WHERE t_name ="张三")))
方法二:条件查询+子表连接
SELECT*FROMstudent s
WHEREs.s_id notin(selecta.s_idfromscore a,course b,teacher cWHEREa.c_id=b.c_idANDb.t_id=c.t_idANDc.t_name="张三")
5.8 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
方法一:子查询+自连接,同列对比可以用自连接
SELECT*FROMstudent s
WHEREs.s_id in(SELECTa.s_idFROMscore a,score bWHEREa.c_id="01"ANDb.c_id="02"ANDa.s_id=b.s_id)
方法二:连表+自连接,同列对比可以用自连接
SELECTs.*FROMstudent s
LEFTJOIN score a
ONs.s_id=a.s_id
LEFTJOINscore b
ONa.s_id=b.s_id
WHEREa.c_id="01"ANDb.c_id="02"
SELECTa.*FROMstudent a,(selecta.s_id,max(if(a.c_id="01",a.s_score,null))as s01,max(if(a.c_id="02",a.s_score,null))as s02fromscore agroupbya.s_id) t
WHEREa.s_id=t.s_id
ANDt.s01 isnotnullANDt.s02 isnull
5.10 查询没有学全所有课程的同学的信息
方法一:条件查询+子查询
SELECTs.*FROMstudent s
WHEREs.s_id in(SELECTa.s_idFROMscore agroupbya.s_idhavingcount(1)<(selectcount(1)from course))
方法二:表连接
SELECTs.*,count(a.c_id) cnt
FROMstudent s
LEFTJOINscore a
ONa.s_id=s.s_id
groupbys.s_id
HAVINGcount(a.c_id)<(selectcount(1)from course)
5.11 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
方法一:子查询
SELECTs.*FROMstudent s
WHEREs.s_id in(SELECTdistinct a.s_idFROMscore aWHEREa.c_id in(SELECTb.c_idFROMscore bWHEREb.s_id="01"))ANDs.s_id!='01'
方法二:表连接+子查询
SELECTa.*FROMstudent a
LEFTJOINscore b
on a.s_id=b.s_id
WHEREb.c_id in(SELECTb.c_idFROMscore bWHEREb.s_id="01")groupby1,2,3,4
5.12 查询和"01"号的同学学习的课程完全相同的其他同学的信息
筛选课程与01号一样的数据,计算课程数与01一致的
SELECTs.*FROMstudent s
WHEREs.s_id in(SELECTdistinct a.s_idFROMscore aWHEREa.c_id in(SELECTa.c_idFROMscore aWHEREa.s_id="01")ANDa.s_id!="01"groupby a.s_idHAVINGcount(distinct a.c_id)=(selectcount(1)from score a where a.s_id="01"))
5.13 查询没学过"张三"老师讲授的任一门课程的学生姓名
查询学过张三老师的学生,在学生表中反向查询
SELECTs.s_name
FROMstudent s
WHEREs.s_id notin(SELECTa.s_idFROMscore aWHEREa.c_id in(SELECTa.c_idFROMcourse aWHEREa.t_id in(SELECT t.t_id FROM teacher t WHERE t.t_name="张三")))
5.14 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
方法一:表连接+分组+having条件
SELECTa.s_id,a.s_name,round(avg(b.s_score),2)as avg_score
FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
groupbya.s_id
havingsum(if(b.s_score>=60,0,1))>=2
方法二:自连接+子查询
selecta.s_id,a.s_name,round(avg(b.s_score),2)as avg_score
FROMstudent a,score b
WHEREa.s_id=b.s_id
ANDa.s_id in(SELECTa.s_idFROMscore aWHEREa.s_score<60groupbya.s_idHAVINGcount(1)>=2)groupbya.s_id
方法三:表连接+子查询
selecta.s_id,a.s_name,round(avg(b.s_score),2)as avg_score
FROMstudent a
LEFTJOINscore b
ona.s_id=b.s_id
wherea.s_id in(SELECTa.s_idFROMscore aWHEREa.s_score<60groupbya.s_idHAVINGcount(1)>=2)groupbya.s_id
5.15 检索"01"课程分数小于60,按分数降序排列的学生信息
方法一:表连接
SELECTa.*,b.c_id,b.s_score
FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
WHEREb.c_id="01"and b.s_score<60orderbyb.s_score desc
5.16 查询各科成绩最高分、最低分和平均分
以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
方法一:if语句
SELECTa.c_id,a.c_name,max(b.s_score)as max_score,min(b.s_score)as min_score,round(avg(b.s_score),2)as avg_score,round(100*sum(if(b.s_score>=60,1,0))/count(1),2)as"及格率",round(100*sum(if(b.s_score>=70and b.s_score<80,1,0))/count(1),2)as"中等率",round(100*sum(if(b.s_score>=80and b.s_score<90,1,0))/count(1),2)as"优良率",round(100*sum(if(b.s_score>=90,1,0))/count(1),2)as"优秀率"FROMcourse a,score b
WHEREa.c_id=b.c_id
groupbya.c_id
SELECTc.t_name,a.c_name,round(avg(b.s_score),2)as avg_score
FROMcourse a
leftJOINscore b
ONa.c_id=b.c_id
LEFTJOINteacher c
ONa.t_id=c.t_id
groupbyc.t_name,a.c_name
orderbyavg_score DESC
5.19 查询每门课程被选修的学生数
SELECTa.c_id,a.c_name,count(1)as cnt
FROMcourse a
LEFTJOINscore b
ON a.c_id=b.c_id
groupbya.c_id
5.20 查询出只有两门课程的全部学生的学号和姓名
方法一:连表
SELECTdistinct a.s_id,a.s_name
FROMstudent a,score b
WHEREa.s_id=b.s_id
groupbya.s_id
HAVINGcount(b.c_id)=2
方法二:条件查询
select s_id,s_name
from student
where s_id in(select s_id from score GROUPBY s_id HAVINGCOUNT(c_id)=2);
select a.s_name,a.s_sex,count(*)from student a
JOIN student b
on a.s_id !=b.s_id
and a.s_name = b.s_name
and a.s_sex = b.s_sex
GROUPBY a.s_name,a.s_sex
5.22 查询每门课程的平均成绩
结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECTa.c_id,round(avg(a.s_score),2)as avg_score
FROMscore a
groupbya.c_id
orderbyavg_score desc,a.c_id asc
5.23 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
方法一:子查询
SELECTa.s_id,a.s_name,t.avg_score
FROMstudent a,(SELECTa.s_id,round(avg(a.s_score),2)as avg_scoreFROMscore agroupbya.s_idHAVINGavg_score>=85) t
WHERE a.s_id=t.s_id
ANDt.avg_score isnotnull
方法二:表连接
selecta.s_id,b.s_name,ifnull(round(avg(a.s_score),2),0)as avg_score
FROMscore a
LEFTJOINstudent b
ONa.s_id=b.s_id
GROUPBYa.s_id
HAVINGavg_score>=85
5.24 查询课程名称为"数学",且分数低于60的学生姓名和分数
方法一:条件查询+子查询
SELECTb.s_name,a.s_score
FROMscore a
LEFTJOINstudent b
ONa.s_id=b.s_id
WHEREc_id in(SELECT c_id FROM course where c_name="数学")ANDa.s_score<60
方法二:多表连接
SELECTb.s_name,a.s_score
FROMscore a
LEFTJOINstudent b
ONa.s_id=b.s_id
LEFTJOINcourse c
ONa.c_id=c.c_id
WHEREc.c_name="数学"ANDa.s_score<60
5.25 查询所有学生的课程及分数情况
方法一:表连接
SELECTa.s_name,c.c_name,b.s_score
FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
LEFTJOINcourse c
ONc.c_id=b.c_id
方法二:if函数
SELECTa.s_id,a.s_name,sum(if(c.c_name="语文",b.s_score,0))as"语文",sum(if(c.c_name="数学",b.s_score,0))as"数学",sum(if(c.c_name="英语",b.s_score,0))as"英语",sum(b.s_score)as"总分"FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
LEFTJOINcourse c
ONc.c_id=b.c_id
groupbya.s_id,a.s_name
方法三:case函数
selecta.s_id,a.s_name,sum(casewhen c.c_name="语文"then b.s_score else0end)as"语文",sum(casewhen c.c_name="数学"then b.s_score else0end)as"数学",sum(casewhen c.c_name="英语"then b.s_score else0end)as"英语",sum(b.s_score)as"总分"FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
LEFTJOINcourse c
ONc.c_id=b.c_id
groupbya.s_id,a.s_name
5.26 查询任何一门课程成绩在70分以上的姓名、课程名称和分数(学生的每门课都大于70)
方法一:表连接+子查询
SELECTa.s_name,c.c_name,b.s_scoreFROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
LEFTJOINcourse c
ONc.c_id=b.c_id
WHEREa.s_id in(select s_id from score groupby s_id havingmin(s_score)>70);
5.27 查询不及格的课程
方法一:表连接
SELECTdistinctb.s_id,b.c_id,a.c_name,b.s_score
fromcourse a
LEFTJOINscore b
ONa.c_id=b.c_id
WHEREb.s_score<60
5.28 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
方法一:子查询
SELECTt.s_id,t.s_name
FROMstudent t
WHEREt.s_id in(SELECTa.s_idFROMscore aWHEREa.c_id="01"ANDa.s_score>80)
方法二:表连接
selecta.s_id,a.s_name
fromstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
WHEREb.c_id="01"ANDb.s_score>80
5.29 求每门课程的学生人数
SELECTa.c_name,count(1)as"人数"FROMcourse a
LEFTJOINscore b
ONa.c_id=b.c_id
groupbya.c_id
5.30 查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
方法一:表连接+子查询
SELECTa.*,b.s_score as max_score,b.c_id,c.c_name
FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
LEFTJOINcourse c
ONc.c_id=b.c_id
WHERE-- 查询idb.c_id in(SELECTc_idFROMcourse WHEREt_id in(select t_id from teacher where t_name="张三"))AND-- 查询最大分数b.s_score=(selectdistinctmax(s_score)from score where c_id="02")
方法二:表连接
SELECTa.*,b.s_score as max_score,b.c_id,c.c_name
FROMstudent a
LEFTJOINscore b
ONa.s_id=b.s_id
LEFTJOINcourse c
ONc.c_id=b.c_id
LEFTJOINteacher d
ONd.t_id=c.t_id
WHEREd.t_name="张三"orderbymax_score desclimit1;
5.31 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECTdistincta.*FROMscore a,score b
WHEREa.c_id!=b.c_id
ANDa.s_score=b.s_score
文章目录 一、介绍1.1 xml 介绍1.2 xml 标准1.3 xml 教程1.4 xml 构成 二、C/C xml 库选型2.1 选型范围2.2 RapidXML2.3 tinyxml22.4 pugixml2.5 libxml 五、性能比较5.1 C xml 相关的操作有哪些5.2 rapidxml、Pugixml、TinyXML2 文件读取性能比较 六、其他问题6.1 version和 e…
yolo及yolo的变体
anchor boxes (锚框)
intersection over union 并集交集
用于计算两个边界框的差异程度
bounding box predictions 边界框预测
non maximum suppression非极大值抑制
为了分离这些边界框并为每个对象获得单个边界框,我们使用IOU。这种获取单…
第六届信息与计算机前沿技术国际学术会议(ICFTIC 2024)
2024 6th International Conference on Frontier Technologies of Information and Computer 第六届信息与计算机前沿技术国际学术会议(ICFTIC 2024)将在中国青岛举行,会期是2024年11月8-10日,为…