SQLite/基本操作

SQLiteは、アプリケーションプログラムにライブラリーとしてリンクされ、単一のファイルにデータベースを構築します。 このため、MySQLのようにサーバー・クライアントモデルで必要な、クライアントからのデータベース・サーバーへの接続・認証は必要なく、データベースファイルをオープンし使用を開始します。

コマンドラインインターフェース

前述の通り、SQLiteはアプリケーションプログラムに組込まれて使用されますが、データベースの保守管理は必要なので、その用途にコマンドラインインターフェースアプリケーション sqlite3が用意されています。

操作と結果概観

個別のコマンドの説明を列挙して解説する前に、実際に sqlite3 を使って

• データベースを開く
• テーブルを定義する
• テーブルにレコードを挿入する
• データを抽出する

などの操作を script(1) を使って記録して様子を掲載します。

typescript

$ cd
$ mkdir sqlite/
$ cd sqlite/
$ sqlite3 periodic.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> -- 
sqlite> CREATE TABLE elements ( number, name, symbol ) ;
sqlite> INSERT INTO element (number, name, symbol) VALUES(1, "Hydrogen", "H");
Parse error: no such table: element
sqlite> INSERT INTO elements (number, name, symbol) VALUES(1, "Hydrogen", "H");
sqlite> SELECT * FROM elements;
1|Hydrogen|H
sqlite> .header on
sqlite> SELECT * FROM elements;
number|name|symbol
1|Hydrogen|H
sqlite> INSERT INTO elements (number, name, symbol) VALUES(2, "Helium");
Parse error: 2 values for 3 columns
sqlite> INSERT INTO elements (number, name) VALUES(2, "Helium");
sqlite> SELECT * FROM elements;
number|name|symbol
1|Hydrogen|H
2|Helium|
sqlite> SELECT number,TYPEOF(number),name,TYPEOF(name),symbol,TYPEOF(symbol) FROM elements;
number|TYPEOF(number)|name|TYPEOF(name)|symbol|TYPEOF(symbol)
1|integer|Hydrogen|text|H|text
2|integer|Helium|text||null
sqlite> DELETE FROM elements WHERE name = "Helium" ;
sqlite> SELECT number,TYPEOF(number),name,TYPEOF(name),symbol,TYPEOF(symbol) FROM elements;
number|TYPEOF(number)|name|TYPEOF(name)|symbol|TYPEOF(symbol)
1|integer|Hydrogen|text|H|text
sqlite> .schema
CREATE TABLE elements ( number, name, symbol );
sqlite> .databases
main: /home/user1/sqlite/periodic.db r/w
sqlite> .dump elements
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE elements ( number, name, symbol );
INSERT INTO elements VALUES(1,'Hydrogen','H');
COMMIT;
sqlite> ^D
$ ls -l
total 20
-rw-r--r--. 1 chronos chronos 8192 Nov 24 07:19 periodic.db
$ file periodic.db 
periodic.db: SQLite 3.x database, last written using SQLite version 3040000, file counter 4, database pages 2, cookie 0x1, schema 4, UTF-8, version-valid-for 4

個別のコマンドの説明

データベースを開く

SQLiteのデータベースは、単一のファイルです。 なのでデータベースへは、S/Cモデルのように接続するのではなくデータベースファイルを開きます。

sqlite3起動時にデータベースファイルを開く

sqlite3 の起動時パラメーターにデータベースファイルを与えると、データベースがオープンした状態で起動します。

sqlite3起動時にデータベースファイルを開く

$ sqlite3 periodic.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> .databases 
main: /home/user1/sqlite/periodic.db r/w
sqlite>

このとき、データベースファイルがない場合、新しいデータベースファイルが作られます。

.databasesは、オープン中のデータベースの一覧を表示する sqlite3 のコマンドです。

.open

sqlite3をパラメータなしで起動したあと、.openコマンドでデータベースファイルを開きます。

sqlite3を起動するときにデータベースファイルを与え開く

$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> .open periodic.db
sqlite> .databases 
main: /home/user1/sqlite/periodic.db r/w
sqlite>

このとき、データベースファイルがない場合、新しいデータベースファイルが作られます。

$ sqlite3
sqlite>

が、sqlite3 のプロンプトです。

.databases

書式

.databases

例

sqlite> .databases
main: /home/user1/sqlite/sample.db r/w

同義のSQL

PRAGMA database_list;

.databases コマンドは、現在開いているデータベースに関する情報を表示します。

.databases までタイプしなくても、.datで同じ意味になります。

これは、.databases にかぎらず、多くのコマンドで. を含め先頭４文字までタイプすればコマンドの別名として受け付けられます。

.open や .databases は、SQLではなくsqlite3のコマンドで、SQLと異なり大文字小文字を区別します。

sqlite> .OPEN periodic.db
Error: unknown command or invalid arguments:  "OPEN". Enter ".help" for help
sqlite> _

.help

sqliteのオンラインヘルプコマンドは.helpです。下記に3.40.0の.helpを示します（なるべく解説へのリンクにしました）

.help

$ sqlite3 /dev/null .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB FROM the existing database
.connection [close] [#]  Open or close an auxiliary database connection
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?OBJECTS?          Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data FROM FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?OPTIONS?     Set output mode
.nonce STRING            Suspend safe mode for one command if nonce matches
.nullvalue STRING        Use STRING in place of NULL VALUES
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program
.read FILE               Read input FROM FILE or command output
.recover                 Recover as much data as possible FROM corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") FROM FILE
.save ?OPTIONS? FILE     Write database to FILE (an alias for .backup ...)
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current VALUES for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ...        Run various sqlite3_test_control() operations
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output
$ 

全部のコマンドを覚える必要はありませんが、「○○○なる機能はない」という前に確認しましょう。

データベースファイルの拡張子

SQLiteのデータベースファイルの拡張子には、.db、.sqliteや.sqlite3が用いられます。 SQLite自身は、拡張子によって動作を変えるようなことはしていないので、どのような拡張子でも構いませんが、オペレーションシステムの拡張子とファイルタイプの関連付けと重複しないように注意が必要です。

ちなみにSQLiteバージョン３のデータベースファイルのIANAメディアタイプは、application/vnd.sqlite3です。

file(1)が返す情報

$ file periodic.db
periodic.db: SQLite 3.x database, last written using SQLite version 3040000, file counter 4, database pages 2, cookie 0x1, schema 4, UTF-8, version-valid-for 4
$ _

どれがSQLiteのデータベースファイルかわからなくなったときに役に立ちます。

終了コマンド

sqlite3は、.exit、.quitまたは^D（EOF）で終了します。

書式

.exit
.quit
^D (ctrl-D)

例

$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit
$

sqlite3 を起動してすぐ終了。

複数行入力モードからの回復

タイプミスなどで複数行入力モードに入ることがあります

$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> open periodic.db
   ...> ;
Parse error: near "open": syntax error
  open periodic.db ;
  ^--- error here
sqlite>

タイプミスの場合どのみち syntax error なので ;（セミコロン）を１文字入力します。

このほか、文字列の ” や ’ が閉じられていない場合も考えられます。

テーブル定義

テーブルを定義する場合は CREATE TABLE コマンドを使います。

CREATE TABLE

テーブル elements の構成とレコード例

原子番号	元素名英名	元素記号
number	name	symbol
1	Hydrogen	H
2	Helium	He
3	Lithium	Li
4	Beryllium	Be
5	Boron	B
6	Carbon	C
7	Nitrogen	N
8	Oxygen	O
9	Fluorine	F
10	Neon	Ne
11	Sodium	Na
12	Magnesium	Mg

書式

CREATE TABLE テーブル名( カラム名１ [ 型１ ], カラム名２ [ 型２ ], カラム名２ [ 型３ ] … カラム名ｎ [ 型ｎ ]) ;

例

CREATE TABLE elements( number, name, symbol );

最後の ;（セミコロン）を忘れがちです。気をつけましょう。

ここでは「型」を省略しています。

テーブル削除

テーブルを削除する場合は DROP TABLE コマンドを使います。

DROP TABLE

[TODO]

カラムの型

SQLiteの型システムは、標準SQLとはポリシーが異なっており、カラムに定義された型と異なる型の値が与えられると、可能な限り情報を落とさないように、場合によっては型を変えて保存します。

$ sqlite3
sqlite> CREATE TABLE test(r1, r2);
sqlite> INSERT INTO test VALUES(null, 42);
sqlite> INSERT INTO test VALUES(2.73, "Shizuoka" );
sqlite> INSERT INTO test VALUES("Hello", 1e-10);
sqlite> SELECT * FROM test;
|42
2.73|Shizuoka
Hello|1.0e-10
sqlite> SELECT r1, TYPEOF(r1), r2, TYPEOF(r2) FROM test;
|null|42|integer
2.73|real|Shizuoka|text
Hello|text|1.0e-10|real
sqlite> CREATE TABLE test2(r1 TEXT, r2 NUMERIC);
sqlite> INSERT INTO test2 VALUES(null, 42);
sqlite> INSERT INTO test2 VALUES(2.73, "Shizuoka" );
sqlite> INSERT INTO test2 VALUES("Hello", 1e-10);
sqlite> SELECT r1, TYPEOF(r1), r2, TYPEOF(r2) FROM test2;
|null|42|integer
2.73|text|Shizuoka|text
Hello|text|1.0e-10|real
sqlite>

TYPEOF(カラム名)関数でレコードのカラムの型（実際の型）を参照できます。

TEXTと定義されたカラムに数値が与えられると、数値を文字列表現に変換されて保存されます。

NUMERICと定義されたカラムに文字列が与えられると、

REALに変換できればREALで保存されます。

INTEGERに変換できればINTEGERで保存されます。

それ以外は、TEXTで保存されます。

これは、テーブル定義と異なる型の値がカラムに与えられた場合、SQLiteが情報を落としてしまうと、SQLiteを利用するアプリケーションプログラムが、適切な判断を行う手段を失ってしまうからです。

このような設計上に選択にも、SQLiteが単体のデータベースシステムではなく、アプリケーションプログラムに組込まれるRDBMSエンジンであるという立ち位置の特徴が現れています。

テーブルへのデータの登録

ではさっそく、1番目の水素のデータをつけたしてみましょう。

INSERT INTO

書式

INSERT INTO テーブル ( カラム１,  カラム２, カラム３, … カラムｎ ) VALUES ( 値１, 値２, 値３, … 値ｎ ) ;

例

INSERT INTO elements (number, name, symbol) VALUES(1, "Hydrogen", "H" );

データの抽出

SELECT

書式

SELECT カラムリスト FROM テーブル ;

例

sqlite> select name FROM elements;
Hydrogen
sqlite> select name,number FROM elements;
Hydrogen|1
sqlite> select symbol,name,number FROM elements;
H|Hydrogen|1
sqlite> SELECT * FROM elements;
1|Hydrogen|H

カラムリストに * を与えると「すべてのカラム」となります。

.header

SELECTコマンドの結果にカラムの名前を含めるか含めないかは、 .headerコマンドで切替えできます。

書式

.header on
.header off

例

sqlite> .header on
sqlite> select symbol,name,number FROM elements;
symbol|name|number
H|Hydrogen|1

.header onでSELECTの結果の１行目にカラムリストを表示します

.header offでSELECTの結果の１行目にカラムリストを表示しません。

ディフォルト は、.header off。

on は、1, true, yes の３つの別名を持ちます。

off は、0, false, no の３つの別名を持ちます。

未入力カラムのあるレコードの挿入

レコード（行）を挿入するとき、何らかの理由で全カラムを埋めることができなかった場合は、一部のカラムだけからなるレコードを挿入することもできます。 また、テーブル定義でカラムをNOT NULL制約することでレコードを挿入するときに必須のカラムを設け、入力がない場合をエラーとし挿入を拒絶することもできあます。

未入力カラムのあるレコードの挿入

sqlite> INSERT INTO elements ( number, name ) VALUES ( 2, "Helium" );
sqlite> select name,number,symbol,TYPEOF(symbol) from elements;
name|number|symbol|TYPEOF(symbol)
Hydrogen|1|H|text
Helium|2||null

未入力の２列目のsymbolの値は””、型はnullとなっているのがわかります

TYPEOF()はカラムの「型」を返す関数です。

行の削除

行の削除は、DELETEコマンドとWHERE演算子を併用します。

書式

DELETE FROM テーブル WHERE カラム = 値 ;

DELETE

ヘリウムのnameデータ ”Helium” の行（レコード）を削除

sqlite> SELECT * FROM elements;
1|Hydrogen|H
2|Helium||null
sqlite> DELETE FROM elements WHERE name = "Helium" ;
sqlite> SELECT * FROM elements;
1|Hydrogen|H

削除対象は、行です（カラムの削除ではありません）

WHERE 以降を忘れて DELETE FROM elementsとすると、elementsテーブルの全てのレコードが削除されます。

テーブル定義の表示

.schema コマンド(Show the CREATE statements matching PATTERN)で、CREATEのフォーマットでテーブル定義を表示できます。

.schema

書式

.schema

例

sqlite> .schema
CREATE TABLE elements( number, name, symbol );

同義のSQL

SELECT name,sql FROM sqlite_master WHERE type="table";

sqlite_masterというテーブルがSQLiteのそれぞれのデータベースにあり、テーブルやインデックス・ビューを管理しています。

実際のアプリケーションでは、sqlite3のコマンドは使えないので、同じ機能のSQLを覚えていくことはとても重要です。

テーブルの一覧

.tables コマンドでテーブルの一覧を表示できます。

.tables

書式

.tables

例

sqlite> .tables
elements

同義のSQL

SELECT name FROM sqlite_master WHERE type="table";

sqlite_masterというテーブルがSQLiteのそれぞれのデータベースにあり、テーブルやインデックス・ビューを管理しています。

実際のアプリケーションでは、sqlite3のコマンドは使えないので、同じ機能のSQLを覚えていくことはとても重要です。