データベースとSQL

データベースはデータの貯蔵庫です。いろいろな種類がありますが、広く使われているのはリレーショナルデータベース（関係データベース、relational database、RDB）と呼ばれるものです。

リレーショナルデータベースを操作するには SQL という言語を使います。SQL はもともと Structured Query Language（構造化問合せ言語）の略でしたが、今では単に SQL（エスキューエル）が正式名になっています。歴史的な事情から英語圏では SQL と書いて Sequel（シークエル）と読む人も多く、冠詞を付ける場合にエスキューエルと読む人は an SQL、シークエルと読む人は a SQL と書くのですが、今は an SQL で統一することが多くなったようです。

データベースへの問合せを query（クエリ、クエリー）といいます。リレーショナルデータベースのクエリは選択・射影・結合を組み合わせて行います。・・・みたいな抽象的な説明ではおもしろくないので、ここでは実習をしながら学んでいきます。

SQL を実習するために使うリレーショナルデータベース管理システム（Relational Database Management System、RDBMS）にはいろいろなものがありますが、ここでは SQLite というオープンソース（パブリックドメイン）のデータベースソフトを使います。macOS や Linux にはたいてい sqlite3 というコマンドとして入っていると思います。他に MySQL や MariaDB や PostgreSQL がよく実習で使われます。

SQLite を起動するには

sqlite3 -column -header filename.db

のように打ち込みます（filename.db は任意のファイル名です）。あるいは

sqlite3 filename.db

のようにオプションなしで立ち上げた場合は

.headers on
.mode column

という設定を打ち込めば出力結果が見やすくなります。ピリオドで始まる命令は SQL の命令ではなく SQLite の設定のためのものです。.help で説明が出ます。.exit または .quit で終了します。

テーブルを作ろう

たとえば hyou という名前のテーブル（表）を作ってみましょう。

create table hyou(hinmei text, nedan int);

と打ち込んでください。あるいは

CREATE TABLE hyou(hinmei TEXT, nedan INT);

のように、SQL の命令部分を大文字で書く流儀もありますが、どちらでも同じことです。

text は文字列、int は整数（integer）の意味です。

この表に値（values）を挿入（insert）してみましょう。

insert into hyou values('りんご', 500);
insert into hyou values('みかん', 300);

挿入した列をすべて表示してみましょう。「すべての列」は米印 * で表します。

select * from hyou;

すると次のような表が表示されるはずです。

hinmei  nedan
------  -----
りんご     500  
みかん     300  

ここでは見やすいように次のように書き表すことにします。

行をレコード、列をフィールドと呼ぶことがあります。

値段が 400 円以上の行だけ表示してみましょう。

select * from hyou where nedan >= 400;

このように、一部の行だけ抜き出すことを、選択といいます。SQLiteでは比較の演算子はPythonなどと同じものが使えます（ただし規格では等しいは =、等しくないは <> です）。

hinmei の列だけ表示するには次のようにします。

select hinmei from hyou;

このように、一部の列だけ抜き出すことを、射影といいます。

選択と射影は同時に行うことができます。

select hinmei from hyou where nedan >= 400;

合計（sum）を求めてみましょう。

select sum(nedan) from hyou;

値段の順に並べてみましょう。

select * from hyou order by nedan;

無指定では値段の小さい順（昇順、ascending order）に並びます。大きい順（降順、descending order）に並べるには desc を付けます。

select * from hyou order by nedan desc;

データの更新には update を使います。

update hyou set nedan=200 where hinmei='みかん';

行の削除は delete です。

delete from hyou where hinmei='みかん';

表を削除するには drop table 表の名前; とします。

データベース設計

主キーを定めよう

上の例で使った商品データベースでは、りんごがいろいろあったら品名だけで行が決まりません。名前だけで行が決まるような詳しい名前を付けるか、あるいは商品コードを付けましょう。

学生名簿でも、同じ名前の学生が二人いたら、名前だけで行が決まりません。学籍番号を付けましょう。

商品コードや学籍番号のように、これが決まれば行が特定できるものを、主キーといいます。

第1正規化

学生名簿に「所属クラブ」という列があると、複数のクラブに所属している学生はどう扱えばいいでしょうか。

こんな芸当はできません。^^;

これだと三つのクラブに所属する人が現れたら困ります。それに、このようにしてしまうと、あとの処理が難しくなります。

そのようなときは、複数の行に分けましょう。

これを第1正規化といいます。

実際に SQL で実習するには、次のようにして入力してください。

create table 学生名簿(学籍番号 text, 氏名 text, 所属クラブ text);
insert into 学生名簿 values('1788', '三重太郎', '野球部');

学籍番号は整数 int でもいいように思いますが、頭が 0 で始まる番号や、途中にアルファベットを含むような「番号」があるかもしれませんので、文字列にするほうが安全です。

第2正規化

上の表では、主キーは「学籍番号」と「所属クラブ」の二つになります。二つの主キーの値を指定すれば行が定まるので、これはまったく問題ありません。問題は、氏名が「学籍番号」だけで決まってしまい、もう一つの主キー「所属クラブ」によらないということです。このように、ある列の値が主キーの一部だけによって決まる場合は、表を次のように二つに分けましょう。

学生名簿（主キーは学籍番号）

クラブ所属表（主キーは学籍番号と所属クラブ）

実際にはクラブに属していない学生も多いので、クラブ所属表は学生名簿ほど大きくなりません。

このように、主キーの一部だけに依存するような項目をなくすことを、第2正規化といいます。

第3正規化

学生名簿は実際にはもっといろいろな項目を含みます。たとえばコースについての情報が次のように入っているとしましょう。

「コース名」が決まれば「コース主任」は決まってしまいますね。そこでまた表を分割しましょう。

学生名簿（主キーは学籍番号）

コース表（主キーはコース名）

コース表は、コースの数だけでいいので、たいへん小さくなります。

このように、別の項目に依存するような項目をなくすことを、第3正規化といいます。

表はできるだけ正規化するのが正しいデータベース設計です。

結合（join）

上のように正規化した場合、いくつかの小さな表ができます。これらを結合して、正規化する前の大きな表に戻す方法が必要になります。SQLの結合にはいくつかの方法がありますが、ここでは説明を簡単にするために、同じ列名の列を一つにまとめる natural join だけを扱います。

学生名簿とクラブ所属表から、両方のクラブに所属する学生について、氏名なども含めた大きい表を作るには、次のようにします。

select * from 学生名簿 natural join クラブ所属表;

三つ以上の表を結合することもできます。

select * from 学生名簿 natural join クラブ所属表 natural join コース表;

また、学生名簿とクラブ所属表から、学生名簿に含まれるがクラブ所属表には含まれない学生も含めて出力するには、次のようにします。

select * from 学生名簿 natural left join クラブ所属表;

このとき所属クラブが空欄の行ができます。空欄のある行だけ抜き出すには次のようにします。

select * from 学生名簿 natural left join クラブ所属表 where 所属クラブ is null;

この否定は is not null です。