# SQLite3 Practice

SQLite3自体は授業で勉強しますが、本資料ではもう少し深い部分について触れながら実践的なSQL、またSQLite3の機能ついて紹介、解説をしていきます。

## RDBMS

ソフトウェア開発の重要な要素の一つとして、 __DB(Database)__ があります。DBは一般的に情報の集まりのことを指し、これに対してアクセスをしてDBの管理を行うシステムのことをDBMS(Database Management System)と言います。SQLite3はこのDBMSの一つです。今日広く使われているDBの一つにRDB(Relational Database)があり、そのDBMSを __RDBMS(Relational Database Management System)__ と言います。SQLite3もRDBを扱うDBMSなので、普通はRDBMSと呼びます。

### DBとファイルシステム

データを管理する方法として、ファイルシステムを利用する方法もあります。

DBとファイルシステムは、どちらもメモリ上ではなくHDD上にデータを保存して管理するため、プログラムやコンピュータを終了しても、データが消えることはありません。これを永続性、あるいは不揮発性があるといいます。

DBを使う場合とファイルシステムを使う場合の違いは以下のとおりです。

||DB|ファイルシステム|
|:-:|:-:|:-:|
|ファイルパス|×|○|
|手軽さ|×|○|
|構造化されたデータ|○|×|
|データを統一的に扱うクエリ|○|×|
|他のアプリケーションとの共有|○|×|
|データの一貫性|○|×|
|実装コード量|○|×|

ファイルシステムは、一般に永続的にデータを残したい場合に使われます。ファイルシステムで扱うデータには、HTML文書、画像ファイル、実行ファイル、設定ファイルなど、様々なものがあります。テキストエディタさえあれば誰でも編集が出来るので、手軽です。

しかし、ファイルシステムで生成されたデータをプログラムから扱う場合、データの構造やインターフェースは、完全にユーザに委ねられてしまいます。例えば、表データなどを扱う場合、区切り文字が、カンマ・タブ・コロンなど、それだけでデータを扱うプログラムに大きな影響を与えます(データの一貫性、実装コード量)。ユーザがデータを扱うプログラムを記述するだけで、大きなコストとなってしまいます。

それに対してDBは、厳密に定められたデータ構造を持ち、ユーザはデータの取り扱いを意識する必要が無くなります。RDBMSを使いデータを処理する場合は、SQL(Structured Query Language)というDBを扱うためだけに使われる、ドメイン固有言語(特定のタスク向けに設計された言語、DSL)を使うことで、統一的なインターフェースを利用することができます。

-- RDMBSを操作するSQLの例
-- 「あの口座の最新20件の預金履歴」を検索するSQL
SELECT * FROM deposits
WHERE deposits.id IN (
	SELECT id FROM accounts
	WHERE accounts.code = '123-45678'
)
ORDER BY deposits.id DESC
LIMIT 20;

-- |deposits.id|code|ammount| 
-- |1|123-45678|10000|
-- |2|123-45678|25000|
-- |3|123-45678|15000|
-- ...
-- |20|123-45678|5000| 

### 色々なDBシステム

データベースシステムは長い歴史の間、集合論と述語論理に基いて考案された関係モデル(リレーショナルモデル)を利用したRDBが主流でしたが、現在は様々なデータベースモデルを利用したNoSQL(一般に "Not only SQL" と解釈される)が多く存在すます。２つのデータベースどちらかに優位性は無く状況に応じて使い分けるのが、現在のソフトウェア開発における一般的な見解です(詳しくは、[CAP定理](https://ja.wikipedia.org/wiki/CAP定理)を参照)。以下にデータベースシステムのいくつかの例を列挙します。

|データベース名|モデル|特徴|
|:-:|:-:|:-|
|MySQL|RDBMS|オープンソース・GPLとコマーシャルライセスの デュアルライセンス方式|
|PostgreSQL|RDBMS|オープンソース・The PostgreSQLライセンス|
|SQLite3|RDBMS|ライブラリ型、Androidなど 組み込み目的で利用される|
|BigTable|列指向|Google製、高速・超大規模向け、 様々なGoogleサービスで利用される|
|MongoDB|ドキュメント指向|BSON(ドキュメント)形式、 JavaScriptコードを実行できる|
|Apache Cassandra|キー・バリュー型|Apache製(元Facebook開発)、オープンソース|

RDBMSにも多くのデータベースシステムが存在します。今回の講義・演習では、データベースを扱うためのサーバを利用が必要の無いプログラム上で稼働するお手軽な**ライブラリ型**のデータベース*SQLite3*を使用します。

**ポイント**
- SQLite3はRDBMSというシステムの一つで、ライブラリ型のRDBMS。
- RDBはデータ集合の関係を扱って操作するデータベース。

## SQLite3の基本操作

SQLite3のREPLを学内環境で起動する時には以下のコマンドを使います。

$ sqlite3
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

また、終了する時にはSQLite3のREPL上で`.exit`または`.quit`を入力します。

sqlite> .exit
$

SQLite3のDBはファイルで管理されているので、まずはそのファイルを作成します。

$ touch example.sqlite

DBファイルを読み込むには以下のようなコマンドを入力します。

$ sqlite3 example.sqlite
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

この状態のREPL上でSQLを実行することでDBファイルにデータが書き込まれ、保存されていきます。

SQLとはクエリーを記述するための言語のことです。

また、ファイルに保存したSQLをSQLite3で実行する時には以下のコマンドを使います。

$ sqlite3 < example.sql

## RDBMS

### 準備

これから簡単なSQLを書いてRDBMSに慣れていきましょう。

[ここから](https://gist.github.com/misoton665/04df7384c51fff1b135c4a919bc9aa33)サンプルのSQLファイルを作業ディレクトリにダウンロードしてください。

初めに、DBファイルを作成して、ダウンロードしたSQLを実行します。

$ sqlite3 sample.sqlite3 < user.sql

DBに書き込んだデータを初期化するには以下のようにします。

$ echo "" > sample.sqlite3

これ以降のSQLはusers.sqlを実行した状態のsample.sqlite3を読み込んだREPL上で実行するものとします。

初めに、RDBのデータがどのような形で格納されているのかを見てみましょう。

【演習】以下のSQLを実行しなさい。

SELECT * FROM users;

実行結果を見るとデータが表の形になっていることがわかります。SQLでデータを取得する時には、このテーブルから列を指定したり別の表を結合することでデータを制限します。

### データを関連づける

次に、RDBの特徴である、データの関連を使った取得をしてみましょう。

【演習】以下の二つのSQLを実行しなさい。

SELECT * FROM users;
SELECT * FROM users NATURAL JOIN exercises;

このSQLはusersとexercisesのテーブルを結合して取得しています。

## SQL

SQLは、RDBMSを操作するために作られたドメイン固有言語です。SQLはDDL(Data Definition Language),DML(Data Manipulation Language),DCL(Data Control Language)の三つの言語をまとめた呼び方です。

それでは、それぞれの言語について見てみましょう。

## DDL

Data Defintion Language(DDL)はデータの形を定義するためのSQLです。

CREATE TABLE, ALTER TABLE, DROP TABLEなど、テーブルを作ったり削除したりする構文がDDLにあたります。

### CREATE TABLE

テーブルを作成する構文です。テーブルには行と列があり、行がデータ、列が属性になっています。CREATE TABLEでは属性を指定してテーブルを作成します。

-- usersテーブルを作成
CREATE TABLE users(id, name, age);

他のRDBMSでは属性には型を指定しなければいけませんが、SQLite3では型を指定する必要はありません。ですが、基本的には指定した方がいいでしょう。

CREATE TABLE users(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(32),
  age INTEGER
);

VARCHER(n)はn文字の文字列を指す型です。他のRDBMSでは日付型が用意されていますが、SQLite3は日付型はないので、文字列で保存します。

また、他に特殊な属性の定義をすることができるので、調べてみると良いでしょう。

### DROP TABLE

テーブルを削除する構文で、非常にシンプルです。

-- usersテーブルを削除
DROP TABLE users;

### テーブルスキーマの確認

生成したテーブルの構造(スキーマ)を確認するには、`.schema`命令を使います。

sqlite> .schema
CREATE TABLE users(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(32),
  age INTEGER
);
CREATE TABLE exercises (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  number INTEGER NOT NULL,
  user_id INTEGER REFERENCES users(id),
  score INTEGER NOT NULL
);

## DML

Data Manipulation Language(DML)はデータを操作するためのSQLです。

SELECT, INSERT, UPDATE, DELETEなど、テーブル上にデータを挿入したり、参照したりする構文がDMLにあたります。

### SELECT

SELECT文はテーブルから行を選択してデータを取得する構文です。

`users`テーブルから`name`属性と`age`属性のデータを取得するときは以下のようになります。

SELECT name, age FROM users;

-- Pole|23
-- Anna|22
-- Mary|19
-- Bob|38
-- Taro|33
-- Tom|17
-- Sara|44
-- Mike|26

-- 全ての属性を取得する場合はアスタリスク(*)を使います。

SELECT * FROM users;

-- 1|Pole|23
-- 2|Anna|22
-- 3|Mary|19
-- 4|Bob|38
-- 5|Taro|33
-- 6|Tom|17
-- 7|Sara|44
-- 8|Mike|26

### WHERE

`WHERE`節を使うことで取得するデータに制約を付けることができます。例えば`users`テーブルの`age`属性が20以上のものだけ取得するときは以下のようになります。

SELECT * FROM users WHERE age >= 20;

-- 1|Pole|23
-- 2|Anna|22
-- 4|Bob|38
-- 5|Taro|33
-- 7|Sara|44
-- 8|Mike|26

また、条件は`AND`、`OR`を付けることで複雑な条件を与えることができます。

SELECT * FROM users WHERE age >= 20 AND age <= 30;

-- 1|Pole|23
-- 2|Anna|22
-- 8|Mike|26

LIKE句を使うことにより、パターンマッチをすることができます。

- `%` ... 任意の0文字以上の文字列
- `_` ... 任意の1文字

-- nameが、Tもしくは、Mから始まるレコード
SELECT * FROM users WHERE name LIKE  'T%' OR name LIKE 'M%';

-- 3|Mary|19
-- 5|Taro|33
-- 6|Tom|17
-- 8|Mike|26

- **注意**
SQLでは等号は`=`で、不等号は`<>`で表します。`==`や`!=`ではないので注意しましょう。

【演習】 `users` テーブルから`age`属性が`15以上かつ25以下`のデータの`name`属性を取得するSQL文を書きなさい。

### COUNT, MAX, MIN

`SELECT`節に`COUNT`を付けることで、データ自体ではなくデータの数を取得することができます。

SELECT COUNT(*) FROM users;

-- 8

同様に`MAX`で属性の最大値、`MIN`で最小値を取得できます。

SELECT MAX(age) FROM users;

-- 44

### SELECTのネストとIN

`SELECT`節はネストすることができます(副問合せ)。ネストすることによってより複雑な制約を付けたデータの取得が可能です。

`users`テーブルから、`exercises`テーブルの`score`属性が80以上のユーザの、`age`属性を取得するときは以下のようになります。

-- ネストした箇所のSQL
SELECT user_id FROM exercises
	WHERE score >= 80;
-- 1
-- 2
-- 4
-- 5
-- 1
-- 3
-- 7
-- 8
-- 9



SELECT id, age FROM users
WHERE id IN (
	SELECT user_id FROM exercises
	WHERE score >= 80
);

-- 1|23
-- 2|22
-- 3|19
-- 4|38
-- 5|33
-- 7|44
-- 8|26

`IN`は属性の値が含まれるかどうかを判定する条件です。()内の`SELECT`節で取得できるデータは複数あるので、その中に`id`が含まれていれば、その`id`を持つユーザは`score`が80以上のはずなので、そのユーザの`id`と`age`を取得する、という流れで見ていきます。

### 正規化

SQLの説明から一旦離れますが、DBの設計で正規化はなくてはならない考え方です。正規化にはその段階に分けて第一正規形から第五正規形までありますが、DBの設計で一般的にされる第一正規形から第三正規形までを簡単に説明します。

#### 非正規形

[^1]

このように、複数の列に同じ項目が繰り返し出てくる状態を非正規形といいます。この状態ではDBに格納することもできません。

#### 第一正規形


第一正規形ではまず、非正規形で繰り返し出てきた項目を各列ごとに分割します。こうすることでDBに格納することが可能になります。

また、別の属性値で導出可能な属性を削除します。入荷数から在庫数を導出できる場合にはこのように在庫数の属性を削除します。

#### 第二正規形


ある一つの属性や、複数の属性の組み合わせによって、また別の属性が特定できる状態を関数従属と言います。第一正規形の状態では、仕入先、商品名、入荷日によって他の全ての属性が関数従属します。このとき、仕入先、商品名、入荷日の組み合わせのことを**主キー**と言います。

テーブルの全ての属性が主キーに依存する状態を完全関数従属といいます。また、主キーの一部に関数従属することを部分関数従属をいい、部分関数従属する部分を別のテーブルに切り出した形が第二正規形です。

#### 第三正規形


このシステム上で一人の代表者が別の農場の代表者も兼任できる場合、第二正規形では正規化は不十分です。なぜなら、例えば松山花子さんがぶどう園の代表者も兼任した場合、代表者連絡先が不要に重複してしまいます。このように主キー以外の属性に依存した属性がテーブル内にある状態を、推移的関数従属といいます。このとき、代表者についてのテーブルを分けることでこの問題は解消されます。

第三正規形とは、テーブルから推移的関数従属性を除いて完全関数従属にした形のことです。

### JOIN

ここでもう一度テーブルスキーマを見てみましょう。

sqlite> .schema
CREATE TABLE users(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(32),
  age INTEGER
);
CREATE TABLE exercises (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  number INTEGER NOT NULL,
  user_id INTEGER REFERENCES users(id),
  score INTEGER NOT NULL
);

user一人に対して、exerciseの結果は複数存在するはずです。しかし、SQLiteでは配列などの複数の値を持つ機能は持ち合わせていません。そこでリレーショナルの出番です。
exercisesテーブルはuser_idを持っており、この属性によりusersテーブルとの関連を持っています。exercisesのデータを見ると、同じuser_idがいくつか並んでいるはずです。これらのテーブルをつなぎ合わせたい場合は`JOIN`節を使います。

sqlite> SELECT * FROM users JOIN exercises ON users.id = exercises.user_id WHERE users.id = 1;

-- 1|Pole|23|1|1|1|100
-- 1|Pole|23|9|2|1|90
-- 1|Pole|23|19|3|1|20

#### INNER JOIN

`JOIN`をするときに考えなければいけないことがあります。それは、指定した属性が、片方のテーブルに存在しないときの対応です。

例えば、先ほどの`users` `exercises`テーブルで、次のようなデータが格納されていたとします。

`users`
|id|name|age|
|:--:|:--:|:--:|
|1|Pole|25|
|2|Mary|42|
|3|Bob|18|

`exercises`
|id|number|user_id|score|
|:-:|:-:|:-:|:-:|
|1|1|1|70|
|2|2|1|80|
|3|1|2|90|

この時、同様に`users.id`と`exercise.user_id`で結合しようとしたのですが、`exercises.user_id`が`3`のデータが`exercises`テーブルにないので`users`テーブルのデータが一つ余ってしまいました。

この時にとる選択肢は二つあります。

- 余ったデータは結合後のテーブルには含めない
- 存在しない属性はNULLで埋めて、結合後のテーブルに含める。

この二つのうち、前者を`INNER JOIN` 後者を`OUTER JOIN`と言います。

ベン図で考えてみましょう。今、`users.id`と`exercises.user_id`はこのような関係になっています。


このベン図を見ると、`exercises.user_id`が内側 `users.id`が外側になっています。内側に合わせて`1`と`2`のみのテーブルに結合するのが`INNER JOIN`で、外側に合わせて`3`も含めたテーブルに結合するのが`OUTER JOIN`ということになります。

SELECT * FROM users JOIN exercises ON users.id = exercises.user_id;

-- 1|Pole|23|1|1|1|100
-- 2|Anna|22|2|1|2|90
-- 3|Mary|19|3|1|3|50
-- 4|Bob|38|4|1|4|90
-- 省略(以下15列)

SQLでは`INNER`または`OUTER`の指定を省略した場合には`INNER JOIN`と解釈されます。省略しない場合は以下のように書きます。

SELECT * FROM users INNER JOIN exercises ON users.id = exercises.user_id;

#### LEFT JOIN

さて、`INNER JOIN`と`OUTER JOIN`によって`JOIN`で起こる問題は解決できました。しかし、`OUTER JOIN`の中でも考えるべき点が存在します。

また、先ほどの例で見てみましょう。

`users`
|id|name|age|
|:--:|:--:|:--:|
|1|Pole|25|
|2|Mary|42|
|3|Bob|18|

`exercises`
|id|number|user_id|score|
|:-:|:-:|:-:|:-:|
|1|1|1|70|
|2|2|1|80|
|3|1|2|90|
|4|1|0|60|

`exercises`テーブルに一つデータが追加されました。このシステムでは`user_id`が0の時に特別なユーザとして扱うというルールを決めているようです。

では、`users.id`と`exercises.user_id`の関係をベン図で見てみましょう。


この関係の上で`OUTER JOIN`をしたい、という時にとる選択肢は三つあります。

- `users.id`をベースに`3`を含め`0`を含めない
- `exercises.user_id`をベースに`0`を含め`3`を含めない
- `0`も`3`も含める

この三つの選択肢はSQL上で表現することができます。

SELECT * FROM users LEFT JOIN exercises ON users.id = exercises.user_id;

SELECT * FROM users RIGHT JOIN exercises ON users.id = exercises.user_id;

SELECT * FROM users FULL OUTER JOIN exercises ON users.id = exercises.user_id;

`LEFT JOIN`はSQLの構文上で左側(users)をベースにした`OUTER JOIN`、`RIGHT JOIN`はその逆です。

また、`FULL OUTER JOIN`は左側、右側の両方を含んだ`OUTER JOIN`のことです。

ただ、SQLite3では`RIGHT JOIN`と`FULL OUTER JOIN`は使うことができないので、`OUTER JOIN`を行う場合は、テーブルの順番に注意して`LEFT JOIN`で結合します。

SELECT * FROM exercises LEFT JOIN users ON exercises.user_id = users.id WHERE exercises.number = 3;

-- 17|3|3|50|3|Mary|19
-- 18|3|9|80|||
-- 19|3|1|20|1|Pole|23

### INSERT

`INSERT`節はテーブルにデータを挿入するための構文です。

INSERT users(id, name) VALUES(29891, 'Tom');

このようにテーブルの属性と値を対応付けてデータを格納します。ここで値を設定していない属性には`NULL`が入ります。

### DELETE

`DELETE`節はテーブルのデータを削除するための構文です。

DELETE FROM users WHERE id = 1;
SELECT * FROM users;

-- 2|Anna|22
-- 3|Mary|19
-- 4|Bob|38
-- 5|Taro|33
-- 6|Tom|17
-- 7|Sara|44
-- 8|Mike|26

`WHERE`節で指定した条件に一致するデータを削除します。

### その他のDML

- GROUP BY
ある属性でデータをグループ化する。

- ORDER BY
取得するデータをソートする。

- LIMIT
取得するデータの数を制限する。

これ以外にもたくさんあるので、必要な時に調べてみましょう。

## DCL

Data Control Language(DCL)はユーザのデータへのアクセス権を操作したり、トランザクションという一連のクエリーの流れを確定したり破棄するための言語です。

SQLite３にはユーザの概念がなく、また今回はトランザクションは扱わないのでこの資料ではDCLについて説明しません。

## 演習

演習では、bank.sqlを使い問題を解いてもらいます。

### 演習問題準備

[ここから](https://gist.github.com/misoton665/1fde951fbffc34f7afc113837b078380)サンプルのSQLファイルを作業ディレクトリにダウンロードしてください。

初めに、DBファイルを作成して、ダウンロードしたSQLを実行します。

$ sqlite3 sample.sqlite3 < bank.sql

DBに書き込んだデータを初期化するには以下のようにします。

$ echo "" > sample.sqlite3

これ以降のSQLはbank.sqlを実行した状態のsample.sqlite3を読み込んだREPL上で実行するものとします。

#### bank.sqlについて

bank.sqlは銀行口座を簡単にRDBにモデル化したものです。bank.sqlには三つのテーブルが定義されています。

- ***customers*** (利用者)
- id: 利用者ID
- name: 利用者名
- ***accounts*** (銀行口座)
- id: 口座ID
- customer_id: 口座の利用者ID
- code: 口座番号
- ***deposits*** (借り入れ履歴)
- id: 借り入れ履歴ID
- account_id: 対象の口座番号
- ammount: 金額
- ***withdrawals*** (引き出し履歴)
- id: 引き出し履歴ID
- account_id: 対象の口座番号
- ammount: 金額


### 演習問題

【演習1】 customers(利用者)の名前の一覧を取得しなさい。

【演習2】 借り入れの最高金額と引き出しの最低金額を求めなさい。

【演習3】 名前の末尾が`n`である利用者の引き出し履歴の数を数えなさい。

【演習4】 新しい利用者を増やし、その借り入れと引き出し履歴もいくつか追加せよ。

【演習5】 演習4で追加したデータを削除せよ。

【演習6】 Jhonの預金履歴を取得しなさい。

【演習7】 一度に10万円以上引き出されたことのある口座の口座番号を取得しなさい。

[^1]: 画像は[http://www.oss-db.jp/measures/dojo_info_04.shtml](http://www.oss-db.jp/measures/dojo_info_04.shtml)から引用