*大量データの一括処理：SQLiteによるリレーショナルデータベース入門 [#i5105a46]
【教科書：44-48p参照】
#contents
前回授業ではDNAデータベースを検索して、自分が必要とするデータを取り出し（検索・ダウンロード）、加工し（アラインメント）、目的とする解析（系統解析）を行いました。授業後の意見調査の結果でも、よく理解できたという人が多かったようです。理解しやすかったのは、皆さんが、インターネットを使って何かを検索し、データを取り出すという作業に慣れているせいだと思います。
授業で使ったDNAデータベースやYahoo、Amazonなどのサイトで、キーワードを入力して検索をかけた時、どうやってデータが取り出されているか想像したことはあるでしょうか？DNAデータベースだと、数千万件のデータ（配列にすると数百億！）、ちょっと実感がわかないほど大量のデータが処理されていますよね。
データの量が多くなるほど、目的のものを取り出すのは、なかなか大変なはずなんですが、やっぱり実感はわきません。では、もう少し、具体的な話しをしましょう。今、あなたの手元には、卒業研究で自分が決定したミオシン遺伝子の部分塩基配列100件分がFASTA形式ファイルとして保存されています。１件１件のデータは1000塩基ぐらいなのですが、この配列の中から、ATTATAATという配列から始まる300bpぐらいだけを抜き出したいと考えています。どうやりますか？
すでに皆さんが習得している方法だと、テキストエディタの検査機能を使うという手がありますね。K2EditorでFASTA形式ファイルを開いて、「検索」を選び、検索文字列にATTATAATを指定いて検索する。そうすると、画面上にATTATAATから始まる部分が強調表示されます。そこから500bpぐらいをマウスを動かして選択して、コピーして、別のウィンドウを開いてペーストして、また前の画面にもどって、この配列のアクセッション番号まで画面を動かして戻って、配列名やアクセッション番号を選択してコピーペーストして、。。。。あーめんどくさい。
>>
Hairetsu1>~
......&color(red){ATTATAAT};GCTAAGATCGAA....~
Hairetu2>~
......................................~
Hairetu3>~
..&color(red){ATTATAAT};GCTATGATCGCA....~
....~
上の赤字の部分をテキストエディタの検索機能を使って探し出し、コピーペースト
>>
Hairetsu1>~
&color(red){ATTATAAT};GCTAAGATCGAA....~
Hairetu3>~
&color(red){ATTATAAT};GCTATGATCGCA....~
....~
「検索」、「選択」、「コピー」、「ペースト」で新しいファイルは作れる。でも、面倒。
テキストエディタでも、ワープロでも、検索はすごく便利な機能だけれど、自分が必要としている部分だけを取りだそうとすると、コピー・ペーストとも、かなり面倒な操作が必要になります（実感がわかない人は、上に書いた作業をやってみてください。途中で嫌になること請け合いです）。
こういう作業を簡単にしてくれるのがデータベースです。先に挙げたDNAデータベースでも、Yahooでも、Amazonでも、いずれもリレーショナルデータベースというシステムを利用することで、ものすごく大量のデータから、一瞬にして必要な情報を検索し、抽出し、並び替えるという処理をおこなっています。この、
|SIZE(20):検索後の抽出と並び替え|
というのが、テキストエディタやワープロには簡単にできないことです。データベースは、こういう面倒な処理を、大量のデータについて簡単に行えるという優れものです。大量データ処理に関しては、現時点における標準的なツールといっても良いでしょう。先日の調査結果によると、多くの皆さんが学生生活（＋研究生活）において、大量のデータを処理することになると考えているようです。そんなとき、データの抽出で、１件１件コピペするのも何か哲学的な意味を持った修行になるかもしれませんが、データベースシステムの扱いに慣れていれば、心と時間に余裕が生まれるでしょう。
データベースという言葉は、保存されたデータのまとまりそのものを指すことも、データを管理するシステムのことを指すこともあります。この授業でも特に厳密な定義はしません。授業で使うのは、リレーショナルデータベースというシステム（RDBS: Relational Data Base System）を無料で簡単に構築できるソフトウェア、''SQLite''です。このソフトウェアを使って、実際のデータからデータベースを構築し、必要なデータを抽出することを学びます。その過程で''SQL言語''という、データベースとお話するための言葉を学びます。
プログラミング言語、HTMLに続いて３つめの言語ですが、安心してください。SQLはかなり英語の文章に近いので、直感的にも理解しやすいと思います。
***関連リンク [#r8f09b48]
-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
**リレーショナルデータベースの実例 [#zfb18b42]
それでは具体例をつかって説明してゆきましょう。「リレーショナル」という言葉を説明し出すと長くなるので、まず、実例をお見せします。押さえてほしいポイントは次の３点です。
-&size(16){１行ごとにデータを並べた表を２つ（以上）作ります};
-&size(16){２つ（以上）の表全てで、１つのカラム（列）には同じデータが入っています};
-&size(16){共通するデータで全ての表を結びつけ、１つの表のようにして扱うことができます};
簡単な例として、サークルの名簿と会費納入状況のデータベースを考えてみましょう。説明のためにはカラム名もかな・漢字表示をする方が良いのでしょうが、ここでは、キーボードからの入力の混乱を避けるために、カラムの名前は全て半角英数文字で表記します。
想定しているのは、次のようなデータです。
|number|namae|yubin|jusho|tel|gakunen|nyuukai|2006_4|2006_5|2006_6|2006_7|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2818|3|200404|500|500|500|500|
|2|朝川毅守|263-8522|千葉市稲毛区緑が丘|043-290-2818|2|200504|500|500|500||
|3|梶田 忠|263-8522|千葉市稲毛区小仲台|043-290-2818|1|200606|||500||
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2874|3|200404|500|500|500|500|
|2|朝川毅守|263-8522|千葉市稲毛区緑が丘|043-290-2874|2|200504|500|500|500||
|3|梶田 忠|263-8522|千葉市稲毛区小仲台|043-290-2874|1|200606|||500||
こういうデータなら、皆さんならどうやって管理しますか？
そうですね、エクセルで管理するという方法があります。では、ちょっとやってみましょう。
この表をエクセルで開いて、8月分の会費を集めたときは、2006_8という列（カラム）を''手作業で''一つ追加します。また、会費の未納状況を見るときには、納入状況を一つ一つ''目で''確かめます。納入された会費を集計するときには、行の右端でsumをとって、また、表の一番下でもsumをとります。e-mailもついでに管理しておこうとおもうと、列（カラム）を一つ追加するので、右のほうがますます見にくくなってしまいます。
しかも、この例では３件しかデータを示していませんが、サークルの会費なので、前の年のデータも有るはずです。なので、ワークシートはとても大きく、見にくくなりますね
こんな感じ：
住所等のデータ 2003年 2004年 2005年 2006年
名前（2003年入会） ●●●●●●●●●●●●●●●●●●●●
名前（2004年入会） ●●●●●●●●●●●●●●●●
名前（2005年入会） ●●●●●●●●●●
名前（2006年入会） ●●●●●
006年の入会者にとっては、左の方の空白のセルがムダになっています。それに、毎年毎年、このワークシートはどんどん右側に大きく伸びてゆくので、1年生と4年生の支払い状況を比較するのは難しそうです。このように、無計画にエクセルで集計表を作ってしまうと、目で見て、手でカラムを追加するというのが大変な作業になってしまうのです。
そこで、手と目に大変なこの作業を、頭とリレーショナルデータベースを使って解決してみましょう。
この表をリレーショナルデータベースに作り直す場合、まず、データの構造を変更します。この表には、
-会員の住所等個人情報
-会員の会費納入状況
という２つの異なるデータが混在しています。そこで、この２つを区別します。また、どの年のどの月の会費を払うかは、会員の入会時期によっても異なるデータなので、会費納入のデータは１行ずつに、会員番号と納入月を追加して表すことにします。そうすると、データの構造は、次のようになります。さらに、会費は年ごとに集計することもあるでしょうから、日付は年と月の２つに分けておく方が使いやすくなります（データ構造をシンプルに使いやすくすることを、データの正規化といいます）。
-会員の住所等個人情報
|number|namae|yubin|jusho|tel|gakunen|nyukainen|nyukaituki|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2818|3|2004|04|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2818|2|2005|04|
|3|梶田 忠|263-8522|千葉市稲毛区小仲台|043-290-2818|1|2006|04|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2874|3|2004|04|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2874|2|2005|04|
|3|梶田 忠|263-8522|千葉市稲毛区小仲台|043-290-2874|1|2006|04|
-会員の会費納入状況
|number|nen|tuki|kaihi|
|1|2006|04|500|
|1|2006|05|500|
|1|2006|06|500|
|1|2006|07|500|
|2|2006|04|500|
|2|2006|05|500|
|2|2006|06|500|
|2|2006|07|500|
|3|2006|06|500|
データの構造がかなりすっきりしたのが分かるでしょうか？「でも、会員番号と納入月だけ出されても、誰のデータかわかんない。。。」という疑問が出そうですよね。それを解決するのが「リレーショナルデータベース」の「リレーション」（関連づけ）という機能です。
上の２つの表は、「会員番号でリレーションをとる（関連づける）」ことによって(下の例では、ついでに名前順・日付順に並び替えています）、
|name|nen|tuki|kaihi|
|綿野泰行|2006|04|500|
|綿野泰行|2006|05|500|
|綿野泰行|2006|06|500|
|綿野泰行|2006|07|500|
|朝川毅守|2006|04|500|
|朝川毅守|2006|05|500|
|朝川毅守|2006|06|500|
|朝川毅守|2006|07|500|
|梶田 忠|2006|06|500|
という形で扱うことができるのです。
**SQLiteとは [#hbbc4b22]
では、実際にデータベースシステムを操作してみましょう。ここでは''SQLite''というソフトを使います。SQLiteというのはフリーで使えるリレーショナルデータベースシステムです。他のデータベースシステム（例えばMySQLやPosgreSQL）ではユーザの設定やネットワークの設定など、面倒なことが多いのですが、SQLiteはダウンロードした実行ファイルをクリックするだけで、データベースを使うことができます。データベースのデータ本体も１つのファイルに保存されるので、コピーやバックアップが簡単にできるという優れものです。
とはいうものの、世間一般のパソコンユーザにはそれほど使わてはいません。なぜかというと
コマンドラインからSQLという命令文を入力しなくてはならない
説明が英語
データの入出力が面倒(エクセルやエディタで前もって編集が必要）
というのが敬遠されている理由でしょうが、Rの操作でコマンドラインに慣れた皆さんにとっては、それほど苦にはならないはずです。データの入出力が面倒なのは大問題ですが、SQLiteは、エクセルやテキストエディタと合わせて使うものと位置づけておけば良いでしょう。
**SQLiteの準備 [#e1f01cee]
では、SQLiteを使う準備をします。以下のリンクから、SQLiteの実行形式ファイルをダウンロードしてください。
-SQLite3 WindowsXP用実行ファイル http://www.sqlite.org/sqlite-3_4_0.zip~
ダウンロードが成功すればデスクトップにsqlite3.exeというファイル&ref(./WS000006.JPG);ができているはずです。
-マイドキュメントにsqliteという名前のフォルダを作って、その中に先ほどのsqlite3.exeを入れる。
~この操作は別にやらなくてもSQLiteは動きますが、後々データベースファイルがどこにあるのか探すのが面倒なので、あらかじめ入れ場所を作っておきます。
-次に、''コマンドプロンプト''を起動します。ウィンドウズのスタートメニューから、「すべてのプログラム／アクセサリ／コマンドプロンプト」を選んで下さい。~
&ref(./WS000009.JPG);~
そうすると黒いウィンドウが開き、Rで使ったようなプロンプトが表示されます。これはWindowsXPというOSを動かすもとになっているDOSのコマンドプロンプト（キーボードから命令を入力する処理系）です。ここに文字を入力することで、コンピュータに命令を与えることができます。試しに下の用に入力してみると、ディレクトリの内容一覧が表示されます。
dir
-では、ここで、下のように入力して、カレントディレクトリ(現在、自分が閲覧しているディレクトリのこと）をZ:\sqlite に変更してください。そうすると、プロンプトのところが、Z:\sqlite> に変わります。
> cd z:\sqlite
Z:\sqlite>
ついでに、dirと打って、sqlite3.exeというファイルが入っていることを確認しておきましょう。
Z:\sqlite>dir
文字がいろいろと表示されますが、その中に
2007/06/19 02:45 375,974 sqlite3.exe
という行があれば、準備は完了です。
**SQLiteの起動と終了 [#g8b9eab9]
sqliteというフォルダにsqlite3.extが入っていることを確認できたら、とりあえず、SQLite3を起動して、ついでに終了の方法も覚えましょう。（手続き通りに''終了''しないとデータが消えてしまうことがあるので、要注意です）
-起動
Z:\sqlite>sqlite3
上のように入力して、下のようなメッセージが表示されたら、正常に起動されています。
SQLite version 3.4.0
Enter ".help" for instructions
sqlite>
-終了 終了は''sqlite>''というプロンプトの後に''.quit''か''.exit''と入力します。
sqlite>quit
''.''を入力するのを忘れないでください。これでプロンプトは
Z:\sqlite>
に戻ったはずです。
**SQLiteの起動とデータベース ex1 の作成（作成済みのデータベースの場合、オープン） [#lb016a30]
それではいよいよ、実際のデータを保存するデータベースを作りましょう。今回の例で作るデータベース以下のような構造になっています。
データベース： ex1.db （sqlite3を起動するとき、「sqlite3 データベース名」という命令で作成
テーブル: table1 (起動後（プロンプトはsqlite> ）SQL命令（create table...)を入力
テーブル: table2
※１つのデータベースは１つまたは複数のテーブルがあつまってできています。
上で説明した１つ１つのエクセルの表はテーブルの一つ一つに相当します。
データベースを作るには、Windowsのコマンドプロンプトから、次のように入力します。
Z:\sqlite>sqlite3 ex1.db
''sqlite3 ＜データベース名＞''は、新しいデータベースを作ったり、もしすでに作成済みのデータベースがあれば、それを開く命令です。上の命令を入力すると、画面がSQLiteのコマンドプロンプト（sqlite>）に変わります。
(注意：このとき、ex1.dbというデータベースは、z:\sqlite\というフォルダの中にできています）
**コマンドラインからのSQL入力 [#l40d26ba]
ここから先は、実際にSQLを入力してデータベースの操作を行います。復習課題では、ここから先と同様の操作を課題用のデータファイルを使ってやってもらいます。
***テーブル tbl1の作成 [#bb67ea40]
先ほどの操作で、今はex1.dbという名前のデータベースに対していろんな命令を与える準備ができました。今から入力する命令は、ex1.dbというデータベースの中にテーブルを作る命令です。作成するテーブルには次のようなデータを入れようと思っています。
-tbl1
|number|namae|yubin|jusho|tel|gakunen|nyukainen|nyukaituki|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2818|3|2004|04|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2818|2|2005|04|
|3|梶田 忠|263-8522|千葉市稲毛区小仲台|043-290-2818|1|2006|04|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2874|3|2004|04|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2874|2|2005|04|
|3|梶田 忠|263-8522|千葉市稲毛区小仲台|043-290-2874|1|2006|04|
使うSQL命令は、''create table ＜テーブル名＞ (カラム名, カラム名...);''です。 以下を sqlite> の後にコピーペーストしてください。
sqlite> create table tbl1 (number, namae, yubin, jusho, tel, gakunen, nyukainen, nyukaituki);
意味は、「number, namae, yubin,...という列（カラム）を持ったtbl1とういテーブルを作りなさい」というものです。命令はけっこう長いし、入力まちがいがあると、うまく動きません。でも、間違ってしまった場合、↑キーでもう一度命令を呼び出して編集できますし、作成したテーブルを削除してもう一度やり直したい場合は、
sqlite> drop table tbl1;
とやれば先ほど作ったtbl1というテーブルを消すことができます。データベース中にあるテーブルの一覧は
sqlite> .table
と入力すれば見ることができます。.で始まるコマンドのことを、[[ドットコマンド>http://bean.bio.chiba-u.jp/lab/index.php?%E6%8E%88%E6%A5%AD%2FH19%2F%E6%83%85%E5%A0%B1%E5%87%A6%E7%90%86%2F13#g596189d]]といいます。ドットコマンドはSQLでは無く、データベースからの入出力などの設定を行います。
***テーブル tbl1 へのデータの一括登録 [#h0554b19]
-《''重要''》 区切り文字の設定： 今回使うサンプルファイル&ref(授業/H18/情報処理/13/ex1.txt); &ref(授業/H18/情報処理/13/ex2.txt);はタブ区切りのテキストファイルです。そのため、SQLiteで区切り文字がタブであることをドットコマンドで指定してやらなければなりません。この指定を忘れると、うまくファイルが読み込まれません。
sqlite> .mode tab
-データファイルの準備： tbl1に一括登録するデータを、&ref(授業/H18/情報処理/13/ex1.txt);からダウンロードして、マイドキュメントのsqliteフォルダに入れてください。
-データのインポート： 先ほどダウンロードしたファイルをドットコマンドを使ってテーブルに読み込みます。また、''select''命令を使って内容を一覧します。
sqlite> .import 'z:\sqlite\ex1.txt' tbl1
sqlite> select * from tbl1;
-同様に次のコマンドでtbl2を作成し、データを追加します。出たファイルは右からダウンロードしてください。&ref(授業/H18/情報処理/13/ex2.txt);
sqlite> create table tbl2 (number, nen, tuki, kaihi);
sqlite> .import 'z:\sqlite\ex2.txt' tbl2
sqlite> select * from tbl2;
***キーを用いた２つのテーブルの連結表示 [#qf74f14d]
２つのテーブルにデータが入ったかどうかを、両者に共通するnumberというキーで結びつけて表示してみましょう。
sqlite> .header on
sqlite> select * from tbl1, tbl2 where tbl1.number=tbl2.number;
このコマンドでは、全てのカラムが表示されるのでみずらいですね。表示したいカラムを選んで、しかも、年ごとに支払金額を集計するには、次のような命令を与えます。
-sqlite> select namae, nen, sum(kaihi) from tbl1, tbl2 where tbl1.number=tbl2.number group by nen, namae;
あるいは
-sqlite> select namae, nen, sum(kaihi) from tbl1 left outer join tbl2 on tbl1.number = tbl2.number group by nen, namae;
namae|nen|sum(kaihi)
梶田 忠|2006|500
朝川守毅|2006|2000
綿野泰行|2006|2000
***SQLiteの終了 [#ibc15202]
sqlite> .quit
上のように入力すれば、終了できます。
***補足説明 [#z16fc6fc]
「こういうコマンドを考えるのは面倒くさいや。上の表をエクセルで、こんな風にして管理したって、同じことでしょ？」
|number|namae|yubin|jusho|tel|gakunen|nyuukai|tuki|kaihi|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2818|3|200404|200604|500|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2818|3|200404|200605|500|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2818|3|200404|200606|500|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2818|3|200404|200607|500|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2818|2|200504|200604|500|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2818|2|200504|200605|500|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2818|2|200504|200606|500|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2818|2|200504|200607|500|
|3|梶田 忠|263-8522|千葉市稲毛区小仲台|043-290-2818|1|200606|200606|500|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2874|3|200404|200604|500|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2874|3|200404|200605|500|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2874|3|200404|200606|500|
|1|綿野泰行|263-8522|千葉市稲毛区弥生町1-33|043-290-2874|3|200404|200607|500|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2874|2|200504|200604|500|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2874|2|200504|200605|500|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2874|2|200504|200606|500|
|2|朝川守毅|263-8522|千葉市稲毛区緑が丘|043-290-2874|2|200504|200607|500|
|3|梶田 忠|263-8522|千葉市稲毛区小仲台|043-290-2874|1|200606|200606|500|
はい。その通りです。
でも、次の２つの問題点があります。
+このテーブルのほとんどは、同じデータの繰り返しになっているので、ムダが多い。
+住所などのデータに変更があったとき、関係のある全てのデータを変更しなければならないので、ムダが多い
ということです。こういうデータ構造でも問題はないのですが、あまりにもムダが多いので、２つに分けるのが良いということです。
**先ほどの操作のまとめ [#b47ab5e7]
上で行った操作とコマンドをまとめておきます。
SQLの最後は ; （半角セミコロン）で終わる
ドットコマンドの最初は . （半角ドット）で始まる （セミコロンは不要）
値は ' ' （シングルクォーツ）で囲む （※省略できる場合も多々ある）
+SQLiteの起動とデータベースを作成
Z:\sqlite> ''sqlite3 ex1.db''
--意味： ex1.dbというデータベースファイルを作成（または既存の場合は開く）。OSのコマンドラインから入力された。場所はsqliteフォルダの中
+テーブルの作成:SQL: ''create table''
create table tbl1 (number, namae, yubin, jusho, tel, gakunen, nyukainen, nyukaituki);
--意味：number, namae, ... nyukaitukiという８つのカラムを持つtbl1というテーブルを作成
+外部ファイルの区切り文字を指定:ドットコマンド: ''.mode''
.mode tab
--意味：入出力に使われるファイルでは、カラム同士はタブで区切られていると設定する
+外部データのインポート：ドットコマンド ''.import''
.import z:\sqlite\ex1.txt tbl1
--意味：sqliteフォルダに入っているex1.txtをテーブルex1.txtに読み込み
+テーブル内容の表示：SQL: ''select''
select * from tbl1;
--意味：tbl1の全てのカラム（*で示されている）を選択して表示
+２つのテーブルを連結して表示：SQL: ''select''
select namae, nen, sum(kaihi) from tbl1, tbl2 where tbl1.number=tbl2.number group by nen, namae;
--意味：tbl1とtbl2からnamae, nen, kaihiの合計（sumという関数が使われている）を表示する。但し、where以下の条件（tbl1のnumberとtbl2のnumberが一致）に合うもので、nenとnamaeでグルーピングしたもの
tbl1.number は tbl1というテーブルのnumberというカラム を表します。
カラムの指定は＜テーブル名＞.＜カラム名＞で指定することができますが、どのテーブルのカラムかが
明らかな場合は＜テーブル名＞の部分は省略できます
-それぞれの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の短所：インターフェースの使いにくさ [#o2c851ee]
大量データの扱いに優れており、しかも、無料で使えるSQLiteですが、コンピュータに慣れていない人には非常に使いにくいだろうなと思う点があります。それは、データを追加したり、編集したりするためのインターフェースの貧弱さです。sqliteを立ち上げて、データを一つ追加するコマンドを入力すると、
sqlite> insert into tbl1 values('3', '梶田 忠', '263-8522', '千葉市稲毛区小仲台', '043-290-2818', '1', '2006', '7');
sqlite> insert into tbl1 values('3', '梶田 忠', '263-8522', '千葉市稲毛区小仲台', '043-290-2874', '1', '2006', '7');
などという、長く、しかも、シングルクォートがたくさんあってタイプミスを起こしやすそうな命令文を入力しなければなりません。検索・抽出がいかに素早くできても、データの追加にいちいちこういう複雑な命令文を考えなければならないのでは、困ってしまいます。
PostgreSQLなど、他のリレーショナルデータベースでは、データ管理を簡単に行える洗練されたフリーソフトがあります。PpostgreSQLと同じくらい人気のあるMySQLでも、同様に使いやすい管理用のフリーソフトがあります。ところが、SQLiteでは、管理ソフトはあるにはあるのですが、利用できる漢字コードの問題、SQLiteのバージョンの問題など、授業で使用するにはいくつか適当ではない点があって、今回は利用しません（自分でSQLiteを使ってみようという人は、SQLite3とSQLiteControlCenterの組み合わせを試してみると良いと思います）。
えーっ！？毎回毎回 insert tbl1 ナンタラカンタラ なんてタイプするなんてムリっ！
編集するのにも update tbl1 ナンタラカンタラ なんていちいちタイプしなければならないなんて、使えなさすぎっ！
そういう面倒なことをしなくちゃならないのだったら、SQLiteは実用的ではないのでは？
という非難の声が聞こえてきそうです。本当にSQLiteは使い物にならないのでしょうか？
皆さんがこれから出会うデータ処理の状況を考えてみて、データベースを常に立ち上げて管理する場面というのは、それほど沢山は無いと思います。それよりも、もっと多いのは、上の課題でやったように、複数の異なる表を組み合わせて、データを検索、抽出するという作業です。このような作業では、''エクセル''、''テキストエディタ''、''SQLite''の組み合わせがものを言います。
**SQLiteとエクセルやテキストエディタの合わせ技 [#j672f247]
***学生の成績一覧表の作成をエクセルやテキストエディタで行う [#c61f2ac8]
今手元に、国語、数学、英語の先生から提出された成績をまとめた書類があります。
- &ref(./成績集計表.doc);
この成績表には学籍番号と成績がそれぞれ書かれています。また、名簿ファイル：&ref(授業/H18/情報処理/13/meibo.txt);には、学籍番号、名前、よみがなが書かれています。学生毎に選択している科目が違うので、３人の先生から集めた情報を１つにまとめるのは大変そうです。
さて、この４つの情報を統合して、学生ごとの成績を一覧表示する場合を考えてみましょう。こういうときエクセル、テキストエディタの組み合わせで、次のような手順で操作を行えば、４つの表から目的のデータを抜き出すことができます。
+まず、上の成績ファイルをデータベースで利用できる形のテーブルにまとめることを考えます。うえのデータをそれぞれタブ区切りテキストに変換し（''正規表現検索・置換です''）、科目名を付け加え、１つのテーブルにまとめます。（&color(red){注};：SQLを使う場合、３つをそれぞれ別のテーブルとして扱う方が、簡単かもしれません。でも、この授業では、エクセル・テキストエディタ・データベースの合わせ技を勉強させたいのと、なるべく簡単な命令文を使いたいので、エクセルで簡単にできることはエクセルでやってしまいます。３つをそれぞれ別の表として扱う方法は、後の授業のページで説明します。）
--ワードで開いた成績ファイルをK2Editorでタブ区切りテキストに変換して、エクセルに移動します。エクセルで、それぞれの科目ごとに新しいカラムを付け加えます。(全部をK2Editorでやっても構いません）。
06S4061 42
06S4028 44
なら、
06S4061 42 eigo
06S4028 44 eigo
にするわけです。この作業を国語、数学、英語の３教科のデータについて行います。
---（学籍番号ごとに集計するだけなら、エクセルでもできますよね（だいぶ前にやりました））
+次に、上で１つのファイルにまとめた３教科分のデータを、タブ区切りのテキストファイルに保存します。K2Editorで新しい書類を作って、そこに、上の３つのファイルの内容を、コピーペーストします。
---注：もちろん、エクセルで最初からタブ区切りテキストにセーブしても構いませんし、エクセルを使わずに、最初からK2Editorを使って正規表現検索置換で科目名を加えてもかまわないのですが、ここで両方のソフトウェアを立ち上げているのは、もっと複雑な処理が必要になったとき
エクセル： データをカラム分けて眺めつつ、編集できる
テキストエディタ: 正規表現検索・置換を行うことで、データの一括整形ができる
ことを利用して、場合によってはそれぞれのステップで編集できることを思い出して貰うためです。
06S4061 42 eigo
.
.
06S4063 55 sugaku
.
.
06S4066 66 kokugo
.
.
のようなデータをK2Editorで作って、これを、ex3.txtというファイル名で、Z:\sqlite\に保存します。
+次にSQLiteを立ち上げて、テーブルを２つ作ります。
--１つは、先ほど作ったex3.txtの内容を入れるテーブルで、scoreという名前にします。このテーブルは
gakuseki score kamoku
という列からできています。のデータベースを作る命令は
--もう１つは、meibo.txtの内容を納めるテーブルで、meiboという名前にします。このテーブルは
gakuseki namae yomi
という列からできています。
--実際の操作は次のようにします。コマンドラインから、SQLiteを起動して, ex3.dbという名前のデータベースを作ります。
Z:\sqlite>sqlite3 ex3.db
--次に、上で考えた２つのテーブルを作ります。
sqlite> create table score (gakuseki, score, kamoku);
sqlite> create table meibo (gakuseki, namae, yomi);
+２つのテーブルに先ほど作ったex3.txtのデータと、meibo.txtのデータをそれぞれ読み込んで、gakusekiをキーにして関連づければ、それぞれの、氏名と科目の点数合計などを表示することがきます。
--まず、入力ファイルの区切り文字をタブに変更します（''忘れられがち'')
sqlite> .mode tab
--まず、それぞれのテーブルにデータを読み込みます。
---上の操作が追いつかなかった人は、このファイルを使ってください&ref(./ex3.txt);。
sqlite> .import 'ex3.txt' score
sqlite> .import 'meibo.txt' meibo
--これでデータが入ったはずです。確かめるには、select命令を使います
sqlite> select * from score;
sqlite> select * from meibo;
--それでは、２つのテーブルをgakuseiで関連づけてみましょう。
---sqlite> select * from meibo left outer join score on meibo.gakuseki = score.gakuseki order by yomi;
--他にも、命令の最後の部分を
order by yomi limit 10;
などにかえてやってみてください。
-※joinについて、 「[[PostgreSQL 8.2.4文書 第 7章問い合わせ> http://www.postgresql.jp/document/pg824doc/html/queries-table-expressions.html]]」では下のような説明と例が挙げられている。
INNER JOIN（内部結合）
T1の各R1行に対して、T2においてR1との結合条件を満たしている行が、結合されたテーブルに含まれます。
LEFT OUTER JOIN（左外部結合）
まず、内部結合が行われます。その後、T2のどの行との結合条件も満たさないT1の各行については、T2の列をNULL値と
して結合した行が追加されます。したがって、連結されたテーブルは無条件にT1の行それぞれに少なくとも1つの行があります。
例（注：[[PostgreSQL 8.2.4文書 第 7章問い合わせ> http://www.postgresql.jp/document/pg824doc/html/queries-table-expressions.html]]から一部のみ引用。
以下のテーブルt1
num | name
-----+------
1 | a
2 | b
3 | c
および、テーブルt2
num | value
-----+-------
1 | xxx
3 | yyy
5 | zzz
を想定すると、以下のように様々な結合に関する結果が得られます。
=> SELECT * FROM t1 CROSS JOIN t2;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
1 | a | 3 | yyy
1 | a | 5 | zzz
2 | b | 1 | xxx
2 | b | 3 | yyy
2 | b | 5 | zzz
3 | c | 1 | xxx
3 | c | 3 | yyy
3 | c | 5 | zzz
(9 rows)
=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)
=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
(3 rows)
=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
| | 5 | zzz
(3 rows)
=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
| | 5 | zzz
(4 rows)
**命令文を知りたい時は [#se38fcd6]
ここまでで説明した命令文についてもっと知りたいときは、
-SQLite SQL日本語リファレンス：http://www.net-newbie.com/sqlite/lang.html
を見てください。
*第13回授業の課題 [#j4ad25af]
-提出期限：7月18日水曜正午（下記課題全て）
--提出期限を過ぎたものでも、点数を半分にするなどで評価しています。
-&size(16){http://bean.bio.chiba-u.jp/joho19/ に、「自分のID」/13 という新しいページを作成し、下の囲みの中にあるアンケートをコピー・ペーストして、「回答：」の後に答えを書き込むこと。};
--手順
++個人ページのトップページ（上の方のページタイトルに、「joho19/自分のID」のみが書かれているページ）、画面の上の方にある〔 新規 〕をクリック
++ページ名を尋ねる入力スペースが表示されるので、半角英数字で、ドット・スラッシュ・1・3を下のように入力
./13
---注：課題提出ページが正しく作れていない場合、課題の点数から１点減点です
良くある間違い： joho19/07s9999/06 というページを作るべきなのに joho19/07s9999/05/06 としてしまったとか
**課題．意見調査 [#g7696e56]
下の囲みの中にあるアンケートをコピー・ペーストして、「回答：」の後に答えを書き込むこと。
*第13回授業アンケート
**氏名：
**課題への回答
-今日（7月12日）の授業の進み方は？（はやい、丁度いい、おそい）
--回答：
-今日の授業の難しさはどう感じましたか（簡単すぎ 簡単 丁度いい 難しい 難しすぎ）：
--回答：
-難しいと答えた人は、特にどの点が難しかったですか？：
--回答：
-今日の授業は（よく分かった 分かった 分からなかった）：
--回答：
-分からないと答えた人は、特にどの点が分からなかったですか？：
--回答：
-今日の講義で理解できなかった用語があったら挙げてください：
--回答：
-これまでの講義を振り返って、でもう一度説明して欲しいテーマや追加説明が欲しい演習などを挙げてください（複数可）。
--回答：
**復習課題：リレーショナルデータベースについて [#o21897ca]
このページには先ほどの３科目のデータに加えて理科と社会のデータが添付されています。&ref(授業/H18/情報処理/13/rika.txt);, &ref(授業/H18/情報処理/13/shakai.txt); この２つのファイルをダウンロードして、上と同様の処理を行いなさい。
-手順：
・２つのファイルをダウンロードして、エクセルに読み込み、rikaとshakaiというカラムを追加します
・カラムを追加したデータをK2Editorの新規書類にコピーして、ex4.txtという名前（他の名前でも良い）でセーブします。
・SQLiteを立ち上げて、国語、数学、英語の成績が入っている ex3.db というデータベースを開きます
Z:\sqlite>sqlite3 ex3.db
・ex3.dbに理科と社会のデータを追加します。
＜上でやった.importで始まる命令文を使う＞
＊＊ここまでで準備完了
-課題：（全部で４題あります）
+上で準備したデータベースから、データを抽出します。データはあいうえお順に並べ替え、名前（namae）, 科目（kamoku）, 点数（score）のみ、最初の10件だけ表示させ、レポート提出ページにテキストとして書き込みなさい。コマンドプロンプトで範囲選択してからコピー・ペーストしてください。行頭に半角空白を入れると、薄緑色のボックスの中に表示できます。）
並び替えと10件だけ表示させる命令
order by yomi limit 10;
を最後に追加
例： > select * from meibo, score where meibo.gakuseki = score.gakuseki order by yomi limit 10;
のようにして使う
+meiboとscoreという２つのテーブルを単純連結して、あいうえお順に並び替え、学籍番号(gakuseki), 名前（namae）, 科目（kamoku）, 点数（score）最初の10件だけをテキストでレポート提出ページに書き込みなさい。
よく似た例： sqlite> select * from meibo, score where meibo.gakuseki = score.gakuseki;
+meiboとscoreの２つを単純連結して、06S4053という学籍番号を持つ人の学籍番号、氏名、5科目の点数の順で表示させ、結果をレポート提出ページに書き込みなさい。特定の学籍番号の人のデータを表示させるには、 where score.gakuseki = '06S4053' を使います。whereの後の条件が２つ以上ある場合は、andでつなぎます。
例： ..... where meibo.gakuseki = score.gakuseki and score.gakuseki = '06S4041'
+全ての科目の合計点数を計算し、点数の高い順番に並び替えて、最初の10件を学籍番号、名前、合計点数の順に表示させ、レポートページにかき込みなさい。合計点数の計算には、授業の最初の例で使った group by を使います。
//------------------------
// エクセル：セルの内容の長さ (文字列) 32,767 文字 (セルに表示できるのは 1,024 文字まで。数式バーでは 32,767 文字 //SQLiteでは１カラムの内容は1MBまで
//<ACCESSION> ＠<ACCESSION>
// <qualifiers name="country"> ＠ <qualifiers name="country">
// <qualifiers name="note"> ＠ <qualifiers name="note">
// ^[^＠].*
// \n\n \n
// ＠ \t
// <ACCESSION> \n
// </ACCESSION>
// <qualifiers name="country">
// <qualifiers name="note">
// </qualifiers>
// -> excelへ
**ドットコマンドの説明（よく使うものだけを抜粋） [#g596189d]
#ヘルプと現在の設定を表示
.help ヘルプの表示
.show 様々な項目の現在の設定値を表示
#プログラムの終了
.exit プログラムの終了
.quit プログラムの終了
#外部ファイルからのデータ取り込み・データ書き出し
.import FILE TABLE TABLEにFILEの内容をインポートする
.output FILENAME 出力結果をFILENAMEという名前のファイルに保存
#外部ファイルのデータ区切り文字を設定
.mode MODE ?TABLE? 入力・出力ファイルの区切り文字切り替え。以下（一部のみ解説）のいずれか
csv コンマ区切り（Comma-separated values)
column 左揃えの列
html HTMLの<table>タグ
line １行に１つの値
list .separator（下の指定） 指定された文字で区切る
tabs タブ区切り
.separator STRING ".mode"と".import"で使うファイルの区切り文字をSTRINGで指定されたものに設定
#画面に表示される出力文字列の設定
.output stdout 出力結果をスクリーンに表示
.header(s) ON|OFF ヘッダーのオン・オフ切り替え
.nullvalue STRING STRING で指定された文字列を NULL（ヌル・データ無しのこと）のところで表示
.width NUM NUM ... "column" modeのときのカラムの幅を指定
#テーブル一覧とテーブルの設定表示
.tables ?PATTERN? PATTERNにマッチするテーブルを一覧
.schema ?TABLE? TABLEが作られた時のCREATE命令を表示
.dump ?TABLE? ... テーブルの内容をSQLで表示
#その他
.prompt MAIN CONTINUE 標準プロンプトを入れ替える
.read FILENAME FILENAMEに書かれたSQLを実行
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")