- 追加された行はこの色です。
- 削除された行はこの色です。
#CONTENTS
// 第 14 回 -- データベース入門 (SQLite)
* データの取り扱いについて [#d7836d66]
ある程度大規模だったり複雑だったりするデータを処理することはコンピュータではよくあることだ．
よくあることであるので，こうしたデータを「どのように保存するか」「保存したデータを
どのように取り出すか」という視点できちんと捉えて一定の規格にのっとってこれらを共通化することで，典型的な作業についてはプログラマがいちいち細かいことを考えないで済ませることができるようになる((そもそも論で言えば OS が提供する機能はある程度こうしたものばかりだが．))．
こうした考え方が一体の形をとったものが以下に述べるデータベースソフトウェアなどと総称されるもの，と思えば良い．
* データベース(ソフトウェア)とは [#daed841d]
大雑把に言えば，収集されたデータの集合体そのものを指す.
または，データを保存すること，また，一定のルールにそって蓄積したデータの中から必要なデータを抽出すること，等のデータ収集かつ抽出の機能を持ったプログラムやライブラリをデータベースソフトウェア，データベースライブラリなどと呼び，その略称としてデータベースと呼ぶ．
データベースソフトウェアの強みはまずはその汎用性，つまり，操作などが共通であることにある．
また，データを取り扱うことに特化していることにより，操作によるデータ破壊やデータ損失に備えた機構をもたせていたり，メモリやストレージの利用効率が高くなっていたり，検索などの操作が高速であったりと，その高機能性も利点である．
ただ，こうした高機能性はソフトウェアの肥大化，煩雑さと強い相関があり，ライトユーザーからみるとかえって使いにくい，わかりにくいという欠点にも繋がる．
&br;&br;
&ref(/materials/warning.png);
よって，「そのデータ構造にのみ」特化した賢いプログラムの方が
汎用データベースソフトウェアよりも優秀であることは充分にありうる．
対象とするデータの取扱にそもそもデータベースソフトウェアを使うべきかどうか，
まずは最初に検討したほうがいいだろう．
** データベースソフトウェアの種類 [#o8fae358]
非常に大雑把に分けるのならば，簡易なものから非常に大規模なものまで以下のような3つに分類できるだろう．
- 表計算ソフト，単なるテキストファイル+Unixコマンド群 等々: 非常に広義に捉えるならばこれらはもっとも簡易なデータベースソフトウェアといえるだろう．
- RDBMS(リレーショナルデータベース管理システム): データベースソフトウェアの主流と言えそうなのがこれである．データの操作には主に SQL という言語が使われる．
ソフトウェアとしては簡易なものはライブラリ，コマンドとして実装され，本格的なものはサーバとして実装される．
- 構造型ストレージ(NoSQL): 超大規模なデータや，リアルタイム性を重要視するような，
RDB(リレーショナルデータベース)では扱いにくいデータを取り扱う．
操作が SQL でまかなえないということでもあるので，NoSQL (Not Only SQL) とも呼ばれる．
** データの規模とデータベースソフトウェアの関係 [#rf02e086]
*** データが単純，もしくは規模が小さい場合 [#ga67fc80]
全データがコンソール画面 1枚に入りきるケースなど，データ規模が小さい場合は，データベースを導入する手間やデータを処理するソフト本体とデータの間にデータベースソフトウェアが挟まる分だけ，かえってコスト高になりかねない．
そうした場合は無理にデータベースソフトウェアを導入しなくても良いだろう((導入してももちろん問題はないけれども))．
こうした場合は単なるテキストファイルにデータを納めておくとか，いわゆる表計算ソフトウェアなどを利用した方が無理がなさそうだ．
また，例えば単純な観測データが数字で並んでいるだけ，というようにデータが単純な場合はそれに特化した処理系を用意したほうが見通しもよく，処理速度や記憶容量の圧縮性などの面で多くの利点があるだろう．
数値解析，数値計算などの場合はこうしたケースが多そうだ．
*** データがある程度複雑，もしくは規模が少し大きい場合 [#ic9b9f5d]
現実的な想定としては住所録やソフトウェアの様々な設定保存などがこのあたりだろう．
これぐらいの規模から，データベースの利用を検討する価値がでてくる．
ただし，複雑さや規模がそれほどでもないこの段階では，
- SQLite などの，RDBMS のなかでもサーバシステムではないような軽量データベースソフトウェア．
- 単なる整理したテキストファイル + 小さなシェルスクリプトの組み合わせ
~
ぐらいが(人的投入コストの面からも)適切と考えられる．
*** データが複雑，もしくは大規模な場合 [#k52591a1]
もう少しデータが複雑，もしくは大規模なケースはこちらになる．
具体的には，会社の各種業務用データやオンラインショッピングサイトのデータ処理など
が想定対象だろうか．
こうした規模では主に RDBMS が使われている．
ただし，近年ではコンピュータの性能向上により，単なるテキストファイル+シェルスクリプト でこうした用途も充分まかなえるとする動きもある．
*** データが超複雑，もしくは超大規模な場合 [#e70cdd8e]
データが非常に複雑だったりその構造が解析前は不明だったり，もしくは規模が
サーバ 1台に収まらないぐらい大規模だったりする場合，RDB は不向きである．
こうした場合は構造型ストレージ(NoSQL)サーバが使われる．
* SQLite [#oca092d3]
本授業で用いる SQLite とは，RDBMS のなかでも最も軽量で広く使われている((有名どころだと，firefox や android で使われている．))ソフトウェアで(世界で一番広く使われている SQL エンジンだと主張している)，各種言語用ライブラリやコマンドとして実装される．
サーバではないため複雑なインストール作業が不要で，すぐ使えるという利点がある．
また，データベース一つにつきファイルを一つ作る方式のため，バックアップなどが取り易いのも便利な点である．
データベースの操作は SQL で行うため，他の RDBMS とあまり変わらない操作が行える．
ただし，データの「型」設定を要求しないという融通さを持つので，簡単に使える．
主に使われるバージョンは 2系統と 3系統であるが，ここでは 3系統(ver.3)を用いる．
阪大の教育計算機システム上では、cygwin, linux(vmware) の両方に sqlite3 コマンドがインストールされているので、コマンドラインからいつでも sqlite3 を使うことが出来る．
** 資料 [#sad25d1b]
- [[&ref(/materials/JNorth_arrow-right-sm.png); SQLite:http://www.sqlite.org/]]:&br;
SQLite の本家 web である．
SQLite をダウンロードすることもできるが，cygwin や Linux ではシステムがもつアプリケーション管理システムでインストールしたほうがいいだろう．
&br;&br;
- SQLite 入門 第2版: 著 西沢 直木, 翔泳社(2009).&br;
上の本家 web でも紹介されている書籍である．題目の通り，入門にはまさにぴったりの本と思われる．
ただし，入門者には難解かと思われる内容は載っていない(とくにリレーショナルデータベースとしての扱い方について)ので，中級者以上は他の書籍なども資料として用意しておくと良いだろう．
&br;&br;
- ”SQLite 入門” というキーワードで検索して出てくるような web.&br;
丁寧に書かれた分かりやすいものもあり，大変為になるので見ておくと良いだろう．
** SQLite の操作 [#q8013323]
&ref(./how-to-treat-db-via-sqlite.png,,50%);&br;
CENTER:SQLite はライブラリ、もしくはコマンドからデータベースを操作する. データベースの操作を一括管理するサーバがあるわけではない．
&br;&br;
他の著名な RDBMS はソフトウェアとしてはサーバ形式をとることが多い.
これは、ネットワーク越しにデータベースを扱うためにはこの方が好都合だからで、
ある程度大きな規模のデータベースでは(利用者が複数になることもあり)必要な形式といえる。
しかし、サーバをインストール、稼働させるためにはシステムの管理者権限が必要だったり、特殊なアカウントを用意したりせねばならず、利用するための労力は小さくない.
これに対し、SQLite はサーバ形式ではなく、単なるコマンド、もしくはライブラリからデータベースを操作するという単純な仕組みである.
本授業では、もっとも簡単なコマンドからの操作を持って学ぶものとする.
** SQLite で用いる言語 [#n417d1a5]
&ref(./SQLite-commands.png,,70%);&br;
CENTER:SQLite で用いる言語には二種類ある
&br;&br;
SQLite を使っての操作には、以下の二種類があり、それぞれについて異なる言語(コマンド)を用いる．
&br;&br;
- SQLite そのものの操作: 各種設定の変更、表示など．
''SQLite コマンド'' と呼ばれるコマンドを用いる．
SQLite コマンドは .(ピリオド)で始まる単語であることが特徴である．
&br;&br;
- データベース処理: データベースへの操作．テーブルの作成、データの登録、データの抽出など、本来のやりたいこと．
これには ''SQL コマンド'' を用いる．
SQL コマンドはあるていど規格化されており((正確には、たびたび規格化されているが、各種データベースの「方言」がまだいろいろあるという状態．))、標準的な機能を使うのであれば他のデータベースソフトウェアと同様に扱える．
* SQLite におけるデータの取扱い [#f78ae92f]
&ref(./database-table-data-diagram.png,,70%);&br;
CENTER:SQLite でのデータの取扱い. 1つのデータベースの中に、表(テーブル=データのリスト)が幾つか含まれる．
&br;&br;
通常は、RDBMS では一つのデータベースの中に，テーブル(表)を複数作り，
そのテーブルの中に一行ずつ決まった形式でデータを入れる(さらに、こうしたデータベースを複数作る)．
テーブルは注目するデータの分類ごと(例えば顧客リストを並べて作る顧客テーブル，商品リストを並べる商品テーブル，そして誰が何を買ったかという購買リストを並べた購買テーブルなど)に作ることが想定されており，それらを関係付ける((別のデータベースのテーブルと関係づけることも出来る))ことで全体の情報がまとまることになる．
このように，リストアップできる情報をテーブルとして並べ，それらの関係をまたテーブルにする，というように［関係性］で全体の情報を表すのが大雑把に言えばリレーショナルDB である．
なお、SQLite では一つのデータベースを一つのファイルに入れるので分かり易いし、バックアップも取りやすい．
&br;&br;
* 実習を通じて学ぶ [#z1c1380f]
この授業では、売上を管理するデータベースを想定してみる．
具体的には、一つのデータベースの中に 商品情報のテーブル、顧客情報のテーブル、売上記録のテーブルを作ってみる．
そのつもりで以下を読み進めてみよう．
&br;&br;
* SQLite3 の起動、終了 [#z1c1380f]
** コマンドから SQLite を起動する = データベースへの接続 or データベースの作成 [#yb4ec40d]
SQLite のコマンドは、引数に文字列を与えるとその名前のデータベース(ファイル)に接続しようとする。
もしそのデータベースがなければ新規にデータベースが作られる。
たとえば ''db1'' という名前のデータベースを作るとすると、
> ''sqlite3 db1''
> ''sqlite3␣db1''
<
と入力すればよい.
sqlite (ver.3) が起動してコマンド受付モードになる(プロンプトが変わるので気づくだろう).
sqlite (ver.3) が起動してコマンド受付モードになる(プロンプトが sqlite> に変わるので気づくだろう).
&ref(/materials/notes.png); (実習) 上のようにして SQLite を起動しよう．
起動したらそのままにして、次の実習まで先へ読み進めよう．
** SQLite の終了 [#w1241a34]
起動した SQLite を終了するには、
> ''.exit''
<
もしくは
> ''.quit''
<
でよい。
それぞれ一文字目がピリオドであることに注意しよう。
&br;&br;
* SQLite3 の設定 [#i03fe0dc]
SQLite を起動してから、まずは
> ''.show''
<
としてみよう．
現在の設定状況が以下のように表示される(もちろん、内容は設定により異なる)．
echo: off
eqp: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:
見て推測できるように、いろいろ設定はあるが、まずは以下の設定だけでもしておくと何かとわかり易くなるので, 手でデータベースにアクセスするときはこうしておこう．
> ''.explain␣on''
<
これは、画面出力をわかりやすくする設定であり、対話的に操作しているときはなにかと助かるだろう．
&br;
&ref(/materials/notes.png); (実習) ''.show'' コマンドを試しに実行したのち、''.explain␣on'' 設定をしておこう．
&br;&br;
* SQLite3 でのテーブル「単独」操作 [#z20c4d9b]
** テーブルを作る [#p0947f21]
リレーショナルデータベースの中身の本質はデータを列挙した塊であるテーブルである。
ただし、一つのテーブルの中に列挙されるデータはすべて同じ構造をしているものとする必要がある。
その構造は、テーブル作成時に定義してやる必要がある。
SQLite でのテーブルの作成時におけるデータの定義は簡単で、以下のような書き方をすればよい。
> CREATE TABLE テーブル名 (変数名1 データの型, 変数名2 データの型, 変数名3 データの型, ...);
SQLite でのテーブルの作成時におけるデータの定義は簡単だ．
しかもデータの型の定義を省略することもできるので、実用的なやり方としては(慣れていないうちは)次のようにしておくと良いだろう．
> ''CREATE␣TABLE␣テーブル名␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, 変数名2, 変数名3, ...);''
<
&br;
&ref(/materials/warning.png); SQLite3 は ; (セミコロン)を入力するまでは命令の解釈を待つので、長いコマンドを
全部 1 行で入力する必要はない．人間にとって分かり易いところで改行して入力を続けるのは良い手である．
&br;
&ref(/materials/warning.png); 慣習として SQL 命令は大文字で表記されるが、SQLite3 は小文字でも問題なく受け付ける．
対話的に入力するときは面倒だろうから、気にせずに小文字のままで入力してしまうのが簡単だろう．
&br;
&ref(/materials/warning.png); ここでは 変数名1 を id と決めてしまっているが、実は自由に決めて良い．
ただし、慣れていないうちはこうしておくとなにかと後で助かるだろう．
まずはテーブルを一つ作ってみよう。
&br;
&ref(/materials/warning.png); id の後の ''INTEGER PRIMARY KEY AUTOINCREMENT'' の意味は、
id という変数を「整数」で「これを主キーとして」「データ削除後の再利用なし」にするという意味である．
主キーというのは、大雑把に言えば、その表の中でこの変数に対するデータは唯一であることが保証される変数である、という意味である．
&br;
&ref(/materials/notes.png); (実習) 次のようにして、まずは商品情報のテーブルとして fruits テーブルを作っておこう．
> ''CREATE␣TABLE␣fruits␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, name, price);''
<
上の注意で書いたように、一行で一気に入力する必要はない．最後の ; (セミコロン) を打つまでは命令は実行されない．
&br;&br;
** テーブルを削除する [#cb7d8d6f]
作ったテーブルを削除するのは簡単で、
> ''DROP␣TABLE␣テーブル名;''
<
だけで良い．
&br;
&ref(/materials/warning.png); この簡単な操作であっさり大量のデータを失うことがあるので、要注意！！
&br;&br;
** データベースにどんなテーブルが有るか見てみる [#g68cef65]
一つのデータベースには複数のテーブルを格納できる．
そこで、データベース中にどんなテーブルがあるか調べる方法を書いておこう．
これは .tables 命令(SQLite 命令)を使って
> ''.tables''
<
とするだけでよい．
&br;
&ref(/materials/notes.png); (実習) 上の ''.tables'' コマンドを用いて、確かに fruits テーブルが作られていることを確認しよう．
&br;&br;
** テーブルにデータを追加する [#h1632d29]
さて、テーブルが既に存在するならばそこにデータを追加できる．
データの追加は INSERT 命令を使って以下のように行う．
> ''INSERT␣INTO␣テーブル名␣VALUES(NULL, 変数2の内容, 変数3の内容, ...);''
<
&br;
&ref(/materials/warning.png); こうすると、変数 id は連番で自動的に数字が入力される.
&br;
&ref(/materials/notes.png); (実習) 次のようにして、 fruits テーブルに商品データを登録しよう．
> ''INSERT␣INTO␣fruits␣VALUES(NULL, "banana", 100);''
> ''INSERT␣INTO␣fruits␣VALUES(NULL, "strawberry", 200);''
> ''INSERT␣INTO␣fruits␣VALUES(NULL, "apple", 150);''
> ''INSERT␣INTO␣fruits␣VALUES(NULL, "water melon", 1500);''
<
&br;
&ref(/materials/warning.png); もちろん、他のテーブルやデータベースからデータをコピーしてくる方法や、他の普通のファイルからデータをコピーしてくる方法なども有る．
必要になったら調べよう．
&br;&br;
** テーブルのデータを表示する, 取り出す [#o2002056]
テーブルに入っているデータをまずは見てみたいという場合は
SELECT 命令を使って以下のようにする．
> ''SELECT␣*␣FROM␣テーブル名;''
<
&br;
&ref(/materials/warning.png); 上の設定で ''.explain on'' としているとこの表示が少しだけ分かり易いようになる．
&br;
&ref(/materials/notes.png); (実習) 上の ''SELECT'' コマンドを用いて、fruits テーブルにデータが登録されて
いることを確認しよう．
うまくいっていれば、次のように表記されるはずだ．
id name pric
---- ------------- ----
1 banana 100
2 strawberry 200
3 apple 150
4 water melon 1500
&br;
さらに、条件を指定してデータを見てみたいという場合は、
SELECT 命令に WHERE 句を追加して以下のようにする．
> ''SELECT␣*␣FROM␣テーブル名␣WHERE␣条件;''
<
WHERE のあとに続く「条件」はいろいろと柔軟に指定できる．
ここで説明するには多すぎるので、以下の二例をみてまずは感覚をつかもう．
&br;
&ref(/materials/notes.png); (実習) 次の ''SELECT'' コマンドを用いて、fruits テーブル中で単価が 1000円以上の品をリストアップしてみよう．
> ''SELECT␣*␣FROM␣fruits␣WHERE␣price>1000;''
<
うまくいっていれば、次のように表記されるはずだ．
id name pric
---- ------------- ----
4 water melon 1500
&br;
さらに今度は、fruits テーブル中で名前が "ほにゃららnana" というものをリストアップしてみよう．
> ''SELECT␣*␣FROM␣fruits␣WHERE␣LIKE('%nana',name);''
<
うまくいっていれば、次のように表記されるはずだ．
id name pric
---- ------------- ----
1 banana 100
&ref(/materials/warning.png); LIKE 中の % は「任意の文字列」という意味を持つ．詳しくは調べよう．
&br;
さらに、項目すべてではなく、一部の項目だけ見たいという場合は SELECT 命令のあとに * ではなく、項目名を列挙する．
> ''SELECT␣見たい項目名1, 見たい項目名2, ... ␣FROM␣テーブル名␣WHERE␣条件;''
<
&br;
&ref(/materials/notes.png); (実習) 次の ''SELECT'' コマンドを用いて、fruits テーブル中の単価「のみ」リストアップしてみよう．
> ''SELECT␣price␣FROM␣fruits;''
<
うまくいっていれば、次のように表記されるはずだ．
pric
----
100
200
150
1500
&br;&br;
** テーブルのデータを修正する [#pefef0aa]
テーブルに格納されているデータを修正するには、REPLACE 命令と UPDATE 命令が使える．
都合に合わせて好きな方を使おう．
&br;
まず REPLACE は、次のように使う．
> ''REPLACE␣INTO␣テーブル名␣VALUES(id番号, 変数2の内容, 変数3の内容, ...);''
<
&ref(/materials/warning.png); REPLACE 命令を使った時、その id番号 をもつデータがない場合、そのデータはその id番号で新規に「登録」される．
その意図がない場合は間違いのもとになるので留意しよう．
&br;
次に、UPDATE の使い方だが、次のようにする．
> ''UPDATE␣テーブル名␣SET␣変数名n=変数nの内容␣WHERE␣条件;''
<
&ref(/materials/warning.png); WHERE 以降を省略すると、データ全部が対象となる．
&br;
&ref(/materials/notes.png); (実習) 次のように UPDATE コマンドを用いて、fruits テーブルのデータの中の、water melon の価格を 2000 に修正しよう．
> ''UPDATE␣fruits␣SET␣price=2000␣WHERE␣id=4;''
<
うまくいっていれば、SELECT コマンドで次のように表記されるはずだ．
id name pric
---- ------------- ----
1 banana 100
2 strawberry 200
3 apple 150
4 water melon 2000
&br;
&ref(/materials/notes.png); (実習:応用) さらに次のように UPDATE コマンドを用いて、
単価が 300円未満の商品を倍に値上げしよう．
> ''UPDATE␣fruits␣SET␣price=2*price␣WHERE␣price<300;''
<
うまくいっていれば、SELECT コマンドで次のように表記されるはずだ．
id name pric
---- ------------- ----
1 banana 200
2 strawberry 400
3 apple 300
4 water melon 2000
&br;&br;
** テーブルのデータを削除する [#d8799e0c]
テーブル中のデータを削除するには、UPDATE コマンドに似た DELETE コマンドが使える．
> ''DELETE␣FROM␣テーブル名␣WHERE␣条件;''
<
&ref(/materials/warning.png); これも WHERE 以降を省略すると、データ全部が対象となる．
つまり、''データが全部消えてしまう!'' ので、間違えてそう操作しないよう気をつけよう．
&br;
&ref(/materials/notes.png); (実習) 次のように DELETE コマンドを用いて、fruits テーブルのデータの中の、
strawberry についての情報を削除しよう．
> ''DELETE␣FROM␣fruits␣WHERE␣id=2;''
<
うまくいっていれば、SELECT コマンドで次のように表記されるはずだ．
id name pric
---- ------------- ----
1 banana 200
3 apple 300
4 water melon 2000
&ref(/materials/warning.png); 一度使われた id 番号 2 が欠番になっていることに注意しよう．
&br;
&ref(/materials/notes.png); (実習) 次のように INSERT コマンドを用いてデータを追加し、
欠番 id がどのように扱われるか把握しよう．
> ''INSERT␣INTO␣fruits␣VALUES(NULL, "orange", 400);''
<
うまくいっていれば、SELECT コマンドで次のように表記されるはずだ．
id name pric
---- ------------- ----
1 banana 200
3 apple 300
4 water melon 2000
5 orange 400
&ref(/materials/warning.png); 自動連番機能を用いると id 番号 2 が欠番のままになっていることに注意しよう．
&br;&br;
* SQLite3 でのテーブルの「相互作用を考慮した」操作 [#h4fbcf95]
データベースでのテーブル相互作用の基本は、何かの項目を共通キーとしてテーブルを合体させることである．
難しくはないがやってみないと実感しにくいので、実習を通じて学ぶとしよう．
** 準備 [#o3638f59]
実習を通じて学ぶために、少し準備をしよう．
具体的には、もう二つ、他のテーブルを同じデータベースの中に作成しよう．
&br;
&ref(/materials/notes.png); (実習) 次のように CREATE コマンドと INSERT コマンドを用いて、今のデータベースの中にもう2つのテーブルを作っておこう．
&br;
まず一つ目は、顧客リストを並べた customers テーブルである．
顧客として 3人ほどデータを入れておこう．
> ''CREATE␣TABLE␣customers␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, name);''
> ''INSERT␣INTO␣customers␣VALUES(NULL,"john");''
> ''INSERT␣INTO␣customers␣VALUES(NULL,"tom");''
> ''INSERT␣INTO␣customers␣VALUES(NULL,"mike");''
<
SELECT 命令で中身をみると次のようになっているはずだ．
id name
---- -------------
1 john
2 tom
3 mike
&br;
二つ目は、売上リストを並べた sales テーブルである．
売上リストは、&br;
''id, 顧客id, 商品id, 売れた個数'' &br;
としておこう．
> ''CREATE␣TABLE␣sales␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, customer_id, fruit_id, quantity);''
<
さて、5 回の売上げ情報を入れておこう．
> ''INSERT␣INTO␣sales␣VALUES(NULL,2,5,3);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,1,1,1);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,2,3,2);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,2,1,4);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,3,4,10);''
<
SELECT 命令で sales テーブルの中身をみると次のようになっているはずだ．
id customer_id frui quan
---- ------------- ---- ----
1 2 5 3
2 1 1 1
3 2 3 2
4 2 1 4
5 3 4 10
&br;
&ref(/materials/warning.png); この sales テーブルは他のテーブルの情報があって初めて意味があることに注意しよう．
** 共通情報をもとにテーブルを合体させる [#cdf33265]
これでこのデータベースには計 3つのテーブルが有るはずである．
一応、''.tables'' コマンドで
> ''.tables''
<
とすると、
customers fruits sales
と出力されることを確認しよう．
この 3つのテーブルのうち他のテーブルの情報を引用する形になっているのは sales テーブルであるので、
sales テーブルに他のテーブルの情報を合体(join)させてみよう．
具体的には、SELECT と一緒に''JOIN'' 命令を使って、合体させたテーブルから情報を取り出す形をとる．
文法としては次のような感じだ．
> ''SELECT␣*␣FROM␣テーブル名1''
> ''␣JOIN␣テーブル名2␣ON␣テーブル名1.項目名=テーブル名2.項目名;''
<
最後の = で繋がれている二つの項目が、このテーブル間で共通の情報である．
まあ、次の実習をやってみたほうがわかりやすいだろう．
&br;
&ref(/materials/notes.png); (実習) 次のように, sales テーブルの売上情報で、顧客の名前が見えるようにしてみよう．
> ''SELECT␣*␣FROM␣sales''
> ''␣JOIN␣customers␣ON␣sales.customer_id=customers.id;''
<
すると、次のような感じでデータが得られるはずである．
id customer_id fruit_id quantity id name
---- ------------- ------------- ------------- ---- -------------
1 2 5 3 2 tom
2 1 1 1 1 john
3 2 3 2 2 tom
4 2 1 4 2 tom
5 3 4 10 3 mike
全体の、第2項目(sales テーブルの customer_id) と第5項目(customers テーブルの id)が一致していることを確認しておこう．
&br;
これでは見にくい、というようであれば出力を見やすいものだけにすればよい．
例えば次のような感じだ．
> ''SELECT␣sales.id, name, fruit_id, quantity''
> ''␣FROM␣sales''
> ''␣JOIN␣customers␣ON␣sales.customer_id=customers.id;''
<
次のような出力が得られるはずだ．
id name fruit_id quantity
---- ------------- ------------- -------------
1 tom 5 3
2 john 1 1
3 tom 3 2
4 tom 1 4
5 mike 4 10
&ref(/materials/warning.png); テーブルを合体させると、同じ項目名が使われていることがある．
これを曖昧に記述して命令するとエラーになるので、そういう時は ''テーブル名.項目名'' として指定して、区別をする．
上の例でも SELECT の対象で sales.id として区別しているのに注意しよう．
&br;
*** JOIN はいくつでもいける! [#zc5e05c3]
JOIN はいくつでもテーブルを合体させられる．
単に後ろに続けて記述すればいいだけである．
次の実習で確かめよう．
&br;
&ref(/materials/notes.png); (実習) sales テーブルの売上情報で、顧客の名前だけでなく、商品の名前や単価も見えるようにしよう．
具体的には先の例にさらにもう一回 join をすれば良い．
> ''SELECT␣*␣FROM␣sales''
> ''␣JOIN␣customers␣ON␣sales.customer_id=customers.id''
> ''␣JOIN␣fruits␣ON␣sales.fruit_id=fruits.id;''
<
すると、(横に長いが)次のような出力を得るはずだ．
id customer_id fruit_id quantity id name id name price
---- ------------- ------------- ------------- ---- ------------- -- ------------- ----------
1 2 5 3 2 tom 5 orange 400
2 1 1 1 1 john 1 banana 200
3 2 3 2 2 tom 3 apple 300
4 2 1 4 2 tom 1 banana 200
5 3 4 10 3 mike 4 water melon 2000
&br;
わかりやすくするならば、次のような感じか．
> ''SELECT␣sales.id, customers.name, fruits.name, price, quantity''
> ''␣FROM␣sales''
> ''␣JOIN␣customers␣ON␣sales.customer_id=customers.id''
> ''␣JOIN␣fruits␣ON␣sales.fruit_id=fruits.id;''
<
すると、次のような分かり易い出力を得るだろう．
id name name price quan
---- ------------- ------------- ------------- ----
1 tom orange 400 3
2 john banana 200 1
3 tom apple 300 2
4 tom banana 200 4
5 mike water melon 2000 10
これをみれば、例えば第4回目の取引きでは tom が200円のバナナを4本買っていったことがすぐわかる．
&br;
&ref(/materials/warning.png); こんな面倒なことをしないで、上の表そのものを用意して、データを足していけばいいんじゃないかと思う人もいるだろう．
しかしそれをやると、途中で何かを変更すると過去のデータとの一貫性を失ってしまったり、
ある欄では tom と書き他の欄では ton と記載ミスすると別人扱いになってしまったりするのだ．
商品情報は商品テーブル単独で、顧客情報は顧客テーブル単独で管理でき、かつ、それらの情報を
他のテーブルから参照できる、というのが RDB のポイントなのである．
これはデータの管理の「安全性」を意味する．
データが多く、複雑になればなるほどこのことの重要性は増すだろう．
&br;
さて、さらにデータを見るだけではなくて、すこし加工してみよう．
> ''SELECT␣sales.id, customers.name, fruits.name, price, quantity, &color(red){price*quantity};''
> ''␣FROM␣sales''
> ''␣JOIN␣customers␣ON␣sales.customer_id=customers.id''
> ''␣JOIN␣fruits␣ON␣sales.fruit_id=fruits.id;''
<
すると次のように、単価x個数=小計 もリストアップできる．
id name name price quan price*quantit
---- ------------- ------------- ------------- ---- -------------
1 tom orange 400 3 1200
2 john banana 200 1 200
3 tom apple 300 2 600
4 tom banana 200 4 800
5 mike water melon 2000 10 20000
&br;
(応用) さて、さらに、誰にいくら請求するべきかも合計を見てみよう．
顧客情報をもとにまとめるわけだが、これには ''GROUP BY'' という命令を使う(詳しくは各人で調べよう)．
なお、合計には単なる ''SUM'' という命令が使える．
> ''SELECT␣customers.name, &color(red){SUM(price*quantity)};''
> ''␣FROM␣sales''
> ''␣JOIN␣customers␣ON␣sales.customer_id=customers.id''
> ''␣JOIN␣fruits␣ON␣sales.fruit_id=fruits.id''
> ''␣&color(red){GROUP␣BY};␣customers.id;''
<
すると、次のような出力を得ることができる．
name sum(price*qua
---- -------------
john 200
tom 2600
mike 20000
これで、だれに合計でいくら支払ってもらえばよいか、一目瞭然である．
&br;&br;
* 最後に [#kff011aa]
今回は SQLite をコマンドラインから直接操作して RDBMS の入門的な部分を理解できるような内容とした．
RDB を実用に供する場合は，ライブラリ経由かサーバ経由となるため，さらに理解しないといけないことが増えるが，基本は同じである．
そういった際によくわからなくなったら，今回のように直接コマンドラインから該当する操作をしてみて，実際に何が起きるか確認してみるのも良いだろう．
&br;&br;
* Report: レポート [#zd897f9f]
以下の課題について能う限り賢明な調査と考察を行い，
&br;
''AppliedMath7-Report-14''
&br;
という題名をつけて e-mail にて教官宛にレポートとして提出せよ. なお，レポートを e-mail の代わりに TeX で作成した書面にて提出してもよい.
** Exercises: 課題 [#vf0c8a45]
+ aaa
&br;
&br;
+ 上の実習を続けて、さらに、どの商品が合計でいくつ売れたか、を出力させてみよ．
&br;&br;
+ SQLite で、ファイルからデータを読み込む方法を調べよ．
&br;&br;
+ SQLite のデータベースを GUI で操作できるアプリケーションが多数存在する．
そういった物を探し，そのうちの一つで良いので実際に操作して試してみよ．
&br;&br;
+ 自分の生活の中で SQLite を利用すると便利になりそうなシーンがないか、考えてみよ．
&br;&br;
* about Icons, ClipArts [#c5b11aa1]
For details, see [[&ref(/materials/JNorth_arrow-right-sm.png); this>materials]].
// ━┃┏┓┛┗┣┳┫┻╋
// コマンドライン入力は「行頭を > で始める」.
// コマンドライン出力は「行頭をブランクで始める」.
// 実習アイコン
// &ref(/materials/notes.png);
// 注意アイコン
// &ref(/materials/warning.png);
// Link アイコン
// &ref(/materials/JNorth_arrow-right-sm.png);
// OK アイコン
// &ref(/materials/OK.png);
// NG アイコン
// &ref(/materials/NG.png);
// サンプルアイコン
// &ref(/materials/Gnome-Preferences.png);
// 大文字での強調
// CENTER:&size(24){''ほげほげ''};
// programu source 表記
// #highlighter(language=ruby,number=on,cache=on){{}}
// パイプ
// ¦
// 空白記号
// ␣
// タブ記号
// ␉
