Mysql 入门 50题目解析
# 表结构
# Student Course Teacher Score
# 学生表 课程表 教师表 成绩表
# 学生ID 主键 s_id 课程ID 主键 c_id 教师ID 主键 t_id 学生ID 主键 s_id
# 学生姓名 s_name 课程名字 c_name 教师姓名 t_name 课程ID c_id
# 学生生日 s_birth 课程教师ID t_id 课程分数 s_score
# 学生性别 s_sex
use test;
show tables;
show table status;
select * from student;
select * from Course;
select * from teacher;
select * from score;
alter table score;
# 1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
# 思路：
# 1.1 查询所有选修01和02的课程的学生
select s_id, score from sc where sc.c_id='01';
select s_id, s_score from score where score.c_id='01'; # as t01
select s_id, s_score from score where score.c_id='02'; # as t02
# 1.2 选出01课程02的课程比02课程分数高的学生ID
select t1.s_id,t2.s_id, t1.s_score, t2.s_score from (
(select s_id, s_score from score where score.c_id='01') as t1,
(select s_id, s_score from score where score.c_id='02') as t2
)
where t1.s_id = t2.s_id and t1.s_score > t2.s_score;
# 1.3 使用join关联学生信息表，查询出所有学生信息
select * from student right join( #右连接
select t1.s_id, class1, class2 from (
(select s_id, s_score as class1 from score where score.c_id='01') as t1,
(select s_id, s_score as class2 from score where score.c_id='02') as t2
)
where t1.s_id = t2.s_id and t1.class1 > t2.class2
) as r # 01课程比01课程分数多的学生的id 以及课程分数
on student.s_id = r.s_id;
# 2 查询同时在01和02课程的学生 条件查询
# 2.1 直接借鉴上一题目
select * from
(select * from score where score.s_id = '01') as t01,
(select * from score where score.s_id = '02') as t02
where t01.s_id = t02.s_id;
# 3 查询学生课程 有01 但是不一定有02 不存在为null
select * from
(select * from score where score.s_id = '01') as t01
left join
(select * from score where score.s_id = '02') as t02
on t01.s_id = t02.s_id;
# 4 查询学生没学过" 01 "课程但学过" 02 "课程的情况
# 4.1 查询出学习01课程的学生
# 4.2 以4.1为条件进行过滤查询
# 4.1 code
select s_id from score where score.s_id = '01';
# 4.2 code
select * from score
where score.s_id
not in(select s_id from score where score.s_id = '01')
and score.s_id = '02';
# 5 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
# 5.1 在 score分数表中使用id分组 对分组数据avg求均值
# 5.2 使用join 对score表 以及student表进行关联查询 筛选出id name avg-score
# 5.1 code
select s_id, avg(s_score) as avgss
from score
group by s_id
having avg(s_score) >60;
# 5.2
select student.s_id, student.s_name, scoreavg.avgss from
student right join (
select s_id, avg(s_score) as avgss
from score
group by s_id
having avg(s_score) >60) as scoreavg on student.s_id = scoreavg.s_id;
# 6 查询score表中存在成绩的学生信息
# distinct 区分 进行唯一查询
# 也可以使用关联查询，但是比较麻烦
select distinct student.* from student, score where student.s_id = score.s_id;
# 7 查询所学生的编号，姓名，选课总数，所有课程成绩的总成绩
# 7.1 所有课程成绩的总和，需要group by 分组 求和
# 7.2 需要所有的学生信息，需要join 连接student表
# 7.1
select s_id, sum(score.s_score) from score group by score.s_id having sum(score.s_score);
# 7.2
select
student_info.s_id,
student_info.s_name,
count(score_info.c_id) as sum_course,
sum(score_info.s_score) as sum_score
from
student as student_info
left join score as score_info on student_info.s_id = score_info.s_id
group by
student_info.s_id;
# 8 查询 所有姓李的老师的数量
# 8.1 使用 like % 模糊匹配
select count(teacher.t_id) from teacher where teacher.t_name like '李%';
# 9 查询学过 张三 老师课程的学生的信息
# 9.1 在teacher 表 找到 张三 老师的id
# 9.2 在course 表 找到所有张三老师的课程ID
# 9.3 在score表和student表中联以课程ID 为条件进行联合查询
# 9.1
select t_id from teacher where t_name = '张三';
# 9.2
select c_id from course where course.t_id = (select t_id from teacher where t_name = '张三');
# 9.3
select *
from
student as student_info join score as score_info on student_info.s_id = score_info.s_id
where
score_info.c_id in
(select c_id
from
course
where course.t_id = (select t_id from teacher where t_name = '张三'));
# 9 方法二
select
student.*, score.c_id
from
student, teacher, course, score
where
student.s_id = score.s_id
and
course.c_id = score.c_id
and
course.t_id = teacher.t_id
and
t_name = '张三';
# 10 查询没学过 张三老师课程的学生的信息
# 同上 使用 not in
# 方法 1
select *
from
student as student_info join score as score_info on student_info.s_id = score_info.s_id
where
score_info.c_id not in
(select c_id
from
course
where course.t_id = (select t_id from teacher where t_name = '张三'));
# 方法 2
select
student.*, score.c_id
from
student, teacher, course, score
where
student.s_id = score.s_id
and
course.c_id = score.c_id
and
course.t_id = teacher.t_id
and
t_name != '张三';
# 11 查询没有学全所有课程的学生
# 反向思维，有学全的就有没有学全的 (漏洞 可能都没有学全)
# 或者
# 在course表中count 课程数
# 在score表中group by student id count 课程数 小于所有课程数的就是没有学全的
# 方法 1 查找出学全的课程的学生ID
select score.s_id, count(score.s_id) from score group by score.s_id having count(score.s_id) = (select count(course.c_id) from course);
# 方法 2
select student_info.* from student as student_info left join score as score_info on student_info.s_id = score_info.s_id
group by
student_info.s_id
having
count(score_info.c_id) < (select count(course.c_id) from course);
# 12 查询至少有一门课程 和 01号学生 所学的课程相同的学生的信息
# 1 从score表中查询 01号 学生所学所有课程的 c_id
# 2 从score表中查询涉及c_id 课程的所有学生的s_id
# 3 从student表中查询包含的s_id 的信息
# step 1
select score.c_id from score where score.s_id = '01';
# step 2
select score.s_id from score where score.c_id in (select score.c_id from score where score.s_id = '01');
# step 3
select
student.*
from
student
where
student.s_id
in
(select score.s_id from score where score.c_id in (select score.c_id from score where score.s_id = '01'));
# 13 查询所有和 01号 学生所学相同的课程的学生
# 1 从score表中group concat 出 01号的课程c_id
# 2 从score中group concat 所有学生的课程c_id
# 3 找出group concat 相同的 s_id
# 4 从student 中查询找到的s_id 信息
# 13.1 01 号学生 的课程
select score.s_id, group_concat(score.c_id) from score group by score.s_id having score.s_id = '01';
# (select score.s_id, group_concat(score.c_id) from score group by score.s_id having score.s_id = '01') as 01group_concat;
# 13.2 所有学生的课程
select score.s_id, group_concat(score.c_id) from score group by score.s_id;
# (select score.s_id, group_concat(score.c_id) from score group by score.s_id) as allgroup_concat
# 13.3 选出所有学生中group_concat c_id = group_concat 01 c_id 的s_id
select allgroup_concat.s_id
from
(select score.s_id, group_concat(score.c_id) as all_concat from score group by score.s_id) as allgroup_concat,
(select score.s_id, group_concat(score.c_id) as 01_concat from score group by score.s_id having score.s_id = '01') as 1_group_concat
where
allgroup_concat.all_concat = 1_group_concat.01_concat;
# 13.4 从student中选出3中的s_id的学生信息 并且排除掉01号本身
select * from student
where
student.s_id in
(
select allgroup_concat.s_id
from
(select score.s_id, group_concat(score.c_id) as all_concat from score group by score.s_id) as allgroup_concat,
(select score.s_id, group_concat(score.c_id) as 01_concat from score group by score.s_id having score.s_id = '01') as 1_group_concat
where
allgroup_concat.all_concat = 1_group_concat.01_concat
)
and
student.s_id != '01';
# 14 查询没有学过 张三老师 课程的学生信息
# 从teacher表中查询老师id
# 从course中通过teacherid找到courseid
# 从score中通过courseid找到student id
# 14.1
select teacher.t_id from teacher where teacher.t_name = '张三';
# 14.2
select course.c_id from course where course.t_id in (select teacher.t_id from teacher where teacher.t_name = '张三');
# 14.3
select score.s_id from score where score.c_id in
(select course.c_id from course where course.t_id in
(select teacher.t_id from teacher where teacher.t_name = '张三'));
# 14.4
select * from student where student.s_id in
(select score.s_id from score where score.c_id in
(select course.c_id from course where course.t_id in
(select teacher.t_id from teacher where teacher.t_name = '张三')));
# 15 查询两门机器以上不及格的学生的学号，姓名，平均成绩
# 从score中选出score小于60的
# group by s_id count score.s_score >1
# 15.1 # 在score表中选出成绩低于60的
select * from score where score.s_score < 60;
# 15.2 # 在score对选出成绩<60的进行分组， 这样才能统计低于60的科目有几科
select *, group_concat(score.s_score) from score where score.s_score < 60 group by score.s_id;
# 15.3 # 在score中对分组后的<60的进行计数
select *, group_concat(score.s_score)from score where score.s_score < 60 group by score.s_id having count(*) > 1;
# 15.4
select
student.s_id, student.s_name, avg(score.s_score), group_concat(score.s_score)
from
student, score
where
student.s_id = score.s_id
and
score.s_score < 60 group by score.s_id having count(*) > 1;
# 16 查询 01课程 分数小于60的学生 按照分数降序排列
# 16.1 在score中查询01课程小于60的学生
# 16.02 排序
# 16.01
select * from score where score.s_score < 60 and score.c_id = '01';
# 16.02
select * from score where score.s_score < 60 and score.c_id = '01' order by score.s_score desc;
# 17 所有学生的查询平均成绩按照降序排列 显示 所有课程成绩以及平均成绩
# 17.1 查出所有学生的平均成绩
select score.s_id, group_concat(score.s_score), avg(score.s_score) from score group by score.s_id;
# 17.2 内连接显示成绩
select * from score
left join
(select score.s_id, avg(score.s_score) as avgscore from score group by score.s_id) as group_score
on
score.s_id = group_score.s_id
order by
avgscore
desc;
# 18 查询各个课程的最高分最低分平均分 按照 课程ID 最高分 最低分 平均分 及格率 中等率 优良率 优秀率 排列
# 及格（ >= 60） 中等（70-80） 优良率（80-90） 优秀率（>90）
# 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
# max min avg
select
score.s_id,
max(score.s_score) as '最高分',
min(score.s_score) as '最低分',
avg(score.s_score) as '平均分',
count(*) as '人数',
sum(case when score.s_score >= 60 then 1 else 0 end )/count(*) as '及格率',
sum(case when score.s_score >= 70 and score.s_score < 80 then 1 else 0 end )/count(*) as '中等率',
sum(case when score.s_score >= 80 and score.s_score < 90 then 1 else 0 end )/count(*) as '优良率',
sum(case when score.s_score >= 90 then 1 else 0 end )/count(*) as '优秀率'
from
score
group by
score.s_id
order by
count(*) desc,
score.c_id asc;
# 19 按照各个科目的成绩进行排序
# 20 查询学生的总成绩 并进行排名 总分重复的时候不保留名次
# 21 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
# 1 course表和score表 group by join 查询
select course.c_name, course.c_id,
sum(case when score.s_score <=100 and score.s_score>85 then 1 else 0 end) as '[100-85]',
sum(case when score.s_score <=100 and score.s_score>85 then 1 else 0 end) / count(*) as '%',
sum(case when score.s_score <=85 and score.s_score>70 then 1 else 0 end) as '[85-70]',
sum(case when score.s_score <=85 and score.s_score>70 then 1 else 0 end) / count(*) as '%',
sum(case when score.s_score <=70 and score.s_score>60 then 1 else 0 end) as '[70-60]',
sum(case when score.s_score <=70 and score.s_score>60 then 1 else 0 end) / count(*) as '%',
sum(case when score.s_score <=60 and score.s_score>0 then 1 else 0 end) as '[60-0]',
sum(case when score.s_score <=60 and score.s_score>0 then 1 else 0 end) / count(*) as '%'
from
score left join course
on
score.c_id = course.c_id
group by
score.c_id;
# 22 查询各个科目成绩的前三名的记录
# 22.1 进行自关联
# 22.2 当2表比1表大的 前3个记录
select
t1.s_id, t1.c_id, t1.s_score
from
score as t1 left join score as t2 on t1.c_id = t2.c_id and t1.s_score < t2.s_score
group by
t1.s_id, t1.c_id, t1.s_score having count(t2.s_id) < 3
order by
t1.c_id;
# 23 查询出只选修两门课程的学生学号和姓名
# 使用 嵌套查询
# 1 对score按照s id分组
# 2 联合student表进行查询
select student.s_id, student.s_name from student
where
student.s_id in
(select score.s_id from score group by score.s_id having count(score.c_id) = 2);
# 24 查询男生人数
# group by 分组查询
select student.s_sex, count(student.s_sex) from student group by student.s_sex;
# 25 .查询同名学生名单，并统计同名人数
# 按照名字进行group by 找到同名的名字并统计个数
select student.s_name, count(student.s_name) from student group by student.s_name having count(*)>1;
# 26 查询 1990 年出生的学生名单
# 简单条件查询
select * from student where year(student.s_birth) = 1990;
# 27 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select score.s_id, course.c_name, avg(score.s_score) as avgscore from score, course where score.c_id = course.c_id group by score.c_id order by avgscore desc, course.c_id asc;
# 28 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
# 1 在score表中 group by s id 找到avg > 70 的 s id
select *, group_concat(score.s_score), avg(score.s_score) from score group by score.s_id having avg(score.s_score) >85;
# 2 嵌套查询
select
* #student.*, avg(score.s_score) as avgscore
from
score, student
where
score.s_id = student.s_id
group by
score.s_id
having
avg(score.s_score) > 85;
# 29 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
# 条件查询
select * from score, student, course where student.s_id = score.s_id and course.c_id = score.c_id and score.s_score < 60;
# 30 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
# 左连接查询 链接 student score
select * from student left join score on student.s_id = score.s_id;
# 31 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
# 多表查询
select * from student, course, score where student.s_id = score.s_id and score.c_id = course.c_id and score.s_score > 70;
# 32 查询存在不及格的课程ID
# group by 来取唯一，也可以用distinct
select * from score where score.s_score < 60 group by score.c_id;
# 33 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
# 多条件查询
select * from score, student where score.s_id = student.s_id and score.s_score >= 80 and score.c_id = '01';
# 34 求每门课程的学生人数
# 在score表中group by c_id
select *, count(score.s_id) from score group by score.c_id;
# 35 查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 (成绩不重复的时候)
# 涉及表 course teacher score student
select * from teacher, course, score, student
where
teacher.t_id = course.t_id
and
course.c_id = score.c_id
and
score.s_id = student.s_id
and
teacher.t_name ='张三'
order by
score.s_score desc limit 1;
# 36 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
# 内连接 链接条件即成绩相同 课程不同
# distinct 去重
select
distinct score_info1.s_id,score_info1.c_id,score_info1.s_score
from
score as score_info1, score as score_info2
where
score_info1.s_score = score_info2.s_score and score_info1.c_id != score_info2.c_id;
# 37 查询每门功成绩最好的前两名
# 内连接 通过每次的对比找出最大的前两条
select
*
from
score as score_info1 left join score as score_info2 on score_info1.c_id = score_info2.s_id and score_info1.s_score < score_info2.s_score
group by
score_info1.c_id, score_info2.c_id
having
count(score_info2.c_id) < 2
order by
score_info1.c_id;
# 统计每门课程的学生选修人数（超过 5 人的课程才统计）
select *, count(score.s_id) as countcid from score group by score.c_id having count(countcid) > 5;
# 38 查询至少选修两门课程的学生学号
# 对score group by sid
select distinct *, group_concat(score.c_id) from score group by score.s_id having count(score.c_id) >1;
# 39 查询选修了全部课程的学生信息
# distinct 出表course的课程
# 在score表中选出与课程数相同的sid
# 在student表中查询sid信息
select student.*
from
student, score
where
student.s_id = score.s_id
group by
score.s_id having count(score.c_id) = (select distinct count(*) from course);
# 40 查询各学生的年龄，只按年份来算
# timestampdiff 按照指定时间间隔计算指定日期到某个日趋的间隔时间
select student.*, (timestampdiff(year, student.s_birth, curdate())) from student;
# 41 查询本周过生日的学生
# 使用时间函数找出 和当前周相同的学生生日周
select student.* from student where weekofyear(student.s_birth) = weekofyear(curdate());
# 43 查询下周过生日的学生
# 使用时间函数找出和当下周（比当前周多一周）的学生生日周
select student.* from student where weekofyear(student.s_birth) = weekofyear(curdate()) + 1;
# 44 查询本月过生日的学生
# 原理同上 找出相同月
select * from student where month(student.s_birth) = month(curdate());
# 45 查询下月过生日的学生
# 原理同43
select * from student where month(student.s_birth) = month(curdate()) + 1;