文科生的SQL初体验之聚合函数与开窗函数

聚合函数

聚合函数：count、avg、sum、max、min

1. count函数

select count(*)from 资料表

查询表中cid为1的有多少列

select count(*)as count1 from 资料表
where cid=1

select count(sphone)as count1 from 资料表
where cid=1

此时表中sphone为null值的是不会算入的

2. max函数和min函数
   求所有分数最大值

select Max(scorecalue) from scoreinfo

求学生id为2的最低分

select min(scorevalue) from scoreinfo
where subid=2

如果要研究语文科目的平均分，我们知道的是分数表和科目表是分离的，因此需要外键连接

select AVG(scorevlaue）
from subjectinfo
inner jion scoreinfo on subid=subjectinfo.sld
where stitle=语文

原则：先连表，再筛选
需要注意的是：
1.聚合函数，一般是对数字类型的列进行操作
2.一条查询中可以同时写多个聚合函数，但是不能与普通列混写

开窗函数over()

select scoreinfo.*,AVG(scorevalue) over()
from scoreinfo
where subid=1

原理：
因为聚合函数是把原先一列列的数据进行合并，合并成一行后进行计算而得出的，但前面的scoreinfo.*要求显示各列，与后面聚合函数合并成一行冲突，加入over函数可还原成原先一行行的数据

分组

select count(*)
from studentinfo
group by sgender

--求每个科目的平均分
select subid,AVG(scorevalue) from scoreinfo
group by subid

sgender,cid属性一样才能分成一组

需要注意的是select后面两个必须是一样的
否则就会报错

练习

--求每个班的男女生人数
--结果集中，分组依据值相同的实例在一组，在结果列中只能出现分组依据列和聚列
select sgender,count(*)
from studentinfo
group by sgender,cid
--统计学生编号大于2的各班级的各性别的学生人数
select cid,sgender count(*)
from studentinfo
where sid>2
group by cid,sgender
--统计学生编号大于2的编号为3的班级的个性别的学生人数
select sgender,count(*)
from studentinfo
where sid>2 and cid=3
group by sgender
--统计学生编号大于2的各班级的各性别的学生人数大于3的信息
select cid,sgender,count(*)
from studentinfo
where sid>2
group by cid,sgender having count(*)>3

1.运用逻辑编写就好
2.分组后的筛选用having 连接

总结

顺序

select distinct top n*
from t1 join t2 on ...join t3 on ...
--连接成一个数据集
where ...
group by ... having...
order by ...