原文: Oracle的DML語言必備基礎知識

前提是咱們都已經對常用的數據操縱語言非常熟悉了，對標準SQL：

SELECT子句??????????????? --指定查詢結果集的列

DROM子句???????????????? --指定查詢來自哪個表或者試圖

[WHERE 子句]????????????? --指定查詢的條件

[GROUP BY 子句]?????????? --指定查詢結果集的分組的條件

[HAVING 子句]???????????? --指定分組或者集合的查詢條件

[ORDERBY 子句]??????????? --對查詢的排列順序

[UNION 子句]????????????? --多個SELET語句組合,得到結果集的并集

掌握的比較熟悉了。

?????? 下面是我參考《Oracle學習筆記》整理的可能會對咱們平時的開發有幫助的SQL基礎：

?

準備工作：

CREATE TABLE dept(

?????? deptno NUMBER(20),--部門編號

?????? NAME VARCHAR2(20) --部門名稱

);

CREATE TABLE emp(

?????? empno NUMBER(20),--員工編號

?????? empname VARCHAR2(100),--員工姓名

?????? deptno number(20)--所屬部門

);

INSERT INTO dept VALUES('1','新農保組');

INSERT INTO dept VALUES('2','老農保組');

INSERT INTO emp VALUES('1','楊以通','1');

INSERT INTO emp VALUES('2','楊敬義','1');

INSERT INTO emp VALUES('3','曹艷芳','1');

INSERT INTO emp VALUES('4','郝岔蕾','1');

INSERT INTO emp VALUES('5','王煒','1');

INSERT INTO emp VALUES('6','陳字文','1');

INSERT INTO emp VALUES('7','某某人','3');

1、??????? Oracle中ROWNUM的使用

SELECT * FROM emp WHERE ROWNUM=1;???? --可以查詢到數據

SELECT * FROM emp WHERE ROWNUM=2;???? --不能查詢到數據

SELECT * FROM emp WHERE ROWNUM<3;???? --可以查詢到數據

SELECT * FROM emp WHERE ROWNUM>3;???? --不能查詢到數據

SELECT * FROM emp WHERE ROWNUM BETWEEN 2 AND 5; --不能查詢

1）??????????? 新農保系統內的分頁功能的實現

SELECT *

? FROM (SELECT ROWNUM AS MYROWNUM, C.* FROM (

SELECT * FROM EMP????????? --這個地方是咱們寫的SQL語句

) C)

?WHERE MYROWNUM <= 5?????????? --分頁的結束值

?? AND MYROWNUM > 1???????????? --分頁的開始值

系統里面，凡是在JSP里面對LIST進行分頁的地方，均會采用上面的方式將咱們編寫的SQL語句進行封裝一下，從而可以支持數據的分頁顯示。

2）??????????? ROWNUM與排序不是對應的

SELECT ROWNUM,emp.* FROM emp ORDER BY 3;

相應的結果：

可以看出來，ROWNUM并不是1,2，3，4,5那樣子順序排列下去，也就是ROWNUM這個偽列仍舊是在數據取出來的時候就產生了該列，排序并不會改變該列的順序，所以，ROWNUM與排序操作之間并不存在必然的聯系。

2、??????? 實現模糊查詢中的通配符

在整個新農保系統的SQL代碼中模糊查詢通配符基本上都是%，例如：

SELECT * FROM emp WHERE empname LIKE'%艷%';

其實還有其他的通配符：

通配符	含義
%	包含零個或者多個任意字符的字符串
_	任意單個字符
?	任意單個字符 ( 啥時候用?在LIKE子句中怎么用? )
#	表示0-9的數字( 啥時候用?在LIKE子句中怎么用? )
[]	指定范圍或者集合中的任意單個字符,例如[a-f]表示a~f中的一個字符 ( 啥時候用?在LIKE子句中怎么用? )

3、??????? HAVING子句和WHERE子句的區別

HAVING子句和WHERE子句的區別在于:WHERE子句搜索條件在進行分組操作之前進行,而HAVING子句則是在分組操作之后應用，HAVING的語法和WHERE的語法類似，HAVING可以包含聚合函數，這也從一個方面說明HAVING子句是在分組操作之后進行的。

4、??????? 連接查詢的內連接、外連接、交叉連接

1)????? 內連接

內連接根據每個表共有的列的值來匹配兩個表中的列，只有每個表中都存在相同的記錄才會出現在結果集中，在內連接中，兩個表沒有主次之分。在內連接中，出了用‘=’還可以使用INNER JOIN來定義表內部的連接關系，內連接是咱們最常用的一種連接了，以下兩種方式得到的結果是一樣的：

SELECT * FROM emp a,dept b WHERE a.deptno=b.deptno;

SELECT * FROM emp a INNER JOIN dept b ON a.deptno=b.deptno;

2)????? 外連接

①??? 左外連接

左外連接以JOIN子句左側的表為主表，主表中所有的記錄都出現在結果集當中。左外連接可以使用LEFT OUTER JOIN或者LEFT JOIN關鍵字定義左外連接，以下三個查詢語句得到的結果是一致的，需要重點關注（+）是加在從表的后面的哦！

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno(+);

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno;

SELECT * FROM emp LEFT? JOIN dept ON emp.deptno=dept.deptno;

②??? 右外連接

右外連接以連接（JOIN）子句右側的表為主表，主表中的所有的記錄都將出現在結果集中，如果主表中的記錄在左表中沒有匹配的記錄，則結果集中右表的記錄為NULL，右外連接可以使用RIGHT OUTER JOIN或者RIGHT JOIN來定義，以下三個操作的結果是一致的：

SELECT * FROM emp,dept WHERE emp.deptno(+)=dept.deptno;

SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.deptno=dept.deptno;

SELECT * FROM emp RIGHT? JOIN dept ON emp.deptno=dept.deptno;

③??? 完整外部連接

完整外部連接包含連接表中的所有的行，無論他們是否匹配。在Oracle中可以使用FULL OUTER JOIN或者FULL JOIN關鍵字定義完整外部連接。

SELECT * FROM emp FULL OUTER JOIN dept ON emp.deptno=dept.deptno;

SELECT * FROM emp FULL? JOIN dept ON emp.deptno=dept.deptno;

結果如下：

3)????? 交叉連接

SELECT * FROM emp CROSS JOIN dept;

結果如下:

這個結果得到的是兩個表之間進行操作的笛卡爾積,交叉連接基本上不會被用到,除非你想窮舉兩個表之間有多少種記錄組合，關鍵字CROSS JOIN是讓兩個表進行組合，所以不需要ON關鍵字來指定兩個表的連接關系。

5、??????? EXISTS關鍵字的使用

從效率上分析，是選擇IN還是選擇EXISTS？

IN到EXISTS的轉換：

--使用IN來寫的代碼，Oracle先轉換為多表連接，如果不能轉換，則先執行SELECT DEPTNO FROM DEPT WHERE DEPT.DEPTNO = 1部分，然后執行外面的那一塊

SELECT *

? FROM EMP

?WHERE EMP.DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DEPT.DEPTNO = 1);

--使用EXITS來實現的兩個表之間的連接，Oracle先執行外面的循環SELECT * FROM EMP，在此過程中，去關聯檢索DEPT表中的內容，根據EXISTS子句的內容看該列是否在DEPT中存在，如果存在則返回TRUE，如果不存在則返回FALSE，因此EXITS后面SELECT 1 還是SELECT 100 都是一樣的

SELECT *

? FROM EMP A

?WHERE EXISTS (SELECT 1

????????? FROM DEPT

???????? WHERE DEPT.DEPTNO = 1

?????? ????AND DEPT.DEPTNO = A.DEPTNO);

6、??????? UNION關鍵字的使用

使用UNION掛件子組合兩個查詢的結果集，結合的基本規則如下：

①??? 所有查詢中的列數和列的順序必須要一致

②??? 數據類型必須要兼容

使用UNION關鍵字進行合并查詢的時候，數據庫引擎會自動過濾掉結果集中的重復記錄。

使用UNION ALL關鍵字進行合并查詢的時候，數據庫引擎就不會過濾結果集中的重復數據，因此在執行效率上將UNION ALL比UNION要高上很多。

7、??????? SELECT DECODE語句的使用

在新農保系統中，經常會用到SELECT DECODE語句，例如查詢該人是男，還是女？如果代碼表SELECT * FROM AA10 WHERE AAA100=UPPSER(‘’);的結果集比較小，就沒有必要再關聯上一張代碼值表（視情況），直接使用DECODE函數：

SELECT AAC001,

????????? AAC002,

????????? AAC003,

????????? DECODE(AAC004,'1','男','2','女','未知') AS aac004

? FROM AC01;

? DECODE函數的語法如下：

?DECODE (<輸入值>,<值1>,<結果1>[,<值2>,<結果2>…][,<默認結果>])

?說明:

?如果輸入值等于值1,則DECODE函數返回值1,如果輸入值等于值2,則DECODE函數返回值2,依次類推.如果參數列表中沒有輸入值相等的,則DECODE的函數返回默認結果。

8、??????? 在SELECT語句中使用CASE函數

SELECT

CASE

???? WHEN? empno=1? THEN empname||'懶洋洋'

???? WHEN? empno=3? THEN empname||'小屁孩'

???? ELSE empname

???? END

FROM emp;

查詢的結果：

使用CASE函數可以實現與DECODE同樣的功能，但是CASE函數有更強大的作用，尤其是在搜索、統計功能方面，語法如下：

CASE?

WHEN <邏輯表達式1> THEN <結果1>

[WHEN<邏輯表達式2> THEN <結果2> …]

[ELSE <默認結果>]

END

9、??????? 保存查詢結果、在正式庫修改數據之前備份要操作的表

我們在開庫修改某一個表的數據的時候，除了要保存執行的腳本，以及保存相應的文檔的同時，也需要丟該表進行備份工作。從而能夠在萬一出現錯誤的時候，保留恢復的依據，這是一個比較好的習慣。例如：

我們即將對AC43表進行UPDATE或者DELETE操作，除了保存相應的執行腳本，還應當對AC43表進行整體的備份。備份語句如下：

CREATE TABLE ac43_20110816 AS SELECT * FROM ac43;

這樣就在數據庫中建立了一張新表ac43_20110816。

CREATE TABLE語句的格式如下：

CREATE TABLE <新的表名> AS

<SELECT 子句>

<FROM 子句>

<WHERE 子句>……

10、???? 修改數據的時候，避免唯一性約束列

首先為EMP的EMPNO字段增加唯一性約束:

ALTER TABLE emp

ADD CONSTRAINT uk_empno

UNIQUE(empno);

然后對EMP的EMPNO列進行UPDATE或者INSERT操作:

INSERT INTO emp VALUES('1','羊羊羊','1');

UPDATE emp SET empno=4 WHERE empname LIKE '楊_通';

都會有一個提示:

插入或修改都不能違反唯一性約束條件XAXNB.UK_EMPNO。

ALTER TABLE emp DROP CONSTRAINT uk_empno;? --刪除該約束條件

11、???? 修改數據的時候，不能違法檢查性約束

首先在EMP的表上，添加一個檢查性約束，檢查條件為empno<10;

ALTER TABLE emp

ADD CONSTRAINT ck_empno CHECK(empno<10);

然后執行以下語句:

?

INSERT INTO emp VALUES('10','羊羊羊','1');

UPDATE emp SET empno=10 WHERE empname LIKE '楊_通';

則會出現一個提示:

ALTER TABLE emp DROP CONSTRAINT? ck_empno;? --刪除該檢查約束

12、???? 修改數據的時候，不能違反外鍵約束

首先準備數據:

①??? UPDATE emp SET deptno=2 WHERE empno=7; 目的是防止在創建外鍵的時候,找不到dept對應的主鍵。

②??? alter table DEPT? add constraint pk primary key (DEPTNO); 給DEPT表增加一個主鍵，這樣子EMP的外鍵才能指向DEPT的主鍵。

③??? ALTER TABLE emp

ADD CONSTRAINT fk_emp

FOREIGN KEY(deptno) REFERENCES dept(deptno); 增加外鍵約束

進行如下操作：

INSERT INTO emp VALUES('10','羊羊羊','4');

UPDATE emp SET deptno=10 WHERE empname LIKE '楊_通';

則會有如下提示產生：

ALTER TABLE emp DROP CONSTRAINT fk_emp;? --刪除EMP表上的外鍵約束

13、???? 如何刪除表中的數據（TRUNCATE）

可以使用DELETE語句刪除表中的所有的數據。DELETE語句的語法結構如下：

DELETE FROM <表名> WHERE <刪除條件>

例如刪除EMP和DEPT表中的數據：

DELETE FROM DEPT WHERE DEPTNO<4;

DELETE FROM EMP WHERE EMPNO<3;

如果要刪除表中的所有的記錄，而不記錄單個行的刪除操作的話，就可以使用TRUNCATE TABLE操作。TRUNCATE TABLE語句的語法結構如下：

TRUNCATA TABLE <表名>

需要注意的事情是：

①??? 曾經新農保系統中的幾張LOG表的數據量非常的大（上億條），最開始的時候使用DELETE進行刪除，速度非常的慢，使用了大概十幾二十分鐘,而且最后需要COMMIT。最后在確定了該表沒有任何價值之后，使用TRUNCATE TABLE 的方式，速度非常快，大概需要不到一分鐘的時間就完成了該表內容的刪除。

②??? TRUNCATE使用有危險，因為默認COMMIT，一旦刪除，不容易找回。使用DELETE的時候，請務必 帶上、帶對條件！！??

?

參考原文： http://blog.csdn.net/ziwen00/article/details/6697769

?

Oracle的DML語言必備基礎知識