CHAPTER 2: Empress SQL の使用

Empressは大きく複雑なプログラムですが、 ユーザーにとっては、コンピュータープログラミングや システムおいてどのようにデータが格納され、検索されるかを 知る必要はありません。 しかしながら、Empress を理解するためにコマンドを習得する必要はあります。 コマンドの数は多くなく、複雑でもありませんので、 基本コマンドを簡単に覚えることができ、すぐにそれを使用することができます。 この章では、基本のEmpress SQLコマンドについて示します。

Empressのデータベースは、オペレーティングシステム上の １つのディレクトリとして作成されます。 このディレクトリは、データを蓄積するための複数のオペレーティング システムファイルが含まれます。 Empressは、これらのファイルとユーザーの間のインターフェイスと言えます。 例えば、テーブルを作成したときにEmpressは、 テーブルデータを格納するためのオペレーティングシステムファイルを作成します。 また、テーブルをアクセスするための問い合わせを行った場合は、 Empressが対応したオペレーティングシステムファイルから 情報を検索します。

データベースには、テーブルの集合が含まれ、 各テーブルには 0 個以上のレコードが格納されます。 各レコードには設定したアトリビュートの値を含み、 各アトリビュートはそのデータの特性の定義が含まれます。 例えば、例のデータベースの場合は、以下のような構造になります。

(personnel テーブル)

10   Kilroy     426-9681      $500.00
 5   Mosca      544-2243      $750.00
17   Wladislaw  723-6073       $50.00
 3   Jones      667-2951      $500.00
 8   Peterson   978-6060      $250.00
 4   Scarlatti  961-7363      $100.00
 9   Jordan     964-3335      $250.00 

(personnel テーブル)

number           integer
name             char (25,1)
phone            char(15,1)
credit_limit     dollar 

Empressは、広く様々なデータタイプを提供します。 データタイプのリストを以下に示します。

表 2-1 Empress データタイプ

NULL値は、特別の形式で内部に格納され, NULL キーワードを使用することにより、 NULL値を入力、検索が可能です。

Empressデータタイプの詳細な説明は、 Empress SQL: リファレンスマニュアルを 参照してください。

CHARACTERデータタイプは、 固定長の文字列データを格納するための データタイプで、入力データのための 3 つのタイプがあります。

表 2-2 Empress CHARACTER データ型のタイプ

例えば、personnelテーブルのnameアトリビュートは、

   name   CHAR (25,1)

25 文字の長さの固定長の文字データを扱い、データの前後に空白がある場合、 取り除かれます。

TEXTデータタイプは、可変長文字列タイプで、 少ない文字から複数ページに渡る可変の文字列を扱うためのデータタイプです。

例えばcustomersテーブルのcommentsアトリビュートは、

   comments  TEXT (25, 0, 80, 1)

表示長が 1行に25 文字、メインテーブルに文字を格納せずに オーバーフローテーブルに固定長レコードとして 80 バイトを格納し、 メインテーブルのオーバーフロー拡張ポインタ領域を1つ持つ、 可変長文字列の定義になります。

Empressは、the ISO Latin1 のソートメカニズムに一致する すべてのNational Language 8ビットキャラクターセットをサポートします。 また、16ビット 漢字およびハングルをサポートします。

例えばフランスの文字を端末から入力する場合、 personnelテーブルのnameアトリビュートに次のように 宣言することができます。

   name   nlschar (25,1)

DATEデータタイプは、日付を格納するためのデータタイプで、9つの 日付データフォーマットがあります。

表 2-3 Empress DATE データタイプフォーマット

*

nnは、 Empressシステム変数MSDATELIMIT によって定義され、入力日付を適切な世紀を指定するため ユーザーが設定する変数です。

すべてのDATEタイプでは、入力キーワードとして today と nowがあり、キーワードは 大文字・小文字に関係なく、現在の日付を適切なフォーマットで 格納します。 を持っています。

以下の例は、loansテーブルのdateアトリビュートです。

   date    DATE (1)

TIME データタイプは、日付に加え時間情報を格納するための データタイプです。 9つのデータフォーマットがあり、以下の表での例は、 1998年 8月 28日 p.m. 2:56:15 を それぞれのフォーマットで出力した例になります。

表 2-4 Empress TIME データタイプフォーマット

TIMEデータタイプは、現在の日付および時間を格納するための キーワードとしてtodayとnowがあります。 todayは、現在の日付で、時間は00:00:00が入力されます。 nowは、現在の日付と時間が入力されます。

次のTIMEデータのアトリビュートの例は、

   time   TIME (1)

1998年 6月 1日 2:30 PMの表示例です。

   1 June 1998 14:30:00

MICROTIMESTAMP データタイプは、マイクロ秒までの日付、時間情報を 格納するためのデータタイプです。

MICROTIMESTAMPのデータフォーマットは以下に示された 異なった9つのタイプがあり、以下の表での例は、 1998年 8月 28日 p.m. 2:56:15 234599マイクロ秒を 各フォーマットで出力した例です。

表 2-5 Empress MICROTIMESTAMP データタイプフォーマット

*

上の表でのnnは、Empressシステム変数 MSDATELIMITに指定された値であり、この値は 日付入力のための適切な世紀を指示した値になります。

38 桁までのDECIMALデータを格納することができます。 ユーザーが小数の桁数を指定することができます。

例えば、circumferenceアトリビュートは 全体の桁数を 7 桁として、小数部の桁数は 3 になります。

   circumference   DECIMAL (7, 3)

DOLLAR データタイプは 通貨(ドル)データを格納するためのデータタイプです。 このデータタイプには 3 のタイプがあり、そのうちの1つを使用し 13桁まで有効です。

表 2-6 Empress DOLLAR データタイプフォーマット

例えば、personnelのcredit_limitアトリビュートは、

   credit_limit     DOLLAR (6,1)

6 桁でタイプ 1 表示 の DOLLAR データタイプになります。

浮動小数点の値を格納するデータ型として以下のデータ型が用意されています。

• REAL
• FLOAT
• LONGFLOAT または DOUBLE PRECISION

データは検索の上では、科学小数点法で表わされます。

例えば、10342は、1.0342e+04と表すことができます。

整数値を格納するデータ型として以下のデータ型が用意されています。

• INTEGER または INT
• SHORTINTEGER または SMALLINT
• LONGINTEGER

例えばpersonnelテーブル中のnumberアトリビュートは、 以下のように定義されています。

   number   INTEGER

INTEGER 型の範囲は-32767から+32767までを扱うことができます。

MULTIMEDIA型、またはBULK型は、 画像イメージ、音声イメージ、サウンドイメージなどのような 構造化されていないデータを格納するためのデータタイプです。 例えば、従業員の写真を格納するpictureというアトリビュートを宣言する 場合は以下のように指定します。

   picture BULK (80,0,1024,1)

このセクションでは、データベースの作成とアクセス方法についてを示します。 Empressデータベースの作成およびアクセスの手順は 通常、以下のようにおこないます。

1. オペレーティングシステムにログインします。

2. 作業ディレクトリ、またはデータベースを作成したいディレクトリに移動します。 例としてworkという作業ディレクトリの場合、以下のように入力します。

      cd work
   
   

3. empmkdbコマンドの後にスペースを開け、作成するデータベース名を 入力することでデータベースを作成することができます。 指定したデータベースが既に存在している場合は作成することはできません。 (この場合、ステップ４に進んでください。) データベースの名前は、その文字列中に空白がない文字の並びにしてください。 "repairs" データベースを作成するには、以下のように入力します。

      empmkdb repairs
   
   

4. empmkdbコマンドは、Empress のデーターベース作成を行います。 指定された名前のシステムディレクトリを作成し、 そのディレクトリ中にEmpressが、データベースとして認識するための 複数のファイル(データ辞書)を作成します。

5. empsqlコマンドの後にスペースを開け、データベース名を指定することで Empress SQLセッションを起動します。 例えば、以下のように入力します。

      empsql repairs
   
   

6. 前ステップで失敗した場合、 Empress SQLスタートアップコマンドは、インストレーション時に リネームされた可能性があります。この場合、システム管理者に相談してください。

7. Empress SQLセッションが起動されると アスタリスク(*) をともなったプロンプトが表示され、 使用されているEmpressバージョンで有効な すべてのEmpress SQLコマンドの使用が可能になります。

データの格納および検索をする前に最初にテーブルを作成します。 例えば、サンプルデータベースは、従業員のローンについての情報 (従業員名、ローン日付、ローン金額)を含んだloansテーブルを持ち、 アトリビュートとしてname、dateおよびamount があります。

loansテーブルを作成するためには、以下のSQLコマンドを発行します。

   CREATE TABLE loans (name CHAR (25,1),
                       date DATE (1),
                       amount DOLLAR (6,1));

CREATE TABLEコマンドの後にテーブル名を指定し、その後に各アトリビュートの 設定を行います。 loansテーブルは、char型データでサイズが25のnameアトリビュートと date 型のdateアトリビュート、 dollar 型のamountアトリビュートになります。

対話型インターフェイス Empress SQLでの、テーブルを 作成するための他の簡単な方法として、 対話型インターフェイスコマンドを使います。 personnelテーブルを作成しまするためには 以下のように入力します。

CREATE TABLE personnel;

プロンプトが表示され、対話的にアトリビュートの属性情報を入力します。

   アトリビュート名 #1: number
   データタイプ: integer
   NULL 値を認めますか ?: y
   
   よろしいですか？ .e
   
   アトリビュート名 #2: name
   データタイプ: char
   Maximum Number of Characters: 25
   Type: 1
   NULL 値を認めますか ?: y
   
   よろしいですか？ .e
   
   アトリビュート名 #3: phone
   データタイプ: char
   Maximum Number of Characters: 15
   Type: 1
   NULL 値を認めますか ?: y
   
   よろしいですか？ .e
   
   アトリビュート名 #4: credit_limit
   データタイプ: dollar
   Maximum Number of Dollar Digits: 6
   Type: 1
   NULL 値を認めますか ?: y
   
   よろしいですか？ .e
   
   アトリビュート名 #5: .q

サンプルデータベースのauto_parts、 customersテーブルを作成するために 上記の例のように対話型インターフェイスコマンドを 使うことができます。

データベースの情報を得るためには、 次のコマンドのどちらかを入力します。

   DISPLAY DB;
   
   DISPLAY DATABASE;

上記のコマンドにより、以下の情報を表示します。

   **** Database: repairs ****
   
   auto_parts
   customers
   loans
   personnel

personnelテーブルの構造を参照するためには 次のように入力します。

   DISPLAY TABLE personnel;

上記のコマンドにより、以下の情報を表示します。

   *** Table: personnel ***
   
   Attributes:
   
   number          integer
   name            char(25, 1)
   phone           char(15, 1)
   credit_limit    dollar(6, 1)

データを格納するためのテーブルの作成が完了することで、 レコードを登録を開始することができます。 Empress SQLでは、 シングルコマンド、対話型インターフェイス およびファイルからのデータを使ったテーブルへの データ登録をすることができます。

サンプルデータベースの personnel、loans、auto_parts customersテーブルへのデータ登録は、 以下に示す例により登録することができます。

シングルコマンドを使用してpersonnelテーブルに最初のレコードを 登録するためには、以下のように入力します。

   INSERT INTO personnel
      VALUES 10, "Kilroy", "426-9681", 500;

上記の例では、 INSERT INTOコマンドで登録するテーブル名として personnelを指定し、 格納するデータとして、続けて指定されたデータの値のリスト (10, "Kilroy","426-9681", 500) を指定することでデータ登録されます。

対話型インターフェイスを使用して、 プロンプトに対し、各アトリビュート値を入力することで レコード登録を行います。 以下に例を示します。

   INSERT INTO personnel;
   
   number: 5
   name: Mosca
   phone: 544-2243
   credit_limit: 750
   
   よろしいですか？.e
   
   number: .q
   
   入力レコード数 = 1

ファイルからデータを登録するためには、あらかじめ システムエディタを使用して、データを含むデータファイルを作成します。 この例では、以下のようなデータを含むdatafileというファイルを作成します。

   17
   Wladislaw
   723-6073
   50
   3
   Jones
   667-2951
   500

以下のコマンドを発行することにより personnelテーブルに ファイルデータを登録することが可能です。

INSERT INTO personnel FROM datafile;

上記のコマンドにより、登録されたレコード数を表示します。

入力レコード数 = 2

今までの登録例で3つの異なった方法を使用してpersonnelテーブルへ 4 レコードが登録されました。

対話型インターフェイスを使い customersテーブルに最初のレコードを登録するには 以下のように入力します。

   INSERT INTO customers;

以下のようにプロンプトにデータを入力します。

   name: Mr. R. Lucas
   address: 1423 Beach Drive,\
   Bayville, CA 92308
   comments: Can always be sold\
   something new and flashy - likes\
   chrome and colored stuff,\
   also little gadgets.

   Ready .e
   
   name: .q

例の値の入力での バックスラッシュ(\)は、複数行に渡るテキストデータ入力の継続を 指示するために使用されますが、データとしては登録されません。 継続文字はEmpress環境変数MSLINECONTによって定義されています。 詳細な情報は Empress: Database 管理者ガイドマニュアルの MSLINECONT を参照してください。

最も一般的な SQL オペレーションは、データの検索です。 データベースからデータを検索することを、 クエリ と呼びます。 SELECTコマンドは、クエリを発行するために使用されます。 personnelテーブルからレコードを検索するためには、 以下のように入力します。

   SELECT * FROM personnel;

アスタリスク(*) は、 personnelテーブルからすべてのレコードをリストします。 上記のコマンドにより、以下の表示をします。

   number    name         phone        credit_limit

       10    Kilroy       426-9681          $500.00
        5    Mosca        544-2243          $750.00
       17    Wladislaw    723-6073           $50.00
        3    Jones        667-2951          $500.00
        8    Peterson     978-6060          $250.00
        4    Scarlatti    961-7363          $100.00
        9    Jordan       964-3335          $250.00

前の例では、 personnelテーブルからすべてのデータの検索をしましたが、 従業員名と電話番号のリストのような特定な情報の検索が必要な場合は、 以下のように入力します。

   SELECT name, phone FROM personnel;

この場合、SELECT句は、 指定されたアトリビュートのみをリスト表示し、 以下のような出力になります。

   name        phone

   Kilroy      426-9681
   Mosca       544-2243
   Wladislaw   723-6073
   Jones       667-2951
   Peterson    978-6060
   Scarlatti   961-7363
   Jordan      964-3335

前に示された例では、指定されたアトリビュート情報の検索でしたが、 特定のレコードのみをさらに検索するためには、 SELECT文にWHERE句を追加します。 WHERE句を使用することで、 WHERE 句に指定された条件を満たすレコードのみを検索します。

例えば、 personnelテーブルから $500 を越えるクレジット制限を持つ 従業員名をするとする場合、以下のように入力します。

   SELECT number, name, phone FROM personnel
      WHERE credit_limit > 500;

出力結果は以下のようになります。

   number   name        phone

        5   Mosca       544-2243

以下の例では、検索条件の使用について示します。

   SELECT number, name, phone FROM personnel
      WHERE credit_limit < 500
      AND credit_limit > 100; 

   SELECT name FROM personnel
      WHERE credit_limit > 500
      OR credit_limit < 100; 

   SELECT name FROM personnel
      WHERE credit_limit != 500; 

   SELECT name FROM personnel
      WHERE credit_limit BETWEEN 100 AND 500; 

   SELECT name FROM personnel
      WHERE credit_limit IN (100, 200, 500); 

   SELECT name FROM personnel
      WHERE name MATCH "C*"; 

より詳細な情報は、 Empress SQL: リファレンスマニュアルの Empress WHERE 句のオペレーション を参照してください。

SELECTコマンドにより要求されたレコードを 共通のアトリビュート値により、グルーピングすることができます。 GROUP BY句の使用は、 出力は指定されたアトリビュートによりグループ化することができます。 例として以下に示します。

   SELECT * FROM loans GROUP BY name;

出力結果は以下のようになります。

   name                                   date        amount
   
   Jones                      7 February  1992        $33.95
                              3 April     1992        $25.00
                             12 August    1992       $300.00
   Kilroy                    16 February  1992       $250.00
   Mosca                      2 February  1992       $150.00
                              4 May       1992       $200.00
   Peterson                   6 June      1992        $50.00
   Scarlatti                 14 October   1992       $125.00
                              9 September 1992       $150.00
   Wladislaw                 25 June      1992        $75.00
                             27 February  1992        $55.00
                             12 May       1992        $25.00

GROUP BY句は、 COUNT、MAX、 MIN、SUM、 AVG などのような集計関数がしばしば利用されます。 例として以下に示します。

SELECT name, SUM(amount) FROM loans GROUP BY name;

出力結果は以下のようになります。

   name                          SUM(amount)

   Jones                              358.95
   Kilroy                             250.00
   Mosca                              350.00
   Peterson                            50.00
   Scarlatti                          275.00
   Wladislaw                          155.00

WHERE句は、 個別のレコードに対しての検索条件を指定するために使用され。 HAVINGは、 グループ化されたレコードに対しての検索条件を指定するために使用されます。 例として以下に示します。

   SELECT name, SUM(amount) FROM loans GROUP BY name
      HAVING SUM(amount) > 300;

出力結果は以下のようになります。

   name                          SUM(amount)

   Jones                              358.95
   Mosca                              350.00

重複した値を除くためには、 SELECT文中にDISTINCTキーワードを指定します。 例として以下に示します。

   SELECT DISTINCT (name) FROM loans:

出力結果は以下のようになります。

   name

   Mosca
   Jones
   Kilroy
   Wladislaw
   Peterson
   Scarlatti

ここまでに示した例はすべて1つのテーブルに対しての問い合わせでしたが、 場合により、1つ以上のテーブルに格納された情報を捜すことがあります。 この場合においては2つ以上のテーブルを組み合わせ検索する必要があります。

loansテーブルからすべてのレコードリストと 併せてpersonnelテーブルから従業員の電話番号を含むレコードリストを 取得するためには、以下のように入力します。

   SELECT name, phone, date, amount
      FROM loans, personnel
      WHERE loans.name = personnel.name;

出力結果は以下のようになります。

   name             phone                   date      amount
   
   Mosca           544-2243     2 February  1992     $150.00
   Jones           667-2951     7 February  1992      $33.95
   Kilroy          426-9681    16 February  1992     $250.00
   Wladislaw       723-6073    27 February  1992      $55.00
   Jones           667-2951     3 April     1992      $25.00
   Mosca           544-2243     4 May       1992     $200.00
   Wladislaw       723-6073    12 May       1992      $25.00
   Peterson        978-6060     6 June      1992      $50.00
   Wladislaw       723-6073    25 June      1992      $75.00
   Jones           667-2951    12 August    1992     $300.00
   Scarlatti       961-7363     9 September 1992     $150.00
   Scarlatti       961-7363    14 October   1992     $125.00

これまでの例において、問い合わせの出力結果の表示順は、 Empressによって決定されていましたが、 SELECT文にORDER BY句を追加することにより 出力表示をソートすることができます。

例えば、personnelテーブルから クレジット制限が $100以下の 従業員番号と従業員名のリストを 従業員番号の昇順にソートし、 出力するためには以下のように入力します。

   SELECT number, name, credit_limit
      FROM personnel WHERE credit_limit <= 100
      ORDER BY number ASCENDING;

出力結果は以下のようになります。

   number   name         credit_limit

       4    Scarlatti         $100.00
      17    Wladislaw          $50.00

DESCENDINGキーワードは、リストを降順にソートし、 出力します。 使用例として以下のように入力します。

   SELECT number, name, credit_limit
      FROM personnel WHERE credit_limit <= 100
      ORDER BY number DESCENDING;

データベースにおいて情報を更新をしなくてはならいない場合があります。 例においては、住所や電話番号の変更、あるいは新しい従業員の追加により 必要になります。

従業員名"Kilroy" の電話番号を"426-9681"から"513-8888"に変更するためには 次のコマンドを使用します。

   UPDATE personnel SET phone TO "513-8888"
      WHERE name = "Kilroy";

UPDATEコマンドは、修正するためのテーブル(personnel)を指定し、 SET句は、指定した値(513-8888)にアトリビュートの値を変更します。 WHERE句は、このコマンドにより影響されるレコードを指定します。

対話型インターフェイスを使ってレコードを更新するためには 以下のコマンドを使用します。

   UPDATE personnel WHERE name = "Kilroy";

Empress prompts you with:

   number: 10
   name: Kilroy
   phone: 426-9681 513-8888
   credit_limit: $500.00

   よろしいですか？ .e

   更新レコード数 = 1

personnelテーブルから従業員名"Kilroy"のレコードを削除するためには 以下のように入力します。

   DELETE personnel WHERE name = "Kilroy";

DELETEコマンドはテーブルを指定し、 WHERE句は削除するためのレコードを指示します。 WHERE句を指定しない場合、テーブルのすべてのレコードが 削除されることに注意してください。

テーブルを作成した後にそのテーブルの構造を変更するためには、 ALTER TABLEコマンドを使用します。 このコマンドは、 アトリビュートの追加または削除および アトリビュートの定義を変更することができます。

personnelテーブルのname アトリビュートのサイズを 25 から 30 文字に変更するためには、以下のように入力します。

   ALTER personnel
      CHANGE name CHAR (30,1);

personnelテーブルへ character 型の長さ 50 の addressアトリビュートを追加するためには、 以下のように入力します。

   ALTER personnel
      ADD address CHAR (50,1);

personnelテーブルから credit_limitアトリビュートを削除するためには、 以下のように入力します。

   ALTER personnel
      DELETE credit_limit;

また、上記のそれぞれのコマンドをすべて組み合わせて １つのコマンドとして実行することができます。

   ALTER personnel
      CHANGE name CHAR (30,1)
      ADD address CHAR (50,1)
      DELETE credit_limit;

personnelからemployeesにテーブルの名前を変更するためには、 以下のように入力します。

   RENAME personnel TO employees;

auto_partsテーブル中の part no.からnumbersに アトリビュートの名前を変更するためには、 以下のように入力します。

   RENAME auto_parts."part no." TO numbers;

アトリビュートpart no.は複雑な名前です。 複雑な名前は、数字または non-letter文字から始まるか、 あるいは空白、タブ、ピリオドなどを含まれていることがあります。 Empressコマンドを使用する場合、複雑な名前は 引用符で囲む必要があります。

データベースからpersonnelテーブルを削除するためには、以下のように入力します。

   DROP personnel;

上記を実行することで、データベースからpersonnelテーブルは削除されます。

このコマンドは十分に注意して使用してください 一度、テーブルを削除すると、データベースから永久に消えることになります。 その場合、テーブルを検索するための唯一の方法は、 そのテーブルを作成しなおすことです。 (トランザクション中であれば、トランザクションのロールバックにより テーブルを戻すことも可能かもしれません。)

Empressは、Empress SQLに関するオンラインヘルプを備えています。 HELPコマンドの構文は以下のとおりです。

   HELP [topic];

上記の指定によって、使用可能なヘルプ情報のリストが表示されます。 ある特定のコマンドに関するヘルプ情報を得るためには、以下のように入力します。

   HELP command;

これによって、コマンドの要約が表示されます。 例えばDATABASEコマンドに関する情報を得る場合は、 以下のように使用します。

   HELP DATABASE;

これによって、以下の出力が得られます。

                       ----------  Database  ----------

   "Database" コマンドは現在使用しているデータベースの名前を表示させたり、使用
 しているデータベースを変更したい場合に使用します。

   [構文]

        |database| [DATABASE]  [| onto | FILE ]
        |db      |              | onto |

   コマンド "database" または "db" だけを指定すると現在のデータベース名を表示します。
   データベース名 DATABASE を指定した場合は、現在使用しているデータベースを変更します。

   "database" コマンドの出力は，"into" あるいは "onto" とそれに続くファイル名を
    指定することにより、端末からリダイレクトして新しいファイルを作るか，あるいは既
    に存在するファイルに追加することができます。

   さらに情報が必要な場合は、以下に示すコマンドを入力してください。

                help display;

ヘルプは、対話型インターフェイスから使用することも可能です。 対話型のCREATE、INSERT 、UPDATE コマンドで 利用可能なコマンドの要約を".?"を入力することで得ることができます。

データベース(SQLセッション)から終了するには、以下のように入力します。

   STOP

または

   EXIT

これにより、オペレーティングシステムに制御が戻ります。