SQLite の場合、データベースの本体は通常のファイルになります。データベースの中には、表計算ソフトのようなデータを格納するための表があります。これを「テーブル (table)」といます。テーブルは行と列で構成されていて、一番上の行はデータの項目を格納します。これを「フィールド (field)」とか「カラム (column)」といいます。2 行目から実際のデータを格納します。これを「レコード (record)」といいます。
データベースを簡単にいえば、次に示すような「表」を複数格納したファイルと考えてください。
| 項目１ | 項目２ | 項目３ | 項目４ |
|---|---|---|---|
| データ | データ | データ | データ |
| データ | データ | データ | データ |
| データ | データ | データ | データ |
| 項目１ | 項目２ | 項目３ |
|---|---|---|
| データ | データ | データ |
| データ | データ | データ |
| データ | データ | データ |
| 項目１ | 項目２ | 項目３ | 項目４ |
|---|---|---|---|
| データ | データ | データ | データ |
| データ | データ | データ | データ |
| データ | データ | データ | データ |
| データ | データ | データ | データ |
RDBMS において、データを操作したり定義するための言語を「データベース言語」といいます。データベース言語の国際標準に SQL があり、1992 年に制定された規格を SQL92 といいます。SQLite は SQL92 の多くの機能を実装しています。
SQLite でデータベースを操作する場合、SQLite のコマンドと SQL 文を使って行います。コマンドはドット ( . ) から始まります。SQL 文を使用する場合、文の最後にセミコロン ( ; ) を付けることに注意してください。
データベースは数値や文字列などいろいろなデータを格納することができます。プログラミング言語では、データの種類のことを「データ型 (Data Type)」とか「型 (Type)」いいます。一般的なデータベースでは、カラムを設定するときにデータ型を指定しますが、SQLite はデータ型を指定しなくても動作します。
ただし、SQLite の内部では格納するデータを下表に示す型に分けて管理しています。
| 名前 | 概要 |
|---|---|
| NULL | NULL (空を表す値) |
| INTEGER | 整数値 (1, 2, 4, 8 byte) |
| REAL | 浮動小数点数 (8 byte) |
| TEXT | テキスト |
| BLOB | Binary Large OBject (入力データをそのまま格納) |
SQLite はカラムにデータ型が指定されていない場合、入力されたデータの記述から型を判定します。たとえば、' や " で囲まれていれば TEXT、数字のデータで小数点も指数も無ければ INTEGER、小数点か指数があれば REAL などです。
端末で sqlite3 ファイル名 を入力すると、端末から指定したファイルをデータベースとして操作することができます。ファイル名は何でもよいのですが、本稿ではファイル名の拡張子を sqlite とすることにします。
mhiroi@mhiroi-VirtualBox:~/sqlite$ sqlite3 sample01.sqlite SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. sqlite> .table sqlite>
sample01.sqlite が存在しない場合、新しいファイルが生成されます。既に存在する場合はそのファイルをデータベースとして操作します。.table コマンドはテーブルの一覧を表示します。sample01.sqlite が新しく作成された場合、この中にテーブルは存在しません。
テーブルの生成は create table 文を使います。
create table テーブル名 (カラム名 [データ型], ...);
SQLite の場合、データ型は省略することができます。指定できるデータ型を下表に示します。
| 名前 | 概要 |
|---|---|
| TEXT | テキスト |
| NUMERIC | 数値 (整数と浮動小数点数) |
| INTEGER | 整数 |
| REAL | 浮動小数点数 |
| NONE | 変換しない (入力データをそのまま格納) |
データ型の指定は小文字でもかまいません。なお、カラムにデータ型を指定した場合、入力されたデータを指定された型に変換するよう SQLite は努力しますが、必ず変換できる保証はありません。ご注意くださいませ。
たとえば、人の名前 (name)、性別 (sex)、メールアドレス (email) を格納するテーブルを作ってみましょう。
sqlite> create table person (id, name, sex, email); sqlite> .tables person sqlite> .schema CREATE TABLE person (id, name, sex, email);
id は番号です。これは連番で自動的に振っていく方法もありますが、ここでは手動で入力することにします。.table コマンドを実行すると person というテーブルが作成されていることがわかります。テーブルの構造は .schema コマンドで確認することができます。
それから、テーブルは drop table 文で削除することができます。
drop table テーブル名;
簡単な実行例として、新しいテーブル test を作成したあと、それを削除してみましょう。
sqlite> create table test (name, age); sqlite> .tables person test sqlite> .schema CREATE TABLE person (id, name, sex, email); CREATE TABLE test (name, age); sqlite> drop table test; sqlite> .table person
create table test ... で新しいテーブルを作成します。.tables と .schema で確認すると、test が作成されていることがわかります。次に drop table test; を実行すると、テーブル test が削除されます。.table で確認すると、確かに削除されていることがわかります。
データの挿入は insert into 文を使います。
insert into テーブル名 (カラム名, ...) values (値, ...);
数値はそのまま入力できますが、文字列は ' で囲ってください。' を文字列に含める場合は " で囲ってください。
それでは、テーブル person にデータを挿入してみましょう。
sqlite> insert into person (id, name, sex, email) ...> values (1, 'foo', 'male', 'foo@yahoo.co.jp'); sqlite> select * from person; 1|foo|male|foo@yahoo.co.jp sqlite> .headers Usage: .headers on|off sqlite> .headers on sqlite> select * from person; id|name|sex|email 1|foo|male|foo@yahoo.co.jp
データの閲覧には select 文を使います。
select カラム名, ... from テーブル名 where 条件式;
カラム名に * を指定すると、すべてのカラムが対象になります。where の後ろに条件式を指定することもできます。これはあとで説明します。それから、.headers on を実行すると、最初にカラム名が表示されます。.headers off を実行すると、カラム名は表示されません。
複数のデータを入力する場合は、values (値, ...), (値, ...), ..., (値, ...); のようにカンマ ( , ) で区切ってください。
sqlite> insert into person (id, name, sex, email) ...> values (2, 'bar', 'female', 'bar@yahoo.co.jp'), ...> (3, 'baz', 'male', 'baz@yahoo.co.jp'), ...> (4, 'oops', 'female', 'oops@yahoo.co.jp'); sqlite> select * from person; id|name|sex|email 1|foo|male|foo@yahoo.co.jp 2|bar|female|bar@yahoo.co.jp 3|baz|male|baz@yahoo.co.jp 4|oops|female|oops@yahoo.co.jp
データの表示方法は .mode コマンドで変更することができます。.help コマンドより抜粋します。
.mode MODE ?TABLE? Set output mode where MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
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 strings
tabs Tab-separated values
tcl TCL list elements
デフォルトは list に設定されています。column と line の例を示します。
sqlite> .mode column sqlite> select * from person; id name sex email ---------- ---------- ---------- --------------- 1 foo male foo@yahoo.co.jp 2 bar female bar@yahoo.co.jp 3 baz male baz@yahoo.co.jp 4 oops female oops@yahoo.co.j sqlite> .mode line sqlite> select * from person; id = 1 name = foo sex = male email = foo@yahoo.co.jp id = 2 name = bar sex = female email = bar@yahoo.co.jp id = 3 name = baz sex = male email = baz@yahoo.co.jp id = 4 name = oops sex = female email = oops@yahoo.co.jp
テーブル名の変更とカラムの追加は alter table 文で行うことができます。
alter table 旧テーブル名 rename to 新テーブル名; alter table テーブル名 add column カラム名;
たとえば、テーブル person に年齢を格納するカラム age を追加してみましょう。
sqlite> alter table person add column age; sqlite> select * from person; id name sex email age ---------- ---------- ---------- --------------- ---------- 1 foo male foo@yahoo.co.jp 2 bar female bar@yahoo.co.jp 3 baz male baz@yahoo.co.jp 4 oops female oops@yahoo.co.j
この場合、age の値は空 (null) になります。カラムの値を更新するには update 文を使います。
update テーブル名 set カラム名1 = 値1, ... where 条件式;
update 文は条件式を満たすレコードのカラムの値を更新します。一度に複数のカラムの値を更新することができます。条件式は次に示す比較演算子と論理演算子を使うことができます。
比較演算子 : =, <>, <, >, <=. >= 論理演算子 : and, or, not
等値を判定する = と <> はＣ言語のように ==, != と書くこともできます。この他にも便利な演算子が用意されていますが、使用するときに詳しく説明することにしましょう。
それでは、age に値をセットしてみましょう。
sqlite> update person set age = 40 where name = 'foo'; sqlite> update person set age = 30 where name = 'bar'; sqlite> update person set age = 50 where name = 'baz'; sqlite> update person set age = 35 where name = 'oops'; sqlite> select * from person; id name sex email age ---------- ---------- ---------- --------------- ---------- 1 foo male foo@yahoo.co.jp 40 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50 4 oops female oops@yahoo.co.j 35
カラムを空にしたい場合は null をセットしてください。
sqlite> update person set age = null where name = 'oops'; sqlite> select * from person; id name sex email age ---------- ---------- ---------- --------------- ---------- 1 foo male foo@yahoo.co.jp 40 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50 4 oops female oops@yahoo.co.j sqlite> update person set age = 35 where name = 'oops';
select 文は where の後ろに条件式を指定することで、条件を満たすデータだけを表示することができます。たとえば、テーブル person で女性だけを取り出すには where で sex = 'female' を指定します。
sqlite> select * from person where sex = 'female'; id name sex email age ---------- ---------- ---------- --------------- ---------- 2 bar female bar@yahoo.co.jp 30 4 oops female oops@yahoo.co.j 35
age >= 40 を指定すると、40 才以上の人を取り出すことができます。
sqlite> select * from person where age >= 40; id name sex email age ---------- ---------- ---------- --------------- ---------- 1 foo male foo@yahoo.co.jp 40 3 baz male baz@yahoo.co.jp 50
文字列を比較するとき、演算子に like を指定するとワイルドカードに % と _ を使用することができます。
% : 任意の 0 文字以上の文字列 _ : 任意の 1 文字
たとえば 'b%' とすると、b から始まる文字列とマッチングします。
sqlite> select * from person where name like 'b%'; id name sex email age ---------- ---------- ---------- --------------- ---------- 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50
また、演算子 glob を指定すると、bash の Globbing と同様のメタ文字を使用することができます。
sqlite> select * from person where name glob 'ba[rz]'; id name sex email age ---------- ---------- ---------- --------------- ---------- 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50 sqlite> select * from person where name glob 'ba[^r]'; id name sex email age ---------- ---------- ---------- --------------- ---------- 3 baz male baz@yahoo.co.jp 50
演算子に in (値1, 値2, ...) を指定すると、カッコの中の値と等しいものが選択されます。
sqlite> select * from person where age in (30, 50); id name sex email age ---------- ---------- ---------- --------------- ---------- 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50 sqlite> select * from person where age not in (30, 50); id name sex email age ---------- ---------- ---------- --------------- ---------- 1 foo male foo@yahoo.co.jp 40 4 oops female oops@yahoo.co.j 35
in の前に not を付けると、カッコの中の値以外のものが選択されます。
空の値 (null) は is null でチェックすることができます。
sqlite> update person set age = null where name = 'oops'; sqlite> select * from person where age is null; id name sex email age ---------- ---------- ---------- ---------------- ---------- 4 oops female oops@yahoo.co.jp sqlite> select * from person where age is not null; id name sex email age ---------- ---------- ---------- --------------- ---------- 1 foo male foo@yahoo.co.jp 40 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50 sqlite> update person set age = 35 where name = 'oops';
null 以外の値は is not null でチェックすることができます。
where のかわりに order by カラム名 を指定すると、そのカラムを昇順に並び替えて出力します。
sqlite> select * from person order by name; id name sex email age ---------- ---------- ---------- --------------- ---------- 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50 1 foo male foo@yahoo.co.jp 40 4 oops female oops@yahoo.co.j 35 sqlite> select * from person order by age desc; id name sex email age ---------- ---------- ---------- --------------- ---------- 3 baz male baz@yahoo.co.jp 50 1 foo male foo@yahoo.co.jp 40 4 oops female oops@yahoo.co.j 35 2 bar female bar@yahoo.co.jp 30
desc を指定すると逆順に並び替えます。
limit で表示する行数を指定することができます。
sqlite> select * from person limit 3; id name sex email age ---------- ---------- ---------- --------------- ---------- 1 foo male foo@yahoo.co.jp 40 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50
offset で開始位置を指定することができます。
sqlite> select * from person limit 2 offset 1; id name sex email age ---------- ---------- ---------- --------------- ---------- 2 bar female bar@yahoo.co.jp 30 3 baz male baz@yahoo.co.jp 50
これは select * from person limit 開始位置, 行数; と同じです。
データの削除は delete 文で行います。
delete from テーブル名 [where 条件式];
delete from テーブル名; で全てのデータを削除することができます。where で条件を指定することもできます。簡単な例を示しましょう。
sqlite> delete from person where name = 'bar'; sqlite> select * from person; id name sex email age ---------- ---------- ---------- --------------- ---------- 1 foo male foo@yahoo.co.jp 40 3 baz male baz@yahoo.co.jp 50 4 oops female oops@yahoo.co.j 35 sqlite> delete from person; sqlite> select * from person; sqlite>
create table 文でカラムを指定するとき、入力するデータに対して制約を指定することができます。
データ型が integer のカラムに primary key を指定すると、データを挿入するときに値を省略した場合、その値はカラムの中の最大値に 1 を足した値になります。さらに、autoincrement を指定すると、今までに使用された値の最大値に 1 を足した値になります。この機能を使って自動的に連番を振ることができます。
簡単な例を示しましょう。
sqlite> create table person1 (id integer primary key autoincrement, name text);
sqlite> insert into person1 (name) values
...> ('foo'), ('bar'), ('baz'), ('oops');
sqlite> select * from person1;
id name
---------- ----------
1 foo
2 bar
3 baz
4 oops
id に integer primary key autoincrement を設定し、名前 (name) だけを順番に入力します。すると、id の値は 1, 2, 3, 4 と連番になります。
次に、oops を削除してから oops1 を追加してみます。
sqlite> delete from person1 where id = 4;
sqlite> select * from person1;
id name
---------- ----------
1 foo
2 bar
3 baz
sqlite> insert into person1 (name) values ('oops1');
sqlite> select * from person1;
id name
---------- ----------
1 foo
2 bar
3 baz
5 oops1
autoincrement を指定した場合、今までに入力された id の最大値 4 に 1 を足した値になるので、oops1 の id は 5 になります。指定しない場合、現在の id の最大値 3 に 1 を足した値になるので、id は 4 になります。
もうひとつ簡単な例題として、年齢を格納するカラム age を追加してみましょう。このとき、条件として 20 才以上であることをチェックします。
sqlite> alter table person1 add column age check(age >= 20); sqlite> select * from person1; id name age ---------- ---------- ---------- 1 foo 2 bar 3 baz 5 oops1 sqlite> update person1 set age = 50 where name = 'foo'; sqlite> update person1 set age = 30 where name = 'bar'; sqlite> update person1 set age = 10 where name = 'baz'; Error: CHECK constraint failed: person1
このように、age に 20 未満の値を代入するとエラーになります。
SQLite は .import コマンドでファイルからデータを読み込むことができます。
.import ファイル名 テーブル名
ファイルにデータを記述するとき、行の区切り記号とカラムの区切り記号を SQLite の設定に合わせる必要があります。これらの設定は .show コマンドで確認することができます。
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width:
colseparator がカラムの区切り記号、rowseparator が行の区切り記号です。これらの記号を変更したい場合は .separator コマンドを使います。
.seprator col [row]
たとえば、カラムの区切り記号をカンマ ( , ) に変更したい場合は .separator , とするだけです。
sqlite> .separator ,
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: ","
rowseparator: "\n"
stats: off
width:
簡単な例題として、次に示す女性の名前と身長をデータベースに登録してみましょう。
リスト : 身長のデータ (height.txt) 1,Ada,148.7,A 2,Alice,149.5,B 3,Carey,133.7,C 4,Ellen,157.9,D 5,Hanna,154.2,A 6,Janet,147.8,B 7,Linda,154.6,C 8,Maria,159.1,D 9,Miranda,148.2,A 10,Sara,153.1,B 11,Tracy,138.2,C 12,Violet,138.7,D
sqlite> create table girls_height (id integer, name text, height real, class text); sqlite> .import height.txt girls_height sqlite> select * from girls_height; id name height class ---------- ---------- ---------- ---------- 1 Ada 148.7 A 2 Alice 149.5 B 3 Carey 133.7 C 4 Ellen 157.9 D 5 Hanna 154.2 A 6 Janet 147.8 B 7 Linda 154.6 C 8 Maria 159.1 D 9 Miranda 148.2 A 10 Sara 153.1 B 11 Tracy 138.2 C 12 Violet 138.7 D
.import でデータを読み込む場合、データ型は TEXT になるので、カラムで適切なデータ型を指定してください。カラムのデータ型に合わせて読み込んだデータを変換します。データ型は関数 typeof(カラム名) で求めることができます。
sqlite> select id, typeof(id), height, typeof(height) from girls_height; id typeof(id) height typeof(height) ---------- ---------- ---------- -------------- 1 integer 148.7 real 2 integer 149.5 real 3 integer 133.7 real 4 integer 157.9 real 5 integer 154.2 real 6 integer 147.8 real 7 integer 154.6 real 8 integer 159.1 real 9 integer 148.2 real 10 integer 153.1 real 11 integer 138.2 real 12 integer 138.7 real
SQLite にはいろいろな関数が用意されています。ここでは基本的な関数の使い方を説明します。テーブルの行数は count(*) で求めることができます。引数にカラムを指定すると、null 以外のデータ数を求めることができます。
sqlite> select count(*) from girls_height; count(*) ---------- 12 sqlite> update girls_height set name = null where id = 5; sqlite> select count(name) from girls_height; count(name) ----------- 11 sqlite> update girls_height set name = 'Hanna' where id = 5;
最大値と最小値は max(カラム名), min(カラム名) で求めることができます。
sqlite> select min(height) from girls_height; min(height) ----------- 133.7 sqlite> select name, min(height) from girls_height; name min(height) ---------- ----------- Carey 133.7 sqlite> select name, max(height) from girls_height; name max(height) ---------- ----------- Maria 159.1
合計値は関数 sum(カラム名), total(カラム名) で、平均値は関数 avg(カラム名) で求めることができます。
sqlite> select sum(height) from girls_height; sum(height) ----------- 1783.7 sqlite> select total(height) from girls_height; total(height) ------------- 1783.7 sqlite> select avg(height) from girls_height; avg(height) ---------------- 148.641666666667
テキストの長さは length() で求めることができます。引数が文字列の場合はその文字数を、カラムが指定された場合はカラムに格納されているテキストの文字数を返します。
sqlite> select length('hello, world');
length('hello, world')
----------------------
12
sqlite> select name, length(name) from girls_height;
name length(name)
---------- ------------
Ada 3
Alice 5
Carey 5
Ellen 5
Hanna 5
Janet 5
Linda 5
Maria 5
Miranda 7
Sara 4
Tracy 5
Violet 6
group by カラム名 を指定すると、カラムの値でデータをグループに分けて集計することができます。
sqlite> select class, count(*) from girls_height group by class; class count(*) ---------- ---------- A 3 B 3 C 3 D 3 sqlite> select class, avg(height) from girls_height group by class; class avg(height) ---------- ---------------- A 150.366666666667 B 150.133333333333 C 142.166666666667 D 151.9 sqlite> select class, name, max(height) from girls_height group by class; class name max(height) ---------- ---------- ----------- A Hanna 154.2 B Sara 153.1 C Linda 154.6 D Maria 159.1 sqlite> select class, name, min(height) from girls_height group by class; class name min(height) ---------- ---------- ----------- A Miranda 148.2 B Janet 147.8 C Carey 133.7 D Violet 138.7
group by の後ろに having 条件式 を指定して、条件式を満たすグループを取り出すこともできます。
sqlite> select class, avg(height) from girls_height group by class having avg(height) < 150; class avg(height) ---------- ---------------- C 142.166666666667