Apache Derby数据库开发

转载自：http://hi.baidu.com/zml525/blog/item/fe293efaaa6317859e51468c.html

Derby 数据库默认启动端口为1527

第一篇文章的结尾讨论了如何正确设置系统以使用 Derby 数据库。总之，因为 Derby 是 Java 应用程序，所以必须正确初始化 CLASSPATH 环境变量。可以设置 DERBY_INSTALL 环境变量，然后将相关的 Java Archive (JAR) 文件添加到 shell 初始化文件（比如 .bashrc 文件）中的 CLASSPATH 中，命令如下：

export DERBY_INSTALL='/opt/Apache/db-derby-10.1.2.1-bin'
export CLASSPATH=$DERBY_INSTALL/lib/derby.jar
export CLASSPATH=$CLASSPATH:$DERBY_INSTALL/lib/derbytools.jar:.
通过创建批处理文件可以在 Microsoft Windows? 系统上获得同样的效果，命令如下：

set DERBY_INSTALL=C:\Apache\db-derby-10.1.2.1-bin
set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar
set CLASSPATH=%CLASSPATH%;%DERBY_INSTALL%\lib\derbytools.jar;. 
注意：在这两个示例中，CLASSPATH 环境变量的初始化（通过 export 或 set shell 命令）都用两个步骤完成

当 ij 工具启动时，显示版本信息并提供提示符，提示符默认情况下是一个大于符号 (>)。在该提示符下，运行 connect 命令，连接数据库。connect 命令需要一个指示符字符串用于查找要连接的数据库。在本例中，该连接字符串是 jdbc:derby:test;create=true。正式地，该字符串称为 Java Database Connectivity (JDBC) URL。（JDBC 是一种 Java 技术，允许 Java 应用程序与数据库通信。）

对 JDBC URL 的完全分析超出了本文范围（请参阅 参考资料 部分获得更多信息）。但是，本例十分简单，可分解如下：

jdbc 是 ij 工具与数据库服务器通信的机制。 
derby 是 JDBC 驱动程序类的名称，ji 工具使用该类与数据库服务器通信。 
test 是要创建的数据库名称。 
create=true 是应传递给 Derby JDBC 驱动程序的特定属性。 JDBC 属性列在 URL 其他部分的后面，并用分号分隔。

help;获得 ij 工具的帮助
现在不需要到同一数据库的两个连接，因此可以使用 disconnect ; 命令关闭当前连接。发出另一个 show connections ; 命令将显示当前惟一打开的连接，但还会看到没有当前连接

Derby 风格
这个 文章系列 遵守特定的风格：所有 SQL 命令都用大写，项目名称采用 camelCase 方式，在 camelCase 风格中，单词被连在一起，第一个单词之后的每个单词的第一个字母大写，例如 aLongIdentifier。把这两种风格组合在一起，这些文章写 SQL 命令时的风格是这样：SELECT aLongIdentifier FROM bigdog.dataTable

SQL 数据类型
Derby 中的基本整数数据类型(考虑到篇幅和重点的因素这里略下面有地址可以参考下)

参考：http://www.ibm.com/developerworks/cn/opensource/os-ad-trifecta3

Derby SQL
清单 1. Apache Derby 的 CREATE TABLE 语法
--Comment describing the purpose and layout of the table

CREATE TABLE [schemaName.]tableName (
    { <columnDefinition> | <tableLevelConstraint> } [,
        { <columnDefinition> | <tableLevelConstraint> }
    ]*
) ;
方括号（[ 和 ]）中的是可选参数。从正式语法中可以看出，模式名是可选的，在必需的头一个列之后（创建连一个列都没有的表是没有意义的），其他列定义或表级约束都是可选的。

您可能理解列定义的含义，但是可能不理解约束 的意义。约束有两种类型：表级约束和列约束。约束通过某种方式对列或表进行限制。例如，可以用约束要求列总要有实际的值（没有 NULL 值），或者列中的每个项必须是惟一的，或者列被自动分配默认值。在未来的文章中将更详细地介绍约束。

最后一个结束方括号之后的星号（*）代表可以包含一个或多个包含项。这意味着表必须有一个或多个列级或表级约束。竖线（|）表明 “可有可无” 条件。在这个语法示例中，必须定义一个新列或者定义一个表级约束。花括号（{ 和 }）把相关项组织在一起，而圆括号（( 和 )）中是必需的元素 。最后，分号（;）表示 SQL 语句的结束。

用 dblook 查看模式
java org.apache.derby.tools.dblook -d jdbc:derby:test
dblook 工具是一个 Java 类，可以用它方便地把数据库的内容输出到控制台。在命令行上运行它，就像运行其他 Java 程序一样；惟一增加的就是使用 -d jdbc:derby:test 参数，这个参数指定 dblook 工具应当查询的数据库

删除 Derby 中的表
DROP TABLE [schemaName.]tableName ;

两个破折号 (--) 开头表示是 SQL 注释；您应该使用它们来提供脚本文件中每个主要组件的用途的基本描述

ij> run 'derby.create.sql' ;
运行合适的脚本文件，通过使用 run 命令可实现该操作，该命令接受单个参数：单引号括起的脚本文件名当该脚本执行时，您可能会看到新 ij> 提示、命令和警告或错误消息的组合。这种显示可能有些笨拙，但 run 命令会执行您的脚本文件，就像您直接将命令键入 ij 工具中一样
如果正在 Bash shell 中工作，则可以使用 
java org.apache.derby.tools.ij < derby.create.sql > derby.create.out 2> derby.create.err 
实现这一点，它运行 derby.create.sql 脚本文件，将输出消息保存到 derby.create.out 中，并将所有 ij 错误消息保存到 derby.create.err 中。

清单 2. SQL INSERT 语法
INSERT INTO table-Name
    [ (Simple-column-Name [ , Simple-column-Name]* ) ]
   Expression

Expression 词可以扩展为下列四个不同的结构之一：
单行 VALUES 列表 
多行 VALUES 列表 
SELECT 表达式 
UNION 表达式

SQL VALUES 表达式的语法相当简单，(INSERT ....VALUES....) 
清单 3. SQL VALUES 语法
{
    VALUES ( Value {, Value }* )
        [ , ( Value {, Value }* ) ]* |
    VALUES Value [ , Value ]*
}
该语法首先显示多行格式，然后是单行格式（记住，竖线字符 | 表示或者，星号字符 * 表示一个或多个）。value 词表示想要插入特定列的值。要将数据插入多列，必须将一行的数据括在逗号分隔的括号内

对应的也可以用命令行：
java org.apache.derby.tools.ij < derby.insert.sql

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

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

表 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 NULL p.description IS NULL 测试表达式或值是否为 null。 
IS NOT NULL p.description IS NOT NULL 测试表达式或值是否非 null。 
AND    (p.price > 29.92) AND (p.itemNumber > 5) 测试两个表达式是否都为真或者值为非零。 
OR     (p.price > 29.92) OR (p.itemNumber > 5) 测试两个表达式的一个或二者是否为真或值为非零。 
NOT    NOT v.vendorNumber = 1 测试表达式是否为假或值为零。 
BETWEEN p.price BETWEEN 29.95 AND 39.95 测试一个值是否包含于两个其他值之间（示例等价于 29.95 <= p.price <= 39.95）。 
LIKE    v.vendorName LIKE 'Lun%' 测试字符表达式是否与模式相匹配，其中百分比字符 (%) 匹配零个或多个任意字符，下划线字符 (_) 只匹配一个任意字符。

清单 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 ;

清单 1. 在查询中使用 DISTINCT 限定符
ij> connect 'jdbc:derby:test' ;
ij> SELECT DISTINCT vendorNumber AS "Vendor #" FROM bigdog.vendors ;
清单 2. 在查询中使用 ORDER BY 子句
ij> SELECT v.vendorNumber AS "Vendor #", vendorName as "Vendor",
        p.price as "Price", p.itemNumber AS "Item #"
    FROM bigdog.products AS p, bigdog.vendors AS v
    WHERE p.itemNumber = v.itemNumber AND p.price > 20.0
    ORDER BY v.vendorNumber ASC, p.price DESC ;

在查询中执行数学操作
表 1. Apache Derby 数学操作符
操作符    示例      描述 
一元 +    +1.0   一个空操作，或无操作，如 +4 = 4 
一元 -    -p.price 将值的符号更改为所应用的符号 
+       p.itemNumber + 10 将第二个值加到第一个值上 
-       p.itemNumber - 10 将第一个值减去第二个值 
*       p.price * 1.0825 将第一个值乘以第二个值 
/       p.price / 100.0 将第一个值除以第二个值

表 2. Apache Derby 类型转换函数
函数   示例            描述 
BIGINT BIGINT(123.45) 返回给定字符串或数字的 64-bit 整数常量。 
CHAR   CHAR(123.45) 返回给定值的固定长度的字符表示，最大长度为 254 个字节，该给定值必须是内置的 Apache Derby 类型之一。可以提供可选的第二个参数以指定字符串的长度。 
DATE   Date('2006-05-16') 返回输入值的日期表示。 
DOUBLE DOUBLE(123.45) 返回输入数字或字符串的双精度浮点表示。 
INTEGER INTEGER(123.45) 返回给定字符串、日期、时间或数字的整数常量。 
SMALLINT SMALLINT(123.45) 返回给定字符串或数字的小整数常量。 
TIME     TIME('12:24:30') 返回输入值的时间表示。 
TIMESTAMP TIMESTAMP('2006-05-16','12:24:30') 返回输入值的时间戳表示。 
VARCHAR VARCHAR(123.45) 返回给定日期、时间、时间戳或字符串值的长度可变的字符表示，最大长度为 32,672 个字节
可以使用 CAST 函数来更改字符串的长度或将一种数据类型转换为另一种，其中包括不受 表 2 中列出的类型转换函数支持的那些数据类型（比如 DECIMAL）。

清单 3. 使用 CAST 操作符
ij> SELECT p.price AS "Price", 
        DOUBLE(p.price * 1.0825) AS "Float Total", 
        CAST(p.price * 1.0825 AS DECIMAL(6,2)) AS "Total",
        CAST(p.description AS CHAR(20)) AS "Description"
    FROM bigdog.products AS p 
    WHERE p.price > 21.0 AND p.stockDate < Date('2006-01-21') ;

计算聚合值
表 3. Apache Derby 聚合函数
函数   示例           描述 
AVG   AVG(p.price) 返回满足某个表达式的所有行的列的平均值。只能与内置数字数据类型一起使用。返回值的精度由要求值的列的精度来定义。 
COUNT COUNT(p.price) 返回满足某个表达式（比如查询）的行数。可与任何数据类型一起使用。 
MAX   MAX(p.price) 返回满足某个表达式的所有行的列的最大值。只能与内置数据类型一起使用。 
MIN   MIN(p.price) 返回满足某个表达式的所有行的最小值。只能与内置数据类型一起使用。 
SUM   SUM(p.price) 返回满足某个表达式的所有行的列的总和。只能与内置数据类型一起使用。 
清单 4. 在查询中使用聚合函数
ij> SELECT COUNT(p.itemNumber) AS Number, 
        CAST((AVG(p.price) + 0.005) AS DECIMAL(5,2)) AS Average,
        MIN(p.stockDate) AS "First Date", MAX(p.stockDate) AS "Last Date"
    FROM bigdog.products AS p ;

数学函数
表 4. Apache Derby 数学函数
函数            示例       描述 
ABS or ABSVAL ABS(-1.0) 返回表达式的绝对值，该值必须是内置数值类型之一。返回类型与参数类型相同。 
MOD           MOD(1, 2) 返回第一个参数除以第二个参数时的余数。返回类型与具有最大整数类型（SMALLINT、INTEGER 或 BIGINT）的参数相同。结果符号由第一个参数的符号单独确定。 
SQRT          SQRT(0.5) 返回表达式的平方根，该值必须是浮点值。返回类型与参数类型相同。

日期和时间函数
例如，可以将清单 3 所示的查询中 WHERE 子句的 p.stockDate < Date('2006-01-21') 部分替换为 YEAR(p.stockDate) < 2006。
表 5. Apache Derby 日期和时间函数
函数 示例                    描述 
DAY    DAY(p.stockDate) 返回一个整数，其中包含一个日期、时间戳或包含有效日期的字符串的日期部分 
HOUR   HOUR('12:21:30') 返回一个整数，其中包含一个时间、时间戳或包含有效时间的字符串的小时部分 
MINUTE MINUTE('12:21:30') 返回一个整数，其中包括一个时间、时间戳或包含有效时间的字符串的分钟部分 
MONTH MONTH('2005-12-20') 返回一个整数，其中包括一个日期、时间戳或包含有效日期的字符串的月份部分 
SECOND SECOND('12:21:30') 返回一个整数，其中包括一个时间、时间戳或包含有效时间的字符串的秒钟部分 
YEAR   YEAR(p.stockDate) < 2006 返回一个整数，其中包括一个日期、时间戳或包含有效日期的字符串的年份部分

字符串函数
表 6. Apache Derby 字符串函数
函数                 示例                                描述 
||            (p.description||v.vendorName)      串联操作符将两个值组合为一个新字符串。如果两个值都是 CHAR 类型，结果还是 CHAR类型。如果值为VARCHAR 类型或数值数据类型（可以转换为 VARCHAR 类型），则结果类型是 VARCHAR。 
LCASE 或 LOWER LCASE(p.description) 返回一个字符串，其中输入值中的所有字母字符都转换为小写。 
LENGTH          LENGTH(p.description) 返回输入值中的字符数。非字符数据隐式转换为字符串。 
LOCATE          LOCATE('beach',p.description) 返回一个子字符串在搜索字符串中第一次出现的起始位置，如果没找到子字符串，则返回 0。第一个参数是子字符串，第二个参数是搜索字符串，可选的起始位置可以提供作为第三个参数。 
RTRIM           RTRIM(p.description) 返回一个字符串，其中输入值结尾处的所有空格都被移除。 
LTRIM           LTRIM(p.description) 返回一个字符串，其中输入值开头处的所有空格都被移除。 
SUBSTR          SUBSTR(p.description, 1, 4) 返回 VARCHAR 类型的输入字符串的一部分，在指定位置处开始，一直延续到字符串末尾，或延续到可的第三个参数指定的位置。如果起始位置是正值，则它相对于字符串的开头；如果是负值，则是相对于字符串的末尾。 
UCASE, or UPPER UCASE(p.description) 返回一个字符串，其中输入值中的所有字母字符都已转换为大写。

删除数据
清单 2.DELETE FROM tableName
[WHERE clause]
删除所选行
ij> DELETE FROM bigdog.products 
    WHERE description LIKE '%towel%' OR itemNumber <= 3

更新数据
清单 3. SQL UPDATE 语句语法
UPDATE tableName
        SET columnName = Value
        [ , columnName = Value} ]*
        [WHERE clause] 
例子：
ij> UPDATE bigdog.products
    SET price = price * 1.25, stockDate = CURRENT_DATE
    WHERE itemNumber = 6 ;

表 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

清单 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. 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 的同义词。该表子查询可以返回多个行，但它们必须只有一个列。

修改表模式
清单 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 ; 
如本例所示，要修改表，在本例中是将一个新列 count 添加到 bigdog.products 表中，首先要创建一个具有需要的正确模式的表。本例需要包括列约束 NOT NULL 使之始终具有有效的值，并通过使用列约束 DEFAULT 0 为 count 列分配默认值 0。注意如何通过将列顺序列出来合并多列约束

清单 2. 使用 GROUP BY 和 HAVING 子句的分组结果
                
ij> SELECT COUNT(p.itemNumber) AS Number, AVG(p.price) AS Average
        FROM bigdog.products AS p, bigdog.vendors AS v
        WHERE p.itemNumber = v.itemNumber
        GROUP BY v.vendorNumber ;
NUMBER     |AVERAGE    
-----------------------
6          |31.4633    
2          |39.9500    
2          |23.9500   

3 rows selected
ij> SELECT v.vendorNumber, 
        COUNT(p.itemNumber) AS Number, AVG(p.price) AS Average
        FROM bigdog.products AS p, bigdog.vendors AS v
        WHERE p.itemNumber = v.itemNumber
        GROUP BY v.vendorNumber
        HAVING v.vendorNumber > 1 ;
VENDORNUMB&|NUMBER     |AVERAGE    
-----------------------------------
2          |2          |39.9500    
3          |2          |23.9500   

2 rows selected
对 NULL 值进行分组
有时，按照查询选择的行包含 NULL 值。在这种情况下，可能想知道在尝试使用包含 NULL 值的列对行进行分组会发生什么情况。答案是很简单的，而且实际上就是想得到的结果，因为 NULL 值不会影响分组结果。

好视图无价
清单 3. 使用视图的正式语法
CREATE VIEW schema.viewName
    [ ( columnName1 [, columnName2] * ) ]
AS sqlQuery ;

DROP VIEW schema.viewName ;
清单 4. 在 Apache Derby 中创建和删除视图
ij> CREATE VIEW bigdog.vendorList (Name)
    AS SELECT DISTINCT vendorName FROM bigdog.vendors ;
0 rows inserted/updated/deleted
DROP VIEW bigdog.vendorList 

用索引加速
清单 6. Apache Derby SQL 索引操作的正式语法
CREATE [UNIQUE] INDEX schema.indexName
ON schema.tableName ( columnName [ ASC | DESC ]
    [ , columnName [ ASC | DESC ]] * ) ;

DROP INDEX schema.indexName ;
    
RENAME INDEX indexName TO newIndexName ; 
清单 7. 在 Apache Derby 中使用索引
ij> SET SCHEMA bigdog ;
0 rows inserted/updated/deleted
ij> CREATE INDEX productsIndex ON products(itemNumber) ;
0 rows inserted/updated/deleted
ij> RENAME INDEX productsIndex TO pi ;
0 rows inserted/updated/deleted
ij> DROP INDEX pi ;
0 rows inserted/updated/deleted