mysql 整理

https://www.cnblogs.com/ShaYeBlog/p/5871969.html

-- 获取日期的月份
select month(now())
-- 添加单位时间
select timestampadd(day,1,now())
-- 格式化时间
select date_format('2020-10-01 12:59:59','%Y-%m-%d')

-- 通过取余数的方式，获取下个月月份
select month(now()) mod 12

-- 两个日期的单位时间间隔
select timestampdiff(day,'2020-10-01','2020-10-07')

-- 获取当前为第几周
select week(now())

-- ROUND() 函数用于把数值字段舍入为指定的小数位数。

ROUND(column_name,decimals)

-- mysql between and 包含边界值

-- 42、查询每门功成绩最好的前两名
select a.s_id,a.c_id,a.s_score from score a
 where (select COUNT(1) from score b where b.c_id=a.c_id and a.s_score< b.s_score)<2 order by a.c_id

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select st.s_id,st.s_name,sc.c_id,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
left join course c on c.c_id=sc.c_id
where (
select count(1) from student st2
left join score sc2 on sc2.s_id=st2.s_id
left join course c2 on c2.c_id=sc2.c_id
where sc.s_score=sc2.s_score and c.c_id!=c2.c_id
)>1

-- 24、查询学生平均成绩及其名次
set @i=0;
select a.*,@i:=@i+1 from (
select st.s_id,st.s_name,round((case when avg(sc.s_score) is null then 0 else avg(sc.s_score) end),2) "平均分" from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id order by sc.s_score desc) a

-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select c.c_id,c.c_name
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=100 and sc.s_score>80)/(select count(1) from score sc where sc.c_id=c.c_id )) "100-85"
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=85 and sc.s_score>70)/(select count(1) from score sc where sc.c_id=c.c_id )) "85-70"
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=70 and sc.s_score>60)/(select count(1) from score sc where sc.c_id=c.c_id )) "70-60"
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=60 and sc.s_score>=0)/(select count(1) from score sc where sc.c_id=c.c_id )) "60-0"
from course c order by c.c_id

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)  平均成绩为空的，小于60是取不到的
select st.s_id,st.s_name,(case when ROUND(AVG(sc.s_score),2) is null then 0 else ROUND(AVG(sc.s_score)) end ) cjScore from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id having AVG(sc.s_score)<60 or AVG(sc.s_score) is NULL

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT st.*
FROM student st
INNER JOIN score sc ON sc.`s_id`=st.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`c_id`="01" OR sc.`c_id`="02" ,1,0))>1

-- 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select DISTINCT st.* from student st
inner join score sc on sc.s_id = st.s_id
where sc.c_id in (
    select c_id from score sc2
    where sc2.s_id='01'
)
-- group by st.s_Id

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

select  st.* from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id
having group_concat(sc.c_id) =
(
select  group_concat(sc2.c_id) from student st2
left join score sc2 on sc2.s_id=st2.s_id
where st2.s_id ='01'
)

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
 -- 可加round,case when then else end 使显示更完美
select
st.s_id,st.s_name,avg(sc4.s_score) "平均分",sc.s_score "语文",sc2.s_score "数学",sc3.s_score "英语" from student st
left join score sc  on sc.s_id=st.s_id  and sc.c_id="01"
left join score sc2 on sc2.s_id=st.s_id and sc2.c_id="02"
left join score sc3 on sc3.s_id=st.s_id and sc3.c_id="03"
left join score sc4 on sc4.s_id=st.s_id
group by st.s_id
order by SUM(sc4.s_score) desc

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select sc.c_id,c.c_name,max(sc.s_score) '最高分',min(sc.s_score) '最低分',avg(sc.s_score) '平均分',
((select count(1) from score sc1 where sc1.s_score>=60 and sc1.c_id=sc.c_id)/(select count(1) from score sc1 where sc1.c_id=sc.c_id)) AS '及格率',
((select count(1) from score sc1 where sc1.s_score>=70 and sc1.s_score<80 and sc1.c_id=sc.c_id)/(select count(1) from score sc1 where sc1.c_id=sc.c_id)) AS '中等率',
((select count(1) from score sc1 where sc1.s_score>=80 and sc1.s_score<90 and sc1.c_id=sc.c_id)/(select count(1) from score sc1 where sc1.c_id=sc.c_id)) AS '优良率',
((select count(1) from score sc1 where sc1.s_score>=90 and sc1.c_id=sc.c_id)/(select count(1) from score sc1 where sc1.c_id=sc.c_id)) AS '优秀率'
from score sc
left join course c on sc.c_Id =c.c_id
group by sc.c_id

-- 19、按各科成绩进行排序，并显示排名
-- mysql没有rank函数
-- 加@score是为了防止用union all 后打乱了顺序
select c1.s_id,c1.c_id,c1.c_name,@score:=c1.s_score,@i:=@i+1 from (
select c.c_name,sc.* from course c
left join score sc on sc.c_id=c.c_id
where c.c_id="01" order by sc.s_score desc) c1 ,
(select @i:=0) a
union all
select c2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1 from (select c.c_name,sc.* from course c
left join score sc on sc.c_id=c.c_id
where c.c_id="02" order by sc.s_score desc) c2 ,
(select @ii:=0) aa
union all
select c3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1 from (select c.c_name,sc.* from course c
left join score sc on sc.c_id=c.c_id
where c.c_id="03" order by sc.s_score desc) c3;
set @iii=0;