Mysql 入门 50题目解析

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;