MySQL经典50题（二刷）

有智青年i
已于 2023-10-19 21:20:39 修改
阅读量34
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2023-10-19 21:20:02 首次发布
本文链接：https://blog.csdn.net/m0_64622390/article/details/133935198
版权
MySQL 专栏收录该内容
15 篇文章 1 订阅
订阅专栏
------------------------------老样子，删除答案自己先做--------------------------------------
create database db_2;
use db_2;

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);
-- 1 查询" 01 "课程比" 02"课程成绩高的学生的信息及课程分数
-- 自查询
select *
from Score a
         join Score b on a.s_id = b.s_id
         join student c on a.s_id = c.s_id
where a.c_id = '01'
  and b.c_id = '02'
  and a.s_score > b.s_score;
-- 2 查询同时存在" 01 "课程和" 02 "课程的学生信息
select *
from score a
         join student b on a.s_id = b.s_id
where c_id in ('01', '02');

-- 3 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select *
from score
where s_id not in (select s_id from score where c_id = '02')
  and c_id = '01';

-- 4 查询不存在" 01 "课程但存在" 02 "课程的情况
select *
from score
where Score.s_id not in (select s_id from score where score.c_id = '01')
  and score.c_id = '02';

-- 5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.s_id, s_name, avg(s_score)
from score a
         join student b on a.s_id = b.s_id
group by a.s_id
having avg(s_score) >= 60;
-- 6. 查询在 Score 表存在成绩的学生信息
select *
from student a
         left join score b on a.s_id = b.s_id
where s_score is not null;

-- 7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select a.s_id, s_name, count(c_id), sum(s_score)
from score a
         right join student b on a.s_id = b.s_id
group by s_id, s_name

-- 8 查没有成绩的学生信息
select *
from student a
         left join Score S on a.s_id = S.s_id
where s_score is null;
-- 9. 查询「李」姓学生的数量
select *
from student
where s_name like '李%';
-- 10. 查询学过「张三」老师授课的同学的信息
select a.*
from student a
         join Score S on a.s_id = S.s_id
         join Course C on S.c_id = C.c_id
         join teacher t on C.t_id = t.t_id
where t_name = '张三';
-- 11. 查询没有学全所有课程的同学的信息（没学过的也算）
# with t1 as
# (select a.s_id,count(c_id) cnt
# from student a
# left join Score S on a.s_id = S.s_id
# # join course c on S.c_id = c.c_id
# group by a.s_id
# having cnt != (select count(c_id) from course))
# select *
# from student b
# where s_id is exists(select * from t1 join b t1.s_id = b.s_id )

SELECT a.*
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
HAVING COUNT(*) < (select count(c_id) from course);
-- 12 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
# with t1 as ( select c_id from score where s_id = '01' )
select distinct a.*
from student a
         left join Score S on a.s_id = S.s_id
where c_id in (select c_id from score where s_id = '01');

-- 13 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select a.*
from student a
         join Score S on a.s_id = S.s_id
group by a.s_id
having count(c_id) = (select count(c_id) from score where s_id = '01');

select distinct a.*
from student a
         left join Score S on a.s_id = S.s_id
where c_id in (select c_id from score where s_id = '01')
group by a.s_id
having count(c_id) = (select count(c_id) from score where s_id = '01');

-- 14 查询没学过"张三"老师讲授的任一门课程的学生姓名
select s_name
from student a
         join Score S on a.s_id = S.s_id
         join Course C on S.c_id = C.c_id
         join Teacher T on C.t_id = T.t_id
where C.c_id not in (select c_id
                     from course a
                              join Teacher T2 on a.t_id = T2.t_id
                     where T2.t_name = '张三');

SELECT *
FROM Student
WHERE s_id NOT IN (
    SELECT b.s_id
    FROM Score a
             JOIN Student b ON a.s_id = b.s_id
    WHERE c_id = (
        SELECT c_id
        FROM Teacher a
                 JOIN Course b ON a.t_id = b.t_id
        WHERE t_name = '张三'
    ));

-- 15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select b.*
from score a
         join student b on a.s_id = b.s_id
where s_score < 60
group by a.s_id
having count(s_score) >= 2;

-- 16 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select student.*
from student
         join Score S on Student.s_id = S.s_id
where c_id = '01'
  and s_score < 60
order by s_score desc;
-- 17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.*,
       GROUP_CONCAT(s_score),
       AVG(s_score)
FROM Student a
         left JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
ORDER BY AVG(s_score) DESC;

-- 18.查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT a.c_id,
       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 a
         join Course b on a.c_id = b.c_id
group by c_id;

-- 19 按各科成绩进行排序，并各科成绩从高到底显示排名， Score 重复时保留名次空缺 RANK()
select *,
       rank() over (partition by s_id order by s_score desc)
from score;

-- 20各科成绩进行排序，并显示排名， Score 重复时合并名次
select *,
       rank() over (partition by s_id order by s_score)
from score;
-- 21查询学生的总成绩，总成绩并进行排名，总分重复时保留名次空缺
SELECT s_id,
       sum_score,
       rank() over (ORDER BY sum_score DESC) AS '排名'
FROM (
         SELECT s_id,
                SUM(s_score) AS sum_score
         FROM Score
         GROUP BY s_id) a;
-- 22统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
-- 同18题
select a.c_id,
       c_name,
       sum(case when s_score <= 100 and s_score > 85 then 1 else 0 end) as '(100-85)',
       sum(case when s_score <= 85 and s_score > 70 then 1 else 0 end)  as '(85-70)',
       sum(case when s_score <= 70 and s_score > 60 then 1 else 0 end)  as '(70-60)',
       sum(case when s_score <= 60 then 1 else 0 end)                   as '(60-0)',
       concat(round((sum(case when s_score <= 100 and s_score > 85 then 1 else 0 end) / count(s_id)), 2) * 100,
              '%')                                                      as '(100-85)所占比分比',
       concat(round((sum(case when s_score <= 85 and s_score > 70 then 1 else 0 end) / count(s_id)), 2) * 100,
              '%')                                                      as '(85-70)所占比分比',
       concat(round((sum(case when s_score <= 70 and s_score > 60 then 1 else 0 end) / count(s_id)), 2) * 100,
              '%')                                                      as '(70-60)所占比分比',
       concat(round((sum(case when s_score <= 60 then 1 else 0 end) / count(s_id)), 2) * 100,
              '%')                                                      as '(60-0)所占比分比'
from score a
         join course c on a.c_id = c.c_id
group by a.c_id, c_name

-- 易忘记关键词：
-- round（数字，保留几位小数）
-- concat（内容，需要拼接的符号）

-- 23查询各科成绩前三名的记录
select a.*
from Score a
where (select count(1) from Score b where b.c_id = a.c_id and b.s_score > a.s_score) < 3
order by c_id desc, s_score desc;

# order by s_score desc
# limit 3
-- 24查询每门课程被选修的学生数
select a.c_id, count(1) as '各科总人数'
from score a
         join course c on a.c_id = c.c_id
         join student s on a.s_id = s.s_id
group by a.c_id;
-- 25 查询出只选修两门课程的学生学号和姓名
select a.s_id, s_name
from score a
         join student s on a.s_id = s.s_id
group by s_id, s_name
having count(a.c_id) = 2;
-- 26 查询男生、女生人数
select s_sex, count(1) as '人数'
from student
group by s_sex;
-- 27 查询名字中含有「风」字的学生信息
select *
from student
where s_name like '%风%';
-- 28 查询同名同性学生名单，并统计同名人数
select a.s_name,
       a.s_sex,
       count(*)
from Student a
         join Student b on a.s_name = b.s_name and a.s_sex = b.s_sex and a.s_id != b.s_id
group by a.s_sex, a.s_name;

-- 29查询 1990 年出生的学生名单
select *
from student
where year(s_birth) = '1990';
-- 30查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select avg(s_score)
from score
group by c_id
order by avg(s_score) desc, c_id asc;

-- 31查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.s_id, s_name, avg(s_score) as ag_s
from score a
         join student s on a.s_id = s.s_id
group by s_id, s_name
having ag_s >= 85;
-- 32查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select s_name, s_score
from score a
         join student s on a.s_id = s.s_id
         join course c on a.c_id = c.c_id
where c.c_name = '数学'
  and s_score < 60;
-- 33查询所有学生的课程名称及分数情况（存在学生没成绩，没选课的情况）
select s_name, c_name, s_score
from student a
         left join score s on a.s_id = s.s_id
         left join Course C on s.c_id = C.c_id;

-- 34查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
/*
 思路查询有70分以下的人的s_id
 not in
 */
select s_name, c_name, s_score
from score a
         join course c on a.c_id = c.c_id
         join student s on a.s_id = s.s_id
where a.s_id not in (select s_id
                     from score
                     where s_score <= 70);

#错
# select s_name, c_name, s_score
# from Student a
#          join (SELECT * from Score where s_score > 70) b on a.s_id = b.s_id
#          join Course c on b.c_id = c.c_id

-- 35 查询不及格的课程
select distinct c_name
from score
         join Course C on Score.c_id = C.c_id
where s_score < 60;
-- 36 查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
select a.s_id, s_name
from student a
         join Score S on a.s_id = S.s_id
where c_id = '01'
  and s_score > 60;
-- 37 求每门课程的学生人数
select c_id, count(1)
from score
group by c_id;

-- 38成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 ROW_NUMBER()
select S.*, s_score
from score
         join Course C on Score.c_id = C.c_id
         join Teacher T on C.t_id = T.t_id
         join Student S on Score.s_id = S.s_id
where t_name = '张三'
order by s_score desc
limit 1;

select s.*,
       row_number() over (order by s_score desc ) as rn
from score a
         join course c on a.c_id = c.c_id
         join student s on a.s_id = s.s_id
         join teacher t on c.t_id = t.t_id
where t_name = '张三'
limit 1;

-- 39成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*,
       rank() over (order by s_score desc ) as rn
from score a
         join course c on a.c_id = c.c_id
         join student s on a.s_id = s.s_id
         join teacher t on c.t_id = t.t_id
where t_name = '张三'
limit 1;

select *
from (
         SELECT a.s_id,
                s_name,
                s_score,
                RANK() over (order by s_score desc) as ranking
         FROM Student a
                  join Score b on a.s_id = b.s_id
                  join Course c on b.c_id = c.c_id
                  join Teacher d on d.t_id = d.t_id
         where t_name = '张三'
     ) as st
where ranking = 1;

-- 40查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.s_id, a.c_id, a.s_score
from score a
         join score s on a.s_id = s.s_id and a.c_id != s.c_id and a.s_score = s.s_score;
-- 41查询每门课程成绩最好的前两名同学 RANK
select *
from (select s.*, rank() over (partition by c_id order by s_score desc ) as rn
      from score a
               join student s on a.s_id = s.s_id
     ) as t1
where rn in (1, 2);

-- 42统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select C.c_id, c_name, count(1) as cnt
from score
         join Course C on Score.c_id = C.c_id
group by C.c_id, c_name
having cnt >= 5;
-- 43检索至少选修两门课程的学生学号
select s_id
from score
group by s_id
having count(c_id) >= 2;
-- 44查询选修了全部课程的学生信息
select s.*
from score
         join Course C on Score.c_id = C.c_id
         join Student S on Score.s_id = S.s_id
group by S.s_id
having count(C.c_id) = (select count(1) from course);
-- 45 查询各学生的年龄，只按年份来算
select *, TIMESTAMPDIFF(YEAR, s_birth, now()) AS age
from student;
-- 46 按照出生日期来算，当前月日 < 出生年月的月日则年龄减一;

select *,
       year(now()) - YEAR(s_birth) age
from Student;
-- 47 查询本周过生日的学生
select *
from student
where week(s_birth) = week(now());

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

SELECT * ,
			WEEK(s_birth),
			WEEK(NOW())
from Student
where WEEK(s_birth) =( WEEK(NOW()) + 1)
-- 49 查询本月过生日的学生

SELECT *,
			MONTH(s_birth),
			MONTH(NOW())
FROM Student
where MONTH(s_birth) = MONTH(NOW());
-- 50查询下月过生日的学生

SELECT *,
			MONTH(s_birth),
			MONTH(NOW())
FROM Student
where  MONTH(s_birth) = (MONTH(NOW())+1)