数据库的连表查询

1，一张学生的name表,包含id,和name,如下图

2.一张分数表包含学生id和分数score，如下图

3.查询出大于平均分数的学生的名字和性别和分数，按分数降序排序；

SELECT  id_n, name_n, score, sex FROM `name_table` a INNER JOIN score_table b ON a.id_n=b.id_n1 
WHERE score>(SELECT  AVG(score) FROM `name_table` a INNER JOIN score_table b ON a.id_n=b.id_n1) 
ORDER BY score DESC;

4.查询出大于平均分数的男女生的人数：

SELECT  sex as “性别”, COUNT(*) FROM `name_table` a INNER JOIN score_table b ON a.id_n=b.id_n1 
WHERE score>(SELECT  AVG(score) FROM `name_table` a INNER JOIN score_table b ON a.id_n=b.id_n1) GROUP BY sex;