データが含まれていれば、それはテキストファイルでも、画像ファイルでも、データベースと呼んでいいでしょう。iTunesでも音楽データを管理するのにデータベースを使っていますし、アーティスト-アルバム-楽曲というフォルダ構成も、データベースといってもいいかもしれません。

既にファイルの読み書きの方法を知っていればデータベースの作成は原理的に可能です。実際、世の中のデータベース管理システム(DBMS: Database Management System)は、突き詰めれば、ファイルの読み書きで実装されています。

ただし、ディスク上のファイルは一次元配列でしかありません。その一本の配列にのようにデータを書き込めば、データを壊すことなく保存でき、再び取り出せるようになるのか。また、新しいデータの追加できるようにデータの配置を管理するなど、単にディスクにデータを保存するといっても、さまざまな工夫が必要です。さらに、データの検索を早くするために、HashMap, TreeMapなどの索引構造を、ディスク上に作成する必要があります（索引のためのデータ構造としてはB+-treeを使うのが一般的です）。

ですから、単純なフォルダやファイルというよりも、コンピューターから高速にデータを検索、更新しやすい形で整理・保存されているものを、一般にデータベースということが多いようです。そして、データベースを作成、更新、検索するためのシステム、ソフトウェアをデータベース管理システム(DBMS)と言います。

今回の演習で扱うデータベースは、リレーショナルデータベース（Relational Database, 関係データベース)というもので、表（テーブル)形式でデータを持つデータベースです。

staff table

id	name	email
1	Taro L. Saito	xxx@xxx.xxxxx
2	Yoichiro Nakatani	yyy@xxx.xxxxx
3	Jun Yoshimura	zzz@xxx.xxxxx

lecture table

person_id	name
1	情報生命科学演習
1	生物情報科学実習
2	生物情報科学実習
3	生物情報科学実習

このテーブルデータは、講義を担当しているstaffのリストと、それぞれが担当している講義lectureを表にしたものです。

このようなテーブルデータを作るために、リレーショナルデータベースシステムに SQL言語を用いて、上記のフォーマットのテーブルを作りなさい、行データを追加しなさいという指示を出すことになります。

SQL (エスキューエル。海外ではsequel(シークェル)と呼ぶ人が多い）は、問い合わせ言語(query language)と言われ、データベースからデータを取り出す命令を記述できます。

今回の演習ではSQLite http://www.sqlite.org/ を使うので、こちらのSQLの構文マニュアル(http://www.sqlite.org/lang.html )を参考にしてください。

上記のテーブル形式のデータを作成するには、create table文を使います。

create table staff (
  id integer primary key not null,
  name text,
  email text
);

create table文の書式は以下の通りです

create table テーブル名 (
  (カラム名) (データ型名）(型の制約...)? ,
  ...
);

Javaのクラス定義の仕方と似ていますが、使えるデータ型の種類がデータベースシステム毎に決まっています。通常、いろいろなシステムに共通して使えるのは以下のデータ型です。

integer   整数
real　　　浮動小数点数 
text　　　文字列
datetime  日時を表す型

id integerの後ろについているprimary key (主キー)という指定は、その列の値(id)は、重複してはいけない、ということを意味します。すなわち、テーブル中に同じidの値を持ったデータが存在してはいけない、という意味です。primary keyは、テーブル中から、一意に行を特定するために使われます。

not nullは、空っぽのデータをその列に入れてはいけないという指示です。

lectureテーブルを作るSQLは、以下のようになります。

create table lecture (
  person_id integer not null,
  name text  
);

lectureテーブルでは、person_idは重複があってもOKです。

テーブルを作成後、insert文を使い、データを挿入していきます。

insert into staff values('1', 'Taro L. Saito', 'xxx@xxx.xxxxx');
insert into staff values('2', 'Yoichiro Nakatani', 'yyy@xxx.xxxxx');
insert into staff values('3', 'Jun Yoshimura', 'zzz@xxx.xxxxx');

insert into lecture values('1', '情報生命科学演習');
insert into lecture values('1', '生物情報科学演習');
insert into lecture values('2', '生物情報科学演習');
insert into lecture values('3', '生物情報科学演習');

insert文の書式は以下の通り

insert into テーブル名 values('列データ', ...)

データベースの検索にはselect文を使います。

select カラム名, ... from テーブル名,... 
where 検索条件 
order by 順序指定 
limit 取得する件数 offset スキップする件数

where, order by, limit offsetなどは、オプションです。

以下は、sqliteデータベースでSQLを実行した例

sqlite> select * from staff;
1|Taro L. Saito|xxx@xxx.xxxxx
2|Yoichiro Nakatani|yyy@xxx.xxxxx
3|Jun Yoshimura|zzz@xxx.xxxxx

sqlite> select id,email from staff;
1|xxx@xxx.xxxxx
2|yyy@xxx.xxxxx
3|zzz@xxx.xxxxx

sqlite> select * from staff where id = 3;
3|Jun Yoshimura|zzz@xxx.xxxxx

where文を使うと条件を指定してテーブルデータを検索できます。

where節中でand, orを使うと複雑な条件を記述できます。

sqlite> select * from staff where name = 'Taro L. Saito' and email = 'xxx@xxx.xxxxx';
1|Taro L. Saito|xxx@xxx.xxxxx

sqlite> select count(*) from staff;
3

sqlite> select count(*) from staff where id = 3;
1

sqlite> select * from staff order by name;
3|Jun Yoshimura|zzz@xxx.xxxxx
1|Taro L. Saito|xxx@xxx.xxxxx
2|Yoichiro Nakatani|yyy@xxx.xxxxx

descをつけると、降順になります。

sqlite> select * from staff order by name desc;
2|Yoichiro Nakatani|yyy@xxx.xxxxx
1|Taro L. Saito|xxx@xxx.xxxxx
3|Jun Yoshimura|zzz@xxx.xxxxx

データ数が大きくなると、検索結果を10個に限定したり、先頭からいくつかデータをスキップしたいという場合があります。

sqlite> select * from staff limit 2;
1|Taro L. Saito|xxx@xxx.xxxxx
2|Yoichiro Nakatani|yyy@xxx.xxxxx

sqlite> select * from staff limit 2 offset 2;
3|Jun Yoshimura|zzz@xxx.xxxxx

from部分に二つ以上テーブルを書きます

sqlite> select staff.id, staff.name, lecture.name from staff, lecture where id = person_id;
1|Taro L. Saito|情報生命科学演習
1|Taro L. Saito|生物情報科学演習
2|Yoichiro Nakatani|生物情報科学演習
3|Jun Yoshimura|生物情報科学演習

left join（左側のテーブルデータの各行に対して、右側で指定されたテーブルデータを条件に基づいて結合していく）を使って書くこともできる。

sqlite> select staff.id, staff.name, lecture.name from staff left join lecture on id = person_id;
1|Taro L. Saito|情報生命科学演習
1|Taro L. Saito|生物情報科学演習
2|Yoichiro Nakatani|生物情報科学演習
3|Jun Yoshimura|生物情報科学演習

上のjoinで得られた結果に対して、staff.idが同じ行を、ひとつの行にまとめる. (group by staff.id)

sqlite> select staff.id, staff.name, group_concat(lecture.name, ',') from staff left join lecture on id = person_id group by staff.id;
1|Taro L. Saito|情報生命科学演習,生物情報科学演習
2|Yoichiro Nakatani|生物情報科学演習
3|Jun Yoshimura|生物情報科学演習

group_concat(lecture.name, ',') は、集約演算で、文字列のリストを、','でつないで結合する、という意味。

group_concat以外にも、avg, count, min, max, sumなどで、複数のデータを集約することができます。group byを使って複数行を１つにまとめるときは、まとめられる行には集約演算を使わなくてはなりません。

select 集約演算(列名) from ..　group by グループを決めるための列

exam_score table

id	gender	class	score
1	female	A	50
2	male	A	30
3	male	B	80
4	female	B	70
5	female	C	90
6	male	C	75

sqlite> select class, avg(score) from exam_score group by class;
A|40.0
B|75.0
C|82.5

sqlite> select class, avg(score), min(score), max(score), group_concat(id) from
exam_score group by class;
A|40.0|30|50|1,2
B|75.0|70|80|3,4
C|82.5|75|90|5,6

性別ごと

sqlite> select gender, avg(score) from exam_score group by gender;
female|70.0
male|61.6666666666667

各クラスの性別ごとに集約 (group byのあとに2つ以上つなげる）

sqlite> select class, gender, avg(score) from exam_score group by gender, class;
A|female|50.0
B|female|70.0
C|female|90.0
A|male|30.0
B|male|80.0
C|male|75.0

行データの一部分を更新

sqlite> insert into staff values('4', 'Atsushi Sasaki', 'aaa@bbb.ccc');
sqlite> select * from staff where id = '4';
4|Atsushi Sasaki|aaa@bbb.ccc

sqlite> update staff set email = 'zzz@xxx.xxx' where id = '4';
sqlite> select * from staff where id = '4';
4|Atsushi Sasaki|zzz@xxx.xxx
sqlite>

setのあとには、複数の値の設定をカンマでつなげて記述できる。

drop table if exists (テーブル名);

insert into (テーブル名) (select文）

• http://www.sqlite.org/download.html
• Windows版　http://www.sqlite.org/sqlite-3_6_1.zip
• sqlite3.exeが含まれる。sqlite3.exeを、環境変数PATHから見える位置に置くと、コマンドプロンプトやcygwinから使えるようになります。
• PATH変数はコマンドプロンプトからは、PATHコマンド. cygwinからはecho $PATHで確認できます。

sqlite3 (作成したいデータベースファイル名、あるいは既存のデータベースファイル名)

で起動できます。ファイル名を入力しないとデータベースはメモリ上に作成されるだけで、sqlite終了後にはデータは消えてしまいます。

> sqlite3 staff.db
SQLite version 3.5.1
Enter ".help" for instructions
sqlite>

sqlite> .help
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode

sqlite> create table staff (id integer primary key not null,  name text, email text);
sqltie> insert into staff values('1', 'Taro L. Saito', 'xxx@xxx.xxxxxx');
sqlite>

sqlite> select * from staff;
1|Taro L. Saito|xxx@xxx.xxxxxx
sqlite>

sqlite> .quit

sqlite> .tables
staff
sqlite>

sqlite> .schema
CREATE TABLE staff (id integer primary key not null, name text, email text);
sqlite>

sqlite> .read score.sql

drop table if exists exam_score;
create table exam_score (
  id integer primary key autoincrement not null, 
  gender text,
  class text,
  score integer
);

drop table if exists exam_score;

は、exam_scoreテーブルが存在していたら、テーブルを消す、という意味。

1	female	A	50
2	male	A	30
3	male	B	80
4	female	B	70
5	female	C	90
6	male	C	75

.separatorでタブを区切り文字に指定。.importでテーブルに、ファイルからデータを読み込む。

.separator \t
.import score.tab exam_score