单行函数，高级查询，数据字典，数据类型--重温Oracle2

最近在忙标项的事情，学习中断了。继续...

 

第四章 单行函数

字符函数

Upper

 

nvl(str,str2) 如果str不为null，返回str否则返回str2(如果str2也为null,也返回null)

select nvl(ename, ‘a’) from emp;

 

last_day  返回日期当月最后一天的日期

select last_day(hiredate) from emp;

 

initcap  返回首字母大写，其余字母小写的格式的字符串

select initcap(ename) from emp;

 

length(str)

substr(str)

to_char(str)

to_date(str, format)

 

 

分页查询

 

Select * from (select rownum no, e.* from emp e) where no > 5 and no <= 10;

 

 

Exists

 

Select * from t1 where exists (select null from t2 where y=x);-- x为t2的字段，y为t1的字段。

 

等同于下面语句：

 

for x in (select from t1) loop
    if (exists(select null from t2 where y = x.x))
    then
        --Output the record
    end if
end loop

对于in和exists的性能区别：

         如果子查询得出的结果集记录较少，主查询中的表较大且有索引时应该用in，反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

         其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是in，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。

另外in不处理null。

 

Select 1 from dual where null in (0,1,2,null)  --为空。

 

 

 

删除一张表重复记录（ID是自增唯一，重复记录：除ID外其他都一样）。

非常经典的一道面试题，可能存在很多数据，要求性能比较高。

 

1  abc  20
2  abc  20
3  ab   15
4  abc  20

 

delete from tbl where id not in (select min(id) from tbl group by name,age)

delete from tbl where id not in (select t1.id from tbl t1, tbl t2 where t1.id >t2.id and t1.name = t2.name and t1.age = t2.age)

 

 

第5-7章均是一些面试题，暂略

 

 

 

 

第八章 高级查询

 

随即返回5条记录

 

Select * from (select ename, job from emp order by dbms_random.value() ) where rownum <=5;

处理空值排序

 

Select * from emp order by comm. Desc nulls last(first);  --默认为first

查询跳过偶数行

 

Select ename from (select row_number() over (order by ename) rn, ename from emp) x where mod(rn,2) = 1;

连续求和

 

Select ename,sal, sum(sal) over(), sum(sal) over(order by ename) from emp;

Sum(sal) over(order by ename)指的是连续求和，是以ename排序。若有两个这样的窗口函数，以后面的为主。

 

分部门连续求和

 

Select deptno, ename, sal, sum(sal) over(partition by deptno order by ename) as s from emp;

得到当前行上一行或者下一行的数据

 

Select ename,sal, lag(sal) over (order by sal) before, lead(sal) over(order by sal) last from emp;

要求用一条SQL语句，统计每个月及上个月和下个月的总收入

 

Select month, sum(income),lag(sum(income)) over(order by sum(income)) as 上月收入,lead(sum(income)) over(order by sum(income)) as 下月收入 from table group by month;

Select add_months(trunc(sysdate, ‘y’), 12) – trunc(sysdate,’y’) from dual;

 

 

 

第九章 数据字典

 

查询某用户下的所有表

 

Select table_name from all_tables where owner=’SCOTT’;

 

查询表的所有字段

 

Select * from all_tab_columns where table_name=’EMP’;

 

查询表的索引列

 

Select * from sys.all_ind_columns where table_name=’EMP’;

 

查询表的约束

 

Select * from all_constraints where table_name=’EMP’;

 

描述数据字典视图

 

Select table_name,comments from dictionary where table_name like ‘%TABLE%’;

 

 

 

第十章 Oracle数据类型

Char,nchar,varchar2,nvarchar2,number(),date,blob(binary 二进制大对象),clob(文件大对象)

注意：

1，  由于char是以固定长度的，所以它的速度要比varchar2快得多！但程序处理起来要麻烦一点，要用trim之类的函数把两边的空格去掉；

2，  Varchar2一般适用于英文和数字，nvarchar2适用于中文和其他字符，其中n表示unicode常量，可以解决多语言字符集之间的转换问题；

3，  Number（4,2）指的是整数占2位，小数占2位(99.994可以，99.995不行，因为是四舍五入)

4， Number默认为38位。

 

Java对blob字段的操作（后续补充）

 

张文海