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
にかぎらず、多くのコマンドで. を含め先頭4文字までタイプすればコマンドの別名として受け付けられます。
.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バージョン3のデータベースファイルのIANAメディアタイプは、application/vnd.sqlite3
です[1]。
- 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 なので
;
(セミコロン)を1文字入力します。
このほか、文字列の ” や ’ が閉じられていない場合も考えられます。
テーブル定義
[編集]テーブルを定義する場合は CREATE TABLE コマンドを使います。
CREATE TABLE
[編集]原子番号 | 元素名英名 | 元素記号 |
---|---|---|
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 テーブル名( カラム名1 [ 型1 ], カラム名2 [ 型2 ], カラム名2 [ 型3 ] … カラム名n [ 型n ]) ;
- 例
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(カラム名)
関数でレコードのカラムの型(実際の型)を参照できます。- REALに変換できればREALで保存されます。
- INTEGERに変換できればINTEGERで保存されます。
- それ以外は、TEXTで保存されます。
これは、テーブル定義と異なる型の値がカラムに与えられた場合、SQLiteが情報を落としてしまうと、SQLiteを利用するアプリケーションプログラムが、適切な判断を行う手段を失ってしまうからです。
このような設計上に選択にも、SQLiteが単体のデータベースシステムではなく、アプリケーションプログラムに組込まれるRDBMSエンジンであるという立ち位置の特徴が現れています。
テーブルへのデータの登録
[編集]ではさっそく、1番目の水素のデータをつけたしてみましょう。
INSERT INTO
[編集]- 書式
INSERT INTO テーブル ( カラム1, カラム2, カラム3, … カラムn ) VALUES ( 値1, 値2, 値3, … 値n ) ;
- 例
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の結果の1行目にカラムリストを表示します.header off
でSELECTの結果の1行目にカラムリストを表示しません。- ディフォルト は、
.header off
。 - on は、1, true, yes の3つの別名を持ちます。
- off は、0, false, no の3つの別名を持ちます。
未入力カラムのあるレコードの挿入
[編集]レコード(行)を挿入するとき、何らかの理由で全カラムを埋めることができなかった場合は、一部のカラムだけからなるレコードを挿入することもできます。 また、テーブル定義でカラムを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
- 未入力の2列目の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を覚えていくことはとても重要です。