*大量データの一括処理：SQLiteによるリレーショナルデータベース入門 [#zd218494]

前回授業ではDNAデータベースを検索して、自分が必要とするデータを取り出し（検索・ダウンロード）、加工し（アラインメント）、目的とする解析（系統解析）を行った。アンケートでの結果「理解できた」という人が多かったのは、皆さんが、インターネットを使って何かを検索し、データを取り出すという作業に慣れているからだろう。

では、授業で使ったDNAデータベースやYahoo、Amazonなどのサイトで、キーワードを入力して検索をかけた時、どんなソフトウェアが働いて、データが取り出されているか想像したことはあるだろうか？DNAデータベースの場合、数1億件以上のデータ（配列にすると数百億！）という、ちょっと実感がわかないほど大量のデータが、インターネットを介して処理されていることになる。

自分の実験で沢山のデータを処理する場合も、データの量が多くなるほど目的のものを取り出すのが大変になってきて、１つ１つ手作業で処理するということは難しくなる。%%%そんなとき役に立ってくれるのが「データベース」だ。%%%先週使ったDNAデータベースでも、Yahooでも、Amazonでも、いずれも''リレーショナルデータベース''というシステムを利用することで、''塩基配列、生物情報、文献情報などのものすごく大量のデータから、一瞬にして必要な情報を検索し、抽出し、並び替える''という処理をおこなっている。この、

|SIZE(16):大量のデータの検索・抽出・並び替え|
と
|SIZE(16):複数のデータ（テーブル）を結合|

というのが、データベースには簡単にできて、テキストエディタやワープロには簡単にできないことだ。~
おそらく、今現在の時点でデータベースを使うことはあまりないだろうが、この技術を知っておくと、将来的にいろんな得をする場面に出会うことは間違い無い（あるいは、授業を聞いておくだけでも、楽をすることができるチャンスに気づくことができる）。さらに、自由に使えるようになれば、心と時間に余裕が生じるだろう。

前回のフィードバックで皆さんに尋ねた、これまでに自分が扱ったことのある最大のデータ件数と、将来扱うことになることが予測されるデータ件数をグラフにしてみ。これをみただけで、将来的にデータ件数がすごく増えると予想していることが分かる。~
&ref(./#15_1.jpg,60%);　　&ref(./#15_2.jpg,60%);

せっかく情報処理を勉強するのだから、こういう便利な方法があることはぜひ知って欲しい。今日の授業の前半部分ではとにかく、データベースを使って簡単なデータ処理に挑戦してみる。

//今日の授業は普段よりも盛りだくさんだが、前半は%%%挑戦%%%、後半は%%%習得%%%という点に注意して、授業に集中してほしい。


**第15回授業の獲得目標：&worried; [#offcb623]
-''1. リレーショナルデータベース（SQLite）を使える環境を作る：FireFoxによるアドオンの使用''
-''2. SQLという言語を使って、データベースとお話しする''
--SQLiteを使って、情報の検索・抽出・並び替えを行う方法を習得する
-''3. データベースにおけるテーブルの概念を理解する''
-''4. SQLを用いて、複数のデータテーブルを連結して、必要なデータだけを抽出する方法に挑戦する''

**1. リレーショナルデータベース（SQLite）を使える環境を作る：FireFoxによるアドオンの使用⌣ [#i5f9bede]

データベースという言葉は、保存されたデータのまとまりそのものを指すことも、データを管理するシステムのことを指すこともある。この授業でも特に厳密な定義はしない。授業で使うのは、''リレーショナルデータベース''という''システム''（RDBS: Relational Data Base System）を無料で簡単に構築できるソフトウェア、''SQLite''だ。このソフトウェアを使って、実際のデータからデータベースを構築し、必要なデータを検索・抽出・並び替えすることを学ぶ。その過程で''SQL言語''という、データベースとお話するための言葉を使う。

昨年までの授業では、自分のパソコンにフリーソフトウェア''SQLite''をインストールして、''コマンドライン''からデータベースを操作していた。~
ちょっとデモしてみるが、コマンドラインからの
-実行
-データの保存・利用

という点は、なかなか皆さんには馴染みの無いことらしく、あきらめてしまう人も多かったようだ。そこで今年からは、いつも使っているFireFoxにデータベース利用機能のアドオンを追加して、SQLiteを利用することにする。

なお、皆さんが大学で使っているコンピュータにはAccessというデータベースソフトも入っているが、無料で、自分のパソコンでも使えるという点を重視して、フリーソフトウェアであるSQLiteを使うことにした。

***FireFoxでのSQLite Managerのインストール [#a6761ec5]
-FireFoxを立ち上げる
-Google等でSQLite Managerを検索し、インストール
-FireFoxを再起動

***SQLiteについて自習する場合は、以下のサイトを参照してみよう [#l0f749aa]
-SQLite http://www.sqlite.org/
--マニュアルの日本語訳：　http://www.3rd-impact.net/Document/SQLite/Translation/Current/
--SQLite3 Windows用実行ファイル http://www.sqlite.org/sqlite-3_4_0.zip
//--PHP4からSQLiteを呼び出す ダイナミックライブラリ  http://pecl4win.php.net/list.php/4_4
//---&ref(php_sqlite.dll);
-SQLite　SQL日本語リファレンス（日本語だけど古い）：http://www.net-newbie.com/sqlite/lang.html
--コマンドチュートリアル（日本語だけど古い）：http://www.net-newbie.com/sqlite/sqlite.html
-rakutoネット：　SQLiteの使用例と解説：http://study.rakuto.net/php/command/
//-PHPマニュアル　http://jp.php.net/manual/ja/index.php　PHPの使い方がとてもよく分かる。プログラム例が豊富なので、とても助かる！
--SQLite CC http://www.takke.jp/soft/sqliteccj.html
---日本語表示も可能なSQLite用GUI

***SQLite Managerの起動 [#dbbeeb9f]
+FireFoxのツールメニューから、SQLite Managerを起動する~
&ref(./#15_3.jpg);
+''データベースディレクトリの指定''：　 画面の右上の方にある「ディレクトリ」をクリックして、データベースのデータファイルを保存するディレクトリ（フォルダーと同じ意味）を指定する~
&ref(./#15_4.jpg);
+''データベースの作成''：　SQLiteのデータベースは、１つのファイルに全てのデータや設定が保存される。サンプルデータの解析に使う''seiseki''という名前のデータベースを作成してみよう。
--FireFoxのメニューバーにある「データベース」から、「新しいデータベース」を選択し、データベース名の入力欄で''seiseki''と入力する。~
&ref(./#15_5.jpg);
~
これで準備は整った。


**データベースにおけるテーブルの概念を理解する⌣ [#g9470169]
SQLでは先ほど作ったデータベースファイルの中に、複数の''テーブル''を作成することで、データの操作を行う。
 テーブル　というのは　表　のこと
であることを、理解しておこう。例えば、エクセルを使うと、次のようなテーブルとして表せる。
~
&ref(授業/H21/情報処理/13/#13_1.gif);
~
また、
 １つのデータベースファイルの中には、複数のテーブルをしまっておける
 （エクセルで、複数のワークシートを使うようなもの）
では、エクセルを使わずに、SQLiteを使ってテーブルを操作することの利点は何か？それは、
 大量のデータを含んだ、　複数のテーブルを、　１つの命令で簡単に処理できること
 また、
 テーブルの連結が簡単にできること
だろう。何千、何万ものデータが入ったテーブルを、エクセルで開いて、並べ替えたり、データを抽出したりするのがどれだけ大変なことか、想像してみるといいだろう。

**SQLiteを使って、２つ以上のテーブルを連結して、データを抽出する方法に挑戦！！⌣ [#w4f923db]
これから実際にSQLiteを使って何ができるかを体験してもらう。目標とするのは、次のようなケースを簡単にSQLiteで簡単に処理することだ。
~
-''モデルケース''（自分が学校の先生になったことを想像してみる）：
今手元に、国語、数学、英語の先生から提出された成績をまとめた書類がある。~
&ref(授業/H19/情報処理/13/成績集計表.doc);~
この成績表には学籍番号と成績がそれぞれ書かれている。これからあなたのクラスの学生全員の、成績の集計をしたい。生徒の名簿は、~
&ref(授業/H21/情報処理/13/名簿.doc);~
という名簿のファイルに、「''学籍番号、名前、よみがな''」が保存されている。~
さて、学生毎に選択している科目が違うのだが、３人の先生から集めた情報を１つ集計するにはどうすればいいだろうか？~
（※じつは、この程度の数のデータなら、エクセルでもわりと簡単に集計できる＜SQLiteを使うと、この数百〜数千倍の規模のデータでも、簡単に処理できる＞）~
&ref(授業/H22/情報処理/13/#13_2010_1.gif,50%);
~
&ref(授業/H22/情報処理/13/#13_2010_2.gif,50%);

***テーブル作成の準備 [#k85c4bc4]
まず、テーブルを確認しておく。''テーブル''には、上の例で示したような''カラム名''が必要になる。それぞれ、タイプしやすいようなカラム名にしておくといいだろう。
|テーブル|テーブル名|カラム|h
|名簿|meibo |gakuseki, namae, yomi|
|国語|kokugo|gakuseki,score|
|数学|sugaku|gakuseki,score|
|英語|eigo|gakuseki,score|

***データをデータベースに入れるために、タブ区切りテキストファイルを準備 [#ubef00e3]
+''タブ区切りテキストファイルの準備''~
データをSQLiteで使える形にするには、''タブ区切りテキストファイル''の形にしておかなければならない。そこで、名簿とそれぞれの科目を、K2Editorを使って、タブ区切りテキストファイルとして保存する。それぞれ、
 meibo.txt
 kokugo.txt
 sugaku.txt
 eigo.txt
という名前で保存しておく。名簿の方はもともとタブ区切りになっているが、点数の方は空白で区切られているし、行頭に空白が入っているので、次の正規表現検索置換をして、テキストファイルにするのを忘れないように。
 検索文字列: ^ +　　置換文字列＜指定しない＞
 検索文字列:  +　　置換文字列: ¥t
-うまく出来ない場合は、次の４つのファイルを、右クリックしてダウンロードして使おう。~
&ref(授業/H18/情報処理/13/meibo.txt);&ref(授業/H18/情報処理/13/kokugo.txt);&ref(授業/H18/情報処理/13/sugaku.txt);&ref(授業/H18/情報処理/13/eigo.txt);
&ref(./meibo.txt);&ref(./kokugo.txt);&ref(./sugaku.txt);&ref(./eigo.txt);

***データベースとテーブルの作成、テキストファイルからのデータの取り込み [#za0f4ba6]
先ほど作成した、''seiseki''という名前のデータベースに、データを取り込んで、上の４つ科目のテーブルを作成してみよう
+SQLite ManagerのImport Wizardを起動する（メニューバーの「データベース」から「取り込み」を選択してもよい）
 
+上で考えた4つのテーブルを作る。なお、それぞれのテーブルのカラム名は、下を参考にしよう。~
テーブル作成の操作は、下のSQLを入力しても可能。
 sqlite> create table meibo (gakuseki, namae, yomi);
 sqlite> create table kokugo (gakuseki,score);
 sqlite> create table sugaku (gakuseki,score);
 sqlite> create table eigo (gakuseki,score);
--同じ操作は''create table　＜テーブル名＞　（　＜カラム名のリスト＞）;''という命令。新しいテーブルは、この命令で作る。
+これでデータが入ったテーブルができたはず。確かめるには、「SQL実行」からselect命令を使う
 sqlite> select * from meibo;
 sqlite> select * from kokugo;
+それでは、２つのテーブルをgakuseiで関連づけてみよう。今、２つのテーブル、''meibo''と''kokugo''を考えてみると、gakuseki（学籍番号）が共通していることが分かる。
 meibo (gakuseki, namae, yomi);
 kokugo (gakuseki,score);
そこで、select文で条件をしていするときに、「学籍番号が共通するものだけを選べ」という命令を入れてみる。このとき、''meibo''の学籍番号は、''meibo.gakuseki''で、''kokugo''の学籍番号は''kokugo.gakuseki''で表される。
--''select * from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki;''~
これだと、２つのテーブルの全てのカラムが表示されるので、表示させるカラムを指定する。
--''select meibo.gakuseki, meibo.namae, kokugo.score from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki;''
+同様にして３つ以上のシートからもデータを抽出できる。また、order byを使って並び替えも可能。
 -左側のパネルに4つのtableが出来上がれば、準備は完成だ。


***ここまでの操作のまとめ [#k4745525]
+テーブル内容の表示：SQL: ''select''
  select * from web;
--意味：webの全てのカラム（*で示されている）を選択して表示
+２つのテーブルを連結して表示：SQL:  ''select''
 select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name;
--意味：webとginkoからname, email, shozoku, date, amount を表示する。但し、webのデータは全部表示させ、ヨミガナが一致するデータがあるものについてだけ、ginkoのデータも表示させる
-それぞれのSQL命令の使い方の例は、以下を参考にしよう。
--SQLite コマンドの文法（英語）http://www.sqlite.org/lang.html
--やや古いが上記の日本語説明 http://www.net-newbie.com/sqlite/lang.html
--rakutoネット：　SQLコマンド　http://study.rakuto.net/php/command/
--PostgreSQLの日本語ドキュメント：http://www.postgresql.jp/document/pg824doc/html/sql-commands.html
---全てがSQLiteで使えるわけでは無いが、createとかselectの例は参考になる


***SQLiteを使って、データベースとお話しする [#o4a390c2]
ここから先は、''簡単な英語''を使って、データベースとお話しする。お話の内容（命令文）は、SQLite Managerの画面にある''SQL実行''をクリックして、入力欄に命令を入力することで行う。~
&ref(./#15_6.jpg);~
これから使う英語の意味をまとめておくと、
 select  　　：選び出して表示しなさい
 from  〜　　：　〜というデータベースから
 　where　　：　　・・・という条件で
というものだ。
 命令の形式さえ守っていれば、コンピュータは内容を分かってくれるので
 変更できるところは自由に変更して楽しんでみよう
-使用例：　selectの簡単な文章：
 書式：　select ＜カラム名,,＞　from ＜テーブル名,,＞ where ＜カラム名＞='＜値＞' order by ＜カラム名＞;
&color(red){''注意''};： &size(18){命令文の最後は必ず '';''（セミコロン）で終わらなければならない。};
~もしも、
 sqlite> select * from rigaku
とセミコロンを抜かして入力してしまうと
   ...>
と表示される。そんなときは、セミコロンを1つだけ入力すると、命令が実行される。
    ...>；
-では、次の命令を入力して、データベースとお話ししてみよう。また、演習問題を解くための命令文を、自分で考えてみよう。
-では、次の命令を入力して、データベースとお話ししてみよう。また、命令文を自分で考えて少しずつかえて、結果を見てみよう。
--''select * from meibo;''　：　「meiboというテーブルから全てのレコード(注： *は全てのレコードを表す）表示しなさい
--''select * from meibo order by yomi;''　　：「meiboというテーブルから全てのレコードを、yomiで並び替えて表示」
--''select gakuseki, namae from meibo;''　：「meiboというテーブルから、gakusekiとnamaeだけを表示しなさい」~
他のデータベースの例だが、こういうコマンドも可能
--''select shimei, shoku from rigaku order by gakka, shoku;'' : 「rigakuというテーブルから、shimeiとshokuを、gakkaとshokuで並び替えて表示」
--''select shimei, shoku from rigaku where gakka='生物学';''　：　「rigakuというテーブルから、shimeiとshokuを、gakkaが生物学のものだけを表示」
--''select * from rigaku limit 10;'' : 「rigakuというテーブルから、全ての項目を、10件だけ表示」
--''select gakuseki, namae from meibo;''　：「meiboというテーブルから、gakusekiとnamaeだけを表示しなさい」~う
***２つのテーブルを連結 [#ke76be76]
それでは、いよいよ２つのテーブルをgakuseiで関連づけてみよう。今、２つのテーブル、''meibo''と''kokugo''を考えてみると、gakuseki（学籍番号）が共通していることが分かる。
 meibo (gakuseki, namae, yomi);
 kokugo (gakuseki,score);
そこで、select文で条件指定するときに、「学籍番号が共通するものだけを選べ」という命令を入れてみる。このとき、''meibo''の学籍番号は、''meibo.gakuseki''で、''kokugo''の学籍番号は''kokugo.gakuseki''で表される。
--''select * from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki;''~
これだと、２つのテーブルの全てのカラムが表示されるので、表示させるカラムを指定する。
--''select meibo.gakuseki, meibo.namae, kokugo.score from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki;''
+同様にして３つ以上のシートからもデータを抽出できる。また、order byを使って並び替えも可能。
-''さらに発展的な連結''（非常に便利な連結方法）~
先ほどの連結方法で得られたレコード数は、97件だった。でも、実は、名簿には、100名分のデータが入っている。３名は国語の試験を受けていないため、成績が表示されなかったわけだ。
 select * from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki
 　↑　この命令文だと、
 kokugo.gakuseki（国語のファイルの学籍番号）が存在している場合だけ、データが表示される
しかし、それでは困ってしまう。成績集計の上では、受験の有無にかかわらず、名簿に載っている全ての人に対して点数をつける必要があるからだ。そこで、SQLに ''left outer join on ...''　という命令を追加する。これは、「左側のテーブルにあるものは全て表示し、対応するデータがあるものは右側のデータも表示するというものだ」。この命令を使うことで、「meiboの内容は全て表示し、受験されている場合だけ、kokugoの点数も表示させる」ということが可能になる。
--''select meibo.gakuseki, meibo.namae, kokugo.score from meibo left outer join kokugo on meibo.gakuseki=kokugo.gakuseki'
-今度は100件のデータが全て表示されており、国語の受験が無い人については下の例の様に、点数が表示されていない
 06S4030  岩井文男  45
 06S4031  岩藤俊幸  
 06S4032  紀谷昌彦  46
このとき、国語を受験しなかった人の点数は''NULL''（無）になっている。
-次に、国語の点数の高い順番で、結果をソートして表示させてみよう
--''select meibo.gakuseki, meibo.namae, kokugo.score from meibo left outer join kokugo on meibo.gakuseki=kokugo.gakuseki order by kokugo.score desc''

-では、次の演習問題をやってみよう
-この授業では、以上の簡単なSQLの使いかただけ演習したが、その他のSQL命令の使い方の例は、以下を参考にしよう。
--SQLite コマンドの文法（英語）http://www.sqlite.org/lang.html
--やや古いが上記の日本語説明 http://www.net-newbie.com/sqlite/lang.html
--rakutoネット：　SQLコマンド　http://study.rakuto.net/php/command/
--PostgreSQLの日本語ドキュメント：http://www.postgresql.jp/document/pg824doc/html/sql-commands.html
---全てがSQLiteで使えるわけでは無いが、createとかselectの例は参考になる



**第15回授業の課題 [#i006501f]
//-提出期限：''8月8日水曜正午''~
以下2題とも-http://bean.bio.chiba-u.jp/moodleから提出して下さい。
***課題１．意見調査 [#ra547a88]
-http://bean.bio.chiba-u.jp/moodleから提出して下さい。

***課題2（復習）：SQLiteを使ったデータベースの操作 [#rd991670]