MySQL 基本查询34--40

题目34：查询男女生人数

select 
 s_sex
 ,count(s_sex) as `人数`
from Student
group by s_sex;

题目35：查询名字中含有风字的学生信息

-- 模糊匹配：在两边都加上了%，考虑的是姓或者名字含有风
select * from Student where s_name like "%风%";

题目36：查询同名同性的学生名单，并统计同名人数

select 
 a.s_name
 ,a.s_sex
 ,count(*)
from Student a  -- 同一个表的自连接
join Student b
on a.s_id != b.s_id   -- 连接的时候不能是同一个人：学号保证，每个人的学号是唯一的，其他字段都可能重复
and a.s_sex = b.s_sex  -- 性别相同
and a.s_name = b.s_name -- 名字相同
group by 1,2;

题目37：查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号c_id升序排列

select 
 c.c_id
 ,c.c_name
 ,round(avg(sc.s_score),2) avg_score
from Score sc
join Course c
on sc.c_id = c.c_id
group by 1,2
order by 3 desc, c.c_id;  -- 指定字段和排序方法

题目38：查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select 
 sc.s_id
 ,s.s_name
 ,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 85;

题目39：查询所有学生的课程及分数（均分、总分）情况

select 
  s.s_id
 ,s.s_name
 ,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文'  -- 语文分数
 ,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学'
 ,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语'
 ,round(avg(sc.s_score),2) as '平均分'  -- 每个人的平均分 
 ,sum(sc.s_score) as '总分'  -- 每个人的总分
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c 
on sc.c_id = c.c_id
group by s.s_id, s.s_name;   -- 学号和姓名的分组

题目40：查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT
	t1.s_id,
	t1.s_name,
	t1.s_sex,
    t1.s_birth,
	t3.c_name,
    t3.c_id,
	MAX(t2.s_score)
FROM
	student t1
JOIN score t2 ON t1.s_id = t2.s_id
JOIN course t3 ON t2.c_id = t3.c_id
JOIN teacher t4 ON t3.t_id = t4.t_id
WHERE
	t4.t_name = '张三';