授業/H21/情報処理/13 のバックアップソース(No.3)

• バックアップ一覧
• 差分 を表示
• 現在との差分 を表示
• バックアップ を表示
• 授業/H21/情報処理/13 へ行く。
  • 1 (2009-07-03 (金) 08:42:32)
  • 2 (2009-07-03 (金) 10:34:18)
  • 3 (2009-07-09 (木) 14:20:22)

*&size(24){&color(red){編集中です！};};大量データの一括処理：SQLiteによるリレーショナルデータベース入門 [#k125ef47]

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

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

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

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

というのが、データベースには簡単にできて、テキストエディタやワープロには簡単にできないことだ。~
おそらく、今現在の時点でデータベースを使うことはあまりないだろうが、この技術は知っておいて、絶対に損はない。将来的に自由に使えるようになれば、心と時間に余裕が生じるだろう。

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

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


**第13回授業の獲得目標：&worried; [#af6a69b9]
-''1. リレーショナルデータベース（SQLite）をインストールする''
-''2. コマンドプロンプトの利用法と、コンピュータにおけるディレクトリ構造を修得する''
-''3. SQLという言語を使って、データベースとお話しする''
--SQLiteを使って、情報の検索・抽出・並び替えを行う方法を習得する
-''4. データベースにおけるテーブルの概念を理解する''
-''5. SQLを用いて、複数のデータテーブルを連結して、必要なデータだけを抽出する方法に挑戦する''

**1. リレーショナルデータベース（SQLite）をインストールする [#v303f1ed]

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

まずはリレーショナルデータベースシステムを利用するために必要なフリーソフトウェア''SQLite''をインストールする。これは予習課題でやって貰っているはずだ。なお、皆さんが大学で使っているコンピュータにはAccessというデータベースソフトも入っているが、自分のパソコンでも無料で使えるという点を重視して、この授業ではSQLiteを使うことにした。SQLiteはダウンロードした実行ファイルをクリックするだけで、データベースを扱うことができるし、データベースのデータ本体が１つのファイルに保存されるので、コピーやバックアップが簡単にできるという特徴を持っている。

***SQLiteのインストール [#pdb38014]
では、以下のリンクから、SQLiteの実行形式ファイルをダウンロードしよう。
-SQLite3 WindowsXP用実行ファイル http://www.sqlite.org/sqlite-3_6_16.zip~
ダウンロードが成功すればデスクトップにsqlite3.exeというファイル&ref(授業/H19/情報処理/13/WS000006.JPG);ができているはず。
~次に、
 マイドキュメント　フォルダの下に
  sqlite  という名前のフォルダを作って
   sqlite3.exe   をその中に移動する。
~※この操作は別にやらなくてもSQLiteは動くが、後々データベースファイルがどこにあるのか探すのが面倒なので、あらかじめ入れ場所を作った。~
今後、SQLiteを使うときには、
 データやデータベースは全て、　マイドキュメント/sqlite　というフォルダに入れて使うことにする
-これでインストールは完了した。

***SQLiteについて自習する場合は、以下のサイトを参照してみよう [#z1fa93e8]
-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

**2. コマンドプロンプトの利用法と、コンピュータにおけるディレクトリ構造を修得する [#ofa16540]
さて、いよいよSQLiteを使ってデータベースを操作するのだが、このとき必要になるのが、
 コマンドプロンプト
というものだ。
***「すべてのプログラム／アクセサリ／コマンドプロンプト」を起動 [#te487651]
まず''コマンドプロンプト''というソフトウェアを起動しよう。
~ウィンドウズのスタートメニューから、「すべてのプログラム／アクセサリ／コマンドプロンプト」を選ぶ。~
&ref(授業/H19/情報処理/13/WS000009.JPG);~
そうすると黒いウィンドウが開き、Rで使ったようなプロンプト
 >
が表示される。これはWindowsXPというOS（オペレーションシステムという、コンピュータの基本ソフト）に、キーボードから命令を入力する処理系だ。普段、皆さんが使っている処理は、''「マウスをクリックすることでコンピュータに命令を与えている」''わけだが、コマンドプロンプトでは、''「文字を入力することで、コンピュータに命令を与える」。''試しに下の囲みのように
 >notepad
と入力すると、WindowsXPの「メモ帳」が起動する。
つまり、&size(18){''文字を入力することでコンピュータに命令を与えた''};わけだ。
~
実は、マウスをクリックしてコンピュータに命令を与えるインターフェース（ヒトと機会を繋ぐもの）は、コンピュータのハードウェアやOSに依存する部分が多いので、ソフトウェアを開発するプログラミングがそれだけ面倒になる。でも、今見ている''コメンドプロンプト''は、文字で命令を与えるので、そういう面倒な部分のプログラミングが必要ない。そのため、コマンドプロンプトを採用する方が、ソフトウェアの開発にかかる手間が減るわけだ。~

 演習：　コマンドプロンプトで命令を入力して、フォルダ（または、ディレクトリと言う）の中身を表示してみよう
 > dir

**3.データベースとお話しよう：　 SQLという言語の利用 [#s51b70b5]
では、今から、SQLiteを使って、データベースと''お話し''してみる。~
「お話し」などという言い方をすると、なんだか曖昧に聞こえるかもしれないが、これからやるのは、
 「データベースから生物学科の先生の名前だけを抜き出して」
 　とか、
 「データベースから教授の名前だけを抜き出して」
などという命令を、データベースに英語で与えるものだ。
***サンプルデータベースの準備 [#d7981416]
-&ref(./meibo);~
ダウンロードしたファイルを、上で作ったsqliteというフォルダの中に入れておこう。~
ファイルエクスプローラーを立ち上げて、マウスを使って、 meiboを
 マイドキュメントの下のsqliteというフォルダ
に入れておこう。

***SQLite3の起動~ [#q1712586]
+''カレントディレクトリの移動''：　~
コマンドプロンプトで、''自分が今いる閲覧している場所''の中を、もう一度見てみよう。
 > dir
そうすると、ここには先ほどだるンロードした、''meibo''というデータベースファイルは入っていないことが分かる。~
そこで、まず、自分がコマンドプロンプトから閲覧しているディレクトリを 次の命令を使って、''Z:\sqlite'' に変更する。そうすると、プロンプトのところが、Z:\sqlite> に変わる。
 > cd z:\sqlite
 Z:\sqlite>
ついでに、dirと打って、''sqlite3.exe''と''meibo''というファイルが入っていることを確認しておこう。
  Z:\sqlite>dir
+''sqlite3を使ってmeiboデータベースを開く''：~
ここまで準備ができたら、
 >sqlite3  meibo
と入力すると、SQLiteが起動し、先ほどダウンロードしたmeiboというデータベースファイルが開かれる

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

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

**SQLiteを使って、２つ以上のテーブルを連結して、データを抽出する方法に挑戦！！⌣ [#m282efdc]
これから実際にSQLiteを使って何ができるかを体験してもらう。目標とするのは、次のようなケースを簡単にSQLiteで簡単に処理することだ。
~
-''モデルケース''（自分が学校の先生になったことを想像してみる）：
今手元に、国語、数学、英語の先生から提出された成績をまとめた書類がある。~
&ref(./成績集計表.doc);~
この成績表には学籍番号と成績がそれぞれ書かれている。これからあなたのクラスの学生全員の、成績の集計をしたい。生徒の名簿は、~
&ref(./名簿.doc);~
という名簿のファイルに、「''学籍番号、名前、よみがな''」が保存されている。~
さて、学生毎に選択している科目が違うのだが、３人の先生から集めた情報を１つ集計するにはどうすればいいだろうか？~
（※じつは、この程度の数のデータなら、エクセルでもわりと簡単に集計できる＜SQLiteを使うと、この数十倍の規模のデータでも、簡単に処理できる＞）
***テーブル作成の準備 [#r09450fd]
まず、テーブルを確認しておく。''テーブル''には、上の例で示したような''カラム名''が必要になる。それぞれ、タイプしやすいようなカラム名にしておくといいだろう。
|テーブル|テーブル名|カラム|h
|名簿|meibo |gakuseki, namae, yomi|
|国語|kokugo|gakuseki,score|
|数学|sugaku|gakuseki,score|
|英語|eigo|gakuseki,score|

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


２つのシート（下の方にあるタブ）　「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;
~
では、２つのテーブルが連結できるところまで、話しを進めてみよう。全体の流れは次のようになる。

|SIZE(16): 1. エクセルの表のデータをSQLiteのテーブルに移す|
|SIZE(14):　1-1. SQLiteを起動し、webとginkoというテーブルを作成する|
|SIZE(13):COLOR(blue):　　create table web (name, name_kana, shozoku, email);|
|SIZE(13):COLOR(blue):　　create table ginko (date, ginko_name, amount);|
|SIZE(14):　1-2. エクセルのデータをK2Editorにコピーし、タブ区切りテキストにしておく|
|SIZE(13):COLOR(blue):　　web.txt, ginko.txtという２つのファイルを作る|
|SIZE(14):　1-3. SQLiteで、webとginkoというテーブルに、web.txt, ginko.txtの内容をそれぞれインポートする|
|SIZE(13):COLOR(blue):　　import 'z:\sqlite\web.txt' web|
|SIZE(13):COLOR(blue):　　import 'z:\sqlite\ginko.txt' ginko |
|SIZE(16):2. SQLiteの２つのテーブルで、ヨミガナが同じデータ同士を連結する|
|SIZE(14):　2-1. select  ... left outer join ...という命令で２つのテーブルを連結|
|SIZE(13):COLOR(blue):　　select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name;|
|SIZE(14):　2-2. 得られた結果をK2Editorにコピーし、空白の連続をタブに変換して、エクセルで閲覧|

上のような操作で２つのテーブルを連結することで、次のような表が得られ、Web登録をしたのに銀行振り込みしていない人がいるかどうかや、間違った金額を支払っている人がどれくらいいるかなどを知ることができる。
|name|email|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の起動：コマンドプロンプトから起動し、テーブルを作成 [#f80ff570]
エクセルにあるデータをSQLiteで処理するには、まず、SQLiteを起動してテーブルを作成し、エクセルのデータを入れなければならない。
   エクセルの表のデータをSQLiteに移す
  そのために、SQLiteを立ち上げ、エクセルの表に対応するテーブルを作る

-まず''コマンドプロンプト''を起動。ウィンドウズのスタートメニューから、「すべてのプログラム／アクセサリ／コマンドプロンプト」を選ぶ。~
&ref(授業/H19/情報処理/13/WS000009.JPG);~
そうすると黒いウィンドウが開き、Rで使ったようなプロンプトが表示される。これはWindowsXPというOSを動かすもとになっているDOSのコマンドプロンプト（キーボードから命令を入力する処理系）。ここに命令を文字で入力することで、コンピュータに命令を与えることができる。試しに下の囲みの中の３文字を入力すると、ディレクトリの内容一覧が表示される。
 dir
-SQLiteを起動する前に、コマンドプロンプトからまず、カレントディレクトリ(現在、自分が閲覧しているディレクトリのこと）をZ:\sqlite に変更する。そうすると、プロンプトのところが、Z:\sqlite> に変わる。
 > cd z:\sqlite
 Z:\sqlite>
ついでに、dirと打って、sqlite3.exeというファイルが入っていることを確認しておこう。
  Z:\sqlite>dir
文字がいろいろと表示されますが、その中に
 2007/06/19  02:45           375,974 sqlite3.exe
という行があれば、準備は完了です。

-&size(14){SQLiteの起動};~
では、コマンドプロンプトからSQLite3を起動して、ついでに終了の方法も覚えよう。（手続き通りに''終了''しないとデータが消えてしまうことがあるので、要注意）
-''起動''
  Z:\sqlite>sqlite3
上のように入力して、下のようなメッセージが表示されたら、正常に起動されている。
 SQLite version 3.5.9
 Enter ".help" for instructions
 sqlite>
-''終了''　　終了は''sqlite>''というプロンプトの後に''.quit''か''.exit''と入力。
 sqlite>quit
''.''を入力するのを忘れないように。これでプロンプトは
  Z:\sqlite>
に戻ったはず。


-&size(14){SQLiteの起動とデータベース ex1 の作成（作成済みのデータベースの場合、オープン）};~
それではいよいよ、データベース中にデータを保存するテーブルを作ろう。今回のテストケースで作るデータベース以下のような構成になっている。

 データベース名：　　　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\というフォルダの中にできている）


-&size(14){SQLiteのシステムの中で、データベースに命令を与える}; ~
ここから先は、実際にSQLを入力してデータベースの操作を行う。

-&size(14){テーブルの作成};~
プロンプトが
 sqlite>
になっているのを確認して、エクセルの表に対応するテーブルを２つ作る。
今はex1.dbという名前のデータベースに対していろんな命令を与える準備ができている。今から入力する命令は、ex1.dbというデータベースの中にテーブルを作る命令。作成するテーブルには、それぞれ次のようなデータを入れる。テーブルの作成には、列の名前である''カラム名''が必要
~web:
|name|name_kana|shozoku|email|h
|金田勝年 |カネダカツトシ|インディアナ大学・植物|06S4043@gmail.com|
|山本条太 |ヤマモトジョウタ|愛知教育大・生物|06S4076@gmail.com|
|河野章 |コウノアキラ|横浜市大・院・総合理学研究科|06S4025@gmail.com|
~ginko:
|date|ginko_name|amount|h
|2008-05-09(Fri)|イワイフミオ|6000|
|2008-05-09(Fri)|マツナガカズヨシ|6000|
|2008-05-09(Fri)|イチカワトミコ|6000|
テーブルの作成に使うSQL命令は、''create table ＜テーブル名＞ (カラム名, カラム名...);''。  sqlite> 以下をコピーペーストしよう。
 sqlite> create table web (name, name_kana, shozoku, email);
意味は、「name, name_kana, shozoku, email という列（カラム）を持ったwebとういテーブルを作りなさい」というもの。命令はけっこう長いし、入力まちがいがあると、うまく動かない。でも、間違ってしまった場合、↑キーでもう一度命令を呼び出して編集できる。~
&size(16){良くやる間違い：　命令文の最後のセミコロン '';'' を忘れる人が多い!};
~
作成したテーブルを削除してもう一度やり直したい場合は、
 sqlite> drop table web;
~
とやれば先ほど作ったwebというテーブルを消すことができる。作成済みのテーブルの一覧は
 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]]という。ドットコマンドはSQLiteでのいろんな設定を行います。
~
上と同様にして、もう一つのテーブル、ginkoも作っておこう。コマンドは下の通り。
 sqlite> create table ginko (date, ginko_name, amount);

-&size(14){''エクセルからSQLiteへのデータの移動''：　タブ区切りテキストファイルを介して一括登録};~
エクセルのデータをSQLiteに移動させるには、一度、タブ区切りテキストファイルを作成する必要がある。
 エクセルのテーブル(Web登録データ） → K2Editorでタブ区切りテキストファイルに → SQLiteに読み込み
--エクセルの表からデータ部分をコピーして、K2Editorの新しい文書にペースト。２つの表をそれぞれ、
 web.txt  と  ginko.txt
という名前で、「マイドキュメントのsqliteフォルダ」に保存。
--《''重要''》　区切り文字の設定：~
次にSQLiteに移動して区切り文字をタブに設定する。この設定を忘れると、うまくファイルが読み込まれない。
 sqlite> .mode tab
--SQLiteへのデータのインポート：　先ほど作成したテキストファイルから、データをテーブルに読み込む。また、''select''命令を使って内容を一覧。
 sqlite> .import 'z:\sqlite\web.txt' web
同様の操作を、もう一つのテーブル ginko についても行う。
 sqlite> .import 'z:\sqlite\ginko.txt' ginko

-&size(14){''SQL文を使ったデータの検索と表示};~
SQLiteでは、リレーショナルデータベースを操作するための標準的な命令文であるSQL文を使う。簡単な英単語を並べたものになっており、例えば、
 webというテーブルから、　全てのデータを　選び出せ
という命令は、SQL文を使うと
 select * from web;
と書くことができる。「全て」に「*」を使うのは、正規表現でおなじみだ。では、下の２つの命令文を実行してみよう。
 sqlite> select * from web;
 sqlite> select * from ginko;
このとき、*の部分をカラム名にすると、選んだカラムの内容だけが表示される
  sqlite> select name from web;
-演習：下の空欄を埋めてSQL文を完成させ、指定のデータ抽出をやってみよう
 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 __;

-&size(14){''キーを用いた２つのテーブルの連結};~
エクセルからデータベースに２つのテーブルを移動することができたので、いよいよ、両者に共通する「ヨミガナ」で、２つのテーブルを結びつけてみよう。２つのテーブルの連結には、次のようなSQL文を使う。
~
> 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を表示

-演習：下の空欄を埋めてSQL文を完成させてみよう
 1. ginkoの全てのデータにたいして、ヨミガナが一致するデータをwebと連結し、
     振り込み者名、日付、振り込み金額、メールアドレスを、振り込み者名で並べ替えて表示
  select ginko_name, ___, amount, email from  ___ left outer join ___ on ___ = ___ order by ____;
 2. 上の命令に以下を追加し、払込金額が6000円の人だけを表示させてみよう
   where ____ = '___'
 
これで、今回の挑戦の最終目標である、２つのレコードの連結はできた。このデータをエクセルで見やすく表示するには、
--得られた結果をコピーして、K2Editorにペースト
  検索文字列:   +（空白の連続)
  置換文字列:   ¥t
でタブ区切りに変更。全てを選択してコピー
--エクセルにペースト

***先ほどの操作のまとめ [#f5d55174]
上で行った操作とコマンドをまとめておこう。
 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の例は参考になる

-※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)

***命令文を知りたい時は [#f611f94c]
　ここまでで説明した命令文についてもっと知りたいときは、
-SQLite　SQL日本語リファレンス：http://www.net-newbie.com/sqlite/lang.html
を見てください。

以上、駆け足でSQLiteの使い方を見てきたが、データベースはどのパソコンでも使えるし、大量データの一括処理という意味では、これに勝る方法は無いのだが、一般ユーザーにはあまり使われていないのが現状だろう。皆さんも、今回の挑戦で行った操作をなんとなくで良いから覚えておき、今後のデータ処理に役立てて欲しい。



***ドットコマンドの説明（よく使うものだけを抜粋） [#h3e4aea2]
 #ヘルプと現在の設定を表示
    .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回授業の課題 [#r0fe932d]
-提出期限：''7月17日水曜正午''（提出期限を水曜にしました）
***課題１．意見調査 [#g7be9276]
+&size(16){http://bean.bio.chiba-u.jp/joho/index.php?joho20 に、「自分のID」/13　という新しいページを作成し、下の囲みの中にあるアンケートをコピー・ペーストして、「回答：」の後に答えを書き込むこと。};

 *第13回授業・基本課題 
 **氏名：
 **課題への回答
 -今日の授業の進み方は？（はやい、丁度いい、おそい）
 --回答：
 -今日の授業の難しさはどう感じましたか（簡単　丁度いい　難しい）：
 --回答：
 -難しいと答えた人は、特にどの点が難しかったですか？：
 --回答：
 -今日の授業は（分かった　半分ぐらいは分かった　分からなかった）：
 --回答：
 -分からないと答えた人は、特にどの点が分からなかったですか？：
 --回答：
 -今日の講義で分からなかった用語があったら挙げてください：
 --回答：
 -授業に関する要望・質問があったらなんでもどうぞ：
 --回答：
 -課題2の答え
 --2-1:
 --2-2:
 -課題3の答え:



***課題2（復習）：SQLiteへの挑戦の結果 [#c89c53e5]
-授業で作成したwebとginkoというテーブルについて、下の指示にあるようにSQL文を完成させ、実行して得られた結果をページに貼り付けなさい
-2-1: webというテーブルから全部のカラムをメールドレスで並べ替えて、最初の5件を表示 
  select ____  from web order by email limit _;
-2-2: webとginkoを氏名のヨミガナで連結させ、払い込み金額で並び替えて、最初の5件の氏名と金額を表示
  select ____, ____ from ____ left outer join ginko on ____ = ginko_name ____ by ____ limit 5;
-評価：
--授業の解説通りにテーブルを作成し、SQL文を実行できているかどうか
--4点で評価

***課題3（予習）：ウェブページの作成と公開 [#bbd02955]
-HTMLファイルを１つ作って、授業で解説した方法に従って作成し、レポート提出ページに添付すること。但し、作成するウェブページには、
--ページタイトルをつけること（<title> </title>というタグで指定する）
--タイトルと内容が一致していること（「自己紹介」というタイトルのページにRの説明だけが書いてあるのはだめ）
--画像の使用も可
--授業で作ったindex.htmlを変更すればできるはず。
-評価：
--細かい指定はしないので、条件さえ満たしていれば、どれだけページを修飾してもらってもいいです
--どれだけ見やすいか・内容が分かりやすいか、どれだけHTML作成に挑戦しているかなど
--3点で評価

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

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.006 sec.