048、SQL五十题（230414）-CSDN博客

本文链接：https://blog.csdn.net/Yora123/article/details/130128398

本文全部记录自己打卡写SQL语句

文章目录

一、初始环境搭建
- 1.建立表
- 2.插入语句
二、开始写SQL语句
总结

一、初始环境搭建

根据视频内容，地址链接：SQL面试50题，进行环境搭建：

1.建立表

建表SQL语句如下：

-- 建立学生表
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`)
);

2.插入语句

初始的一些数据的SQL语句如下：

-- 插入学生表测试数据
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);

-- 查询学生表所有数据
SELECT * FROM student

二、开始写SQL语句

每次写SQL之前，现需要弄清楚表之间的关系；
需要知道自己的思考逻辑和思路；

1.partⅠ

这小节，我写的是前十条语句：

第一条SQL：

 -- 1.查询课程编号为’01‘的课程比’02‘的课程成绩高的所有学生的学号；
select a.*, b.s_score 
from (select distinct s_id ,s_score from score where c_id = '01') a 
inner join (select distinct s_id ,s_score from score where c_id = '02') b 
on a.s_id = b.s_id where a.s_score > b.s_score;

-- 查询学生学号和课程号为’01‘成绩
select distinct s_id ,s_score from score where c_id = '01';
-- 查询学生学号和课程号为’01‘成绩
select distinct s_id ,s_score from score where c_id = '02';

第二条SQL：

-- 2.查询平均成绩大于60分的学生的学号和平均成绩；
select s_id,avg(s_score) from score group by s_id having avg(s_score) > 60;

第三条SQL：

-- 3.查询所有学生的学号、姓名、选课数、总成绩
select student.s_id,s_name,a.count,a.sum from student 
left join (select s_id,count(distinct c_id) 'count',sum(s_score) 'sum' from score group by s_id) a 
on student.s_id = a.s_id;

-- 查询所有学生的学号、选课数、总成绩
select s_id,count(distinct c_id) 'count',sum(s_score) 'sum' from score group by s_id;

第四条SQL：

-- 4.查询姓张的老师的个数
select count(*) from teacher where t_name like '张%';

第五条SQL：

-- 5.查询没学过张三老师课的学生的学号和姓名
select s_id, s_name from student where s_id
not in (select s_id from score inner join course c on score.c_id = c.c_id inner join teacher t on c.t_id = t.t_id where t_name = '张三');

-- 张三老师的id
select t_id from teacher where t_name = '张三';
-- 张三老师上的课程id
select c_id from course inner join teacher t on course.t_id = t.t_id where t_name = '张三';
-- 上过张三老师课的学生的学号
select s_id,c.c_id from score inner join course c on score.c_id = c.c_id inner join teacher t on c.t_id = t.t_id where t_name = '张三';

第六条SQL：

-- 6.查询学过张三老师所教的所有课的同学的学号和姓名；
select student.s_id, s_name from student
inner join (select s_id from score inner join course c on score.c_id = c.c_id inner join teacher t on c.t_id = t.t_id where t_name = '张三') a
on student.s_id = a.s_id;

-- 张三老师教的所有课程
select c_id from course inner join teacher t on course.t_id = t.t_id where t_name ='张三';
-- 上过张三老师课程的所有同学的学号
select s_id from score inner join course c on score.c_id = c.c_id inner join teacher t on c.t_id = t.t_id where t_name = '张三';

第七条SQL：

-- 7.查询学过编号为01的课程并且也学过编号为02的课程的学生的学号和姓名；
select student.s_id, s_name from student 
inner join (select score.s_id from score inner join (select s_id from score  where c_id ='02') a on score.s_id = a.s_id  where c_id ='01') b 
on student.s_id = b.s_id;

-- 学过编号为01的课程也学过编号为02课程的学生
select score.s_id from score inner join (select s_id from score  where c_id ='02') a on score.s_id = a.s_id  where c_id ='01' ;
select * from score  where c_id ='02';

第八条SQL：

-- 8.查询课程编号为02的总成绩
select sum(s_score) from score where c_id = '02';

第九条SQL：

-- 9.查询所有课程成绩小于60分的学生的学号和姓名
select student.s_id, s_name from student
inner join (select s_id, count(distinct c_id) from score group by s_id) a on student.s_id = a.s_id
inner join (select s_id,count(distinct c_id) from score where s_score < 60 group by s_id) b on student.s_id = b.s_id;

-- 查询每个学生学的课程数量
select s_id, count(distinct c_id) from score group by s_id;
-- 查询这个学生小于60分学的课程数量
select s_id,count(distinct c_id) from score where s_score < 60 group by s_id;

第十条SQL：

-- 10.查询没有学全所有课的学生的学号和姓名；
select * from student where s_id 
not in (select s_id from score group by s_id having count(distinct c_id) = (select count(distinct c_id) 'count' from course));

-- 课程总数
select count(distinct c_id) 'count' from course;
-- 学了全部课程数量的学生学号
select s_id, count(distinct c_id) 'number' from score group by s_id having number = (select count(distinct c_id) 'count' from course);

2.partⅡ

这小节，写的是11-20的SQL语句，12是重点；

SQL-11：

-- 11.查询至少有一门课与学号为01的学生所学课程相同的学生的学号和姓名
select distinct s.s_id , s_name from student s 
inner join score a on s.s_id = a.s_id 
inner join score b on a.c_id = b.c_id where b.s_id = '01' and a.s_id != '01';

-- 学号为01的学生所学的课程的id
select distinct c_id from score where s_id = '01';
select distinct a.s_id from score a inner join score b on a.c_id = b.c_id where b.s_id = '01' and a.s_id != '01';

SQL-12：

重点

-- 12.查询和01号同学所学课程完全相同的其它同学的学号
select a.s_id from
(select s_id, count(distinct c_id) 'count' from score group by s_id having count = (select count(distinct c_id) 'number' from score where s_id = '01') and s_id != '01') a
where a.s_id not in (select s_id from score where c_id not in (select c_id from score where s_id = '01'));

-- 01号同学所学的课程id
select c_id from score where s_id = '01';
-- 查找上过和01同学不同的课程的学生id
select s_id from score where c_id not in (select c_id from score where s_id = '01');
-- 和01号同学所学课程数量完全相同的其它同学的学号
select s_id, count(distinct c_id) 'count' from score group by s_id having count = (select count(distinct c_id) 'number' from score where s_id = '01') and s_id != '01';

sql-13有重复的，sql-14没有；

SQL-15：

重点：

-- 15.查询两门及其以上不及格课程的同学的学号姓名及其平均成绩;
select student.s_id, s_name, avg from student
	inner join (select s_id, count(distinct c_id) 'number', avg(s_score) 'avg' from scor where s_score < 60 group by s_id  having number > 1) a
		on student.s_id = a.s_id;

-- 查询两门及其以上不及格课程的人的学号和平均成绩
select s_id, count(distinct c_id) 'number', avg(s_score) 'avg' from score where s_score < 60 group by s_id  having number > 1 ;

SQL-16：

-- 16.查询01课程分数小于60，按照分数降序排列的学生信息；
select student.*, s_score from student
    inner join score s on student.s_id = s.s_id
                          where c_id ='01' and s_score < 60 order by s_score desc ;

-- 查询01课程的学生
select * from score where c_id = '01' and s_score < 60 order by s_score desc ;

SQL-17：

重点

-- 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id,max(case when b.c_id = '01' then b.s_score else NULL end) '语文',
       max(case when b.c_id = '02' then b.s_score else NULL end) '数学',
       max(case when b.c_id = '03' then b.s_score else NULL end) '英语',
       avg(a.s_score) 'avg'
from score a
    inner join score b on a.s_id = b.s_id
        group by a.s_id order by avg desc ;

-- 所有学生的平均成绩
select s_id, avg(s_score) 'avg' from score group by s_id order by avg desc ;

SQL-18：

重点

-- 18.查询各科成绩最高分，最低分，平均分，及格率，中等率，优良率，优秀率；
select score.c_id '课程id' , c.c_name '科目名称',
       max(s_score) '最高分' ,
       min(s_score) '最低分' ,
       avg(s_score) '平均分' ,
       sum(case when s_score >= 60 then 1 else 0 end ) / count(s_id) '及格率' ,
       sum(case when s_score >= 70 and s_score < 80 then 1 else 0 end ) / count(s_id) '中等率' ,
       sum(case when s_score >= 80 and s_score < 90 then 1 else 0 end ) / count(s_id) '优良率' ,
       sum(case when s_score >= 90 then 1 else 0 end ) / count(s_id) '优秀率'
from score left join course c on score.c_id = c.c_id
group by score.c_id ;

-- 难点是怎么求及格率、中等率、优良率、优秀率
-- 采用case when ***** then **** else *** end

SQL-19：

重点

-- 19.按各科成绩进行排序，并且显示排名;
-- 最后显示的字段列为：学生id，课程id，课程成绩，排名；
-- 先分组再去排序
select s.* , dense_rank() over (partition by c_id order by s_score desc ) '排名' from score s ;-- 1,2,2,3,4
select s.* , row_number() over (partition by c_id order by s_score desc ) '排名' from score s ;--  1,2,3,4,5
select s.* , rank() over (partition by c_id order by s_score desc ) '排名' from score s ;-- 1,2,2,4,5

SQL-20:

-- 20.查询学生的总成绩并且进行排名；
select distinct s_id, sum(s_score) 'sum', rank() over (order by sum(s_score) desc ) from score group by s_id;

3.partⅢ

本章节记录的第21到第30题：

SQL-21：

-- 21.查询不同老师所教不同课程的平均分从高到低显示；
select a.*, avg(s_score) 'avg' from score
    inner join (select course.c_id, course.c_name, teacher.t_name from course inner join teacher on course.t_id = teacher.t_id) a
        on score.c_id = a.c_id
            group by score.c_id order by avg desc ;

-- 查询不同的老师教的不同课程
select course.c_id, course.c_name, teacher.t_id, teacher.t_name from course inner join teacher on course.t_id = teacher.t_id;

SQL-22：

重点

-- 22.查询所有课程的成绩第二名到第三名的学生信息以及该课程成绩；
select student.s_id,s_name,c_id,a.s_score,a.number from student 
    inner join (select s_id,c_id, s_score,row_number() over (partition by c_id order by s_score desc ) 'number' from score) a 
        on student.s_id = a.s_id where number > 1 and number < 4;

select s_id,row_number() over (partition by c_id order by s_score desc ) 'rank' from score;

SQL-23：

重点

-- 23.使用分段来统计各科成绩，分别统计各分数段人数；
-- 统计[100,85],(85,70],(70,60],(60,0]有多少人

select score.c_id, c.c_name,
       sum(case when s_score >= 85 then 1 else 0 end ) '[100,85]' ,
       sum(case when s_score >= 70 and s_score < 85 then 1 else 0 end ) '(85,70]' ,
       sum(case when s_score >= 60 and s_score < 70 then 1 else 0 end ) '(70,60]' ,
       count(case when s_score < 60 then 1 else null end ) '(60,0]'
from score
    inner join course c on score.c_id = c.c_id
        group by score.c_id;

SQL-24：

-- 24.查询学生平均成绩及其名次
select student.s_id,
       s_name,
       avg(score.s_score),
       row_number() over (order by avg(s_score) desc )
from score
         inner join student on student.s_id = score.s_id
group by s_id;

SQL-25和SQL-22相同
SQL-26：

-- 26.查询每门课程被选修的学生数；
select score.c_id,c_name,count(distinct s_id) from score
    inner join course c on score.c_id = c.c_id
        group by score.c_id;

SQL-27：

-- 27.查询出只有两门课程的全部学生的学号和姓名；
select score.s_id,s_name from score
    inner join student s on score.s_id = s.s_id
        group by s_id having count(distinct c_id) = 2;

SQL-28：

-- 28.查询男生、女生人数；
select s_sex, count(s_id) from student group by s_sex;

SQL-29：

-- 29.查询名字中含有风字的学生信息；
select * from student where s_name like '%风%';

SQL-30没有这个题目；

4.partⅣ

本章节部分是关于第31题到第40题

SQL-31：

重点

-- 31.查询1990年出生的学生名单
select * from student where year(s_birth) = 1990;

-- 日期的用法如下：
select month('1990-09-01');
select month('1990/09/01');
select month('19900901');

select * from student where month(s_birth) = 01;
select * from student where day(s_birth) = 01;

SQL-32：

-- 32.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩;
select s.s_id, s_name, avg(s_score) from score
    inner join student s on score.s_id = s.s_id
        group by score.s_id having avg(s_score) >= 85;

SQL-33：

-- 33.查询每门课程的平均成绩，结果按照平均成绩升序排序，平均成绩相同时，按照课程号降序排列；
select score.c_id, c_name,avg(s_score) 'avg' from score
    inner join course c on score.c_id = c.c_id
    group by c_id order by avg asc ,c_id desc ;

SQL-34：

-- 34.查询课程名称为数学，且分数低于60的学生姓名和分数；
select score.s_id,s_name,s_score,c_name from score
    inner join course c on score.c_id = c.c_id
         inner join student s on score.s_id = s.s_id
            where c_name = '数学' and s_score < 60;

SQL-35：

重点

-- 35.查询所有学生的课程及分数情况；
select score.s_id, s_name,
        max(case when c.c_name = '语文' then score.s_score else null end ) '语文',
       max(case when c.c_name = '数学' then score.s_score else null end ) '数学',
      max(case when c.c_name = '英语' then score.s_score else null end )'英语'
       from score inner join student s on score.s_id = s.s_id
        inner join course c on score.c_id = c.c_id group by score.s_id;

SQL-36：

-- 36.查询课程成绩在70分以上课程姓名，分数，学生姓名
select s_name,c_name,s_score from score
    inner join course c on score.c_id = c.c_id
        inner join student s on score.s_id = s.s_id
            where s_score > 70 order by score.c_id;

SQL-37：

-- 37.查询不及格的课程并按照课程号从大到小排列；
select c.c_id, c_name, s_name, s_score from score
    inner join course c on score.c_id = c.c_id
        inner join student s on score.s_id = s.s_id
            where s_score < 60 order by c_id;

SQL-38：

-- 38.查询课程编号为03并且课程成绩在80分以上的学生的学号和姓名；
select score.s_id,s_name from score
    inner join student s on score.s_id = s.s_id
        where c_id = '03' and s_score > 80;

SQL-39：

-- 39.求每门课程的学生人数
select c_name,count(distinct s_id) from score
    inner join course c on score.c_id = c.c_id
        group by score.c_id;

SQL-40：

-- 40.查询选修张三老师所教授课程的学生中成绩最高的学生姓名及其成绩
select s_name,c_name, max(s_score) from student
    inner join score s on student.s_id = s.s_id
        inner join course c on s.c_id = c.c_id
            inner join teacher t on c.t_id = t.t_id
                where t_name ='张三'

5.partⅤ

本章节为第41题到第50题

SQL-41：

-- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩；
select b.s_id, s_score from (select * from (select s_id,s_score from score group by s_id,s_score) a
         group by s_id having count(s_id) = 1) b
    inner join (select s_id,count(distinct c_id) from score group by s_id having count(distinct c_id) > 1) c on b.s_id = c.s_id;

-- 根据学号和课程成绩分组
select * from (select s_id,s_score from score group by s_id,s_score) a
         group by s_id having count(s_id) = 1 ;
-- 选择选了不止一门课的学生学号
select s_id,count(distinct c_id) from score group by s_id having count(distinct c_id) > 1;

SQL-42已经有了；
SQL-43：

-- 43.统计每门课程的学生选修人数
select c.c_id,c_name,count(distinct s_id) from score
    inner join course c on score.c_id = c.c_id
         group by score.c_id;

SQL-44：

-- 44.检索至少选修两门课程的学生学号;
select s_id,count(distinct c_id) from score group by s_id having count(distinct c_id) >= 2;

SQL-45：

-- 45.查询选修了全部课程的学生信息
select score.s_id, s_name from score
    inner join student s on score.s_id = s.s_id
            group by s_id having count(distinct c_id) = (select count(distinct c_id) from course);

-- 全部课程数
select count(distinct c_id) from course;

SQL-46：

重点

-- 46.查询各学生的年龄；
select s_id,s_name,floor(datediff(now(),s_birth) / 365) from student;

SQL-47：

-- 47.查询没学过张三老师讲授的任何一门课程的学生姓名
select * from student where s_id 
    not in (select distinct score.s_id from score inner join course c on score.c_id = c.c_id inner join teacher t on c.t_id = t.t_id where t_name = '张三');

SQL-48：

重点
在这里插入图片描述

-- 48.查询下周过生日的同学；
select * from student where week(s_birth,1) = week(now(),1)+1;

SQL-49：

-- 49.查询本月过生日的人；
select * from student where month(s_birth) = month(now());

SQL-50：

-- 50.查询下个月过生日的人；
select * from student where month(s_birth) = month(now()) +1;

总结

先想再去做，think before take actions：

关于一些函数的使用，日期函数；
case when的使用；
子查询和内连接和外连接的使用；