我已经记不起来，有多久没更新文章了。
5月中旬我还在上班，中旬以后一系列发生的事情，真的远远超出了可承受范围，只能硬着头皮面对！
我是谁，我应该是谁，又能怎样，只能向前·····
【资料图】
# 自链接 SELECT c.name,t.name FROM course c,teacher t WHERE c.teacher_id=t.id # 内连接 SELECT course.name,teacher.name FROM course INNER JOIN teacher ON course.teacher_id=teacher.id结果：
2、查询学习课程"数据结构"比课程"java语言"成绩低的学生的学号；# 内连接 SELECT shuju.student_id FROM (SELECT score.course_id, score.student_id, score.mark FROM score INNER JOIN course ON score.course_id=course.id WHERE course.name="数据结构") AS shuju INNER JOIN (SELECT score.course_id, score.student_id, score.mark FROM score INNER JOIN course ON score.course_id=course.id WHERE course.name="java") AS java ON shuju.student_id=java.student_id WHERE shuju.mark结果：
3、查询平均成绩大于65分的同学的id和平均成绩(保留两位小数)SELECT score.student_id, round(AVG(score.mark),2) AS avgScore FROM score GROUP BY score.student_id HAVING avgScore>65结果：
4、查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数)SELECT student.`name`, ROUND(AVG(score.mark),2) AS avgScore FROM score INNER JOIN student ON student.id=score.student_id GROUP BY score.student_id HAVING avgScore>65结果：
5、查询所有同学的姓名、选课数、总成绩SELECT student.name AS "名字", COUNT(score.course_id) AS "选课数",SUM(score.mark) AS "总成绩"FROM scoreINNER JOIN studentON student.id=score.student_idGROUP BY student_id结果：
6、查询没学过"大牛"老师课的同学的姓名select student.name from student where id not in(select student_id from score where course_id in(select course.id from course inner join teacher on course.teacher_id = teacher.id where teacher.name="大牛"))结果：
7、查询学过"大牛"老师所教的全部课程的同学的姓名select student.name from student where id in(select student_id from score where course_id in(3,3))结果：
8、查询所有课程成绩小于60分的同学的姓名select student.name from student inner join score on student.id = score.student_id where score.mark<60 group by score.student_id结果：
9、查询选修了全部课程的学生姓名select student.name from student where id in (select score.student_id from score group by score.student_id having count(1)=(select count(1) from course))结果：
10、查询至少有一门课程与"小草"同学所学课程相同的同学姓名SELECT student.nameFROM studentWHERE id IN (SELECT student_id FROM score WHERE course_id IN (SELECT course_id FROM score WHERE student_id=5)) AND student.name!="小草"结果：
11、查询至少有一门课程和"小草"同学所学课程不相同的同学姓名select student.name from student where id in (select student_id from score where course_id not in (select course_id from score where student_id=5)) and student.name!="小草"结果：
12、查询各科成绩最高和最低的分：以如下形式显示：课程id，最高分，最低分select course_id as "课程id",max(mark) as "最高分",min(mark) as "最低分"from score group by course_id结果：
13、查询只选修了一门课程的学生的学号和姓名# 感觉有点low，但是能查出来 select student.id as "学号",student.name as "姓名"from student inner join score on student.id = score.student_id where student.id=(select student_id from score group by student_id having count(course_id)=1)# 这个好一些 select student.id as "学号",student.name as "姓名"from student inner join score on student.id = score.student_id group by student_id having count(course_id)=1结果：
14、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程id降序排列select course.id as "课程id",course.name AS "课程名称",avg(mark) as "平均成绩" from course inner JOIN score on course.id = score.course_id group by course_id order by avg(mark) ,"平均成绩",course_id desc结果：
15、按平均成绩倒序显示所有学生的"数据库原理"、“java语言”、"C语言"三门的课程成绩，按如下形式显示： 学生id、数据库原理、java语言、C语言、课程数、平均分；(高级应用较难)
select sc.student_id as "学生id", (select mark from score inner join course on course.id=score.course_id where course.name="数据库原理" and score.student_id=sc.student_id) as "数据库原理", (select mark from score inner join course on course.id=score.course_id where course.name="java" and score.student_id=sc.student_id) as "java", (select mark from score inner join course on course.id=score.course_id where course.name="C语言" and score.student_id=sc.student_id) as "C语言", count(course_id) as "课程数", round(avg(sc.mark),2) as "平均分" from score as sc group by sc.student_id order by avg(sc.mark) desc结果：
写在最后整个数据库这部分的复习，早在近一个月前就开始了。
在做了两道题后，就遇到了各种事情，就被搁置了，差点被遗忘了。。。
今天有时间，接着把学习的感觉续上，总体下来，算是初步复习了下sql的一些常用查询操作，就一个测试仔来说，我个人感觉这些都能写正确写出来，真的很厉害，我也是用了近6小时呢。
不管遇到了什么难事，学习、跑步都不能停（我又胖了5斤，好扎心）.....
明天继续我的5公里，加油！
关键词：
