oracle的语句

使用

(二) Union 与union all把两个表连接起来 UNION 过滤重复的 UNION ALL 全部连接显示重

复的信息，

Select * from tb_test union all select * from tb_test Select * from tb_testunion select * from tb_test (三) Merge（合并行）

create table merge1 (

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60) );

insert into merge1 values (1501, 'VIVITAR 35MM', 'ELECTRNCS'); insert into merge1 values (1502, 'OLYMPUS IS50', 'ELECTRNCS'); insert into merge1 values (1600, 'PLAY GYM', 'TOYS'); insert into merge1 values (1601, 'LAMAZE', 'TOYS');

insert into merge1 values (1666, 'HARRY POTTER', 'DVD');

create table merge2 (

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60) );

insert into merge2 values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS'); insert into merge2 values (1601, 'LAMAZE', 'TOYS');

insert into merge2 values (1666, 'HARRY POTTER', 'TOYS');

insert into merge2 values (1700, 'WAIT INTERFACE', 'BOOKS');

MERGE INTO merge1 m1 USING merge2 m2

ON (m1.product_id = m2.product_id) WHEN MATCHED THEN UPDATE

SET m1.product_name = m2.product_name, m1.category = m2.category

（4）TRANSLATE

SELECT TRANSLATE('abcdj','ac','123456') FROM dual;

（5）rownum

selectrownumrn, empno, ename from emp

selectrownumrn, empno, ename from emp where rownum=1

selectrownumrn, empno, ename from emp where rownum=2

selectrownum,empno from emp group by rownum,empno having rownum= 2;

select * from(select rownumrn, empno, ename from emp) where rn>2 and rn<8

select * from( select rownumrn , e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from empe,dept d where e.deptno=d.deptno and d.deptno=20 ) where rn>2 and rn<5

(6)

selecte.deptno,e.sal ,e.ename,e.job ,case when e.sal<=1000 then '1' when 1000<=e.sal and e.sal<=2000 then '2' when 2000<=e.sal and e.sal<=3000 then '3' when e.sal>=3000 then '4' end leval from emp e