SQL语句一些实用技巧for oracle.doc

SQL语句一些实用技巧for oracle1）在select语句中使用条件逻辑1select ename,sal, 2 case when sal = 4000 then OVERPAID 4 else OK 5 end as status 6from emp ENAME SAL STATUS- - -SMITH 800 UNDERPAIDALLEN 1600 UNDERPAIDWARD 1250 UNDERPAIDJONES 2975 OKMARTIN 1250 UNDERPAIDBLAKE 2850 OKCLARK 2450 OKSCOTT 3000 OKKING 5000 OVERPAIDTURNER 1500 UNDERPAIDADAMS 1100 UNDERPAIDJAMES 950 UNDERPAIDFORD 3000 OKMILLER 1300 UNDERPAID 2）从表中随机返回n条记录1select * 2 from ( 3 select ename, job 4 from emp 5 order by dbms_random.value() 6 ) 7 where rownum = 5 3）按照子串排序 比如要从EMP表中返回员工名字和职位，并且按照职位字段最后2个字符排序1select ename,job 2 from emp 3order by substr(job,length(job)-2) ENAME JOB- -KING PRESIDENTSMITH CLERKADAMS CLERKJAMES CLERKMILLER CLERKJONES MANAGERCLARK MANAGERBLAKE MANAGERALLEN SALESMANMARTIN SALESMANWARD SALESMANTURNER SALESMANSCOTT ANALYSTFORD ANALYST 4）处理空值排序 当被排序的列存在空值，如果希望空值不影响现有排序 1select ename,sal,comm 2 from emp 3order by comm nulls last ENAME SAL COMM- - -TURNER 1500 0ALLEN 1600 300WARD 1250 500MARTIN 1250 1400SMITH 800JONES 2975JAMES 950MILLER 1300FORD 3000ADAMS 1100BLAKE 2850CLARK 2450SCOTT 3000KING 50001select ename,sal,comm 2 from emp 3order by comm desc nulls first ENAME SAL COMM- - -SMITH 800JONES 2975CLARK 2450BLAKE 2850SCOTT 3000KING 5000JAMES 950MILLER 1300FORD 3000ADAMS 1100MARTIN 1250 1400WARD 1250 500ALLEN 1600 300TURNER 1500 05）根据数据项的键排序 比如如果job是“SALESMAN”，根据COMM排序，否则根据SAL排序 1select ename,sal,job,comm 2 from emp 3 order by case when job = SALESMAN then comm else sal end ENAME SAL JOB COMM- - - -TURNER 1500 SALESMAN 0ALLEN 1600 SALESMAN 300WARD 1250 SALESMAN 500SMITH 800 CLERKJAMES 950 CLERKADAMS 1100 CLERKMARTIN 1250 SALESMAN 1300MILLER 1300 CLERKCLARK 2450 MANAGERBLAKE 2850 MANAGERJONES 2975 MANAGERSCOTT 3000 ANALYSTFORD 3000 ANALYST 6）从一个表中查找另一个表中没有的值 比如要从DEPT中查找在表EMP中不存在数据的所有部门（数据中，DEPTNO值为40的记录在表EMP中不存在） 1select deptno from dept 2minus 3select deptno from emp 7）在运算和比较时使用null值 null不会等于和不等于任何值，null和自己都不等于。以下例子是当comm有null的情况下列出比“WARD”提成低的员工。 （coalesce函数将null转换为其他值） 1select ename,comm,coalesce(comm,0) 2 from emp 3where coalesce(comm,0) ( select comm 4 from emp 5 where ename = WARD )ENAME COMM COALESCE(COMM,0)- - -SMITH 0ALLEN 300 300JONES 0BLAKE 0CLARK 0SCOTT 0KING 0TURNER 0 0ADAMS 0JAMES 0FORD 0MILLER 0 8）删除重复记录 对于名字重复的记录，保留一个1delete from dupes 2 where id not in ( select min(id) 3 from dupes 4 group by name ) 9）合并记录比如如下需求：如果表EMP_COMMISSION中的某员工也存在于EMP表，那么更新comm为1000如果以上员工已经更新到1000的员工，如果他们SAL少于2000，删除他们否则，从表中提取该员工插入表EMP_COMMISSION1merge into emp_commission ec 2using (select * from emp) emp 3 on (ec.empno=emp.empno) 4 when matched then 5 update set m = 1000 6 delete where (sal 2000) 7 when not matched then 8 insert (ec.empno,ec.ename,ec.deptno,m) 9 values (emp.empno,emp.ename,emp.deptno,m) 10）用sql生成sql 1select select count(*) from |table_name|; cnts 2 from user_tables; （user_tables是oracle的元数据表之一）CNTS-select count(*) from ANT;select count(*) from BONUS;select count(*) from DEMO1;select count(*) from DEMO2;select count(*) from DEPT;select count(*) from DUMMY;select count(*) from EMP;select count(*) from EMP_SALES;select count(*) from EMP_SCORE;select count(*) from PROFESSOR;select count(*) from T;select count(*) from T1;select count(*) from T2;select count(*) from T3;select count(*) from TEACH;select count(*) from TEST;select count(*) from TRX_LOG;select count(*) from X; 11）计算字符在字符串里的出现次数判断字符串里有多少个 , 1select (length(10,CLARK,MANAGER)- 2 length(replace(10,CLARK,MANAGER,)/length(,) 3 as cnt 4from t1 先计算原字符串长度，再减去去掉逗号的长度，这个差再除以，的长度 12）将数字和字母分离原数据是：DATA-SMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE2850CLARK2450SCOTT3000KING5000TURNER1500ADAMS1100JAMES950FORD3000MILLER1300 1select replace( 2 translate(data,0123456789,0000000000),0) ename, 3 to_number( 4 replace( 5 translate(lower(data), 6 abcdefghijklmnopqrstuvwxyz, 7 rpad(z,26,z),z) sal 8 from ( 9 select ename|sal data from emp 10 ) ENAME SAL- -SMITH 800ALLEN 1600WARD 1250JONES 2975MARTIN 1250BLAKE 2850CLARK 2450SCOTT 3000KING 5000TURNER 1500ADAMS 1100JAMES 950FORD 3000MILLER 1300思路是很复杂的，比如先去除数字，是先把所有数字翻译为0，然后用replace去掉0. 13）根据表中的行创建分割列表表中数据：DEPTNO EMPS- -10 CLARK10 KING10 MILLER20 SMITH20 ADAMS20 FORD20 SCOTT20 JONES30 ALLEN30 BLAKE30 MARTIN30 JAMES30 TURNER30 WARD 1select deptno, 2 ltrim(sys_connect_by_path(ename,),) emps 3 from ( 4 select deptno, 5 ename, 6 row_number() over 7 (partition by deptno order by empno) rn, 8 count(*) over 9 (partition by deptno) cnt 10 from emp 11 ) 12 where level = cnt 13 start with rn = 1 14 connect by prior deptno = deptno and prior rn = rn-1 查询结果DEPTNO EMPS- -10 CLARK,KING,MILLER20 SMITH,JONES,SCOTT,ADAMS,FORD30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 14）按字母顺序排序 1select old_name, new_name 2 from (select old_name, replace(sys_connect_by_path(c, ), ) new_name 3 from (select e.ename old_name, 4 row_number() over(partition by e.ename order by substr(e.ename, iter.pos, 1) rn, 5 substr(e.ename, iter.pos, 1) c 6 from emp e, (select rownum pos from emp) iter 7 where iter.pos = length(e.ename) 8 order by 1) x 9 start with rn = 1 10 connect by prior rn = rn - 1 11 and prior old_name