一些不常用的SQL语句

1: 确定表的结构
   DESC table_name;
 2: 处理NULL,NVL(EPER1,EPER2),EXPER1为空，返回EXPER2，反之则返回EXPER1；NVL2(EPER1,EPER2,EPER3),EPER1不为空，返回
  EPER3，为空返回EPER2，数据类型一致。
   SELECT NVL(EPER1,EPER2) FROM DUAL;
 3: 联结字符串
   SELECT conn1||'字符串'||conn2 AS '字符串' From dual;
 4: 使用子查询直接装载,/*+APPEND*/为直接装载，对大批量数据处理很快
   INSERT /*+APPEND*/ INTO employee(empno,ename,sal,deptno)
   SELECT empno,ename,sal,deptno FROM emp WHERE deptno = 20;
 5: 使用ALL,FIRST操作符进行多表插入,
   INSERT ALL
   WHEN depno=10 THEN INTO dept10
   WHEN depno=20 THEN INTO dept20
   WHEN depno=30 THEN INTO dept30
   ELSE INTO other
   SELECT  * FROM emp;
 6: 使用DEFAULT选项更新数据
   UPDATE emp SET jop=DEFAULT WHERE ename='test';
 7: 复制表数据
   UPDATE employee SET depno=(SELECT depno FROM emp WHERE empno='10')
   WHERE job=(Select jop From emp WHERE empno='10')
 8: 使用TRUNCATE TABLE 截断表
   TRUNCATE TABLE emp;
 9: 事务提交与回退 
   COMMIT,ROLLBACK;
 10: 分组函数只能出现在选择列表,HAVING 和 ORDER BY 子句中,ORDER BY 放在最后.
   Select depno,max(sal),min(sal) From emp GROUP BY depno HAVING MAX(sal)>100 ORDER BY avg(sal)
 11: 使用ROLLUP操作符,生成横向小计统计.CUBE生成横向和纵向统计.
   Select depno,max(sal),min(sal) From emp GROUP BY ROLLUP(depno);
   Select depno,max(sal),min(sal) From emp GROUP BY CUBE(depno);
 12: 内连接与外连接
   Select table1.COLUMN,table2.COLUMN From table1 [inner|left|right|full]
   join table2 ON table1.column=table2.column;
 13: 在DML,DDL中用子查询.
   Create TABLE newName(depno,depname) AS Select depno,depname From emp;
 14: 合并查询,UNION,取并集,UNION ALL取并集且有重复,INTERSECT,取交集,MINUS取两个结果的差集.
   Select table1.COLUMN1,table1.COLUMN2 From table1
   UNION [UNION ALL|INTERSECT|MINUS]
   Select table2.COLUMN1,table2.COLUMN2 From table2;