Oracle 查询

scott用户下emp表内嵌式图查询

SELECT * FROM EMP A JOIN(SELECT DEPTNO, AVG(SAL) MYSAL FROM EMP GROUP BY DEPTNO) B ON A.DEPTNO=B.DEPTNO WHERE A.SAL>B.MYSAL;

SELECT * FROM EMP A ,(SELECT DEPTNO, AVG(SAL) MYSAL FROM EMP GROUP BY DEPTNO) B where A.DEPTNO=B.DEPTNO and A.SAL>B.MYSAL;

分页

select * from (select a1.*, rownum rn from (select * from emp) a1 where rownum<=10) a2 where rn>=6;

创建一张表，用以后的表结构和数据，可以只取部分字段。

create table mytable(id,name,sal,job,deptno) as select empno,ename, sal, job, deptno from emp;

取得两个结果集的并集，使用union会自动去掉重复行。

select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER';

不去重 union all

select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER';

取交集 intersect

select ename, sal, job from emp where sal>2500 intersect select ename, sal, job from emp where job='MANAGER';

差集 minus

select ename, sal, job from emp where sal>2500 minus select ename, sal, job from emp where job='MANAGER';

函数

大写转小写 lower （）

select lower(ename) from emp;

小写转大写 upper()

select upper(ename) from emp;

返回字符串的长度length（）

select * from emp where length(ename)=5;

取得字符串的子串subetr（）

select substr(ename,1,3) from emp; // 从第一个开始取，取三个

首字母大些其他小写

select upper(substr(ename,1,1)) from emp;
select lower(substr(ename,2,length(ename)-1)) from emp;
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

替换函数replace()

select replace(ename,'A','我是A') FROM EMP;

instr(char1,cahr2,[,n[,m]]) 取子串在字符串的位置

Database configuration Assistant 数据库配置助手