经典mysql题_MySQL经典题目

一、常见的SQL面试题：经典50题。

表结构：

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

1. 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分(groupby分组聚合)

select courseid,max(score),min(score) from score group by courseid;

2. 查询平均成绩大于60分学生的学号和平均成绩(having)

select stuid,avg(score)

from score

group by stuid

having avg(score) > 60;

3. 查询同名同性学生名单并统计同名人数

select stuname,count(stuid)

from student

group by stuname

having count(stuid) > 1;

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

select courseid,avg(score)

from score

group by courseid

order by avg(score),courseid desc;

6. 查询两门以上不及格课程的同学的学号及其平均成绩

select stuid,avg(score) as avg_score

from score

where score < 60

group by stuid

having count(courseid) >=2;

7. 查询所有课程成绩小于80分学生的学号、姓名

select student.stuid,student.stuname

from student

where stuid not in(

select distinct stuid

from score

where score >80

);

8. 1990年出生的学生名单(stubirtthdate为date类型)

select stuid,stuname

from student

where year(stubirthdate) = 1990;

9.

TOPN问题 查询各科成绩最高的记录(关联子查询)

select *

from score as a

where score = (select max(score) from score as b where a.courseid = b.courseid);

上述关联子查询顺序

1. select * from score

2. select max(score) from score where courseid = 0001

3. select * from score where score =81 and courseid = 0001

TOPN问题 查询各科成绩前两名的记录(union)

(select * from score where courseid = 0001 order by score desc limit 2)

union

(select * from score where courseid = 0002 order by score desc limit 2)

union

(select * from score where courseid = 0003 order by score desc limit 2);

10. 查询出每门课程的及格人数和不及格人数(case表达式)

select courseid,

sum(case when score >= 60 then 1 else 0 end) as jigerenshu,

sum(case when score < 60 then 1 else 0 end) as bujigerenshu

from score

group by courseid;

11. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称(case表达式)

select score.courseid,course.coursename,

sum(case when score between 85 and 100 then 1 else 0 end) as '[85-100]',

sum(case when score between 70 and 85 then 1 else 0 end) as '[70-85]',

sum(case when score between 60 and 70 then 1 else 0 end) as '[60-70]'

from score right join course

on score.courseid = course.courseid

group by courseid;

12. 行列互换

下面是学生的成绩表(表名score，列名：学号、课程号、成绩)

使用sql实现将该表行转列为下面的表结构

select stuid,

max(case courseid when '0001' then score else 0 end) as '课程号0001',

max(case courseid when '0002' then score else 0 end) as '课程号0002',

max(case courseid when '0003' then score else 0 end) as '课程号0003'

from score

group by stuid;

二、互联网校招SQL笔试经典50题及答案解析

表结构

学生表Student

教师表Teacher

课程表Course

成绩表SC

1. 查询“01”课程比“02”课程成绩高的所有学生的学号(生成新表 表连接)

select distinct t1.sid as sid

from

(select * from sc where cid='01') as t1

left join

(select * from sc where cid='02') as t2

on t1.sid=t2.sid

where t1.score>t2.score;

2. 查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名

select sid,sname

from student

where sid IN

(select `sid`

from SC

group by `sid`

#分组保证 学生 01 和 02课程都学过

having count(if(cid='01',score,null))>0 and count(if(cid='02',score,null))>0);

3. 查询所有课程成绩小于60分的同学的学号、姓名；

select s_sid.`sid`,student.sname

from (select distinct `sid`

from sc

group by `sid`

having max(score) < 60)s_sid

left join student on s_sid.`sid` = student.`sid`;

4. 查询没有学全所有课的同学的学号、姓名；

select ssid.`sid`,student.sname

from (select `sid`

from sc

group by `sid` having count(`score`) < (select count(*) from course))ssid

left join student on ssid.`sid` = student.`sid`;

5. 查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名

select

distinct sc.sid

from

(

select

cid

from sc

where sid='01'

)t1

left join sc

on t1.cid=sc.cid;

6. 查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名

select t1.`sid` ,student.sname

from

(select sc.`sid`

from

(

select cid

from sc

where `sid` = 01

)t1

left join sc on t1.`cid` = sc.`cid`

group by `sid`

having count(distinct sc.cid)= (select count(distinct cid) from sc where sid = '01'))t1

left join student on t1.`sid` = student.`sid`;

7. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select ssid.`sid`,student.sname,ssid.`avg(score)`

from

(

select sid,avg(score)

from sc

group by sid

having count(if(score<60,score,NULL)) >= 2

)ssid left join student on ssid.`sid` = student.`sid`;

8. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(窗口函数)

select `sid`,rank_num,score,`cid`

from

(

select rank() over(PARTITION by `cid` order by score desc) as rank_num,

`sid`,`score`,`cid`

from sc

)t

where rank_num in (2,3);

9. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select sc.`cid`,cname,

count(if(score between 85 and 100,sid,NULL))/count(sid)

,count(if(score between 70 and 85,sid,null))/count(sid)

,count(if(score between 60 and 70,sid,null))/count(sid)

,count(if(score between 0 and 60,sid,null))/count(sid)

from sc

left join course on sc.`cid` = course.`cid`

group by sc.`cid`;

10. 查询学生平均成绩及其名次(窗口函数)

select sid,avg_score,rank() over(order by avg_score desc)

from

(select sid,avg(score) as avg_score

from sc

group by `sid`)t;

11. 查询各科成绩前三名的记录(窗口函数)

select * from

(select `cid`,`sid`,rank() over (PARTITION by `cid` order by score desc) as rank_num

from sc)t

where rank_num <=3;

12. 查询各学生的年龄 (curdate())

select sid,sname,year(curdate())-year(sage) as age

from student;

2020.4.19 15:00