hakeの日記

Windows環境でプログラミングの勉強をしています。

SQLite - 出力のソート（ORDER BY)

database SQLite

指定したカラムを昇順（ASC,省略可）または降順（DESC）でソートして出力する。

sqlite> select * from AAA;
tt          rr
----------  ----------
aaa         8.0
bbb         7.0
ccc         6.0
ddd         5.0
aaa         4.0
bbb         3.0
ccc         2.0
ddd         1.0


sqlite> select * from AAA order by tt asc, rr desc;
tt          rr
----------  ----------
aaa         8.0
aaa         4.0
bbb         7.0
bbb         3.0
ccc         6.0
ccc         2.0
ddd         5.0
ddd         1.0


sqlite> select * from AAA order by rr, tt desc;
tt          rr
----------  ----------
ddd         1.0
ccc         2.0
bbb         3.0
aaa         4.0
ddd         5.0
ccc         6.0
bbb         7.0
aaa         8.0

SQLite - カラム方向の結合（JOIN）

database SQLite

2つのテーブルの指定したカラムで結合することで２つのテーブルをカラム方向に広げたひとつのテーブルとして扱う

sqlite> select * from AAA;
tt          ii
----------  ----------
aaa         1
bbb         2
ccc         3
ddd         4
sqlite> select * from BBB;
ii          rr
----------  ----------
1           1.0
2           2.0
3           3.0
5           5.0

　
INNNER JOINまたはOUTER JOINにより、tt, ii, rrというカラムを持つひとつのテーブルに結合される。

内部結合（INNER JOIN)

続きを読む

SQLite - グループ化（GROUP BY）

database SQLite

指定したカラム毎にグループ化して、そのグループ内で処理を行う

sqlite> select * from AAA;
tt          cate        val
----------  ----------  ----------
aaa         A           1.0
bbb         A           2.0
aaa         A           3.0
bbb         B           4.0
aaa         B           5.0
bbb         B           6.0
aaa         C           7.0
bbb         C           8.0

　
selectにカラム名のみ記述した場合は、そのグループでの最初のレコードのみが出力される。グループ化対象を条件選択したい場合はwhere句を使用する。

続きを読む

SQLite - ビューとインデックス 

sqlite> create table AAA(ii integer, tt text, rr real);
sqlite> insert into AAA values(1, 'aaa', 0.8),
   ...> (2, 'aaa', 0.7),
   ...> (3, 'bbb', 0.6),
   ...> (4, 'bbb', 0.5),
   ...> (5, 'ccc', 0.4),
   ...> (6, 'ccc', 0.3),
   ...> (7, 'ddd', 0.2),
   ...> (8, 'ddd', 0.1);

sqlite> select * from AAA;
ii          tt          rr
----------  ----------  ----------
1           aaa         0.8
2           aaa         0.7
3           bbb         0.6
4           bbb         0.5
5           ccc         0.4
6           ccc         0.3
7           ddd         0.2
8           ddd         0.1

VIEW

続きを読む

SQLite - ROWIDとカラム制約事項

database SQLite

ROWID

特に指定しなくても、レコードをユニークに決定するROWIDというinteger型のカラムが内部的に付加される。

sqlite> create table AAA(ii integer, tt text);
sqlite> insert into AAA values(10, 'aaa'),(20, 'bbb');
sqlite> select rowid,* from AAA;
rowid       ii          tt
----------  ----------  ----------
1           10          aaa
2           20          bbb

　

PRIMARY KEY

主キーを指定する。

create table テーブル名( カラム1名 カラム1型名 primary key, ……);
create table テーブル名( カラム1名 カラム1型名, ……,  primary key(カラム名, …… ));

ひとつのカラムを主キーにする場合はどちらの書き方でも良いが、二つ以上のカラムを主キーにする場合は後者の書き方とする。
主キーの値（複数カラムの場合は値の組み合わせ）が同じレコードを追加することはできない。
注意点として、主キーをNULLにしたレコードは複数追加することが可能。ただし後でNULLを変更した場合に、既に存在する主キーを一致する場合はエラーになる。

sqlite> create table AAA(ii integer, tt text, primary key(ii, tt) );
sqlite> insert into AAA values(10, 'aaa');
sqlite> insert into AAA values(null, 'aaa');
sqlite> insert into AAA values(null, 'aaa');
sqlite> insert into AAA values(40, 'bbb');
sqlite> select rowid,* from AAA;
rowid       ii          tt
----------  ----------  ----------
1           10          aaa
2                       aaa
3                       aaa
4           40          bbb
sqlite> update AAA set ii=10 where rowid=2;
Error: UNIQUE constraint failed: AAA.ii, AAA.tt

sqlite> insert into AAA values(20, 'aaa');

sqlite> insert into AAA values(10, 'aaa');
Error: UNIQUE constraint failed: AAA.ii, AAA.tt

　

続きを読む