| データベース【TIBQueryでSQLを使おう(4)】 |
２つ目のテーブルを作成しましたので、３つ目のテーブルを作成します。この３つ目のテーブルでは前回２つのテーブルを参照する事を意識し、それぞれの番号Nを参照するための２つのフィールドを用意します。
プロジェクト「d007」の準備を行います。「C:\DelphiProgram\jww」フォルダの中に「d007」というフォルダを作成し、前回の「d006」からファイルを全部コピーしてきます。Delphi６を起動します。メニュー「プロジェクト」→「オプション」を実行し、下記の設定を行います。
［アプリケーション］頁
タイトル | d007−データベース作成 |
［ディレクトリ／条件］頁
パス及びディレクトリ | C:\DelphiProgram\db\d007 |
「C:\DelphiProgram\db\d007」の中に「d007.dpr」として保存
メニュー「表示」→「プロジェクトマネージャ」
を実行し、コード画面の左側にドッキングさせておきます。
メニュー「プロジェクト」→「d007を再構築」を実行し、エクスプローラ等で不要になった「C:\DelphiProgram\db\d007」内にある「d006.〜」のファイルを消しておきます。
前回まで「TEST1」「TEST2」というテーブルを作成していましたので今回は「TEST3」というテーブルを作成します。テーブル「TEST3」の内容は下記のようにします。
| N | 整数 |
| RN1 | 整数 TEST1への参照用 |
| RN2 | 整数 TEST2への参照用 |
| SI | 整数(SMALLINT) |
整数INTEGER型の入力範囲は、-2,147,483,648〜2,147,483,647が可能な32bit整数、つまり Long Integer：倍長整数です。整数だけれどもここまで必要が無い場合、少しでもデータ量を節約したい場合には、入力範囲-32,768〜32,767をもつ16bit整数のSMALLINT型というものがあります。例えば、単純な状態フラグや、都道府県番号＝1〜47のように最初から小さい数字であると分かっているデータの場合には、SMALLINT型を使った方が良いでしょう。逆に、家計簿の金額の場合には、最大３万円強までしか扱えませんので、それだとまずいでしょうから INTEGER型を使います。INTEGER型でも最大20億円強までですから、中企業・大企業の経理用データの場合はダメになってきます。
画面レイアウトは下記のようにします。
上記に合わせてプログラムを変更します。まずはテーブル作成の箇所です。
procedure TForm1.Button1Click(Sender: TObject);
(・・・中略・・・)
// テーブル作成
try
IBTransaction1.Active := False ;
IBTransaction1.StartTransaction ;
IBQuery1.Close;
IBQuery1.ParamCheck := False ;
with IBQuery1.SQL do begin
Clear ;
Add('CREATE TABLE TEST3 (' );
Add(' N INTEGER NOT NULL UNIQUE,');
Add(' RN1 INTEGER,');
Add(' RN2 INTEGER,');
Add(' SI SMALLINT');
Add(' );');
end;
(・・・後略・・・) |
登録部分のプログラムです。なお、一旦整数化して再度文字列に戻す作業をして、文字列で受け取ってそのまま扱っていない理由は、数値でない文字が入った場合の対処のためです。
// [登録]
procedure TForm1.Button3Click(Sender: TObject);
var
i,j,k,l : integer ;
p1,p2,p3,p4 : string ;
begin
if not(IBDatabase1.Connected) then exit ;
i := SInt(Edit3.Text);
j := SInt(Edit4.Text);
k := SInt(Edit5.Text);
l := SInt(Edit6.Text);
if (l < -32768) then l :=-32768;
if (l > 32767) then l := 32767;
p1 := IntToStr(i);
p2 := IntToStr(j);
p3 := IntToStr(k);
p4 := IntToStr(l);
try
IBTransaction1.Active := False ;
IBTransaction1.StartTransaction ;
IBQuery1.Close;
IBQuery1.ParamCheck := False ;
with IBQuery1.SQL do begin
Clear ;
Add('INSERT INTO TEST3 (');
Add(' N, RN1, RN2, SI)');
Add('VALUES (');
Add(' ' + p1 + ',');
Add(' ' + p2 + ',');
Add(' ' + p3 + ',');
Add(' ' + p4 + ');');
end;
if not (IBQuery1.Prepared) then IBQuery1.Prepare;
IBQuery1.ExecSQL;
IBTransaction1.Commit;
ShowMessage('登録OK');
except
IBTransaction1.Rollback ;
ShowMessage('登録失敗');
end;
IBQuery1.Active := False ;
IBTransaction1.Active := False ;
end; |
同様に、表示、編集、削除の箇所も修正します。
これでテーブルが３つになりました。
さて、これまで[表示]を行った際に SQL文
を実行する、とあっさり書いてきましたが、ここではもう少し詳しく記述します。
テーブル内容を選択・取得し、内容を読み出すには、SQL文「SELECT」を実行します。
| SELECT <フィールド名> FROM <テーブル名>; |
フィールド名には、取得したいフィールド名を羅列します。上記「TEST3」の場合で例えば「RN1」「RN2」だけを取得したい場合には
| SELECT RN1,RN2 FROM TEST3; |
のように記述します。これを実行すると・・・
と、その前に、SQL文を変えるたびにプログラムを修正するというのは手間なので、画面レイアウトを下記のようにして、Edit7にSQL文を入力して右端の[表示]をクリックしたら、その内容を表示するようにしてみます。
[表示]ボタン Button8 をダブルクリックしてクリック時のプログラムを記述します。Edit7の内容を見る以外は、以前の[表示]とほぼ同じです。
// [表示(2)]
procedure TForm1.Button8Click(Sender: TObject);
var
s : string ;
i : integer ;
begin
if not(IBDatabase1.Connected) then exit ;
s := Edit7.Text ;
if (s = '') then exit ;
Memo1.Lines.Clear ;
IBQuery1.Active := False ;
IBTransaction1.Active := False ;
IBQuery1.ParamCheck := True ;
with IBQuery1.SQL do begin
Clear ;
Add(s) ;
end;
//
try
IBQuery1.Open ;
IBQuery1.First ;
while not(IBQuery1.Eof) do begin
s := '';
for i:=0 to IBQuery1.FieldCount-1 do
s := s + IBQuery1.Fields[i].AsString + ',';
Memo1.Lines.Add(s);
IBQuery1.Next ;
end;
except
;
end;
IBQuery1.Active := False ;
end; |
適当にデータを作ってみます。例えば
1,1,1,10,
2,2,2,20,
3,3,4,30,
4,4,3,444,
5,1,4,500,
100,1,1,100,
50,2,1,50,
30,3,1,30, |
のように入力したとします。
それでは話を戻して、Edit7の箇所に
「SELECT RN1,RN2 FROM TEST3;」
と入力して[表示]してみて下さい。以下のように表示されます。
1,1,
2,2,
3,4,
4,3,
1,4,
1,1,
2,1,
3,1, |
見て分かるように、上記の「RN1」「RN2」の箇所だけが表示されます。次に「SELECT SI,RN2,RN1 FROM TEST3;」と入力して[表示]してみて下さい。以下のように表示されます。
10,1,1,
20,2,2,
30,4,3,
444,3,4,
500,4,1,
100,1,1,
50,1,2,
30,1,3, |
列の順番が指定したように表示される事が分かります。そして「*」を指定すると、全てのフィールドを取得して表示する、という事になります。
次に、全てのデータを取得したくない、特定の行だけを読み出したい場合に、条件式を指定する方法です。
| SELECT <フィールド名> FROM <テーブル名> WHERE <条件式>; |
既にデータの編集の時に記述しましたが、ここでも利用可能です。使える条件式は以下のようになっています。
| 等号不等号 |
< > <= >= = <>
値が数値型以外は ''で括る |
| BETWEEN <A> AND <B> | 値が<A>〜<B>の間のもの |
| IN (<A>,<B>,<C>,〜) | 値が<A>,<B>,<C>,〜のどれかの時 |
| LIKE '〜' |
文字列のパターンマッチング
%：ワイルドカード
'%ABC' ：〜〜ABC の値のもの
'ABC%' ：ABC〜〜 の値のもの
'%ABC%'：〜ABC〜 の値のもの |
| NOT |
否定；〜でない 他の演算子と合わせる
NOT A=B：A=Bではないもの |
| IS NULL |
値が入って無い 値がヌル値であるもの
0 や 空白文字 は対象外 |
| AND | 且つ |
| OR | 又は |
| 括弧（） | 複数条件 |
それでは「SELECT * FROM TEST3 WHERE N=1;」と入力して[表示]してみて下さい。以下のように表示されます。
フィールド名「N」の値が「1」のものだけを表示します。次に「SELECT * FROM TEST3 WHERE RN1=1;」と入力して[表示]してみて下さい。以下のように表示されます。
1,1,1,10,
5,1,4,500,
100,1,1,100, |
「SELECT * FROM TEST3 WHERE N>5;」の場合は「N」の値が「5」よりも大きいものだけが表示されます。
100,1,1,100,
50,2,1,50,
30,3,1,30, |
「SELECT * FROM TEST3 WHERE (N>=3)AND(N<=5);」の場合は「N」の値が「3」以上「5」以下のものだけが表示されます。
3,3,4,30,
4,4,3,444,
5,1,4,500, |
これは「SELECT * FROM TEST3 WHERE N BETWEEN 3 AND 5;」という風に記述する事も出来ます。
なお、記述にミスがあった場合には例外（エラー）が発生しますので注意して下さい。
フィールド名の箇所には、SQL関数を記述する事が出来ます。
COUNT関数：行の数を求める
「COUNT(<式>)」：NULLでない行をカウント
「COUNT(*)」：NULLを含めて全て数える
<式>にはフィールド名や算術式を指示出来ます。
SELECT COUNT(<式>) FROM <テーブル名>;
SELECT COUNT(*) FROM <テーブル名>; |
例えば「SELECT COUNT(*) FROM TEST3;」とすると
と表示されます。つまり８個のデータがあるという事です。勿論「SELECT COUNT(*) FROM TEST3 WHERE RN1=1;」というような指示も可能です。「RN1」の値が「1」のデータが何個あるかが分かります。
MAX関数：<式>の値の最大値を求める
| SELECT MAX(<式>) FROM <テーブル名>; |
例えば「SELECT MAX(N) FROM TEST3;」とすると
と表示されます。フィールド「N」の最大値は「100」であるという事が分かります。
MIN関数：<式>の値の最小値を求める
| SELECT MIN(<式>) FROM <テーブル名>; |
例えば「SELECT MIN(SI) FROM TEST3;」とすると
と表示されます。フィールド「SI」の最小値は「10」であるという事が分かります。
SUM関数：<式>の値の合計値を求める
| SELECT SUM(<式>) FROM <テーブル名>; |
例えば「SELECT SUM(N) FROM TEST3;」とすると
と表示されます。フィールド「N」の値を全て加算した結果は「195」であるという事が分かります。
AVG関数：<式>の値の平均値を求める
| SELECT AVG(<式>) FROM <テーブル名>; |
例えば「SELECT AVG(N) FROM TEST3;」とすると
と表示されます。フィールド「N」の値の平均値は「24」であるという事が分かります。整数値ですから丸められています。小数点以下は切り捨てのようです。
などのように、該当データを参照してプログラムを作るというような事をしなくても上記のような関数を利用する事が出来れば容易になるという事です。その他については SQL関連書籍を参考にして下さい。
さて、次に並び替え（ソート）についてです。
特別指定しない場合は、登録された順序で表示されます。登録されたデータが下記のような場合、「N」の値で並び替えて表示したくなります。
1,1,1,10,
2,2,2,20,
3,3,4,30,
4,4,3,444,
5,1,4,500,
100,1,1,100,
50,2,1,50,
30,3,1,30, |
こういった場合には「SELECT * FROM TEST3 ORDER BY N;」とします。すると
1,1,1,10,
2,2,2,20,
3,3,4,30,
4,4,3,444,
5,1,4,500,
30,3,1,30,
50,2,1,50,
100,1,1,100, |
のように綺麗に並び替えて表示されます。
SELECT <フィールド名> FROM <テーブル名> ORDER BY <フィールド名> [ASC];
SELECT <フィールド名> FROM <テーブル名> ORDER BY <フィールド名> DESC; |
後に「ASC」を付けるか省略をすると昇順、「DESC」を付けると降順となります。フィールド「N」は UNIQUE指定を付けていますので同じ値は存在しないのですが「RN1」等は同じ値を持つ事がありますので第２のキーを指定する事も出来ます。
「SELECT * FROM TEST3 ORDER BY RN1;」とすれば
1,1,1,10,
100,1,1,100,
5,1,4,500,
2,2,2,20,
50,2,1,50,
30,3,1,30,
3,3,4,30,
4,4,3,444, |
となりますが、「SELECT * FROM TEST3 ORDER BY RN1,N;」とすると
1,1,1,10,
5,1,4,500,
100,1,1,100,
2,2,2,20,
50,2,1,50,
3,3,4,30,
30,3,1,30,
4,4,3,444, |
という具合になります。後につけた「N」が並び替えの第２キーとなります。
勿論、「WHERE」節を併用する事も出来ます。
| SELECT <フィールド名> FROM <テーブル名> WHERE <条件式> ORDER BY <フィールド名>; |
例えば「SELECT * FROM TEST3 WHERE N>5 ORDER BY N;」とすると
30,3,1,30,
50,2,1,50,
100,1,1,100, |
という具合に並び替えられて表示されます。
さて、いよいよリレーショナルデータベース(RDB)な話と行きます。
テーブル「TEST3」では、「RN1」が「TEST1」の「N」への参照番号、「RN2」が「TEST2」の「N」への参照番号、という具合に書きましたが、フラットなデータベースで考えた場合、予め「TEST1」「TEST2」を読み込んでおいて、それらを参照して、該当するデータを検索して表示するプログラムを作成する、等と想像しますが、リレーショナルデータベースでは、例えば
SELECT * FROM テーブル名1,テーブル名2
WHERE テーブル名1.フィールド名=テーブル名2.フィールド名; |
のように記述すると、テーブル名１のあるフィールドと、テーブル名２のあるフィールドが関連付けられるキーとなり、テーブル名１とテーブル名２を関連付けて参照する事が出来るようになってテーブルの結合を行う事が出来ます。
テーブル「TEST1」の内容が
1,1234567
2,No2-TEST DATA
3,No3-Sample
4,444-4444-4444 |
であり、テーブル「TEST2」の内容が
1,10,100,No.1,
2,20,200,No.2 ,
3,30,300,No.3, |
であるとします。
「SELECT * FROM TEST3;」とした場合は
1,1,1,10,
2,2,2,20,
3,3,4,30,
4,4,3,444,
5,1,4,500,
100,1,1,100,
50,2,1,50,
30,3,1,30, |
でしたが、これを
「SELECT *
FROM TEST3,TEST1
WHERE TEST3.RN1=TEST1.N;」
のようにすると、
1,1,1,10,1,1234567,
2,2,2,20,2,No2-TEST DATA,
3,3,4,30,3,No3-Sample,
4,4,3,444,4,444-4444-4444,
5,1,4,500,1,1234567,
100,1,1,100,1,1234567,
50,2,1,50,2,No2-TEST DATA,
30,3,1,30,3,No3-Sample, |
のように結合されて表示されます。FROMの後に「TEST3」を先に書いていますので、「TEST3」が優先となります。単純に結合されただけなので不要な部分もありますから、必要なフィールド名だけを表示するよう
「SELECT TEST3.N,TEST1.MOJIDATA,TEST3.RN2,TEST3.SI
FROM TEST3,TEST1
WHERE TEST3.RN1=TEST1.N;」
とします。
1,1234567,1,10,
2,No2-TEST DATA,2,20,
3,No3-Sample,4,30,
4,444-4444-4444,3,444,
5,1234567,4,500,
100,1234567,1,100,
50,No2-TEST DATA,1,50,
30,No3-Sample,1,30, |
フィールド名の欄は、テーブル名が２つあるのでどちらのテーブルのフィールドなのかを明記するために「テーブル名.フィールド名」と記述する必要があります。
それでは次に「TEST2」も組み込んでみましょう。
「SELECT TEST3.N,TEST1.MOJIDATA,
TEST2.VAL1,TEST2.VAL2,TEST2.MOJI,TEST3.SI
FROM TEST3,TEST1,TEST2
WHERE TEST3.RN1=TEST1.N AND TEST3.RN2=TEST2.N;」
とします。「RN2」から参照される「TEST2」の「N」値に「4」はありませんので、「TEST3」のN=3、N=5のデータは除外されます。
1,1234567,10,100,No.1,10,
2,No2-TEST DATA,20,200,No.2 ,20,
4,444-4444-4444,30,300,No.3,444,
100,1234567,10,100,No.1,100,
50,No2-TEST DATA,10,100,No.1,50,
30,No3-Sample,10,100,No.1,30, |
例えば住所録で、都道府県名を管理するテーブルと個人データテーブルとに分けておき、後者で都道府県を番号で指示するようにしておけば、住所録エディタでは、コンボボックスで都道府県を選択するようにして数値指定＆数値でのデータ登録、表示では上記のように結合表示出来ますので、入力の際に楽になり、データサイズを減らす事が出来る、ひょっとすると、その都道府県テーブルは他のプログラムで再利用出来るかもしれない、というようなメリットがあります。
これで、ざっとですが SQLについての説明を終了、という事にします。SQLはこれ以外にももっと色々な機能がありますが、それについては別途、SQL関連書籍を参考にして下さい。データベースソフトによって対応・非対応や記述方法が異なるという場合もありますが、Firebirdに添付の文書なども参考にしてみて下さい。
|
