sqlite を操作するには、sql と呼ばれるデータベース操作言語を使用します。
リレーショナルデータベースシステム(RDBS) に使用されており、ISOやANSI、JISによって標準化されています。但し、データベースによって、独自の拡張・縮小を加えているので、使用するに当たって、それぞれの仕様を確認する必要があります。
SQLのコマンドは、機能によって「データ定義言語 （DDL：Data Definition Language）」「データ操作言語（DML：Data Manipuration Language）」「データ制御言語（DCL：Data Control Language）」の3つに分類することができます。
以下は、sqlite3における基本となる sql 言語定義となります。
データ定義言語 (DDL)
データベース作成
$ sqlite3 database_name;
ターミナルウィンドウを立ち上げ、sqlite3 コマンドの引数にデータベース名をつけることで作成されます。
database_name が sqlite3データベースのファイル名となり、カレントディレクトリに作成されます。
引数なしでもテーブル作成、操作は出来ますが、saveコマンドで保存しない限り、sqlite3ドットコマンドを終了すると、データは消滅してしまいます。takai@takai-pc ~/db
$ sqlite3 basic_sample.sq3
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite>テーブル作成 [CREATE TABLE]
CREATE TABLE table-name (column_name1[datatype][table-constraint ],
column_name2[datatype][table-constraint ],
,
,
) [table-options];
table-constraint テーブル制約 ( primary key, unique, foreign key, check など)
table-options テーブルオプション ( without rowid, struct など )sqlite> create table friend (id integer primary key, f_name text, bone_year integer, email text);同じ名前のテーブル、インデックス、またはビューが既に含まれているデータベースに CREATE TABLE ステートメントを発行しようとすると、通常はエラーになります。この場合は、CREATE TABLE IF NOT EXISTS …. とすれば、回避できます。
テーブルの定義を変更する [ALTER]
ALTER TABLE table-name ADD column_name [datatype]; 列を追加する
追加したカラムはテーブルの最後に追加されます。
カラムを追加する場合は次の条件を満たしていなければなりません。
1. PRIMARY KEY や UNIQUE 制約は設定できない
2. DEFAULT 制約を設定する時は、CURRENT_TIME/CURRENT_DATE/CURRENT_TIMESTAMP は指定できない
3. NOT NULL 制約を設定する時は、NULL以外のデフォルト値の設定が必要
ALTER TABLE DROP column_name; 列を削除する
カラムを追加する場合は次の条件を満たしていなければなりません。
1. PRIMARY KEY ではない
2.
ALTER TABLE table_name RENAME TO new_table_name; テーブル名を変更するテーブルを削除する [DROP]
DROP TABLE table_name; テーブルを削除するデータ操作言語 (DML)
データを挿入・追加する [INSERT]
INSERT INTO table-name ( column1, column2,...columnN)
VALUES ( value1, value2,....valueN);
レコードをついか・挿入するsqlite> insert into friend values (null, 'tanaka', 1990, 'unknown@mail.com');
sqlite> insert into friend values (null, 'nisi', 1995, 'unknown2@mail.com');
sqlite> insert into friend values (null, 'miyata', 2005, 'unknown3@mail.com');
sqlite> insert into friend values (null, 'arai', 1970, 'unknown4@mail.com');
sqlite> insert into friend values (null, 'hamada', 1962, 'unknown4@mail2.com');書き込みステートメントであるINSERT を発行すると、暗黙にトランザクションモードとなります。デフォルトでは、占有ロックがかかり、他のプロセス受け入れが遮断されます。SELECT などの読み込みステートメントと比べ、実行コストがかかります。よって、単独で INSERT ステートメントを発行するよりも、複数の INSERT ステートメントを BEGIN COMMIT で囲むと、すべての挿入が 1 つのトランザクションにグループ化され、実行速度の短縮が期待されます。
まとめてデータ挿入処理[INSERT INTO,… VALUES(…),(…),(…)]
sqlite> create table multi_insert_sample (i integer primary key, t text);
sqlite> insert into multi_insert_sample(t) values ('takai'),
('ito'),
('tanaka');
sqlite> select * from multi_insert_sample;
i t
- ------
1 takai
2 ito
3 tanakaデータを参照する [SELECT]
SELECT column1, column2,...columnN
FROM table_name;
table_nameのcolumn1, column2,・・・・を参照する。データの析出条件をつける [WHERE]
SELECT column1, column2....columnN
FROM table_name
WHERE [condition | exoression];
condition: 条件式
expression: 析出計算式JOIN、INNER JOIN、または CROSS JOIN の場合、WHERE 句の制約式と ON 句の制約式の間に違いは概ねありません。
データ数をカウントして表示する [COUNT]
SELECT COUNT(column_name)
FROM table_name;
table_nameテーブルにあるcolumn_name列のデータ数をカウントして表示する。重複レコードを排除し、一意のレコードのみを参照する [DISTINCT]
SELECT DISTINCT column1, column2,.....columnN
FROM table_name;
table_nameテーブルにあるcolumn1, column2,.....columnN列群と同じ列群を含めず参照する。サブクエリーで析出した条件がtrueになるカラムを返す [EXISTS]
SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM table_name2 );
table_nameテーブルに対して、table_name2テーブルのcolumn_nameが真になるレコードを析出し、column1, column2,,,columnNを表示する。、文字パターンによる照合 [GLOB]
SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };
GLOBパターンに一致するレコードを析出する
使用可能ワイルドカード * (０個以上の数字または文字)、? (１個の数字または文字)カラム名によるクループ化でグループごとに集計 [GROUP BY]
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
条件式によって析出したレコードに対して、column_name列に共通するレコードごとにグループ化し、そのグループ化されたレコード数を表示する。最終結果に表示されるグループの結果をフィルタリングする条件を指定[HAVING]
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
グループ化されたレコードに対して、フィルタリングしてそのレコード数を表示する。1を返すサブクエリ [IN]
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
[val-1, val-2, ...val-Nが存在するcolumn_nameがある]とするレコード析出条件で表示するワイルドカードを使用してテキスト値をパターンと照合 [LIKE]
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
使用可能ワイルドカード：% (０個以上の数字または文字)、_ (１個以上の数字または文字)
'%' にマッチさせたい場合 --> '^%' escape '^'
'_' にマッチさせたい場合 --> '^_' escape '^'
エスケープ文字である '^' は '%' '_' 以外の任意の一文字であればよい。 sqlite> select 'abcd' like '%bc_'; -- '%' '_' はワイルドカード
1
sqlite> select '\abcd' like '\abc_'; -- '_' はワイルドカード
1
sqlite> select '_abcd' like '_abc_'; -- '_' はワイルドカード
1
sqlite> select 'labcd' like '_abc_' escape '_'; -- '_' はエスケープ文字
0 -- エスケープ文字にワイルドカードを使うとうまくいかない
sqlite> select '99%' like '99%'; -- % はワイルドカード
1
sqlite> select '999' like '99%'; -- '%' はワイルドカード
1
sqlite> select '999' like '99%' escape '%'; -- '%' はワイルドカード
0
sqlite> select '99%' like '99%' escape '%';
0 -- エスケープ文字'%'にワイルドカードを使うとうまくいかない
sqlite> select '99%' like '99^%' escape '^'; -- '^' はエスケープ文字
1
sqlite> select '^99%' like '^99^%' escape '^'; -- '^' はエスケープ文字
0 -- 検索対象にエスケープ文字が入っているとうまくいかない
sqlite> select '^99%' like '^99\%' escape '\'; -- '\' はエスケープ文字
1 -- 検索対象文字列で使われていない文字を使用するとうまくいく
sqlite> select '\n' like '\n'; -- '\' はエスケープ文字ではない
1
sqlite> select '\n' like '\\n';
00を返すサブクエリ [NOT IN]
ELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);
[val-1, val-2, ...val-Nが存在しないcolumn_nameがある]とするレコード析出条件で表示する。データを昇順または降順で並べ替える [ORDER BY]
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
昇順(ASC)、降順(DESC) でデータを並び替えて表示する。デフォルトではASC データ量を制限する [LIMIT]
SELECT column1, column2, columnN
FROM table_name
LIMIT [limit number];
SELECT column1, column2, columnN
FROM table_name
LIMIT [limit number] OFFSET [start number ]; -- start number = row counted from 0 start.
numberまでのデータを表示する。OFSETがあれば、OFSETからnumberまでのデータを表示する。データを更新する [UPDATE]
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE condition ];
条件式に従って、columnN列をvalueNに更新する。
WHERE 句がない場合、テーブル内のすべての行が UPDATE によって変更されます。データを削除する [DELETE]
DELETE FROM table_name WHERE {condition};
table_nameテーブルにおいて、条件式に従ってレコードを削除する。---- friend テーブルの id が 2 のデータを削除する。
sqlite> delete from friend where id=2;
sqlite> select * from friend;
id f_name bone_year email
-- ------ --------- ------------------
1 tanaka 1990 unknown@mail.com
3 miyata 2005 unknown3@mail.com
4 arai 1970 unknown40@mail.com
5 hamada 1962 unknown4@mail2.com使用例
sqlite> .mode column
sqlite> insert into friend values (2, 'nisi', 1995, 'unknown2@mail.com');
-- friend テーブルのすべてのデータを表示する。
sqlite> select * from friend;
id f_name bone_year email
-- ------ --------- ------------------
1 tanaka 1990 unknown@mail.com
2 nisi 1995 unknown2@mail.com
3 miyata 2005 unknown3@mail.com
4 arai 1970 unknown4@mail.com
5 hamada 1962 unknown4@mail2.com
-- friend テーブルの f_name 列のデータを表示する。
sqlite> select f_name from friend;
f_name
------
tanaka
nisi
miyata
arai
hamada
---- friend テーブルの bone_year 列が 1990 より小さいデータを表示する。
sqlite> select * from friend where bone_year < 1990;
id f_name bone_year email
-- ------ --------- ------------------
4 arai 1970 unknown4@mail.com
5 hamada 1962 unknown4@mail2.com
---- count()
-- null を含むレコードを追加する。
sqlite> insert into friend values(null, 'unknown', null, null);
sqlite> select * from friend;
id f_name bone_year email
-- ------- --------- ------------------
1 tanaka 1990 unknown@mail.com
2 nisi 1995 unknown2@mail.com
3 miyata 2005 unknown3@mail.com
4 arai 1970 unknown40@mail.com
5 hamada 1962 unknown4@mail2.com
6 unknown
-- テーブルのレコード総数 count()
sqlite> select count() from friend;
count()
-------
6
-- 特定フィールドのレコード総数 count(column name)
sqlite> select count(bone_year) from friend;
count(bone_year)
----------------
5
---- 一番若くない年齢のレコードを析出
-- 一番若くない年齢を析出
sqlite> select min(bone_year) from friend;
min(bone_year)
--------------
1962
-- 上記のレコードを析出 （サブクエリー）
sqlite> select * from friend
...> where
...> bone_year = (select min(bone_year) from friend)
...> ;
id f_name bone_year email
-- ------ --------- ------------------
5 hamada 1962 unknown4@mail2.com
副問い合わせ (subquery)
副問い合わせ処理は、select 文の結果を利用してsql文を発行し、結果を得ることです。前者をサブクエリー、後者をメインクエリーと呼ばれております。最初にサブクエリーが実行さ れ、その結果をもって、メインクエリーが実行されます。サブクエリーを発行できる条件は以下のようです。
- サブクエリは括弧で囲む必要があります。
- サブクエリは、選択された列を比較するために、通常メイン クエリに含まれている一つの列との間でSELECT 句を発行することになります。
- メイン クエリでは ORDER BY を使用できますが、サブクエリでは ORDER BY を使用できません。GROUP BY は、サブクエリで ORDER BY
- 複数の行を返すサブクエリは、IN 演算子などの複数値演算子でのみ使用できます。
- BETWEEN 演算子はサブクエリでは使用できません。
- select文、insert文、update文、delete文で使用で決ます
- where 条件文、from析出列条件、select列、having条件式として使われます。
---- friend テーブルのbone_yearの平均値より高いレコードを析出する
sqlite> select AVG(bone_year) from friend; -- サブクエリ
AVG(bone_year)
----------------
1984.4 -- 返却値となる
sqlite> select * from friend where bone_year > (select AVG(bone_year) from friend);
-- 条件式 where bone_year > 1984
id f_name bone_year email
-- ------ --------- -----------------
1 tanaka 1990 unknown@mail.com
2 nisi 1995 unknown2@mail.com
3 miyata 2005 unknown3@mail.com
---- friendテーブルのf_name、bone_year、bone_yearの平均値より高いbone_year値をavg_over_judge句という名称で析出する
sqlite> select f_name, bone_year, bone_year > (select AVG(bone_year) from friend ) as avg_over_judge from friend; -- select 列でのサブクエリ
f_name bone_year avg_over_judge
------- --------- --------------
tanaka 1990 1
nisi 1995 1
miyata 2005 1
arai 1970 0
hamada 1962 0
unknown
---- friendテーブルのbone_year値が1990より高いレコードの件数を析出
sqlite> select bone_year from friend where bone_year > 1990;
bone_year
---------
1995
2005
sqlite> select count(bone_year) as over_1990_count from friend where bone_year in (select bone_year from friend where bone_year > 1990);
-- where bone_year in (1995, 2005) とする条件式になる。
over_1990_count
---------------
2
---- friend テーブルのレコードをcopyed_friendテーブルにコピーする
sqlite> create table copyed_friend (id integer primary key, f_name text, bone_year integer, email text);
sqlite> select id from friend;
id
--
1
2
3
4
5
6
sqlite> insert into copyed_friend select * from friend where id in (select id from friend);
sqlite> select * from copyed_friend; -- where id in (1, 2, 3, 4, 5, 6)とする条件式
id f_name bone_year email
-- ------- --------- ------------------
1 tanaka 1990 unknown@mail.com
2 nisi 1995 unknown2@mail.com
3 miyata 2005 unknown3@mail.com
4 arai 1970 unknown40@mail.com
5 hamada 1962 unknown4@mail2.com
6 unknown
---- friendテーブルのbone_yearが1990より高いレコードのf_name値を大文字にかえる
sqlite> update friend set f_name=upper(f_name) where bone_year in (select bone_year from copyed_friend where bone_year > 1990); -- where bone_year in (1995, 2005)とする条件式
sqlite> select * from friend;
id f_name bone_year email
-- ------- --------- ------------------
1 tanaka 1990 unknown@mail.com
2 NISI 1995 unknown2@mail.com -- upper('nish')
3 MIYATA 2005 unknown3@mail.com -- upper('miyata')
4 arai 1970 unknown40@mail.com
5 hamada 1962 unknown4@mail2.com
6 unknown
---- friendテーブルのbone_yearの値が1990より低いレコードを削除する
sqlite> delete from friend where bone_year in (select bone_year from copyed_friend where bone_year < 1990); -- where bone_year in (1970, 1962)とする条件式
sqlite> select * from friend;
id f_name bone_year email
-- ------- --------- -----------------
1 tanaka 1990 unknown@mail.com
2 NISI 1995 unknown2@mail.com
3 MIYATA 2005 unknown3@mail.com
6 unknownデータ定義言語 (DDL) + データ操作言語 (DML)
SELECT ステートメントの結果に基づいてデータベース テーブルを作成[AS SELECT]
CREATE TABLE new_table_name AS SELECT column1, column2,...FROM table_name [WHERE condition];
table_nameテーブルの列定義、レコードを析出して、その結果をnew_table_nameテーブルとして作成する。
PRIMARY KEY も制約もありません。
各列のデフォルト値は NULL です。
sqlite> create table friend2 as select id, f_name from friend where bone_year < 2000;
sqlite> select * from friend2;
id f_name
-- ------
1 tanaka
4 arai
5 hamada
sqlite> select rowid from friend2;
rowid
-----
1
2
3データベース制御言語 (DCL)
トランザクション処理に使われる、「commit rollup」、データベースのアクセス権を操作する「grant revoke」があります。sqlite3 では、アクセス権の操作は出来ないので、grant revoke は使うことが出来ません。
