mysql sql语句 面试_sql语句面试50题(Mysql版附解析)

本人最近在自学sql,从开始学到自己写完本练习50题大概花了12天的时间。

学习路径:《sql基础教程》第1遍(3天)→知乎中的sql网课+leetcode刷题(4天)→牛客网刷题(2天)→《sql基础教程》第2遍(1晚上)→sql面试练习50题(3天)

文中的代码都是按自己思路写的,大家在做题的时候最好也按照自己的思路来写,实在想不通的再参考我的思路,毕竟每个人的编写思路不一样,唯有自己思考过才会印象深刻。有问题的小伙伴可以评论留言,一起探讨~

表名和字段

Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别

–2.课程表

Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号

–3.教师表

Teacher(t_id,t_name) --教师编号,教师姓名

–4.成绩表

Score(s_id,c_id,s_score) --学生编号,课程编号,分数

测试数据

--建表

--学生表

CREATE TABLE `Student`(

`s_id` VARCHAR(20),

`s_name` VARCHAR(20) NOT NULL DEFAULT '',

`s_birth` VARCHAR(20) NOT NULL DEFAULT '',

`s_sex` VARCHAR(10) NOT NULL DEFAULT '',

PRIMARY KEY(`s_id`)

);

--课程表

CREATE TABLE `Course`(

`c_id` VARCHAR(20),

`c_name` VARCHAR(20) NOT NULL DEFAULT '',

`t_id` VARCHAR(20) NOT NULL,

PRIMARY KEY(`c_id`)

);

--教师表

CREATE TABLE `Teacher`(

`t_id` VARCHAR(20),

`t_name` VARCHAR(20) NOT NULL DEFAULT '',

PRIMARY KEY(`t_id`)

);

--成绩表

CREATE TABLE `Score`(

`s_id` VARCHAR(20),

`c_id` VARCHAR(20),

`s_score` INT(3),

PRIMARY KEY(`s_id`,`c_id`)

);

--插入学生表测试数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--课程表测试数据

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

--教师表测试数据

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

--成绩表测试数据

insert into Score values('01' , '01' , 80);

insert into Score values('01' , '02' , 90);

insert into Score values('01' , '03' , 99);

insert into Score values('02' , '01' , 70);

insert into Score values('02' , '02' , 60);

insert into Score values('02' , '03' , 80);

insert into Score values('03' , '01' , 80);

insert into Score values('03' , '02' , 80);

insert into Score values('03' , '03' , 80);

insert into Score values('04' , '01' , 50);

insert into Score values('04' , '02' , 30);

insert into Score values('04' , '03' , 20);

insert into Score values('05' , '01' , 76);

insert into Score values('05' , '02' , 87);

insert into Score values('06' , '01' , 31);

insert into Score values('06' , '03' , 34);

insert into Score values('07' , '02' , 89);

insert into Score values('07' , '03' , 98);

练习题目及sql语句

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

-- [分析] 筛选出课程号01这门课的成绩比02这门课成绩高的学生,输出这些学生的信息及课程分(表:Student,Score)——表联结

select a.*, b.s_score as 01_score, c.s_score as 02_score

from Student as a inner join Score as b on a.s_id = b.s_id and b.c_id = '01'

inner join Score as c on a.s_id = c.s_id and c.c_id = '02'

where b.s_score > c.s_score;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

-- [分析] 与上体的思路一样,换个符合方向就可

select a.*, b.s_score as 01_score, c.s_score as 02_score

from student as a inner join score as b on a.s_id = b.s_id and b.c_id = '01'

inner join score as c on a.s_id = c.s_id and c.c_id = '02'

where b.s_score < c.s_score;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

-- [分析] 要求平均成绩,就需要用group by对学生分组,然后利用聚合函数avg求出平均成绩,由于where语句中不能包含聚合函数,故再利用having语句和60分比较。

-- 输出的结果学生编号和学生姓名在student表中,成绩在score表中,故需要用到内联结。

select a.s_id, a.s_name, avg(s_score) as avg_score

from student as a inner join score as b on a.s_id = b.s_id

group by a.s_id,a.s_name

having avg(b.s_score) >= 60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

-- [分析] 和3题思路一样,改个符号方向即可

select a.s_id, a.s_name, avg(s_score) as avg_score

from student as a inner join score as b on a.s_id = b.s_id

group by b.s_id

having avg(b.s_score) < 60;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

-- [分析]学生编号、学生姓名在表student中;选课数可通过在表score中通过group by 学生编号后利用count(c_id)算出;所有课程的总成绩则用sum可以算出。

-- 因为要用到表student和表score,故需要对表进行联结。

select a.s_id, a.s_name, count(b.c_id), sum(b.s_score)

from student as a inner join score as b on a.s_id = b.s_id

group by a.s_id,a.s_name;

6、查询"李"姓老师的数量

-- [分析] 这里用到知识点字符串模糊查询,如:where 姓名 like '猴%' 即可查到猴什么什么。

-- 关于字符串模糊查询的知识点可查看我的另一篇文章《SQL学习笔记——汇总分析》

select count(t_name) as number

from teacher

where t_name like '李%';

7、查询学过"张三"老师授课的同学的信息

-- [分析] 老师的信息在表teacher中,可通过teacher.t_id与表course联结,

-- 再通过course.c_id与表score联结,

-- 再再通过score.s_id与表student联结,查找出上过张三老师课的同学的信息。

select s.*

from teacher as a inner join course as b on a.t_id = b.t_id

inner join score as c on b.c_id = c.c_id

inner join student as s on c.s_id = s.s_id

where a.t_name = '张三';

8、查询没学过"张三"老师授课的同学的信息

-- [分析]这里不能直接将“=”替换成“<>”,因为不等于张三时,还有别的老师。所以要用到子查询来解决。

select *

from student

where s_id not in

(select s.s_id

from teacher as a inner join course as b on a.t_id = b.t_id

inner join score as c on b.c_id = c.c_id

inner join student as s on c.s_id = s.s_id

where a.t_name = '张三');

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

-- [分析]由于既要学过01课程的,又要学过02课程的,所以共需要联结两次才能达到筛选效果

select *

from student

where s_id in (select a.s_id from student as a inner join score as b on a.s_id = b.s_id and b.c_id = '01'

inner join score as c on b.s_id = c.s_id and c.c_id = '02');

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- [分析] 在上述语句中用and联结,注意不能直接将“=”换成“<>”

select *

from student

where s_id in (select a.s_id from student as a inner join score as b on a.s_id = b.s_id and b.c_id = '01')

and s_id not in (select a.s_id from student as a inner join score as c on a.s_id = c.s_id and c.c_id = '02');

11、查询没有学全所有课程的同学的信息

-- [分析]没学全所有课程的情况很多,如学0门,学1门,学2门。所以这里先筛选出学全所有课程的同学再取反,即没有学全所有课程的同学。

select *

from student

where s_id not in (select s_id

from score

group by s_id

having count(c_id) = (select count(distinct c_id) from course));

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 *

-- [分析]子查询两次,第一次找到学号为01的同学所学的课程,第二次再找到至少一门课与学号01同学上的课相同的同学信息

select *

from student

where s_id in (select distinct a.s_id from score as a

where a.c_id in(select b.c_id from score as b

where b.s_id = '01')) and s_id <> '01';

-- 方法2:内联结+一次子查询

select distinct a.*

from student as a inner join score as b on a.s_id = b.s_id

where b.c_id in (select c_id from score where s_id = '01')

and b.s_id <> '01' ;

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 (不会)

-- [分析] 利用子查询 查出学号01的课程数 再去找课程数一样的其他同学

select student.*

from student

where s_id in (select s_id from score group by s_id having count(c_id) = (select count(c_id) from score where s_id = '01')

and s_id <> '01');

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

-- [分析] 方法一:疯狂子查询

select s_name

from student

where s_id not in (select s_id from score

where c_id = (select c_id from course

where t_id = (select t_id from teacher

where t_name = '张三')));

方法二:子查询+内联结

select s_name

from student

where s_id not in (select a.s_id from score as a inner join course as b on a.c_id = b.c_id

inner join teacher as c on b.t_id = c.t_id and c.t_name = '张三');

15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

-- [分析] 内联结+筛选

select a.s_id,a.s_name,avg(b.s_score) as avg_score

from student as a inner join score as b on a.s_id = b.s_id

where a.s_id in (select s_id from score where s_score < 60 group by s_id having count(s_score) >= 2)

group by a.s_id,a.s_name; -- where筛选出来后一定不要忘记分组显示!!!

-- 方法2 (简单)

select a.s_id,a.s_name,avg(b.s_score)

from student as a inner join score as b on a.s_id = b.s_id and b.s_score < 60

group by a.s_id,a.s_name

having count(b.s_score) >= 2;

16、检索"01"课程分数小于60,按分数降序排列的学生信息

-- [分析] 内联结+子查询筛选+排序

select a.*,b.c_id,b.s_score

from student as a inner join score as b on a.s_id = b.s_id and b.c_id = 01

where a.s_id in (select s_id from score where s_score < 60 order by s_score desc);

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重要!)

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

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

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

round(avg(s_score),2) as 平均分

from score as a

group by a.s_id

order by 平均分 desc;

-- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率--及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90(重要!)

-- [分析] 用到case语句,后面记得按课程号分组来显示全部结果

select a.c_id,a.c_name,max(b.s_score),min(b.s_score),avg(b.s_score),

round(100*(sum(case when b.s_score >= 60 then 1 else 0 end)/sum(case when b.s_score then 1 else 0 end)),2) as 及格率,

round(100*(sum(case when b.s_score between 70 and 80 then 1 else 0 end)/sum(case when b.s_score then 1 end)),2) as 中等率,

round(100*(sum(case when b.s_score between 80 and 90 then 1 else 0 end)/sum(case when b.s_score then 1 end)),2) as 优良率,

round(100*(sum(case when b.s_score >= 90 then 1 else 0 end)/sum(case when b.s_score then 1 end)),2) as 优秀率

from course as a inner join score as b on a.c_id = b.c_id

group by a.c_id,a.c_name;

19、按各科成绩进行排序,并显示排名(涉及到TopN问题,重要!!!)

-- [分析] 本题涉及到窗口函数的使用 重点记住语句:dense_rank() over (partition by 列名 order by 排序用列 desc

select s_id, c_id, s_score, dense_rank() over (PARTITION by c_id order by s_score desc) as rank_

from score;

20、查询学生的总成绩并进行排名

select s_id,sum(s_score)

from score

group by s_id

order by sum(s_score) desc;

21、查询不同老师所教不同课程平均分从高到低显示

select a.t_id,avg(b.s_score)

from course as a inner join score as b on a.c_id = b.c_id

group by a.t_id

order by avg(b.s_score) desc;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select a.*,sum(b.s_score)

from student as a inner join score as b on a.s_id = b.s_id

group by s_id -- 一定不要忘记分组啊啊啊啊啊!

order by sum(b.s_score) desc

limit 1,2 -- 1代表把排名第一的数去了,2代表去除第一名后往下筛选两个出来

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

select a.c_id, a.c_name,

sum(case when b.s_score between 85 and 100 then 1 else 0 end) as '100-85',

round(100*(sum(case when b.s_score between 85 and 100 then 1 end)/sum(case when b.s_score then 1 end)),2),

sum(case when b.s_score between 70 and 85 then 1 end) as '85-70',

round(100*(sum(case when b.s_score between 75 and 85 then 1 end)/sum(case when b.s_score then 1 end)),2),

sum(case when b.s_score between 60 and 70 then 1 end) as '70-60',

round(100*(sum(case when b.s_score between 60 and 70 then 1 end)/sum(case when b.s_score then 1 end)),2),

sum(case when b.s_score < 60 then 1 end) as 不及格,

round(100*(sum(case when b.s_score <=60 then 1 end)/sum(case when b.s_score then 1 end)),2)

from course as a inner join score as b on a.c_id = b.c_id

group by a.c_id,a.c_name

24、查询学生平均成绩及其名次

select s_id,avg(s_score),rank() over (order by avg(s_score) desc) as rank_score

from score

group by s_id

25、查询各科成绩前三名的记录(重要!!!!!)

-- [分析]这道题最简单的写法就是分别写出课程号,然后用union关键字合并

(select * from score where c_id = '01' order by s_score desc limit 3)

UNION

(select * from score where c_id = '02' order by s_score desc limit 3)

UNION

(select * from score where c_id = '03' order by s_score desc limit 3);

26、查询每门课程被选修的学生数

-- [分析] 有点简单,写完甚至有点难以置信,还亲自去数了一遍。。。。。。

select c_id, count(s_id)

from score

group by c_id;

27、查询出只有两门课程的全部学生的学号和姓名

-- [分析] 内联结+筛选

select a.s_id,a.s_name

from student as a inner join score as b on a.s_id = b.s_id

group by a.s_id

having count(b.c_id) = 2;

28、查询男生、女生人数

方法1:

select s_sex,count(s_sex) as 人数

from student

group by s_sex;

方法2:

(select s_sex,count(s_sex) as 人数 from student where s_sex = '男')

union

(select s_sex,count(s_sex) as 人数 from student where s_sex = '女')

29、查询名字中含有"风"字的学生信息

select *

from student

where s_name like "%风%";

30、查询同名同性学生名单,并统计同名人数(重要!!!!!)

-- [分析] 将表student定义两次,然后进行内联结,并给出判断条件

select a.s_name,a.s_sex,count(*)

from student as a inner join student as b on a.s_id <> b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex

group by a.s_name,a.s_sex;

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

-- [分析] 字符串模糊查询

select *

from student

where s_birth like "1990%";

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

-- [分析] 考察order by 语句的升降序及先后次序

select c_id,avg(s_score)

from score

group by c_id

order by avg(s_score) desc,c_id asc;

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

select a.s_id, a.s_name, avg(b.s_score)

from student as a inner join score as b on a.s_id = b.s_id

group by b.a_id,a.s_name

having avg(b.s_score) >= 85;

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

-- [分析] 三个表联结,两个筛选条件:①c.cname = '数学' ②b.s_score < 60

select a.s_name, b.s_score

from student as a inner join score as b on a.s_id = b.s_id

inner join course as c on b.c_id = c.c_id and c.c_name = '数学'

where b.s_score < 60;

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

select a.s_id, b.c_id, b.c_name, a.s_score

from score as a inner join course as b on a.c_id = b.c_id;

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

select a.s_name,c.c_name,b.s_score

from student as a inner join score as b on a.s_id = b.s_id

inner join course as c on c.c_id = b.c_id

where b.s_score >= 70;

37、查询不及格的课程

select a.s_id,a.s_name,c.c_id,c.c_name,b.s_score

from student as a inner join score as b on a.s_id = b.s_id

inner join course as c on b.c_id = c.c_id

where b.s_score < 60;

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

select a.s_id,a.s_name

from student as a inner join score as b on a.s_id = b.s_id

where b.c_id = '01' and b.s_score >= 80;

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

select a.c_id,a.c_name,count(b.s_id)

from course as a inner join score as b on a.c_id = b.c_id

group by b.c_id;

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

select d.*,max(c.s_score)

from teacher as a inner join course as b on a.t_id = b.t_id

inner join score as c on c.c_id = b.c_id

inner join student as d on d.s_id = c.s_id

where a.t_name = '张三'

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (重要!!!!)

select distinct b.s_id,b.c_id,b.s_score

from score as a,score as b

where a.c_id != b.c_id and a.s_score = b.s_score

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

-- [分析]这道题不吹牛逼,我认为我的写法最牛逼

(select c_id,s_score from score where c_id = '01' order by s_score desc limit 2)

UNION

(select c_id,s_score from score where c_id = '02' order by s_score desc limit 2)

UNION

(select c_id,s_score from score where c_id = '03' order by s_score desc limit 2)

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

select c_id,count(s_id)

from score

group by c_id

having count(s_id) >= 5

order by count(s_id) desc,c_id asc;

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

select s_id,count(c_id)

from score

group by s_id

having count(c_id) >= 2;

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

select a.*

from student as a inner join score as b on a.s_id = b.s_id

group by s_id

having count(b.c_id) = (select count(c_id) from course);

46、查询各学生的年龄 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一

-- [分析] 这道题俺不会,学sql 的时候没学日期函数的使用,答案是查来的

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;

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

select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)

select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d'))

select WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))

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

select*from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1=WEEK(s_birth)

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

select*from student whereMONTH(DATE_FORMAT(NOW(),'%Y%m%d'))=MONTH(s_birth)

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

select*from student whereMONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1=MONTH(s_birth)

  • 2
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值