Posted on 2010-06-30 23:38
断点 阅读(290)
评论(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;