内容


用 Apache Derby 进行开发 —— 取得节节胜利

用 Apache Derby 进行数据库开发,第 4 部分

使用查询选择数据

Comments

系列内容:

此内容是该系列 # 部分中的第 # 部分: 用 Apache Derby 进行开发 —— 取得节节胜利

敬请期待该系列的后续内容。

此内容是该系列的一部分:用 Apache Derby 进行开发 —— 取得节节胜利

敬请期待该系列的后续内容。

数据库查询

本系列的前一篇文章 用 Apache Derby 进行开发 —— 取得节节胜利:用 Apache Derby 进行数据库开发,第 3 部分:运行脚本和插入数据,在结束时运行了一个脚本,该脚本插入了 10 行,然后显示这些行进行验证。那篇文章没有讨论如何选择显示的行,因为它侧重于介绍如何将数据插入 Apache Derby 数据库中。 文的主题是从 Apache Derby 数据库中选择和提取数据。在可以执行数据库查询之前,必需创建一个数据库,其中包含用相关数据填充的多个表。

本文并没有假设您具有这样的一个数据库或者要求您完成本系列前几篇文章中列出的步骤,而是提供了一个叫做 derby5.build.sql 的 SQL 脚本文件,它捆绑在叫做 derby5.zip 的 .zip 文件(参阅本文后面的 下载 一节)中。该 SQL 脚本文件首先创建一个数据库,然后以各自的模式创建两个表,在每个表中插入 10 行,然后显示两个表的内容进行验证。要运行该脚本文件中的命令,可以使用本系列前一篇文章中讨论的三种方法之一,或者使用 清单 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 所示的命令创建并更改工作目录(在本例中为 derbyWork),展开包含本文其余部分所需的 SQL 构建命令的 .zip 文件,并使用 ij Apache Derby 交互式 SQL 工具运行脚本文件中的 SQL 命令。虽然您不必执行其中所有命令,但却需要处理 derby.build.sql 脚本文件,因为它创建两个表并用数据填充这些表。

在本例中,您可能会获得几个错误之一。例如,您可能获得 database exists 错误,或者是 清单 1 所示的 schema does not exist 错误。这两个错误都可以安全地忽略。如果获得其他错误,或者没有得到包含 10 rows selected 消息的行列表,则发生了一些必须解决的错误。有关可能出现的问题的更多信息,请参阅本系列的第一篇文章 用 Apache Derby 进行开发 —— 取得节节胜利:Apache Derby 简介,或 Apache Derby 网站(参阅本文末尾处的 参考资料 一节中的链接)。

选择数据

在 SQL 编程语言中,执行查询的任务属于 SELECT 语句。为了提供数据库应用程序所需的所有查询功能,SELECT 语句的能力十分广泛。下文将介绍 SELECT 语句的基础知识,它允许您为启用数据库的应用程序构建功能强大的查询。首先,在下一节中将介绍 SELECT 的形式语法。

SELECT 语句语法

在形式上,SELECT 语句的语法十分简单,如 清单 2 所示。基本格式是 SELECT ... FROM ... WHERE;您可以从一个或多个表的行中选择您感兴趣的满足特定条件的列。当然,事情可以变得更加复杂。本文将介绍 SELECT 的基本功能,而将比较高级的问题留给后续文章。

清单 2. SELECT 语句的形式语法
SELECT [ DISTINCT | ALL ] SelectItem [ , SelectItem ]*
FROM clause
[ WHERE clause ]
[ GROUP BY clause ]
[ HAVING clause ]

清单 2 的语法中,可以看到基本的 SELECT 语句只需要 SELECTFROM 语句;必须指定要选择的数据并指明您感兴趣的数据的位置。其他内容都是可选的(用方括号表示)。DISTINCTALL 关键字是可选的限定符,分别用于指明应选择包含惟一值的行还是选择所有行。默认情况下,ALL 是隐式指定的,并且每个 SELECT 语句只可以使用一个 DISTINCT 限定符。

SELECT 关键字之后,SELECT 语句可以列出多个列。Apache Derby 目前的限制为 SELECT 关键字之后最多可以有 1,012 个元素 —— 这意味着您可能永远无需担心这个限制!多个元素(或者更通俗地说,是多个列名称)用逗号分隔开。例如,SELECT a, b, c 选择三个列 a、b 和 c。要选择表中的所有列,可以使用星号 (*) 作为所有列的简写。值得注意的重要一点是,任何 SELECT 语句的结果都是 Apache Derby 表,您可以用几乎与使用更持久的表相同的方式来使用该表。

SELECT 语句的 FROM 组件指明将从哪个表(或多个表)中提取数据。这一节将重点介绍如何从单表中选择数据;本文中的 最后一节 将介绍表连接和如何从多个表中进行选择。在这种情况下,要查询的表的完全限定名称必须位于 FROM 关键字之后。

SELECT 语句的其他部分都是可选的。但是,在构建第一个查询之前,您应该知道 Apache Derby 对 SELECT 语句组件的求值顺序。当 Apache Derby 处理查询时,求值顺序是:

  1. FROM 子句
  2. WHERE 子句
  3. GROUP BY 子句
  4. HAVING 子句
  5. SELECT 子句

当您对 Apache Derby 处理查询时执行的过程进行分解时,该顺序十分直观。首先必须定位要分析的数据,然后过滤出感兴趣的行。下一步是对相关行进行分组,最后是选择感兴趣的实际列。

从表中选择行

为了演示 SELECT 语句,可以提取位于 bigdog 模式中的 products 表中的所有列,如 清单 3 所示。

清单 3. 使用 SELECT 语句提取 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>

在查询 Apache Derby 中的数据库之前,必须建立数据库连接。这要求您启动 ij 工具并发出相应的 connect 命令。在本例中,第一个查询使用 * 缩写选择 bigdog.products 表中的所有列。这正是在本文开始处执行的 SQL 脚本文件中使用的语句。在该示例中,SELECT 语句验证了表是否已正确创建和加载。第二个 SQL 语句试图执行完全相同的查询,但没有指定 products 表的完全限定名称。因为 Apache Derby 无法定位表,因此产生了一个错误。

最后一条 SQL 语句在 SELECT 关键字之后显式列出了三个列 —— priceitemNumberdescription。这说明您可以只提取感兴趣的三个列,还可以使用与数据库中的顺序不同的顺序从表中提取它们。显式列出列是最佳实践(参阅 侧栏 获得更多详细信息)。

WHERE 子句

到目前为止,只选择了单个表中所有行的列。就查询性能而言,这是十分昂贵的,尤其是当您只需要大型表中的行的子集时。更有效的方法是通过在 WHERE 子句中放置条件来过滤数据库行,在 FROM 子句中指定表之后会立即对 WHERE 子句求值。本文其余部分将讨论通过使用 WHERE 子句启用的一些基本功能,其中包括选择满足布尔条件的行的能力,以及连接多个表以执行更复杂的查询的能力。

过滤行

WHERE 子句最简单最常见的用法是在选择任意列之前过滤表中的行,如 清单 4 所示。

清单 4. 使用 WHERE 子句过滤查询中的行
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>

本例所示的第一个查询将选择 bigdog.products 表中 price 列值大于 $30.00 的所有行的 itemNumberprice 列。第二个查询将扩展同一查询,只选择 price 列值大于 $30.00 且 stockDate 列值小于 2006 年 1 月 1 日的那些列。可以通过使用 Boolean AND 操作符将这两个查询限制组合在该查询中。

可以在 WHERE 子句中执行许多不同的布尔操作。表 1 列出并提供了可以用于查询中的基本 SQL 布尔操作的示例。

表 1. 基本 SQL 布尔操作符
操作符示例描述
=p.price = 29.95测试任何内置类型是否等于指定值。
<p.price < 29.95测试任何内置类型是否小于指定值。
>p.price > 29.95测试任何内置类型是否大于指定值。
<=p.price <= 29.95测试任何内置类型是否小于等于指定值。
>=p.price >= 29.95测试任何内置类型是否大于等于指定值。
<>p.price <> 29.95测试任何内置类型是否等于指定值。
IS NULLp.description IS NULL测试表达式或值是否为 null。
IS NOT NULLp.description IS NOT NULL测试表达式或值是否非 null。
AND(p.price > 29.92) AND (p.itemNumber > 5)测试两个表达式是否都为真或者值为非零。
OR(p.price > 29.92) OR (p.itemNumber > 5)测试两个表达式的一个或二者是否为真或值为非零。
NOTNOT v.vendorNumber = 1测试表达式是否为假或值为零。
BETWEENp.price BETWEEN 29.95 AND 39.95测试一个值是否包含于两个其他值之间(示例等价于 29.95 <= p.price <= 39.95)。
LIKEv.vendorName LIKE 'Lun%'测试字符表达式是否与模式相匹配,其中百分比字符 (%) 匹配零个或多个任意字符,下划线字符 (_) 只匹配一个任意字符。

第一个查询还引入了 AS 子句,该子句可用于创建表同义词(table synonym)。在这些示例中,为完全限定表名称 bigdog.products 定义了同义词 p。通过定义同义词,可以使用更短的符号表示表数量。当只在查询中引用一个表时,这似乎并不重要,但下一节将介绍如何在查询中将多个表连接在一起;在这种情况下,提供表同义词将十分有用。还可以使用 AS 子句命名查询中的选定列,从而允许您控制如何显式结果,这也在下一节中讲述。

连接表

WHERE 子句执行的第二个主要功能是将多个表连接到单个表中,从而更易于查询。连接多个表是一种功能强大的技术,当您处理几个大型表时可能非常复杂。表可以通过使用 JOIN 关键字显式地连接,也可以通过使用 WHERE 子句隐式地连接。

可以通过使用内连接或外连接来连接两个表。内连接 实际上是两个表的交集,它通过比较关键列(比如 itemNumber)的值来匹配表。结果表只包括这两个表之间匹配的行。外连接 更像是两个表的并集,它通过比较关键列的值来匹配表,但不匹配的行仍包括在结果表中,并在适当的时候用 NULL 值填充。编写使用这些比较高级的表连接的 SQL 查询将在后续文章中介绍。

在当前的简单模式中,过程十分简单;清单 5 执行 bigdog.products 表和 bigdog.vendors 表之间的隐式内连接。

清单 5. 使用表连接查询两个表
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>

该查询似乎很复杂,这主要是因为它的长度。但通过将其逐行分解,可以容易地看懂所发生的操作。首先,从 bigdog.products 表中选择两列,从 bigdog.vendors 表中选择一列,并使用 AS 子句命名这些列,以便使用 ij 工具显示它们。因为查询(通过使用隐式内连接)将两个表连接,您可以从两个表中选择列。在 FROM 子句中,列出两个表并为其提供别名,以简化完整的 SQL 语句。在 WHERE 子句中,通过显式指示 Derby 数据库引擎只从 itemNumber 列中具有匹配值的两个表中选择行,提供了用于连接两个表的逻辑。处理查询时,Derby 数据库引擎首先提取查询中第一个表(在本例中为 bigdog.products)中的所有行,然后在查询的第二个表(在本例中为 bigdog.vendors)中查找在 itemNumber 列中具有匹配值的那些行。

结束语

本文介绍了 SELECT 语句,并展示了如何恰当地将它与 Apache Derby 结合使用来选择和提取数据库中的数据。SELECT 语句的完整功能十分复杂,但基本概念允许您使用隐式内连接执行从一个或多个表中提取多个列的高级查询。下一篇文章将讨论 SELECT 语句提供的一些更高级的功能,它们允许您计算 SQL 语句中的数量、更改数据类型并对得到的数据进行排序。


下载资源


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Open source, Information Management
ArticleID=162931
ArticleTitle=用 Apache Derby 进行开发 —— 取得节节胜利: 用 Apache Derby 进行数据库开发,第 4 部分
publish-date=09212006