今回のポイント
・データベースへの問い合わせにはSQL文が使われる
・リレーションシップの考え方

 データベースを使う案件では,いろいろな資料の中で「SQL」という単語を目にすることが多くなります。「SQLデータベース」や「SQL文」といった使われ方をします。このSQLとはなんでしょうか。

SQLとはなにか

 ここまでデータベースというものを理解していただくのに,Microsoft Excelで作った表を見てきました。

 データベースの中にデータが納まっている様子を直感的に理解するのに,Excelはとても便利です。例えばExcelにデータを足すというのは,普通に書きたいセルに移動して,数字や文字をタイプするだけです。データを引き出すという行為がありませんし,並べ替えや抽出はメニューからの操作になります。

 しかし,多くのデータベース・ソフトにはこうしたExcelのような操作画面は存在しません。一覧すら出ません。ではどうやって一覧を見たり,データを足したり,必要なものを取り出したりするのでしょうか。その答えが「SQL」です。

 SQLは以前は「Structured Query Language」の略と呼ばれていました。直訳すれば「構造型問い合わせ言語」です。しかし,ISO(国際標準化機構)によって標準化され,現在では何かの略ではなく,単なる「SQL」として定められています。ホームページを記述する言語HTMLと同様,データベースとのやり取りにも標準規格があるというわけですね。

 SQLには決まった記述方法があって,その記述に沿ってSQLを書くことでデータベースから必要な情報を必要な形で取り出したり,データを記録,削除,編集することができます。

 SQLによって操作できるデータベースのことを「SQLデータベース」と呼びます。Web+DB案件では,ほぼ100%近くがSQLデータベースを使用します。SQLデータベースと呼ばれるデータベース・ソフトとしては,以下の五つが主なものでしょう。最初の二つはオープンソース・ソフト,残り三つが商用ソフトです。

・MySQL(マイエスキューエル)
・PostgreSQL(ポストグレスまたはポストグレスキューエル)
・Microsoft SQL Server(マイクロソフト エスキューエルサーバー)
・Oracle(オラクル)
・DB2(デービーツー)

 SQLは標準規格ですが,現実には各データベースに独自拡張や独自規格が多々あって,完全な互換性は確保されていません。それでも基本的な部分では90%くらいが共通になっています。

実際のSQLを体験してみよう

 さて,今回はリレーションシップの説明です。

 リレーションシップについて理解するには,実はSQLを理解するのが近道です。リレーションシップというのは,いくつかに別れているテーブルに存在するデータを,ある“鍵”を使ってひも付けてやり,自分がほしい形で引っ張り出すというものです。

 ある自動車販売会社のデータベースを例に,SQLとリレーションシップについて説明をしていきましょう。四つのテーブルがあるものと想定します。

 まずは社員の名簿テーブルです。テーブル名は「社員名簿」です。

図1●社員名簿テーブルのイメージ
図1●社員名簿テーブルのイメージ

 次に販売している車のテーブルです。テーブル名は「商品リスト」です。同一商品で色違いがあるようです。

図2●商品リスト・テーブルのイメージ
図2●商品リスト・テーブルのイメージ

 三つ目は販売履歴のテーブルです。テーブル名は「販売履歴」です。数値の羅列になっています。カラム名を見ると他のテーブルとの関連性で成り立つテーブルのようです。

図3●販売履歴テーブルのイメージ
図3●販売履歴テーブルのイメージ

 最後に購入顧客テーブルです。アフター・サービスのために必要ですね。テーブル名は「顧客名簿」です。

図4●顧客名簿テーブルのイメージ
図4●顧客名簿テーブルのイメージ

 一般にSQLデータベースのテーブルにはアルファベットで名前を付けます。日本語のテーブル名を使用できるデータベースもありますが,文字化けの原因になりやすいことからアルファベットを使う場合がほとんどです。ここでは,わかりやすさのために各テーブルの名前を日本語にしておきます。説明用のテーブル名だと理解してください。

 さて,四つのテーブルの中で日々変化していくのはどれでしょう。社員が車を販売すると,いつ何を誰に売ったかを「販売履歴」テーブルに記録します。同時に購入者の情報が「顧客名簿」テーブルに入りますね。この二つのテーブルのレコードは日々増加していくと考えられます。一方,「社員名簿」テーブルや「商品リスト」テーブルは,新入社員が入ったとか退社したとか,あるいは新製品ができた,生産中止した,などの場合にしか変化は出ません。

 まずはSQLの練習として,社員全員のリストを出してみましょう。社員名簿テーブルからすべてのデータを取り出す場合のSQLは次のようになります。

SELECT * FROM 社員名簿

 このように,データを取り出すときのSQLは,

SELECT 抽出するカラム FROM テーブル名

と書きます。「SELECT」と最初に書くのがポイントです。ちなみに,SQLで書かれた一連のコードのことを「SQL文」と呼びます。

 「SELECT * FROM 社員名簿」の実行例は,元のテーブルのデータ(レコード)すべてを抽出したものになります。SELECTの後に出てくる「*(アスタリスク)」が,「すべてのカラム」を表すからです。

図5●SQL文「SELECT * FROM 社員名簿」の結果
図5●SQL文「SELECT * FROM 社員名簿」の結果

 苗字のカラムだけを抽出したい場合には次のように,カラム名を一つだけ書きます。

SELECT 苗字 FROM 社員名簿

 このSELECT文の結果は下図のようになります。見事に苗字だけ抽出されました。

図6●SQL文「SELECT 苗字 FROM 社員名簿」の結果
図6●SQL文「SELECT 苗字 FROM 社員名簿」の結果

 複数のカラムを取り出したいときは,カラム名を「,(半角のカンマ)」でつないで列記します。苗字と名前であれば次のように書きます。

SELECT 苗字, 名前 FROM 社員名簿

図7●SQL文「SELECT 苗字, 名前 FROM 社員名簿」の結果
図7●SQL文「SELECT 苗字, 名前 FROM 社員名簿」の結果

 抽出時にデータの並べ替え(ソート)もできます。フリガナのカラムで社員名簿を並べ替えてみましょう。

SELECT * FROM 社員名簿 ORDER BY フリガナ ASC

図8●SQL文「SELECT * FROM 社員名簿 ORDER BY フリガナ ASC」の結果
図8●SQL文「SELECT * FROM 社員名簿 ORDER BY フリガナ ASC」の結果

 ソートには「ORBER BY」を使います。ORDER BYの後に並び替えのキーになるカラムを指定します。最後の「ASC」は昇順の意味で,降順でソートしたい場合は「DESC」と書きます。ORDER BYは常にSQLの最後に書きます。

 漢字のデータが格納されたカラムをキーに並べ替える場合は,注意が必要です。正しいソートができないのです。例えば,「安藤」や「青島」が必ずしも「伊藤」の上に来るとは限りません。

 コンピュータのソートでは,先頭文字のキャラクター・コードと呼ばれる内部機械コードが使用されます。安藤と書かれていても,読み方が「あんどう」なのか「やすふじ」なのかの判別ができるわけではないということです。姓名のような日本語カラムをソートしたい場合は,必ずふりがなのカラムを用意して,そちらでソートするようにします。さらに,ふりがなは,カタカナかひらがなで統一しないと正しくソートできません。

抽出条件を指定するSQL

 SQLには,特定条件に合致するかどうかという,Excelでいうフィルタに近い抽出機能もあります。Excelのフィルタよりもさらに詳細な条件付けが可能です。商品リストから色の赤い車だけを抽出してみましょう。

SELECT * FROM 商品リスト WHERE 色="赤"

図9●SQL文「SELECT * FROM 商品リスト WHERE 色="赤"」の結果
図9●SQL文「SELECT * FROM 商品リスト WHERE 色="赤"」の結果

 条件指定には「WHERE」を使います。WHEREの後にカラムの値が何かという条件を書きます。もし「赤でないとき」のように否定条件の場合は次のように書きます。

SELECT * FROM 商品リスト WHERE 色!="赤"

 「!=」というのが「等しくなければ」です。このほかにWHEREでは数値評価もできます。

SELECT * FROM 商品リスト WHERE 値段<2000000

図10●SQL文「SELECT * FROM 商品リスト WHERE 値段<2000000」の結果<2000000」の結果
図10●SQL文「SELECT * FROM 商品リスト WHERE 値段<2000000」の結果

 上記は「値段が200万より小さければ」を意味します(以下ではないので200万は含みません)。

 今度は複合条件にしてみましょう。色が赤あるいは青の例と,値段が200万円より安く,かつ色が赤の例です。

SELECT * FROM 商品リスト WHERE 色="赤" OR 色="青"
SELECT * FROM 商品リスト WHERE 色="赤" AND 値段<2000000

 複数の条件について結合する際は「OR」あるいは「AND」を使います。

 ORは「または」であり,ANDは「かつ」です。条件式は必要なだけANDやORでつなげて,いくらでも書けます。SQLでの条件指定については,そこにデータがあるのならば,どんな条件の組み合わせでも書けるのだと理解してください。

 Excelとデータベースの決定的な違いがここです。データベースというのは溜め込んだデータを,必要な形に整形して取り出すことができる環境です。あくまでも取り出すことが主であり,溜め込むことが主ではありません。ですから考え方としては,いかにして取り出しやすいようにデータを格納しておくかがポイントなんですね。取り出すときの条件に使う項目は,必ず存在していなければならないということでもあります。

複数テーブルから必要なデータを生成

 SQLによる抽出の概略が理解できたところで,複数のテーブルから必要な情報でデータを取り出すということを考えます。複数のテーブルから必要な情報を取り出す際に,各テーブルを結び付けていくのがリレーションシップです。例えば次のようなデータの抽出を考えてみます。

・各社員別顧客リスト
・各社員の総売上高
・女性購入者が赤い自動車を購入する比率

 こうしたデータは,一つのテーブルからだけでは作成できません。複数のテーブルから必要なものを取り出してきて連結(マージ)する必要があります。

 SQLでどうするか以前に,四つのテーブルをじっと眺めて,手作業で統計を取るにはどうするかを考えてみることから始めてください。

 それぞれのテーブルには一番最初に「○○ID」というカラムがあります。社員IDや商品IDという名前です。これが前回お話した「主キー」(プライマリー・キー)になっています。

 主キーは一意であり重複がありません。販売履歴は社員/商品/顧客の三つのテーブルから,その販売に関係する主キーを記録しています。誰が売ったのかは社員ID,何を売ったのかは商品ID,誰に売ったのかは顧客IDで記録されています。

 すべてが主キーの数値で,その数値が意味するところ(販売社員,商品,顧客)を知るには該当テーブルの内容を見ればいいわけです。見ればいいと言われても,これでは非常にわかりにくいですね。視認性が悪くてデータをまとめるのが困難だと感じます。しかしSQLならば,人間が処理するのには手間な段取りも一発でやってくれます。

 一例としてテーブルの連結というものをやってみましょう。

SELECT * FROM 販売履歴,社員名簿 INNER JOIN 販売履歴.社員ID = 社員名簿.社員ID

図11●テーブル連結の例
図11●テーブル連結の例

 販売履歴と社員名簿を,両方に存在する社員IDを使って連結しました。「INNER JOIN」を使っています。黄色くなっている列「社員ID」によって,販売履歴に埋め込まれていた社員IDに対応する社員名簿のデータをマージしたわけです。SELECT * としているので,両方のテーブルのすべてのカラムが出ています。

 テーブルの連結は,実際にデータベース上にあるデータを連結して,元のデータが変わってしまう,というものではありません。あくまで見かけ上連結しましたよという話です。データベースの中では何も変化は起きません。複数のテーブルから仮想的に大きなテーブルがあるかのような出力をしているというわけです。

 このとき,複数のテーブルを結びつけるのに使用される関係を「リレーションシップ」と言います。先の例では社員IDというカラムを使って,販売履歴テーブルと社員名簿テーブルの間にリレーションシップが成立しているわけです。今回使用した四つのテーブルは,すべて真ん中に販売履歴テーブルを置いて考えるとわかりやすくなります。

 マイクロソフトのデータベース・ソフトMicrosoft Accessに,リレーションシップを図として表現する機能があります。この機能を使って四つのテーブルの例を示すと,図12のようになります。

図12●Accessでリレーションシップ図を作成してみました
図12●Accessでリレーションシップ図を作成してみました

 データベースの基礎部分がわかってくると,Accessのいろんな部分の意味が見えてきます。Accessをお持ちの方は,一度簡単なサンプルを自分の手で作ってみるとさらに理解が深まるかと思います。

 三つ,四つのテーブルから必要なデータを取り出すこともできます。ただ本稿の意図するところは,皆さんにデーターベース・マスターになっていただくということではありませんので,ぼちぼちデータベースの説明からは離れていこうかと思います。

ここまでのまとめ

 ここまで,データベースの基礎部分について理解していただくために説明してきました。Web+DB案件のキモは,サイト全体のデザインやウォークスルーのしやすさではなくて(それは別途デザイナーとご検討いただく内容です),何をデータとして蓄積して,どんな統計を必要とするかというデータ周りに対する意識です。

・統計に不要なデータは蓄積しない

 自身がユーザーになって考えてみてください。例えば登録フォームで誕生日の入力が本当に必要でしょうか。特に決済を行わないサイトで住所の登録が必要でしょうか。どこのURLからたどってきたかを入力させる必要があるでしょうか。

 こうした後々使用されることのないデータはハードディスク容量を圧迫し,データが増えることでデータベースやサイト全体のパフォーマンスを落とします。また個人情報を持ちすぎると漏洩などの被害に遭いやすくなり,もしそうなると社会的信用を失って企業が受けるダメージは相当なものになります。

・初期データは十分に検討する

 開発には必ず商品リストのような初期データが必要です。商品の検索機能などでは,初期データにないものは絶対に検索できません。検索が必要か,どんな条件での検索を提供するか,設定したカラムですべての商品のスペックを記録できるかを検討してください。システム稼動後にカラムを追加していくことは困難です。手持ちの初期データはExcelできれいに表にできるかどうかを検討してください。

・そこにあるものは必ず取り出せる

 リレーションシップによって,たとえ何十個に分かれているテーブルからでも必要なデータを抽出することができます。統計には妥協しないようにしてください。またあとで統計を取りたいものについてはできるだけ初期から開発者に対して伝えておくと,設計段階でリレーションシップについて検討してくれるはずです。当初のテーブル設計段階でリレーションシップ図も提出するようにすれば,漠然とでもデータベース全体像をイメージできます。

 「よくわからないから」と当初から開発に対して丸投げにしてしまうと,多くの場合は後々に希望を出しても「できません」と言われます。データベースというのは,そこにあるものから取り出すということに対しては非常に優れていますが,今まで集めていなかったものを新たに集めたいと言われると,根底から作り直しが必要になる場合もあるのです。特に数千,数万とデータがすでに入ってしまっている状態では,作り直そうとしても,既存のデータの安全性を保証しづらくなります。

 システムが本格稼動する前に,とにかくデータベース構造だけでも固めてしまうというのは,Web+DB案件ではとても重要なステップなのだと理解してください。