與創建數據庫模式和用數據填充表一樣，有選擇地修改數據是數據庫開發人員必備的最重要的技能之一。本文教您如何有選擇地刪除或更新現有表中的數據以及如何修改現有表的結構。要對更復雜的數據庫模式執行數據修改，您將通過數據更新和數據插入操作來學習涉及到標量和表的嵌入式子查找。您還將學習如何使用 Apache Derby 數據庫刪除和修改復雜模式中的數據。
簡介
本 系列 的前幾篇文章已經：
- 介紹了 Apache Derby 數據庫。
- 介紹了
ij 工具。
- 演示了如何創建數據庫模式、設計關系數據庫表以及將數據插入表中。
- 演示了如何通過編寫 SQL 查詢提取數據。
尚未介紹的一個重要任務是如何修改現有數據。本文介紹 SQL DELETE 和 UPDATE 語句，您可以使用它們有選擇地刪除或修改 Apache Derby 數據庫中的現有數據。
要進行本文的示例，您需要：
- 具有可工作的 Apache Derby 數據庫安裝，本系列的 第一篇文章 有介紹。
- 熟悉 Apache Derby
ij 命令行工具，本系列的 第二篇文章 有介紹。
- 具有正確初始化的 Bigdog 的 Surf Shop 示例數據庫，本系列的 第四篇文章 和 第五篇文章 有詳細介紹。
- 熟悉 SQL
SELECT 語句的基礎知識，本系列的 第五篇文章 有介紹。
如果您還不具備這些條件，請確保在繼續下文之前完成上述步驟，通過回顧本系列的前幾篇文章可以很容易地實現。
刪除數據
本文將介紹的第一個數據修改技術是刪除數據。要刪除 Apache Derby 數據庫中的數據，可以使用 SQL DELETE 語句，它可以刪除表中的所有行，也可以刪除特定的行子集。可以用于 Apache Derby 數據庫的 SQL DELETE 語句的正式語法相當簡單，如下所示：
DELETE FROM tableName
[WHERE clause]
DELETE 語句從指定表中刪除滿足可選 WHERE 子句的所有行。如果沒有包括任何 WHERE 子句，則刪除表中的所有行。為了演示 DELETE 語句的這種用法，創建一個臨時表，插入幾行，然后全部刪除，如 清單 1 所示。
清單 1. 刪除行
ij> CREATE TABLE bigdog.temp (aValue INT) ;
0 rows inserted/updated/deleted
ij> INSERT INTO bigdog.temp VALUES(0), (1), (2), (3) ;
4 rows inserted/updated/deleted
ij> SELECT COUNT(*) AS COUNT FROM bigdog.temp ;
COUNT
-----------
4
1 row selected
ij> DELETE FROM bigdog.temp ;
4 rows inserted/updated/deleted
ij> SELECT COUNT(*) AS COUNT FROM bigdog.temp ;
COUNT
-----------
0
1 row selected
ij> DROP TABLE bigdog.temp ;
0 rows inserted/updated/deleted
|
本例創建保存整數值的單列臨時表。您將四行插入數據庫中，然后執行 SELECT 語句以驗證新表包含四行。通過使用無約束的 DELETE 語句，將刪除臨時表中的全部四行，這通過來自 Apache Derby 的消息 4 rows inserted/updated/deleted 和第二個 SELECT 語句來驗證，該語句指明臨時表包含 0 行。最后，DROP TABLE 語句刪除模式中的空表。
但是，一般地，您不希望刪除表中的所有行；而是有選擇地刪除行。為此，創建一個適當的 WHERE 子句來標識所有相關行。與 DELETE 一起使用的 WHERE 子句的語法與 第 4 部分 中討論的語法一樣，該部分提供了完全的 SQL SELECT 語句語法。在 WHERE 子句中構造布爾表達式的基本構建塊在那篇文章的表 1 提供，并在本文的 清單 2 中演示，在此您將刪除至少滿足兩個條件之一的所有行。
清單 2. 刪除所選行
ij> DELETE FROM bigdog.products
WHERE description LIKE '%towel%' OR itemNumber <= 3 ;
5 rows inserted/updated/deleted
ij> SELECT itemNumber, description FROM bigdog.products ;
ITEMNUMBER |DESCRIPTION
----------------------------------------------------
4 |Male bathing suit, blue
5 |Female bathing suit, one piece, aqua
6 |Child sand toy set
9 |Flip-flop
10 |Open-toed sandal
5 rows selected
|
在本例中，DELETE 語句包括了一個標識 5 行的 WHERE 子句，您可以通過 ij 工具由 Apache Derby 返回的幫助消息 5 rows inserted/updated/deleted 中看到。WHERE 子句包含由 OR 操作符聯結的兩個表達式，這意味著對于特定行，如果任一表達式值為 TRUE，則將刪除該行。
第一個表達式查找產品描述中包含單詞 “towel” 的所有行。如果回憶本系列前幾篇文章（或者在 DELETE 語句之前執行 SELECT 語句），則 bigdog.products 表中有兩個 towel，其 itemNumber 列值為 7 和 8。另一個表達式選擇 itemNumber 列值小于或等于 3 的所有行。bigdog.products 表的內容最終用一個簡單的 SELECT 語句顯示，展示了只有原來 10 行中的 5 行保留在表中。
您還可以包括 第 5 部分 中討論的 SQL 函數以獲得對刪除行的選擇的更多控制，但是本例中沒有明確演示這些函數的使用。可用于 DELETE 語句的 WHERE 子句中的相同函數和其他操作符還可以與 UPDATE 語句一起使用，從而有選擇地修改表中行的值，如下一節所述。
更新數據
您需要進行的處理數據的最后一個 SQL 任務是更新表中選定行的特定列值。在某種程度上，SQL UPDATE 語句是 SQL INSERT 和 DELETE 語句的聯合，因為您必須選擇要修改的行，還必須指定如何修改它們。形式上，UPDATE 語句語法非常簡單，因為您必須指定要更新的行集合的新的列值，如 清單 3 所示。
清單 3. SQL UPDATE 語句語法
UPDATE tableName
SET columnName = Value
[ , columnName = Value} ]*
[WHERE clause]
|
如該 SQL 語法所示，SQL UPDATE 語句必須至少具有一個 SET 組件來更新一列，以及一個或多個 SET 組件和一個 WHERE 子句，這些是可選的。如果沒有包括 WHERE 子句，則 UPDATE 語句將修改表中所有行的指定列。
執行 UPDATE 語句相當容易，如 清單 4 所示，其中修改了單個行的兩列。
清單 4. 更新所選行
ij> SELECT itemNumber, price, stockDate FROM bigdog.products WHERE itemNumber = 6 ;
ITEMNUMBER |PRICE |STOCKDATE
-------------------------------
6 |9.95 |2006-01-15
1 row selected
ij> UPDATE bigdog.products
SET price = price * 1.25, stockDate = CURRENT_DATE
WHERE itemNumber = 6 ;
1 row inserted/updated/deleted
ij> SELECT itemNumber, price, stockDate FROM bigdog.products WHERE itemNumber = 6 ;
ITEMNUMBER |PRICE |STOCKDATE
-------------------------------
6 |12.43 |2006-06-20
1 row selected
|
本示例在 UPDATE 語句的前后都使用了 SELECT 語句，以證實對目標行的更改。SELECT 語句從 bigdog.products 表選擇了單個行（itemNumber 列值為 6 的行）的三列。UPDATE 語句修改該特定行的 price 和 stockDate 列。price 列中的值增加 25%（例如，可能由于貨品很搶手），stockDate 列被修改以保存當前日期，通過在 SQL 查詢中使用 CURRENT_DATE 內置函數，可以在 Apache Derby 中很容易獲得該日期。
Apache Derby 包括一些內置函數，您可以使用它們獲得與當前數據庫連接相關的數據。這些內置函數的完整列表如 表 1 所示。
表 1. Apache Derby SQL 當前函數
| 函數 |
描述 |
CURRENT_DATE |
以合適的 Apache Derby DATE 格式返回當前日期 |
CURRENT_ISOLATION |
以兩字符字符串返回當前事務處理隔離級別，這將在后續文章中詳細討論 |
CURRENT_SCHEMA |
以最多 128 個字符的字符串返回模式名稱，用于限定未限定的數據庫對象名稱 |
CURRENT_TIME |
以合適的 Apache Derby TIME 格式返回當前時間 |
CURRENT_TIMESTAMP |
以合適的 Apache Derby TIMESTAMP 格式返回當前時間戳 |
CURRENT_USER |
以最多 128 個字符的字符串返回當前用戶的授權標識符，如果沒有當前用戶，則返回 APP |
上例演示了如何修改單個表中特定行的多個列值。但是有時候，用于選擇要更新的行的邏輯比較復雜。例如，假設您需要修改 bigdog.products 表中從 Quiet Beach Industries 中獲得的所有對象的價格，Quiet Beach Industries 在 bigdog.vendors 表中 vendorNumber 列的值為 3。為此，您需要使用嵌入式查詢，如 清單 5 所示。
清單 5. 使用嵌入式 SELECT 更新行
ij> UPDATE bigdog.products
SET price = price * 1.10, description = 'NEW: ' || description
WHERE itemNumber IN
( SELECT v.itemNumber
FROM bigdog.products as p, bigdog.vendors as v
WHERE p.itemNumber = v.itemNumber AND v.vendorNumber = 3 ) ;
2 rows inserted/updated/deleted
ij> SELECT * FROM bigdog.products ;
ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION
------------------------------------------------------------------------
4 |29.95 |2006-02-10|Male bathing suit, blue
5 |49.95 |2006-02-20|Female bathing suit, one piece, aqua
6 |12.43 |2006-06-20|Child sand toy set
9 |14.24 |2006-03-12|NEW: Flip-flop
10 |38.44 |2006-01-24|NEW: Open-toed sandal
5 rows selected
|
在本例中，UPDATE 語句修改從 bigdog.vendors 表中 vendorNumber 列值為 3 的供應商獲得的所有產品的 price 和 description 列。因為不能在 UPDATE 語句中進行簡單聯結，所以必須在 WHERE 子句中包括子查詢，以提取與來自 Quiet Beach Industries 的產品對應的 itemNumber 行。UPDATE 中的 WHERE 子句使用 IN 操作符選擇 itemNumber 列值屬于嵌入式子查詢所選的值集合的那些行。
兩種查詢可以用于 UPDATE 語句的 WHERE 子句中：標量子查詢 和表子查詢。標量子查詢是一種嵌入式查詢，返回包含單個列的單個行，本質上就是單個值，該值稱為標量。可以使用標量子查詢選擇將用于 WHERE 子句的表達式中的特定值。例如，itemNumber = (標量子查詢) 更新 itemNumber 列值與標量子查詢結果匹配的任何行。
另一方面，表子查詢可以返回多個行，這些行通常只有一列。在某些情況下，表子查詢可以包含多個列。要使用表子查詢，需要使用 SQL 操作符將嵌入式查詢與布爾表達式組合在一起。例如，如上一代碼清單所示，IN 操作符選擇 bigdog.products 表中由 Quiet Beach Industries 生產的所有行。IN 操作符是可以用于表子查詢的四個 SQL 操作符之一。全部四個操作符如 表 2 所述。
表 2. Apache Derby SQL 操作符和表子查詢
| 操作符 |
示例 |
描述 |
IN |
itemNumber IN（表子查詢） |
如果表達式的值在表子查詢中，則返回 TRUE，該表子查詢只能返回單個列。可以包括 NOT 操作符，如 NOT IN，以僅選擇不在表查詢中的行。 |
EXISTS |
EXISTS（表子查詢） |
如果表子查詢返回任何行，則返回 TRUE，如果沒有選擇任何行，則返回 FALSE。這意味著，取決于表子查詢選擇的行數，將修改所有行或不修改任何行。可以包括 NOT 操作符以反轉該規則。 |
ALL |
itemNumber = ALL（表子查詢） |
稱為量化比較，因為 ALL 關鍵字修改比較操作符（=、<、>、<=、>= 或 <> 之一），所以僅當對所有行都為真時，結果才為 TRUE。該表子查詢可以返回多個行，但它們必須只有一個列。 |
ANY |
itemNumber = ANY （表子查詢） |
另一個量化比較，但是在這個查詢中，如果它對于任一行為真，則結果為 TRUE。SOME 可以用作 ANY 的同義詞。該表子查詢可以返回多個行，但它們必須只有一個列。 |
通過使用 表 2 中的信息，應該看到如果您重新編寫 清單 4 中 UPDATE 語句中的 WHERE 子句，以使用量化比較和相同的表子查詢 WHERE itemNumber = ANY (...)，您將獲得相同的結果。如果使用 ALL 操作符和相同的表子查詢，則不更新任何行，因為 bigdog.products 表中的所有 itemNumber 值不在表查詢中。另一方面，如果使用 EXISTS 操作符，則將修改所有行，因為至少有一個 itemNumber 值存在于表子查詢中。
修改表模式
上一節討論了修改表中現有的數據。還可以修改數據庫表的結構或模式。這可以采用添加列、更改列的數據類型、添加約束或者甚至刪除列的方式來實現。 該過程并不簡單，所以當您開始設計模式時一定要認真。如果不需要修改表的結構，則需要使用臨時表，如 清單 6 所示。
清單 6. 更新表
ij> CREATE TABLE bigdog.newProducts (
itemNumber INT NOT NULL,
price DECIMAL(5, 2),
stockDate DATE,
count INT NOT NULL DEFAULT 0,
description VARCHAR(40)
) ;
0 rows inserted/updated/deleted
ij> INSERT INTO bigdog.newProducts(itemNumber, price, stockDate, description)
SELECT itemNumber, price, stockDate, description FROM bigdog.products ;
5 rows inserted/updated/deleted
ij> DROP TABLE bigdog.products ;
0 rows inserted/updated/deleted
ij> RENAME TABLE bigdog.newProducts TO products ;
0 rows inserted/updated/deleted
ij> SELECT * FROM bigdog.products ;
ITEMNUMBER |PRICE |STOCKDATE |COUNT |DESCRIPTION
------------------------------------------------------------------------------------
4 |29.95 |2006-02-10|0 |Male bathing suit, blue
5 |49.95 |2006-02-20|0 |Female bathing suit, one piece, aqua
6 |12.43 |2006-06-20|0 |Child sand toy set
9 |14.24 |2006-03-12|0 |NEW: Flip-flop
10 |38.44 |2006-01-24|0 |NEW: Open-toed sandal
5 rows selected
|
如本例所示，要修改表，在本例中是將一個新列 count 添加到 bigdog.products 表中，首先要創建一個具有需要的正確模式的表。本例需要包括列約束 NOT NULL 使之始終具有有效的值，并通過使用列約束 DEFAULT 0 為 count 列分配默認值 0。注意如何通過將列順序列出來合并多列約束。
下一步是將現有數據從原始表復制到新表中。可以通過使用 SQL INSERT 語句來實現，該語句使用一個子查詢來獲得要插入的值。這是一種功能強大的技術，允許您很容易地將現有表的全部或部分復制到第二個表中。
創建新表并復制了合適的數據之后，通過使用 SQL DROP TABLE 語句刪除舊表，并通過使用 SQL RENAME TABLE 語句將新表重命名為原來的名稱。重命名操作十分簡單：將 oldTableName 重命名為 newTableName，但不為新表名提供模式名稱，因為 RENAME 操作不能在不同的數據模式間移動表。本例最后執行 SELECT 語句以顯示新 bigdog.products 表的模式和內容。可以看到，新表具有 5 列，count 列始終為 0。這時，真正的應用程序將通過執行必要的 SQL UPDATE 語句來適當修改 count 列。
結束語
本文專門介紹修改 Apache Derby 數據庫中的數據。討論的第一種數據修改技術是數據刪除，這通過使用 SQL DELETE 語句來執行。然后使用 SQL UPDATE 語句來修改表中選定行的列值。最后，使用臨時表來修改現有數據表的結果。本文還演示了如何通過使用嵌入式子查詢來修改比較復雜的數據庫模式。下一篇文章將介紹其他一些高級的數據庫主題，之后本系列將開始討論如何從 Java 應用程序連接到 Apache Derby 數據庫。