Oracle知识点总结—多表查询与统计函数_oracle 关联子表 统计信息

1、多表查询
先看表有多少数据（经验）
select count(*) from emp;//14
select count(*) from dept;//4

select * from emp,dept;//笛卡尔积 ,56

多表查询的性能是很差的，尽量避免多表查询
select * from emp,dept where emp.deptno = dept.deptno;

查询雇员的编号、姓名、职位、部门名称、位置
select e.empno,e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

查询雇员的姓名、职位、领导名称
select e1.ename,e1.job,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
左连接查询
select e1.ename,e1.job,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;

查询雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置
select e1.empno,e1.ename,e1.sal,e1.job,e2.ename,d.dname,d.loc from emp e1,emp e2, dept d where e1.empno = e2.empno and e2.deptno = d.deptno;

查询雇员的姓名、工资、部门名称、工资等级、以及领导的姓名、工资和工资等级
SELECT e.ename,e.sal,d.dname, DECODE(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),m.ename,m.sal,
DECODE(ms.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资') FROM emp e,dept d,salgrade s,emp m,salgrade ms
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal AND e.mgr=m.empno AND m.sal BETWEEN ms.losal AND ms.hisal;

左右连接
select * from emp e,dept d where e.deptno = d.deptno;
(+)用于左右连接的更改。这种符号有两种情况
(+)=:放在了等号的左边，表示的是右连接
=(+):放在了等号的右边，表示的是左连接
select * from emp e,dept d where e.deptno (+)= d.deptno;

SQL:1999语法
交叉连接：
用于产生笛卡尔积（CROSS JOIN）
select * from emp cross join dept;
自然连接
自动找到匹配的关联字段，消除掉笛卡尔积(NATURAL JOIN)
select * from emp natural join dept;
JOIN...USING()子句
用户自己指定一个消除笛卡尔积的关联字段
select * from emp join dept using(deptno);
JOIN...ON()
用户自己指定一个可以消除笛卡尔积的关联条件
select * from emp join dept on(emp.deptno = dept.deptno);
连接方向的改变
左(外)连接：LEFT [OUTER] JOIN...ON
select * from emp left outer join dept on(emp.deptno = dept.deptno);
右(外)连接：RIGHT [OUTER] JOIN...ON
select * from emp right outer join dept on(emp.deptno = dept.deptno);
全(外)连接：FULL [OUTER] JOIN...ON
select * from emp full outer join dept on(emp.deptno = dept.deptno);

多表查询性能不高，尤其在大数据的情况下，开发中能不用就不用

2、统计函数
COUNT():查询表中的数据
AVG(): 求出平均值
SUM():求和
MAX():求出最大值
MIN():求出最小值
范例：
select count(empno),sum(sal),avg(sal),max(sal),min(sal) from emp;

关于COUNT函数：COUNT函数的功能主要是进行数据的统计，但是在进行数据统计的时候，如果一张表中没有统计记录，COUNT也会返回数据，只是这个数据时"0"
select count(*) from bonus;
如果使用其它函数，则有可能返回null，但是count永远否会返回一个具体的数字，开发中会用到

分组统计
GROUP BY子句要放在order by子句的前面
order by 分组字段1，分组字段1...
按照部门分组，求出每个部门的人数和平均工资
select deptno,count(*),avg(sal) from emp group by deptno;
按照职位分组，求出最高工资和最低工资
select job,max(sal),min(sal) from emp group by job;
统计出领取佣金和不领取佣金的雇员的人数及平均工资
select nvl(comm,0),count(*),avg(sal) from emp group by nvl(comm,0);
分组限制：
1、分组函数可以在没有分组的时候单独使用，可是不能出现其他的查询字段
2、如果现在要进行分组，则在select子句之后，只能出现分组的字段和统计函数，其他的字段不能出现
3、分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段
错误：select max(avg(sal)),min(sal) from emp group by job;
正确：select max(avg(sal)) from emp group by job;
查询每个部门的名称、部门的人数、平均工资
select d.dname,count(e.empno),nvl(avg(e.sal),0) from emp e,dept d where e.deptno(+) =d.deptno group by d.dname;
查询每个部门的编号、名称、位置、部门的人数、平均工资
select d.deptno,d.dname,d.loc,count(e.empno),nvl(avg(e.sal),0) from emp e,dept d where e.deptno(+) =d.deptno group by d.dname,d.loc,d.deptno;
要求统计每个部门的详细信息,并且要求这些部门的平均工资高于2000
select d.deptno,d.dname,d.loc,count(e.empno),nvl(avg(e.sal),0) from emp e,dept d where e.deptno(+) =d.deptno group by d.dname,d.loc,d.deptno having nvl(avg(e.sal),0)>=2000;
在where子句中不能使用统计函数，对分组后的数据过滤，使用having子句

WHERE与HAVING的区别
WHERE:是在执行ORDER BY 操作之前进行的过滤，表示从全部数据之中筛选出部分的数据，在WHERE之中不能使用统计函数
HAVING：是在GROUP BY分组之后的再次过滤，可以再HAVING子句中使用统计函数