select 文で distinct を指定すると、重複データを除外することができます。
select distinct カラム名, ... from テーブル名 where 条件式;
指定したカラムが 1 つの場合、カラムの値が等しければ重複データとみなします。簡単な例を示しましょう。
sqllite> select distinct class from girls_height; class ---------- A B C D sqlite> select distinct class from girls_height where height < 140; class ---------- C D sqlite> select distinct class from girls_height where height > 155; class ---------- D
複数のカラムを指定することもできます。この場合、各々のカラムの値が等しいとき、重複データとみなします。たとえば、item1, item2 と指定した場合、1, 2 と 1, 2 は等しいデータですが、1, 2 と 2, 1 は異なるデータになります。
sqlite> create table sample (item1, item2); sqlite> insert into sample (item1, item2) values ...> (1, 1), (2, 2), (1, 3), (2, 1), (1, 2), (2, 3), (1, 1), (2, 2); sqlite> select * from sample; item1 item2 ---------- ---------- 1 1 2 2 1 3 2 1 1 2 2 3 1 1 2 2 sqlite> select distinct item1, item2 from sample; item1 item2 ---------- ---------- 1 1 2 2 1 3 2 1 1 2 2 3
SQLite は as を使ってカラムに別名を付けることができます。
カラム名 as 別名, ...
たとえば、クラスの平均身長を求める場合、avg(height) を指定するとカラム名は avg(height) になりますが、as を使ってわかりやすい名前を付けることができます。
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, avg(height) as average from girls_height group by class; class average ---------- ---------------- A 150.366666666667 B 150.133333333333 C 142.166666666667 D 151.9
もちろん、where や having の中でも別名を使うことができます
sqlite> select class, avg(height) as average from girls_height group by class having average < 150; class average ---------- ---------------- C 142.166666666667
SQLite はカラムの値で四則演算や他の計算を行うことができます。たとえば、平均身長との差分は次のように求めることができます。
sqlite> select avg(height) from girls_height; 148.641666666667 sqlite> .mode column sqlite> .headers on sqlite> select name, height, height - 148.6 from girls_height; name height height - 148.6 ---------- ---------- ------------------ Ada 148.7 0.0999999999999943 Alice 149.5 0.900000000000006 Carey 133.7 -14.9 Ellen 157.9 9.30000000000001 Hanna 154.2 5.59999999999999 Janet 147.8 -0.799999999999983 Linda 154.6 6.0 Maria 159.1 10.5 Miranda 148.2 -0.400000000000006 Sara 153.1 4.5 Tracy 138.2 -10.4 Violet 138.7 -9.90000000000001
関数 round(n, m) を使うと、小数以下 m 桁で数値 n を四捨五入することができます。
sqlite> select name, height, round(height - 148.6, 1) from girls_height; name height round(height - 148.6, 1) ---------- ---------- ------------------------ Ada 148.7 0.1 Alice 149.5 0.9 Carey 133.7 -14.9 Ellen 157.9 9.3 Hanna 154.2 5.6 Janet 147.8 -0.8 Linda 154.6 6.0 Maria 159.1 10.5 Miranda 148.2 -0.4 Sara 153.1 4.5 Tracy 138.2 -10.4 Violet 138.7 -9.9
複数のカラムの値を使って計算することもできます。簡単な例を示しましょう。
sqlite> create table sample1 (name text, price integer, num integer);
sqlite> insert into sample1 (name, price, num)
...> values ('apple', 128, 5),
...> ('grape', 1900, 7),
...> ('orange', 98, 10);
sqlite> select *, price * num from sample1;
name price num price * num
---------- ---------- ---------- -----------
apple 128 5 640
grape 1900 7 13300
orange 98 10 980
sqlite> select sum(price * num) from sample1;
sum(price * num)
----------------
14920
SQLite はテーブルからデータを抽出して新たなテーブルを作成することができます。これを VIEW といいます。VIEW は create view 文で作成します。
create view テーブル名 as select 文;
たとえば、クラスの平均身長を格納したテーブル class_average は次のように作成することができます。
sqlite> create view class_average as select class, avg(height) as average from girls_height group by class; sqlite> .table class_average girls_height person person1
.table コマンドを実行すると、テーブル class_average が作成されていることがわかります。このテーブルを使って、データを抽出することができます。
sqlite> select * from class_average; class average ---------- ---------------- A 150.366666666667 B 150.133333333333 C 142.166666666667 D 151.9 sqlite> select count(*) from class_average; count(*) ---------- 4 sqlite> select class, max(average) from class_average; class max(average) ---------- ------------ D 151.9 sqlite> select class, min(average) from class_average; class min(average) ---------- ---------------- C 142.166666666667 sqlite> select avg(average) from class_average; avg(average) ---------------- 148.641666666667 sqlite> select * from class_average order by average; class average ---------- ---------------- C 142.166666666667 B 150.133333333333 A 150.366666666667 D 151.9
VIEW は実際にデータを保持しているわけではなく、select 文を実行するたびに、元のテーブルから必要なデータを抽出します。したがって、元のテーブルの値が書き換えられると、select 文の実行結果が異なることがあります。それから、VIEW に対してデータを追加したり削除することはできません。ご注意くださいませ。
VIEW の削除は drop view 文で行います。
drop view テーブル名;
それでは実際に削除してみましょう。
sqlite> drop view class_average; sqlite> .table girls_height person person1
VIEW を削除しても、元のテーブルに影響を与えることはありません。
一般に、データベースに格納されているデータが多くなると、検索に時間がかかるようになります。このような場合、あらかじめ「インデックス (index, 索引)」を作成しておくと、処理時間を短縮することができます。
インデックスは create index 文で作成します。
create index インデックス名 on テーブル名(カラム名, ...);
インデックスはテーブル内のカラムが対象になります。指定するカラムは一つでも複数でもかまいません。また、同じテーブルでインデックスを複数設定することもできます。
簡単な例として、テーブル girls_height の name にインデックスを設定してみましょう。
sqlite> create index name_index on girls_height(name); sqlite> select * from girls_height where name = 'Sara'; id name height class ---------- ---------- ---------- ---------- 10 Sara 153.1 B
インデックスを生成しても、select 文は今までと同じように使うことができます。もちろん、insert into 文でデータを挿入することもできます。
作成したインデックスは .indices コマンドで確認することができます。
.indices [テーブル名]
sqlite> .indices name_index sqlite> .indices girls_height name_index
インデックスは drop index 文で削除することができます。
drop index インデックス名;
それでは実際に削除してみましょう。
sqlite> drop index name_index; sqlite> .indices sqlite>
インデックスの対象となるカラムは重複データがあってもかまいませんが、重複データを許可しないように制約をかけることができます。これを「ユニークインデックス」といいます。設定は簡単で、create index でインデックスを生成するとき、index の前に unique を指定するだけです。
create unique index on テーブル名(カラム名, ...);
テーブル girls_height のカラム name は重複データがないので、ユニークインデックスを作成することができます。
sqlite> select * from girls_height where name = 'Violet'; id name height class ---------- ---------- ---------- ---------- 12 Violet 138.7 D sqlite> insert into girls_height (id, name, height, class) ...> values (13, 'Alice', 145.0, 'A'); Error: UNIQUE constraint failed: girls_height.name
このように、重複したデータを挿入しようとするとエラーになります。
.dump コマンドはデータベースの内容を SQL 文の形式で出力 (ダンプ) します。
.dump [テーブル名]
テーブル名を省略すると、データベースの全ての内容がダンプされます。girls_height の内容をダンプすると次のように表示されます。
sqlite> .dump girls_height PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE girls_height (id integer, name text, height real, class text); INSERT INTO "girls_height" VALUES(1,'Ada',148.7,'A'); INSERT INTO "girls_height" VALUES(2,'Alice',149.5,'B'); INSERT INTO "girls_height" VALUES(3,'Carey',133.7,'C'); INSERT INTO "girls_height" VALUES(4,'Ellen',157.9,'D'); INSERT INTO "girls_height" VALUES(5,'Hanna',154.2,'A'); INSERT INTO "girls_height" VALUES(6,'Janet',147.8,'B'); INSERT INTO "girls_height" VALUES(7,'Linda',154.6,'C'); INSERT INTO "girls_height" VALUES(8,'Maria',159.1,'D'); INSERT INTO "girls_height" VALUES(9,'Miranda',148.2,'A'); INSERT INTO "girls_height" VALUES(10,'Sara',153.1,'B'); INSERT INTO "girls_height" VALUES(11,'Tracy',138.2,'C'); INSERT INTO "girls_height" VALUES(12,'Violet',138.7,'D'); CREATE UNIQUE INDEX unique_name on girls_height(name); COMMIT;
.output コマンドを使うと、画面に出力された情報をファイルに保存することができます。
.output ファイル名
.output のデフォルト値は標準出力 (stdout) になっています。.output で出力先をファイルに切り替えたあと、.output stdout を実行すれば、出力先は元の画面に戻ります。
それでは実際に試してみましょう。
sqlite> .output ./girls_height.txt sqlite> .dump girls_height sqlite> .output stdout
mhiroi@mhiroi-VirtualBox:~/sqlite$ cat girls_height.txt PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE girls_height (id integer, name text, height real, class text); INSERT INTO "girls_height" VALUES(1,'Ada',148.7,'A'); INSERT INTO "girls_height" VALUES(2,'Alice',149.5,'B'); INSERT INTO "girls_height" VALUES(3,'Carey',133.7,'C'); INSERT INTO "girls_height" VALUES(4,'Ellen',157.9,'D'); INSERT INTO "girls_height" VALUES(5,'Hanna',154.2,'A'); INSERT INTO "girls_height" VALUES(6,'Janet',147.8,'B'); INSERT INTO "girls_height" VALUES(7,'Linda',154.6,'C'); INSERT INTO "girls_height" VALUES(8,'Maria',159.1,'D'); INSERT INTO "girls_height" VALUES(9,'Miranda',148.2,'A'); INSERT INTO "girls_height" VALUES(10,'Sara',153.1,'B'); INSERT INTO "girls_height" VALUES(11,'Tracy',138.2,'C'); INSERT INTO "girls_height" VALUES(12,'Violet',138.7,'D'); CREATE UNIQUE INDEX unique_name on girls_height(name); COMMIT;
ダンプしたデータは .read コマンドで読み込むことができます。
.read ファイル名
それでは girls_height.txt を .read で読み込んでみましょう。
mhiroi@mhiroi-VirtualBox:~/sqlite$ sqlite3 sample02.sqlite SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. sqlite> .table sqlite> .read ./girls_height.txt sqlite> .table girls_height sqlite> .schema CREATE TABLE girls_height (id integer, name text, height real, class text); CREATE UNIQUE INDEX unique_name on girls_height(name); sqlite> select * from girls_height; 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
データベース sample02.sqlite は空なので、テーブルは何もありません。次に、.read コマンドで girls_height.txt を読み込みます。すると、テーブル girls_height が作成され、データも読み込まれていることがわかります。
あるテーブルのデータを更新したとき、同時に別のテーブルのデータも更新できると便利な場合があります。このような場合、役に立つ機能が「トリガー (trigger)」です。トリガーを使うと、insert や update などのイベントが発生したときに、指定した SQL 文を実行させることができます。なお、トリガーの書式はけっこう複雑なので、本稿では基本的なことのみを説明します。詳しい説明は 参考 URL 3 をお読みください。
トリガーは create trigger 文で作成します。データの挿入 (insert), 削除 (delete), 更新 (update) したときのトリガーは次のように定義します。
create trigger トリガー名 insert on テーブル名
begin
SQL 文;
...
end;
create trigger トリガー名 delete on テーブル名
begin
SQL 文;
...
end;
create trigger トリガー名 update of カラム名 on テーブル名
begin
SQL 文;
...
end;
これでテーブルを操作 (insert, delete, update) したとき、begin ... end の中の SQL 文が実行されます。この中で更新前後のカラムの値を次の文で取得することができます。
old.カラム名 : 更新前の値 (delete, update のとき有効) new.カラム名 : 更新後の値 (insert, update のとき有効)
簡単な例を示しましょう。名前 (name) と値 (val) を格納するテーブル test と値の更新記録を格納するテーブル change を作ります。change には名前 (name), 更新前の値 (old_val), 更新後の値 (new_val) を記録します。これはトリガーを使うと簡単に実現することができます。
sqlite> create table test (name text, val integer); sqlite> create table change (name text, old_val integer, new_val); sqlite> create trigger test_trigger update of val on test ...> begin ...> insert into change (name, old_val, new_val) values ...> (old.name, old.val, new.val); ...> end; sqlite> .schema CREATE TABLE test (name text, val integer); CREATE TABLE change (name text, old_val integer, new_val); CREATE TRIGGER test_trigger update of val on test begin insert into change (name, old_val, new_val) values (old.name, old.val, new.val); end;
テーブル test のカラム val の値を update で更新するときにトリガーを指定します。begin 文の中では、old.name, old.val, new.val で名前と更新前後の値を取り出して、insert into 文でテーブル change に挿入します。なお、トリガーの定義はコマンド .schema で確認することができます。
次にテーブル test にデータを挿入します。
sqlite> .mode column
sqlite> .headers on
sqlite> insert into test (name, val) values
...> ('foo', 1), ('bar', 2), ('baz', 3);
sqlite> select * from test;
name val
---------- ----------
foo 1
bar 2
baz 3
今回定義したトリガーは update なので、insert では起動しません。それでは、update で val の値を更新してみましょう。
sqlite> update test set val = 10 where name = 'foo'; sqlite> select * from test; name val ---------- ---------- foo 10 bar 2 baz 3 sqlite> select * from change; name old_val new_val ---------- ---------- ---------- foo 1 10 sqlite> update test set val = 20 where name = 'bar'; sqlite> update test set val = 30 where name = 'baz'; sqlite> select * from change; name old_val new_val ---------- ---------- ---------- foo 1 10 bar 2 20 baz 3 30
値を更新すると、テーブル change にその記録が保存されていることがわかります。
次は、delete 文で test からデータを削除したとき、change からもデータを同時に削除してみましょう。
sqlite> create trigger test_del_trigger delete on test ...> begin ...> delete from change where name = old.name; ...> end;
トリガーの定義は簡単ですね。削除したデータの名前は old.name からわかるので、それと同じ名前を change から全て削除するだけです。それでは実際に試してみましょう。
sqlite> update test set val = 100 where name = 'foo'; sqlite> select * from change; name old_val new_val ---------- ---------- ---------- foo 1 10 bar 2 20 baz 3 30 foo 10 100 sqlite> delete from test where name = 'foo'; sqlite> select * from test; name val ---------- ---------- bar 20 baz 30 sqlite> select * from change; name old_val new_val ---------- ---------- ---------- bar 2 20 baz 3 30
このように、test から foo を削除すると、同時に change からも foo を削除することができます。
トリガーの削除は drop trigger 文で行います。
drop trigger トリガー名;
それでは実際にトリガーを削除してみましょう。
sqlite> .schema CREATE TABLE test (name text, val integer); CREATE TABLE change (name text, old_val integer, new_val); CREATE TRIGGER test_trigger update of val on test begin insert into change (name, old_val, new_val) values (old.name, old.val, new.val); end; CREATE TRIGGER test_del_trigger delete on test begin delete from change where name = old.name; end; sqlite> drop trigger test_del_trigger; sqlite> .schema CREATE TABLE test (name text, val integer); CREATE TABLE change (name text, old_val integer, new_val); CREATE TRIGGER test_trigger update of val on test begin insert into change (name, old_val, new_val) values (old.name, old.val, new.val); end;
drop trigger test_del_trigger でトリガー test_del_trigger が削除されていることがわかります。
RDBMS は複数のテーブルを結合してデータを取得することができます。これを「テーブルの結合」とか「表結合」といいます。SQLite でもテーブルの結合はサポートされています。本稿では表結合の基本的な機能を簡単に説明します。
「内部結合 (inner join)」は指定したカラムの値が一致するデータだけを取り出す方法です。以下に書式を示します。
select テーブル.カラム, ... from テーブル1 inner join テーブル2 on テーブル1.カラムa = テーブル2.カラムb
テーブル 1 のカラム a とテーブル 2 のカラム b を比較して、同じ値のデータをテーブル 2 から取り出してテーブル 1 に結合します。
簡単な例を示しましょう。
sqlite> create table person (id integer, name text); sqlite> insert into person (id, name) values ...> (1, 'foo'), (2, 'bar'), (3, 'baz'), (4, 'oops'); sqlite> create table color_table (id integer, color text); sqlite> insert into color_table (id, color) values ...> (1, 'red'), (2, 'blue'), (3, 'green'), ...> (3, 'red'), (1, 'blue'), (2, 'green'), (1, 'pink'); sqlite> .mode column sqlite> .headers on sqlite> select * from person; id name ---------- ---------- 1 foo 2 bar 3 baz 4 oops sqlite> select * from color_table; id color ---------- ---------- 1 red 2 blue 3 green 3 red 1 blue 2 green 1 pink
テーブル person は id と name を保持し、テーブル color_table では id と color を保持しています。内部結合を使うと、name と color の関係を簡単に求めることができます。
sqlite> select * from person inner join color_table on person.id = color_table.id; id name id color ---------- ---------- ---------- ---------- 1 foo 1 blue 1 foo 1 pink 1 foo 1 red 2 bar 2 blue 2 bar 2 green 3 baz 3 green 3 baz 3 red
同じデータが複数ある場合、その数だけテーブル結合が行われます。person.id と color_table.id が等しいデータを取り出して結合するので、foo には 3 つの色 (bule, pink, red)、bar には 2 つの色 (blue, green), baz には 2 つの色 (green, red) があることがわかります。oops の id は 4 ですが、color_table の id に 4 はないので、id = 4 のデータが取り出されることはありません。
「外部結合 (outer join)」は指定したカラムの値が一致するデータだけではなく、どちらかのテーブルにデータが存在すれば、それもいっしょに取り出す方法です。以下に書式を示します。
select テーブル.カラム, ... from テーブル1 left outer join テーブル2 on テーブル1.カラムa = テーブル2.カラムb
left outer join は条件式 テーブル1.カラムa = テーブル2.カラムb を満たさない場合でも、テーブル 1 (左側) のデータを取り出します。SQL では left のほかに right や full を指定することができますが、SQLite では left のみをサポートしています。
簡単な例を示しましょう。
sqlite> select * from person left outer join color_table on person.id = color_table.id; id name id color ---------- ---------- ---------- ---------- 1 foo 1 blue 1 foo 1 pink 1 foo 1 red 2 bar 2 blue 2 bar 2 green 3 baz 3 green 3 baz 3 red 4 oops
id が 4 のデータは person にはありますが color_table にはありません。そのような場合でも、外部結合すると person の id = 4 のデータを取り出すことができます。
「交差結合 (cross join)」は 2 つのテーブルのデータの全ての組み合わせを取得する方法です。プログラミング言語でいえば、2 つの集合の「直積集合」を求める処理といえるでしょう。以下に書式を示します。
select テーブル.カラム, ... from テーブル1 closs join テーブル2 [on 結合条件];
交差結合の場合、on 結合条件 を省略することが多いようです。
簡単な例を示しましょう。
sqlite> select * from person; id name ---------- ---------- 1 foo 2 bar 3 baz 4 oops sqlite> create table sports (id integer, name text); sqlite> insert into sports (id, name) values ...> (1, 'baseball'), (2, 'tennis'), (3, 'basketball'); sqlite> select * from sports; id name ---------- ---------- 1 baseball 2 tennis 3 basketball sqlite> select * from person cross join sports; id name id name ---------- ---------- ---------- ---------- 1 foo 1 baseball 1 foo 2 tennis 1 foo 3 basketball 2 bar 1 baseball 2 bar 2 tennis 2 bar 3 basketball 3 baz 1 baseball 3 baz 2 tennis 3 baz 3 basketball 4 oops 1 baseball 4 oops 2 tennis 4 oops 3 basketball
テーブル sports にスポーツの種類 (baseball, tennis, basketball) を格納します。そして、person と sports の交差結合を求めると、テーブル person のデータひとつひとつに対して sports テーブルのデータをひとつずつ結合したデータを取得することができます。
natural というキーワードを指定すると、2 つのテーブルで同じカラム名のデータを比較します。これを「自然結合」といいます。この場合、on 結合条件の指定は必要ありません。
select テーブル.カラム, ... from テーブル1 natural inner join テーブル2 select テーブル.カラム, ... from テーブル1 natural left outer join テーブル2
ただし、同じ名前のカラムがなかったり、同じ名前のカラムが複数存在する場合、自然結合を使用することはできません。
簡単な例を示しましょう。
sqlite> select * from person natural inner join color_table; id name color ---------- ---------- ---------- 1 foo blue 1 foo pink 1 foo red 2 bar blue 2 bar green 3 baz green 3 baz red sqlite> select * from person natural left outer join color_table; id name color ---------- ---------- ---------- 1 foo blue 1 foo pink 1 foo red 2 bar blue 2 bar green 3 baz green 3 baz red 4 oops
自然結合の場合、同名のカラムはひとつだけしか表示しません。
テーブル結合は自分自身に対しても行うことができます。これを「自己結合」といいます。自己結合するときはテーブル名の後ろに別名を定義します。
select 別名1.カラム名, 別名2.カラム名, ... from テーブル名 別名1 [inner join | left outer join | cross join] テーブル名 別名2 on 結合条件;
簡単な例として、テーブル内のデータの直積集合を求めてみましょう。最初にテーブルを定義します。
sqlite> create table colors (id integer, color text); sqlite> insert into colors (id, color) values ...> (1, 'red'), (2, 'blue'), (3, 'yellow'), (4, 'green'); sqlite> .headers on sqlite> .mode column sqlite> select * from colors; id color ---------- ---------- 1 red 2 blue 3 yellow 4 green
テーブル colors には 4 つの色が登録されています。色の直積集合を求める場合、自己結合を使うと簡単です。
sqlite> select c1.color, c2.color from colors c1 cross join colors c2; color color ---------- ---------- red red red blue red yellow red green blue red blue blue blue yellow blue green yellow red yellow blue yellow yellow yellow green green red green blue green yellow green green
colors の別名に c1 と c2 を使います。select 文で取り出すデータを c1.color, c2.color とすると、color の直積集合を求めることができます。同じ色を省きたい場合は、where で条件 c1.color != c2.color を追加するだけです。この場合、4 色から 2 色を選ぶ順列を求めることになります。
sqlite> select c1.color, c2.color from colors c1 cross join colors c2 where c1.color != c2.color; color color ---------- ---------- red blue red yellow red green blue red blue yellow blue green yellow red yellow blue yellow green green red green blue green yellow
組み合わせを求めたい場合は where の条件を c1.color < c2.color に変更するだけです。
sqlite> select c1.color, c2.color from colors c1 cross join colors c2 where c1.color < c2.color; color color ---------- ---------- red yellow blue red blue yellow blue green green red green yellow
この他にも、自己結合には便利な使い方があります。興味のある方は 参考 URL 3 をお読みくださいませ。