oracle查詢/子查詢/復制表和數據

一、連接查詢
1.內連接
    內連接用于返回滿足連接條件的所有記錄。默認情況下，在執行連接查詢時如果沒有指定任何連接操作符，那么這些連接查詢都屬于內連接。

SELECT a.dname,b.ename from dept a,emp b where a.deptno=b.deptno and a.deptno=10;

   在FROM子句中指定INNER JOIN選項，也可以指定內連接。

SELECT a.dname,b.ename from dept a INNER JOIN emp b where a.deptno=b.deptno and a.deptno=10;

   從Oracle9i開始，如果主表的主鍵列和從表的外部鍵列名稱相同，那么可以使用NATURAL JOIN 關鍵字自動執行內連接操作。

SELECT dname,ename FROM dept NATURAL JOIN emp;

2.左外連接
    左外連接通過指定LEFT［OUTER］ JOIN選項來實現。當使用左外連接時，不僅會返回滿足連接條件的所有記錄，而且還會返回不滿足連接條件的連接操作符左邊表的其他行。

SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;

3.右外連接
    右外連接通過指定RIGHT［OUTER］ JOIN選項來實現的。當使用右外連接時，不僅會返回滿足連接條件的所有行，而且還會返回不滿足連接條件操作符右邊表的其他行。

SELECT a.dname,b.ename FROM dept a RIGHT JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;

4.完全外連接
    完全外連接是指定FULL［OUTER］ JOIN 選項來實現的。當使用完全外連接時，不僅會返回滿足連接條件的所有行，而且還會返回不滿足連接條件的所有其他行。

SELECT a.dname,b.ename FROM dept a FULL JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;

5.使用（＋）操作符
    在Oracle9i之前，當執行外連接時，都是使用連接操作符（＋）來完成的。盡管可以使用操作符（＋）執行外連接操作。但Oralce9i開始Oracle建議使用OUTER JOIN執行外連接。語法如下：
    SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1(+) = table2.column2;
    當使用（＋）操作符執行外連接時，應該將該操作符放在顯示較少行（完全滿足連接條件行）的一端。
     一句話，左外連接時，（＋）要放在右邊表的一端。右外連接時，（＋）放在左邊表的一端。
    左外連接：

SELECT a.dname,b.ename FROM dept a, emp b where a.deptno=b.deptno(+) AND b.deptno(+)=10;

   右外連接

SELECT a.dname,b.ename FROM dept a, emp b where a.deptno(+)=b.deptno AND a.deptno(+)=10;

二、遞歸查詢
1.語法

Select * from …. Where [結果過濾條件語句]
Start with  [起始條件過濾語句]
Connect by prior [中間記錄過濾條件語句] 

2.例子

Select * from company t Where t.flag=1
Start with  t.company_id=50500000
Connect by prior t.company_id=t.parent_id

說明：

select [level],column,expr from table [where condition]
[start with] //[起點]
[connect by prior + 主鍵=外鍵 或 外鍵=主鍵]

a.自頂向下: 左邊放主鍵，右邊放外鍵。
b.自底向上: 右邊放主鍵，左邊放外鍵。
c.level（偽列）層次的級別,不固定值。

三、更改數據庫的時間格式

1、臨時更改
alter  session  set  nls_date_format='YYYY-MM-DD HH24:MI:SS'

2.永久更改
    在注冊表[HKEY_CODE_MACHINE\SOFTWARE\ORACLE]里面，加入NLS_DATE_FORMAT，值為YYYY-MM-DD HH24:MI:SS。

3、Unix下，在用戶的.profile文件中增加以下內容： 
  NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
  export  NLS_DATE_FORMAT

附：在SQLPLUS下，實現中-英字符集轉換

alter session set nls_language='AMERICAN';
alter session set nls_language='SIMPLIFIED CHINESE'; 

四、復制表和數據
1)建表

create table test as select * from dept; --從已知表復制數據和結構
create table test as select * from dept where 1=2; --從已知表復制結構但不包括數據

   建立視圖：

CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno;

   建立實體化視圖：

CREATE MATERIALLIZED VIEW summary_emp AS SELECT deptno,job,avg(sal) avgsal, sum(sal) sumsal FROM emp GROUP BY cube(deptno,job);

   實體化視圖管理是用于匯總,預計算,復制或分發數據的對象, 在大型的數據庫中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查詢的速度,只要在實體化視圖管理上創建了統計,查詢優化器將自動的使用實體化視圖管理,這特性被稱為QUERY REWRITE(查詢重寫).與普通的視圖不同的是實體化視圖管理存儲數據,占據數據庫的物理空間的.
2)不使用列的列表插入數據：

insert into test select * from dept;

3)使用DEFAULT插入數據:如果列存在默認值，則會使用其默認值；如果列不存在默認值，則自動使用NULL.

insert into dept values(10,'市場部',default);

4)使用子查詢插入數據

insert into employee(empno,ename,sql,deptno)
select empno,ename,sal,deptno from emp
where deptno = 10;

    當要裝載大批量數據時，用直接裝載方式:

insert /*+APPEND */ into employee(empno,ename,sql,deptno)
select empno,ename,sal,deptno from emp
where deptno = 10;

5)使用多表插入數據。從Oracle9i開始，使用INSERT語句可以將某張表的數據同時插入到多張表中。
    使用ALL操作符，在每個條件子句上都要執行INTO子句后的子查詢。

insert all when deptno=10 then into t_dept10
when deptno=20 then into t_dept20
when deptno=30 then into t_dept30
when job='clerk' then into t_clerk
else into t_other
select * from emp;

     使用FIRST操作符執行多表插入，如果數據已經滿足了先前條件，并且已經被插入到某表，那么該行數據在后續插入中將不會被再次使用。

insert first when deptno=10 then into t_dept10
when deptno=20 then into t_dept20
when deptno=30 then into t_dept30
when job='clerk' then into t_clerk
else into t_other
select * from emp;

五、多列子查詢
    多列子查詢是指返回多列數據的子查詢語句。當多列子查詢返回單行數據時，在WHERE子句中可以使用單行比較符；當多列子查詢返回多行數據時，在WHERE子句中必須使用多行比較符（IN，ANY，ALL）。
    如顯示與Id為10000的人員部門和崗位完全相同的所有雇員：

SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE id=10000);

    當執行成對比較是時，因為要求多個列的數據必須同時匹配，所以必須要使用多列子查詢實現。
     如顯示工資和補助與部門30雇員的工資和補助完全匹配的所有雇員：

SELECT ename,sal,comm,dptno FROM emp WHERE (sal, nvl(comm,-1)) IN (SELECT sal, nvl(comm,-1) FROM emp WHERE deptno=30);

    執行非成對比較時，應該使用多個多行子查詢來實現。
     如顯示工資匹配于部門30工資列表、補助匹配于部門30補助列表的所有雇員：

SELECT ename,sal,comm,deptno FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND nvl(comm,-1) IN (SELECT nvl(comm,-1) FROM emp WHERE deptno=30);

六、相關子查詢與FROM子句中子查詢
     相關子查詢是指需要引用主查詢表列的子查詢語句，是通過EXISTS謂詞來實現的。
      如顯示工作在“NEW YORK”的所有雇員：

SELECT ename,job,sal,deptno FROM emp WHERE EXISTS (SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc='NEW YORK');

     在FROM子句中使用子查詢時，該子查詢會被作為視圖對待。因此也被稱為內嵌視圖。
      如顯示高于部門平均工資的雇員信息：

SELECT ename,job,sal FROM emp, (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) dept WHERE emp.deptno=dept.deptno AND sal>dept.avgsal;

七、合并查詢
    集合操作符有UNION，UNION ALL， INTERSECT和MINUS。當使用集合操作符時，必須確保不同查詢的列個數和數據類型都要匹配。
     有以下一些限制：
1.對于LOB，VARRAY和嵌套表列來說，集合操作符是無效的。
2.對于LONG列來說，UNION，INTERSECT，MINUS操作符是無效的。
3.如果選擇列表包含了表達式，則必須為為其指定列別名。
(一)UNION
    UNION操作符用于獲取兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行。并且會以第一列的結果進行排序。

SELECT ename,sal,job FROM emp WHERE sal>2500 UNION SELECT ename,sal,job FROM emp WHERE job='MANAGER';

(二)UNION ALL
    UNION ALL操作符獲取兩個結果集的并集，但不會取消重復值，而且也不會以任何列進行排序。

SELECT ename,sal,job FROM emp WHERE sal>2500 UNION ALL SELECT ename,sal,job FROM emp WHERE job='MANAGER';

(三)INTERSECT
    INTERSECT操作符用于獲取兩個結果集的交集。當使用該操作符時，只會顯示同時存在于兩個結果集中的數據，并且會以第一列進行排序。

SELECT ename,sal,job FROM emp WHERE sal>2500 INTERSECT SELECT ename,sal,job FROM emp WHERE job='MANAGER';

(四)MINUS
    MINUS用于獲取兩個結果集的差集。以第一列進行排序。

SELECT ename,sal,job FROM emp WHERE sal>2500 MINUS SELECT ename,sal,job FROM emp WHERE job='MANAGER';

八、CASE表達式和倒敘查詢
     通過CASE表達式，可以避免調用過程來完成條件分支操作。

SELECT ename,sal,CASE WHEN sal>3000 THEN 3 WHEN sal>2000 THEN 2 ELSE 1 END grade FROM emp WHERE deptno=10;

    默認情況下，當執行查詢操作時，只能看到最近提交的數據。從Oracle9i開始，通過使用倒敘查詢(Flashback Query)特征，可以查看到過去某個時間點所提交的數據。注意，如果使用倒敘查詢，那么要示數據庫必須彩UNDO管理方式，并且初始化參數undo_retention限制了UNDO數據的保留時間。

SELECT ename,sal FROM emp AS OF TIMESTAMP to_timestamp('2009-01-01 12:12:00','YYYY-MM-DD HH24:MI:SS') WHERE ename='CLARK';

九、使用WITH子句重用子查詢
     從Oracle9i開始，通過WITH子句可以給子查詢指定一個名稱，并且使得在一條語句中可以完成所有任務，從而避免使用臨時表。
      如顯示部門工資總和高于雇員工資總和三分之一的部門名稱及工資總和。

WITH summary AS (SELECT dname,SUM(sal) AS dept_total FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname)
SELECT dname, dept_total FROM summary WHERE dept_total>(
SELECT SUM(dept_total) * 1/3 FROM summary);

十、oracle對記錄進行去重sql

  DELETE FROM [TABLE_NAME]
WHERE ROWID NOT IN (
SELECT MIN(ROWID) FROM [TABLE_NAME]
GROUP BY [COL1,COL2,COL3...]
);