Apache Derby を使用した開発 ― 3 連単を当てる: 第 6 回 Apache Derby を使用したデータベース開発

データとスキーマを変更する

データベース・スキーマの作成、テーブルへのデータ入力の他、選択的にデータを変更できるというのも、データベース開発者に必要とされる最も重要なスキルです。この記事では、既存のテーブル内のデータを選択的に削除したり更新する方法、そして既存のテーブルの構造を変更する方法を説明します。より複雑なデータベース・スキーマにデータ変更を加えるために、データ更新とデータ挿入操作による組み込みサブクエリー (スカラーとテーブルの両方) を学びます。また、Apache Derby データベースを使って、複雑なスキーマのデータを削除および変更する方法についても紹介します。

Robert Brunner (rb@ncsa.uiuc.edu), Research Scientist, National Center for Supercomputing Applications

Robert BrunnerRobert J. Brunnerは、米国立スーパー・コンピュータ応用研究所に科学者として勤務するかたわら、イリノイ大学アーバナ・シャンペーン校で天文学の助教授を務めています。何冊かの著作と、さまざまな分野にわたる数多くの記事や解説書を発表しています。



2006年 8月 15日

はじめに

前回までの連載記事では、以下の内容を取り上げました。

  • Apache Derby の紹介
  • ij ツールの紹介
  • データベース・スキーマを作成する、リレーショナル・データベース・テーブルを設計する方法、およびテーブルにデータを挿入する方法のデモ
  • SQL クエリーを作成してデータを抽出する方法のデモ

ここまででまだ説明していない重要なタスクは、既存のデータを変更する方法です。この記事では、Apache Derby データベース内の既存のデータを選択的に削除または変更できる SQL の DELETE 文および UPDATE 文を紹介します。

この記事に記載されている例に沿って学習を進めるには、以下の前提条件が必要になります。

  1. Apache Derby データベース・システムを稼動させていること (シリーズ第 1 回を参照)
  2. Apache Derby の ij コマンド行ツールに慣れていること (シリーズ第 2 回を参照)
  3. Bigdog の Surf Shop サンプル・データベースを正しく初期化していること (シリーズ第 4 回および第 5 回を参照)
  4. SQL の SELECT 文の基礎が分かっていること (シリーズ第 5 回を参照)

これらの条件を満たしているどうか不安な場合は、上記にリストしたステップを確実に完了してから、この記事の続きを読んでください。このシリーズの今までの記事を参照すれば、すぐに条件を満たすことできます。


データを削除する

この記事で最初に紹介するデータ変更の手法は、データを削除することです。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

上記の例では、整数値を持つ単一列の一時テーブルを作成しています。次にデータベースに 4 行を挿入し、SELECT 文を実行して新規テーブルに 4 行が含まれていることを確認します。この一時テーブルから 4 行すべてを削除するには、条件なしの DELETE 文を使用します。これは、Apache Derby からのメッセージ「4 rows inserted/UPDATEd/DELETEd」と、一時テーブルに行が 1 つも含まれていないことを示す 2 番目の SELECT 文の両方で確認されています。最後に、DROP TABLE 文によって、スキーマから空のテーブルを削除します。

ただし通常は、テーブルからすべての行を削除することはなく、選択的に行を削除します。これを行うには、対象となるすべての行を識別する適切な WHERE 節を作成します。DELETE 文で使うことができる WHERE 節の構文は、第 4 回で説明した構文と同じで、これによって完全な SQL の SELECT 文の構文になります。WHERE 節内でブール式を作成するための基本ビルディング・ブロックは、この記事の表 1 に記載しています。以下のリスト 2 は、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

上記の例では、Apache Derby が ij ツールを介して返す参考メッセージ「5 rows inserted/UPDATE d/deleted」からわかるように、DELETE 文には 5 つの行を識別する WHERE 節が組み込まれています。この WHERE 節には OR 演算子で結合された 2 つの式が含まれているため、どちらかの式が特定の行を TRUE と評価した場合、その行が削除されることになります。

最初の式は、製品説明に「towel」という言葉が含まれるすべての行を検索します。このシリーズの前回の記事を思い出してください (記憶になければ、DELETE 文の前に SELECT 文を実行してみてください)。bigdog.products テーブルには、 itemNumber 列の値が 7 と 8 の 2 つの towel が含まれています。もう一方の式は、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 構文で示されているように、1 つの列を更新する場合、SQL の UPDATE 文には最低 1 つの SET コンポーネントが必要で、さらにオプションとして 1 つ以上の SET コンポーネントと WHERE 節を含めることができます。WHERE 節が含まれていない場合、UPDATE 文はテーブル内のすべての行で指定された列を変更します。

UPDATE 文の実行方法は、リスト 4 に示すように極めて簡単です。このリストでは、1 つの行に含まれる 2 つの列を変更しています。

リスト 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 文でラップしています。2 つの SELECT 文はどちらも、単一行 (itemNumber 列の値が 6 の行) の bigdog.products テーブルから 3 つの列を選択しています。UPDATE 文によって、この特定の行の price と stockDate 列が変更されます。price 列の値は 25% (その品目の人気などにより) 引き上げられ、stockDate 列が現在の日付になるように変更されています。現在の日付は、Apache Derby では SQL クエリーで CURRENT_DATE 組み込み関数を使えば簡単に取得できます。

Apache Derby には、このような組み込み関数がいくつか組み込まれているため、これらの関数を使って現行データベース接続に関するデータを取得できます。表 1 に、これらの組み込み関数をすべてリストします。

表 1. Apache Derby の SQL 現行関数
関数説明
CURRENT_DATE現在の日付を適切な Apache Derby DATE 形式で返します。
CURRENT_ISOLATION現在のトランザクション分離レベルを返します。これについては、2 文字の文字列として今後の記事で詳しく説明します。
CURRENT_SCHEMAスキーマ名を最大 128 文字の文字列として返します。この文字列は、非修飾データベース・オブジェクト名を修飾するために使用されます。
CURRENT_TIME現在の時刻を適切な Apache Derby TIME 形式で返します。
CURRENT_TIMESTAMP現在のタイムスタンプを適切な Apache Derby TIMESTAMP 形式で返します。
CURRENT_USER許可 ID を 128 文字の現行ユーザーの文字列として返します。現行ユーザーがいない場合は、APP を返します。

前述の例では、1 つのテーブルで特定の行に含まれる複数の列値を変更する方法を示しました。一方、複数の行を更新対象として選択するロジックは、時としてより複雑になります。例えば、Quiet Beach Industries (bigdog.vendors テーブルの vendorNumber 列の値が 3) から取得した bigdog.products テーブルに含まれるすべてのオブジェクトの価格を変更しなければならないとします。それには、組み込みクエリーを使う必要があります。リスト 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 文内では単純な結合を行うことができないため、Quiet Beach Industries の製品に対応する itemNumber 行を抽出するには、WHERE 節にサブクエリーを組み込む必要があります。UPDATE 文の WHERE 節では IN 演算子を使って、組み込みクエリーによって選択された値のセットから itemNumber 列を持つ行を選択しています。

UPDATE 文の WHERE 節では、スカラー・サブクエリーとテーブル・サブクエリーという 2 つのタイプのクエリーを使うことができます。スカラー・サブクエリーは、1 列のみを含む 1 行 (実質的には、スカラーとして知られる 1 つの値) を返す組み込みクエリーです。スカラー・サブクエリーでは、WHERE 節の式で使われる特定の値を選択できます。例えば、itemNumber = (Scalar Subquery) では、スカラー・サブクエリーの結果と一致する itemNumber 列の値を持つ行を更新します。

一方、テーブル・サブクエリーでは、通常は 1 つの列しかない行を複数返すことができます。場合によっては、テーブル・サブクエリーに複数の列が含まれることもあります。テーブル・サブクエリーを使用するには、SQL 演算子を使って、組み込みクエリーとブール式とを組み合わせる必要があります。例えば前述のコード・リストでは、IN 演算子が Quiet Beach Industries によって作成された bigdog.products テーブルのすべての列を選択しています。IN 演算子は、テーブル・サブクエリーで使うことができる 4 つの SQL 演算子のうちの 1 つです。表 2 に、この 4 つすべての演算子を記載します。

表 2. Apache Derby の SQL 演算子とテーブル・サブクエリー
演算子説明
INitemNumber IN (Table Subquery)1 列のみを返すテーブル・サブクエリーに式の値がある場合は、TRUE を返します。NOT IN のように、NOT 演算子を含めてテーブル・サブクエリーにない行を選択することもできます。
EXISTSEXISTS (Table Subquery)テーブル・サブクエリーが行を返した場合は TRUE、行が 1 つも選択されなかった場合は FALSE を返します。つまり、テーブル・サブクエリーで選択された行数に応じて、すべての行が変更されるか、あるいはどの行も変更されないかのいずれかです。NOT 演算子を含めると、このルールを逆にできます。
ALLitemNumber = ALL (Table Subquery)数量比較とも呼ばれます。ALL キーワードは、比較演算子 (=、<、>、<=、>=、または <> のいずれか) を変更するため、すべての行について真である場合にのみ結果が TRUE になります。テーブル・サブクエリーは複数の行を返すことができますが、それぞれの行に含まれる列は 1 つでなければなりません。
ANYitemNumber = ANY (Table Subquery)これも数量比較ですが、この場合、行のいずれかが真の場合に結果が TRUE になります。ANY の同義語として SOME を使用できます。テーブル・サブクエリーは複数の行を返すことができますが、それぞれの行に含まれる列は 1 つでなければなりません。

表 2 の説明から、リスト 4 で示した UPDATE 文の WHERE 節を作成し直し、同じテーブル・サブクエリーで数量比較を使うと (WHEREitemNumber = ANY (...))、同じ結果を得られることが分かります。同じテーブル・サブクエリーで ALL 演算子を使った場合は、bigdog.products テーブル内のすべての itemNumber 値がテーブル・サブクエリーにないため、いずれの行も更新されません。一方、EXISTS 演算子を使うと、itemNumber 値の少なくとも 1 つがテーブル・サブクエリーにあるため、すべての行が変更されます。


テーブル・スキーマを変更する

前のセクションでは、テーブル内にすでに存在するデータを変更する方法について説明しました。もう 1 つの可能な方法は、データベース・テーブルの構造 (つまり、スキーマ) を変更することです。これは、列の追加、列のデータ型の変更、制約の追加、あるいは列の削除という形で行います。このプロセスは簡単なものではありません。この理由からも、スキーマを最初に設計するときには注意が必要です。テーブルの構造を変更しなければならない場合は、リスト 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 文で、挿入する値を取得するためのサブクエリーを使います。この強力な手法によって、既存のテーブルのすべて、あるいはその一部を 2 番目のテーブルに簡単にコピーできます。

新規テーブルを作成して適切なデータをコピーしたら、SQL の DROP TABLE 文を使って古いテーブルを削除し、SQL の RENAME TABLE 文を使って新規テーブルの名前を元の名前に変更します。この名前変更の操作は簡単で、oldTableName を newTableName に変更するだけです。ただし、RENAME操作は異なるデータベース・スキーマ間ではテーブルを移動できないため、新規テーブル名のスキーマ名は指定しないでください。この例では最後に、SELECT 文を実行して、新しい bigdog.products テーブルのスキーマと内容を表示しています。見てのとおり、新規テーブルには 5 つの列があり、count 列は常にゼロです。この段階で、実際のアプリケーションが、必要な SQL の UPDATE 文を実行して count 列を適切に変更することになります。


まとめ

この記事では、Apache Derby データベース内のデータを変更することだけに焦点を絞りました。最初に紹介したデータ変更の手法はデータの削除で、これは SQL の DELETE 文を使って実行します。次に、SQL の UPDATE 文を使って、テーブル内の選択された行の列値を変更しました。最後に、テンポラリー・テーブルを使って既存のデータベース・テーブルの構造を変更しました。この記事ではまた、組み込みサブクエリーを使用して、より複雑なデータベース・スキーマを変更する方法も示しました。次回は拡張データベースに関する残りの話題について取り上げます。その後、このシリーズは Java アプリケーションから Apache Derby データベースに接続する方法に展開していきます。

参考文献

学ぶために

製品や技術を入手するために

  • Apache Derby の最新リリースをダウンロードしてください。
  • IBM ソフトウェアの試用版を使用して、次のオープン・ソース開発を革新してください。ダウンロード、あるいは DVD で入手できます。

議論するために

コメント

developerWorks: サイン・イン

必須フィールドは(*)で示されます。


IBM ID が必要ですか?
IBM IDをお忘れですか?


パスワードをお忘れですか?
パスワードの変更

「送信する」をクリックすることにより、お客様は developerWorks のご使用条件に同意したことになります。 ご使用条件を読む

 


お客様が developerWorks に初めてサインインすると、お客様のプロフィールが作成されます。会社名を非表示とする選択を行わない限り、プロフィール内の情報(名前、国/地域や会社名)は公開され、投稿するコンテンツと一緒に表示されますが、いつでもこれらの情報を更新できます。

送信されたすべての情報は安全です。

ディスプレイ・ネームを選択してください



developerWorks に初めてサインインするとプロフィールが作成されますので、その際にディスプレイ・ネームを選択する必要があります。ディスプレイ・ネームは、お客様が developerWorks に投稿するコンテンツと一緒に表示されます。

ディスプレイ・ネームは、3文字から31文字の範囲で指定し、かつ developerWorks コミュニティーでユニークである必要があります。また、プライバシー上の理由でお客様の電子メール・アドレスは使用しないでください。

必須フィールドは(*)で示されます。

3文字から31文字の範囲で指定し

「送信する」をクリックすることにより、お客様は developerWorks のご使用条件に同意したことになります。 ご使用条件を読む

 


送信されたすべての情報は安全です。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=60
Zone=Open source, Information Management
ArticleID=232559
ArticleTitle=Apache Derby を使用した開発 ― 3 連単を当てる: 第 6 回 Apache Derby を使用したデータベース開発
publish-date=08152006