数据库常用到的查询积累

-- 1、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩  
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
  student b 
  join score a on b.s_id = a.s_id
  GROUP BY b.s_id,b.s_name HAVING avg_score >=60;
-- 2、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from 
  student a 
  left join score b on a.s_id=b.s_id
  GROUP BY a.s_id,a.s_name;
-- 3、查询"李"姓老师的数量 
select count(t_id) from teacher where t_name like '李%';
-- 4、查询每门课程被选修的学生数 （分组查询）
select c_id,count(s_id) from score a GROUP BY c_id
-- 5、查询男生、女生人数 
select s_sex,COUNT(s_sex) as 人数  from student GROUP BY s_sex
-- 6、查询名字中含有"风"字的学生信息 （模糊是查询）
select * from student where s_name like '%风%';
-- 7、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
  select c_id,ROUND(AVG(s_score),2) as avg_score from score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC
--8、查询各学生的年龄  按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
  select s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - 
    (case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age
  from student;
-- 9、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d')) =WEEK(s_birth)
-- 10、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)