oracle的查询&函数&效率问题

oracle数据库分组查询时，查询条件只能写聚合函数或者是分组查询的条件，不可写其他。

select e.deptno/count(*) from emp e group by e.deptno;

null 很特殊 null 不等于null ，null 跟谁运算谁就变成null
查询过程中 null值参加任何比较 结果都为false
在in的比较时无影响。因为in：a in (1,2,3,null) 相当于 a=1 or a=2 or a=3 or a=null 就算有null也不影响其他结果输出。
然而，not in ：a not in (1,2,3,null) 相当于 a=1 and a=2 and a=3 and a=null 此时，a=null为fales 所以一个数据都查不出来 。
在做子查询时，需要先去null值（is not null）

oracle中的分页查询：

oracle通过rownum实现分页查询， rownum，rowid为伪列
rownum 表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
rowid 表中每行数据指向磁盘上的物理地址。
—–rownum 不支持 大于 （因为在查询的过程中rownum并不知道之后的数据，只知道当前行之前查询出来的数据） —————–

select * from (select t.*,rownum rm from (select * from emp order by sal desc) t)  tt 
where tt.rm <7 and tt.rm >3;    

——–不常用分页————-

select * from (select row_number() over(order by sal desc) rm , e.* from emp e) t
where t.rm<7 and t.rm >3;        

分页：
——-oracle 通过 rownum——
——-mysql limit———–
——-sqlserver top———-

oracle中的行转列可以利用case when then 来完成

select
 sum(x)  "Total",
 sum(case t.y 
      when '1980' then t.x
        end)  "1980",
    sum(case t.y 
      when '1981' then t.x
        end)  "1981",
        sum(case t.y 
      when '1982' then t.x
        end)  "1982",
        sum(case t.y 
      when '1987' then t.x
        end)  "1987"    
 from (select count(*) x ,to_char(e.hiredate,'yyyy') y from  emp  e group by  to_char(e.hiredate,'yyyy')) t

其中聚合函数可以自动去掉null值。

count聚合函数效率：

select count(*) from emp ;
select count(1) from emp ;

两者的效率是一样的。
原因：
count（）中写的值分为两种，字段名和其他。count(*)，count(1)都代表其他。
首先count(1)并不代表查询第一行，因为count（999）也可以查出数据并不会报错，就算写count（“字符串”）也可以查询出数据。
count（）中写其他值时，oracle默认查询表的主键总数。如果没有主键，查询记录总数。
count（）中写其他字段时，oracle查询的是对应字段的记录数。

exists（）效率

exists（） 函数用来判断（）里的查询语句是否有结果 有结果为true 没有则为false （与in的功能相当）：

select d.* from dept d where exists (select e.* from emp e where d.deptno=e.deptno);
select * from dept d where d.deptno in (select distinct e.deptno from emp e);

但是查询效率不同，2000年之前是exists效率高，但是数据库厂商一直在提高in的性能，现在in和exists的效率差不多，区别是：
—-左表大 （数据量多） 右表小 （数据量少） 的时候 in效率高
—-左表小 （数据量少） 右表大 （数据量多） 的时候 exists效率高
因为in是从左向右查，exists是从右向左查。

集合运算：
并集：union 、union all ( union去重复）
交集： intersect
差集：minus

只要 保证 查询 列的数量 列的类型一致 就可以做集合操作
select e.empno ,e.ename from emp e
union
select d.deptno,d.dname from dept d;