聚合函数:
名称 作用 语法
avg 平均值 AVG(表达式)
sum 求和 SUM(表达式)
min\max 最小值、最大值 MIN(表达式)、MAX(表达式)
count 数据统计 COUNT(不为空的字段)
group by 分组
having 分组后的筛选
order by 一个或多个字段的 排序 默认升序排列 ASC,
DESC降序
null是最大值
SQL语句执行顺序:
select5 –from1–where2–group by3–having4–order by 6–
===============================================================
SELECT MAX(SAL),min(sal),
sum(sal),avg(sal),
count(1)
FROM EMP2;
SELECT e.deptno,max(d.dname),MAX(e.SAL),min(e.sal),
sum(e.sal),avg(e.sal),
count(1)
FROM EMP2 e ,dept d
where e.deptno = d.deptno
group by e.deptno
–having
select * from (
SELECT e.deptno,MAX(e.SAL) max_sal,min(e.sal) min_sal,
sum(e.sal) sum_sal,avg(e.sal) avg_sal,
count(1) as total
FROM EMP2 e
group by e.deptno ) t
where avg_sal >3000;
SELECT e.deptno,MAX(e.SAL) max_sal,min(e.sal) min_sal,
sum(e.sal) sum_sal,avg(e.sal) avg_sal,
count(1) as total
FROM EMP2 e
group by e.deptno having avg(e.sal)>3000
–Where可以筛选的条件,不要写在having后进行筛选
select deptno,sum(sal) from emp2 e
group by deptno having deptno in(20,30);
select deptno,sum(sal) from emp2 e
where deptno in(20,30)
group by deptno ;
===========================================================&#