|
Robert Brunner (rb@ncsa.uiuc.edu), NCSA 研究科學(xué)家、天文學(xué)助理教授, University of Illinois, Urbana-Champaign
2006 年 9 月 21 日
Apache Derby 軟件提供了功能強大的開(kāi)發(fā)源碼數據庫，可用作范圍廣泛的數據庫應用程序的持久存儲庫。它受歡迎的主要原因之一是 Apache Derby 的查詢(xún)支持，該支持允許您有選擇地從一個(gè)或多個(gè)表的特定行中提取滿(mǎn)足某個(gè)布爾條件的列。了解 Apache Derby 的查詢(xún)能力以及如何使用 SELECT 語(yǔ)句執行復雜查詢(xún)。
數據庫查詢(xún)
本系列的前一篇文章 用 Apache Derby 進(jìn)行開(kāi)發(fā) —— 取得節節勝利：用 Apache Derby 進(jìn)行數據庫開(kāi)發(fā)，第 3 部分：運行腳本和插入數據，在結束時(shí)運行了一個(gè)腳本，該腳本插入了 10 行，然后顯示這些行進(jìn)行驗證。那篇文章沒(méi)有討論如何選擇顯示的行，因為它側重于介紹如何將數據插入 Apache Derby 數據庫中。本 文的主題是從 Apache Derby 數據庫中選擇和提取數據。在可以執行數據庫查詢(xún)之前，必需創(chuàng )建一個(gè)數據庫，其中包含用相關(guān)數據填充的多個(gè)表。
本文并沒(méi)有假設您具有這樣的一個(gè)數據庫或者要求您完成本系列前幾篇文章中列出的步驟，而是提供了一個(gè)叫做 derby5.build.sql 的 SQL 腳本文件，它捆綁在叫做 derby5.zip 的 .zip 文件（參閱本文后面的 下載 一節）中。該 SQL 腳本文件首先創(chuàng )建一個(gè)數據庫，然后以各自的模式創(chuàng )建兩個(gè)表，在每個(gè)表中插入 10 行，然后顯示兩個(gè)表的內容進(jìn)行驗證。要運行該腳本文件中的命令，可以使用本系列前一篇文章中討論的三種方法之一，或者使用 清單 1 所示的命令。
清單 1. 初始化 Derby 工作區
rb$ mkdir derbyWork
rb$ cd derbyWork/
rb$ unzip ../derby5.zip
Archive: ../derby5.zip
inflating: derby.build.sql
rb$ ls
derby.build.sql
rb$ java org.apache.derby.tools.ij < ../derby.build.sql
ij version 10.1
ij>ij> ERROR 42Y07: Schema 'BIGDOG' does not exist
ij> ERROR 42Y07: Schema 'BIGDOG' does not exist
ij> 0 rows inserted/updated/deleted
ij> 0 rows inserted/updated/deleted
ij> 10 rows inserted/updated/deleted
ij> 10 rows inserted/updated/deleted
ij> ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION
------------------------------------------------------------------------
1 |19.95 |2006-03-31|Hooded sweatshirt
2 |99.99 |2006-03-29|Beach umbrella
3 |0.99 |2006-02-28|
4 |29.95 |2006-02-10|Male bathing suit, blue
5 |49.95 |2006-02-20|Female bathing suit, one piece, aqua
6 |9.95 |2006-01-15|Child sand toy set
7 |24.95 |2005-12-20|White beach towel
8 |32.95 |2005-12-22|Blue-striped beach towel
9 |12.95 |2006-03-12|Flip-flop
10 |34.95 |2006-01-24|Open-toed sandal
10 rows selected
ij> ITEMNUMBER |VENDORNUMB&|VENDORNAME
------------------------------------------------------
1 |1 |Luna Vista Limited
2 |1 |Luna Vista Limited
3 |1 |Luna Vista Limited
4 |2 |Mikal Arroyo Incorporated
5 |2 |Mikal Arroyo Incorporated
6 |1 |Luna Vista Limited
7 |1 |Luna Vista Limited
8 |1 |Luna Vista Limited
9 |3 |Quiet Beach Industries
10 |3 |Quiet Beach Industries
10 rows selected
ij> rb$
|
清單 1 所示的命令創(chuàng )建并更改工作目錄（在本例中為 derbyWork），展開(kāi)包含本文其余部分所需的 SQL 構建命令的 .zip 文件，并使用 ij Apache Derby 交互式 SQL 工具運行腳本文件中的 SQL 命令。雖然您不必執行其中所有命令，但卻需要處理 derby.build.sql 腳本文件，因為它創(chuàng )建兩個(gè)表并用數據填充這些表。
在本例中，您可能會(huì )獲得幾個(gè)錯誤之一。例如，您可能獲得 database exists 錯誤，或者是 清單 1 所示的 schema does not exist 錯誤。這兩個(gè)錯誤都可以安全地忽略。如果獲得其他錯誤，或者沒(méi)有得到包含 10 rows selected 消息的行列表，則發(fā)生了一些必須解決的錯誤。有關(guān)可能出現的問(wèn)題的更多信息，請參閱本系列的第一篇文章 用 Apache Derby 進(jìn)行開(kāi)發(fā) —— 取得節節勝利：Apache Derby 簡(jiǎn)介，或 Apache Derby 網(wǎng)站（參閱本文末尾處的 參考資料 一節中的鏈接）。
選擇數據
在 SQL 編程語(yǔ)言中，執行查詢(xún)的任務(wù)屬于 SELECT 語(yǔ)句。為了提供數據庫應用程序所需的所有查詢(xún)功能，SELECT 語(yǔ)句的能力十分廣泛。下文將介紹 SELECT 語(yǔ)句的基礎知識，它允許您為啟用數據庫的應用程序構建功能強大的查詢(xún)。首先，在下一節中將介紹 SELECT 的形式語(yǔ)法。
SELECT 語(yǔ)句語(yǔ)法
在形式上，SELECT 語(yǔ)句的語(yǔ)法十分簡(jiǎn)單，如 清單 2 所示?；靖袷绞?SELECT ... FROM ... WHERE；您可以從一個(gè)或多個(gè)表的行中選擇您感興趣的滿(mǎn)足特定條件的列。當然，事情可以變得更加復雜。本文將介紹 SELECT 的基本功能，而將比較高級的問(wèn)題留給后續文章。
清單 2. SELECT 語(yǔ)句的形式語(yǔ)法
SELECT [ DISTINCT | ALL ] SelectItem [ , SelectItem ]*
FROM clause
[ WHERE clause ]
[ GROUP BY clause ]
[ HAVING clause ]
|
從 清單 2 的語(yǔ)法中，可以看到基本的 SELECT 語(yǔ)句只需要 SELECT 和 FROM 語(yǔ)句；必須指定要選擇的數據并指明您感興趣的數據的位置。其他內容都是可選的（用方括號表示）。DISTINCT 和 ALL 關(guān)鍵字是可選的限定符，分別用于指明應選擇包含惟一值的行還是選擇所有行。默認情況下，ALL 是隱式指定的，并且每個(gè) SELECT 語(yǔ)句只可以使用一個(gè) DISTINCT 限定符。
在 SELECT 關(guān)鍵字之后，SELECT 語(yǔ)句可以列出多個(gè)列。Apache Derby 目前的限制為 SELECT 關(guān)鍵字之后最多可以有 1,012 個(gè)元素 —— 這意味著(zhù)您可能永遠無(wú)需擔心這個(gè)限制！多個(gè)元素（或者更通俗地說(shuō)，是多個(gè)列名稱(chēng)）用逗號分隔開(kāi)。例如，SELECT a, b, c 選擇三個(gè)列 a、b 和 c。要選擇表中的所有列，可以使用星號 (*) 作為所有列的簡(jiǎn)寫(xiě)。值得注意的重要一點(diǎn)是，任何 SELECT 語(yǔ)句的結果都是 Apache Derby 表，您可以用幾乎與使用更持久的表相同的方式來(lái)使用該表。
SELECT 語(yǔ)句的 FROM 組件指明將從哪個(gè)表（或多個(gè)表）中提取數據。這一節將重點(diǎn)介紹如何從單表中選擇數據；本文中的 最后一節 將介紹表連接和如何從多個(gè)表中進(jìn)行選擇。在這種情況下，要查詢(xún)的表的完全限定名稱(chēng)必須位于 FROM 關(guān)鍵字之后。
SELECT 語(yǔ)句的其他部分都是可選的。但是，在構建第一個(gè)查詢(xún)之前，您應該知道 Apache Derby 對 SELECT 語(yǔ)句組件的求值順序。當 Apache Derby 處理查詢(xún)時(shí)，求值順序是：
FROM 子句
WHERE 子句
GROUP BY 子句
HAVING 子句
SELECT 子句
當您對 Apache Derby 處理查詢(xún)時(shí)執行的過(guò)程進(jìn)行分解時(shí)，該順序十分直觀(guān)。首先必須定位要分析的數據，然后過(guò)濾出感興趣的行。下一步是對相關(guān)行進(jìn)行分組，最后是選擇感興趣的實(shí)際列。
從表中選擇行
為了演示 SELECT 語(yǔ)句，可以提取位于 bigdog 模式中的 products 表中的所有列，如 清單 3 所示。
清單 3. 使用 SELECT 語(yǔ)句提取 Apache Derby 表中的行
rb$ java org.apache.derby.tools.ij
ij version 10.1
ij> connect 'jdbc:derby:test' ;
ij> SELECT * FROM bigdog.products ;
ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION
------------------------------------------------------------------------
1 |19.95 |2006-03-31|Hooded sweatshirt
2 |99.99 |2006-03-29|Beach umbrella
3 |0.99 |2006-02-28|
4 |29.95 |2006-02-10|Male bathing suit, blue
5 |49.95 |2006-02-20|Female bathing suit, one piece, aqua
6 |9.95 |2006-01-15|Child sand toy set
7 |24.95 |2005-12-20|White beach towel
8 |32.95 |2005-12-22|Blue-striped beach towel
9 |12.95 |2006-03-12|Flip-flop
10 |34.95 |2006-01-24|Open-toed sandal
10 rows selected
ij> SELECT * FROM products ;
ERROR 42X05: Table 'PRODUCTS' does not exist.
ij> SELECT price, itemNumber, description FROM bigdog.products ;
PRICE |ITEMNUMBER |DESCRIPTION
------------------------------------------------------------------------
19.95 |1 |Hooded sweatshirt
99.99 |2 |Beach umbrella
0.99 |3 |
29.95 |4 |Male bathing suit, blue
49.95 |5 |Female bathing suit, one piece, aqua
9.95 |6 |Child sand toy set
24.95 |7 |White beach towel
32.95 |8 |Blue-striped beach towel
12.95 |9 |Flip-flop
34.95 |10 |Open-toed sandal
10 rows selected
ij>
|
|
當捷徑出問(wèn)題時(shí)
清單 3 使用星號字符選擇 bigdog.products 表中的所有列，而沒(méi)有顯式地將其列出。這可能是一種十分有用的捷徑，尤其在開(kāi)發(fā)數據庫應用程序時(shí)，但這不是值得推薦的做法。通過(guò)使用捷徑，沒(méi)有顯式指定數據列名稱(chēng)或其順序。在數據庫應用程序中，如果總是假設表中的列名稱(chēng)及其順序是固定的，如果其他人修改了您的應用程序所依賴(lài)的數據庫表，那么您最終會(huì )得到一些微小的 bug。您應該始終在 SELECT 語(yǔ)句中顯式命名數據庫列，并列出您需要的順序。 |
|
在查詢(xún) Apache Derby 中的數據庫之前，必須建立數據庫連接。這要求您啟動(dòng) ij 工具并發(fā)出相應的 connect 命令。在本例中，第一個(gè)查詢(xún)使用 * 縮寫(xiě)選擇 bigdog.products 表中的所有列。這正是在本文開(kāi)始處執行的 SQL 腳本文件中使用的語(yǔ)句。在該示例中，SELECT 語(yǔ)句驗證了表是否已正確創(chuàng )建和加載。第二個(gè) SQL 語(yǔ)句試圖執行完全相同的查詢(xún)，但沒(méi)有指定 products 表的完全限定名稱(chēng)。因為 Apache Derby 無(wú)法定位表，因此產(chǎn)生了一個(gè)錯誤。
最后一條 SQL 語(yǔ)句在 SELECT 關(guān)鍵字之后顯式列出了三個(gè)列 —— price、itemNumber 和 description。這說(shuō)明您可以只提取感興趣的三個(gè)列，還可以使用與數據庫中的順序不同的順序從表中提取它們。顯式列出列是最佳實(shí)踐（參閱 側欄 獲得更多詳細信息）。
WHERE 子句
到目前為止，只選擇了單個(gè)表中所有行的列。就查詢(xún)性能而言，這是十分昂貴的，尤其是當您只需要大型表中的行的子集時(shí)。更有效的方法是通過(guò)在 WHERE 子句中放置條件來(lái)過(guò)濾數據庫行，在 FROM 子句中指定表之后會(huì )立即對 WHERE 子句求值。本文其余部分將討論通過(guò)使用 WHERE 子句啟用的一些基本功能，其中包括選擇滿(mǎn)足布爾條件的行的能力，以及連接多個(gè)表以執行更復雜的查詢(xún)的能力。
過(guò)濾行
WHERE 子句最簡(jiǎn)單最常見(jiàn)的用法是在選擇任意列之前過(guò)濾表中的行，如 清單 4 所示。
清單 4. 使用 WHERE 子句過(guò)濾查詢(xún)中的行
ij> SELECT p.itemNumber, p.price FROM bigdog.products AS p
WHERE p.price > 30.00 ;
ITEMNUMBER |PRICE
--------------------
2 |99.99
5 |49.95
8 |32.95
10 |34.95
4 rows selected
ij> SELECT * FROM bigdog.products
WHERE price > 30.00 AND stockDate < '2006-01-01' ;
ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION
------------------------------------------------------------------------
8 |32.95 |2005-12-22|Blue-striped beach towel
1 row selected
ij>
|
本例所示的第一個(gè)查詢(xún)將選擇 bigdog.products 表中 price 列值大于 $30.00 的所有行的 itemNumber 和 price 列。第二個(gè)查詢(xún)將擴展同一查詢(xún)，只選擇 price 列值大于 $30.00 且 stockDate 列值小于 2006 年 1 月 1 日的那些列?？梢酝ㄟ^(guò)使用 Boolean AND 操作符將這兩個(gè)查詢(xún)限制組合在該查詢(xún)中。
可以在 WHERE 子句中執行許多不同的布爾操作。表 1 列出并提供了可以用于查詢(xún)中的基本 SQL 布爾操作的示例。
表 1. 基本 SQL 布爾操作符
| 操作符 |
示例 |
描述 |
= |
p.price = 29.95 |
測試任何內置類(lèi)型是否等于指定值。 |
< |
p.price < 29.95 |
測試任何內置類(lèi)型是否小于指定值。 |
> |
p.price > 29.95 |
測試任何內置類(lèi)型是否大于指定值。 |
<= |
p.price <= 29.95 |
測試任何內置類(lèi)型是否小于等于指定值。 |
>= |
p.price >= 29.95 |
測試任何內置類(lèi)型是否大于等于指定值。 |
<> |
p.price <> 29.95 |
測試任何內置類(lèi)型是否等于指定值。 |
IS NULL |
p.description IS NULL |
測試表達式或值是否為 null。 |
IS NOT NULL |
p.description IS NOT NULL |
測試表達式或值是否非 null。 |
AND |
(p.price > 29.92) AND (p.itemNumber > 5) |
測試兩個(gè)表達式是否都為真或者值為非零。 |
OR |
(p.price > 29.92) OR (p.itemNumber > 5) |
測試兩個(gè)表達式的一個(gè)或二者是否為真或值為非零。 |
NOT |
NOT v.vendorNumber = 1 |
測試表達式是否為假或值為零。 |
BETWEEN |
p.price BETWEEN 29.95 AND 39.95 |
測試一個(gè)值是否包含于兩個(gè)其他值之間（示例等價(jià)于 29.95 <= p.price <= 39.95）。 |
LIKE |
v.vendorName LIKE 'Lun%' |
測試字符表達式是否與模式相匹配，其中百分比字符 (%) 匹配零個(gè)或多個(gè)任意字符，下劃線(xiàn)字符 (_) 只匹配一個(gè)任意字符。 |
第一個(gè)查詢(xún)還引入了 AS 子句，該子句可用于創(chuàng )建表同義詞（table synonym）。在這些示例中，為完全限定表名稱(chēng) bigdog.products 定義了同義詞 p。通過(guò)定義同義詞，可以使用更短的符號表示表數量。當只在查詢(xún)中引用一個(gè)表時(shí)，這似乎并不重要，但下一節將介紹如何在查詢(xún)中將多個(gè)表連接在一起；在這種情況下，提供表同義詞將十分有用。還可以使用 AS 子句命名查詢(xún)中的選定列，從而允許您控制如何顯式結果，這也在下一節中講述。
連接表
WHERE 子句執行的第二個(gè)主要功能是將多個(gè)表連接到單個(gè)表中，從而更易于查詢(xún)。連接多個(gè)表是一種功能強大的技術(shù)，當您處理幾個(gè)大型表時(shí)可能非常復雜。表可以通過(guò)使用 JOIN 關(guān)鍵字顯式地連接，也可以通過(guò)使用 WHERE 子句隱式地連接。
可以通過(guò)使用內連接或外連接來(lái)連接兩個(gè)表。內連接 實(shí)際上是兩個(gè)表的交集，它通過(guò)比較關(guān)鍵列（比如 itemNumber）的值來(lái)匹配表。結果表只包括這兩個(gè)表之間匹配的行。外連接 更像是兩個(gè)表的并集，它通過(guò)比較關(guān)鍵列的值來(lái)匹配表，但不匹配的行仍包括在結果表中，并在適當的時(shí)候用 NULL 值填充。編寫(xiě)使用這些比較高級的表連接的 SQL 查詢(xún)將在后續文章中介紹。
在當前的簡(jiǎn)單模式中，過(guò)程十分簡(jiǎn)單；清單 5 執行 bigdog.products 表和 bigdog.vendors 表之間的隱式內連接。
清單 5. 使用表連接查詢(xún)兩個(gè)表
ij> SELECT p.price, p.description AS "Item",
v.vendorName AS "Vendor"
FROM bigdog.products AS p, bigdog.vendors AS v
WHERE p.itemNumber = v.itemNumber ;
PRICE |Item |Vendor
--------------------------------------------------------------------------------
19.95 |Hooded sweatshirt |Luna Vista Limited
99.99 |Beach umbrella |Luna Vista Limited
0.99 | |Luna Vista Limited
29.95 |Male bathing suit, blue |Mikal Arroyo Incorporated
49.95 |Female bathing suit, one-piece, aqua |Mikal Arroyo Incorporated
9.95 |Child sand toy set |Luna Vista Limited
24.95 |White beach towel |Luna Vista Limited
32.95 |Blue-striped beach towel |Luna Vista Limited
12.95 |Flip-flop |Quiet Beach Industries
34.95 |Open-toed sandal |Quiet Beach Industries
10 rows selected
ij>
|
該查詢(xún)似乎很復雜，這主要是因為它的長(cháng)度。但通過(guò)將其逐行分解，可以容易地看懂所發(fā)生的操作。首先，從 bigdog.products 表中選擇兩列，從 bigdog.vendors 表中選擇一列，并使用 AS 子句命名這些列，以便使用 ij 工具顯示它們。因為查詢(xún)（通過(guò)使用隱式內連接）將兩個(gè)表連接，您可以從兩個(gè)表中選擇列。在 FROM 子句中，列出兩個(gè)表并為其提供別名，以簡(jiǎn)化完整的 SQL 語(yǔ)句。在 WHERE 子句中，通過(guò)顯式指示 Derby 數據庫引擎只從 itemNumber 列中具有匹配值的兩個(gè)表中選擇行，提供了用于連接兩個(gè)表的邏輯。處理查詢(xún)時(shí)，Derby 數據庫引擎首先提取查詢(xún)中第一個(gè)表（在本例中為 bigdog.products）中的所有行，然后在查詢(xún)的第二個(gè)表（在本例中為 bigdog.vendors）中查找在 itemNumber 列中具有匹配值的那些行。
結束語(yǔ)
本文介紹了 SELECT 語(yǔ)句，并展示了如何恰當地將它與 Apache Derby 結合使用來(lái)選擇和提取數據庫中的數據。SELECT 語(yǔ)句的完整功能十分復雜，但基本概念允許您使用隱式內連接執行從一個(gè)或多個(gè)表中提取多個(gè)列的高級查詢(xún)。下一篇文章將討論 SELECT 語(yǔ)句提供的一些更高級的功能，它們允許您計算 SQL 語(yǔ)句中的數量、更改數據類(lèi)型并對得到的數據進(jìn)行排序。
下載
| 描述 |
名字 |
大小 |
下載方法 |
| Derby SQL scripts for this article |
derby.build.sql |
1KB |
HTTP |
參考資料
學(xué)習
- 您可以參閱本文在 developerWorks 全球站點(diǎn)上的 英文原文 。
- 閱讀本系列的其他文章：
- 請在 Apache Derby 項目的站點(diǎn)上參閱關(guān)于如何使用 Apache Derby 數據庫的 手冊。
- 閱讀詳細介紹如何 下載和安裝 Apache Derby 的 Apache Derby 項目教程。
- 學(xué)習如何正確地 檢驗下載。
- 請訪(fǎng)問(wèn) developerWorks Apache Derby 項目區，獲得文章、教程和其他參考資料，這些可以幫助您馬上開(kāi)始使用 Derby。
- 請訪(fǎng)問(wèn) developerWorks 開(kāi)放源碼專(zhuān)區，獲得豐富的入門(mén)信息、工具和項目更新，這些可以幫助您用開(kāi)放源碼技術(shù)進(jìn)行開(kāi)發(fā)，并將這些技術(shù)與 IBM 產(chǎn)品結合使用。
- 請閱讀關(guān)于 IBM Cloudscap? 數據庫的 文章和教程，它是用 Apache Derby 代碼庫構建的。
- 瀏覽 developerWorks 開(kāi)放源碼專(zhuān)區上的所有 Apache 文章 和 免費 Apache 教程。
- 在 Safari 書(shū)店 上瀏覽關(guān)于這些主題和其他技術(shù)主題的圖書(shū)。
獲得產(chǎn)品和技術(shù)
討論
關(guān)于作者
|
|
|
|
Robert J. Brunner 是 National Center for Supercomputing Applications 的研究科學(xué)家，也是位于 Urbana-Champaign 的伊利諾斯大學(xué)的天文學(xué)助理教授。他出版過(guò)多部著(zhù)作，發(fā)表過(guò)主題廣泛的文章和教程。
|
|
