关联关系

--31、查询1990年出生的学生名单

(1)SELECT * from student where s_birth like '1990%';

(2)SELECT * from student where YEAR(s_birth)='1990';

2中写法结果一样

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

分析思路：需要求每门课程的平均成绩，group by c_id

SELECT c_id, avg(s_score) avs from score GROUP BY c_id ORDER BY avs desc, c_id asc;

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

分析思路：avg(s_score)>=85;  student表

SELECT s.s_id, s_name, r.avs from student s,

(SELECT s_id, avg(s_score) avs from score GROUP BY s_id having avs>=85)r

where s.s_id=r.s_id;

--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

分析思路：course表 c_name=数学；s_score<60；s_name和分数。

SELECT s.s_name, sc.s_score from student s,score sc where s.s_id= sc.s_id and sc.c_id = (SELECT c_id from course where c_name='数学') and s_score<60;

-- 35、查询所有学生的课程及分数情况

分析思路：可使用类似17题第二种方法的查询

SELECT s.s_id,

(SELECT s_score from score where s_id=s.s_id and c_id='01') as '语文',

(SELECT s_score from score where s_id=s.s_id and c_id='02') as '数学',

(SELECT s_score from score where s_id=s.s_id and c_id='03') as '英语',

SUM(s_score) Total from score s GROUP BY s.s_id ORDER BY total desc;

我发现这样做有点问题，好像不是所有的学生，因为有没选课的学生，那么用下面的查询语句：

SELECT s_name,r.* from student s LEFT JOIN

(SELECT s.s_id,

(SELECT s_score from score where s_id=s.s_id and c_id='01') as '语文',

(SELECT s_score from score where s_id=s.s_id and c_id='02') as '数学',

(SELECT s_score from score where s_id=s.s_id and c_id='03') as '英语',

SUM(s_score) Total

from score s GROUP BY s.s_id ORDER BY total desc)r

on s.s_id=r.s_id;

或者：

select a.s_id, a.s_name,

SUM(case c.c_name when '语文' then b.s_score else 0 end) as '语文',

SUM(case c.c_name when '数学' then b.s_score else 0 end) as '数学',

SUM(case c.c_name when '英语' then b.s_score else 0 end) as '英语',

SUM(b.s_score) as  '总分'

from student a left join score b on a.s_id = b.s_id

left join course c on b.c_id = c.c_id GROUP BY a.s_id, a.s_name；

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

分析思路：查出分数大于70 的学生编号

SELECT s.s_name, c.c_name, r.s_score from student s, course c,

(SELECT * from score where s_score>70)r

where s.s_id=r.s_id and c.c_id=r.c_id;

--37、查询不及格的学生编号，课程编号，课程名称及分数

SELECT s.s_id, s.c_id, c.c_name, s.s_score from score s RIGHT JOIN

course c on s.c_id=c.c_id and s.s_score <60;

--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

分析思路：过滤条件：c_id=01 ands_score>80；s_id和s_name 需要student和score表

SELECT s_id, s_name from student where s_id in(SELECT s_id from score where c_id='01' and s_score>80);

没有

-- 39、求每门课程的学生人数

SELECT c_id, count(s_id) from score GROUP BY c_id;

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

分析思路：查询张三老师所交科目的学生分数，降序排列，limit1。

SELECT s.*, sc.c_id, sc.s_score from student s, score sc, course c,teacher t 

where s.s_id=sc.s_id and sc.c_id=c.c_id 

and c.t_id=t.t_id and t_name='张三'

ORDER BY sc.s_score desc limit 1;

或者：SELECT s.*,r.c_id, r.s_score from student s

RIGHT JOIN (SELECT * from score where c_id=

(SELECT c_id from course c, teacher t where c.t_id=t.t_id and t_name="张三")

ORDER BY s_score desc LIMIT 1)r on s.s_id=r.s_id;

--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

分析思路：课程不同 and成绩相同

select DISTINCT b.s_id, b.c_id, b.s_score from score a, score b where a.c_id != b.c_id and a.s_score = b.s_score；

--42、查询每门功成绩最好的前两名

分析思路：和25题一样

SELECT * from score where

(select count(*) from score as a

where score.c_id = a.c_id and score.s_score<a.s_score)<2

order by c_id asc, s_score desc;

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

分析思路：count()>5; order by count() desc,  c_id

SELECT c_id, count(*) from score GROUP BY c_id having count(*)>5 ORDER BY count(*) desc, c_id;

-- 44、检索至少选修两门课程的学生学号

SELECT s_id from score GROUP BY s_id having count(c_id)>1;

--45、查询选修了全部课程的学生信息

 分析思路：和11题相反。查询共有几门课，学生选修课数与总课数相等

SELECT * from student where s_id in

(SELECT s_id from score GROUP BY s_id having count(s_id)=

(SELECT count(*) from course));

--46、查询各学生的年龄

SELECT s.*, TIMESTAMPDIFF (year, s_birth, curdate()) as '年龄' from student s;

-- 47、查询本周过生日的学生

SELECT * from student  where WEEKOFYEAR (s_birth)=WEEKOFYEAR(CURDATE());

没有

-- 48、查询下周过生日的学生

SELECT * from student 

where WEEKOFYEAR(s_birth)=WEEKOFYEAR(CURDATE())+1;

-- 49、查询本月过生日的学生

SELECT * from student where month(s_birth)=MONTH(CURDATE());

-- 50、查询下月过生日的学生

SELECT * from student where month(s_birth)=MONTH(CURDATE())+1;

经典50道面试题分析方法及查询语句都在这3篇的内容上了，表数据是网上找的，但是建库建表，插入数据都是自己一步步做的，每一题都有自己的解题方法。希望自己的SQL编写能力有了进一步的提升，同时也希望能给各位小伙伴提供一些思路和方法，如果各位还有更好的方法还请评论区分享，大家共同学习。