この章のコードはSample_SQL10.txtを使用します。
ここではSQLを使用してテーブルを作成する方法を学びます。また、作成したテーブルにデータを登録したり、そのデータを修正したり削除したりする方法も学びます。
9章まではBaseのUI機能であるクエリーデザイン画面上でSQLを実行・検証してきましたが、Baseクエリーはそもそもデータ操作言語(DML)の内、主としてSELECT文を自動生成して実行させるオブジェクトです。そのため、データ定義言語(DDL)やデータ制御言語(DCL)としてのSQLには対応しておりません。（DDL、DCLの詳細については第1章「SQLとは」を参照して下さい。）
そこで、ここ第10章ではBaseクエリーを使わず、メニュー「ツール＞SQL」を選択すると開くダイアログボックス「SQLステートメントを実行する」を使用してSQLを実行し、結果を検証することにします。
テーブル作成
テーブルを作成する場合、CREATE TABLE文を使用します。
SQLの書式は下記のとおりです。
<書式10.1>
CREATE TABLE <テーブル名>(
<列名1> <データ型1> <属性1>,
<列名2> <データ型2> <属性2>,
.
.
.
<列名n> <データ型n> <属性n>
);
※ テーブル名に続く括弧内に各列（フィールド）を定義します。ひとつの列には列名、データ型、属性を記述します。また、列と列の間は「,」で区切ります。
例としてテーブル「Prefecture」をSQL上で定義し、それを実行することによりBase上にテーブルを作成してみましょう。
<SQL例10.1>
CREATE TABLE ”Prefecture“(
”ID” INTEGER PRIMARY KEY,
”Name” VARCHAR(4),
”Pronouncing” VARCHAR(10)
);
※ <SQL例10.1>ではテーブル「Prefecture」（都道府県マスター）を定義します。最初の列は「ID」（都道府県ID）で整数とし、プライマリーキー設定します。次の列は「Name」（名称）でテキスト4文字とします。3番目の列は「Pronouncing」（ふりがな）でテキスト10文字とします。
それでは、このSQLコードを使用してBase上にテーブルを作成することにしましょう。
①下図のようにBase編集画面の左ペイン「データベース」欄の「テーブル」を指定し、メニュー「ツール＞SQL」を選択します。
②すると、下図のようにダイアログボックス「SQLステートメントを実行する」が表示されますので、「実行コマンド」欄に<SQL例10.1>のコードを入力するか、またはSample_SQL10.txtを開いて<SQL例10.1>のコードをコピペし、ボタン[実行]をクリックします。
③すると、下図のように「状態」欄に「1:コマンドは正常に実行されました」と表示されますので、ボタン[閉じる]をクリックします。
④ダイアログボックスを閉じた直後の状態ではBase編集画面上、なにも変化がありませんが、次に下図のようにメニュー「表示＞テーブルの更新」を選択してください。
⑤すると、下図のように右ペイン下部のテーブル一覧にテーブル「Prefecture」が追加されて表示されるので、テーブル名を右クリックし、コンテキストメニューから「編集」を選択します。
（注）これを「テーブルを編集モードで開く」といいます。
⑥すると下図のようにテーブル「Prefecture」が意図どおりに作成されたことが確認できます。
データ入力
テーブルにデータを入力する場合、INSERT文を使用します。
SQLの書式は下記のとおりです。
<書式10.2>
INSERT INTO <テーブル名>(<列名1>,<列名2>,…)
VALUES(<値1>,<値2>,…);
※ テーブル名に続く括弧内に列名を記述し、VALUES句の括弧内の各列に対応する位置にセットする値を記述します。列の数と値の数は同じでなければなりません。
<SQL例10.2>
INSERT INTO ”Prefecture“(”ID“,”Name“,”Pronouncing“)
VALUES(1,’北海道’,’ほっかいどう’);
それでは、このSQLコードを使用してテーブル「Prefecture」にデータ入力することにしましょう。
①図のようにメニュー「ツール＞SQL」を選択して開いたダイアログボックス「SQLステートメントを実行する」の「実行コマンド」欄に<SQL例10.2>のコードをコピペし、ボタン[実行]をクリックします。
②すると、下図のように「状態」欄に「1:コマンドは正常に実行されました」と表示され、「出力」欄に「1rows updated」と表示されますので、ボタン[閉じる]をクリックします。
③下図がテーブル「Prefecture」をダブルクリックして開いた状態です。SQLに記述したとおりのデータが1レコード登録されていることが確認できます。
データ入力（列名省略形）
INSERT文では、全ての列に値をセットする場合に限り、列名を省略することができます。
SQLの書式は下記のようになります。
<書式10.3>
INSERT INTO <テーブル名> VALUES(<値1>,<値2>,…);
<SQL例10.3>
INSERT INTO ”Prefecture“ VALUES(2,’青森県’,’あおもりけん’);
①ダイアログボックス「SQLステートメントを実行する」を開き、「実行コマンド」欄に<SQL例10.3>のコードをコピペし、ボタン[実行]をクリックします。
②すると、下図のように「状態」欄に「1:コマンドは正常に実行されました」と表示され、「出力」欄に「1rows updated」と表示されますので、ボタン[閉じる]をクリックします。
③下図がテーブル「Prefecture」をダブルクリックして開いた状態です。2件目のデータが1レコード追加されていることが確認できます。
データ入力（連続入力）
INSERT文を複数同時に実行することができます。各INSERT文の文末には「;」を忘れずに記述して下さい。このように複数の命令文を一度に実行するSQL文を「マルチステートメント」といいます。
<SQL例10.4>
INSERT INTO ”Prefecture“ VALUES(3,’岩手県’,’いわてけん’);
INSERT INTO ”Prefecture“ VALUES(4,’宮城県’,’みやぎけん’);
INSERT INTO ”Prefecture“ VALUES(5,’秋田県’,’あきたけん’);
①ダイアログボックス「SQLステートメントを実行する」を開き、「実行コマンド」欄に<SQL例10.4>のコードをコピペし、ボタン[実行]をクリックします。
②すると、「状態」欄に「1:コマンドは正常に実行されました」と表示され、「出力」欄に「1rows updated」と表示されますので、ボタン[閉じる]をクリックします。
③下図がテーブル「Prefecture」をダブルクリックして開いた状態です。3件目以降のデータが3レコード追加されていることが確認できます。
データ入力（検証SQL付き）
これまでINSERT文を実行した後に毎回テーブル「Prefecture」をダブルクリックで開いて検証してきました。これでは操作に手間が掛かるので、これから先はダイアログボックス内で検証できるように工夫してみましょう。
<SQL例10.5>
INSERT INTO ”Prefecture“ VALUES(6,’山形県’,’やまがたけん’);
INSERT INTO ”Prefecture“ VALUES(7,’福島県’,’ふくしまけん’);
INSERT INTO ”Prefecture“ VALUES(8,’茨城県’,’いばらぎけん’);
SELECT * FROM ”Prefecture“;
（注）4番目の文は検証のためのSELECT文です。
①下図のようにダイアログボックス「SQLステートメントを実行する」を開き、「実行コマンド」欄に<SQL例10.5>のコードをコピペし、今回からはチェックボックス「”select”ステートメントの出力を表示」をONにしてボタン[実行]をクリックします。
②すると、下図のように「状態」欄に「1:コマンドは正常に実行されました」と表示され、「出力」欄にテーブル「Prefecture」の内容が表示されます。これで6件目以降のデータが3レコード追加されていることが確認できます。
データ更新
データの更新はUPDATE文で行います。
SQLの書式は下記のとおりです。
<書式10.4>
UPDATE <テーブル名> SET <列名1>=<値1>,<列名2>=<値2>,…
WHERE <条件式>;
（注）UPDATE文には必ずWHERE句を記述して更新するレコードを限定して下さい。記述しないと全てのレコードが更新対象になってしまいますので注意してください。
前節で<SQL例10.5>を実行して登録したID=8のレコードのふりがな4文字目が「ぎ」になっています。茨城県は「いばらきけん」と「き」に濁点がないのが正式名称ですので、これを更新します。
<SQL例10.6>
UPDATE ”Prefecture“ SET ”Pronouncing”=’いばらきけん’
WHERE ”ID”=8;
SELECT * FROM ”Prefecture“;
※ 2番目のSELECT文が検証のためのSQLです。
①ダイアログボックス「SQLステートメントを実行する」の「実行コマンド」欄に<SQL例10.6>のコードをコピペし、チェックボックス「”select”ステートメントの出力を表示」をONにしてボタン[実行]をクリックします。
②すると、下図のように「状態」欄に「1:コマンドは正常に実行されました」と表示され、「出力」欄にテーブル「Prefecture」の内容が表示されます。これでID=8のレコードのふりがなが「いばらきけん」に更新されていることが確認できます。
データ削除
データの更新はDELETE文で行います。
SQLの書式は下記のとおりです。
<書式10.5>
DELETE FROM <テーブル名> WHERE <条件式>;
（注）DELETE文もUPDATE文のように、必ずWHERE句を記述して削除するレコードを限定して下さい。記述しないと全てのレコードが削除対象になってしまいますので十分注意してください。
<SQL例10.7>
DELETE FROM ”Prefecture“ WHERE ”ID”=1;
SELECT * FROM ”Prefecture“;
【<SQL例10.7>の実行結果】
ここまで学習を進めてきた方はダイアログボックス「SQLステートメントを実行する」の操作法に十分慣れたと思われますので、これ以降は操作手順の記述を省きます。
①実行すると下図のように「出力」欄にテーブル「Prefecture」の内容が表示されますので、ID=1のレコード（北海道）が削除されているのが確認できます。
テーブル削除
Base編集画面上でテーブルを削除する場合は、テーブル一覧に表示されているテーブル名を指定して右クリックし、コンテキストメニューより「削除」を選択すれば削除することができますが、ここではSQLを使用して削除する方法を学びます。
テーブル削除はDROP文で行います。
SQLの書式は下記のとおりです。
<書式10.6>
DROP TABLE <テーブル名>;
<SQL例10.8>
DROP TABLE ”Prefecture_2“;
【<SQL例10.8>の実行結果】
上記SQLを読んで気づかれたことと思いますが、ファイル「Sample_SQL.txt」に掲載した<SQL例10.8>は「DROP TABLE ”Prefecture“;」になっています。実装されたテーブル「Prefecture」にはこれまでの実装体験で既にデータを入力してあり、この後も検証に使用しますので、ここではテーブル「Prefecture」を削除せず、削除対象テーブルの名前を「Prefecture_2」としました。しかし、テーブル「Prefecture_2」は未だ実装されていないので、このままでは削除することができません。
①そこで、削除するためのテーブル「Prefecture_2」を作成することにします。コードは<SQL例10.1>を使います。ただし、CREATEするテーブル名を「Prefecture_2」に変更して実行して下さい。
②実行後、メニュー「表示＞テーブルの更新」を選択してテーブル「Prefecture_2」が作成されたことを確認して下さい。
③テーブルの存在確認が済んだら、上記<SQL例10.8>を実行して下さい。
④実行後、再びメニュー「表示＞テーブルの更新」を選択してテーブル「Prefecture_2」が削除されたことを確認して下さい。
データ入力（INSERT文によるテーブルTOテーブル）
INSERT文を使用して既にデータ登録してあるテーブルから同じ構造の別テーブルにデータを複写して登録することができます。
SQLの書式は下記のとおりです。
<書式10.7>
INSERT INTO <出力テーブル名>
SELECT * FROM <入力テーブル名> WHERE <条件式>;
<SQL例10.9>
CREATE TABLE ”東北地方“(
”ID” INTEGER PRIMARY KEY,
”Name” VARCHAR(4),
”Pronouncing” VARCHAR(10)
);
INSERT INTO ”東北地方”
SELECT * FROM ”Prefecture” WHERE ”ID”>1 AND ”ID”<8;
SELECT * FROM ”東北地方“;
※ <SQL例10.9>は3つのSQL文からなるマルチステートメントです。
1番目の文は<SQL例10.1>と同じ文ですが、テーブル名を「東北地方」としています。このように出力先のテーブルを最初に作成しておきます。
2番目の文はINSERT文ですが、VALUE句の代わりに入力テーブルから検索するSELECT文をはめ込んでいます。（これもサブSQLの例です。）こうすることにより、入力した各列の値をそのまま出力テーブルの各項目にセットすることが可能になります。
3番目の文は検証のためのSELECT文です。
【<SQL例10.9>の実行結果】
下図のように「出力」欄にテーブル「東北地方」の内容が表示されます。これでID=2からID=7までの東北6県のレコードがテーブル「東北地方」に登録されたことが確認できます。
※ もちろん、ダイアログボックスを閉じ、メニュー「表示＞テーブルの更新」を選択した後にテーブル「東北地方」をダブルクリックして開いてもデータ内容を確認することが可能です。
データ入力（SELECT文によるテーブルTOテーブル）
SELECT文を使用すると予め出力テーブルを作成しておかなくてもデータを複写して登録することができます。また、出力テーブルの種類を選択することが可能です。
SQLの書式は下記のとおりです。
<書式10.8>
SELECT <列名1>,<列名2>,…
INTO [CACHED|TEXT|TEMP] <出力テーブル名>
FROM <入力テーブル名>
WHERE <条件式>;
(注1）
INTO句の[CACHED|TEXT|TEMP]はテーブルの種類のオプションで、「CACHED」はデフォルトです。各オプションの意味は下記の通りです。
・CACHED:Base上にオブジェクトとして登録される、キャッシュテーブルが作られる
・TEXT:キャッシュテーブルが作られると同時にデータベースファイルの外側にCSVファイルが出力される
・TEMP:テンポラリーテーブルが作られる
(注2）
指定したテーブルの列のタイプがそのまま出力テーブルの出力テーブルの列のタイプとして使われます。
<SQL例10.10>
SELECT ”ID“,”Name“,”Abbreviation”
INTO CACHED ”State_2”
FROM ”State“;
SELECT * FROM ”State_2“;
【<SQL例10.10>の実行結果】
下図のように「出力」欄にテーブル「State_2」の内容が表示され、テーブル「State」に登録された全レコードがコピーされて出力されたことが確認できます。
もちろん、今回もメニュー「表示＞テーブルの更新」を選択してテーブル「State_2」が作成されたことを確認することができます。
<SQL例10.11>
SELECT ”ID“,”Name“,”Abbreviation”
INTO TEXT ”State_3”
FROM ”State“;
SELECT * FROM ”State_3“;
【<SQL例10.11>の実行結果】
下図のように「出力」欄にテーブル「State_3」の内容が表示され、テーブル「State」に登録された全レコードがコピーされて出力されたことが確認できます。
今回もメニュー「表示＞テーブルの更新」を選択してテーブル「State_3」が作成されたことを確認してみましょう。
下図の破線で囲ったオブジェクトが作成されたテーブル「State_3」ですが、外部ファイルに出力された状態を示すアイコンになっています。
また、実行・検証に使用しているデータベースファイルBase_SQL練習.odbが格納されているフォルダーの直下にCSVファイル「state_3.csv」が外部ファイルとして出力されていますので、エディター等で開いて内容を確認してみましょう。
下図はサクラエディターで開いた状態のstate_3.csvです。
（注）現在のところ、残念ながら全角文字には対応していないので文字化けが生じます。半角文字または数値のフィールドに限定して活用するとよろしいでしょう。
<SQL例10.12>
SELECT ”ID“,”Name“,”Abbreviation”
INTO TEMP ”State_4”
FROM ”State“;
SELECT * FROM ”State_4“;
【<SQL例10.12>の実行結果】
下図のように「出力」欄にテーブル「State_4」の内容が表示され、テーブル「State」に登録された全レコードがコピーされて出力されたことが確認できます。
ここでメニュー「表示＞テーブルの更新」を選択してテーブル「State_4」が作成されたことを確認してみると、今回はテーブル一覧に表示されません。このように、テンポラリーテーブルはテーブル一覧に表示されないのです。
ちなみに<SQL例10.12>を再度実行してみると、「状態」欄にエラーメッセージ「1:Table already exist State_4 in statement…」が表示されて実行されません。
<SQL例10.13>
DROP TABLE IF EXISTS ”State_4“;
SELECT ”ID“,”Name“,”Abbreviation”
INTO TEMP ”State_4”
FROM ”State“;
SELECT * FROM ”State_4“;
最初の文は予めテーブル「State_4」を削除するためのSQLですが、存在しない場合にエラーとなることを避けるために「IF EXISTS」という条件を加えています。こうしておけば、このSQLを最初に実行する場合でも２度めに実行する場合でもエラーを発生させずに実行させることができます。
2番目以降の文は<SQL例10.12>と同じです。
【<SQL例10.13>の実行結果】
下図のように<SQL例10.12>と同じ結果が確認できます。
テーブルの存在確認
前節でも述べたように、テンポラリーテーブルはテーブル一覧に表示されません。テンポラリーテーブルを作成した直後ならテーブル名を知っていますから、不要な場合にDROP文で削除できますが、何度かSQLを実行した後に、一体いくつのテンポラリーテーブルが登録されているか知りたくなることがあるでしょう。そうした場合はテーブル「SYSTEM_TABLES」から情報を得る方法があります。
SQLの書式は下記のとおりです。
<書式10.9>
SELECT <列名1>,<列名2>,…
FROM <スキーマ名>.<テーブル名>
WHERE <条件式>;
(注）実はSELECT文ではFROM句で<スキーマ名>.<テーブル名>と記述するのが正式な書式です。
ここで「スキーマ」について解説しておきます。DBMSを学習すると「スキーマ」という用語が出てきて、その扱い方法に苦労する事があります。スキーマは簡単にいうと、「データベース全体を理解するために割り当てられた構造」のことです。Windowsエクスプローラーにおけるフォルダーのようなものと考えて頂いてもいいでしょう。そして定義されたスキーマの配下にテーブルを定義するわけです。皆さんがBaseを使用する上で、普段はスキーマを意識する必要はありませんが、内部的には「PUBLIC」というスキーマ名が付けられています。
Baseにはスキーマ「PUBLIC」のほかに、システムが管理するために用意されているスキーマ「INFORMATION_SCHEMA」があります。今回はその配下にあるテーブル「SYSTEM_TABLES」から情報を取得します。
<SQL例10.14>
SELECT ”TABLE_NAME”
FROM ”INFORMATION_SCHEMA”.”SYSTEM_TABLES”
WHERE ”TABLE_NAME“ NOT LIKE ’SYSTEM_%’;
（注）テーブル「SYSTEM_TABLES」には全てのテーブル名が格納されていますが、その中にはシステムが管理するためのテーブルもあります。それらは頭に「SYSTEM_」がついていますので、WHERE句にキーワード「NOT LIKE」を付け、あいまい検索で「SYSTEM_」がついているテーブル名を対象から外しています。
蛇足ですが、<SQL例10.14>からキーワード「NOT」を削除して実行するとシステムが管理するためのテーブルの名前の全貌が分かります。
【<SQL例10.14>の実行結果】
下図の破線で囲ったテーブル名が、テーブル一覧に表示されないテンポラリーテーブル「State_4」です。
※ このSQLはクエリーデザイン画面上でも実行可能です。
テーブル更新
ALTER文を使用してテーブルの構造や属性等の変更を行うことができます。
列の追加
テーブルに列を追加するにはALTER TABLE文にADD COLUMN句を記述します。
SQLの書式は下記のとおりです。
<書式10.10>
ALTER TABLE <テーブル名>
ADD COLUMN <列名> <データ型> <属性>;
<SQL例10.15>
ALTER TABLE ”State”
ADD COLUMN ”Capital” VARCHARA(10);
（注）テーブル「State」に新たな列「Capital」（州都）を追加するSQLです。列は最終列として追加されます。
【<SQL例10.15>の実行結果】
<SQL例10.15>を実行し、メニュー「表示＞テーブルの更新」を選択した後にテーブル「State」を編集モードで開いてください。
下図のように破線で囲った部分が新たに追加された列「Capital」です。
列の削除
テーブルの列を削除するにはALTER TABLE文にDROP COLUMN句を記述します。
SQLの書式は下記のとおりです。
<書式10.11>
ALTER TABLE <テーブル名>
DROP COLUMN <列名>;
<SQL例10.16>
ALTER TABLE ”State”
DROP COLUMN ”Capital”;
【<SQL例10.16>の実行結果】
<SQL例10.16>を実行し、メニュー「表示＞テーブルの更新」を選択した後にテーブル「State」を編集モードで開いてください。列「Capital」が削除されたのが確認されます。
自動採番の値変更
最後にALTER TABLE文で自動採番の値を変更する例を紹介しましょう。
プライマリーキーを自動採番で登録するテーブルは前回までの採番の値を記憶していますが、その値をリセットするSQLです。
SQLの書式は下記のとおりです。
<書式10.12>
ALTER TABLE <テーブル名>
ALTER COLUMN <列名> RESTART WITH <値>;
※ このSQL例は当塾「知っていますか」のページ、「Baseでプライマリーキーの自動採番をリセットするには」で詳しく説明していますので、そちらの記事をご覧ください。
【講座を終えるに当たって…】
当講座では全ての機能を紹介しきれていません。Base SQLにはまだまだ多くの関数や構文が用意されています。
もっと詳しく知りたい方は他のDBMS用SQLであってもBase上で実行したら有効に働く場合がありますので、試してみて下さい。
第1章でも説明したように、この講座はBaseのデフォルト埋め込みデータベースエンジンHSQLDBの仕様に沿ってSQL文を紹介しましたが、HSQLDB用SQLはMySQLに文法が近いので、より深く研究される方はMySQLの関数や構文を参考にするといいでしょう。
これにてBase SQL講座は終了と致します。お疲れ様でした。