sqlite3コマンドの使い方

Sqlite3はポータブルなRDBエンジンです。OracleやPostgreSQLのような大きなRDBMSがなくても、ファイルにデータを蓄積してRDBを利用することができます。

GUIツールはいろいろあり便利ですが、ここではコマンドラインから利用するsqlite3コマンドを紹介します。

インストール方法

Ubuntuの場合、SQLite3のインストールは簡単です。

apt install でサクッとインストールができます。

$ sudo apt install sqlite3 -y
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following package was automatically installed and is no longer required:
  libfwupdplugin1
Use 'sudo apt autoremove' to remove it.
Suggested packages:
  sqlite3-doc
The following NEW packages will be installed:
  sqlite3
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 861 kB of archives.
After this operation, 2803 kB of additional disk space will be used.
Fetched 861 kB in 0s (29.0 MB/s)
Selecting previously unselected package sqlite3.
(Reading database ... 147378 files and directories currently installed.)
Preparing to unpack .../sqlite3_3.31.1-4ubuntu0.3_amd64.deb ...
Unpacking sqlite3 (3.31.1-4ubuntu0.3) ...
Setting up sqlite3 (3.31.1-4ubuntu0.3) ...
Processing triggers for man-db (2.9.1-1) ...

which sqlite3コマンドを実行して以下のように出力されれば成功です。

$ which sqlite3
/use/bin/sqlite3

sqlite3ファイルを作成する

sqlite3ファイルを作成するには以下のコマンドを実行します。

sqlite3 sqlite3file.db

実際にはこれだけではsqlite3ファイルは作成されません。Create Tableなどを実行することで初めてファイルが作成されます。

以下のように、SQLiteのTerminalに入ります。

$ sqlite3 sqlite3file.db 
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> 

このSQLite terminal から抜けるには、.exitコマンドを実行します。

sqlite> .exit
$ 

テーブルを作成する

CREATE TABLE IF NOT EXISTS table_name (
    Col0 INTEGER PRIMARY KEY,
    Col1 REAL,
    Col2 TEXT,
    Col3 Blob
) WITHOUT ROWID;

table_nameというテーブル名のテーブルが存在しない場合に、テーブルを作成します。

カラムは4つで、Col0はINTEGER型の主キーとして定義しています。

テーブル定義を表示する

作成したテーブル定義を表示してみましょう。

sqlite3から以下のコマンドを実行します。

.head on
.mode column
pragma table_info(table_name);

以下のように表示されたら成功です。

sqlite> .head on
sqlite> .mode column
sqlite> pragma table_info(table_name);
cid         name        type        notnull     dflt_value  pk        
----------  ----------  ----------  ----------  ----------  ----------
0           Col0        INTEGER     1                       1         
1           Col1        REAL        0                       0         
2           Col2        TEXT        0                       0         
3           Col3        Blob        0                       0         

SQLite3のデータ型

SQLite3には以下4種類のデータ型があります。

  • INTEGER : 符号付き整数
  • REAL : 浮動小数点
  • TEXT : テキスト文字列
  • BLOB : バイナリオブジェクト

https://www.sqlite.org/datatype3.html#datatypes_in_sqlite

Oracleなどと比べるととても少ないですが、その分考える手間が省けますね。

INSERT

レコードをインサートするには以下のようなINSERT文を実行します。他のRDBMSと同じですね。

INSERT INTO table_name values (1, 1, 'test string', null);

SELECT

SELECT文も他のRDBMSと同じですが、.head on.mode columnをつけることで見やすくなります。

.head on
.mode column
select * from table_name;
sqlite> select * from table_name;
Col0        Col1        Col2         Col3      
----------  ----------  -----------  ----------
0           1.0         test string            
1           1.0         test string            

UPDATE

UPDATE文も文法は同じです。

UPDATE table_name set Col2 = 'update string' where Col0 = 0;

SELECTで見てみるとたしかにUPDATEされています。

sqlite> UPDATE table_name set Col2 = 'update string' where Col0 = 0;
sqlite> select * from table_name;
Col0        Col1        Col2           Col3      
----------  ----------  -------------  ----------
0           1.0         update string            
1           1.0         test string              

commitとrollback

SQLiteでもcommitとrollbackを明示的に実行することができます。

以下はROLLBACKの例です。トランザクションの開始はBEGINで行います。

トランザクションの終了はENDですが、その前にROLLBACKを実行しており、このタイミングでトランザクションが終了しているため、END命令でエラーが表示されています。

sqlite> begin;
sqlite> UPDATE table_name set Col2 = 'next update string' where Col0 = 1;
sqlite> rollback;
sqlite> end;
Error: cannot commit - no transaction is active
sqlite> select * from table_info;
Error: no such table: table_info
sqlite> select * from table_name;
Col0        Col1        Col2           Col3      
----------  ----------  -------------  ----------
0           1.0         update string            
1           1.0         test string              
sqlite> 

以下はCOMMITの例です。BEGINでトランザクションを開始し、COMMITで終了しているという部分は先ほどのROLLBACKと同じです。

sqlite> begin;
sqlite> UPDATE table_name set Col2 = 'next update string' where Col0 = 1;
sqlite> commit;
sqlite> end;
Error: cannot commit - no transaction is active
sqlite> select * from table_name;
Col0        Col1        Col2           Col3      
----------  ----------  -------------  ----------
0           1.0         update string            
1           1.0         next update s            

DELETE

DELETE文もいつものと同じです。

DELETE FROM table_name where Col0 = 1;
sqlite> select * from table_name;
Col0        Col1        Col2           Col3      
----------  ----------  -------------  ----------
0           1.0         update string            

終わりに

SQLiteは決してパフォーマンスの悪いプロダクトではありません。昨今はいろいろなデータストアプロダクトがありますし、導入のしやすさはそれぞれですが、開発効率の面でSQLiteを採用することもあるかと思います。

その時のツールとしてGUIを使うかCUIを使うかいろいろな判断がありますが、CUIツールを採用する場合にこの記事が役に立てば幸いです。

コメントする