![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
前回授業ではDNAデータベースを検索して、自分が必要とするデータを取り出し（検索・ダウンロード）、加工し（アラインメント）、目的とする解析（系統解析）を行った。アンケートでの結果「理解できた」という人が多かったのは、皆さんが、インターネットを使って何かを検索し、データを取り出すという作業に慣れているからだろう。
ところで、授業で使ったDNAデータベースやYahoo、Amazonなどのサイトで、キーワードを入力して検索をかけた時、どうやってデータが取り出されているか想像したことはあるだろうか？DNAデータベースの場合、数千万件のデータ（配列にすると数百億！）という、実感がわかないほど大量のデータが、インターネットを介して処理されていることになる。
自分の実験で沢山のデータを処理する場合も、データの量が多くなるほど目的のものを取り出すのが大変になってきて、１つ１つ手作業で処理するということは難しくなる。では、どうすれば良いか？そんなとき役に立ってくれるのが「データベース」だ。先週使ったDNAデータベースでも、Yahooでも、Amazonでも、いずれもリレーショナルデータベースというシステムを利用することで、ものすごく大量のデータから、一瞬にして必要な情報を検索し、抽出し、並び替えるという処理をおこなっています。この、
| 検索後の抽出と並び替え |
と
| 複数のデータ（テーブル）を結合 |
というのが、データベースには簡単にできて、テキストエディタやワープロには簡単にできないことだ。
おそらく、今現在の時点でデータベースを使うことはあまりないだろうが、この技術は知っておいて、絶対に損はない。将来的に自由に使えるようになれば、心と時間に余裕が生じる。
せっかく情報処理を勉強するのだから、こういう便利な方法があるということを知っておかなければ勿体ないので、今日の授業の前半部分ではとにかく、データベースを使って簡単なデータ処理に挑戦してみる。
また、授業の後半では、普段皆さんが使っているインターネットとウェブページの仕組みを解説して、自分でも簡単なウェブページを作成・公開できるようになってもらう。
今日の授業は普段よりも盛りだくさんだが、前半は挑戦、後半は習得という点に注意して、授業に集中してほしい。
![[worried]](image/face/worried.png)
†データベースという言葉は、保存されたデータのまとまりそのものを指すことも、データを管理するシステムのことを指すこともある。この授業でも特に厳密な定義はしない。授業で使うのは、リレーショナルデータベースというシステム（RDBS: Relational Data Base System）を無料で簡単に構築できるソフトウェア、SQLite。このソフトウェアを使って、実際のデータからデータベースを構築し、必要なデータを検索・抽出・並び替えすることを学ぶ。その過程でSQL言語という、データベースとお話するための言葉を使う。
あれこれ説明を聞くよりも、まずは手を動かして作業してみよう。まずはリレーショナルデータベースシステムを利用するために必要なフリーソフトウェアSQLiteをインストールする。皆さんが大学で使っているコンピュータにはAccessというデータベースソフトも入っているが、自分のパソコンでも無料で使えるという点を重視して、SQLiteを使うことにした。SQLiteはダウンロードした実行ファイルをクリックするだけで、データベースを扱うことができる。また、データベースのデータ本体が１つのファイルに保存されるので、コピーやバックアップが簡単にできるという特徴を持っている。
とはいうものの、世間一般のパソコンユーザにはそれほど使われていない。なぜかというと
コマンドラインからSQLという命令文を入力しなくてはならない 説明が英語 データの入出力が面倒(エクセルやエディタで前もって編集が必要）
だろう。ただ、Rの操作でコマンドラインに慣れた皆さんにとっては、それほど苦にはならないはずだし、データの入出力が面倒なのは大問題だが、SQLiteは、エクセルやテキストエディタと合わせて使うものと位置づけておけば良いだろう。
では、以下のリンクから、SQLiteの実行形式ファイルをダウンロードしよう。
ができているはず。この操作は別にやらなくてもSQLiteは動くが、後々データベースファイルがどこにあるのか探すのが面倒なので、あらかじめ入れ場所を作った。
meibo
dir
> cd z:\sqlite Z:\sqlite>ついでに、dirと打って、sqlite3.exeというファイルが入っていることを確認しておこう。
Z:\sqlite>dirさらに
sqlite3 meiboと入力すると、SQLiteが起動し、先ほどダウンロードしたmeiboというデータベースファイルが開かれる
このとき知っているべき英語
select from where
これから実際にSQLiteを使って何ができるかを体験してもらう。目標とするのは、次のようなケースを簡単にSQLiteで簡単に処理することだ。
モデルケース： あなたは参加者が2000人ぐらいの学会の会計をやることになった。参加者はウェブから氏名や所属などの参加登録データを送信する。送信されたデータはエクセルの表として保存されている。また、参加費は銀行振り込みで支払われる。振り込みデータもエクセルの表として保存されている。
右の表をダウンロードしてエクセルで開いてみよう。&ref(): File not found: "#13_table.xls" at page "授業/H21/情報処理/13";
（この表に記述されているデータは、全て架空のものです)
２つのシート（下の方にあるタブ） 「Web登録データ」と「銀行振り込み」が入っている。
会計係のあなたは、２つの表を比較して、参加費未納者や、振り込み金額間違いのリストを作らなければならない。また、メールアドレスを抜き出して、督促状を送らなければならない。
さて、どうすれば良いだろうか？
このケースでは、２つの表がる。
ウェブ登録で得られた参加者のデータ： 氏名、よみがな、所属、メールアドレス（後にmeiboと呼ぶ表） 銀行から得られた振り込みリスト： 日付、振り込み人氏名（カナ）、振り込み金額（後にfurikomiと呼ぶ表）
手作業で解決するならば、それぞれの表をエクセルで１つのシートにまとめ、氏名で並び替え、上から順に、会費未納の人はいないか（あるいは、２回振り込んでしまっている人がいないか）、振り込み金額が間違っている人がいないかをチェックする。
しかし、この例で用いた100件のデータでもかなり大変な作業になる。これが2000件とか1万件とかのデータを対象に手作業でやるとなると、、、、ちょっと気が遠くなってしまう。
こういうときこそ、データベースソフトウェアであるSQLiteの出番だ。まず答えだけを言ってしまうと、SQLiteを使って２つのテーブルを連結して、２つを比較する次の命令を与えれば、上の作業をコンピュータにやらせることができる。
select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name;
では、２つのテーブルが連結できるところまで、話しを進めてみよう。全体の流れは次のようになる。
| 1. エクセルの表のデータをSQLiteのテーブルに移す |
| 1-1. SQLiteを起動し、webとginkoというテーブルを作成する |
| create table web (name, name_kana, shozoku, email); |
| create table ginko (date, ginko_name, amount); |
| 1-2. エクセルのデータをK2Editorにコピーし、タブ区切りテキストにしておく |
| web.txt, ginko.txtという２つのファイルを作る |
| 1-3. SQLiteで、webとginkoというテーブルに、web.txt, ginko.txtの内容をそれぞれインポートする |
| import 'z:\sqlite\web.txt' web |
| import 'z:\sqlite\ginko.txt' ginko |
| 2. SQLiteの２つのテーブルで、ヨミガナが同じデータ同士を連結する |
| 2-1. select ... left outer join ...という命令で２つのテーブルを連結 |
| select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name; |
| 2-2. 得られた結果をK2Editorにコピーし、空白の連続をタブに変換して、エクセルで閲覧 |
上のような操作で２つのテーブルを連結することで、次のような表が得られ、Web登録をしたのに銀行振り込みしていない人がいるかどうかや、間違った金額を支払っている人がどれくらいいるかなどを知ることができる。
| name | shozoku | date | amount | |
| 市川トミ子 | 06S4080@gmail.com | 東北大・院・理 | 2008-05-09(Fri) | 6000 |
| 越川和彦 | 06S4010@gmail.com | 東大院・理学系・生物科学 | 2008-05-16(Fri) | 7000 |
| 宇山智哉 | 06S4009@gmail.com | 東邦大・医・生物 | 2008-05-16(Fri) | 6000 |
| 宇山秀樹 | 06S4008@gmail.com | 東邦大・理・生物 | ||
| 坂場三男 | 06S4064@gmail.com | 東北大・院・生命科学 | 2008-05-16(Fri) | 6000 |
というテーブルが得られることを期待している。上をみると、2番目の人は払いすぎ、4番目の人は払っていないことがわかる。
エクセルにあるデータをSQLiteで処理するには、まず、SQLiteを起動してテーブルを作成し、エクセルのデータを入れなければならない。
エクセルの表のデータをSQLiteに移す そのために、SQLiteを立ち上げ、エクセルの表に対応するテーブルを作る
dir
> cd z:\sqlite Z:\sqlite>ついでに、dirと打って、sqlite3.exeというファイルが入っていることを確認しておこう。
Z:\sqlite>dir文字がいろいろと表示されますが、その中に
2007/06/19 02:45 375,974 sqlite3.exeという行があれば、準備は完了です。
Z:\sqlite>sqlite3上のように入力して、下のようなメッセージが表示されたら、正常に起動されている。
SQLite version 3.5.9 Enter ".help" for instructions sqlite>
sqlite>quit.を入力するのを忘れないように。これでプロンプトは
Z:\sqlite>に戻ったはず。
データベース名： ex1.db （コマンドプロンプトから「sqlite3 データベース名」で作成
テーブル: web (起動後（プロンプトはsqlite> ）SQL命令（create table...)を入力
テーブル: ginko (起動後（プロンプトはsqlite> ）SQL命令（create table...)を入力
※１つのデータベースは１つまたは複数のテーブルがあつまってできている
エクセルのそれぞれの表は、データベースのテーブルのそれぞれに相当
データベースを作成する命令: Windowsのコマンドプロンプトから、次のように入力。
Z:\sqlite>sqlite3 ex1.db
sqlite3 ＜データベース名＞は、新しいデータベースを作ったり、もしすでに作成済みのデータベースがあれば、それを開く命令。上の命令を入力すると、画面がSQLiteのコマンドプロンプト（sqlite>）に変わる。 (注意：このとき、ex1.dbというデータベースは、z:\sqlite\というフォルダの中にできている）
sqlite>になっているのを確認して、エクセルの表に対応するテーブルを２つ作る。 今はex1.dbという名前のデータベースに対していろんな命令を与える準備ができている。今から入力する命令は、ex1.dbというデータベースの中にテーブルを作る命令。作成するテーブルには、それぞれ次のようなデータを入れる。テーブルの作成には、列の名前であるカラム名が必要
web:
| name | name_kana | shozoku | |
| 金田勝年 | カネダカツトシ | インディアナ大学・植物 | 06S4043@gmail.com |
| 山本条太 | ヤマモトジョウタ | 愛知教育大・生物 | 06S4076@gmail.com |
| 河野章 | コウノアキラ | 横浜市大・院・総合理学研究科 | 06S4025@gmail.com |
ginko:
| date | ginko_name | amount |
| 2008-05-09(Fri) | イワイフミオ | 6000 |
| 2008-05-09(Fri) | マツナガカズヨシ | 6000 |
| 2008-05-09(Fri) | イチカワトミコ | 6000 |
sqlite> create table web (name, name_kana, shozoku, email);意味は、「name, name_kana, shozoku, email という列（カラム）を持ったwebとういテーブルを作りなさい」というもの。命令はけっこう長いし、入力まちがいがあると、うまく動かない。でも、間違ってしまった場合、↑キーでもう一度命令を呼び出して編集できる。
sqlite> drop table web;
sqlite> .tableと入力すれば見ることができる。.で始まるコマンドのことを、ドットコマンドという。ドットコマンドはSQLiteでのいろんな設定を行います。
sqlite> create table ginko (date, ginko_name, amount);
エクセルのテーブル(Web登録データ） → K2Editorでタブ区切りテキストファイルに → SQLiteに読み込み
web.txt と ginko.txtという名前で、「マイドキュメントのsqliteフォルダ」に保存。
sqlite> .mode tab
sqlite> .import 'z:\sqlite\web.txt' web同様の操作を、もう一つのテーブル ginko についても行う。
sqlite> .import 'z:\sqlite\ginko.txt' ginko
webというテーブルから、 全てのデータを 選び出せという命令は、SQL文を使うと
select * from web;と書くことができる。「全て」に「*」を使うのは、正規表現でおなじみだ。では、下の２つの命令文を実行してみよう。
sqlite> select * from web; sqlite> select * from ginko;このとき、*の部分をカラム名にすると、選んだカラムの内容だけが表示される
sqlite> select name from web;
1. テーブル web から 氏名とメールアドレスを名前
注： order by カラム名でそのカラム名を使った並べ替えができる
select name, name_kana, ____ from ____ order by name_kana;
2. テーブル ginko から 振り込み金額が6000円では無い人の名前と金額を表示
注： where ＜条件式＞ で条件式に一致したデータだけが表示される
演算子： = < > !=（異なるという意味）
select ginko_name, ___ from ___ where amount != '6000';
3. テーブル webから氏名だけを10件だけ抽出
注： limit 数字 で数字の数の件数だけデータを表示できる
select ___ from web limit __;select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name;
意味は、
webというテーブルとginkoというテーブルをnama_kanaとginko_nameが同じであるデータを連結 name, email, shozoku, date, amountを表示
1. ginkoの全てのデータにたいして、ヨミガナが一致するデータをwebと連結し、
振り込み者名、日付、振り込み金額、メールアドレスを、振り込み者名で並べ替えて表示
select ginko_name, ___, amount, email from ___ left outer join ___ on ___ = ___ order by ____;
2. 上の命令に以下を追加し、払込金額が6000円の人だけを表示させてみよう
where ____ = '___'
これで、今回の挑戦の最終目標である、２つのレコードの連結はできた。このデータをエクセルで見やすく表示するには、
検索文字列: +（空白の連続) 置換文字列: ¥tでタブ区切りに変更。全てを選択してコピー
上で行った操作とコマンドをまとめておこう。
SQLの最後は ; （半角セミコロン）で終わる ドットコマンドの最初は . （半角ドット）で始まる （セミコロンは不要） 値は ' ' （シングルクォーツ）で囲む （※省略できる場合も多々ある）
Z:\sqlite> ''sqlite3 ex1.db''
create table web (name, name_kana, shozoku, email);
.mode tab
.import 'z:\sqlite\web.txt' web
select * from web;
select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name;
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)ここまでで説明した命令文についてもっと知りたいときは、
以上、駆け足でSQLiteの使い方を見てきたが、データベースはどのパソコンでも使えるし、大量データの一括処理という意味では、これに勝る方法は無いのだが、一般ユーザーにはあまり使われていないのが現状だろう。皆さんも、今回の挑戦で行った操作をなんとなくで良いから覚えておき、今後のデータ処理に役立てて欲しい。
#ヘルプと現在の設定を表示
.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を実行
*第13回授業・基本課題 **氏名： **課題への回答 -今日の授業の進み方は？（はやい、丁度いい、おそい） --回答： -今日の授業の難しさはどう感じましたか（簡単 丁度いい 難しい）： --回答： -難しいと答えた人は、特にどの点が難しかったですか？： --回答： -今日の授業は（分かった 半分ぐらいは分かった 分からなかった）： --回答： -分からないと答えた人は、特にどの点が分からなかったですか？： --回答： -今日の講義で分からなかった用語があったら挙げてください： --回答： -授業に関する要望・質問があったらなんでもどうぞ： --回答： -課題2の答え --2-1: --2-2: -課題3の答え:
select ____ from web order by email limit _;
select ____, ____ from ____ left outer join ginko on ____ = ginko_name ____ by ____ limit 5;