データベース入門

概要

今回は、リレーショナルデータベースの概要とそれを操作するための要であるSQLというデータベース言語を学びます。

データベースとは

データベース（DB）とは、大量の情報を保存し、コンピュータから効率よくアクセスできるようにしたデータの集まりのことです。フォームを通して送られてきた情報など、様々な情報をここに保存しておきます。

リレーショナルデータベース

データベースと一概に行っても、様々な種類のものがあります。例えば、階層型データベースやリレーショナルデータベース（RDB）と言ったものです。

前者は、古くからあるデータベースの一種で、木構造によって表現されています。速度が早いことがメリットとして挙げられますが、データの追加や削除に大きなコストがかかるなどによって、後者のデータベースに取って代わられました。後者は、現在最も広く利用されているデータベースです。その認知容易性と柔軟な表現方法により、1969年誕生にもかかわらず、長い間第一線で活躍しています。

リレーショナルデータベースは表形式でデータを管理しています。これはExcelとかでよくみられる形なので、馴染みやすいだろうと思います。この表のことをテーブルと言います。テーブルはRDBMSが管理するデータベースの中に保存されます。複数のテーブルを一つのデータベース内に保管することができます。

SQL

SQLはRDBを効率よく操作するための言語です。SQLのコードを書いて発行することによって、データベースを操作すること（例えば、データベースを作ったり、データを挿入したり）ができます。

標準SQL

SQLは国際標準化機構（ISO）で定められた標準規格があり、それに準拠したSQLを標準SQLと言います。ただ、歴史的な背景から必ずしも標準SQLに全てのRDBMSが準拠しているとは言えず、そのRDBMS独自の方言があったりとかもします。しかし、最近は標準SQLの文法サポートが進んできているので、とりあえずこれを覚えておけば大丈夫です。

データベースの構成

コンピュータ上でデータベースを管理するシステムをデータベースマネージメントシステム（DBMS）と言います。今回はリレーショナルデータベースを取り上げて扱うので、その先頭にリレーショナルのRをつけて、RDBMSと表記することにします。

クライアント/サーバ型

さて、RDBMSを利用する際のシステム構成は、クライアント/サーバ型（C/S型）が一般的です。

サーバは、一般的に他のプログラムからの要求を受け取り、それに応じて操作を行うソフトウェア・マシンのことを指していたことを思い出してください。RDBにおいてもその意味であり、これはRDBMSが相当します。

一方、サーバに要求するプログラム・マシンをクライアントと言います。

流れにそって見てきましょう。まず、クライアントはSQL文をサーバ、すなわちRDBMSに渡します。するとそれを受け取ったサーバはデータベースにアクセスして、データ取り出し・削除などの操作を行います。そうして、取り出したデータをクライアントに返します。

SQLiteとDB Browser for SQLite

SQLite

RDBMSの一つとして、SQLiteというものがあります。データがファイルに保存されたり、Pythonから操作しやすいというメリットがありますので、これを使用していきます。ただ、実用環境には適していないので、SQLiteの使用は開発環境のみに留めておき、それ以外ではPostgreSQLやMySQLと言ったRDBMSを使用した方が良いでしょう。

DB Browser for SQLite

DB Browser for SQLiteとは、SQLiteのデータベースをGUIで管理することができるツールです。初心者にもハンズオンで操作することができてわかりやすいので、今回はこれを使用します。

DB Browser for SQLiteのダウンロードとインストール

DB Browser for SQLiteにはSQLiteが付属しているので、DB Browser for SQLiteのみをインストールすればセットアップは完了となります。

ダウンロード

まず、公式サイトのダウンロードページにアクセスします（2020/9/29現在リンク有効）Windowsの方は、Windowsのところからダウンロードします。自分のパソコンが32-bitなのか64-bitなのか確認しましょう。おそらく大半の人が64-bitですので、その場合は、「DB Browser for SQLite - Standard installer for 64-bit Windows」というところをクリックします。Macの場合はMacOSセクションの「DB Browser for SQLite」をクリックします。順次ダウンロードが始まると思います。

インストール

ダウンロードができたら、インストールを進めていきます。Windowsの場合は、インストーラが開いて保存先や規約の同意を求められるようです。ここはわからないので、なんとか進めてください（基本的にデフォルトで大丈夫だと思います）。Macの場合は、アプリケーションフォルダにいれる画面になります。ただ、アプリケーションDB Browser for SQLiteを開いた時に次のようなダイアログが出てしまいました（自分だけかもしれません）

この場合は、右クリックで開くとアプリケーションを開くことができます。

テーブルを定義する

データベースを作成する

まず「新しいデータベース」ボタンをクリックします。ファイル名と保存先を指定して、保存します。これでデータベースのファイルが作成されました。

テーブルを定義するには

すると、テーブル定義の画面になります。ここには、テーブルの内容を書き込んでいきます。少しおさらいをすると、テーブルは表のことでした。例えば、「会員」表を作ったとします。（ここでは、所属は1つ以下しかできないことにします。所属していない場合はNULLとなります）

ここには5人のメンバーの情報が記載されています。表頭によってこの表になんの情報を載せるかを定義されています。ですから、テーブルの作成はまず、なんの情報を記載するかを定義するところから始まります。

テーブルを定義してみる

「テーブル」と書かれたところに、「Member」と打ち込んでください（鉤括弧は要りませんよ）。テーブル名をつけることができました。

次にフィールドの「追加」ボタンを押してください。そして、名前のところに「member_id」でデータ型を「INTEGER」、NN（もしかしたらWindowsだとNotと書いてあるかも）とPKにチェックを入れてください。

データ型の指定

上の作業は、まずテーブルに「member_id」つまり会員番号を載せたいということを示しています。次に「INTEGER」としたのは、会員番号の列に整数を入れるよというのを明示するためです。「INTEGER」と指定した列に文字列などの他のデータ型は原則入れない方がいいです（SQLiteの場合）。

SQLiteのデータ型には次のような種類があります（一部のみ）

制約の設定

制約はデータ型の他に、列に入れるデータに制限・条件を課したい時に用います。「member_id」にはNNとPKという制約がかかっています。

さて、NNはNOT NULLの略です。NOT NULL制約は、NULL即ち「無記入状態」を禁止します。つまり、行を追加した時に、「member_id」はデータなしではいけないということです（つまり記入しなければなりません）

PKはPRIMARY KEYの略です。PRIMARY KEYのことを日本語で主キーというので、この制約のことを主キー制約と言います。主キーは一つの行を特定できる列のことです。例えば、会員番号からその人の名前、所属、登録日などは行として一意に定めることができるので、会員番号は主キーにするのにふさわしいことがわかります。主キーは一意性があります。

定義を続ける

同じ要領でいくつかの列名を定義していきます。またフィールドの追加を押してください。2番目の列は名前は「member_name」、データ型は「TEXT」、NNにチェックを入れましょう。3番目の列は名前は「belong_to」、データ型は「TEXT」でチェックは何もなしです。4番目の列は名前は「registration_date」、データ型は「TEXT」でNNにチェックを入れましょう。

さて、結果としては次のような感じになっています。

これでOKを押してみましょう。ふう、データベースの定義が終わりました。

データを登録する

DB Browser for SQLiteの場合、データを登録する作業はグラフィカルに行うことができます。まず、先ほど作成したMemberテーブルを選択します。そうして、「データ閲覧」というところに移ります。すると定義した行頭部分が作成されていることがわかります。

次に、「新しいレコードを現在のテーブルに挿入」ボタンを押します。

この写真の右から2番めのアイコンがそれです。押してみると、行が追加されます。主キーに指定した「member_id」には勝手に一意の整数が入力されていることでしょう。それは無視して他のますの空欄を埋めていきます。上の「テーブルを定義する」で記載した表を参考にしてください。ちなみに、未記入を表すNULL状態にしたいときは、「NULL」と空欄に記入するのではなく、そのまま何も記入しないようにしましょう。

さて、次のようになったでしょうか。

これでデータ登録は終わりです。お疲れ様でした。

データを選択する

列を出力する

テーブルからデータを取り出すときはSELECT文を使用します。テーブルからSELECT文で必要なデータを取り出すことをクエリ（query）と言います。このクエリこそSQLのミソであり、最も重要な部分でもあります。

最も基本的な列の出力から行っていきましょう。構文は以下のようになります。

SELECT <列名>, ...
FROM <テーブル名>;

列名には、「member_id」だとか「name」だとかが入ります。複数の列を一気に取り出したい場合は、カンマで区切って複数指定します。テーブル名は今回の場合は「Member」となっています。あと、セミコロンがあるのも注意してください。

では、会員の名前を取り出してみましょう。先ほどと同じく、「Member」テーブルを選択したまま「SQL実行」というタブを開きます。開いたら、一番左上のボックスがコーディングをする場所になっています。ここに、

SELECT member_name
FROM Member;

と入力し、その上にある再生ボタン（いわゆる三角のボタン）を押してください。すると、コードの下のボックスに取り出されたデータがあるはずです。初クエリ完了です。

もちろん、

SELECT member_name, registration_date
FROM Member;

と複数してもいいかもしれませんね。ここで気付くのは、結果の列がSELECT以降に書いた順番通りになっていることです。例えば、「SELECT membername, registrationdate」なら左から会員の名前、登録日という順番に並びます。

全ての列を出力する

全ての列を出力するには、全ての列を意味する「*」（アスタリスク）を使用します。

SELECT *
FROM Member;

これでテーブル全体が確認できるでしょう。

結果から重複を除く

ここで、登録日に重複を作るため、会員番号6の深沢くんが入会したとしましょう。「membername」に「深沢」、「belongto」に「セキュリティ」、「registration_date」に「2020-5-11」として新規のレコードを登録してください。

さて、次のクエリを実行してみてください。

SELECT registration_date
FROM Member;

すると、2020-5-11が重複していることがわかります（当然です、それを意図的に作り出したのですから）この重複を取り除いた結果を得たいときは、SELECT句でDESTINCTというキーワードを用います。

SELECT DISTINCT registration_date
FROM Member;

これで、重複行を省いたクエリ結果を得ることができました。

WHERE句による行の選択

WHERE句は、選択したい行の条件を付与します。例えば、「会員番号が3以上の人」だったり、「ハロプロ部門に所属している人」だったりと言ったことで、条件をつけるということです。

SELECT分では、WHERE句を次のように書きます。

SELECT <列名>, ...
FROM <テーブル名>
WHERE <条件式>;

順番がややこしいので気をつけてください。FROM -> WHEREの順番です。

では、試しに「会員番号が3以上の人の名前」を取り出してみましょう。このクエリは次のようになります。

SELECT member_name
FROM Member
WHERE member_id >= 3

また、「ハロプロ部門に所属している人の名前と登録日」を出力する場合は、次のようになります。

SELECT member_name, registration_date
FROM Member
WHERE belong_to = "ハロプロ"