select a.stuNo from score a,score b where a.cNo='1'and b.cNo='2' and a.stuNo=b.stuNo and a.score>b.score;
这个比较简单，主要就是从两个表里读取数据，通过学号相等来进行查找。
查询平均成绩大于60分的同学的学号和平均成绩
select stuNo,AVG(score) from score group by stuNo having avg(score)>60 ;
首先使用AVG这个函数来取平均值，然后使用group by进行分组
查询所有同学的学号、姓名、选课数、总成绩
select a.stuNo,a.stuName,count(cNo),sum(score) from student a,score b where a.stuNo=b.stuNo group by a.stuNo,a.stuName ;
和上面那个差不多，主要使用count进计数和sum进行求和
查询姓“孙”的老师的个数
SELECT count(teaName) ,'孙'as 'Name'from teacher WHERE teaName like '孙%'
主要使用like相似查询，并且使用%进行适配，以及使用as来显示
查询没学过“孙一”老师课的同学的学号、姓名
select stuName from student where stuNo not in (select stuNo from score where cNo in(select a.cNo from course a,teacher b where b.teaNo=a.cTeacher and b.teaName ='孙一'))
方法一：
SELECT a.stuNo,a.stuName from student a,score b ,score c WHERE a.stuNo=b.stuNo and a.stuNo=c.stuNo and b.cNo='2'and c.cNo='1'and b.score < c.score;
方法二：
select stuNo,stuName from student where stuNo in (select a.stuNo from score a,score b where a.cNo='1' and b.cNo='2' and a.stuNo=b.stuNo and a.score>b.score)
第一个方法和第一个差不多。就是多了进行成绩的比较
第二个方法就是换了个写法来进行操作
查询没有学全所有课的同学的学号、姓名
SELECT a.stuNO,a.stuName FROM student a, score b WHERE a.stuNo=b.stuNo group by b.stuNo having count(b.cNo)<(select count(cNo) from course)
就是学生选的课的总数和课程总数进行比较，
查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名
select distinct a.stuNo,stuName from student a,score b where a.stuNo=b.stuNo and cNo in (select cNo from score where stuNo='1')
先把学号一的学生选的课找出来，然后进行查找
查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cNo,max(score) as 最高分,min(score) as 最低分 from score group by cNo
删除“2”同学的“1”课程的成绩
delete from score where stuNo='2' and cNo='1'
查询和" 01 "号的同学学习的课程完全相同的其他同学的信
SELECT * from student WHERE stuNo in (SELECT stuNo FROM score WHERE cNo in(SELECT distinct cNo FROM score WHERE stuNo='01')and stuNo<>'01'GROUP BY stuNo having count(cNo)>=4);
查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.stuNo,a.stuName,b.平均成绩 FROM student a right join (select stuNo,AVG(score)平均成绩 from score where score<60 group by stuNo having COUNT(score)>=2)b on a.stuNo=b.stuNo;
查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID ，课程 name ，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select distinct A.cNo,cName,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 from score A
left join Course on A.cNo=course.cNo
left join (select cNo,MAX(score)最高分,MIN(score)最低分,AVG(score)平均分 from score group by cNo)B on A.cNo=B.cNo
left join (select cNo,(((sum(case when score>=60 then 1 else 0 end)*1.00)/COUNT(*))*100)及格率 from score group by cNo)C on A.cNo=C.cNo
left join (select cNo,(((sum(case when score >=70 and score<80 then 1 else 0 end)*1.00)/COUNT(*))*100)中等率 from score group by cNo)D on A.cNo=D.cNo
left join (select cNo,(((sum(case when score >=80 and score<90 then 1 else 0 end)*1.00)/COUNT(*))*100)优良率 from score group by cNo)E on A.cNo=E.cNo
left join (select cNo,(((sum(case when score >=90 then 1 else 0 end)*1.00)/COUNT(*))*100)优秀率
from score group by cNo)F on A.cNo=F.cNo
按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺(不保留)
保留
select *,RANK()over(order by score desc)排名 from score;
不保留
select *,DENSE_RANK()over(order by score desc)排名 from score
查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select *,RANK()over(order by 总成绩 desc)排名 from(
select stuNo,SUM(score)总成绩 from score group by stuNo)A
查询各科成绩前三名的记录
select * from(select *,rank()over (partition by stuNo order by score desc)A from score)B where B.A<=3
select * from score a where (select COUNT(*) from score where cNo=a.cNo and score>a.score)<3 order by a.cNo,a.score desc
如何提高SQL查找效率
