有意思的基础sql教程

--------2020-04-17

今天看到一篇sql练习题，很有趣，稍作提取，记录一下~

原文章链接：https://zhuanlan.zhihu.com/p/38354000

--------------------------------------------------------------------无情冷血的分割线----------------------------------------------------------

一、库表

学生表：student (学号,学生姓名,出生年月,性别)

成绩表：score (学号,课程号,成绩)

课程表：course (课程号,课程名称,教师号)

教师表：teacher (教师号,教师姓名)

二、练习题

1、简单查询

• 查找姓猴的学生：like‘猴%’
• 查找最后一个字为猴的学生：like‘%猴’
• 查找有猴字的学生：like‘%猴%’

2、汇总分析

• 查询课程为‘002’的总成绩：（求和）select sum(成绩) from score where 课程号=‘002’;
• 查询选课人数：（数据的去重）select count(distinct 学号) as 选课人数 from score;

3、分组

• 查询各科成绩最高和最低的分：

• select 课程号,max(成绩) as 最高分,min(成绩) as 最低分
  from score
  group by 课程号;

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

• select 课程号, count(学号)
  from score
  group by 课程号;

• 查询男女人数：

• select 性别,count(*)
  from student
  group by 性别;

4、分组结果再查询

• 查询平均成绩大于60分学生的学号和平均成绩：（查出每个学生的平均成绩【按学号分组】，分组数据筛选：成绩>60）

• select 学号, avg(成绩)
  from score
  group by 学号
  having avg(成绩)>60;

• 查询至少选修2门课的学生学号：（查出每个学生的课程数量【按学号分组】，分组数据筛选：课程数>2）

• select 学号, count(课程号) as 选修课程数目
  from score
  group by 学号
  having count(课程号)>=2;

• 查询同名同性学生名单并统计同名人数：（查同名同性的学生个数【按姓名分组】，分组数据筛选：个数>=2【同名同性说明至少两个人】）

• select 姓名,count(*) as 人数
  from student
  group by 姓名
  having count(*)>=2;

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

• select 课程号
  from score 
  where 成绩<60
  order by 课程号 desc;

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

• 按课程号分组，按平均成绩升序排序:asc，平均成绩相同时，按课程号降序排列:desc

• select 课程号, avg(成绩) as 平均成绩
  from score
  group by 课程号
  order by 平均成绩 asc,课程号 desc;

5、复杂查询

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

• ①分数表score查出成绩小于60分学生的学号;②根据学号在student表查询学生姓名

• select 学号,姓名
  from student
  where  学号 in (
  select 学号 
  from student
  where 成绩 < 60
  );

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

• ①count求所有课程数量;②每一个学生选修课程数量：group by；③having条件：学生课程数量<课程总数量

• select 学号,姓名
  from student
  where 学号 in(
  select 学号 
  from score
  group by 学号
  having count(课程号) < (select count(课程号) from course)
  );

6、多表查询

• 查询所有学生的学号、姓名、选课数、总成绩：（左连接：left join）

• selecta.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
  from student as a left join score as b
  on a.学号 = b.学号
  group by a.学号;

• 查询平均成绩大于85的所有学生的学号、姓名和平均成绩：（左连接：left join）

• select a.学号,a.姓名, avg(b.成绩) as 平均成绩
  from student as a left join score as b
  on a.学号 = b.学号
  group by a.学号
  having avg(b.成绩)>85;

• 查询学生的选课情况：学号，姓名，课程号，课程名称：（内连接：inner join）

• select a.学号, a.姓名, c.课程号,c.课程名称
  from student a inner join score b on a.学号=b.学号
  inner join course c on b.课程号=c.课程号;

• 查询出每门课程的及格人数和不及格人数：（判断条件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 课程号;

• 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称：（判断条件case的使用）

• elect a.课程号,b.课程名称,
  sum(case when 成绩 between 85 and 100 
       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.课程名称;

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

• select a.学号,a.姓名
  from student  as a inner join score as b on a.学号=b.学号
  where b.课程号='0003' and b.成绩>80;

--------------------------------------------------------------------无情冷血的分割线----------------------------------------------------------

左连接：left join / left outer join（左表有的，右表没有的为null）

右连接：right join / right outer join（左表没有的，右表有的为null）

内连接：inner join / inner outer join（显示左右边表共有的）

三者的使用表现：

左连接：select A.*,B.* from A left outer join B on(A.a1=B.a2) ------（左表有的，右表没有的为null）

右连接：select A.*,B.* from A right outer join B on(A.a1=B.a2)------（左表没有的，右表有的为null）

内连接：SELECT * FROM a, b inner join b on(a.a1=b.a2) ------（显示左右边表共有的）

自然联结：SELECT * FROM a, b where a.a1=b.a2，这两种写法一样，一般情况下都使用自然联结。