sqlserver中聚合函数

/*
聚合函数
*/
SELECT COUNT(*),
AVG(stuAge),
SUM(stuAge) / COUNT(stuAge),
SUM(stuAge),
MAX(stuAge),
MIN(stuAge) 
FROM 
student
 
SELECT * FROM student
/*
需要注意的
NUll不计算在内，如下示例
*/
SELECT COUNT(schoolName),COUNT(hobby), COUNT(*) FROM student
/*
分组和聚合函数同时使用，看下面示例
要显示其他列，必须使用那个列进行分组GROUP BY
*/
SELECT groupId, stuAddress, AVG(stuAge) 
FROM 
student
GROUP BY
groupId,stuAddress
--
SELECT * FROM student
--
SELECT groupId, AVG(stuAge) 
FROM 
student
GROUP BY
groupId
HAVING AVG(stuAge) >=25
/*
where 和 having 的区别
where 对表中的原始数据进行的筛选
having 对分组后使用聚合函数计算后的数据进行的筛选，与GROUP BY结合使用
*/
SELECT groupId, AVG(stuAge) 
FROM 
student
WHERE 
stuAge > 20
GROUP BY
groupId
HAVING 
AVG(stuAge) >=25

/*
in可以用or代替
*/
--使用or关键字
SELECT * FROM student WHERE stuAge = 18 OR stuAge = 27 OR stuAge = 26
--使用in关键字
SELECT * FROM student WHERE stuAge IN (18, 27, 26)
--使用BETWEEN AND 关键字
SELECT * FROM student WHERE stuAge BETWEEN 18 AND 27
--
SELECT * FROM student WHERE stuAge >= 18 AND stuAge <= 27
/*
如果要把某列显示在使用聚合函数的列
中，那么必须对他进行GROUP BY分组
*/
SELECT * FROM student WHERE NOT stuAge = 18
SELECT * FROM student WHERE stuAge <> 18
SELECT * FROM student WHERE stuAge != 18
SELECT * FROM student WHERE NOT (stuAge = 18)
SELECT * FROM student WHERE (NOT stuAge = 18)
SELECT * FROM student WHERE (stuAge <> 18)
SELECT * FROM student WHERE (stuAge != 18)