Oracle SQL

1.截取日期

TRUNC('20141111595959', -6) = 20141111000000

SELECT TRUNC(TO_CHAR(sysdate - 1, 'YYYYMMDDhhmiss'), -6) FROM dual; 

SELECT TO_CHAR(sysdate, 'YYYYMMDD')||'000000' FROM dual;

2.闪回误操作

ALTER TABLE t1 ENABLE row movement;
FLASHBACK TABLE  t1 TO timestamp to_timestamp('14-11-14 14:59:29','YY-MM-DD hh24:mi:ss');

3.查询某一时点的数据

SELECT * FROM t1 AS OF TIMESTAMP(TO_DATE('20141202103000', 'YYYYMMDDHH24MISS'));

4.NOT

SELECT * FROM t1 WHERE NOT(sex = 'M' AND company IN ('a', 'b', 'c', 'd'))

想要查询company NOT IN ('a', 'b', 'c', 'd')，但又想缩小范围即sex=‘M'时有此约束，体会NOT的妙用。

5.删除数据需要关联其他表用EXISTS

DELETE FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE t1.name = t2.name)

6.分析函数

SUM(amt) OVER(PARTITION BY custId ORDER BY age)

RANK() OVER()

7.复制表结构及表数据

create table table1_HC as select * from table1