長谷川 裕行 （はせがわ ひろゆき）
有限会社 手國堂 代表取締役
http://www.hirop.com/
テクニカルライターとして活躍。プログラミングに関する著書多数、DB Magazineなどにも多くの記事を提供している。 |
柔軟なデータベース制御を実現するには、効率的なクエリーを適切に使用することが重要です。クエリーはデータベース内のオブジェクトとして保存できる他、プログラム内のコードとして記述することも可能です。その場合に活躍するのがSQL文です。
今回は、SQLの基礎的な使い方を紹介しましょう。
データベースの中核となるのは情報を貯えたテーブルです。しかしテーブルだけでは、単に「データが記録されているだけ」に過ぎません。貯えられた情報を活用するにはそれらを取り出し、並べ替え、加工する必要があります。それを担当するのがクエリー※です。
※従来は「クエリー」と表記されていましたが、Access2000から「クエリ」と表記されるようになりました。が、用語の混乱を避けるため、本稿では従来通り「クエリー」と書き表すことにします。
- コーディングによる -
- テーブルの加工は非効率 -
ADOなどのデータアクセスオブジェクトを使う現在のプログラミング手法では、プログラミング言語の側でデータベース内のテーブルに対して、直接操作を行うコードを記述することはまずありません。もちろん不可能ではありませんが、著しく非効率的です。
例えば、
顧客名簿テーブルのレコードを
氏名の読みの五十音順に並べ替える
年齢順に並べ替える
女性のレコードだけ抜き出す
といった処理を、コーディングで解決することを考えてみましょう。レコードを先頭から読み進み、「読み」や「性別」のフィールドの値を参照して、条件に合致していれば配列に格納する――といったループ構造を、逐一コードとして記述しなければなりません。
- クエリーはデータ加工の要 -
これがどのくらい面倒で、かつ間違いの入り込む可能性が高くなるかは、経験者ならすぐに分かるはずです。そんなことに時間を割くより、予め作成しておいたクエリーを指定して開いた方が、遥かに簡単でかつ安全です。
データベース操作の要はクエリーであり、テーブルに対する加工は、クエリーで行わせるのが最も効率的です。目的に沿ったクエリーを作っておけば、プログラマーはクエリーの機能とテーブルの構造だけを意識すればよくなります。その中で何が行われているかを意識する必要はありません。要するに、クエリーも関数と同じブラックボックスとなるのです。
- データベース内のクエリーを扱う -
データベースにはテーブルの他にクエリーも格納できます。例えばAccessの場合なら、GUIによって目的とするクエリーを簡単に作れます。こうして作成されたクエリーはAccessのオブジェクトとなるため、VBからはデータアクセスオブジェクトを通じて、間接的に呼び出すことになります。
「呼び出す」と書きましたが、実際データベース内のクエリーは「プログラムの外部に存在する関数」と捉えても構わないでしょう。Accessではクエリーを2種類に分けて捉えるようになっています。選択クエリーとアクションクエリーです(その他に、ユニオンクエリーがあります)。
- 「開く」と「呼び出す」 -
選択クエリーは一般に言うビューで、指定した内容に基づいて新たなレコードセットを生成します。一方アクションクエリーはレコードセットを生成することはなく、元になるテーブルの内容を書き換えたり、新たなテーブルを生成するなど、データベースに対して直接的な加工を行います。
両者の違いは、操作対象のデータベース(テーブルではなくデータベース)に対して、何らかの変化を引き起こすかどうかです。
単にレコードセットを生成するだけで、データベースに対して変更を引き起こさない選択クエリーは、テーブルと同じように名前を指定して開きます。一方データベースを書き換えるアクションクエリーは、データベース内部のオブジェクトに対する加工手順をプログラム化した「ストアドプロシージャ」として扱われ、実行または呼び出すことになります。
AccessならAccessだけ――というように、常に1種類のデータベースしか扱わないのなら、データベース内部にクエリーを用意しておくのが最も簡単です。しかし、様々なデータベースを扱う場合は、少し事情が異なってきます。
- クエリーをデータベースの外に置く -
データベース内にオブジェクトとしてクエリーを作成し保存してしまうと、そのクエリーはそれを作ったデータベースでしか使用できません。クエリーエディタを使い、外部にクエリーファイルとして保存することも可能ですが、その場合でも対象となるデータベースは固定されてしまいます。
よって、異なるデータベース・エンジンにたいして同じ結果を得るためのクエリーが必要な場合、それぞれのデータベース・エンジンごとにクエリーを作成しなければならなくなります。
また、たとえ1種類のデータベースしか使用しないことが分かっている場合でも、プログラムの仕様変更に伴ってクエリーの機能まで変更しなければならない場合、データベースにクエリーを内包してしまう方法では、プログラムとデータベースの両方に対してメンテナンスを施す必要が生じます。
データベースは単なる情報の入れ物ではなく、クエリーによって様々な加工の手順まで保存できる訳ですが、その内容が大規模で複雑になればなるほど、元になる情報とそれを扱う手順とは、切り離しておいた方が効率的になります。
つまり、
データベースはできるだけ情報のみを保存し
加工の過程はプログラムの側で対応する
という形が理想的になってくるのです。
- SQLでデータベースを制御する -
プログラムの側でクエリーを機能させるには、SQL文を使います。SQLは"Structured Query Language"の略で「構造化照会言語」と訳されます。データベースを扱う際の共通言語です。
どのようなクエリーも、最終的にはSQL文となります。従って、VBのコード内でデータアクセスオブジェクトに対してSQL文を発行すれば、データベース内部にクエリーを抱え込む必要はなくなります。
SQL文は文字による命令なので、プログラミング言語と同じわずらわしさがあります。記述ミスによる誤動作も覚悟しなければなりません。が、先に挙げたデータベースにクエリーを内包する方法のデメリットをカバーすることが可能です。
つまり、操作対象のデータベースがどのような形式であっても、データアクセスオブジェクトが適切にそのデータベースを制御できるよう設定されていれば、VBのコードの側でデータベースを制御できる――ということです。
当然、メンテナンスもVBのソースコードに対してのみ行えばよいことになります(もちろん、テーブル構造自体を変更するような場合は別ですが)。
先述したようにSQL文は記述式の言語なので、Accessでクエリーを作るように感覚的な操作は期待できません。が、VBなどのプログラミング言語に比べて言語構造が単純で、覚えなければならない決まりごとも少ないため、さほど難しくはありません。
慣れてしまえば、Accessでクエリーを作るより手早くテーブルの操作を記述できるようになるでしょう。
- SQL文の構造 -
SQLでは、ステートメント、句、演算子、集合関数などの要素を用いてクエリーを作ります。それぞれの役割を紹介しておきましょう。
| ・ |
ステートメント:データベースに対する処理 |
| ・ |
句:処理の条件 |
| ・ |
演算子:複合条件の指定 |
| ・ |
集合関数:値の計算 |
例えば、テーブル"T_Item"から"ItemID"フィールドが"31050"のレコードを抽出するなら
| SELECT * FROM T_Item WHERE ItemID='31050';
(1) (2) (3) (4) (5) |
| (1) |
SELECTはステートメントで、テーブルから必要なフィールドを抜き出すことを意味します。 |
| (2) |
FROMは句で、操作対象のテーブルを指定します。 |
| (3) |
T_Itemが、FROM句で指定されるテーブル名です。 |
| (4) |
WHEREは句で、FROM句で指定されたテーブルから、どのようなレコードを抜き出すかを指示します。 |
| (5) |
がWHERE句で指定される「レコードの抽出方法」です。ここではItemIDフィールドが'31050'であるレコードを抽出する命令となります。 |
- SQL文の規則 -
SQL文は、以下のような規則に基づいて記述します。
| 1. |
ステートメント、句などの予約語は1バイト文字で、通常は大文字とする
実際にはこれは決まりではないので、小文字で記述しても構いません。しかし、小文字で記述するとプログラミング言語本来の予約語や変数名などのシンボルと区別できなくなるため、すべて大文字で記述します。
|
| 2. |
フィールド名にスペースを含む場合は、フィールド名を[ ]で囲む
スペースはステートメントや句の区切り記号となるため、スペースの入ったフィールド名は必ず[ ]で囲みます。フィールド名には2バイト文字も使えますが、その場合も[
]で囲んでおいた方が安全です。 |
| 3. |
文字列は' 'で囲む
VBでは" "で囲みますが、SQLでは' 'を使います。 |
| 4. |
予約語やテーブル名、条件などの間は1バイトスペースまたはタブで区切る
通常は、1個のスペースを使います。 |
| 5. |
フィールド指定でテーブル名を明示する場合は、フィールド名の前にテーブル名と「.」を付ける
この書き方は、異なるテーブルに同じフィールド名があるときに用います。例えば
T_Item.Name:テーブル"T_Item"のNameフィールド
T_Cstm.Name:テーブル"T_Cstm"のNameフィールド
といったように区別します。
1つのテーブルしか扱わない場合や、同名フィールドが存在しない場合でも、この書き方でテーブル名を明示して構いません。
|
| 6. |
文末にはセミコロン(;)を付ける
文末の;がなくても正しく認識するデータベース・エンジンもありますが、付けておく方が安全です。 |
この他「SQL文の間には、適宜改行を挿入できる」という規則もあります。
| SELECT * FROM T_Item WHERE ItemID='31050'; |
としても、文末の;までの間に改行を入れて
SELECT *
FROM T_Item
WHERE ItemID='31050'; |
としても同じです。
しかし、VBではSQL文を文字列として扱うため、間に改行を入れると「文字列が未完結」とみなされます。以降の説明では1行の字数の都合で複数行に分けて記述する場合がありますが、VBのコードとして記述する際には文字列として扱われることに注意してください。
便宜上Accessの「選択クエリー」という表現を用いますが、要するに、元のレコードセットを変化させることなく、新たなレコードセットを生成するクエリーです。
- フィールドの選択・SELECT～FROM -
レコードの中から特定のフィールドだけを選択して提示することは、データベース操作の基本であり、SQLで最も頻繁に用いる操作です。
フィールドの選択にはSELECTステートメントを用います。SELECTステートメントは、必ずFROM句を伴い、FROM句で操作対象のテーブルまたはクエリーを指定します。
書式
SELECT <フィールドリスト>
FROM <テーブルリスト>
パラメータ
フィールドリスト:抽出したいフィールド名を「,」で区切って列挙します。レコードセットには、列挙した順にフィールドの値が並べられます。*を指定すると、すべてのフィールドがテーブルのフィールド構造順に並んで選択されます。
テーブルリスト:操作対象のテーブルまたはクエリーを「,」で区切って列挙します。順序は関係ありません。
例)
テーブル"T_Cstm"から"Name"フィールドと"PhoneNum"フィールドだけを抜き出して提示します。
| SELECT Name, PhoneNum FROM T_Cstm; |
SELECTは「選べ」、FROMは「～から」という意味ですから、上記のSQL文は「"T_Cstm"から"Name"と"PhoneNum"を選び出せ」と読み取れます。
|
|
- レコードの整列・ORDER BY -
整列も、抽出に次いで重要な操作です。
テーブル内のレコードを並べ替えるには、ORDER BY句を用います。
書式
SELECT...FROM...
ORDER BY <フィールド1> [ASC|DESC],<フィールド2> [ASC|DESC]..
パラメータ
フィールド1、フィールド2:整列の基準となるフィールド(キーフィールド)名を列挙します。
ASCまたはDESC:指定したフィールドの並べ方を指定します。ASCは昇順、DESCは降順です。省略するとASCを指定したとみなされます。
例)
テーブル"T_Result"のレコードを"Score"フィールドの値の降順に並べ替え、さらに"Score"フィールドの値が同じ場合には"MemId"の昇順に並べ替えます。
| SELECT * FROM T_Result ORDER BY Score
DESC, MemId ASC; |
このように、フィールド名とASCまたはDESCの組を複数列挙して指定でき、整列は列挙した順に実行されます。
ORDER BY句は、常に文の一番最後に指定します。ORDER BY句を省略すると、レコードはテーブルに記録されている順に並べられます。
- レコードの抽出・WHERE句 -
データベース操作の基本は
テーブルから特定の条件を満たすレコードを抽出する
処理です。これによってレコードセットにレコードが抜き出され、それらに対して表示や加工を行います。
特定の条件を満たすレコードだけを抽出するには、WHERE句を用います。
WHERE句はFROM句の直後に記述しなければなりません。
書式
SELECT...FROM...
WHERE <条件式>
パラメータ
条件式:キーフィールドと比較演算子を用いて、抽出条件を記述します。条件式の記述方法は、VBの規則と同じです。
例)
テーブル"T_Cstm"から"FamilyName"フィールドの値が"Okazaki"というレコードを抽出します。
| SELECT * FROM T_Cstm WHERE FamilyName='Okazaki'; |
以下のように、Like演算子を用いた曖昧検索もできます。Likeは*や?などのワイルドカード文字を使い、文字列が一定の範囲に合致するかどうかを調べる演算子です。
| SELECT * FROM T_Cstm WHERE PersonalName
Like '*jima Yayoi'; |
この場合なら、"Tajima Yayoi"、"Okajima Yayoi"など該当する何件かのレコードが抽出されます。
比較演算子と関係演算子を用い、フィールドの値が一定の範囲にあるレコードだけを抽出することも可能です。
以下のSQL文は、テーブル"T_Item"から"Value"フィールドの値が5000以上20000未満のレコードを抽出します。
| SELECT * FROM T_Item WHERE Value >= 5000
AND Value < 20000; |
便宜上Accessの「アクションクエリー」という表現を用いますが、要するに、データベースの構造に変更を加えるクエリーです。
- テーブルの作成・INTO句 -
既存のテーブルから指定したレコードを抜き出し、それらを集めた新しいテーブルを作るにはINTO句を使います。
INTO句はSELECTコマンドとFROM句の間に記述します。
書式
SELECT... INTO <テーブル名> FROM...
パラメータ
テーブル名:新しく作成するテーブル名を指定します。
例)
テーブル"T_Cstm"から"Gender"(性別)フィールドが"female"(女性)となっているレコードだけを抜き出し、新しく"T_CstmFml"テーブルを作ります。
| SELECT * INTO T_CstmFml FROM T_Cstm WHERE
Gender='female'; |
テーブルの作成では元になるテーブルとフィールドの指定が必要なため、INTOはSELECTステートメントに付随する「句」となります。以下に紹介する削除や追加などの処理はSELECTステートメントから独立した命令なので、それぞれがステートメントとなっています。
- レコードの削除・DELETEステートメント -
指定したレコードだけをテーブルから削除するには、DELETEステートメントを用います。DELETEステートメントは通常WHERE句を伴い、指定した条件に合致するレコードだけを削除します。
書式
DELETE <フィールド> FROM <テーブル> WHERE <条件式>
パラメータ
フィールド:通常は*で全フィールドを指定します。
テーブル:削除対象のレコードを保有するテーブル名です。
条件式:削除対象のレコードを抽出するための条件を指定します。
例)
テーブル"T_Cstm"から"Gender"(性別)フィールドが"female"(女性)のレコードだけを削除します。
| DELETE * FROM T_Cstm WHERE Gender='female'; |
- レコードの追加・INSERTステートメント -
既存テーブルにレコードを追加するには、INSERT INTOステートメントを用います。これは、INSERTステートメントとINTO句を組み合わせたものと解釈できます。
INSERTステートメントはSELECTステートメントと組み合わせて用い、SELECTステートメントによって抽出されたレコードを別のテーブルに追加します。
書式
INSERT INTO <追加先テーブル>(<フィールドリスト>)
SELECT <フィールドリスト> FROM <追加元テーブル> WHERE...
パラメータ
追加先テーブル:レコードを追加するテーブル名を指定します。フィールドリストを指定すれば、抽出されたレコードのフィールドが、フィールドリストで指定されたフィールドに格納されます。
追加元テーブル:追加したいレコードを保存するテーブル名を指定します。
SELECTステートメントによって「追加元テーブル」から<フィールドリスト>で指定したフィールド群が抽出され、それがINSERT
INTOステートメントで指定した「追加先テーブル」に追加される――という形です。
例)
テーブル"T_Cstm"の"Pref"フィールドが"Osaka"となっているレコードを、テーブル"T_CstmOsaka"に追加します。
INSERT INTO T_CstmOsaka(Pref)
SELECT * FROM T_Cstm WHERE Pref='Osaka'; |
- フィールドの更新・UPDATEステートメント -
特定のフィールドの値を一斉に更新するには、UPDATEステートメントを用います。
UPDATEステートメントはSET句を伴い、必ず更新すべき値を指定しなければなりません。
書式
UPDATE <テーブル>
SET <フィールド1 = 値1> ,<フィールド2 = 値2>... WHERE..
パラメータ
テーブル:値を更新したいテーブルを指定します。
フィールド1 = 値1:フィールドと、そこに設定したい値を指定します。複数の組み合わせを列挙できます。
テーブル内の特定の条件を満たすレコードのみに対して更新を行うのが普通なので、通常はWHERE句を用いてレコードを抽出し、そのレコード群に対して処理を行ないます。
例)
テーブル"T_Item"の"ItemId"フィールドが600で始まるレコードの"Stock"(在庫)フィールドを一斉に"No"に更新します。
UPDATE T_Item
SET Stock = 'No' WHERE ItemId Like '600*'; |
DAOとADOではSQL文の扱いが異なります。
- DAOの場合 -
選択クエリー(ビュー)では、DatabaseオブジェクトのOpenRecordsetメソッドをSQL文を引数として実行すれば、その結果がレコードセットとして取得できます。
例)
DataBaseオブジェクトdbMainDb内のテーブルT_ItemからValueフィールドの値が1000以下のレコードをRecordsetオブジェクトrsTempに取り出します。
| Set rsTemp = dbMainDb.OpenRecordset _
"SELECT * FROM T_Item WHERE_Value<=1000;" |
アクションクエリーでは、SQL文を引数としてDatabaseオブジェクトのExecuteメソッドを実行します。
例)
DataBaseオブジェクトdbMainDb内のテーブルT_ItemからValueフィールドの値が1000以下のレコードを削除します。
| dbMainDb.Execute "DELETE * FROM T_Item
WHERE Value<=1000;" |
- ADOの場合 -
ADOでは、一般にCommandオブジェクトのCommandTextプロパティにSQL文をセットし、Executeメソッドを実行します。その際、CommandTypeプロパティにCommandTextプロパティの評価方法を指定できます。
| ・ |
adCmdText:CommandTextをテーブル名やSQL文を示す文字列として評価します |
| ・ |
adCmdStoredProc:CommandTextをストアドプロシージャとして評価します |
例)
接続の確立されたConnectionオブジェクト内のCommandオブジェクトを介して、テーブルT_ItemからValueフィールドの値が1000以下のレコードを取り出します。
cmd.CommandText = _
"SELECT * FROM T_Item WHERE Value<=1000;"
reItems = cmd.Execute() |
また、RecordsetオブジェクトのOpenメソッドを実行することも可能です。
例)
RecordsetオブジェクトrsItems内のテーブルT_ItemからValueフィールドの値が1000以下のレコードを取り出します。
| rsItems.Open "SELECT * FROM T_Item WHERE
Value<=1000;" |
- AccessのSQLビューを利用する -
コード内にSQL文を記述する場合、複雑なテーブル操作ではフィールド名やステートメント、句の綴りを間違える可能性も高くなってきます。そのような心配があるなら、Accessでクエリーを作ってそのSQLビューを利用すると便利です。
Accessでクエリーのデザイン画面から「表示(V)」→「SQLビュー(Q)」を選択すると、画面1のようなSQL文が表示されます。これをVBのコードウィンドウにコピーすれば、そのまま利用できます。
画面1:SQLビューでクエリーのSQLソースが得られる
(この画面は、イメージを把握していただくために加工したものです。実際には、クエリーのデザイン画面とSQLビューは同時に表示できません)
SQLは記述式でVBとはまた違った構文となっているため、一見難しそうに思えるかもしれません。しかし実際には覚えることが少なく、VBのようなプログラミング言語に比べて遥かに習得が容易です。慣れてしまえば、AccessのGUIでクエリーをデザインするより、SQLで記述した方が簡単に思えてくるでしょう。
簡単な練習用のデータベースを作り、SQLを試してみましょう。 |
