Posted on 2010-06-30 23:38
斷點 閱讀(303)
評論(0) 編輯 收藏 所屬分類:
Oracle DBA
select chr(65) from dual;
select ascii('A') from dual; --求編碼
select ename from emp where lower(ename) like '_a%' -- upper大寫
select round(23.652,2) from dual; -- 四舍五入 23.65,round(23.652)為24
select substr(ename,1,3) from emp; --從第一個開始，總接取3個。
select ename,sal,deptno from emp where length(sal)>3;
select to_char(sal,'$99,999.9999') from emp; --'L00000.0000'千位不夠補0
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
select ename,hiredate from emp where hiredate>to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal>to_number('$1,250.00','$9,999.99');
select ename,sal*12 + nvl(comm,0) from emp; --對空值處理
組函數：
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
select count(*) from emp; -- *求出總記錄數, count(comm)求出該列不為空的值。
select deptno,job,max(sal) from emp group by deptno,job; --按照條件組合分組
select ename from emp where sal =(select max(sal) from emp); --子查詢
update Web_Bas_Edr_Rsn set c_rsn_txt=REPLACE(c_rsn_txt,'天津','北京') where c_rsn_txt like '%天津%' --批量替換
-- having對分組進行限制，where對單行限制
select avg(sal) from emp where sal>1000 group by deptno having avg(sal) >1500 order by avg(sal) desc;