参考：https://zhuanlan.zhihu.com/p/38354000

一、前期准备

1.SQL建表语句

  1. drop table if exists course;
  2. CREATE TABLE `course` (
  3.   `course_id` varchar(255) NOT NULL COMMENT '课程号',
  4.   `course_name` varchar(255) NOT NULL COMMENT '课程名称',
  5.   `teacher_id` varchar(255) NOT NULL COMMENT '教师号'
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  8. #drop table if exists score;
  9. #CREATE TABLE `score` (
  10. # `student_id` varchar(255) NOT NULL COMMENT '学号',
  11. # `course_id` varchar(255) NOT NULL COMMENT '课程号',
  12. # `scores` float NOT NULL COMMENT '成绩',
  13. # PRIMARY KEY (`course_id`)
  14. #) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  16. drop table if exists score;
  17. CREATE TABLE `score` (
  18.  `student_id` varchar(255) NOT NULL COMMENT '学号',
  19.  `course_id` varchar(255) NOT NULL COMMENT '课程号',
  20.  `scores` float NOT NULL COMMENT '成绩',
  21. #  PRIMARY KEY (`course_id`),
  22.  unique index (course_id,student_id)
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  27. drop table if exists student;
  28. CREATE TABLE `student` (
  29.     `student_id` varchar(255) NOT NULL COMMENT '学号',
  30.     `student_name` varchar(255) NOT NULL COMMENT '姓名',
  31.     `birth_date` varchar(255) NOT NULL COMMENT '出生日期',
  32.     `sex` varchar(255) NOT NULL COMMENT '性别',
  33.     PRIMARY KEY (`student_id`)
  34. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  36. drop table if exists teacher;
  37. CREATE TABLE `teacher` (
  38.     `teacher_id` varchar(255) NOT NULL COMMENT '教师号',
  39.     `teacher_name` varchar(255) NOT NULL COMMENT '教师名称',
  40. PRIMARY KEY (`teacher_id`)
  41. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.数据录入

  2. insert into student(student_id,student_name,birth_date,sex)
  3. values('0001' , '猴子' , '1989-01-01' , '男');
  5. insert into student(student_id,student_name,birth_date,sex)
  6. values('0002' , '猴子' , '1990-12-21' , '女');
  8. insert into student(student_id,student_name,birth_date,sex)
  9. values('0003' , '马云' , '1991-12-21' , '男');
  11. insert into student(student_id,student_name,birth_date,sex)
  12. values('0004' , '王思聪' , '1990-05-20' , '男');
  15. insert into score(student_id,course_id,scores)
  16. values('0001' , '0001' , 80);
  18. insert into score(student_id,course_id,scores)
  19. values('0001' , '0002' , 90);
  21. insert into score(student_id,course_id,scores)
  22. values('0001' , '0003' , 99);
  24. insert into score(student_id,course_id,scores)
  25. values('0002' , '0002' , 60);
  27. insert into score(student_id,course_id,scores)
  28. values('0002' , '0003' , 80);
  30. insert into score(student_id,course_id,scores)
  31. values('0003' , '0001' , 80);
  33. insert into score(student_id,course_id,scores)
  34. values('0003' , '0002' , 80);
  36. insert into score(student_id,course_id,scores)
  37. values('0003' , '0003' , 80);
  39. select * from score;
  42. insert into course(course_id,course_name,teacher_id)
  43. values('0001' , '语文' , '0002');
  45. insert into course(course_id,course_name,teacher_id)
  46. values('0002' , '数学' , '0001');
  48. insert into course(course_id,course_name,teacher_id)
  49. values('0003' , '英语' , '0003');
  52. insert into teacher(teacher_id,teacher_name)
  53. values('0001' , '孟扎扎');
  55. insert into teacher(teacher_id,teacher_name)
  56. values('0002' , '马化腾');
  58. -- 这里的teacher_name是空值（null）
  59. insert into teacher(teacher_id,teacher_name)
  60. values('0003' , null);
  62. -- 这里的teacher_name是空字符串（''）
  63. insert into teacher(teacher_id,teacher_name)
  64. values('0004' , '');

二、简单查询

  1. -- 查询姓“孟”老师的个数
  2. select * from teacher where teacher_name like '%孟%';

三、汇总分析

  1. -- 查询课程编号为“0002”的总成绩
  2. select sum(scores) from score where course_id='0002';
  1. -- 查询选了课程的学生人数
  2. select count(distinct student_id) from score;

四、分组查询

  1. -- 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
  2. select course_id, max(scores) as maxscores, min(scores) as minscores
  3. from score group by course_id;
  1. -- 查询每门课程被选修的学生数
  2. select course_id, count(distinct student_id) from score group by course_id;
  1. -- 查询男生、女生人数
  2. select sex, count(student_id) from student group by sex;

五、分组结果查询

  1. -- 查询平均成绩大于60分学生的学号和平均成绩
  2. select student_id, avg(scores) from score group by student_id having avg(scores) > 60;
  1. -- 查询至少选修两门课程的学生学号
  2. select student_id from score group by student_id having count(course_id) >= 2;
  1. -- 查询同名同姓学生名单并统计同名人数
  2. select student_name, count(student_name) from student  group by student_name having count(student_name) >= 2;
  1. -- 查询不及格的课程并按课程号从大到小排列
  2. select course_id from score group by course_id having min(scores) <60 order by course_id desc ;
  3. select course_id from score where scores < 60 order by course_id desc;
  1. -- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
  2. select avg(scores) from score group by course_id order by avg(scores) , course_id desc;

六、汇总分析

  1. # 查询学生的总成绩并进行排名
  2. select student_id, sum(scores) from score group by student_id order by student_id;
  1. # 查询平均成绩大于60分的学生的学号和平均成绩
  2. select student_id, avg(scores) from score group by student_id having avg(scores) > 60;

七、复杂查询

  1. # 查询所有课程成绩小于60分学生的学号、姓名
  2. select a.student_id, s.student_name from score a join student s on a.student_id = s.student_id group by a.student_id having avg(a.scores) < 60;
  1. # 查询没有学全所有课的学生的学号、姓名|
  2. select a.student_id, student_name
  3. from student a
  4.     join score s on a.student_id = s.student_id
  5.     left join course c on s.course_id = c.course_id where c.course_id is null;

八、多表查询

  1. # 查询所有学生的学号、姓名、选课数、总成绩
  2. select s.student_id, s2.student_name, count(distinct s.course_id), sum(s.scores)
  3. from score s join student s2 on s.student_id = s2.student_id group by s.student_id, s2.student_name;
  1. # 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
  2. select s.student_id, s2.student_name, avg(s.scores) from score s join student s2 on s.student_id = s2.student_id
  3. group by s.student_id, s2.student_name having avg(s.scores) > 85;
  1. # 查询学生的选课情况：学号，姓名，课程号，课程名称
  2. select s.student_id, s.student_name, c.course_id, c.course_name
  3. from student s join score s2 on s.student_id = s2.student_id join course c on s2.course_id = c.course_id;
  1. # 查询出每门课程的及格人数和不及格人数
  2. select sum(case when scores >= 60 then 1 else 0 end) as '及格',
  3.        sum(case when scores < 60 then 1 else 0 end) as '不及格' from score group by course_id;
  1. # 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
  2. select
  3.     sum(case when scores<=100 and scores > 85 then 1 else 0 end) 100_85,
  4.     sum(case when scores<=85 and scores > 70 then 1 else 0 end) 85_70,
  5.     sum(case when scores<=70 and scores > 60 then 1 else 0 end) 70_60,
  6.     sum(case when scores <=60 then 1 else 0 end) 60_1,
  7.     c.course_id,
  8.     c.course_name
  9. from score s join course c on s.course_id = c.course_id group by c.course_id, c.course_name;
  1. # 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名|
  2. select s2.student_id, s2.student_name
  3. from score s join student s2 on s.student_id = s2.student_id where s.course_id='0003' and s.scores > 80;

九、多表连接

  1. # 检索"0001"课程分数小于60，按分数降序排列的学生信息
  2. select s.student_id, s2.student_name
  3. from score s join student s2 on s.student_id = s2.student_id
  4. where course_id='0001' and scores < 90 order by scores desc;
  1. # 查询不同老师所教不同课程平均分从高到低显示
  2. select c.teacher_id, c.course_id, avg(s.scores)
  3.     from score s join course c on s.course_id = c.course_id
  4.     group by c.teacher_id, c.course_id
  5.     order by avg(scores) desc;
  1. #查询课程名称为"数学"，且分数低于60的学生姓名和分数
  2. select s2.student_name, s.scores 
  3.     from score s 
  4.   join course c on s.course_id = c.course_id 
  5.   join student s2 on s.student_id = s2.student_id
  6.   where c.course_name='数学' and s.scores < 60;

十、SQL高级功能：窗口函数

  1. # 查询学生平均成绩及其名次
  2. select t.student_id, avgs, row_number() over ( order by avgs desc)
  3. from (  select student_id, avg(scores) as avgs from score group by student_id) t;
  1. # 按各科成绩进行排序，并显示排名
  2. select
  3.     course_id,
  4.     scores,
  5.     row_number() over (partition by course_id order by scores desc) as rnk
  6. from score;
  1. # 查询每门功成绩最好的前两名学生姓名
  2. select * from (
  3. select
  4.     s.course_id,
  5.     scores,
  6.     s2.student_name,
  7.     row_number() over (partition by course_id order by scores desc) as rnk
  8. from score s join student s2 on s.student_id = s2.student_id) a where a.rnk<=2;