一、連接查詢 1.內(nèi)連接
內(nèi)連接用于返回滿足連接條件的所有記錄。默認(rèn)情況下，在執(zhí)行連接查詢時如果沒有指定任何連接操作符，那么這些連接查詢都屬于內(nèi)連接。
SELECT a.dname,b.ename from dept a,emp b where a.deptno=b.deptno and a.deptno=10;
在FROM子句中指定INNER JOIN選項(xiàng)，也可以指定內(nèi)連接。
SELECT a.dname,b.ename from dept a INNER JOIN emp b where a.deptno=b.deptno and a.deptno=10;
從Oracle9i開始，如果主表的主鍵列和從表的外部鍵列名稱相同，那么可以使用NATURAL JOIN 關(guān)鍵字自動執(zhí)行內(nèi)連接操作。
SELECT dname,ename FROM dept NATURAL JOIN emp;
2.左外連接
左外連接通過指定LEFT［OUTER］ JOIN選項(xiàng)來實(shí)現(xiàn)。當(dāng)使用左外連接時，不僅會返回滿足連接條件的所有記錄，而且還會返回不滿足連接條件的連接操作符左邊表的其他行。
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選項(xiàng)來實(shí)現(xiàn)的。當(dāng)使用右外連接時，不僅會返回滿足連接條件的所有行，而且還會返回不滿足連接條件操作符右邊表的其他行。
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 選項(xiàng)來實(shí)現(xiàn)的。當(dāng)使用完全外連接時，不僅會返回滿足連接條件的所有行，而且還會返回不滿足連接條件的所有其他行。
SELECT a.dname,b.ename FROM dept a FULL JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;
5.使用（＋）操作符
在Oracle9i之前，當(dāng)執(zhí)行外連接時，都是使用連接操作符（＋）來完成的。盡管可以使用操作符（＋）執(zhí)行外連接操作。但Oralce9i開始Oracle建議使用OUTER JOIN執(zhí)行外連接。語法如下：
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1(+) = table2.column2;
當(dāng)使用（＋）操作符執(zhí)行外連接時，應(yīng)該將該操作符放在顯示較少行（完全滿足連接條件行）的一端。
一句話，左外連接時，（＋）要放在右邊表的一端。右外連接時，（＋）放在左邊表的一端。 左外連接：
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 [結(jié)果過濾條件語句]Start with [起始條件過濾語句]Connect by prior [中間記錄過濾條件語句]
2.例子
Select * from company t Where t.flag=1Start with t.company_id=50500000Connect by prior t.company_id=t.parent_id
說明：
select [level],column,expr from table [where condition][start with] //[起點(diǎn)][connect by prior + 主鍵=外鍵 或 外鍵=主鍵]
a.自頂向下: 左邊放主鍵，右邊放外鍵。
b.自底向上: 右邊放主鍵，左邊放外鍵。
c.level（偽列）層次的級別,不固定值。
三、更改數(shù)據(jù)庫的時間格式 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文件中增加以下內(nèi)容：
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
export NLS_DATE_FORMAT
附：在SQLPLUS下，實(shí)現(xiàn)中-英字符集轉(zhuǎn)換
alter session set nls_language='AMERICAN';alter session set nls_language='SIMPLIFIED CHINESE';
四、復(fù)制表和數(shù)據(jù) 1)建表
create table test as select * from dept; --從已知表復(fù)制數(shù)據(jù)和結(jié)構(gòu)create table test as select * from dept where 1=2; --從已知表復(fù)制結(jié)構(gòu)但不包括數(shù)據(jù)
建立視圖：
CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno;
建立實(shí)體化視圖：
CREATE MATERIALLIZED VIEW summary_emp AS SELECT deptno,job,avg(sal) avgsal, sum(sal) sumsal FROM emp GROUP BY cube(deptno,job);
實(shí)體化視圖管理是用于匯總,預(yù)計(jì)算,復(fù)制或分發(fā)數(shù)據(jù)的對象, 在大型的數(shù)據(jù)庫中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查詢的速度,只要在實(shí)體化視圖管理上創(chuàng)建了統(tǒng)計(jì),查詢優(yōu)化器將自動的使用實(shí)體化視圖管理,這特性被稱為QUERY REWRITE(查詢重寫).與普通的視圖不同的是實(shí)體化視圖管理存儲數(shù)據(jù),占據(jù)數(shù)據(jù)庫的物理空間的.
2)不使用列的列表插入數(shù)據(jù)：
insert into test select * from dept;
3)使用DEFAULT插入數(shù)據(jù):如果列存在默認(rèn)值，則會使用其默認(rèn)值；如果列不存在默認(rèn)值，則自動使用NULL.
insert into dept values(10,'市場部',default);
4)使用子查詢插入數(shù)據(jù)
insert into employee(empno,ename,sql,deptno)select empno,ename,sal,deptno from empwhere deptno = 10;
當(dāng)要裝載大批量數(shù)據(jù)時，用直接裝載方式:
insert /*+APPEND */ into employee(empno,ename,sql,deptno)select empno,ename,sal,deptno from empwhere deptno = 10;
5)使用多表插入數(shù)據(jù)。從Oracle9i開始，使用INSERT語句可以將某張表的數(shù)據(jù)同時插入到多張表中。
使用ALL操作符，在每個條件子句上都要執(zhí)行INTO子句后的子查詢。
insert all when deptno=10 then into t_dept10when deptno=20 then into t_dept20when deptno=30 then into t_dept30when job='clerk' then into t_clerkelse into t_otherselect * from emp;
使用FIRST操作符執(zhí)行多表插入，如果數(shù)據(jù)已經(jīng)滿足了先前條件，并且已經(jīng)被插入到某表，那么該行數(shù)據(jù)在后續(xù)插入中將不會被再次使用。
insert first when deptno=10 then into t_dept10when deptno=20 then into t_dept20when deptno=30 then into t_dept30when job='clerk' then into t_clerkelse into t_otherselect * from emp;
五、多列子查詢 多列子查詢是指返回多列數(shù)據(jù)的子查詢語句。當(dāng)多列子查詢返回單行數(shù)據(jù)時，在WHERE子句中可以使用單行比較符；當(dāng)多列子查詢返回多行數(shù)據(jù)時，在WHERE子句中必須使用多行比較符（IN，ANY，ALL）。
如顯示與Id為10000的人員部門和崗位完全相同的所有雇員：
SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE id=10000);
當(dāng)執(zhí)行成對比較是時，因?yàn)橐蠖鄠€列的數(shù)據(jù)必須同時匹配，所以必須要使用多列子查詢實(shí)現(xiàn)。
如顯示工資和補(bǔ)助與部門30雇員的工資和補(bǔ)助完全匹配的所有雇員：
SELECT ename,sal,comm,dptno FROM emp WHERE (sal, nvl(comm,-1)) IN (SELECT sal, nvl(comm,-1) FROM emp WHERE deptno=30);
執(zhí)行非成對比較時，應(yīng)該使用多個多行子查詢來實(shí)現(xiàn)。
如顯示工資匹配于部門30工資列表、補(bǔ)助匹配于部門30補(bǔ)助列表的所有雇員：
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);
六、相關(guān)子查詢與FROM子句中子查詢 相關(guān)子查詢是指需要引用主查詢表列的子查詢語句，是通過EXISTS謂詞來實(shí)現(xiàn)的。
如顯示工作在“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子句中使用子查詢時，該子查詢會被作為視圖對待。因此也被稱為內(nèi)嵌視圖。
如顯示高于部門平均工資的雇員信息：
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。當(dāng)使用集合操作符時，必須確保不同查詢的列個數(shù)和數(shù)據(jù)類型都要匹配。
有以下一些限制：
1.對于LOB，VARRAY和嵌套表列來說，集合操作符是無效的。
2.對于LONG列來說，UNION，INTERSECT，MINUS操作符是無效的。
3.如果選擇列表包含了表達(dá)式，則必須為為其指定列別名。
(一)UNION
UNION操作符用于獲取兩個結(jié)果集的并集。當(dāng)使用該操作符時，會自動去掉結(jié)果集中的重復(fù)行。并且會以第一列的結(jié)果進(jìn)行排序。
SELECT ename,sal,job FROM emp WHERE sal>2500 UNION SELECT ename,sal,job FROM emp WHERE job='MANAGER';
(二)UNION ALL
UNION ALL操作符獲取兩個結(jié)果集的并集，但不會取消重復(fù)值，而且也不會以任何列進(jìn)行排序。
SELECT ename,sal,job FROM emp WHERE sal>2500 UNION ALL SELECT ename,sal,job FROM emp WHERE job='MANAGER';
(三)INTERSECT
INTERSECT操作符用于獲取兩個結(jié)果集的交集。當(dāng)使用該操作符時，只會顯示同時存在于兩個結(jié)果集中的數(shù)據(jù)，并且會以第一列進(jìn)行排序。
SELECT ename,sal,job FROM emp WHERE sal>2500 INTERSECT SELECT ename,sal,job FROM emp WHERE job='MANAGER';
(四)MINUS
MINUS用于獲取兩個結(jié)果集的差集。以第一列進(jìn)行排序。
SELECT ename,sal,job FROM emp WHERE sal>2500 MINUS SELECT ename,sal,job FROM emp WHERE job='MANAGER';
八、CASE表達(dá)式和倒敘查詢 通過CASE表達(dá)式，可以避免調(diào)用過程來完成條件分支操作。
SELECT ename,sal,CASE WHEN sal>3000 THEN 3 WHEN sal>2000 THEN 2 ELSE 1 END grade FROM emp WHERE deptno=10;
默認(rèn)情況下，當(dāng)執(zhí)行查詢操作時，只能看到最近提交的數(shù)據(jù)。從Oracle9i開始，通過使用倒敘查詢(Flashback Query)特征，可以查看到過去某個時間點(diǎn)所提交的數(shù)據(jù)。注意，如果使用倒敘查詢，那么要示數(shù)據(jù)庫必須彩UNDO管理方式，并且初始化參數(shù)undo_retention限制了UNDO數(shù)據(jù)的保留時間。
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子句可以給子查詢指定一個名稱，并且使得在一條語句中可以完成所有任務(wù)，從而避免使用臨時表。
如顯示部門工資總和高于雇員工資總和三分之一的部門名稱及工資總和。
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對記錄進(jìn)行去重sql DELETE FROM [TABLE_NAME]WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM [TABLE_NAME]GROUP BY [COL1,COL2,COL3...]);
