- 追加された行はこの色です。
- 削除された行はこの色です。
#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;
この授業では、売上を管理するデータベースを想定してみる．
具体的には、一つのデータベースの中に 商品情報のテーブル、顧客情報のテーブル、売上記録のテーブルを作ってみる．
そのつもりで以下を読み進めてみよう．
&br;&br;
* SQLite3 の起動、終了 [#z1c1380f]
** コマンドから SQLite を起動する = データベースへの接続 or データベースの作成 [#yb4ec40d]
SQLite のコマンドは、引数に文字列を与えるとその名前のデータベース(ファイル)に接続しようとする。
もしそのデータベースがなければ新規にデータベースが作られる。
たとえば ''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]
* SQLite3 でのテーブル「単独」操作 [#z20c4d9b]
** テーブルを作る [#p0947f21]
リレーショナルデータベースの中身の本質はデータを列挙した塊であるテーブルである。
ただし、一つのテーブルの中に列挙されるデータはすべて同じ構造をしているものとする必要がある。
その構造は、テーブル作成時に定義してやる必要がある。
SQLite でのテーブルの作成時におけるデータの定義は簡単だ．
しかもデータの型の定義を省略することもできるので、実用的なやり方としては(慣れていないうちは)次のようにしておくと良いだろう．
> ''CREATE␣TABLE␣テーブル名␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, 変数名2, 変数名3, ...);''
<
(web では一行に見えないかもしれないが、入力はできれば一行で．)
&br;
&ref(/materials/warning.png); 慣習として、SQL 命令は大文字で書かれるが、SQLite3 は小文字でも問題なく受け付ける．
&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);''
<
(web では一行に見えないかもしれないが、入力はできれば一行で．)
&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;&br;
** テーブルのデータを表示する [#o2002056]
テーブルに入っているデータをまずは見てみたいという場合は
SELECT 命令を使って以下のようにする．
> ''SELECT␣*␣FROM␣テーブル名;''
<
&br;
&ref(/materials/warning.png); 上の設定で ''.explain on'' としているとこの表示が少しだけ分かり易いようになる．
&ref(/materials/notes.png); まずは次の手順でテーブルを一つ作り、データを格納してみよう。
&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;&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]
&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 テーブルである．
5 回の売上げ情報を入れておこう．
> ''CREATE␣TABLE␣sales␣(id␣INTEGER␣PRIMARY␣KEY␣AUTOINCREMENT, cstm id, frt id);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,2,5);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,1,1);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,2,3);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,2,1);''
> ''INSERT␣INTO␣sales␣VALUES(NULL,3,4);''
SELECT 命令で中身をみると次のようになっているはずだ．
id cstm frt
---- ------------- ----
1 2 5
2 1 1
3 2 3
4 2 1
5 3 4
&br;
&ref(/materials/warning.png); この sales テーブルは他のテーブルの情報があって初めて意味があることに注意しよう．
&br;&br;
* Report: レポート [#zd897f9f]
以下の課題について能う限り賢明な調査と考察を行い，
&br;
''AppliedMath7-Report-14''
&br;
という題名をつけて e-mail にて教官宛にレポートとして提出せよ. なお，レポートを e-mail の代わりに TeX で作成した書面にて提出してもよい.
** Exercises: 課題 [#vf0c8a45]
+ aaa
&br;
&br;
&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){{}}
// パイプ
// ¦
// 空白記号
// ␣
// タブ記号
// ␉
