内容


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

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

修改数据和模式

Comments

系列内容:

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

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

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

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

简介

系列 的前几篇文章已经:

  • 介绍了 Apache Derby 数据库。
  • 介绍了 ij 工具。
  • 演示了如何创建数据库模式、设计关系数据库表以及将数据插入表中。
  • 演示了如何通过编写 SQL 查询提取数据。

尚未介绍的一个重要任务是如何修改现有数据。本文介绍 SQL DELETEUPDATE 语句,您可以使用它们有选择地删除或修改 Apache Derby 数据库中的现有数据。

要进行本文的示例,您需要:

  1. 具有可工作的 Apache Derby 数据库安装,本系列的 第一篇文章 有介绍。
  2. 熟悉 Apache Derby ij 命令行工具,本系列的 第二篇文章 有介绍。
  3. 具有正确初始化的 Bigdog 的 Surf Shop 示例数据库,本系列的 第四篇文章第五篇文章 有详细介绍。
  4. 熟悉 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 INSERTDELETE 语句的联合,因为您必须选择要修改的行,还必须指定如何修改它们。形式上,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 语句修改该特定行的 pricestockDate 列。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 的供应商获得的所有产品的 pricedescription 列。因为不能在 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 操作符和表子查询
操作符示例描述
INitemNumber IN(表子查询)如果表达式的值在表子查询中,则返回 TRUE,该表子查询只能返回单个列。可以包括 NOT 操作符,如 NOT IN,以仅选择不在表查询中的行。
EXISTSEXISTS(表子查询)如果表子查询返回任何行,则返回 TRUE,如果没有选择任何行,则返回 FALSE。这意味着,取决于表子查询选择的行数,将修改所有行或不修改任何行。可以包括 NOT 操作符以反转该规则。
ALLitemNumber = ALL(表子查询)称为量化比较,因为 ALL 关键字修改比较操作符(=<><=>=<> 之一),所以仅当对所有行都为真时,结果才为 TRUE。该表子查询可以返回多个行,但它们必须只有一个列。
ANYitemNumber = ANY (表子查询)另一个量化比较,但是在这个查询中,如果它对于任一行为真,则结果为 TRUESOME 可以用作 ANY 的同义词。该表子查询可以返回多个行,但它们必须只有一个列。

通过使用 表 2 中的信息,应该看到如果您重新编写 清单 4UPDATE 语句中的 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 0count 列分配默认值 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 数据库。


相关主题


评论

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

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