DB の考え方と SQL

どんなことに使われているのか

DataBase (DB) とは、データの管理とその利用を担当するシステムです。
データが多くなるにつれ、高速なデータ検索性能の確保、複数クライアントからの操作時のデータ同期など、さまざまな問題が起こり得ますが、DB はそのあたりを解決してくれます。
以下、データベースの例です。

• Web の検索エンジン ... キーワード、分類を指定しての検索 [応答性能]
• 図書館の蔵書検索 ... 条件、キーワードを指定しての検索 [応答性能]
• 名簿の管理 ... データ保持。検索
• 銀行でのオンラインシステム ... ATM を介しての操作 [応答性能][操作の保証]

今回は、以降でデータベースについての考え方と、SQL 構文について説明します。

---

実際にデータを処理してみよう

リレーショナル DB

本内容では、リレーショナル(表)・データベースを扱います。その名の通り、データを表に格納したイメージでもって扱います。こんな感じです。

学籍番号順にソ−トされたリレ−ショナル タブ区切りのファイル

学籍番号	名前	所属
2004001	あんどう なっつ	情報科学
2004002	くたに やきもの	情報科学
2004101	はら へった	情報工学
2004102	いのうえ はのど	情報工学
2004203	こうき にゅうがく	情報科学
2005001	さとう みつこ	情報科学
2005002	みはま げんば	情報科学
2005003	とうきょう あきは	情報科学

では早速、配付資料１から、以下の条件に該当する情報を検索してみましょう。

• 学籍番号が 2004000 〜 2004999 な人の名前
• 学籍番号が 2004000 〜 2004999、で、情報科学に所属している人の名前
• 名字があ行の学生から３人の名前

.
.
.

できました？

上記の検索を、データベースについての問い合わせ言語である SQL で記述すると、こんな感じになります。データの準備等については、後で紹介します。

• 全学生の表示

    SELECT * FROM db1_students; 
  

• 学籍番号が 2004000 〜 2004999 な人の名前

    SELECT * FROM db1_students WHERE (student_id >= 2004000 and student_id <= 2004999); 
  

• 学籍番号が 2004000 〜 2004999、で、情報科学に所属している人の名前

    SELECT * FROM db1_students WHERE (student_id >= 2004000 and student_id <= 2004999) and attribute = '情報科学'; 
  

• 名字があ行の学生から３人の名前

    SELECT * FROM db1_students ORDER BY name limit 3; 
  

ここで「学籍番号が 2004000 〜 2004999、で、情報科学に所属している人の名前の検索」は、まず学籍番号で検索して、その結果から情報科学な人を検索、という方法をとることもできます。

• 検索結果からさらに検索

    SELECT * FROM
      (SELECT * from db1_students WHERE student_id >= 2004000 and student_id <= 2004999) AS db1_student_part
      WHERE attribute = '情報科学'; 
  

インデックス

では、先の例と同じ検索を、以下に示すリレーションを見ながら行ってみて下さい。

名前でソートされたリレーショナル

名前	学籍番号	所属
あんどう なっつ	2004001	情報科学
いのうえ はのど	2004102	情報工学
くたに やきもの	2004002	情報科学
こうき にゅうがく	2004203	情報科学
さとう みつこ	2005001	情報科学
とうきょう あきは	2005003	情報科学
はら へった	2004101	情報工学
みはま げんば	2005002	情報科学

今回のリレーションでは、名前順に人を検索する操作が簡単になったのと思います。そのかわり、学籍番号についての検索はかなり面倒になりました。
このように、同じデータでも管理状態によって、検索しやすい、しにくい場合があります。これは、実際のデータベースでは、応答時間といった形で現れてきます。

そこで、あるデータベースで、学籍番号が 2004000 〜 2004999 かどうか、といった検索が多く行われると分かっている場合、あらかじめインデックスを作成しておくことができます。この例では、2004000 〜 2004999 の場合を ○ で示します。

名前でソートされたリレーショナル(入学年度のインデックス付き)

	名前	学籍番号	所属
○	あんどう なっつ	2004001	情報科学
○	いのうえ はのど	2004102	情報工学
○	くたに やきもの	2004002	情報科学
○	こうき にゅうがく	2004203	情報科学
	さとう みつこ	2005001	情報科学
	とうきょう あきは	2005003	情報科学
○	はら へった	2004101	情報工学
	みはま げんば	2005002	情報科学

実際のデータベースにおいても、どのような検索が多く行われるかを意識しながらデータベースの設計を行うことが大事です。多分。

複数のリレーションを用いた検索

最後に、複数リレーションを用いた検索例を示します。以下のリレーションは、ある講義を申請している学生の学籍番号一覧です。

ある講義を申請した学籍番号のリレーショナル タブ区切りのファイル

受講者の学籍番号

2004001

2004203

2005001

2005002

2005003

では、この講義を受講している学生の名前を検索して下さい。

.
.
.

面倒ですよね...。

人間にやらせると面倒がることも、コンピュータは間違えない限り文句を言いません。なので、早速この問い合わせを実現する SQL を紹介します。 SQL の概略としてはリレーションを連結し、新たに作ったリレーションに対して問い合わせを行っている感じです。順に見ていきましょう。

• リレーション同士を ,(カンマ)で区切った場合の問い合わせ結果

    SELECT db1_students.student_id, name
      FROM db1_students, db1_class 
  

  -結果

  # SELECT * FROM db1_students, db1_class;
   student_id |       name        | attribute | student_id 
  ------------+-------------------+-----------+------------
      2004101 | はら へった       | 情報工学  |    2005002
      2004101 | はら へった       | 情報工学  |    2005003
      2004102 | いのうえ はのど   | 情報工学  |    2004001
      2004102 | いのうえ はのど   | 情報工学  |    2004203
      2004102 | いのうえ はのど   | 情報工学  |    2005001
      2004102 | いのうえ はのど   | 情報工学  |    2005002
      2004102 | いのうえ はのど   | 情報工学  |    2005003
      2004203 | こうき にゅうがく | 情報科学  |    2004001
      2004203 | こうき にゅうがく | 情報科学  |    2004203
      2004203 | こうき にゅうがく | 情報科学  |    2005001
      2004203 | こうき にゅうがく | 情報科学  |    2005002
      2004203 | こうき にゅうがく | 情報科学  |    2005003
      2005001 | さとう みつこ     | 情報科学  |    2004001
      2005001 | さとう みつこ     | 情報科学  |    2004203
      2005001 | さとう みつこ     | 情報科学  |    2005001
      2005001 | さとう みつこ     | 情報科学  |    2005002
      2005001 | さとう みつこ     | 情報科学  |    2005003
      2005002 | みはま げんば     | 情報科学  |    2004001
      2005002 | みはま げんば     | 情報科学  |    2004203
      2005002 | みはま げんば     | 情報科学  |    2005001
      2005002 | みはま げんば     | 情報科学  |    2005002
      2005002 | みはま げんば     | 情報科学  |    2005003
      2005003 | とうきょう あきは | 情報科学  |    2004001
      2005003 | とうきょう あきは | 情報科学  |    2004203
      2005003 | とうきょう あきは | 情報科学  |    2005001
      2005003 | とうきょう あきは | 情報科学  |    2005002
      2005003 | とうきょう あきは | 情報科学  |    2005003 
  

長いですが、簡単に表現するなら２つのリレーションの総組み合わせです。 これを用いて受講者のリストを作るならば、db1_students の student_id と db1_class の student_id が同じ行にあるデータが、目的の検索結果と同じなります。

• ある講義を受講している学生の名前を検索の SQL 例

    SELECT db1_students.student_id, name
      FROM db1_students, db1_class
      WHERE db1_students.student_id = db1_class.student_id; 
  

リレーション同士の演算は、上記のようなものと、自然結合と呼ばれる演算が一般的です。というか、自然結合の結果が今回の問い合わせの答えになったりします。
自然結合とは、２つのリレーショナルで共通の項目を持つレコード同士を取り出すような感じです。

• 自然結合の結果

  # SELECT * FROM db1_students NATURAL JOIN db1_class;
   student_id |       name        | attribute 
  ------------+-------------------+-----------
      2004001 | あんどう なっつ   | 情報科学
      2004203 | こうき にゅうがく | 情報科学
      2005001 | さとう みつこ     | 情報科学
      2005002 | みはま げんば     | 情報科学
      2005003 | とうきょう あきは | 情報科学 
  

• で、改めて SQL を書き出すと、こんな感じです。

    SELECT * FROM db1_students NATURAL JOIN db1_class; 
  

まぁ、今後はこういったことを、少しずつ説明していきます。