授業/H24/情報処理/15 のバックアップソース(No.1)

• バックアップ一覧
• 差分 を表示
• 現在との差分 を表示
• バックアップ を表示
• 授業/H24/情報処理/15 へ行く。
  • 1 (2012-07-26 (木) 15:14:05)
  • 2 (2012-07-26 (木) 16:03:38)
  • 3 (2012-07-30 (月) 11:23:01)
  • 4 (2012-08-01 (水) 14:32:36)

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

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

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

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

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

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

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

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


**第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]


***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



**3.データベースとお話しよう：　 SQLという言語の利用 ⌣ [#e85f43bf]
では、今から、SQLiteを使って、データベースと''お話し''してみる。~
「お話し」などという言い方をすると、なんだか曖昧に聞こえるかもしれないが、これからやるのは、
 「データベースから生物学科の先生の名前だけを抜き出して下さい」
 　とか、
 「データベースから教授の名前だけを抜き出して下さい」
などという命令を、'''コンピュータが理解できるように'''簡単な英語でデータベースに伝えることだ。

***サンプルデータ [#r4e4f97f]

***SQLite Managerの起動~ [#dbbeeb9f]
+''データベースディレクトリの指定''：　~
+''テーブルの作成''

***SQLiteを使って、データベースとお話しする [#o4a390c2]
ここから先は、''簡単な英語''を使って、データベースとお話しする。これから使う英語の意味をまとめておくと、
 select  　　：選び出して表示しなさい
 from  〜　　：　〜というデータベースから
 　where　　：　　・・・という条件で
というものだ。
 命令の形式さえ守っていれば、コンピュータは内容を分かってくれるので
 変更できるところは自由に変更して楽しんでみよう
-使用例：　selectの簡単な文章：
 書式：　select ＜カラム名,,＞　from ＜テーブル名,,＞ where ＜カラム名＞='＜値＞' order by ＜カラム名＞;
&color(red){''注意''};： &size(18){命令文の最後は必ず '';''（セミコロン）で終わらなければならない。};
~もしも、
 sqlite> select * from rigaku
とセミコロンを抜かして入力してしまうと
   ...>
と表示される。そんなときは、セミコロンを1つだけ入力すると、命令が実行される。
    ...>；
-では、次の命令を入力して、データベースとお話ししてみよう。また、演習問題を解くための命令文を、自分で考えてみよう。
--''select * from rigaku;''　：　「rigakuというテーブルから全てのレコード(注： *は全てのレコードを表す）表示しなさい
--''select * from rigaku order by shoku;''　　：「rigakuというテーブルから全てのレコードを、shokuで並び替えて表示」
--''select shimei, shoku from rigaku;''　：「testというテーブルから、nameとpositionだけを表示しなさい」
--''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件だけ表示」

-なお、今使っている ''meibo''というデータベースの構造は、次のようになっている。
　meio  データベースの構造：
 含まれるテーブル：　rigaku
 テーブル rigaku　のカラム　shimei,　heya,　gakka,　kouza,　shoku,　kenmu,　kenmu_flag
 
 このように、データベースの中にどういうテーブルが入っているか知りたいときは、
 sqlite> .tables
 と入力すると、　rigaku　と表示される。また、rigakuの構造を知りたいときは、
 sqlite> .schema rigaku
 と入力すると、rigakuというテーブルのカラム名（列の名前）が（　）の中に表示される。
 CREATE TABLE rigaku (shimei,heya,gakka,kouza,shoku,kenmu,kenmu_flag);
-では、次の演習問題をやってみよう
 1. 千葉大学理学部生物学科の教員全員を、分野（kouza）ごとに並び替えてで選び出す
 2. 生物学科の教員の氏名と職名と講座名を、講座ごとに選び出し、職名ごとに並べる
 3. 理学部の教授の氏名だけを10件だけ表示させる

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

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

***データベースとテーブルの作成、テキストファイルからのデータの取り込み [#za0f4ba6]
つぎに、''seiseki''という名前でデータベースを作成して、上の４つのテーブルを作成して、データを取り込んでみよう
+コマンドラインから、SQLiteを起動して, seisekiという名前のデータベース作る
 Z:\sqlite>sqlite3 seiseki
+最初に　''タブモード''にしておく &color(red){''重要''};~
SQLiteを立ち上げたときには、データの区切り文字は | になっているので、これを''タブ''に変更しておく。%%%忘れがちなので注意%%%.
 sqlite> .mode tab
 
+上で考えた4つのテーブルを作る
 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　＜テーブル名＞　（　＜カラム名のリスト＞）;''という命令。新しいテーブルは、この命令で作る。
+作成したテーブルに、先ほど作ったex3.txtのデータと、meibo.txtのデータをそれぞれ読み込む。
 sqlite> .import meibo.txt meibo
 sqlite> .import kokugo.txt kokugo
これを、他の科目のについても繰り返す
 sqlite> .import sugaku.txt sugaku
 sqlite> .import eigo.txt eigo
--先ほどの
 .mode tab
や、今行った
 .import ＜テキストファイル名＞　＜テーブル名＞
というコマンドは、''ドットコマンド''と言って、データベースのシステムと、データベース以外のファイルの間でデータのやり取りをする命令。一番最後に'';''（セミコロン）は''不要''なので、注意。
+これでデータが入ったはず。確かめるには、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を使って並び替えも可能。

***データの保存と終了 [#s2bbe096]
プログラムを正常に終了すると、データは自動的にデータベースファイルに保存される。
 sqlite>.quit
これを忘れると、データは消えてしまうので、注意。

***ここまでの操作のまとめ [#k4745525]
上で行った操作とコマンドをまとめておこう。
 SQLの最後は ; （半角セミコロン）で終わる
 ドットコマンドの最初は . （半角ドット）で始まる　（セミコロンは不要）
 値は ' ' （シングルクォーツ）で囲む　（※省略できる場合も多々ある）
+SQLiteの起動とデータベースを作成
 Z:\sqlite> ''sqlite3 ex1.db''
--意味：　ex1.dbというデータベースファイルを作成（または既存の場合は開く）。OSのコマンドラインから入力された。場所はsqliteフォルダの中
+テーブルの作成:SQL:  ''create table''
 create table web (name, name_kana, shozoku, email);
--意味：name, name_kana, shozoku, email という4つのカラムを持つwebというテーブルを作成
+外部ファイルの区切り文字を指定:ドットコマンド: ''.mode''
 .mode tab
--意味：入出力に使われるファイルでは、カラム同士はタブで区切られていると設定する
+外部データのインポート：ドットコマンド ''.import''
 .import 'z:\sqlite\web.txt' web
--意味：sqliteフォルダに入っているweb.txtをテーブルwebに読み込み
+テーブル内容の表示：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の例は参考になる


***ドットコマンドの説明（よく使うものだけを抜粋） [#w2149e31]
 #ヘルプと現在の設定を表示
    .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を実行

**Macユーザーへのコメント [#v8b52093]
Macユーザの人はsqlite3をダウンロードする必要はありません。Macには最初から、sqliteが入っています。
***使い方 [#pb9c6ff5]
-1. Finderから、「移動／アプリケーション／ユーティリティー／ターミナル.app」を開く~
コマンドプロンプトに似た画面が開く
-2. この画面で は$の右側に、コマンドを打ち込むようになっている。試しに
 ls
と入力すると、ホームディレクトリの一覧が表示される~
ホームディレクトリは、どれかのフォルダを開いたときに、左側のカラムに家のマークで示されているディレクトリ。課題のファイルはここに入れるといい。
-3. このページから、meibo.txt, sugaku.txt, kokugo.txt, eigo.txt　をダウンロードして、ホームディレクトリに入れる
-4. ''重要''　MacとWindowsでは、文字コードが違うので、ここで文字コードと改行コードの変更が必要
--Mac用のテキストエディタ, mi（http://mimikaki.net/）をダウンロードして、インストール。
--miで先ほどダウンロードしたファイルを開く
--画面上部に　文字コードと改行コードが
 Shift_JIS     CR+LF(Windows)
と表示されているので、これをMacのターミナル（UNIX)用に変更する
 UTF-8  LF(UNIX)
-5. 後の操作は、Windowsと同じ。ターミナルで
 sqlite3 seiseki
と入力すると、seisekiという名前のあたらしいデータベースが作られる。

Macは最初からUNIX系のソフトウェアが入っているので、とても便利ですね。

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

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

[ トップ ]   [ 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ ]

Site admin: Tadashi Kajita

PukiWiki 1.4.7 Copyright © 2001-2006 PukiWiki Developers Team. License is GPL.
Based on "PukiWiki" 1.3 by yu-ji. Powered by PHP 5.3.29. HTML convert time: 0.004 sec.