mysql查询 多门课程的平均成绩_数据分析中级 MySQL 任务6 总结复习

0 入门

0.1 MySQL安装，Navicat安装；

0.2 MySQL设置，Nacicat设置，包括链接点，unicode-8；

0.3 创建表格，student, course, score, teacher .

1 简单查询

1.1 查询姓’猴‘学生名单，名字最后一字是’猴‘，名字中含有’猴‘字的名单

-- aselect * from student

where name like '猴%';

-- bselect * from student

where name like '%猴';

-- cselect * from student

where name like '%猴%';

1.2 查询姓“孟”老师的个数

select count(教师号) from teacher

where name like '孟%';

2 汇总分析

2.1 查询课程编号为“0002”的总成绩

/*

分析思路

select 查询结果 [总成绩:汇总函数sum]

from 从哪张表中查找数据[成绩表score]

where 查询条件 [课程号是0002]

*/

select sum(成绩) from score

where 课程号 = '0002';

2.2 查询选了课程的学生人数

/*

select 学号，count(distinct 成绩表里学号)

from 从课程表查找score;

*/

select count(distinct 学号) as 学生人数

from score;

2.3 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分

/*

分析思路

select 查询结果 [课程ID：是课程号的别名,最高分：max(成绩),最低分：min(成绩)]

from 从哪张表中查找数据 [成绩表score]

where 查询条件 [没有]

group by 分组 [各科成绩：也就是每门课程的成绩，需要按课程号分组];

*/

select 课程号,max(成绩) as 最高分,min(成绩) as 最低分

from score

group by 课程号;

2.4 查询每门课程被选修的学生数

/*

select 查询结果 [课程号，count(选修该课程的学生学号)]

from 从哪张表中查找数据 [成绩表score]

group by 按课程号分组

*/

select 课程号, count(学号) from score

group by 课程号;

2.5 查询男生、女生人数

/*

select 查询结果 [性别，count(性别)]

from 从哪张表中查找数据 [student]

group by 按课程号分组

*/

select distinct 性别, count(性别) from student

group by 性别;

2.6 条件查询 -- 查询平均成绩大于60分学生的学号和平均成绩

/*

select 查询结果 [性别，count(性别)]

from 从哪张表中查找数据 [score]

group by 按课程号分组

*/

select 学号, avg(成绩) from score

group by 学号

having avg(成绩) > 65;

2.7 查询至少选修两门课程的学生学号

/*

select 查询结果 [学号, count(学号)]

from 从哪张表中查找数据 [score]

group by 按学号

having 查询条件 选了多门课

*/

select 学号, count(学号) as 选课数 from score

group by 学号

having count(学号) > 1;

2.8 查询同名同性学生名单并统计同名人数

/*

select 查询结果 [姓名, count(姓名)]

from 从哪张表中查找数据 [student]

group by 姓名

having 查询条件 重名

*/

select 姓名, count(姓名)

from student

group by 姓名

having count(姓名)>1;

2.9 查询不及格的课程并按课程号从大到小排列

/*

select 查询结果 [课程号]

from 从哪张表中查找数据 [成绩表score]

where 查询条件 [不及格：成绩 ‹60]

order by 对查询结果排序[课程号从大到小排列：降序desc];

*/

select 课程号

from score

where 成绩‹60

order by 课程号 desc;

2.10 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

/*

select 查询结果 [课程号,平均成绩：汇总函数avg(成绩)]

from 从哪张表中查找数据 [成绩表score]

group by 分组 [每门课程：按课程号分组]

order by 对查询结果排序[按平均成绩升序排序:asc，平均成绩相同时，按课程号降序排列:desc];

*/

select 课程号, avg(成绩) as 平均成绩

from score

group by 课程号

order by 平均成绩 asc,课程号 desc;

2.11 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

/*

要点，where条件查询中用and连接

*/

select 学号

from score

where 课程号='04' and 成绩 ‹60

order by 成绩 desc;

2.12 统计每门课程的学生选修人数(超过2人的课程才统计)

要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select 课程号, count(学号) as '选修人数'

from score

group by 课程号

having count(学号)›2

order by count(学号) desc,课程号 asc;

2.13 查询两门以上不及格课程的同学的学号及其平均成绩

/*

注意where having的条件查询的不同

where 查询条件 [限制条件：不及格课程，平均成绩‹60]

group by 分组 [每个学生的平均：按学号分组]

having 对分组结果指定条件 [限制条件：课程数目›2,汇总函数count(课程号)›2]

*/

select 学号, avg(成绩) as 平均成绩

from score

where 成绩 ‹60

group by 学号

having count(课程号)›=2;

3 复杂查询

3.1 查询所有课程成绩小于60分学生的学号、姓名

/*

子查询在条件语句中

*/

select 学号,姓名

from student

where 学号 in (

select 学号

from score

group by 学号

having max(成绩) < 60

);

3.2 查询没有学全所有课的学生的学号、姓名

/*

两种情况：1.课没修够，2.没修过任何课

*/

select 学号,姓名

from student

where 学号 in (

select 学号

from score

group by 学号

having count(学号)

)

or 学号 not in (select 学号 from score);

3.3 查询出只选修了两门课程的全部学生的学号和姓名

select 学号,姓名

from student

where 学号 in(

select 学号

from score

group by 学号

having count(课程号)=2);

3.4 1990年出生的学生名单

/*

查找1990年出生的学生名单

学生表中出生日期列的类型是datetime

*/

select 学号,姓名

from student

where year(出生日期)=1990;

3.5 【面试题类型】topN问题

(select * from score where 课程号 = '0001' order by 成绩 desc limit 2)

union all

(select * from score where 课程号 = '0002' order by 成绩 desc limit 2)

union all

(select * from score where 课程号 = '0003' order by 成绩 desc limit 2);

4 多表查询

4.1 查询所有学生的学号、姓名、选课数、总成绩

select a.学号, a.姓名, count(b.学号) as 选课数, sum(成绩) as 总成绩

from student as a left join score as b on a.学号 = b.学号

group by b.学号;

4.2 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.学号, a.姓名, sum(成绩)/count(b.学号) as 平均成绩

from student as a left join score as b on a.学号 = b.学号

group by b.学号

having sum(成绩)/count(b.学号) >85;

4.3 查询学生的选课情况：学号，姓名，课程号，课程名称

select a.学号, a.姓名, b.课程号, 课程名称

from (student as a left join score as b on a.学号 = b.学号)

left join course as c on b.课程号 = c.课程号

order by a.学号, b.课程号;

4.4 查询出每门课程的及格人数和不及格人数

-- case语句统计

select 课程号,

sum(case when 成绩 >=60 then 1

else 0

end) as 及格人数,

sum(case when 成绩 < 60 then 1

else 0

end) as 不及格人数

from score

group by 课程号;

4.5 使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select a.课程号,b.课程名称,

sum(case when 成绩 > 85 then 1 else 0 end) as '[100-85]',

sum(case when 成绩 >=70 and 成绩 < 85 then 1 else 0 end) as '[85-70]',

sum(case when 成绩 >=60 and 成绩 < 70 then 1 else 0 end) as '[70-60]',

sum(case when 成绩 <60 then 1 else 0 end) as '[<60]'

from score as a right join course as b

on a.课程号=b.课程号

group by a.课程号,b.课程名称;

4.6 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

select a.学号,a.姓名

from student as a inner join score as b on a.学号=b.学号

where b.课程号='0003' and b.成绩 > 80;

4.7 【面试题类型总结】这类题目属于行列如何互换

/*

第1步，使用常量列输出目标表的结构

第2步，使用case表达式，替换常量列为对应的成绩

第3关，分组

*/

select 学号,'课程号0001','课程号0002','课程号0003'

from score;

select 学号,

(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',

(case 课程号 when '0002' then 成绩 else 0 end) as '课程号0002',

(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'

from score;

select 学号,

max(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',

max(case 课程号 when '0002' then 成绩 else 0 end) as '课程号0002',

max(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'

from score

group by 学号;