case oracel 子查询_SQL查询总结

一、简单查询

查询姓孟的教师人数

二、汇总分析

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

查询选了课程的人数

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

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

查询平均成绩大于60分学生的学号和平均成绩

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

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

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

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

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

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

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

三、复杂查询

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

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

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

1990年出生的学生名单

关联子查询解决topN问题

按课程号分组取成绩最小值所在行的数据

但这里显然出现了一个错误：课程0003的最低成绩应该是80才对，为什么结果是100呢？
在接单查询一章我们讲过，由于当发现“大的数据”反而排在“小的数据”的前面，这时就要注意该列数据的类型是以字符串形式存储的。观察成绩列存储的数据类型：

成绩列以定长字符串存储，改为float类型

结果正确

四、多表查询

查询不是近视眼的学生（多表联结）

查询不是近视眼的学生（子查询）

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

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

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

查询出每门课程的及格人数和不及格人数（case表达式）

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

查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名（多表联结）

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

行列互换问题，将表1变成表2

表1

表2

步骤1:使用常量列输出目标表的结构

select 学号,'课程号0001','课程号0002','课程号0003'
from score;

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

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;

步骤3:分组，并使用最大值函数max取出上图每个方块里的最大值

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 学号;

将表进行行列互换

五、如何提高SQL查询的效率？

1. select子句中尽量避免使用* （增加运行时间，多表联结中造成更大成本开销）

2. where子句比较符号左侧避免函数 （全表扫描，增加运行时间）

3. 尽量避免使用in和not in （全表扫描，增加运行时间）

4. 尽量避免使用or （union代替）（全表扫描，增加运行时间）

5.使用limit子句限制返回的数据行数