MySQL经典题目--初学者练手入门，包含解题答案及解题思路

数据表介绍

      --题目设计四张表，学生表，课程表，教师表，成绩表

--1.学生表

Student(SId,Sname,Sage,Ssex)

--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表

Course(CId,Cname,TId)

--CId 课程编号,Cname 课程名称,TId 教师编号

--3.教师表

Teacher(TId,Tname)

--TId 教师编号,Tname 教师姓名

--4.成绩表

SC(SId,CId,score)

--SId 学生编号,CId 课程编号,score 分数

      --载入数据

学生表 Student

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

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

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

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

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

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

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

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

insert into Student values('09' , '张三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2012-06-06' , '女');

insert into Student values('12' , '赵六' , '2013-06-13' , '女');

insert into Student values('13' , '孙七' , '2014-06-01' , '女');

科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

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

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

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

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));

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

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

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

成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

     -- 题目、解题思路及答案 

-- 1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
/*
 解题思路：将SC表分别通过课程号为01，02进行筛选后再进行两表联查，
         筛选出符合" 01 "课程比" 02 "课程成绩高的学生的学号，
         根据题目要求连接Student(学生表)，筛选出相关学生的姓名
         等信息。
 */
select s.*, sc1.score, sc2.score
from Student s
         join SC sc1 on s.SId = sc1.SId and sc1.CId = 01
         join SC sc2 on s.SId = sc2.SId and sc2.CId = 02
where sc1.score > sc2.score;

-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
/*
 解题思路：将SC表当作两张表进行查询，和第一题相似，根据题目需筛选出
          sc1表和sc2表的交集部分，选择内连接
 */
select sc2.SId, sc1.score, sc2.score, sc1.CId, sc2.CId
from SC sc1
         join SC sc2 on sc1.SId = sc2.SId
where sc1.CId = 01
  and sc2.CId = 02;

-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
/*
 解题思路：
       查询SC表可知查询结果应存在06号学生且不存在07号学生,思路同上
 */

select sc1.SId, sc1.CId, sc2.CId, sc1.score, sc2.score
from SC sc1
         join SC sc2 on sc1.SId = sc2.SId
where sc1.CId = 01
  and sc2.CId >= 2;


-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
/*
 解题思路：
       查询SC可知查询结果应存在07号学生而不存在06号学生，思路同上
 */
select sc1.SId, sc1.CId, sc2.CId, sc1.score, sc2.score
from SC sc1
         join SC sc2 on sc1.SId = sc2.SId
where sc2.CId = 02
  and sc1.CId <> '02';


-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
/*
 解题思路：
      使用聚合函数avg计算出每个学生的平均分并筛选出符合平均分>=60的学生
      根据mysql语句的执行顺序：
      from >join >where >聚合函数 >having >select >distinct >order by >limit
      选择填写筛选条件
 */
select s.Sname, AVG(score) avg_score
from Student s,
     SC sc
where s.SId = sc.SId
group by s.Sname
having avg_score >= 60;


-- 3.查询在 SC 表存在成绩的学生信息
/*
 解题思路：
     查询SC表可知01-07号学生符合条件
 */

select distinct s.*
from SC sc
         join Student s on sc.SId = s.SId;


-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
/*
 解题思路：
     SC表和Student表两表联查
     使用聚合函数count计算选课，sum计算总分
 */
select s.SId, s.Sname, count(sc.CId) class_count, sum(sc.score)
from Student s
         left join SC sc on s.SId = sc.SId
group by s.SId, s.Sname;


-- 4.1显示没选课的学生(显示为NULL)
/*
 解题思路：
     SC表和Student表两表联查，CID为null的学生即没选课
     使用Cid为null当作筛选条件即可
 */
select s.*, sc.CId
from Student s
         left join SC sc on s.SId = sc.SId
where sc.CId is null;


-- 4.2查有成绩的学生信息
/*
 解题思路：同上
 */
select s.*, sc.CId
from Student s
         left join SC sc on s.SId = sc.SId
where sc.CId is not null;


-- 5.查询「李」姓老师的数量
/*
 解题思路：
     李姓老师只有一位，使用聚合函数count计算教师的数量，
     使用模拟查询like‘__%’筛选姓李的老师
 */
select count(Tname)
from Teacher
where Tname like '李%';


-- 6.查询学过「张三」老师授课的同学的信息
/*
 解题思路：
     使用自查询，在Teacher表中查询出张三的工号，
     再根据工号在Course表中查询出张三所授课程的课程号，
     根据课程号在SC表中可以查询出选择张三课程的学生学号，
     思路简单，就是联查的表多
 */
select s.*
from (select sc.SId id
      from Teacher t
               join Course c on t.TId = c.TId
               join SC sc on sc.CId = c.CId
      where t.Tname = '张三') t1
         join Student s
where s.SId = t1.id;


-- 7.查询没有学全所有课程的同学的信息
/*
 解题思路：
     使用聚合函数count，课程数少于3即没有学全课程
 */
select s.Sname, count(CId) c_count
from Student s
         join SC sc on s.SId = sc.SId
group by s.Sname
having c_count < 3;


-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
/*
 解题思路：可知学号01的学生选择了全部3门课程
 */
-- 学号01的学生所学的课程
select sc.CId
from SC sc
where sc.SId = 01;

/*
 解题思路：
     只要有一门课程相同所以用in(....)，Student表和SC表两表联查，
     最后筛选出课程号有一门与01学生相同且不是01学生的学生
 */
-- distinct(colum1, column2...) 返回指定的多个列中所有不重复的组合
select distinct s.*
from Student s
         join SC sc on s.SId = sc.SId
where sc.CId in (select sc.CId from SC sc where sc.SId = 01) and sc.SId <> '01';


-- 9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
/*
 解题思路：
     本题一共三门课程，基本思路同第八题，查询出和01学生所学课程
     基本一致的学生且所选课程数要等于3才能保证和01学生所选课程一致
 */
select *
from Student s
where s.SId in (select sid
                from SC sc
                where sc.CId in (select distinct cid from SC where sid = '01')
                  and sid <> '01'
                group by sid
                having count(cid) = 3);


-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
/*
 解题思路：
      本题的查询结果是06号学生以及其他没有选择课程的学生，
      筛选出没有选择张三的学生的学号，在联合Student进行查询，
      只要不在张三所教授的学生的学号列表里即可
 */

select s.* from Student s where s.SId not in(select sc.SId id
      from Teacher t
               join Course c on t.TId = c.TId
               join SC sc on sc.CId = c.CId
      where t.Tname = '张三');


-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
/*
 解题思路：
      查询结果是04，06两个学生，可以先分组查询出哪些学生的不及格课程数大于等于2
 */
select sc.SId id, count(sc.SId) score_under_60
from SC sc
where sc.score < 60
group by sc.SId
having score_under_60 >= 2;
/*
 解题思路：
      再根据题目加上需要筛查出来其他条件
 */
select s.SId, s.Sname, avg(sc.score) avg_score
from (select sc.SId id, count(sc.SId) score_under_60
      from SC sc
      where sc.score < 60
      group by sc.SId
      having score_under_60 >= 2) t1
         join Student s on s.SId = t1.id
         join SC sc on s.SId = sc.SId
group by s.SId, s.Sname;


-- 12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
/*
 解题思路：
     加一个order by进行排序
 */
-- mysql默认升序
select s.*, sc.CId, sc.score
from Student s
         join SC sc on s.SId = sc.SId
where sc.CId = 01
  and score < 60
order by sc.score desc;


-- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
/*
 解题思路：
     使用了窗口函数，直接添加一列
 */
-- 窗口函数over（）当中如果不写 oder by就统计组内的所有数据，如果写了oder by 就统计第一行到当前行的数据
select s.*, sc.CId ,sc.score, avg(sc.score) over (partition by sc.SId) avg_score
from Student s
         join SC sc on s.SId = sc.SId;


-- 14.查询各科成绩最高分、最低分和平均分
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

-- 课程 ID，课程 name，最高分，最低分，平均分
/*
 解题思路：
      及格率，中等率，优良率，优秀率略
 */
select distinct c.CId,
                c.Cname,
                min(score) over (partition by c.CId) min_score,
                max(score) over (partition by c.CId) max_score,
                avg(score) over (partition by c.CId) avg_score
from SC sc
         join Course c on sc.CId = c.CId;


-- 15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
/*
 解题思路：
     使用rank（）可以在排名并列的时候保持排名的增加
 */
select sc.SId, s.Sname, c.Cname, rank() over (partition by sc.CId order by score) 'rank'
from SC sc
         join Student s on sc.SId = s.SId
         join Course c on sc.CId = c.CId;


-- 16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
/*
 解题思路：
      思路同上，只是计算的分数不同且采用密集排名
 */
select s.Sname, dense_rank() over (order by t1.total_score) 'rank'
from (select SId, sum(score) total_score from SC group by SId) t1
         join Student s on s.SId = t1.SId;


-- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，
/*
 解题思路：
      加入了if判断语句和sum()函数搭配使用
 */
select sc.CId,
       c.Cname,
       sum(if(score between 85 and 100, 1, 0)) excellent,
       sum(if(score between 70 and 85, 1, 0))  normal
from Course c
         join SC sc on c.Cid = sc.CId
group by c.Cname, sc.CId;