|
|
楼主 |
发表于 2007-5-28 10:24:34
|
显示全部楼层
|
3本柱で完全マスターする データベースの基本
「データベース」と聞いてどのように思うでしょうか。「何か難しそうで自分には関係ないもの」と感じられる人もいるかもしれません。確かに，企業システムで使っている顧客管理データベースや売上管理データベースは大規模で複雑なものが多く，職業プログラマ以外の方にはとても手におえそうにありません。
でも，データベースが活躍するのは，企業システムだけではありません。例えば，同窓会名簿など個人で使うちょっとしたアプリケーションも，データベースを使うことでずいぶん楽に作れるようになるのです。
あるいは，「データベースだったら，年賀状作成ソフトについている住所管理データベースをずいぶん前から使っているよ」という方もいるかもしれません。そうした方はきっとデータベースの便利さを実感していますから，改めてご利益を説明する必要はないでしょう。この特集を読んで，データベースの中身や仕組みについての理解を深めてください。データベースを使った新しいアプリケーションのアイデアが湧くかもしれません。
ある決まりごとに従って格納したデータの集まり 最初に，データベースとは何か，ということについて説明しておきましょう。ある人は，「読んだ本の感想をテキスト・ファイルにどんどん書き込んで，“読書データベース”を作っているんだ」と言うかもしれません。しかしこうしたファイルはここでは，データベースと呼びません。
データベースは，ある規則にしたがってまとめられたデータの集まりを指します。例えば，現在の主流であり，この特集で取り上げるリレーショナル・データベース（RDB：RelationalDataBase）は，すべてのデータをテーブルの形式でまとめたものです。テーブルについては，後で詳しく説明しますが，とりあえずここでは，2次元の表のようなものと考えておいてください。
データベースは入れ物に過ぎません。データベースにデータを格納したり，格納してあるデータを検索するなどの管理を行うには専用のソフトウエアを使います。こうしたソフトウエアをデータベース管理システムと呼びます。
RDBの例で説明しましょう。RDBには，複数のテーブルが含まれていて，テーブルのマス目にはそれぞれデータが格納されています。利用者や開発者は，リレーショナル・データベース管理システム（RDBMS：Relational DataBase ManagementSystem）を介して，RDBにアクセスし，データベース内のデータを検索したり，テーブルを作成したりします。
データベース技術は奥が深いですから，すべてを理解しようとすると大変です。しかし，何ごとにも，ここさえ押さえておけば，とりあえずは大丈夫，という基本があります。RDBの場合それが，先ほど説明した「テーブル」，そして「正規化」「SQL」です。
RDBでは，必ずテーブル形式でデータを格納しなくてはなりません。テーブルについて理解することは，RDBを理解するうえで不可欠なのです。さらに，データを素早く検索したり，格納したデータを効率よく保守するには，テーブルをうまく作る必要があります。その際に指針になる決まりごとが，正規化です。
SQLは，RDBにデータを格納したり，検索したりする際に使う命令です。RDBでは基本的にすべての操作は，RDBMSにSQLを渡すことにより行います。SQLについて学習すれば，RDBのデータを自由自在に扱えるようになるのです。ここでは，これら3本柱について順番に，基礎知識と，それぞれの関連情報について説明していきます。
テーブルはすべての基礎 リレーショナル・データベース（RDB）では，データはテーブルと呼ばれる2次元の表に格納されます。テーブルは「行（row）」と「列（column）」で構成し，それぞれの列には名前（「列名」）が付いています。
図1はある会社の社員の情報をテーブルにまとめたものです。それぞれの列には，どのような種類のデータを格納するのかを表す列名がついていて，各行に社員一人ずつの情報を格納します。そして，社員が増えるたびに新しい行として追加していくわけです。
 | 図1●RDBのテーブルの構造
|
テーブルでは，行と列を指定することにより，データを特定できます。例えば飯田高志さんの部署を知りたい場合，図1のテーブルの氏名の列で飯田高志さんを探し，飯田さんの行の部署名の列を見ると所属部署がわかるわけです。
ここまで読んで，Excelなどのスプレッドシート・ソフトを使ったことがある人は疑問を抱くかもしれません。Excelでも，図1と同じような2次元の表を作れます。RDBのテーブルとExcelで作る2次元の表では何が違うのでしょうか。
RDBのテーブルでは，データの検索を効率よく実行したり，データベース内容の不整合が発生しにくいように，スプレッドシート・ソフトとは異なる仕組みがいろいろ取り入れられています。ポイントは次の4点です。
ポイント1：各列は一意の名前を持つ RDBのテーブルでは，各列につける名前である列名が，ほかの列と重複してはならないという決まりがあります。実際，RDBソフトを使ってテーブルを作成するときに，列名が重複するとエラーが発生します。これを，「各列には一意な列名を付ける」などと言います。
RDBでは列に対して条件を指定して，該当するデータを検索することがよくあります。例えば図1のテーブルで，性別が男である社員を検索するような場合です。RDBでは列名で列を区別しますから，列名がダブっているとどの列を検索すればよいのかわからなくなってしまいます。
これに対してExcelの表では，同じ名前の列があってもエラーになりません。例えば，住所という名前を二つの列に付けて，一方には県名を格納し，もう一方には市町村名と番地を格納する，といったこともできます。
ポイント2：主キーが存在する RDBのテーブルは，該当する行をただ一つに決めることができる列を持っていなければなりません。例えば，図1のテーブルであれば，社員番号の列の値を決めると，対応する社員がただ一人に決まります（氏名では同姓同名がいますね）。該当する行をただ一つに決めることができる列を「主キー」，あるいは「識別キー」と呼びます。
主キーは行をただ一つに決めるためのものですから，同一の値を持つ行が複数あってはいけません。加えて，すべての行は主キーに値を持っていなければなりません。すなわち，主キー（先の例で言うと社員番号）の値が入っていない行があってはいけません。ちなみに，値が入っていない状態のことを「NULL（ヌル）」と呼びます。
複数の列を組み合わせて主キーとして扱うことも可能です。図1で言えば，もしこの企業に「一つの部署には同姓同名の社員を配属しない」という規則があって，それを将来にわたって厳守するとすれば，氏名と部署名を組み合わせて主キーとして扱うこともできます。例えば，「第1営業部の吉田正美さん」と言えば，対応する社員がただ一人に決まるわけです。組み合わせて主キーとして扱える列のことを，「連結キー」あるいは「複合キー」と呼びます。
ポイント3：テーブル間を関連付けられる 図1のテーブルでは，部署名が変更になった際に，該当するデータをすべて修正しなくてはなりません。例えば，第1営業部が海外営業部に名称を変更した場合，吉田正美さん（男）と福山友子さんの部署名をそれぞれ，海外営業部に直す必要があります。
修正するのが数カ所程度ならいいですが，数百人規模の部署だったら大変な作業です。多くの行を持つテーブルにおいて，修正する個所は少なければ少ないほど良いと言えます。手間が省けるだけでなく，更新漏れによる不整合を防ぐことにもなるからです。
そこで，データはできるだけ重複して持たせない，あるいは同じデータはできるだけ1カ所で管理するようにしたい，という要求が生まれます。RDBでは，複数のテーブルを作成し，それらの間に「関連（リレーションシップ）」を持たせることによって，こうした要求に応えられるようになっています。
図2をご覧ください。各部署に対して部署コードを割り当てて，部署名を主キーとするテーブル（部署テーブル）を別に作成しました。さらに，図1のテーブルにおける部署名の列を部署コードに置き換えます（社員テーブル）。
 | 図2●図1のテーブルを分割したところ。二つのテーブルには，部署コードを通じてリレーションシップ（関連）が付けられている
|
こうしておけば，社員番号を指定したとき，両テーブルに共通して存在する列である部署コードを介して，部署テーブルから部署名を取得できます。部署名に変更があったときには，部署テーブルの対応する部署名を修正するだけで済みます。
図2の部署テーブルにおいては，部署コードが主キーになります。一方，社員テーブルにおける部署コードは，ほかのテーブルの主キーになっていることから「外部キー」と呼びます。
ポイント4：制約を設定できる RDBではテーブルや列，また列と列の関連に「制約」を持たせることができます。制約とは，RDBの内容を整合性が取れた状態に保つことを目的として，テーブルに格納するデータに課す条件のことです。制約を設定することで，意図しないデータや間違ったデータの入力を防げるので，データベースを管理する際の手間を軽減できます。
例えば，図2の主キーである社員番号の列に対しては，ポイント2で説明したように，「値が重複してはならない」「NULLであってはならない」といった制約があります。これらをそれぞれ，「一意制約」「NOTNULL制約」といいます。主キーに対するこうした制約はまとめて，「主キー制約」と呼ばれます。
ほかに，氏名の列に対しては，値がNULLでは困るのでNOT NULL制約をつけておきます。また性別は男か女かのいずれかですから，それ以外の値の格納を許さない「CHECK制約」を付けるのが良いでしょう。
外部キーである部署コードに対しては，「参照整合性制約」と呼ぶ制約を付けます。参照整合性制約とは，関連付けられているテーブルで主キーの値として登録されていない値は，その列に格納できないということです。
図2の社員テーブルに新しいデータを追加する場合を考えてみましょう。もし何かのミスで存在しない部署コードを入力しようとした場合，参照整合性制約が設定されていなければそのまま格納されてしまいます。追加する社員は，部署テーブルに存在するいずれかの部署に所属するはずですから，これは明らかにおかしいことになります。
外部キーである部署コードに参照整合性制約を設定しておけば，部署テーブルに存在しない部署コードを社員テーブルに追加できません。同時に，社員テーブルから関連付けされている部署テーブルの行を削除することもできなくなります。不整合を引き起こすような更新をあらかじめ防ぐことができるわけです。
リレーショナル・データベース（RDB）ではテーブル形式で表現したデータをデータベースに格納しました。データを表現する形式はテーブルに限りません。ここでは，XML（Extensible MarkupLanguage）形式のデータを格納するXMLデータベースについて紹介します。XMLデータベース自体は以前からあるのですが，最近になってXMLを利用するアプリケーションが増えてきたため，改めて注目されています。
リストAは書籍のタイトル（title），著者（author），価格（price），発行年月日（date），記事（article）を記述したXML文書の例です。タグの入れ子構造によって，データの階層構造が表現されていることがわかります。
<?xml version="1.0"?>
<books>
<book>
<title>日経ソフトウエア11月号</title>
<author>日経BP社</author>
<price>980</price>
<date>2004/09/24</date>
<articles>
<article>データベース</article>
<article>Java</article>
</articles>
</book>
<book>
<title>日経コンピュータ9月6日号</title>
<author>日経BP社</author>
<price>980</price>
<date>2004/09/06</date>
<articles>
<article>社員監視時代</article>
<article>ザ・プロジェクト</article>
</articles>
</book>
</books> | リストA●XML文書の例
|
XMLデータをツリー構造で格納 XML文書をデータベースに格納するには2種類の方法があります。一つは，XML文書をそのまま扱えるネイティブXMLデータベース（以下，単にXMLデータベースと呼びます）を使うこと。もう一つは，XML形式のデータをテーブル形式に変換して格納するXML対応RDBを使うことです。
図A(a)はXMLデータベースのデータ格納構造のイメージです。リストAのXML文書をツリー構造で格納します。
 | | 図A●リストAのXMLデータの格納構造。(a)XMLデータベース，(b)XML文書の要素や属性をレコードに対応付けて格納するRDB。ほかに，XML文書をテキストでRDBにそのまま格納したり，ツリーに展開してRDBに格納するタイプがある [画像のクリックで拡大表示] |
多くのXMLデータベースが備える特徴の一つは，スキーマ（データベース構造の仕様）の定義を必要としないことです。XML文書には，構造とデータの両方を記述しますから，スキーマなしでデータの種類を判別できるからです。データの構造を変更したり，項目を追加するときにいちいちスキーマを修正する必要がありません。
RDBと比べたときの注意点として，XMLデータベースは更新処理を不得手としていることが挙げられます。更新の単位，ロックの範囲，トランザクション機能は製品によって様々であるため，導入の際には十分な検討が必要です。
RDBにXML形式のデータを格納する XML対応RDBの格納方法には大きく三つあります。一つは，XML文書をテキストとしてそのまま格納するタイプです。実装は簡単ですが，XMLデータベースが持つ柔軟性は持っていません。検索する場合も，XMLデータを取り出して解析する処理が必要になるため，パフォーマンスが悪くなりがちです。内部にインデックスを持たせることによってパフォーマンスを改善しているものあります。
ニつ目はXML文書の要素や属性を，RDBのレコードに対応付ける（マッピングする）タイプです（図A(b)）。テーブル形式で格納しますから，格納後はSQL文で操作できます。このタイプの問題点は，XML文書の構造やRDBの構造によってはマッピングが困難な場合があること，マッピング処理の負荷がかかることです。
三つ目は（図A(a)）と同じように，XML文書をDOM（Document ObjectModel）ツリーと呼ばれるオブジェクトに展開してRDBに格納するタイプです。データ構造を解析した状態で保存しているため，高パフォーマンスが期待できます。XMLデータベースとRDBのメリットを併せ持つタイプと言えます。
正規化なしにRDBはあり得ない RDBに格納するデータの項目が多くなってくると，一つのテーブルで管理するのは大変です。例えば，図1のテーブルでは，部署名に変更があったときに複数個所を修正しなければなりませんでした。そこで，図2のようにテーブルを二つに分割することで，修正すべき個所をまとめましたね。
この例からわかるようにRDBでは，テーブルを分割することによって管理を容易にできます。しかし，やみくもに分割すればよいわけではありません。下手に分割するとかえって扱いにくくなってしまいます。
ここで紹介する「正規化（Normalization）」と呼ぶ手続きに従ってテーブルを分割していくと，管理しやすいRDBを自然に作ることができます。正規化は実用的なRDBを作成するための必須知識ですから，しっかりとマスターしておきましょう。正規化は，第一正規形から第五正規形までがあります。また，正規化される前の表を非正規形と呼びます。通常は，第三正規形まで正規化されることが多く，第四正規形，第五正規形までの正規化はあまり行われません。
ステップ1：繰り返しをなくして第一正規形に 図3はある問屋の受注伝票です。ここに記述してあるデータをRDBに格納することを考えましょう。
 | 図3●今からRDB化する受注伝票
|
図3に登場するデータ項目を整理して表形式で表したものが図4の非正規形の表です。図4は図3の内容をほぼそのまま表形式にしただけです。一つの伝票番号に複数の行が含まれていますから，テーブルとは呼べませんね。
 | | 図4●図3の受注伝票に現れるデータを表形式でまとめたもの [画像のクリックで拡大表示] |
そこで，一つの伝票番号に現れる複数の行（「繰り返し部分」と呼びます）をなくして，RDBのテーブルにすることを考えます。一番簡単なのは，繰り返し部分のすべての行に，伝票番号から得意先住所までの列を追加することです（図5）。しかし，これでは見るからにムダが多いですね。伝票番号が決まれば受注年月日～得意先住所はただ一つに決まります。そこで，伝票番号ごとに受注年月日～得意先住所を格納するテーブル（受注テーブル）を作成します。そして，繰り返し部分の各行に対しては，伝票番号だけを付加したテーブル（受注明細テーブル）を作成します。こうして作成した二つのテーブルを，第一正規形と呼びます（図6）。
 | | 図5●図4を一つの行に複数の行が含まれないように修正した表。見るからにムダが多い [画像のクリックで拡大表示] |
 | 図6●第一正規形のテーブル
|
ステップ2：主キーに注目して第二正規形に 第一正規形になったテーブルを第二正規形にするには，主キー以外のすべての列の値が主キーによってのみ決まるように，テーブルを分割します。主キーの値によってほかの列の値が一意に決まることを，「主キー以外の列が主キーに従属する」と言います＊1。
第一正規形である図6の受注テーブルは，主キーである伝票番号が決まれば，受注年月日，得意先コード，得意先名，得意先住所が一意に決まります。したがってこのテーブルはすでに第二正規形を満たしていることになります。
図6の受注明細テーブルはどうでしょう。伝票番号と商品コードの組み合わせを主キーとすれば，残りの列が従属しているように思えます。しかしテーブルの内容についてよく考えると，商品名と単価は，商品コードだけに従属していることがわかります。そこで，商品コードを主キーにするテーブル（商品テーブル）を新たに作成して，図6の受注明細テーブルの商品名と単価の列を商品テーブルに切り出します。
こうして作成した第二正規形のテーブルが図7です。小計は単価×数量によって求められるため，テーブルで保持する必要がないと考えて削除しました。
 | 図7●第二正規形のテーブル
|
ステップ3：主キー以外の列の関係を調べて第三正規形に 第三正規形ではさらに，主キー以外の列の間に従属関係がないようにテーブルを分割します。図7の第二正規形の受注テーブルを見てください。伝票番号が決まると確かに得意先名と得意先住所が決まります。しかしよく考えてみると，得意先名と得意先住所は，得意先コードを決めただけで決まってしまいます。すなわち得意先名と得意先住所は，得意先コードに従属しているわけです＊2。
そこで，得意先コードを主キーとするテーブル（得意先テーブル）を作成し，そこに元々の受注テーブルの得意先名と得意先住所の列を切り出します。
こうして作成した第三正規形が図8です。最初は一つだったテーブルが，四つに分割されてしまいましたね。正しく分割できたかどうかは，第三正規形のテーブルから，元のデータ，つまり受注伝票を再現できるかどうかで確かめることができます。試してみましょう。
 | | 図8●第三正規形のテーブル [画像のクリックで拡大表示] |
受注テーブルから受注番号と受注年月日と得意先コードが得られます。得意先コードからはさらに，得意先テーブルを用いて得意先名と住所がわかります。受注の内容については，伝票番号から受注明細テーブルを用いて，商品コードと数量がわかります。そして商品コードからは，商品テーブルに基づいて，商品名と単価がわかる，といった具合です。これで受注伝票を再現できました。もし再現できなければ，正規化の過程で何かミスをしていることになります。
正規化のメリット 正規化のメリットの一つは，データに変更があったときにどこを直せばよいのかが一目瞭然になるということです。例えばある得意先の名前や住所が変わったとしましょう。非正規化表では受注テーブルのデータのすべての得意先名や住所を変える必要があります。しかし第三正規形では得意先は得意先テーブルだけで管理していますから，1行変更するだけで済みます。こうしたテーブルを「マスター・テーブル」と呼びます。
注文に後から別の商品を追加したい場合には，受注明細テーブルに同じ伝票番号の行を追加するだけで済みます。ほかに，得意先ごとの割引率を設定したり，注文の日付によって違う単価を適用するなどの複雑な機能をアプリケーションに実装しやすくなります。
受注明細テーブルの商品コードに参照整合性制約を設定することで，商品テーブルにある商品以外は注文できないようにすることも可能です。すなわち，正規化することによって，データベースの整合性を保ちやすくなると同時に，不正なデータや間違ったデータの入力を防止できるのです。
もっとも，検索時のパフォーマンスが問題になるときに，あえて正規化を崩すこともあります＊3。非正規化の方法としては，関連付けられた複数のテーブルをまとめる，繰り返し部分を保持させる，合計金額など計算で求められる値をあらかじめ保持しておく，などがあります。ただしこうした非正規化は第三正規形まで正規化した後に行うものであり，正規化をしなくてもよい，ということでは決してありません。
最近では，ハードウエアの性能向上により，非正規化を行う機会はかなり少なくなっています。きちんと正規化を実施したうえで，性能検証やデータベースのチューニングをして，それでもどうしてもパフォーマンスが不足する場合にだけ非正規化を検討するようにしてください。繰り返しになりますが，RDBのテーブルと言えば正規化と肝に銘じておいてください。まずは正しく正規化することが最優先です。
テーブルに正規化を施すことのメリットの一つとして，SQL文の記述が容易になるということがあります。しかし，いくら正しく正規化されていたとしても，オブジェクト指向で開発したアプリケーションからリレーショナル・データ・モデルのRDBを扱う際には必然的に「インピーダンス・ミスマッチ」（後述）が発生してしまいます。これはオブジェクト指向とリレーショナル・データ・モデルの設計思想の違いに起因するもので，結果としてわずらわしい処理が付きまとうことになります。
この問題を解決するアプローチとして期待されているのが「O/Rマッピング」です。O/Rマッピングは，アプリケーション側のオブジェクトと，RDB側のテーブルを対応付ける（マッピングする）ことです。
オブジェクトとテーブルを対応付ける インピーダンス・ミスマッチについて簡単に説明しておきましょう。オブジェクト指向アプリケーションでは，SQL文を使ってデータベースからデータを取得した際に，そのままオブジェクトに格納できないため，オブジェクトとして組み立て直す必要があります。更新の際には，オブジェクトからデータを取り出して，SQL文を作成するコードを書かねばなりません。
これは，プログラムのコードにSQLが入り込んでしまうことにもなります。そのため，データベースの構造に変更があった際に，検索結果を取得している部分や，オブジェクトから値を取り出してSQL文を組み立てる部分にまで影響が及んでしまい，プログラムの保守性を悪くする要因になります。
O/Rマッピングをサポートするフレームワークを使うと，検索結果をオブジェクトに組み立て直す処理や，オブジェクトからデータを取り出してSQL文を作成する処理を，開発者が直接書く必要がなくなります。その結果，アプリケーションとデータベースの結びつきが弱まり，開発生産性や保守性が向上することを期待できます。
O/Rマッピングのフレームワークを使用したアプリケーションからRDBへのアクセスの手順は図Bのようになります。開発者が用意するのは，オブジェクト，データ・アクセス・オブジェクト，マッピング・ファイル，テーブル，の四つです。これらのうち，O/Rマッピングのフレームワークに固有なマッピング・ファイルと，アプリケーション側でデータの受け渡しに利用するデータ・アクセス・オブジェクトについて説明しましょう。
 | | 図B●O/Rマッピング・フレームワークを使ってアプリケーションからRDBにアクセスする仕組み [画像のクリックで拡大表示] |
マッピング・ファイルには，オブジェクトのデータをマッピングする先，データ型をはじめとする文字数，操作の制限などを記述します。データ・アクセス・オブジェクトには，アプリケーションから受け取ったオブジェクトを使ってO/Rマッピングのフレームワークに処理を依頼したり，O/Rマッピング・フレームワークから受け取った処理結果のオブジェクトをアプリケーションに渡す処理を記述します。O/Rマッピング・フレームワークの多くは，データベースの構造定義に基づいて，マッピング・ファイルの作成を支援する機能を備えています。
マッピング・ファイルとデータ・アクセス・オブジェクトが用意できたら，アプリケーションのオブジェクトからデータベースのテーブルにアクセスするのは簡単です。データ・アクセス・オブジェクトの検索や更新を行うメソッドの引数に，値やオブジェクトを入れてアクセスするだけです。検索であれば，このメソッドの戻り値として検索結果のオブジェクトが返されます。
現在入手できるO/Rマッピング・フレームワークにはそれぞれ特徴があり，使い勝手も千差万別です。実際の開発で使用する前に，一度試してみることをお勧めします。また，O/Rマッピング・フレームワークも内部ではSQLを使っています。デバッグやチューニング時にはSQLをメンテナンスする必要がありますから，SQLを理解しなくてもよいというわけではありません。
|
|
SQLを制するものはRDBを制する 現在では，「データベースといえばRDB」ですが，ここにまで普及したのは「SQL」があったから，と言っても良いでしょう。SQLは元々「Structured Query Language」の略で，日本語では「構造化問い合わせ言語」と呼ばれます＊4。RDB内のデータの検索や更新のほか，テーブルの作成や削除，データベースを管理・操作するのに利用します。
RDBが出始めた1970年代前半は，階層型データベースやネットワーク型データベースが全盛でした。これらのデータベースではデータベースとアプリケーションのプログラムが密接に関係しているため，システムの保守や変更が難しいという問題がありました。RDBはSQLを介してデータベースにアクセスするという方法を採ることで，こうした問題点の解決を図ったのです。当初は，SQLの処理の負荷が大きいためにパフォーマンスを出しにくいという問題がありました＊5。しかし，1990年代半ばくらいのマシン性能の飛躍的な向上に伴い，RDBが普及するようになったのです。
SQLはANSIの規格にのっとっており，様々なRDBMSで互換性が保たれているため，ベンダーに依存せず利用できます＊6。しかし，多くのRDBソフトはSQLの規格から外れた独自の命令も用意していますから注意が必要です。システムを開発するときには，データベースを変更する際のリスクを考えて，できるだけベンダー非依存のSQLを使用しましょう。
基本になる命令は四つだけ SQLで利用できる命令は大きく，(1)データ操作言語（DML：Data ManipulationLanguage），(2)データ定義言語（DDL：Data Definition Language），(3)データ制御言語（DCL：DataControl Language）に分類できます（表1）。
 | 表1●SQLの命令の種類
|
DMLはテーブルに対して，データの検索，追加，変更，削除などの操作を行います。DDLはテーブルやインデックス＊7を作成・変更・削除したり，RDBのユーザーを作成・変更・削除するのに使います。DCLは，データベースを制御するための命令です。ユーザーに権限を与えたり，権限を剥奪したり，アクセスを制御できます。処理の確定（コミット）や取消（ロールバック）など，トランザクション処理＊8を制御する命令も，DCLに含まれます。
ここでは最も頻繁に利用されるDMLについて説明しましょう。DMLに分類される命令のうち，ぜひとも覚える必要があるのは四つ。行を検索するSELECT，行を追加するINSERT，行を更新するUPDATE，行を削除するDELETEです。順に説明しましょう。
SELECTの構文構造は次の通りです。SELECT文の最後には必ずセミコロン（;）が必要です。これはどんなSQL文にも言えることであり，これがないとエラーになりますから注意してください。
SELECT 列名 FROM テーブル名 WHERE 検索条件;
SELECT句には検索したい列名を，FROM句には検索したいテーブル名を，WHERE句には検索条件を記述します。すなわち，「テーブル名」のテーブルから「検索条件」に合致した行の「列名」を表示する，といった具合です。図2のようなテーブルがあった場合，テーブル名として社員テーブルを，検索条件として男を，列名として氏名を指定して検索すると，男性社員の氏名がずらりと表示されます。WHERE句の検索条件の書き方については，後で詳しく説明します。
列名の代わりにアスタリスク（＊）を指定することもできます。この場合，検索条件に合致した行のすべての列を検索結果として返します。WHERE句以降を省略すると，検索条件なしと解釈され，テーブルに含まれるすべての行について，列名で指定した列の値を表示します。
これら二つを組み合わせるとどうでしょう。すなわち，列名にアスタリスク（＊）を指定してWHERE句以降を省略すると，テーブルの内容をすべて表示するSQL文になります。
INSERT文はテーブルに行を一つ追加するための命令で，構文構造は次の通りです。
INSERT INTO テーブル名(列1,列2,列3) VALUES(値1,値2,値3);
INSERT INTO句でデータを追加するテーブルと列を指定し，VALUES句で追加する値を記述します＊9。列名と値はペアになっており，追加した行の「列1に値1」「列2に値2」「列3に値3」がそれぞれ入ります。列と値の数が違ったり，列のデータ型＊10と違うデータを入れようとするとエラーになります。値を指定しなかった列にはテーブルに設定されているデフォルト値が入ります。
テーブル名の後に続く列を省略することもできます。その場合はテーブルのすべての列が追加対象となり，VALUES句に列の数ぶんの値を，定義されている順に記述します。
UPDATEは行のデータを更新するのに利用します。構文は次の通りです。
UPDATE テーブル名 SET 列名 = 値 WHERE 条件;
UPDATE句でテーブル名を指定し，SET句で更新したい列名と値，WHERE句で更新したい行を指定する条件を記述します。SET句にカンマ区切りで複数の列を記述することで，複数の列を更新できます。
SELECT文と同様にWHERE句を省略すると全件が更新対象とみなされます。更新を行う際は同じ条件で検索を行い，更新したい行が検索結果として抽出されているかをあらかじめ確認するとよいでしょう。
行の削除を行うDELETEは次のように利用します。
DELETE FROM テーブル名 WHERE 条件;
FROM句でテーブル名，WHERE句で削除の対象となる行を指定する条件を記述します。SELECT文やUPDATE文と同様に，WHERE句を省略すると条件なしと見なされ，すべての行が削除されてしまいますから注意が必要です。
SQL自由自在 以下ではSQLを使った様々な命令（問い合わせ）について紹介します。同じ結果を返すSQL文は一つとは限りません。できるだけ，わかりやすく，効率のよい方法を選ぶようにしましょう。
WHERE句を駆使すれば様々な検索が可能 まず，SELECT文，UPDATE文，DELETE文で条件を指定するWHERE句の使い方について紹介しましょう。WHERE句を使いこなせば，様々な条件を指定してデータを検索できます。以下ではSELECT文を例としてあげますが，DELETE文やUPDATE文に対しても同じように使えます。
●比較演算
＜，＜=，=，＞=，＞などの比較演算子を使って具体的に条件を指定します。次の例は，商品テーブルから商品コードが0001の商品名を返すSQL文です。
SELECT 商品名 FROM 商品テーブル WHERE 商品コード = '0001';
不等号を使えば，単価が100円以下の商品名を返すといったSQL文も作れます。
●論理演算
比較演算子と，論理積（AND），論理和（OR），否定（NOT）といった論理演算子を組み合わせて使うことで，より凝った検索ができます。例えば，単価が100円以上，かつ，1000円以下のすべての商品名を返すには
SELECT 商品名 FROM 商品テーブル WHERE 単価 ＞= 100 AND 単価 ＜= 1000;
のようにSQL文を記述します。
●あいまい検索
WHERE句でLIKEを使うことで，ワイルドカード＊11を用いた検索が可能になります。例えば，マイクロソフトのSQL Serverや日本オラクルのOracleでは，「%」は任意の文字複数個，「_」は任意の1文字を表します。したがって，次のように書くと商品名の最後に「ナイフ」と付くすべての商品名が返されます。
SELECT 商品名 FROM 商品テーブル WHERE 商品名 LIKE '%ナイフ';
INを使うと複数の値を指定して，いずれかに該当するものを検索できます。例えば単価が100，200，300，400，500のいずれかである商品名を検索するには次のようにSQL文を記述します。
SELECT 商品名 FROM 商品テーブル WHERE 単価 IN(100,200,300,400,500);
BETWEENを使うと，データの範囲を指定できます。次の例は，単価が100円以上，1000円以下の商品名が返されます。
SELECT 商品名 FROM 商品テーブル WHERE 単価 BETWEEN 100 AND 1000;
BETWEEN条件は，比較演算子と論理演算を組み合わせて表すこともできます。
SELECTの検索結果を加工して返す SELECT文では，検索結果の列の値をそのまま返す以外に，加工して返すこともできます。
●重複を取り除く
SELECT文でDISTINCT句を使って列名を指定すると，検索結果から重複を取り除いて返します。先に説明したように
SELECT 顧客名 FROM 受注テーブル;
は，受注テーブル内の顧客名をすべて返しますが，受注テーブルに同一顧客名の行が複数あると，その数だけ同じ顧客名が返されます。そこで SELECT DISTINCT 顧客名 FROM 受注テーブル;
のようにSQL文を記述すれば，顧客名の重複を取り除きますから，それぞれの顧客名は一度しか返しません。 ●検索結果に対して演算を行う
SELECTでの検索結果に対して指定した演算を施した値を返すように指定することもできます。例えば，商品テーブルから，すべての商品名と単価を検索し，単価と並べて消費税込み単価も表示させたいときは，次のように記述します。
SELECT 商品名,単価,単価*1.05 AS 税込金額 FROM 商品テーブル;
ここでは，AS句を使って単価に1.05をかけた値に対して，税込金額という別名を付けています。こうすると，単価に1.05をかけた値の列が税込金額と言う列名で表示されます。
●昇順/降順で並べ替える
検索結果が数値の場合，小さい順（昇順），あるいは大きい順（降順）に並べ替えて返してくれると便利なことが多いですね。ORDER BY句を使えば，検索結果を特定の列について並べ替えることができます。次のSQL文は，社員テーブルのすべての社員の社員番号と氏名を，社員番号が小さい順に並べ替えて返します。
SELECT 社員番号,氏名 FROM 社員テーブル ORDER BY 社員番号;
ORDER BY句には列名に続けて，昇順（ASC），降順（DESC）を指定できます。次のSQL文は社員番号の大きい順に社員番号と氏名を返します。
SELECT 社員番号,氏名 FROM 社員テーブル ORDER BY 社員番号 DESC;
昇順を指定するときにはORDER BY句にASCを指定しますが，何も書かなければ昇順を指定したものとされます。
●合計値や平均値を求める
SQLは，合計や平均を求める関数を用意しています。これらの関数はまとめて，集合関数と呼ばれます。例えば商品テーブルに含まれている商品の単価を合計するには，SUM関数の引数として列名（単価）を指定して次のように書きます。
SELECT SUM(単価) FROM 商品テーブル;
単価の平均を求める場合は，AVG関数を使います。
SELECT AVG(単価) FROM 商品テーブル;
COUNT関数を使えば，検索結果得られた行数を数える（カウントする）ことができます。例えば次のSQL文は，商品テーブルの商品数を返します。
SELECT COUNT(*) FROM 商品テーブル;
集合関数にはほかに，最大値を求めるMAX関数，最小値を求めるMIN関数などがあります。
●列の値でグループ化して扱う
GROUP BY句を使うことにより，特定の列の値についてグループ化して扱うことができます。次の例は，社員テーブルの部署名をグループ化して，部署ごとに社員数をカウントして返します。
SELECT 部署名,COUNT(*) FROM 社員テーブル GROUP BY 部署名;
HAVING句を用いると，グループ化した行に対して，条件を指定して絞り込みを行うことができます。
SELECT 部署名,AVG(年齢) FROM 社員テーブル GROUP BY 部署名 HAVING AVG(年齢) ＜= 35;
この例では部署ごとの平均年齢を求めて，平均年齢が35歳以下の部署の部署名と平均年齢を表示します。
●問い合わせ結果を使って問い合わせる
SELECT文の中に，別のSELECT文を埋め込み，埋め込んだSELECT文の結果を用いて次の検索を行うことができます。これを「副問い合わせ」と呼びます。
SELECT 商品名 FROM 商品テーブル WHERE 単価 ＞ (SELECT AVG(単価) FROM 商品テーブル);
カッコ内のSQL文では，商品テーブルの全商品の単価の平均値を求めています。そしてWHERE句で，単価がその平均値より大きいという条件を指定して，商品テーブルから該当する商品名を取り出します。副問い合わせはいくらでも入れ子にできますが，パフォーマンスの面からはできるだけ少なくしておくのが良いでしょう。
知っておきたいキーワード
SQL/XMLとXQuery |
現在，XMLデータを扱うためのSQL/XMLとXQueryという規格が注目を集めています。SQL/XMLはANSIが2003年に規格化したSQL 2003に含まれるSQLの拡張機能です。一方，XQueryはW3C（World Wide Web Consortium）で標準化作業が行われている規格です。どちらもXMLデータを扱うための規格なのですが，両者を比較すると，SQL/XMLは“SQL寄り”で，XQueryは“XML寄り”と言えるでしょう。
SQL/XMLはSQLの拡張 SQL/XMLを使うと，RDBのデータの検索結果をXML形式に変換して取得できます。通常のSQL文と同様にして使えますから，SQL文を使用した経験がある人にとってなじみやすい仕組みと言えます。
最も基本となるSQL/XML文を紹介しましょう。図Cはテーブルからデータを取り出して，XML形式に変換するSQL/XMLの例です。XMLELEMENTはXML要素を作成するために，XMLATTRIBUTESはXML属性を作成するために使用します。図を見ると，XMLELEMENTを指定したところが要素になっていて，XMLATTRIBUTESを指定したところが属性になっていることがわかるでしょう。
 | | 図C●ITEMSテーブル（左）からデータを取り出してXML形式に変換するSQL/XMLの例 [画像のクリックで拡大表示] |
市販の主要RDBのうち，SQL 2003そのものの採用を明示している製品はOracleなど少数の製品に限られます。しかし，そのサブセットであるSQL/XMLについては，多くの製品が現行版あるいは次版で同等機能を実現することをうたっています。
XQueryはXMLを意識する必要がある XQueryで問い合わせに利用できる句には，for，let，where，order by，returnがあります。それぞれの頭文字をとってFLWOR表現式と呼びます。
それぞれの機能は，SQLのSELECT文の句に対応付けられます。SQLのSELECT句に当たるのがreturn，FROM句に当たるのがforとlet。そして，WHERE句に当たるのがwhere，ORDER BY句にあたるのが，order byです。
XQuery1.0の仕様はXPath2.0を含んでいます。XPathは，XML文書が表すツリー構造の場所を特定するための言語です。XQueryではXPathを使用してXMLの各要素を指定します。
図DはXQueryでの問い合わせの例です。左のXML文書に対して，XQueryを適用して右のXML文書を作成しました。priceタグの値が100以上のbookを抜き出して，title，priceと一緒に返しています。変数（$b）や繰り返し（for）などのFLWOR表現式と，XPath（$b/title，$b/priceなど）を用いてXMLのデータ構造を表現しています。通常のSQL文と同様に，四則演算，比較演算，論理演算を行うこともできますし，複数のXML文書に対してXQueryを実行することもできます。
 | | 図D●条件に合致するデータを取り出すXQueryの例 [画像のクリックで拡大表示] |
XQueryと並行して，XQJ（XQuery forJava）という規格も開発中です。JDBCがSQLを用いるRDBへのJava用APIであるのと同様に，XQJはXQueryを用いるXML文書へのJava用APIです。Java技術の標準化団体Java Community ProcessがJSR-225として策定中です。
[ 本帖最后由 bgx5810 于 2007-5-28 10:30 编辑 ] |
|
发表于 2007-5-28 10:23:58
提升卡
变色卡
