SQL查询的艺术学习笔记--聚合函数的应用

use seldata
select * from sys.tables
--聚合函数
--sum() 函数（求和）
--语法：select sum(column_name) from table_name
select SUM(sal) from teacher where sex='男'
--查询男教师工资总和
select * from teacher order by sex
select SUM(sal) from teacher where age>40
--当列中存在null值时，系统会忽略该值


--count()函数（计数）
--注：count(*)括号中的星号表示所有值，NULL值计入计算。
--    count(column)则是该列行数，NULL值不计入计算。
select COUNT(*) as totalitem from teacher
--count(*)使用
select * from teacher
--count()使用
select COUNT(tno) as totaltno,COUNT(tname) as totaltname,
COUNT(sal) as totalsal from teacher
--count()多列的使用
select COUNT(tno) as totaltno,COUNT(tname) as totaltname,
COUNT(sal) as totalsal,COUNT(cast(tno as CHAR)+tname) as totaltname,
count(cast(sal as char(5))+tname) as tsalname from teacher where sex='女'
--多列用连接符进行查询
--计数查询条件查询
select COUNT(*) as Wteacher from teacher where sex='女'


--MAX()/MIN()取最大值和最小值函数
--MAX(column)/MIN(column)的列值可以为字符，日期 数字等类型
select MAX(age) as MAXAGE from teacher
use seldata
--select tname,dname,sex,MAX(age) from teacher
--select tname,dname,tsex,sal,age from teacher where age=max(age)
--以上语句为错误的，正确是采用子查询返回最大值
select * from teacher where age=(select MAX(age)from teacher)
select a.* from teacher as a where a.age=(select MAX(age) from teacher)
--max()用于日期和字符型
select MAX(tname) as maxname from teacher
select convert(char(11),MAX(ctest),20) as bigtime,convert(char(11),MIN(ctest),20) as littletime from course
--通过时间转换函数对聚合函数进行操作


--avg()均值函数(对整列相加后再除以非NULL列数，用于处理数值型的列。null值行不参与运算
--语法：select avg(column) from table_name
select AVG(age)  as avgage from teacher 
select AVG(sal) as avg_age1,SUM(sal)/COUNT(*) as avgage2,SUM(sal)/COUNT(sal) as avgage3 
from teacher
--注意：avg(sal) 和sum(sal)/count(sal)是相等的，因为count(sal)不会处理null行，而sum(sal)/count(*)
--需要处理NULL值，所得均值小于前面两种方式。
select AVG(age) as computer from teacher where dname='计算机'
--avg()函数同样需要查均完整信息需要结合子查询来进行操作。
select * from teacher where age>=(select AVG(age) from teacher )and sex='女' order by age 
--聚合函数的重复值处理 语法：select 函数(all/distinct) column) from table name
select COUNT(all sal) as totalsal,COUNT(distinct sal) as totalsal2  from teacher
select COUNT(distinct sal) as totalsal2 from teacher


--扩展写法，查询表中的重复值，并列出来。
select * from teacher a where a.sal in (select sal from teacher group by sal having COUNT(*)>1)
order by sal
--查询表中的重复次数
select COUNT(sal) as '重复次数' ,sal from  teacher a
group by sal having COUNT(sal)>1 order by sal desc


--聚合函数的组合使用
select  COUNT(*) as '参与统计数量',
        max(sal) as '最高工资',
        min(sal) as '最低工资',
        sum(sal) as '工资合计',
        SUM(sal)/COUNT(sal) as '平均工资',
        avg(sal) as '还是平均工资'
        from teacher
--组合查询中，分组组合查询   
--语法：select coloum1 count/avg/max/sum(column) from table group by column1 
--注意: group by coloumn1必须为前面的column1
select AVG(sal) from teacher where sex='男'    
select AVG(sal) from teacher where sex='女'
select AVG(sal) from teacher
select ((select AVG(sal) from teacher where sex='男') +   
(select AVG(sal) from teacher where sex='女'))/2
--以上为求平均值的一些语句，而我们同时取求男 女分别平均值？
select sex+'教师' as '教师' ,AVG(sal) as '平均工资' from teacher
group by sex --sex 为选择的分组列
--group by 根据多列组合行（当组合为多组，对满足多个条件的每一条值进行分组处理。统计函数同时对这些分类进行统计。
--(满足条件有多少类就会出现多少值的分类统计)
select dname,sex,COUNT(*) as '总人数'  from teacher group by dname,sex order by sex
--group by 扩展查询结果：rollup cube
select dname,sex,COUNT(*) as '总人数'  from teacher group by dname,sex with rollup 
order by dname
select dname,sex,COUNT(*) as '总人数'  from teacher group by sex,dname with rollup 
order by dname
--使用rollup
select dname,sex,COUNT(*) as '总人数' from teacher group by dname, sex with cube
order by dname
select dname,sex,COUNT(*) as '总人数' from teacher group by dname, sex 
order by dname
--group by 对null值处理
select sal,COUNT(*) from teacher group by sal order by sal
--对于null值，也同样进行相同的分组处理。
--组合查询：having字句
--语法：select column,sum/max/count/avg(column) from table
--      group by column having sum/max/count/avg(column) condition<条件表达式> value 
select dname,COUNT(*) as '统计人数'
from teacher
group by dname
having count(*)>=2
--having 子 句和where 子句
--1.having在有group by 语句时， 仅作用接受于group by子句创建的分组
--2.having在指定where子句而没有group by 子句时，作用接受于where条件输出看作一个组处理
--3.having在没有where，group by子句时，作用接受于from语句输入。
select dname,COUNT(sex) as '有女教师系统计'
from teacher where sex='女'
group by dname  


select dname,COUNT(sex) as '有女教师系统计'
from  teacher
group by dname
having sex='女'
--消息 8121，级别 16，状态 1，第 4 行
--HAVING 子句中的列 'teacher.sex' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
--按下面这样写就正确。这样可以看出 where 和having区别，having是作用于组列内容，where是作用于组列。
select dname,COUNT(sex) as '有女教师系统计' from teacher
group by dname 
having dname='计算机'


--having和where联合使用 
select dname,COUNT(sex) as '女性人数统计' from teacher
where sex='女'
group by dname
having count(sex)>=2
order by COUNT(sex)
--having子句在没有group by时单独使用
select COUNT(sex) as tmteacher from teacher
where sex='女'
having COUNT(sex)>=2
order by COUNT(sex)