sql将所有课的最⾼分、最低分、平均分赋值给变量并输出。
_SQL第六关：求职⾯试...
学习内容
1. 简单查询
2. 汇总分析
3. 复杂查询
4. 多表查询
5. 如何提⾼SQL查询效率
简单查询
创建学校数据库的表
查找学⽣
查询姓‘猴’的学⽣名单
查询姓名中最后⼀个字是‘猴’的学⽣名单
查询姓名中带‘猴’的学⽣名单
lect *
from student
where 姓名 like '猴%';
lect *
from student
where 姓名 like '%猴';
lect *
from student
where 姓名 like '%猴%';
查询姓‘孟’⽼师的个数
lect count(教师姓名)
喜气
from teacher
where 教师姓名 like '孟%';
汇总分析
汇总
查询课程编号为‘0002’的总成绩适合孕妇的养生茶
lect sum(成绩)
from score
where 课程号='0002';
查询选了课程的⼈数
新食品/*
查询有多少个⼈选了课程
*/
lect count(distinct 学号)
from score
分组
查询各科成绩最⾼和最低的分
lect 课程号,max(成绩),min(成绩)
from score
group by 课程号;
和歌山大学
查询每门
每门课程被选修的学⽣⼈数
lect 课程号, count(distinct 学号) as 选课⼈数from score
group by 课程号;
查询男⽣、⼥⽣⼈数
lect 性别,count(distinct 学号) as ⼈数
from student
group by 性别;
分组结果的条件
查询平均成绩⼤于60分
平均成绩⼤于60分学⽣的学号和平均成绩
lect distinct 学号, avg(成绩) as 平均成绩
from score
group by 学号
having avg(成绩)> 60;
查询⾄少选修两门课程的学⽣学号
lect 学号,count(课程号) as 选课数
from score
group by 学号
having count(课程号)>1;
同名同姓学⽣名单并统计同名⼈数
查询同名同姓
lect 姓名, count(姓名) as ⼈数
from student
group by 姓名
having count(姓名)>1;
排序
查询不及格的课程并按课程号从⼤到⼩排列
lect 课程号，成绩
from score
where 成绩 < 60
order by 课程号 DESC;
查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
lect 课程号,avg(成绩) as 平均成绩
from score
group by 课程号
ORDER BY 平均成绩 ASC, 课程号 DESC
检索课程编号为‘0004’且分数⼩于60的学⽣学号，结果按分数降序排列
lect 学号
from score
where 课程号='0004' and 成绩 < 60
order by 成绩 desc;
查询两门以上不及格课程的同学的学号，以及不及格课程的平均成绩
lect 学号,avg(成绩) as 平均成绩
from score
where 成绩<60
group by 学号
having COUNT(学号)>2;
复杂查询
所有课程成绩⼩于60分的学⽣的学号、姓名
查询所有课程成绩⼩于60分
/*
所有成绩⼩于60 翻译成⼤⽩话就是学⽣的所有课程的最⾼成绩也是不及格
*/
lect 姓名，学号
from student
where 学号 in ( lect 学号
from score
group by 学号
having max(成绩) < 60
)
没有学全所有课的学⽣的学号、姓名
查询没有学全所有课
lect 姓名，学号
from student
where 学号 in ( lect 学号
from score
group by 学号
having count(课程号) < (lect distinct count(课程号) from score)
);
-- 也可以使⽤多表查询
lect a.姓名,a.学号
from student as a inner join score as b
on a.学号=b.学号
group by a.学号
having count(b.课程号) < (lect count( distinct 课程号) from cour);
返回的结果是学⽣有上课但没有选修完所有课
lect a.姓名,a.学号
from student as a left join score as b
邴怎么读
on a.学号=b.学号
group by a.学号
having count(b.课程号) < (lect count( distinct 课程号) from cour);
left join 返回的结果会包含完全没有上课的学⽣
网络技术应用只选修了两门课程的全部学⽣的学号和姓名
查询出只选修了两门课程
lect 姓名，学号
from student
where 学号 in ( lect 学号
from score
陶教授group by 学号
having count(课程号)=2)
-- 也可以使⽤多表查询
lect a.姓名,a.学号
from student as a inner join score as b
on a.学号=b.学号
group by a.学号
having count(b.课程号) =2
1990年出⽣的学⽣名单
查询1990年出⽣
lect *
from student
where YEAR(出⽣⽇期)='1990'
学⽣表中出⽣⽇期列的类型是datetime
Top N 问题
⼯作中会经常遇到的业务问题：如何找到每个类别下⽤户最喜欢的产品是哪个？如何找到每个类别下⽤户点击最多的5个商品是什么？这类问题其实就是最常见的：分组取每组最⼤值、最⼩值，每组最⼤的N条（top N）
分组取每组最⼤值
example：按课程号分组取成绩最⼤值所在⾏的数据
分组（goup by） 和汇总函数得到每个组⾥的⼀个值（max,min,avg,sum)，但⽆法得到成绩最⼤值所在⾏的数据可以使⽤关联⼦查询来实现
lect *
from score as a
where 成绩=(
lect max(成绩)
from score as b
where b.课程号=a.课程号
group by 课程号);
分组取每组最⼩值
按课程号分组取成绩最⼩值所在⾏的数据
lect *
from score as a
where 成绩 in (
lect min(成绩)
from score as b
where b.课程号=a.课程号
group by 课程号);
每组最⼤的N N条记录
查询各科成绩前两名的记录
第⼀步：查出有哪些组
按课程号分组，查询出有哪些组，对应这个问题就是有哪些课程号
lect max(成绩)
from score
group by 课程号
从这⾥我们可以得知我们有四个课程号，分别是0001，0002，0003，0005
蒙古大草原第⼆步：如何查询⼀科前两名的记录
使⽤order by⼦句使成绩按降序排列（desc），然后⽤limit⼦句返回 topN（对应这个问题返回的是成绩前两名）
lect *
from score
where 课程号=‘0001‘
order by 成绩 desc
limit 2
如此类推可以查出每门课的前两名记录
第三步：使⽤union all 将每组选出的数据合并到⼀起
