############################################### DDL #################################################################
create table if not exists student
(
s_id varchar(20) not null
primary key,
s_name varchar(20) default '' not null,
s_birth varchar(20) default '' not null,
s_sex varchar(10) default '' not null
);
create table if not exists teacher
(
t_id varchar(20) not null
primary key,
t_name varchar(20) default '' not null
);
create table if not exists course
(
c_id varchar(20) not null
primary key,
c_name varchar(20) default '' not null,
t_id varchar(20) not null
);
create table if not exists score
(
s_id varchar(20) not null,
c_id varchar(20) not null,
s_score int(3) null,
primary key (s_id, c_id)
);
############################################### DML #################################################################
inSERT inTO student (s_id, s_name, s_birth, s_sex)
VALUES ('01', '赵雷', '1990-01-01', '男');
inSERT inTO student (s_id, s_name, s_birth, s_sex)
VALUES ('02', '钱电', '1990-12-21', '男');
inSERT inTO student (s_id, s_name, s_birth, s_sex)
VALUES ('03', '孙风', '1990-05-20', '男');
inSERT inTO student (s_id, s_name, s_birth, s_sex)
VALUES ('04', '李云', '1990-08-06', '男');
inSERT inTO student (s_id, s_name, s_birth, s_sex)
VALUES ('05', '周梅', '1991-12-01', '女');
inSERT inTO student (s_id, s_name, s_birth, s_sex)
VALUES ('06', '吴兰', '1992-03-01', '女');
inSERT inTO student (s_id, s_name, s_birth, s_sex)
VALUES ('07', '郑竹', '1989-07-01', '女');
inSERT inTO student (s_id, s_name, s_birth, s_sex)
VALUES ('08', '王菊', '1990-01-20', '女');
inSERT inTO teacher (t_id, t_name)
VALUES ('01', '张三');
inSERT inTO teacher (t_id, t_name)
VALUES ('02', '李四');
inSERT inTO teacher (t_id, t_name)
VALUES ('03', '王五');
inSERT inTO course (c_id, c_name, t_id)
VALUES ('01', '语文', '02');
inSERT inTO course (c_id, c_name, t_id)
VALUES ('02', '数学', '01');
inSERT inTO course (c_id, c_name, t_id)
VALUES ('03', '英语', '03');
inSERT inTO score (s_id, c_id, s_score)
VALUES ('01', '01', 80);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('01', '02', 90);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('01', '03', 99);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('02', '01', 70);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('02', '02', 60);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('02', '03', 80);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('03', '01', 80);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('03', '02', 80);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('03', '03', 80);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('04', '01', 50);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('04', '02', 30);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('04', '03', 20);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('05', '01', 76);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('05', '02', 87);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('06', '01', 31);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('06', '03', 34);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('07', '02', 89);
inSERT inTO score (s_id, c_id, s_score)
VALUES ('07', '03', 98);
#####################################################################################################################
# 01 查询“01”课程比“02”课程成绩高的学生的信息及课程分数
select s.*, a.s_score as 01_score, b.s_score as 02_score
from student s
left join score a on a.s_id = s.s_id and a.c_id = '01'
left join score b on b.s_id = s.s_id and b.c_id = '02'
where a.s_score > b.s_score;
# 02 查询“01”课程比“02”课程成绩低的学生的信息及课程分数(题目 1 是成绩高)
select s.*, a.s_score as 01_score, b.s_score as 02_score
from student s
left join score a on a.s_id = s.s_id and a.c_id = '01'
left join score b on b.s_id = s.s_id and b.c_id = '02'
where a.s_score < b.s_score;
# 03 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select score.s_id, student.s_name, round(avg(s_score), 2) as avg_score
from score
right join student on score.s_id = student.s_id
group by student.s_id
having round(avg(s_score), 2) >= 60;
# 04 查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
select score.s_id, student.s_name, round(avg(s_score), 2) as avg_score
from score
right join student on score.s_id = student.s_id
group by student.s_id
having round(avg(s_score), 2) < 60;
# 05 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select student.s_id,
student.s_name,
count(distinct score.c_id) as sum_course,
SUM(score.s_score) as sum_score
from student
left join score on student.s_id = score.s_id
group by student.s_id;
# 06 查询“李”姓老师的数量
select count(*)
from teacher
where t_name LIKE '李%';
# 07 查询学过张三老师授课的同学的信息
select *
from student
where s_id 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 = '张三')));
# 08 找出没有学过张三老师课程的学生
select *
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 = '张三')));
# 09 查询学过编号为 01，并且学过编号为 02 课程的学生信息
select *
from student
where s_id in (select s_id from score where c_id = '01')
and s_id in (select s_id from score where c_id = '02');
# 10 查询学过 01 课程，但是没有学过 02 课程的学生信息(注意和上面9题目的区别)
select *
from student
where s_id in (select s_id from score where c_id = '01')
and s_id not in (select s_id from score where c_id = '02');
# 11 查询没有学完全部课程的同学的信息
select s.*
from score
left join student s on score.s_id = s.s_id
group by s_id
having count(c_id) < (select count(*) from course);
# 12 查询至少有一门课与学号为 01 的同学所学相同的同学的信息
select *
from student
where s_id in (select distinct s_id
from score
where c_id in (select c_id from score where s_id = '01')
and s_id != '01');
# 13 查询和 01 同学学习的课程完全相同的同学的信息
select s.*
from score
left join student s on score.s_id = s.s_id
where score.s_id != '01'
group by s_id
having count(distinct c_id) = (select count(distinct c_id) from score where s_id = '01')
and s_id not in
(select s_id from score where c_id not in (select c_id from score where s_id = '01'));
# 14 查询没有修过张三老师讲授的任何一门课程的学生姓名
select s_name
from student
where s_id not in (select distinct s_id
from score
where c_id in (select c_id
from course
where t_id = (select t_id from teacher where t_name = '张三')));
# 15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.s_id, student.s_name, round(avg(s_score), 2) as avg_score
from (select s_id, c_id from score where s_score < 60) a
left join student on a.s_id = student.s_id
left join score s on student.s_id = s.s_id
group by a.s_id
having count(a.c_id) >= 2;
# 16 检索 01 课程分数小于 60，按分数降序排列的学生信息
select *
from score
where c_id = '01'
and s_score < 60
order by s_score desc;
# 17 按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
select s_id, SUM(s_score), round(avg(s_score), 2) as avg_score
from score
group by s_id
order by avg_score desc;
# 18 查询各科成绩最高分、最低分和平均分:以如下形式显示:课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率;及格:>=60，中等为:70-80，优良为:80-90，优秀为:>=90
select a.c_id,
b.c_name,
max(s_score),
min(s_score),
round(avg(s_score), 2),
round(100 * sum(case when a.s_score >= 60 then 1 else 0 end) /
sum(case when a.s_score then 1 else 0 end), 2) as 及格率,
round(100 * sum(case when a.s_score >= 70 and a.s_score <= 80 then 1 else 0 end) /
sum(case when a.s_score then 1 else 0 end), 2) as 中等率,
round(100 * sum(case when a.s_score >= 80 and a.s_score <= 90 then 1 else 0 end) /
sum(case when a.s_score then 1 else 0 end), 2) as 优良率,
round(100 * sum(case when a.s_score >= 90 then 1 else 0 end) /
sum(case when a.s_score then 1 else 0 end), 2) as 优秀率
from score a
left join course b on a.c_id = b.c_id
group by a.c_id, b.c_name;
# 19 按照各科成绩进行排序，并且显示排名
# mysql-8新增rank()函数 https://dev.mysql.com/doc/refman/8.4/en/window-function-descriptions.html#function_rank
# 5.x版本暂未支持
select a.*, b.*, c.*
from (select score.*, @i := @i + 1 as rank1
from score,
(select @i := 0) r
where c_id = '01'
order by s_score desc) a
left join (select score.*, @j := @j + 1 as rank2
from score,
(select @j := 0) r
where c_id = '02'
order by s_score desc) b on a.rank1 = b.rank2
left join (select score.*, @k := @k + 1 as rank3
from score,
(select @k := 0) r
where c_id = '03'
order by s_score desc) c on a.rank1 = c.rank3;
# 20 查询学生的总成绩，并进行排名
select @i := @i + 1 as rank1, a.s_id, sum_score
from (select s_id, sum(s_score) as sum_score
from score
where c_id = '01'
group by s_id
order by sum_score desc) a,
(select @i := 0) r
union all
select @i := @i + 1 as rank2, a.s_id, sum_score
from (select s_id, sum(s_score) as sum_score
from score
where c_id = '02'
group by s_id
order by sum_score desc) a,
(select @i := 0) s;
# 21 查询不同老师所教不同课程平均分从高到低显示
select teacher.t_id, teacher.t_name, score.c_id, round(avg(s_score), 2) avg
from score
left join course on score.c_id = course.c_id
left join teacher on teacher.t_id = course.t_id
group by score.c_id
order by avg desc;
# 22 查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩
select s_id, s_name, c_id, s_score
from (select student.s_id, student.s_name, c_id, s_score
from score
left join student on score.s_id = student.s_id
where c_id = '01'
order by s_score desc
limit 2 offset 1) a
union all
(select student.s_id, student.s_name, c_id, s_score
from score
left join student on score.s_id = student.s_id
where c_id = '02'
order by s_score desc
limit 2 offset 1)
union all
(select student.s_id, student.s_name, c_id, s_score
from score
left join student on score.s_id = student.s_id
where c_id = '03'
order by s_score desc
limit 2 offset 1);
# 23 统计各科成绩各分数段人数:课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60] 及所占百分比
select c_id,
sum(if(s_score <= 60, 1, 0)) as '[60-0]',
sum(if(s_score >= 60 and s_score <= 70, 1, 0)) as '[70-60]',
sum(if(s_score >= 70 and s_score <= 85, 1, 0)) as '[85-70]',
sum(if(s_score >= 85 and s_score <= 90, 1, 0)) as '[100-85]'
from score
group by c_id;
# 24 查询学生的平均成绩及名次
select @i := @i + 1 as rank,a.*
from (select s_id, round(avg(s_score), 2) avg_score
from score
group by s_id
order by avg_score desc) a, (select @i := 0) r;
# 25 查询各科成绩前三名的记录
select a.s_id, a.c_id, a.s_score
from score a
left join score b on a.c_id = b.c_id and a.s_score < b.s_score
group by a.s_id, a.c_id, a.s_score
HAVING COUNT(b.s_id) < 3
ORDER BY a.c_id, a.s_score desc;
# 26 查询每门课被选修的学生数
select c_id, count(s_id)
from score
group by c_id;
# 27 查询出只有两门课程的全部学生的学号和姓名
select stu.s_id, stu.s_name
from student stu
left join score sco on stu.s_id = sco.s_id
group by s_id
having count(sco.c_id) = 2;
# 28 查询男女生人数
select s_sex, count(*)
from student
group by s_sex;
# 29 查询名字中含有 风 字的学生信息
select *
from student
where s_name like '%风%';
# 30 查询同名同性的学生名单，并统计同名人数
select s_name, count(*) total
from student
group by s_name, s_sex
having total > 1;
# 31 查询 1990 年出生的学生信息
select *
from student
where YEAR(s_birth) = '1990';
# 32 查询每门课程的平均成绩，结果按平均成绩降序排列;平均成绩相同时，按课程编号 c_id 升序排列
select c_id, round(avg(s_score), 2) avg
from score
group by c_id
order by avg desc, c_id;
# 33 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select score.s_id, student.s_id, avg(score.s_score) avg
from score
left join student on score.s_id = student.s_id
group by score.s_id
having avg >= 18;
# 34 查询课程名称为数学，且分数低于 60 的学生姓名和分数
select student.s_name, score.s_score
from score
right join student on score.s_id = student.s_id
where c_id = (select c_id from course where c_name = '数学')
and s_score < 60;
# 35 查询所有学生的课程及分数情况
select stu.s_id, stu.s_name, c.c_id, c.c_name, sco.s_score
from score sco
right join course c on sco.c_id = c.c_id
right join student stu on stu.s_id = sco.s_id;
# 36 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select stu.s_name, c.c_name, sco.s_score
from score sco
left join student stu on sco.s_id = stu.s_id
left join course c on sco.c_id = c.c_id
where s_score >= 70;
# 37 查询不及格的课程
select a.s_id, a.c_id, c.c_name, a.s_score
from score a
left join course c on a.c_id = c.c_id
where s_score < 60;
# 38 查询课程编号为 01 且课程成绩大于等于 80 的学生的学号和姓名
select student.s_id, student.s_name
from student
left join score on student.s_id = score.s_id
where c_id = '01'
and s_score >= 80;
# 39 每门课程的学生人数
select c_id, count(distinct s_id)
from score
group by c_id;
# 40 查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
select *
from score
where
c_id in (select c_id from course where t_id = (select t_id from teacher where t_name = '张三'))
order by s_score desc
limit 1;
# 41 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select DISTINCT b.s_id, b.c_id, b.s_score
from score a,
score b
where a.c_id != b.c_id
and a.s_id != b.s_id
and a.s_score = b.s_score;
# 42 查询每门功成绩最好的前两名
select a.c_id, a.s_id, a.s_score
from score a
where (select COUNT(1) from score b where b.c_id = a.c_id and b.s_score >= a.s_score) <= 2
ORDER BY a.c_id;
# 43 统计每门课程的学生选修人数(超过 5 人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列， 若人数相同，按课程号升序排列
select c_id, count(s_id) as total
from score
group by c_id
having total > 5
order by total desc, c_id;
# 44 检索至少选修两门课程的学生学号
select s_id
from score
group by s_id
having count(c_id) >= 2;
# 45 查询选修了全部课程的学生信息
select s_id
from score
group by s_id
having count(c_id) = (select count(distinct c_id) from course);
# 46 查询各学生的年龄:按照出生日期来算，当前月日 < 出生年月的月日则，年龄减 1
select s_id,
s_name,
s_birth,
date_format(now(), '%Y') - date_format(s_birth, '%Y') -
if(date_format(now(), '%m%d') < date_format(s_birth, '%m%d'), 1, 0) as age
from student;
# 47 查询本周过生日的学生
select *
from student
where week(s_birth) = week(now());
# 48 查询下周过生日的学生
select *
from student
where week(s_birth) = week(now()) + 1;
# 49 查询本月过生的同学
select *
from student
where month(s_birth) = month(now());
# 50 查询下月过生的同学
select *
from student
where month(s_birth) = month(now()) + 1;
