資料維護 5 在瞭解資料庫查詢的各種應用以後, 這一章說明資料庫的新增 修改與刪除 包含新增資料的 INSERT 與 REPLACE 敘述, 修改資料的 UPDATE 敘述, 還有刪除資料的 DELETE 與 TRUNCATE 敘述 5.1 取得表格資訊 在執行資料維護的時候, 例如新增一筆資料到指定的表格, 應該要清楚知道表格的詳細資訊, 包含表格欄位的名稱 型態 順序和預設值, 還有表格的主索引鍵 這些資訊都會影響資料維護敘述的寫法, 如果違反表格的設計架構, 就可能會產生錯誤或儲存錯誤的資料 DESCRIBE 是 MySQL 資料庫提供的指令, 它只能在 MySQL 資料庫中使用, 這個指令可以取得指定表格的結構資訊, 下面是它的語法 :
5-2 MySQL 新手入門超級手冊 執行 DESC cmdev.dept 指令以後,MySQL 會傳回 cmdev.dept 表格 的結構資訊 : 根據上列查詢後得到的結果,cmdev.dept 表格總共有三個欄位, 分別是 depatno dname 與 location,deptno 是儲存部門編號的數字欄位, dname 與 location 是儲存部門名稱與地點的文字欄位 部門編號與名稱欄位不可以儲存 NULL 值, 地點欄位可以儲存 NULL 值 這個表格把 deptno 欄位設定為主索引鍵, 所以部門編號的值不可以重複 每一個表格在設計的時候, 都會決定它有哪一些欄位和各自的詳細設定 另外也會決定表格中的欄位順序, 在新增資料的時候需要明確知道表格的欄位順序 :
第 5 章資料維護 5-3 5.2 新增資料 MySQL 提供 INSERT 與 REPLACE 兩種新增資料到指定表格的敘述, 一般新增資料的需求可以使用 INSERT 敘述 INSERT 搭配 IGNORE 與 ON DUPLICATE KEY UPDATE 子句, 還可以完成一些特殊的新增資料需求 REPLACE 敘述的語法跟 INSERT 非常類似, 不過它同時具有新增或修改資料的功能 接下來的內容會詳細說明新增敘述的語法和應用 5.2.1 基礎新增敘述 需要新增資料到資料庫的表格中使用 INSERT 敘述, 下面它的基本語法 : 使用這個語法新增紀錄的時候, 要特別注意表格的欄位個數與順序, 下列 的新增敘述會新增一筆部門資料到 cmdev.dept 表格中 : 除了明確指定新增紀錄的每一個欄位資料外, 你也可以使用 DEFAULT 關鍵字, 讓 MySQL 使用在設計表格的時候為欄位指定的預設值 下列的新增敘述同樣會新增一筆部門資料到 cmdev.dept 表格中, 不過部門的所在位置 (location) 欄位值指定為使用預設值 :
5-4 MySQL 新手入門超級手冊 使用這種語法新增紀錄的時候, 如果在 VALUES 後面提供的資料個數與欄 位個數不一樣的話, 就會發生錯誤 : 資料個數雖然沒有錯, 順序卻不對了, 也有可能會造成錯誤 : 新增敘述的另外一種語法提供比較靈活的作法, 你可以自己指定新增紀錄 的欄位個數和順序 :
第 5 章資料維護 5-5 在額外為這個新增敘述指定欄位以後, 指定儲存資料的時候, 就要依照自 己指定的欄位個數與順序 : 如果沒有依照自己指定的欄位個數與順序, 就會發生錯誤 : 因為這種新增敘述的語法可以自己指定欄位的個數與順序, 所以只要指定 寫入欄位的資料就可以了 不過要特別注意下列兩種語法的差異 : 因為上面說明的規定, 下列這個新增敘述在語法上雖然沒有錯誤, 可是違 反表格設計上的規定, 所以執行敘述以後會發生錯誤 :
5-6 MySQL 新手入門超級手冊 這種新增敘述的語法還有一種比較特別的用法, 如果你要新增的紀錄, 所有欄位的值都要使用預設值, 就可以使用下面說明的寫法 不過要特別注意, 下列的新增敘述執行以後會造成錯誤, 因為 deptno 與 dname 欄位的預設值是 NULL, 可是它們又不能儲存 NULL : 下列是新增敘述的第三種語法 : 這種語法只是提供你另外一種新增紀錄的寫法, 下列兩個新增敘述的效果是一樣的 :
第 5 章資料維護 5-7 5.2.2 同時新增多筆紀錄 上列說明的新增敘述在執行以後, 如果沒有發生任何錯誤, 都只會新增一筆紀錄到指定的表格 如果需要的話, 你也可以使用一個 INSERT 敘述新增多筆紀錄, 差異只有在 VALUES 子句後面新增資料的指定: 如果需要新增下列三個員工資料到 cmdev.emp 表格中: empno ename job manager hiredate salary comm deptno 8001 SIMON MANAGER 7369 2001-02-03 3300 NULL 50 8002 JOHN PROGRAMMER 8001 2002-01-01 2300 NULL 50 8003 GREEN ENGINEER 8001 2003-05-01 2000 NULL 50 你可以分別執行三個新增敘述, 將三個員工資料新增到 cmdev.emp 表 格中 也可以使用下列說明的作法, 這個敘述執行以後會一次新增三筆紀錄 :
5-8 MySQL 新手入門超級手冊 5.2.3 索引值 欄位 : 在設計表格的時候, 通常會視需要指定表格中的某一個欄位為 主索引 一個表格除了可以設定 主索引 欄位外, 資料庫還提供其它幾種不同的索引 索引的應用與設定會在後面的內容詳細說明 如果一個表格設定某一個欄位為主索引, 在新增紀錄的時候就不可以違反主索引的規定, 否則會產生錯誤 : 你可以在使用 INSERT 敘述的時候, 加入 IGNORE 關鍵字, 它可以 在執行一個違反主索引規定的新增敘述時, 自動忽略新增的動作, 這樣就不會產生錯誤訊息了 :
第 5 章資料維護 5-9 5.2.4 索引值與 ON DUPLICATE KEY UPDATE 使用 INSERT 敘述新增紀錄的時候, 還可以依照需求在最後搭配使用 ON DUPLICATE KEY UPDATE, 它可以用來指定在違反重複索引值的規定時要執行的修改工作 : 需要為 INSERT 敘述搭配 ON DUPLICATE KEY UPDATE 的情況會比較特殊一些, 所以接下來使用 cmdev.travel 這個表格來討論它的用法 cmdev.travel 是員工資料庫用來儲存出差資料的表格, 每一個員工到某個地方出差的資料都會儲存在這裡 :
5-10 MySQL 新手入門超級手冊 因為這個表格的設計方式, 所以如果要處理編號 7900 的員工到 BOSTON 出差資料的話, 你就要執行下列的動作 : 你會發現要處理員工出差資料, 需要搭配查詢 新增與修改三種敘述, 才 可以正確的完成這樣的需求 使用搭配 ON DUPLICATE KEY UPDATE 的 INSERT 敘述, 可以讓處理這類需求的敘述比較簡單一些 : 這個 INSERT 敘述執行以後, 資料庫會幫你執行需要的檢查, 根據檢查 的結果執行不同的動作 如果目前表格沒有重複的資料 :
第 5 章資料維護 5-11 檢查資料以後, 如果發現已經有重複的資料 : 5.2.5 REPLACE 敘述 除了使用 INSERT 敘述新增紀錄外, REPLACE 敘述同樣可以新增 紀錄, 它們的語法幾乎相同 : INSERT 敘述的另一種語法也可以換成 REPLACE 敘述 :
5-12 MySQL 新手入門超級手冊 會使用 REPLACE 敘述新增紀錄的原因, 主要還是考慮索引值的情況 REPLACE 敘述在沒有違反索引值的規定時, 效果跟 INSERT 敘述完全一樣, 同樣會新增紀錄到表格中 在發生重複索引值的時候, INSERT 敘述會發生錯誤 : INSERT 敘述搭配 IGNORE 關鍵字的時候 : 同樣的情況改用 REPLACE 敘述的話, 它會執行修改紀錄的動作 :
第 5 章資料維護 5-13 5.3 修改資料 法 : 修改已經儲存在表格中的紀錄使用 UPDATE 敘述, 下列是它的基本語 使用 UPDATE 敘述的時候, 通常會搭配使用 WHERE 子句, 用來指 定要修改的紀錄 : 所以在執行 UPDATE 敘述的時候, 通常會依照實際的需求, 正確設定 修改的條件 以下列兩個修改敘述來說, 它們執行後的差異是很大的 : 5.3.1 搭配 IGNORE 在使用 UPDATE 敘述的時候, 可以依照需求加入 IGNORE 關鍵字, 它可以防止錯誤的修改敘述出現錯誤訊息 :
5-14 MySQL 新手入門超級手冊 除了因為主索引鍵造成的問題, 另外也要注意修改多個欄位值的情況 首 先是沒有使用 IGNORE 關鍵字的時候, 錯誤的資料會在執行修改敘述的時 候產生錯誤訊息, 所以也不會執行任何修改的動作 : 同樣的修改敘述加入 IGNORE 關鍵字, 執行後的結果就不一樣了 :
第 5 章資料維護 5-15 5.3.2 搭配 ORDER BY 與 LIMIT 執行修改的時候使用 WHERE 子句是一般最常見的作法, 在處理一些比 較特殊的修改需求時, 也會搭配 ORDER BY 與 LIMIT 子句 : LIMIT 子句也可以在查詢敘述中使用, 不過在 UPDATE 敘述中使 用 LIMIT 子句只能夠指定一個數字 : 以同樣為員工加薪一百的需求來說, 搭配 ORDER BY 與 LIMIT 子 句, 可以完成許多不同的需求 :
5-16 MySQL 新手入門超級手冊 5.4 刪除資料 刪除表格中已經不需要的紀錄, 可以使用 DELETE 與 TRUNCATE 敘述 單獨使用 DELETE 敘述的時候, 可以刪除表格中的所有記錄, 搭配其它子句可以刪除特定的記錄 下列是 DELETE 敘述的語法 : 使用 DELETE 敘述的時候, 通常也會使用 WHERE 子句設定要刪除 哪些紀錄 : 執行刪除的時候也可以搭配 ORDER BY 與 LIMIT 子句 :
第 5 章資料維護 5-17 如果要刪除一個表格中所有的紀錄, 你可以選擇使用 TRUNCATE 敘述, 下列是它的語法 : 要執行刪除表格中所有的紀錄, 下列兩個敘述的效果是一樣的 : TRUNCATE 敘述在執行刪除紀錄的時候, 會比使用 DELETE 敘述 的效率好一些, 尤其是表格中的紀錄非常多的時候會更明顯
5-18 習題 選擇題 ( ) 1. 選擇可以查詢 country 表格結構資訊的敘述 : A. SHOW country B. LIST country C. DESC country D. HELLO country ( ) 2. 假設沒有索引值與其它問題, 選擇可以正確新增紀錄的敘述 : A. ADD dept (deptno,dname,location) VALUES (99,'HELLO','Antarctica') B. NEW dept (deptno,dname,location) VALUES (99,'HELLO','Antarctica') C. APPEND dept (deptno,dname,location) VALUES (99,'HELLO','Antarctica') D. INSERT INTO dept (deptno,dname,location) VALUES (99,'HELLO','Antarctica') ( ) 3. 假設沒有索引值與其它問題, 選擇可以正確修改紀錄的敘述 : A. UPDATE dept SET dname='hello' B. MODIFY dept SET dname='hello', C. CHANGE dept SET dname='hello' D. ALTER dept SET dname='hello' ( ) 4. 根據下列的敘述 : TRUNCATE dept 選擇正確的說明 : A. 刪除 dept 表格所有紀錄 B. 刪除 dept 表格一筆紀錄 C. 使用 DELETE FROM dept 敘述才可以刪除 dept 表格所有紀錄 D. 以上皆非
5-20 習題 實作題 1. 啟動 MySQL Workbench 並連線到 MySQL 資料庫 選擇功能表 File -> Open SQL Script, 開啟 Masoloa/resource/Lab05.sql, 選擇功能表 Query -> Execute(All or Selection) 執行這個檔案提供的 SQL 敘述 2. 參考下面的輸出畫面, 查詢 cmdev.myemp 表格資訊: 3. 使用三個 INSERT 敘述新增下列三個員工資料: id firstname lastname salary birthdate 1 Betty Johnson 1200 1968-1-6 2 Sam Johnson 900 1972-9-12 3 Rose Johnson 1100 1971-11-6