.Net程式員學用Oracle系列(12)：增刪改查

1、 " 插入語句 " 1.1、 "INSERT" 1.2、 "INSERT ALL" 2、 " 刪除語句 " 2.1、 "DELETE" 2.2、 "TRUNCATE" 3、 " 更新語句 " 3.1、 "UPDATE" 3.2、 "帶子查詢的 UPDATE" 3.3、 "一次更新多個欄位的 UP ...

1、插入語句
- 1.1、INSERT
- 1.2、INSERT ALL
2、刪除語句
- 2.1、DELETE
- 2.2、TRUNCATE
3、更新語句
- 3.1、UPDATE
- 3.2、帶子查詢的 UPDATE
- 3.3、一次更新多個欄位的 UPDATE
4、查詢語句
- 4.1、SELECT
- 4.2、帶子查詢的 SELECT
- 4.3、查詢語句的各子句詳解
5、總結

1、插入語句

INSERT 語句用來向表、分區或視圖中添加行。可以向單個表或者多個表中添加數據行。單表插入將會向一個表中插入一行數據，這行數據可以顯示地列出插入值也可以通過一個子查詢來獲取。多表插入將會向一個或多個表中插入行，並且會通過子查詢獲取值來計算所插入行的值。

1.1、INSERT

如果你要插入表中所定義的所有欄位的值，那麼欄位的列表是可選的。但是如果你只想提供部分欄位的值，則必須在欄位的列表中指明所需的欄位名。建議即便要插入所有欄位的值，也明確給出欄位列表。語法：

INSERT INTO target_table(column1, column2, ... column_n) VALUES(expression1, expression2, ... expression_n);

或

INSERT INTO target_table(column1, column2, ... column_n)
SELECT expression1, expression2, ... expression_n FROM source_table [WHERE conditions];

示例：

-- 向 t_staff 表添加一條員工數據
INSERT INTO demo.t_staff(staff_name, dept_code, gender, birthday, edu_bg, base_salary, post_salary, post_code)
VALUES('李陽','010101',1,TO_DATE('1989-01-14','yyyy-mm-dd'),2,2500,8000,'P50');

1.2、INSERT ALL

語法：

INSERT [ALL/FIRST]
  WHEN <expression> THEN INTO target_table1(column1, column2, column_n)
  WHEN <expression> THEN INTO target_table2(column1, column2, column_n)
  ELSE INTO target_table3(column1, column2, column_n)
SELECT <column list> FROM source_table;

或

INSERT ALL
  INTO target_table1(column1, column2, column_n) VALUES(expr1, expr2, expr_n)
  INTO target_table2(column1, column2, column_n) VALUES(expr1, expr2, expr_n)
  INTO target_table3(column1, column2, column_n) VALUES(expr1, expr2, expr_n)
SELECT * FROM DUAL;

示例：

-- 向 t_field_enum 表中添加一條部門數據，同時向 t_staff 表添加一條員工數據
INSERT ALL
  INTO demo.t_field_enum(field_code, enum_code, enum_name, enum_level, enum_order)
  VALUES('DEPT','010203','實施三部',3,3)
  INTO demo.t_staff(staff_name, dept_code, gender, birthday, edu_bg, base_salary, post_salary, post_code)
  VALUES('徐來','010202',1,TO_DATE('1991-04-01','yyyy-mm-dd'),1,2500,5200,'P50')
SELECT * FROM DUAL;

同步插入：有時候我們需要把一張表中的數據分別插入到另外兩張表中，如果不加事物僅寫兩條 INSERT 語句分別添加是有問題的，因為表中數據完全有可能在第一條語句執行之後第二條語句執行之前發生了變化。如要將 t_staff 表中月薪達到 8000 的 90 員工分別添加到 t_staff_high 和 t_staff_young 兩個表中，示例：

INSERT ALL
  INTO demo.t_staff_high(staff_id, staff_name, dept_code, gender)
  INTO demo.t_staff_young(staff_id, staff_name, dept_code, gender)
SELECT t.staff_id, t.staff_name, t.dept_code, t.gender FROM demo.t_staff t
WHERE t.birthday>=TO_DATE('1990-01-01','yyyy-mm-dd') AND (t.base_salary+t.post_salary)>=8000;

有條件的插入：使用 INSERT ALL 語句還可以根據條件將數據插入到不同的表中。示例：

INSERT ALL
  WHEN test_value<8 THEN INTO demo.t_staff_high(staff_id, staff_name, dept_code, gender)
  WHEN test_value<7 THEN INTO demo.t_staff_low(staff_id, staff_name, dept_code, gender)
  ELSE INTO demo.t_staff_copy(staff_id, staff_name, dept_code, gender)
SELECT t.staff_id, t.staff_name, t.dept_code, t.gender FROM demo.t_staff t WHERE t.post_salary>=5000;

在上面的語句中，如果 test_value=6，崗位工資達到 5000 的員工會被插入到 t_staff_high 和 t_staff_low 兩張表中；如果 test_value=7，崗位工資達到 5000 的員工會被插入到 t_staff_low 表中；如果 test_value=8，崗位工資達到 5000 的員工會被插入到 t_staff_copy 表中。

假如 test_value=6，也就是前兩個條件都滿足時，優先執行第一個插入，當第一個條件不滿足時，再來判斷是否要執行第二個條件的插入，這種情況只需把 ALL 換成 FIRST 即可實現。

2、刪除語句

2.1、DELETE

DELETE 語句用來從表中移除數據行。該語句的語法結構由 3 部分組成：DELETE、FROM 和 WHERE。DELETE 關鍵字是單獨列出的，沒有其它選項與 DELETE 關鍵字相結合（hint除外）。FROM 子句用來指定要從那個表中刪除數據行。WHERE 子句提供篩選條件有助於確定那些行是要刪除的。語法：

DELETE FROM target_table [WHERE conditions];

示例：

-- 把 t_staff_high 表中名字叫“韓三”的職員刪除
DELETE FROM demo.t_staff_high t WHERE t.staff_name='韓三';

2.2、TRUNCATE

TRUNCATE 語句是用來清空表中數據的。從錶面上看它的效果與不帶 WHERE 條件的 DELETE 語句效果是一樣的。它有 4 個特點，分別是：

1、刪除表全部內容，但保留表結構；
2、速度快，但不可回滾，這意味著數據刪除後無法再恢復；
3、觸發器中不能使用，因為 TRUNCATE 語句不會觸發任何操作；
4、DELETE 語句每刪除一行數據都是一個事務，會產生若幹“日誌”。但 TRUNCATE 語句是釋放整個數據頁（一個頁 8K）。

TRUNCATE TABLE target_table
[ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ][ DROP STORAGE | REUSE STORAGE ];

如要把 t_staff_high 表中所有數據全部刪除，示例：

TRUNCATE TABLE t_staff_high;

3、更新語句

3.1、UPDATE

UPDATE 語句的作用是改變表中原有行的欄位值。這個語句的語法由3部分組成：UPDATE、SET 和 WHERE。UPDATE 子句用來指定要更新的表，SET 子句用來指明那些欄位要被改變以及改變後的值，WHERE 子句用來指定要更新的行。語法：

UPDATE target_table
SET column1 = expression1,
    column2 = expression2,
    ...
    column_n = expression_n
[WHERE conditions];

示例：

-- 給所有女性職工的工資加 100
UPDATE demo.t_staff t SET t.post_salary=t.post_salary+100 WHERE t.gender=0;

從語法的角度來說，UPDATE 語句完全可以不帶條件，但實際需求中基本上都有條件。我曾維護過某醫院的 EMR 系統，有一天醫生反饋說，他們科室所有患者的年齡顯示都一樣，我仔細檢查之後發現是因為修改患者信息的 SQL 語句沒有 WHERE 條件。後來我問一個老員工相關代碼怎麼會那麼爛，他說當初這塊功能是讓一幫還沒畢業的研究生做的，可能他們不太懂 SQL 語句。我只想說，這種低級錯誤即便是專科實習生也不應該犯，因為會犯這種錯誤至少能說明3個問題，首先這人根本沒搞明白需求或業務邏輯太差，需求是要改某一個患者的信息；其次 SQL 基礎也很差；再次他可能太過自信或不負責任，發佈後的代碼中還有這麼嚴重的 Bug 說明他自己沒有認真測試過。當然了，那個公司也沒有測試人員，不過這是另一個層面的問題了。

3.2、帶子查詢的 UPDATE

UPDATE 語句內部可以包含子查詢，常見的有兩種情況，一種是僅 WHERE 子句中帶有查詢，這種情況和帶子查詢的 SELECT 語句類似，可參考下文；另一種是 SET 子句中帶有查詢，本節主要介紹這種情況。語法：

UPDATE target_table
SET column1 = (SELECT expression1
               FROM source_table
               WHERE conditions)
[WHERE conditions];

儘管這個語法看起來很簡單，但初次接觸 Oracle 的開發人員極易寫錯，本人在工作中發現不少工齡超過 5 年的開發人員依然會用錯這個語法（沒寫最後那個可選的 WHERE 條件，結果導致表中不符合查詢條件的數據全都被改成了 NULL），當我口頭指出這個問題的時候他們往往還不以為然，直到我把測試結果亮出來他們才恍然大悟。

我分析之所以會這樣主要有兩方面原因，一方面很多人會習慣性的認為，查詢語句的 WHERE 條件里已經和要更新的目標表關聯過了，那更新的時候自然就會只更新 WHERE 條件限定的這部分數據呀！其實這隻是一廂情願的想法，因為他們忽略了這個查詢其實是個行間查詢。另一方面，實際開發中必須用這個語法的情況很少，而低頻應用開發中偶爾用一次，即便有問題只要當時沒發現，被坑的也是下一個人（我就是那個被坑了 N 多次的人）。所以會有很多工作多年的開發人員依然不太理解這個語法。

假如發現 t_staff_salary 表中研發一部的員工固定工資計算錯了，需要重新計算一下，示例：

UPDATE demo.t_staff_salary t1 
SET t1.fixed_salary=(SELECT t2.base_salary+t2.post_salary 
                     FROM demo.t_staff t2 
                     WHERE t2.staff_id=t1.staff_id AND t2.dept_code='010101')
WHERE t1.dept_code='010101';

如果把上述語句中最後一個 WHERE 條件去掉，再次執行就會把除研發一部以外的其它部門員工固定工資都改成 NULL。

3.3、一次更新多個欄位的 UPDATE

一次更新多個欄位，這個說法看上去沒有什麼稀奇之處，最普通的 UPDATE 語句的 SET 子句後面本身就可以寫多個欄位。如果特殊點，其中一個欄位的值來自另一張表的某個欄位呢？上一節已經說過，寫個子查詢就能搞定。但如果再特殊一點，其中兩個甚至多個欄位來自另一張甚至多張表呢？語法：

UPDATE target_table
  SET(column1,column2,column3...) = (
  SELECT expression1,expression2,expression3...
    FROM source_table
    WHERE conditions)
[WHERE conditions];

舉個例子，假如公司人員異動和調薪後，員工表 t_staff 中的數據已經更新，但 t_staff_salary 表中的部門和工資欄位尚未同步更新，示例：

UPDATE demo.t_staff_salary t1 SET(t1.dept_code,t1.fixed_salary)=(
  SELECT t2.dept_code,t2.base_salary+t2.post_salary FROM demo.t_staff t2 WHERE t1.staff_id=t2.staff_id
);

當然，有些朋友可能會想到下麵這種更新方式，我在不知道上述語法時也經常用下麵這種方式，示例：

TRUNCATE TABLE demo.t_staff_salary;
INSERT INTO demo.t_staff_salary(staff_id,staff_name,dept_code,fixed_salary) 
SELECT t.staff_id,t.staff_name,t.dept_code,t.base_salary+t.post_salary FROM demo.t_staff t;

這種方式一般也是行的，但畢竟要更新整張表，某些時候表中部分欄位里的數據其實是不能刪的，那就不能用這種方式了。

4、查詢語句

SELECT 語句用來從一個或多個表中或者其它資料庫對象中提取數據。要想寫出符合語法規則的高效 SQL 語句，需要理解 SQL 語句是如何執行的。一個查詢語句在邏輯上的處理方式可能會與實際物理處理過程大相徑庭。Oracle 基於查詢成本的優化器(cost-based optimizer，CBO)用來產生實際的執行計劃。

4.1、SELECT

語法：

SELECT expressions FROM target_table [WHERE conditions];

示例一：

-- 查詢所有 90 後女員工的姓名
SELECT t.staff_name FROM demo.t_staff t WHERE t.gender=0 AND t.birthday>=TO_DATE('1990-01-01','yyyy-mm-dd');

示例二：

-- 查詢所有 90 後女員工的姓名和部門
SELECT t1.staff_name,t2.enum_name dept_name
FROM demo.t_staff t1
LEFT JOIN demo.t_field_enum t2 ON t1.dept_code=t2.enum_code AND t2.field_code='DEPT'
WHERE t1.gender=0 AND t1.birthday>=TO_DATE('1990-01-01','yyyy-mm-dd');

4.2、帶子查詢的 SELECT

WHERE 子句帶子查詢：如要查詢開發部所有員工的姓名和生日。示例：

SELECT t1.staff_name,t1.birthday
FROM demo.t_staff t1
WHERE t1.dept_code IN(SELECT t3.enum_code FROM demo.t_field_enum t3 WHERE t3.parent_enum_id=2);

FROM 子句帶子查詢：如要查詢所有部門的崗位工資之和。示例：

SELECT DISTINCT t2.enum_name dept_name,t3.total_post_salary
FROM demo.t_staff t1
LEFT JOIN demo.t_field_enum t2 ON t1.dept_code=t2.enum_code AND t2.field_code='DEPT'
LEFT JOIN(SELECT s1.dept_code,SUM(s1.post_salary) total_post_salary
          FROM demo.t_staff s1 GROUP BY s1.dept_code) t3 ON t1.dept_code=t3.dept_code;

SELECT 子句帶子查詢：如要查詢所有部門的人數。示例：

SELECT DISTINCT t2.enum_name dept_name,(SELECT COUNT(1) FROM demo.t_staff s1 
                                        WHERE s1.dept_code=t1.dept_code) total_staff
FROM demo.t_staff t1
LEFT JOIN demo.t_field_enum t2 ON t1.dept_code=t2.enum_code AND t2.field_code='DEPT';

4.3、查詢語句的各子句詳解

查詢語句完整語法：

SELECT DISTINCT <select list>
FROM <left source object> <join type> JOIN <right source object> ON <on predicates>
WHERE <where predicates>
GROUP BY <group by list>
HAVING <having predicates>
ORDER BY <order by list>;

1>、FROM 子句列出了所查詢數據的源對象。這個子句可以包含表、視圖、物化視圖、分區或子分區，或者你可以建立一個子查詢來生成子對象。如果使用了多個源對象，其邏輯處理階段也將會應用到每一個聯結類型以及謂語 ON。
2>、WHERE 子句提供了一種方法，可以按照條件來限制查詢最終返回結果集的行數。每個條件或者謂語都是以兩個值或表達式相比較的形式出現的。比較的結果要麼是匹配（值為TRUE）要麼是不匹配（值為FALSE）。如果比較的結果是 FALSE，那麼相應的行不會被包含在最終結果集中。WHERE 子句的目的是限制或者減少結果集。你所使用的限制條件越少，最終返回結果集中包含的數據就會越多。你需要返回的數據越多，執行查詢的時間也就越長。事實上，SQL 中邏輯比較的可能結果是 TRUE、FALSE 以及未知。當其中包含空值(NULL)的時候比較的結果就會是未知。空值與任何值比較或者用在表達式中都會得到空值，或者是未知。一個空值代表一個相應值的缺失，並且可能因為 SQL 語言中的不同部分對空值的處理不同而令人費解。
3>、GROUP BY 子句將執行 FROM 和 WHERE 子句後得到的經過篩選後的結果集進行聚合。查詢出來的結果按照 GROUP BY 子句中列出的表達式進行分組，來為每一個分組得出一行彙總結果。你可以按照 FROM 子句中所列出對象的任意欄位進行分組，即使你並不想在輸出結果中顯示該列。相反，SELECT 列表中任何非聚合欄位都必須包括在 GROUP BY 表達式中。GROUP BY 子句中還可以包含兩個附加的運算：ROLLUP和 CUBE。ROLLUP 運算用來產生部分求和值，CUBE 運算用來求得交互分類值。
4>、HAVING 子句將分組彙總後的查詢結果限定為只有該子句中的條件為真的數據行。事實上，GROUP BY 子句和 HAVING 子句的位置是可以互換的。從本質上來說，HAVING 子句是在 GROUP BY 子句執行後用來篩選彙總值的第二個 WHERE 子句。
5>、SELECT 列表列出查詢的返回最終結果集中需要顯示那些列。這些列可以是數據表中的一個實際的列、一個表達式，或者甚至是一個 SELECT 語句的結果。當使用另外一個 SELECT 語句來產生結果中的一列的值的時候，這個查詢必須只能返回一行一列的值。這種類型的子查詢被稱為標量子查詢。儘管這可能是一個非常有用的語法，但需要牢記於心的是標量查詢在結果集中的每一行結果產生時都要執行一遍。在某些情況下可以進行優化以減少標量子查詢的重覆執行，但更糟糕的場景是每一行都需要標量子查詢執行。你可以想象如果你的結果集中有幾千行甚至上百萬行數據的時候所需要付出的查詢代價！
6>、DISTINCT 子句用於從結果集中刪除重覆項。DISTINCT 子句只能與 SELECT 語句一起使用。
7>、ORDER BY 子句用來對查詢最終返回的結果集進行排序。當輸出結果需要排序的時候，Oracle 必須在其他所有子句都執行完之後按照指定的順序對最終結果集進行排序。需要排序的數據量大小是非常重要的。這裡所說的大小是指結果集中所包含的總位元組數。可以通過用行數乘以每一行的位元組數來估計數據集的大小。每行所包含的位元組數通過將選擇列表中包含的每一列的平均長度相加來確定。較小的排序會完全在記憶體中來實現，而較大的排序將不得不使用臨時磁碟空間來完成。如你可能推斷的那樣，在記憶體中完成排序比使用磁碟的排序要快。因此，當優化器估算排序數據的影響時，它必須要考慮排序數據集的大小，以此來調整如何能夠以有效的方法來獲得查詢的結果。一般來說，排序是查詢過程中開銷相當大的一個處理步驟，尤其是當返回結果集很大的時候。

5、總結

本文主要講述了 4 個特別常用的 SQL 語句，它們分別為 INSERT、DELETE、UPDATE 和 SELECT。基於關係型資料庫如 Oracle 的應用開發中，最常見、最基本的增刪改查（CRUD）操作本質上也是由這 4 個語句來完成的，所以這 4 個語句是 SQL 的重中之重，必須熟練掌握。

本文鏈接：http://www.cnblogs.com/hanzongze/p/Oracle-CRUD.html
版權聲明：本文為博客園博主 韓宗澤 原創，作者保留署名權！歡迎通過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文鏈接！本人初寫博客，水平有限，若有不當之處，敬請批評指正，謝謝！