《sql语句一些实用技巧for oracle》由会员分享，可在线阅读，更多相关《sql语句一些实用技巧for oracle（13页珍藏版）》请在金锄头文库上搜索。
1、SQL 语句一些实用技巧 for oracle1）在 select 语句中使用条件逻辑1 select ename,sal, 2 case when sal = 4000 then OVERPAID 4 else OK 5 end as status 6 from emp ENAME SAL STATUS- - -SMITH 800 UNDERPAIDALLEN 1600 UNDERPAIDWARD 1250 UNDERPAIDJONES 2975 OKMARTIN 1250 UNDERPAIDBLAKE 2850 OKCLARK 2450 OKSCOTT 3000 OKKING 5000 O
2、VERPAIDTURNER 1500 UNDERPAIDADAMS 1100 UNDERPAIDJAMES 950 UNDERPAIDFORD 3000 OKMILLER 1300 UNDERPAID 2）从表中随机返回 n 条记录1 select * 2 from ( 3 select ename, job 4 from emp 5 order by dbms_random.value() 6 ) 7 where rownum = 5 3）按照子串排序 比如要从 EMP 表中返回员工名字和职位，并且按照职位字段最后2个字符排序1 select ename,job 2 from emp 3 o
3、rder by substr(job,length(job)-2) ENAME JOB- -KING PRESIDENTSMITH CLERKADAMS CLERKJAMES CLERKMILLER CLERKJONES MANAGERCLARK MANAGERBLAKE MANAGERALLEN SALESMANMARTIN SALESMANWARD SALESMANTURNER SALESMANSCOTT ANALYSTFORD ANALYST4）处理空值排序 当被排序的列存在空值，如果希望空值不影响现有排序 1 select ename,sal,comm 2 from emp 3 ord
4、er 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 50001 select ename,sal,comm 2 from emp 3 order by comm desc nulls first ENAME SAL COMM- - -SMITH 800JONES 2975
5、CLARK 2450BLAKE 2850SCOTT 3000KING 5000JAMES 950MILLER 1300FORD 3000ADAMS 1100MARTIN 1250 1400WARD 1250 500ALLEN 1600 300TURNER 1500 05）根据数据项的键排序 比如如果 job 是“ SALESMAN”，根据 COMM 排序，否则根据 SAL 排序 1 select ename,sal,job,comm 2 from emp 3 order by case when job = SALESMAN then comm else sal end ENAME SAL J
6、OB 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 ANALYST6）从一个表中查找另一个表中没有的值 比如要从 DEPT 中查找在表 EMP 中不存
7、在数据的所有部门（数据中， DEPTNO 值为40的记录在表EMP 中不存在） 1 select deptno from dept 2 minus 3 select deptno from emp 7）在运算和比较时使用 null 值 null 不会等于和不等于任何值，null 和自己都不等于。以下例子是当 comm 有 null 的情况下列出比“WARD” 提成低的员工。 （ coalesce 函数将 null 转换为其他值） 1 select ename,comm,coalesce(comm,0) 2 from emp 3 where coalesce(comm,0) ( select c
8、omm 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）删除重复记录 对于名字重复的记录，保留一个1 delete from dupes 2 where id not in ( select min(id) 3 from dupes 4 group by name ) 9）合并记录比如如下需求：如果表 EMP_COMMISSI
9、ON 中的某员工也存在于 EMP 表，那么更新 comm 为1000如果以上员工已经更新到1000的员工，如果他们 SAL 少于2000，删除他们否则，从表中提取该员工插入表 EMP_COMMISSION1 merge into emp_commission ec 2 using (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.empn
10、o,ec.ename,ec.deptno,m) 9 values (emp.empno,emp.ename,emp.deptno,m) 10）用 sql 生成 sql 1 select 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
11、 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(
12、*) from TEST;select count(*) from TRX_LOG;select count(*) from X;11）计算字符在字符串里的出现次数判断字符串里有多少个 , 1 select (length(10,CLARK,MANAGER)- 2 length(replace(10,CLARK,MANAGER,)/length(,) 3 as cnt 4 from t1 先计算原字符串长度，再减去去掉逗号的长度，这个差再除以， 的长度12）将数字和字母分离原数据是：DATA-SMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE28
13、50CLARK2450SCOTT3000KING5000TURNER1500ADAMS1100JAMES950FORD3000MILLER13001 select 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-
14、-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 WARD1 select deptno, 2 ltrim(sys_connect_by_path(ename,),) e
