やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3構文　select

SQLite3構文　select

SQLite3

　データの取得。最難関。

成果物

情報源

一覧

句 意味
select 取得する列
from 対象テーブル
where 対象レコード
group by 集計
window 窓関数
with 再帰またはサブクエリ
order by ソート
limit 取得レコード上限数

基本

select

select 1;

1

select 1, 'A';

1|A

select random();

2560352554999816368

select 
  case 
    when true then 1 
    else 2 
  end;

1

*

　*で全列指定。

create table T(A integer, B text);
insert into T values(1,'A');
insert into T values(2,'B');
insert into T values(3,'C');

select * from T;

1|A
2|B
3|C

as

　列の別名定義。

.headers on
select 'Yamada' as name;

name
Yamada

distinct

テーブル行

create table T(A text);
insert into T values('A');
insert into T values('B');
insert into T values('A');

select distinct * from T;

A
B

select distinct A from T;

A
B

　distinctがないと重複列を表示する。

select * from T;

A
B
A

all

テーブル行

create table T(A text);
insert into T values('A');
insert into T values('B');
insert into T values('A');

select all * from T;

A
B
A

select all A from T;

A
B
A

　distinct,allを省略したらall。

select * from T;

A
B
A

固定値レコード（最初からall有効？）

select all 1 union all select 2 union all select 1;

1
2
1

select all 1 union select 2 union select 1;

1
2

from

　from句の後にはテーブル名かサブクエリ。

省略

　fromが省略されたら1行0列になる。

select 1;

1

テーブル名

create table T(A text);
insert into T values('A');
insert into T values('B');

select * from T;

A
B

サブクエリ

select * from (select 1);

1

結合

　テーブルやサブクエリが複数あると、結合する。

  • 結合演算子

  • 結合制約

  • 結合演算子

    • 内部結合（,,join,inner join）
    • 外部結合
      • 左外部結合（left join,left outer join）
      • 右外部結合（SQLite3非サポート）
      • 完全外部結合（SQLite3非サポート）
    • 交差結合（cross join）
  • 結合制約
    • on 式
    • using(列名,...)

　SQLite3のすべての結合はデカルト積（直積、積集合）に基づく。左と右の全組合せである。たとえばトランプでいうと左がスーツ(♠, ♥, ♦, ♣)、右がランク（A, K, Q, J, 10, 9, 8, 7, 6, 5, 4, 3, 2）としたとき、これらのデカルト積は4元×13元＝52元になる。

　このデカルト積にあたるのが交差結合（cross join）である。また、結合条件のない他の結合も同様。

　結合には3種類ある。

結合方法 キーワード 取得レコード
内部結合 inner 両テーブルに存在するレコードのみ
外部結合 outer 内部結合に加えて、一方のみか両テーブルに存在しないレコードも含める
交差結合 cross 全レコードの組合せ

　外部結合はさらに3種類に分けられる。計5パターン。

  • 内部結合（,,join,inner join）
  • 外部結合
    • 左外部結合（left join,left outer join）
    • 右外部結合（SQLite3非サポート）
    • 完全外部結合（SQLite3非サポート）
  • 交差結合（cross join）

　4象限マトリクスでいうと以下。

-- 有 無
有 内 左
無 右 全

　上記には交差結合がない。交差結合は絞込条件がない全パターン網羅だから。

右
有無
左有内左
無右全

　SQLite3は外部結合のうち左LEFTしかサポートしていない。

句 SQLite3サポート
LEFT ○
RIGHT ☓
FULL ☓

結合演算子

,,join,inner join

　内部結合。左右のテーブルにあれば表示する。おそらくテーブル結合といえば大抵はこれだろう。そのためか,のように最も短く略記できる。

pragma foreign_keys=0;
create table classes(name text);
create table students(name text, cid integer references classes(rowid));
insert into classes values('A');
insert into classes values('B');
insert into students values('Yamada', 1);
insert into students values('Suzuki', 2);
insert into students values('Tanaka', 1);
insert into students values('Toudou', 3);

　各クラスに所属する生徒一覧。

select c.name, s.name from classes c, students s on c.rowid=s.cid;
select c.name, s.name from classes c join students s on c.rowid=s.cid;
select c.name, s.name from classes c inner join students s on c.rowid=s.cid;

A|Yamada
B|Suzuki
A|Tanaka

　生徒一覧とその所属。

select s.name, c.name from students s, classes c on c.rowid=s.cid;
select s.name, c.name from students s join classes c on c.rowid=s.cid;
select s.name, c.name from students s inner join classes c on c.rowid=s.cid;

Yamada|A
Suzuki|B
Tanaka|A

　Toudouレコードは出てこない。なぜなら存在しないクラスに所属しているから。

  • 内部結合するときは3つの表記ができる。,, join, inner joinである。正式名は長いものだが、略記できる。
  • 結合条件onがなければ交差結合と同じ結果になる（ただしSQLite3の交差結合は最適化（ソート）しないため順序が異なる場合がある）

　結合キーが同一列名なら略記using, naturalが使える。ただし結合キー以外に同一列名があると、それも結合キー扱いになってしまう。よって以下はclasses表のname列名をsymbolに変更した。

pragma foreign_keys=0;
create table classes(cid integer primary key, symbol text);
create table students(name text, cid integer references classes(cid));
insert into classes values(1, 'A');
insert into classes values(2, 'B');
insert into students values('Yamada', 1);
insert into students values('Suzuki', 2);
insert into students values('Tanaka', 1);
insert into students values('Toudou', 3);

　using句を使った略記法。

select c.symbol, s.name from classes c, students s using(cid);
select c.symbol, s.name from classes c join students s using(cid);
select c.symbol, s.name from classes c inner join students s using(cid);

A|Yamada
B|Suzuki
A|Tanaka

　natural句を使った略記法。

select c.symbol, s.name from classes c, students s using(cid);
select c.symbol, s.name from classes c natural join students s;
select c.symbol, s.name from classes c natural inner join students s;

A|Yamada
B|Suzuki
A|Tanaka
異なる 同一
... on c.rowid=s.cid ... using(cid)
join ... on ... natural join ...
left join

　左外部結合。左にあれば表示する。右になければNULLとして。

pragma foreign_keys=0;
create table classes(name text);
create table students(name text, cid integer references classes(rowid));
insert into classes values('A');
insert into classes values('B');
insert into students values('Yamada', 1);
insert into students values('Suzuki', 2);
insert into students values('Tanaka', 1);
insert into students values('Toudou', 3);

　各クラスの所属生徒一覧。

select c.name, s.name from classes c left join students s on c.rowid=s.cid;
select c.name, s.name from classes c left outer join students s on c.rowid=s.cid;

A|Tanaka
A|Yamada
B|Suzuki

　各生徒の所属クラス一覧。Toudouは存在しないクラスに所属しているためクラスがNULL。でも表示される。外部結合だから。

select s.name, c.name from students s left join classes c on c.rowid=s.cid;
select s.name, c.name from students s left outer join classes c on c.rowid=s.cid;

Yamada|A
Suzuki|B
Tanaka|A
Toudou|
  • left join, left outer joinの2種類で表記できる。長いほうが正式名で、短いほうは略記

　ちなみに上記は外部キー制約を1にすればinsert全件でError: foreign key mismatch - "students" referencing "classes" になってしまうる。rowidは外部キー制約の相手にできない。よってinteger primary keyで別名定義すべき。以下のように。

pragma foreign_keys=1;
create table classes(id integer primary key, name text);
create table students(name text, cid integer references classes(id));
insert into classes values(1, 'A');
insert into classes values(2, 'B');
insert into students values('Yamada', 1);
insert into students values('Suzuki', 2);
insert into students values('Tanaka', 1);
insert into students values('Toudou', 3);
select c.name, s.name from classes c left join students s on c.rowid=s.cid;
select s.name, c.name from students s left join classes c on c.rowid=s.cid;

　Toudouレコードを挿入しようとすると以下のように外部キー制約違反となる。

Error: FOREIGN KEY constraint failed

　必然的に、存在しないクラスに所属する生徒など作れなくなって、内部結合と同じ結論になる。

A|Tanaka
A|Yamada
B|Suzuki
Yamada|A
Suzuki|B
Tanaka|A

　ふつうはこうするだろう。すると外部結合の意味がなくなる。

　また、以下の点は内部結合と同じである。

  • 結合条件onがなければ交差結合と同じ結果になる（ただしSQLite3の交差結合は最適化（ソート）しないため順序が異なる場合がある）
  • 結合キーが同一列名なら略記using, naturalが使える
cross join

　交差結合。デカルト積。全パターン。

　SQLite3の特殊な効能として「テーブル内の順序を変更しない」。もしデカルト積が欲しくて、テーブル順序を最適化したいなら、結合条件なしの内部結合をすること。

create table classes(V text);
create table ranks(V integer);
insert into classes values('A');
insert into classes values('B');
insert into ranks values(1);
insert into ranks values(2);
insert into ranks values(3);

　以下はすべて交差結合である。cross join以外にも、結合条件がない内部・外部結合でも同じ。

select c.V || r.V from classes as c, ranks as r;
select c.V || r.V from classes as c join ranks as r;
select c.V || r.V from classes as c inner join ranks as r;
select c.V || r.V from classes as c left join ranks as r;
select c.V || r.V from classes as c left outer join ranks as r;
select c.V || r.V from classes as c cross join ranks as r;

A1
A2
A3
B1
B2
B3

　ちなみに、同じ表を組合せることを「自己結合」という。結合方法は難でもいい。ただし同一テーブル名なのでas句で別名定義する必要がある。また、as句は省略できる。

create table dices(V integer);
insert into dices values(1);
insert into dices values(2);
insert into dices values(3);
insert into dices values(4);
insert into dices values(5);
insert into dices values(6);

select d1.V || ',' || d2.V from dices as d1, dices as d2;
select d1.V || ',' || d2.V from dices as d1 cross join dices as d2;
select d1.V || ',' || d2.V from dices d1, dices d2;
select d1.V || ',' || d2.V from dices d1 cross join dices d2;

結果

1,1
1,2
1,3
1,4
1,5
1,6
2,1
2,2
2,3
2,4
2,5
2,6
3,1
3,2
3,3
3,4
3,5
3,6
4,1
4,2
4,3
4,4
4,5
4,6
5,1
5,2
5,3
5,4
5,5
5,6
6,1
6,2
6,3
6,4
6,5
6,6

　using句で同一列名を結合キーにできる。

select d1.V || ',' || d2.V from dices as d1, dices as d2 using(V);
select d1.V || ',' || d2.V from dices as d1 cross join dices as d2 using(V);

1,1
2,2
3,3
4,4
5,5
6,6

　natural結合はusing句を省略できる。つまり自動で同一列名を探してそれを結合条件とする。

select d1.V || ',' || d2.V from dices as d1, dices as d2 using(V);
select d1.V || ',' || d2.V from dices as d1 natural join dices as d2;
select V|| ',' || V from dices as d1 natural join dices as d2;
select V|| ',' || V from dices natural join dices;

1,1
2,2
3,3
4,4
5,5
6,6
おまけ

　結合方法は上記の通り、内部、外部、交差、の3種類である。

　その他にも以下のように、さも上記の仲間であるかのように名付けられているものがある。だが、これらは略記などおまけ要素にすぎない。結合方法ではない。

名前 概要
自然結合 結合キーを省略できる。同一列名すべてを結合キーとする。using句の略記法。
自己結合 同じ表を結合する。

　いずれも結合方法は不問。

natural

　natural結合はusing句を省略できる。つまり自動で同一列名を探してそれを結合条件とする。

create table dices(V integer);
insert into dices values(1);
insert into dices values(2);
insert into dices values(3);
insert into dices values(4);
insert into dices values(5);
insert into dices values(6);
select d1.V || ',' || d2.V from dices as d1, dices as d2 using(V);
select d1.V || ',' || d2.V from dices as d1 natural join dices as d2;
select V|| ',' || V from dices as d1 natural join dices as d2;
select V|| ',' || V from dices natural join dices;

1,1
2,2
3,3
4,4
5,5
6,6

　内部・外部、いずれの結合方法においても使える。同一列名が1つもなければ交差結合になる。

create table E(A integer);
create table O(B integer);
insert into E values(0);
insert into E values(2);
insert into E values(4);
insert into O values(1);
insert into O values(3);
insert into O values(5);
select E.A, O.B from E natural join O;

0|1
0|3
0|5
2|1
2|3
2|5
4|1
4|3
4|5
自己結合

　同じ表を結合させることを「自己結合」という。結合方法は内部・外部・交差のどれでもいい。ただし同一テーブル名なのでas句で別名定義する必要がある。また、as句は省略できる。

create table dices(V integer);
insert into dices values(1);
insert into dices values(2);
insert into dices values(3);
insert into dices values(4);
insert into dices values(5);
insert into dices values(6);
select d1.V || ',' || d2.V from dices as d1, dices as d2;
select d1.V || ',' || d2.V from dices as d1 cross join dices as d2;
select d1.V || ',' || d2.V from dices d1, dices d2;
select d1.V || ',' || d2.V from dices d1 cross join dices d2;

結果

1,1
1,2
1,3
1,4
1,5
1,6
2,1
2,2
2,3
2,4
2,5
2,6
3,1
3,2
3,3
3,4
3,5
3,6
4,1
4,2
4,3
4,4
4,5
4,6
5,1
5,2
5,3
5,4
5,5
5,6
6,1
6,2
6,3
6,4
6,5
6,6

結合制約

　結合条件。指定した条件に合ったレコードのみ結果として返す。なければ交差結合として返す。

　以下の各句はそれぞれの略記関係にある。省略するほど自由度が下がる。

  • on→略→using→略→natural
- 列名 キーにするか否か
on 任意 任意
using 同一 任意
natural 同一 する
on 式

　onは結合条件を記す。以下のレコードがあったとする。

pragma foreign_keys=0;
create table classes(name text);
create table students(name text, cid integer references classes(rowid));
insert into classes values('A');
insert into classes values('B');
insert into students values('Yamada', 1);
insert into students values('Suzuki', 2);
insert into students values('Tanaka', 1);
insert into students values('Toudou', 3);

　各クラスに所属する生徒一覧。rowidとcidをon句で結合条件にしている。

select
  c.name, s.name
from classes c, students s 
  on c.rowid=s.cid;

A|Yamada
B|Suzuki
A|Tanaka
using(列名,...)

　usingはonの略記。両方のテーブルに指定した列名があるときのみ使える。

　以下テーブルのとき、cidで結合する。

pragma foreign_keys=0;
create table classes(cid integer primary key, symbol text);
create table students(name text, cid integer references classes(cid));
insert into classes values(1, 'A');
insert into classes values(2, 'B');
insert into students values('Yamada', 1);
insert into students values('Suzuki', 2);
insert into students values('Tanaka', 1);
insert into students values('Toudou', 3);

select 
  c.symbol, s.name 
from classes c, students s 
  using(cid);

　natural句を使った略記法は以下。

select c.symbol, s.name 
from 
  classes c 
  natural join 
  students s;

where

　レコード抽出条件。絞込条件。

create table T(V integer);
insert into T values(0);
insert into T values(1);
insert into T values(2);

select * from T where 0 < V;

1
2
select * from T where 0 < V and V < 2;

1
select * from T where V=0 or V=2;

0
2

on句との違い

　on句との違いは、on句はNULL行が追加される点。外部結合のときに違いが出る。

group by

　集約する。group by句があるselect文は集約クエリと呼ぶ。ふつう、集約クエリのすべての列は、集約関数の引数か、group by句に記すことになる。

create table T(C text, D integer);
insert into T values('A', 10);
insert into T values('B', 87);
insert into T values('A', 40);
insert into T values('B', 32);

select C, avg(D) from T group by C;

A|25.0
B|59.5

裸列

　集約クエリにおいて、集約キー列や集約関数以外の列を裸列と呼ぶ。裸列は未定義である。集約の中にあるいずれかひとつが出る。

　以下、裸列Dを使ってみた。

select C, D from T group by C;

A|10
B|87

min(), max()

select C, max(D) from T group by C;

A|40
B|87
select C, min(D) from T group by C;

A|10
B|32
select C, min(D), max(D) from T group by C;

A|10|40
B|32|87

複数列

　複数の列でそれぞれグループ化する。カンマ区切りで続けて列を指定する。

create table T(year integer, class text, point integer);
insert into T values(1, 'A', 10);
insert into T values(1, 'B', 87);
insert into T values(1, 'A', 40);
insert into T values(1, 'B', 32);
insert into T values(2, 'A', 92);
insert into T values(2, 'A', 74);
insert into T values(2, 'A', 56);
insert into T values(2, 'B', 5);
insert into T values(2, 'B', 21);

select year, class, avg(point), min(point), max(point) 
from T 
group by year, class;

1|A|25.0|10|40
1|B|59.5|32|87
2|A|74.0|56|92
2|B|13.0|5|21

group by having

　抽出するグループの条件。where句のグループ版。

create table T(C text, D integer);
insert into T values('A', 10);
insert into T values('B', 87);
insert into T values('A', 40);
insert into T values('B', 32);

select C, avg(D) from T group by C having C = 'A';

A|25.0

window

　Window関数（窓関数、分析関数、OLAP関数、などとも呼ばれる）。結果セットを部分的に切り出した領域に集約関数を適用できる。新しい機能。

  • SQL関数 over
  • filter (where 式)
  • (窓名 partition by order by frame-spec)
  • frame-spec
    • range
    • row
    • groups

　複雑すぎてパターンを網羅できない。いつか別枠でやる。

create table t0(x integer primary key, y text);
insert into t0 values (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
select x, y, row_number() over (order by y) as row_number from t0 order by x;

1|aaa|1
2|ccc|3
3|bbb|2

複合

集合演算子

集合演算子 意味
union all 和集合（重複を含む）
union 和集合（重複を除く）
intersect 積集合（共通部分）
except 差集合（左にのみ存在する部分）

union all

　和集合（重複を含む）。

create table T(A integer);
create table U(A integer);
insert into T values(1);
insert into T values(3);
insert into T values(5);
insert into U values(1);
insert into U values(2);

select * from T union all select * from U;

1
3
5
1
2

union

　和集合（重複を除く）。

select * from T union select * from U;

1
2
3
5

intersect

　積集合（共通部分）。

select * from T intersect select * from U;

1

except

　差集合（非重複部分）。

select * from T except select * from U;

3
5
select * from U except select * from T;

2

order by

　並び替える。ソート方法は昇順・降順の2種類。省略時は昇順。ソートキーが複数あるときは記入順がそのまま優先順位になる。

順序 記法 意味
昇順 asc 値が小さいほうを先に出す（デフォルト）
降順 desc 値が大きいほうから先に出す 
create table T(A integer);
create table U(A integer);
insert into T values(1);
insert into T values(3);
insert into T values(5);
insert into U values(1);
insert into U values(2);

select * 
from (select * from T union all select * from U) A
order by A asc;

1
1
2
3
5
select * 
from (select * from T union all select * from U) A
order by A desc;

5
3
2
1
1

複数ソートキー

create table T(A integer, B text);
insert into T values(2, 'B');
insert into T values(1, 'B');
insert into T values(1, 'C');
insert into T values(1, 'A');
insert into T values(2, 'C');
insert into T values(2, 'A');

select A, B 
from T 
order by A, B;

1|A
1|B
1|C
2|A
2|B
2|C

select A, B 
from T 
order by A, B desc;

1|C
1|B
1|A
2|C
2|B
2|A

select A, B 
from T 
order by A desc, B;

2|A
2|B
2|C
1|A
1|B
1|C

select A, B 
from T 
order by A desc, B desc;

2|C
2|B
2|A
1|C
1|B
1|A

　第1ソートキーをBにする。

select A, B 
from T 
order by B, A;

1|A
2|A
1|B
2|B
1|C
2|C

collate

　照合シーケンスを指定できる。

create table T(A integer, B text);
insert into T values(2, 'B');
insert into T values(1, 'b');
insert into T values(1, 'C');
insert into T values(1, 'a');
insert into T values(2, 'c');
insert into T values(2, 'A');

select A, B 
from T 
order by A, B collate nocase;

1|a
1|b
1|C
2|A
2|B
2|c

　もし照合シーケンスが指定されていなければ以下のようになる。（大文字・小文字を区別する（文字コードポイント順でソート））

select A, B 
from T 
order by A, B;

1|C
1|a
1|b
2|A
2|B
2|c

limit

　取得するレコード数の上限を指定する。

　まず、1〜10の各値をレコードに持つ表をつくる。

with recursive cnt(x) as (values(1) union select x+1 from cnt where x < 10) select x from cnt;

1
2
3
4
5
6
7
8
9
10

　これをビューにする。

create view cnts as with recursive cnt(x) as (values(1) union select x+1 from cnt where x < 10) select x from cnt;

　これを5行だけ取得する。

select * 
from cnts
limit 5;

1
2
3
4
5

limit offset

　offsetは指定行数を飛ばす。

　先頭から2件飛ばして5件取得する。

select * 
from cnts
limit 5 offset 2;

3
4
5
6
7

　大量のレコードのうち指定件数の一部分だけを取得してみる。

.param init
.param set @limit 3
.param set @offset 0

select * 
from cnts
limit @limit offset @offset;

1
2
3

　次は4件目から3件を取得する。offset値をlimit分だけ足せばいい。

update sqlite_parameters set value=value+@limit where key='@offset';

select * 
from cnts
limit @limit offset @offset;

4
5
6

　上記のupdate文を繰り返すことで最後のレコードまで少しずつ取得できる。パラメータを使うことで同一SQL文にて実現しているのがポイント。

with

　サブクエリ。

with 
  t(x) as (values(1) union values(3)),
  u(x) as (values(5) union select * from t)
select * from t,u;

1|1
1|3
1|5
3|1
3|3
3|5

　ネストを回避できるので見やすくなる。

with recursive

　再帰。

with recursive cnt(x) as (
  values(1) union 
  select x+1 from cnt where x < 10
) 
select x from cnt;

1
2
3
4
5
6
7
8
9
10

　再帰用の一時テーブルを作れる。

values

values(1);

1

values(1,'A');

1|A

values(1,'A'),(2,'B');

1|A

　上記はinsertのvaluesでも使える。

create table T(A integer, B text);
insert into T values(1,'A'),(2,'B');
select * from T;

1|A

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで