第 8 章 資料的 新增、修改與刪除.

Presentation on theme: "第 8 章 資料的 新增、修改與刪除."— Presentation transcript:

1 第 8 章 資料的 新增、修改與刪除

2 本章提要 8-1 使用 SQL Server Management Studio 管理工具編輯資料 8-2 新增記錄 ― INSERT 敘述
8-3 簡易查詢 ― SELECT 敘述初體驗 8-4 用查詢結果建立新資料表 ― SELECT INTO 8-5 更新記錄 ― UPDATE 敘述 8-6 刪除記錄 ― DELETE 與 TRUNCATE TABLE

3 8-1 使用 SQL Server Management Studio 管理工具編輯資料

4 開啟及查詢資料表 一看到要執行查詢, 可能馬上就有人反應『那是不是要先設計查詢啊？』
不急, 此處執行查詢的目的, 純粹是為了顯示資料表中的記錄以進行編輯, 只要利用 SQL Server Management Studio 管理工具內建的查詢就夠了。 首先開啟要查詢的資料表：

5 開啟及查詢資料表

6 開啟及查詢資料表

7 開啟及查詢資料表 開啟資料表後, 請在工具列執行『檢視 / 屬性視窗』命令開啟屬性窗格, 我們可以在屬性窗格內設定查詢的條件。
查詢條件大致有查詢前幾筆資料列和查詢前百分之幾的資料列 2 種。 查詢前幾筆資料列 當資料筆數不多時, 可以使用這個查詢條件, 指定查詢資料表的前幾筆記錄：

8 開啟及查詢資料表

9 開啟及查詢資料表 查詢前百分之幾的資料列 另一個條件則可以查詢所有資料的前百分之幾筆資料：(如下頁)
結果窗格除可以了顯示資料查詢的結果, 還可以在此進行資料的編輯。

10 開啟及查詢資料表

11 在結果窗格編輯資料時的限制 當顯示查詢結果後, 理應可以開始進行資料編輯的工作, 但在這之前, 我們必須先提醒您在結果窗格中編輯資料的一些限制, 免得您 "動輒得咎", 破壞了編輯資料的雅興。

12 在結果窗格編輯資料時的限制 整個查詢結果的限制 首先您必須確定, 查詢結果是否可以接受編輯。
因為有些情況我們是無法直接在查詢結果上變更資料內容的, 例如沒有變更資料表的權限、查詢結果沒有足夠的資訊可以辨識出每一筆記錄 (常發生在沒有顯示 Primary key 欄位的查詢結果上) ... 。 還有些情況目前不方便列出 (因為我們暫時還不會遇到), 不過各位可以到 SQL Server 線上叢書搜尋 "更新結果的規格" 標題, 以取得完整的說明。

13 在結果窗格編輯資料時的限制 欄位的限制 確定查詢結果可以接受編輯後, 再來就是注意欄位的限制了：
有些資料型別的欄位是不允許在結果窗格中輸入或修改的, 像 timestamp 以及 binary 類型 (包括 binary、varbinary、image 三種型別)。遇到這種欄位, 只要忽略就可以了。 若欄位的值可以自動產生, 請不要費事去更動它。像計算欄位、設定識別 (IDENTITY) 屬性的欄位、利用 NEWID() 函數自動產生值的 uniqueidentifier 型別的欄位。

14 在結果窗格編輯資料時的限制 再來就是自己要小心了, 若您企圖輸入任何不符合欄位定義 (包括資料型別、長度、屬性、各項條件約束設定) 的值, 都是會被打回票的。 建議您在編輯資料之前, 先在物件總管窗格中欲編輯的資料表上按滑鼠右鈕執行『修改』命令, 查看一下各欄位的定義內容比較保險。

15 在結果窗格編輯資料時的限制 其實, 各位對於上述所說的限制不用太在意, 因為除了資料庫設計者會因測試的需要而直接在查詢結果上編輯資料外, 一般的資料庫使用者很少會接觸到, 所以您只要讓自己在測試時, 不要出錯就行了。

16 在結果窗格中的編輯技巧 想必已經有人等得不耐煩了, 我們現在馬上就為您介紹在結果窗格中修改、新增以及刪除記錄的方法。 修改現有記錄內容
修改現有記錄內容的操作步驟如下, 此處以練習 01 的客戶資料表為例。 請在物件總管窗格選取客戶資料表, 再按滑鼠右鈕執行『開啟資料表』命令, 開啟資料表：

17 在結果窗格中的編輯技巧 1). 先將插入點移到欲修改的欄位上。假若查詢結果上的記錄很多, 您可以利用下方的快速移動工具來幫助您：

18 在結果窗格中的編輯技巧 2). 修改欄位值。這裏順便介紹幾項編輯小技巧及注意事項：
若想修改欄位中的幾個字, 而此時卻顯示選取整個欄位值, 可以按一下 F2 鍵, 插入點就會出現了。 在 "允許 NULL" 的欄位中, 若要將值改成 NULL, 請按 Ctrl + O 鍵, 則儲存後, 該欄位會顯示 <NULL>。注意！將欄位值完全清除並不會填入 <NULL>。 若有其它資料表需參照到您要修改的欄位值, 該欄位值將不允許修改。但若您要編輯的資料表將更新規則屬性設為重疊顯示, 便沒有這個限制。 3). 修改完畢, 將插入點移到其它筆記錄上 (或關閉查詢設計視窗), 便可將剛才所做的變更儲存起來。

19 復原所做的更動 在 "步驟 3", 也就是儲存之前, 利用 Esc 鍵可以取消 "步驟 2" 所做的更動：
復原單一欄位：若只要取消某一欄位的修改, 則將插入點放在該欄位中, 然後按 鍵。 復原整筆記錄：若要取消整筆記錄的修改 (可能已更動多個欄位值), 則將插入點放在該筆記錄沒有被改過的欄位上, 然後按 Esc 鍵；或者直接連按兩次 Esc 鍵即可復原整筆記錄。

20 出現警告訊息怎麼辦？ 在結果窗格中編輯資料, 最大的好處是可以立即看到編輯結果, 若有錯誤, 也會立即被警告！但是當您被警告訊息纏上時, 該怎麼處理呢？ 若是誤改 "不允許編輯" 或 "會自動產生值" 的欄位, 請按下警告訊息的確定鈕關閉訊息窗, 然後迅速離開那些欄位。 若是筆誤, 則關閉警告訊息後, 可立即訂正, 或按 Esc 鍵復原。

21 在結果窗格中的編輯技巧 新增記錄 要在查詢結果上新增記錄, 請參照下面的步驟：
1). 將插入點移到查詢結果最底部的空白列上。或者, 按查詢結果底下的移至新資料列鈕, 也可快速到達最底部的空白列。

22 2). 輸入各欄位值。輸入的技巧、注意事項和修改欄位值差不多, 這裏再補充幾點：
若要將欄位值設為 NULL (假設該欄位 “允許 NULL”), 您不需要在該欄位輸入任何資料, 因為只要儲存記錄後, 該欄位便會自動填入 NULL 。 若要讓欄位填入預設值 (假設該欄位有設定預設值) , 同樣也不需要在欄位內輸入任何資料, 如此儲存後, 該欄位便會自動填入預設值。

23 在結果窗格中的編輯技巧 刪除記錄 要刪除現有記錄請如下操作：
假如某欄位既 "允許 NULL" , 且設有預設值, 則略過該欄位不填, 儲存後, 該欄位會填入預設值；若是要填入 NULL 值, 則您必須在該欄位上按 Ctrl + O 鍵, 強制輸入。 此步驟所做的任何編輯, 同樣可利用 Esc 鍵復原。 3). 輸入完畢, 將插入點移到其它記錄上 (或關閉結果窗格), 該筆記錄便儲存起來了。 刪除記錄 要刪除現有記錄請如下操作：

24 在結果窗格中的編輯技巧 1). 選取欲刪除的記錄：按一下記錄最左側的灰色按鈕可選取整筆記錄, 按住滑鼠左鈕在灰色按鈕上拉曳, 可選取多筆連續的記錄。 2). 按滑鼠右鈕執行『刪除』命令, 或按 Delete 鍵, 此時螢幕會出現如下訊息： 3). 按是鈕, 選取的記錄就被刪除掉了。

25 8-2 新增記錄 - INSERT 敘述 T-SQL 在資料編輯方面也提供了許多敘述, 首先介紹為資料表新增記錄的 INSERT 敘述。
基本用法 INSERT/SELECT INSERT/EXEC

26 基本用法 INSERT 敘述的基本語法如下： INTO：此參數純粹是為了增加整個敘述的可讀性而已, 用不用都沒關係。
table_name：要新增記錄的資料表名稱。 column_list：列出預備要輸入值的欄位名稱, 欄位名稱之間請用逗號相隔。此處若沒有指定任何欄位, 則表示資料表中的所有欄位。

27 基本用法 data_values：列出要填入欄位中的值, 值與值之間須用逗號隔開。此處必須和 column_list 互相對應, 亦即若 column_list 列出 3 個欄位名稱, 這裏也要列出 3 筆欄位值。欄位值可用 NULL 或 DEFAULT 來指定, 表示要填入 NULL 值或預設值。 現在我們就利用上述的語法, 替練習 01 資料庫中的圖書室借用記錄資料表新增記錄。為方便各位對照欄位的屬性, 先將圖書室借用記錄資料表的結構列示如下：

28 基本用法 底下即利用 INSERT 敘述為圖書室借用記錄資料表新增兩筆記錄：

29 基本用法

30 基本用法 在剛才那兩組 INSERT 敘述中, 各位應該可以發現, 有些欄位被省略了, 可是觀察結果竟沒有一個欄位是空的, 為什麼呢？對於那些沒有被指定資料的欄位, SQL Server 是這樣處理的： 如果欄位設定了識別 (IDENTITY) 屬性, 那麼將填入自動編號, 如編號欄。 如果欄位有設定預設值, 則填入預設值, 如數量欄。 如果欄位 "允許 NULL" , 則填入 NULL, 如附註欄。 若前幾項都不符合時, 則會顯示錯誤訊息而取消操作, 不輸入任何資料。

31 手動輸入識別 (IDENTITY) 屬性的欄位值
有設定識別屬性的欄位, 其值會因自動編號而產生, 不需我們手動輸入。 如果需要在這種屬性的欄位手動輸入資料, 則要在 SQL 敘述中將資料表的 IDENTITY_INSERT 選項設為 ON。當 IDENTITY_INSERT 選項設為 ON 時, 在 INSERT 敘述中就必須明確將值指定給 IDENTITY 欄位, 否則會導致錯誤。 而輸入完後, 最好在 SQL 敘述中將 IDENTITY_INSERT 選項設為 OFF 關閉。

32 手動輸入識別 (IDENTITY) 屬性的欄位值

33 INSERT/SELECT 將 INSERT 敘述搭配 SELECT 敘述, 可以從某一個資料表中取出現成的資料, 並接著將這些資料輸入到另一個資料表中, 而且一次可新增多筆記錄。 其語法如下：

34 INSERT/SELECT SELECT 敘述的功用是 “查詢” ― 就是可以從資料庫中挑出符合特定條件的資料。
運用 INSERT/SELECT 這組敘述時, 要注意 SELECT 敘述的查詢結果必須與 column_list 列出的欄位互相對應, 例如 column_list 列出 A、B 兩個欄位名稱, 那傳回的查詢結果就要能夠配合這兩個欄位的各項設定, 如資料型別、長度 ... 等, 否則會產生錯誤訊息而被取消整個操作。

35 INSERT/SELECT 有關 SELECT 敘述的用法, 下一節以及第 9 章會有比較詳細的介紹。這裏我們先做個簡單的示範, 讓各位了解資料是怎麼 "取" 怎麼 "入", 待熟悉 SELECT 敘述的用法後, 各位再自行發揮。 下面是練習01 資料庫的圖書室借用記錄與書籍資料表現有的結構及內容：

36 INSERT/SELECT

37 INSERT/SELECT 現在我們要從書籍資料表取出編號小於 4 的書籍名稱, 然後輸入到圖書室借用記錄資料表中：

38 INSERT/SELECT

39 INSERT/EXEC 在前文我們介紹了用 INSERT 敘述搭配 SELECT 敘述, 一次新增多筆記錄到資料表中。而我們接下來要介紹的 INSERT 敘述搭配 EXECUTE 敘述 (可簡寫為 EXEC), 也可以達到相同的效果。 其語法如下：

40 INSERT/EXEC INSERT/EXEC 這組敘述的用法和 INSERT/SELECT 很像, 只是這裏我們要注意的是, EXEC 敘述傳回的執行結果須與 column_list 的欄位相對應。 底下我們以系統預存程序 “sp_helpdb” 來做個簡單的範例。sp_helpdb 可以查詢指定的資料庫或所有資料庫的相關資訊。 以下我們即利用 sp_helpdb 查詢所有資料庫的相關資訊, 並將這些資訊存入一個暫存資料表中, 其範例如下：

41 INSERT/EXEC

42 8-3 簡易查詢 ― SELECT 敘述初體驗 由於進行資料處理時, 很少不牽涉到查詢的動作, 所以在此我們先對查詢敘述, 也就是 SELECT 敘述, 做個簡單的介紹。 SELECT 敘述的用法很廣泛, 若要將它的語法完整列出, 可能有很多人一時會吃不消, 所以我們做了些簡化。底下是經過簡化後的語法：

43 簡易查詢 ― SELECT 敘述初體驗 select_list：列出要顯示的欄位名稱, 欄位名稱之間請用逗號相隔。可用 * 代表資料表的所有欄位。 table_source：欲查詢的資料表名稱。 search_condition：查詢的條件。

44 基本用法 現在我們就利用上述的語法, 從圖書室借用記錄資料表中找出員工編號為 2 的圖書借用記錄：

45 多資料表的查詢 除了在一個資料表中查詢, SELECT 敘述還可以從多個相關 (不一定要建立關聯) 的資料表中取出資料, 用法相當有彈性。接著就來看看多資料表查詢的範例。 下面是練習 01 資料庫員工資料表的內容：

46 多資料表的查詢 假如我們想要從圖書室借用記錄及員工資料表中, 找出所有 '劉天王' 曾經借用的書名、數量、歸還日期, 和劉天王的電話, 則可以如下查詢：

47 多資料表的查詢 上面的敘述中設了兩項條件：經由『圖書室借用記錄.員工編號 = 員工.編號』這個條件, 可以傳回二資料表中員工編號相同的記錄。再加上『姓名 = '劉天王'』這個條件, 則最後便只傳回 "劉天王" 的借書資料了。

48 設定資料表及欄位的別名 在 SELECT 敘述中, 我們可以替資料表取別名以方便使用, 或替查詢結果的欄位取別名以變更輸出的欄位名稱。例如將前一個範例修改如下：

49 8-4 用查詢結果建立新資料表 ― SELECT INTO
運用 SELECT INTO 敘述可以建立新資料表, 同時將 SELECT 的查詢結果輸入到新資料表中, 一舉兩得。其語法如下：

50 用查詢結果建立新資料表 ― SELECT INTO
我們現在就利用這組敘述, 從練習 01 資料庫的圖書室借用記錄及員工資料表取出相關資料, 另外建立一個新資料表：(如下頁) 用過 SQL Server 7.0 的讀者可能知道, 以前 SELECT INTO 敘述可直接建立暫存資料表, 若要建立永久性的資料表, 則必須先將資料庫的 select into/bulkcopy 選項設為 true 才行。但是 SQL Server 2000 及2005已經不需要這麼做了, 上面的範例就是最佳的舉證。

51 用查詢結果建立新資料表 ― SELECT INTO

52 複製資料表結構 將 SELECT INTO 敘述中的 WHERE 條件固定為 False, 可以用來複製某個資料表結構 (但不包括條件約束的設定, 以及資料表中的資料), 另外成立一個新的資料表。底下的例範中, 我們將複製員工資料表的結構, 另外產生一個聯絡名冊資料表：(如下頁) 您也可以在物件總管窗格中的資料表項目上, 按右鈕執行『重新整理』命令, 就能看到剛剛新增的聯絡名冊資料表。

53 複製資料表結構

54 8-5 更新記錄 ― UPDATE 敘述 T-SQL 的 UPDATE 敘述可以一次更新多筆記錄, 其語法如下：
從上面的語法中, 各位應可看出, SET 子句就是用來設定欄位的新值。

55 更新記錄 ― UPDATE 敘述 其參數說明如下： column_name：指定欲變更的欄位名稱。
expression：指定新的欄位值, expression 可以是一個常數、運算式、變數 ... 等。 DEFAULT：使用 DEFUALT, 可將 column_name 的欄位值重新設為預設值。 NULL：使用 NULL, 可將 column_name 的欄位值重新設為 NULL。

56 基本應用 假設我們想將圖書室借用記錄資料表中, 所有員工編號為 '3' 的記錄, 都改為 '6', 並將附註欄的內容改為 NULL。我們先看看原本的資料表：

57 基本應用 接著執行以下敘述進行更新：

58 基本應用 請注意, 由於 UPDATE 敘述可以一次更改多筆記錄, 因此在設定 WHERE 條件時請特別小心。

59 基本應用 在設定新值時, 我們還可以引用同一欄位或是其他欄位的值來做變化。例如我們想再將所有 '編號 6' 員工所借的數量都加 5, 並註記於附註欄中, 則可以執行以下敘述：

60 引用其它資料表的值來更新 在 UPDATE 敘述中加上 FROM 子句, 可引用其他資料表的欄位值來更新, 例如：
我們要在圖書室借用記錄中新增一個附註欄位, 用來記錄借書人的姓名。我們可以從員工資料表得到需要的資料：

61 引用其它資料表的值來更新 最後提醒您, 在前面的『修改現有記錄內容』部份曾經提到, 若有其它資料表需參照到欲修改的欄位值, 則該欄位值將不允許修改。 但若要編輯的資料表將更新規則屬性設為重疊顯示, 便沒有這個限制。 利用 UPDATE 敘述更新欄位值時, 同樣也要注意這些事項。

62 8-6 刪除記錄 ― DELETE 與 TRUNCATE TABLE
最後介紹 T-SQL 中兩個用來刪除資料表記錄的敘述：DELETE 敘述與 TRUNCATE TABLE 敘述。 DELETE 敘述 TRUNCATE TABLE 敘述

63 DELETE 敘述 若要刪除資料表中的部份記錄, 請使用 DELETE 敘述, 其語法如下： 下圖是練習 01 的圖書室借用記錄資料表：

64 DELETE 敘述

65 DELETE 敘述 假設我們想將資料表中, 借用 'Word 手冊' 的記錄都刪除掉, 則可以執行下列敘述：

66 DELETE 敘述 在 DELETE 敘述中加上 FROM 子句, 還可以引用其他資料表的值來做為刪除的條件, 其語法如下：

67 DELETE 敘述 例如我們想在圖書室借用記錄資料表中, 將 ‘楊咩咩’ 借用的記錄都刪除掉, 則必須先在員工資料表中找到楊咩咩的員工編號, 再到圖書室借用記錄中將對應的員工編號的記錄都刪掉。 不過透過 DELETE FROM 敘述, 則可以用更簡便的方式來操作, 直接引用員工資料表中的記錄來做為刪除的條件：

68 DELETE 敘述

69 TRUNCATE TABLE 敘述 TRUNCATE TABLE 敘述可一次就刪除掉資料表中的所有記錄, 其語法如下： 例如：