原文: Oracle的DML語(yǔ)言必備基礎(chǔ)知識(shí)

前提是咱們都已經(jīng)對(duì)常用的數(shù)據(jù)操縱語(yǔ)言非常熟悉了，對(duì)標(biāo)準(zhǔn)SQL：

SELECT子句??????????????? --指定查詢(xún)結(jié)果集的列

DROM子句???????????????? --指定查詢(xún)來(lái)自哪個(gè)表或者試圖

[WHERE 子句]????????????? --指定查詢(xún)的條件

[GROUP BY 子句]?????????? --指定查詢(xún)結(jié)果集的分組的條件

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

[ORDERBY 子句]??????????? --對(duì)查詢(xún)的排列順序

[UNION 子句]????????????? --多個(gè)SELET語(yǔ)句組合,得到結(jié)果集的并集

掌握的比較熟悉了。

?????? 下面是我參考《Oracle學(xué)習(xí)筆記》整理的可能會(huì)對(duì)咱們平時(shí)的開(kāi)發(fā)有幫助的SQL基礎(chǔ)：

?

準(zhǔn)備工作：

CREATE TABLE dept(

?????? deptno NUMBER(20),--部門(mén)編號(hào)

?????? NAME VARCHAR2(20) --部門(mén)名稱(chēng)

);

CREATE TABLE emp(

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

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

?????? deptno number(20)--所屬部門(mén)

);

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

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

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;???? --可以查詢(xún)到數(shù)據(jù)

SELECT * FROM emp WHERE ROWNUM=2;???? --不能查詢(xún)到數(shù)據(jù)

SELECT * FROM emp WHERE ROWNUM<3;???? --可以查詢(xún)到數(shù)據(jù)

SELECT * FROM emp WHERE ROWNUM>3;???? --不能查詢(xún)到數(shù)據(jù)

SELECT * FROM emp WHERE ROWNUM BETWEEN 2 AND 5; --不能查詢(xún)

1）??????????? 新農(nóng)保系統(tǒng)內(nèi)的分頁(yè)功能的實(shí)現(xiàn)

SELECT *

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

SELECT * FROM EMP????????? --這個(gè)地方是咱們寫(xiě)的SQL語(yǔ)句

) C)

?WHERE MYROWNUM <= 5?????????? --分頁(yè)的結(jié)束值

?? AND MYROWNUM > 1???????????? --分頁(yè)的開(kāi)始值

系統(tǒng)里面，凡是在JSP里面對(duì)LIST進(jìn)行分頁(yè)的地方，均會(huì)采用上面的方式將咱們編寫(xiě)的SQL語(yǔ)句進(jìn)行封裝一下，從而可以支持?jǐn)?shù)據(jù)的分頁(yè)顯示。

2）??????????? ROWNUM與排序不是對(duì)應(yīng)的

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

相應(yīng)的結(jié)果：

可以看出來(lái)，ROWNUM并不是1,2，3，4,5那樣子順序排列下去，也就是ROWNUM這個(gè)偽列仍舊是在數(shù)據(jù)取出來(lái)的時(shí)候就產(chǎn)生了該列，排序并不會(huì)改變?cè)摿械捻樞?，所以，ROWNUM與排序操作之間并不存在必然的聯(lián)系。

2、??????? 實(shí)現(xiàn)模糊查詢(xún)中的通配符

在整個(gè)新農(nóng)保系統(tǒng)的SQL代碼中模糊查詢(xún)通配符基本上都是%，例如：

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

其實(shí)還有其他的通配符：

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

3、??????? HAVING子句和WHERE子句的區(qū)別

HAVING子句和WHERE子句的區(qū)別在于:WHERE子句搜索條件在進(jìn)行分組操作之前進(jìn)行,而HAVING子句則是在分組操作之后應(yīng)用，HAVING的語(yǔ)法和WHERE的語(yǔ)法類(lèi)似，HAVING可以包含聚合函數(shù)，這也從一個(gè)方面說(shuō)明HAVING子句是在分組操作之后進(jìn)行的。

4、??????? 連接查詢(xún)的內(nèi)連接、外連接、交叉連接

1)????? 內(nèi)連接

內(nèi)連接根據(jù)每個(gè)表共有的列的值來(lái)匹配兩個(gè)表中的列，只有每個(gè)表中都存在相同的記錄才會(huì)出現(xiàn)在結(jié)果集中，在內(nèi)連接中，兩個(gè)表沒(méi)有主次之分。在內(nèi)連接中，出了用‘=’還可以使用INNER JOIN來(lái)定義表內(nèi)部的連接關(guān)系，內(nèi)連接是咱們最常用的一種連接了，以下兩種方式得到的結(jié)果是一樣的：

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子句左側(cè)的表為主表，主表中所有的記錄都出現(xiàn)在結(jié)果集當(dāng)中。左外連接可以使用LEFT OUTER JOIN或者LEFT JOIN關(guān)鍵字定義左外連接，以下三個(gè)查詢(xún)語(yǔ)句得到的結(jié)果是一致的，需要重點(diǎn)關(guān)注（+）是加在從表的后面的哦！

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）子句右側(cè)的表為主表，主表中的所有的記錄都將出現(xiàn)在結(jié)果集中，如果主表中的記錄在左表中沒(méi)有匹配的記錄，則結(jié)果集中右表的記錄為NULL，右外連接可以使用RIGHT OUTER JOIN或者RIGHT JOIN來(lái)定義，以下三個(gè)操作的結(jié)果是一致的：

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;

③??? 完整外部連接

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

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

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

結(jié)果如下：

3)????? 交叉連接

SELECT * FROM emp CROSS JOIN dept;

結(jié)果如下:

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

5、??????? EXISTS關(guān)鍵字的使用

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

IN到EXISTS的轉(zhuǎn)換：

--使用IN來(lái)寫(xiě)的代碼，Oracle先轉(zhuǎn)換為多表連接，如果不能轉(zhuǎn)換，則先執(zhí)行SELECT DEPTNO FROM DEPT WHERE DEPT.DEPTNO = 1部分，然后執(zhí)行外面的那一塊

SELECT *

? FROM EMP

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

--使用EXITS來(lái)實(shí)現(xiàn)的兩個(gè)表之間的連接，Oracle先執(zhí)行外面的循環(huán)SELECT * FROM EMP，在此過(guò)程中，去關(guān)聯(lián)檢索DEPT表中的內(nèi)容，根據(jù)EXISTS子句的內(nèi)容看該列是否在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關(guān)鍵字的使用

使用UNION掛件子組合兩個(gè)查詢(xún)的結(jié)果集，結(jié)合的基本規(guī)則如下：

①??? 所有查詢(xún)中的列數(shù)和列的順序必須要一致

②??? 數(shù)據(jù)類(lèi)型必須要兼容

使用UNION關(guān)鍵字進(jìn)行合并查詢(xún)的時(shí)候，數(shù)據(jù)庫(kù)引擎會(huì)自動(dòng)過(guò)濾掉結(jié)果集中的重復(fù)記錄。

使用UNION ALL關(guān)鍵字進(jìn)行合并查詢(xún)的時(shí)候，數(shù)據(jù)庫(kù)引擎就不會(huì)過(guò)濾結(jié)果集中的重復(fù)數(shù)據(jù)，因此在執(zhí)行效率上將UNION ALL比UNION要高上很多。

7、??????? SELECT DECODE語(yǔ)句的使用

在新農(nóng)保系統(tǒng)中，經(jīng)常會(huì)用到SELECT DECODE語(yǔ)句，例如查詢(xún)?cè)撊耸悄?，還是女？如果代碼表SELECT * FROM AA10 WHERE AAA100=UPPSER(‘’);的結(jié)果集比較小，就沒(méi)有必要再關(guān)聯(lián)上一張代碼值表（視情況），直接使用DECODE函數(shù)：

SELECT AAC001,

????????? AAC002,

????????? AAC003,

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

? FROM AC01;

? DECODE函數(shù)的語(yǔ)法如下：

?DECODE (<輸入值>,<值1>,<結(jié)果1>[,<值2>,<結(jié)果2>…][,<默認(rèn)結(jié)果>])

?說(shuō)明:

?如果輸入值等于值1,則DECODE函數(shù)返回值1,如果輸入值等于值2,則DECODE函數(shù)返回值2,依次類(lèi)推.如果參數(shù)列表中沒(méi)有輸入值相等的,則DECODE的函數(shù)返回默認(rèn)結(jié)果。

8、??????? 在SELECT語(yǔ)句中使用CASE函數(shù)

SELECT

CASE

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

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

???? ELSE empname

???? END

FROM emp;

查詢(xún)的結(jié)果：

使用CASE函數(shù)可以實(shí)現(xiàn)與DECODE同樣的功能，但是CASE函數(shù)有更強(qiáng)大的作用，尤其是在搜索、統(tǒng)計(jì)功能方面，語(yǔ)法如下：

CASE?

WHEN <邏輯表達(dá)式1> THEN <結(jié)果1>

[WHEN<邏輯表達(dá)式2> THEN <結(jié)果2> …]

[ELSE <默認(rèn)結(jié)果>]

END

9、??????? 保存查詢(xún)結(jié)果、在正式庫(kù)修改數(shù)據(jù)之前備份要操作的表

我們?cè)陂_(kāi)庫(kù)修改某一個(gè)表的數(shù)據(jù)的時(shí)候，除了要保存執(zhí)行的腳本，以及保存相應(yīng)的文檔的同時(shí)，也需要丟該表進(jìn)行備份工作。從而能夠在萬(wàn)一出現(xiàn)錯(cuò)誤的時(shí)候，保留恢復(fù)的依據(jù)，這是一個(gè)比較好的習(xí)慣。例如：

我們即將對(duì)AC43表進(jìn)行UPDATE或者DELETE操作，除了保存相應(yīng)的執(zhí)行腳本，還應(yīng)當(dāng)對(duì)AC43表進(jìn)行整體的備份。備份語(yǔ)句如下：

CREATE TABLE ac43_20110816 AS SELECT * FROM ac43;

這樣就在數(shù)據(jù)庫(kù)中建立了一張新表ac43_20110816。

CREATE TABLE語(yǔ)句的格式如下：

CREATE TABLE <新的表名> AS

<SELECT 子句>

<FROM 子句>

<WHERE 子句>……

10、???? 修改數(shù)據(jù)的時(shí)候，避免唯一性約束列

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

ALTER TABLE emp

ADD CONSTRAINT uk_empno

UNIQUE(empno);

然后對(duì)EMP的EMPNO列進(jìn)行UPDATE或者INSERT操作:

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

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

都會(huì)有一個(gè)提示:

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

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

11、???? 修改數(shù)據(jù)的時(shí)候，不能違法檢查性約束

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

ALTER TABLE emp

ADD CONSTRAINT ck_empno CHECK(empno<10);

然后執(zhí)行以下語(yǔ)句:

?

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

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

則會(huì)出現(xiàn)一個(gè)提示:

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

12、???? 修改數(shù)據(jù)的時(shí)候，不能違反外鍵約束

首先準(zhǔn)備數(shù)據(jù):

①??? UPDATE emp SET deptno=2 WHERE empno=7; 目的是防止在創(chuàng)建外鍵的時(shí)候,找不到dept對(duì)應(yīng)的主鍵。

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

③??? ALTER TABLE emp

ADD CONSTRAINT fk_emp

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

進(jìn)行如下操作：

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

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

則會(huì)有如下提示產(chǎn)生：

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

13、???? 如何刪除表中的數(shù)據(jù)（TRUNCATE）

可以使用DELETE語(yǔ)句刪除表中的所有的數(shù)據(jù)。DELETE語(yǔ)句的語(yǔ)法結(jié)構(gòu)如下：

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

例如刪除EMP和DEPT表中的數(shù)據(jù)：

DELETE FROM DEPT WHERE DEPTNO<4;

DELETE FROM EMP WHERE EMPNO<3;

如果要?jiǎng)h除表中的所有的記錄，而不記錄單個(gè)行的刪除操作的話，就可以使用TRUNCATE TABLE操作。TRUNCATE TABLE語(yǔ)句的語(yǔ)法結(jié)構(gòu)如下：

TRUNCATA TABLE <表名>

需要注意的事情是：

①??? 曾經(jīng)新農(nóng)保系統(tǒng)中的幾張LOG表的數(shù)據(jù)量非常的大（上億條），最開(kāi)始的時(shí)候使用DELETE進(jìn)行刪除，速度非常的慢，使用了大概十幾二十分鐘,而且最后需要COMMIT。最后在確定了該表沒(méi)有任何價(jià)值之后，使用TRUNCATE TABLE 的方式，速度非常快，大概需要不到一分鐘的時(shí)間就完成了該表內(nèi)容的刪除。

②??? TRUNCATE使用有危險(xiǎn)，因?yàn)槟J(rèn)COMMIT，一旦刪除，不容易找回。使用DELETE的時(shí)候，請(qǐng)務(wù)必 帶上、帶對(duì)條件?。??

?

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

?

Oracle的DML語(yǔ)言必備基礎(chǔ)知識(shí)