sql数据库优化书籍&第一章之单表查询

1.判断空值函数（coalesce 与 nvl）

coalesce

eg: select coalesce(c1,c2,c3,c4,c5,c6) as c from v;

eg: select fname,fbirhday,fregday,coalesce(fbirhday,fregday,'2008-08-08') as imporday ; 

nvl

eg: select nvl(nvl(nvl(nvl(nvl(c1,c2),c3),c4),c5),c6) as c from v;

结果：calesce支持多个参数，很方便的返回第一个不为空的值，nvl就需要嵌套很多层；

 

2.在where子句中引用取别名的列

eg:select name as 姓名，sex as 性别,sal as 工资 from emp where 工资>2000;

eg:select * from (select name as 姓名，sex as 性别) where 性别=‘男’;

结果：别名是在select之后才会有效；

 

3.sql生成sql,利用 || 拼接列

eg:select 'update A a set a.x='||b.x||' where 条件' from B b where ...；

 

4.随机取数据

eg:select empno,ename

from(select empno,ename from emp order by dbms_random.value())

where rownum<=3;

eg:select empno,ename from emp where rewnum <= 3 order by dbms_random.value;

结果：先随机排序在取数据；