SQLite3 使い方：コマンドラインでデータ操作をマスター

SQLite3 使い方：コマンドラインでデータ操作をマスター

SQLite3は、軽量で組み込み型のデータベースエンジンであり、C言語ライブラリとして実装されています。その小ささと手軽さから、Webブラウザ、スマートフォン、組み込みシステムなど、さまざまなプラットフォームで使用されています。SQLite3データベースは、単一のディスクファイルに保存されるため、配布やバックアップが簡単です。

この記事では、SQLite3のコマンドラインインターフェース（CLI）を使用して、データベースの作成、テーブルの操作、データの追加、クエリ、削除などの基本的な操作をマスターする方法を詳細に解説します。

1. SQLite3のインストールと起動

まず、SQLite3がシステムにインストールされているか確認します。ターミナルを開き、以下のコマンドを実行してください。

bash
sqlite3 --version

バージョン情報が表示されれば、インストールは完了しています。もしインストールされていない場合は、お使いのオペレーティングシステムに対応した方法でインストールしてください。

• Linux (Debian/Ubuntu):

  bash
sudo apt-get update
sudo apt-get install sqlite3
  * Linux (Fedora/CentOS/RHEL):

  bash
sudo dnf install sqlite
  * macOS:

  macOSには通常、SQLite3がプリインストールされています。もしインストールされていない場合は、Homebrewを使ってインストールできます。

  bash
brew install sqlite
  * Windows:

  SQLite3公式サイト (https://www.sqlite.org/download.html) から、Windows用のバイナリをダウンロードし、パスが通った場所に配置してください。

インストールが完了したら、SQLite3を起動します。以下のコマンドを実行してください。

bash
sqlite3 mydatabase.db

mydatabase.db はデータベースファイルの名前です。存在しない場合、SQLite3はこの名前で新しいデータベースファイルを作成します。すでに存在するデータベースファイル名を指定すると、そのデータベースに接続します。

SQLite3 CLIが起動すると、sqlite> というプロンプトが表示されます。ここから、SQLite3のコマンドを入力できます。

2. 基本的なコマンド

SQLite3 CLIで利用できる基本的なコマンドをいくつか紹介します。

• .help: 利用可能なコマンドのリストを表示します。

  sqlite
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.explain Show output of EXPLAIN on, off, auto
.headers ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indexes ?TABLE? Show names of all indexes
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Set string to print for NULL values
.once FILENAME Output once to FILENAME
.open FILENAME Open a database file
.output FILENAME Send output to FILENAME
.print STRING... Print STRING literally
.prompt MAIN CONTINUE Replace standard prompts
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.schema ?TABLE? Show the CREATE statements
.separator STRING Change separator string for .mode list
.shell CMD ARGS... Invoke system shell
.show Show the current values for various settings
.stats ON|OFF Turn stat output on or off
.tables ?TABLE? List names of tables
.timeout MS Try opening locked tables for MS milliseconds
.width NUM NUM ... Set column widths for "column" mode

• .tables: データベース内のすべてのテーブルの名前を表示します。

  sqlite
sqlite> .tables

• .schema [テーブル名]: 指定されたテーブルのCREATE TABLEステートメントを表示します。

  sqlite
sqlite> .schema users
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER
);

• .exitまたは.quit: SQLite3 CLIを終了します。

  sqlite
sqlite> .exit

3. テーブルの作成

テーブルを作成するには、CREATE TABLEステートメントを使用します。

sqlite
CREATE TABLE [テーブル名] (
[カラム名] [データ型] [制約],
[カラム名] [データ型] [制約],
...
);

• テーブル名: 作成するテーブルの名前を指定します。
• カラム名: テーブル内の各カラムの名前を指定します。
• データ型: 各カラムに格納できるデータの型を指定します。一般的なデータ型には、INTEGER, TEXT, REAL, BLOB, NUMERICなどがあります。
• 制約: カラムに適用される制約を指定します。一般的な制約には、PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECKなどがあります。

例:

usersという名前のテーブルを作成し、id, name, email, ageという4つのカラムを定義します。

sqlite
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER
);

この例では、

• idカラムはINTEGER型で、PRIMARY KEY制約とAUTOINCREMENT制約が設定されています。PRIMARY KEYはテーブル内の各行を一意に識別するための主キーを指定し、AUTOINCREMENTは新しい行が挿入されるたびに自動的にインクリメントされるように指定します。
• nameカラムはTEXT型で、NOT NULL制約が設定されています。NOT NULLは、このカラムにNULL値を格納できないように指定します。
• emailカラムはTEXT型で、UNIQUE制約とNOT NULL制約が設定されています。UNIQUEは、このカラムの値がテーブル内で一意であることを保証します。
• ageカラムはINTEGER型です。

4. データの挿入

データをテーブルに挿入するには、INSERT INTOステートメントを使用します。

sqlite
INSERT INTO [テーブル名] ([カラム名], [カラム名], ...) VALUES ([値], [値], ...);

• テーブル名: データを挿入するテーブルの名前を指定します。
• カラム名: データを挿入するカラムの名前を指定します。省略した場合、テーブルのすべてのカラムにデータが挿入されます。
• 値: 各カラムに挿入する値を指定します。

例:

usersテーブルに新しいユーザーデータを挿入します。

sqlite
INSERT INTO users (name, email, age) VALUES ('John Doe', '[email protected]', 30);
INSERT INTO users (name, email, age) VALUES ('Jane Smith', '[email protected]', 25);

5. データのクエリ

データをテーブルから取得するには、SELECTステートメントを使用します。

sqlite
SELECT [カラム名], [カラム名], ... FROM [テーブル名] WHERE [条件];

• カラム名: 取得するカラムの名前を指定します。* を使用すると、テーブルのすべてのカラムを取得できます。
• テーブル名: データを取り出すテーブルの名前を指定します。
• 条件: データを選択するための条件を指定します。WHERE句を省略すると、テーブルのすべての行が選択されます。

例:

• usersテーブルからすべてのカラムを取得します。

  sqlite
SELECT * FROM users;

• usersテーブルからnameとemailカラムのみを取得します。

  sqlite
SELECT name, email FROM users;

• usersテーブルからageが25より大きいユーザーのデータを取得します。

  sqlite
SELECT * FROM users WHERE age > 25;

• usersテーブルからemailが'[email protected]'のユーザーのデータを取得します。

  sqlite
SELECT * FROM users WHERE email = '[email protected]';

6. データの更新

テーブル内のデータを更新するには、UPDATEステートメントを使用します。

sqlite
UPDATE [テーブル名] SET [カラム名] = [新しい値], [カラム名] = [新しい値], ... WHERE [条件];

• テーブル名: データを更新するテーブルの名前を指定します。
• カラム名: 更新するカラムの名前を指定します。
• 新しい値: 各カラムに設定する新しい値を指定します。
• 条件: 更新する行を選択するための条件を指定します。WHERE句を省略すると、テーブルのすべての行が更新されます。

例:

• usersテーブルで、idが1のユーザーのageを31に更新します。

  sqlite
UPDATE users SET age = 31 WHERE id = 1;

• usersテーブルで、emailが'[email protected]'のユーザーのnameを'Jane Williams'に更新します。

  sqlite
UPDATE users SET name = 'Jane Williams' WHERE email = '[email protected]';

7. データの削除

テーブルからデータを削除するには、DELETE FROMステートメントを使用します。

sqlite
DELETE FROM [テーブル名] WHERE [条件];

• テーブル名: データを削除するテーブルの名前を指定します。
• 条件: 削除する行を選択するための条件を指定します。WHERE句を省略すると、テーブルのすべての行が削除されます。

例:

• usersテーブルで、idが2のユーザーを削除します。

  sqlite
DELETE FROM users WHERE id = 2;

• usersテーブルで、ageが30より大きいユーザーを削除します。

  sqlite
DELETE FROM users WHERE age > 30;

8. より高度なクエリ

SELECTステートメントには、データをより効率的にクエリするためのさまざまなオプションがあります。

• ORDER BY: 結果を特定のカラムでソートします。

  sqlite
SELECT * FROM users ORDER BY age DESC; -- 年齢の高い順にソート

• LIMIT: 返される行数を制限します。

  sqlite
SELECT * FROM users LIMIT 10; -- 最初の10行のみを取得

• OFFSET: 指定された数の行をスキップしてから、結果を返します。

  sqlite
SELECT * FROM users LIMIT 10 OFFSET 20; -- 21行目から30行目までを取得

• JOIN: 複数のテーブルから関連するデータを結合します。

  例えば、usersテーブルとordersテーブルがあり、各ユーザーが複数の注文を持つ場合、JOINを使用して、ユーザーの名前と注文の情報を一緒に取得できます。

  “`sqlite
  CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  product TEXT,
  price REAL,
  FOREIGN KEY (user_id) REFERENCES users(id)
  );

  INSERT INTO orders (user_id, product, price) VALUES (1, ‘Laptop’, 1200.00);
  INSERT INTO orders (user_id, product, price) VALUES (1, ‘Mouse’, 25.00);
  INSERT INTO orders (user_id, product, price) VALUES (2, ‘Keyboard’, 75.00);

  SELECT users.name, orders.product, orders.price
  FROM users
  INNER JOIN orders ON users.id = orders.user_id;
  “`

• GROUP BY: 同じ値を持つ行をグループ化し、集計関数（COUNT, SUM, AVG, MIN, MAXなど）を適用します。

  sqlite
SELECT age, COUNT(*) FROM users GROUP BY age; -- 各年齢のユーザー数をカウント

9. インデックス

インデックスは、データベース内のデータの検索を高速化するために使用されます。インデックスを作成するには、CREATE INDEXステートメントを使用します。

sqlite
CREATE INDEX [インデックス名] ON [テーブル名] ([カラム名]);

例:

usersテーブルのemailカラムにインデックスを作成します。

sqlite
CREATE INDEX email_index ON users (email);

インデックスを作成すると、emailカラムに基づいてユーザーを検索するクエリが高速化されます。ただし、インデックスはストレージスペースを消費し、データの挿入、更新、削除のパフォーマンスに影響を与える可能性があるため、必要に応じてのみ作成する必要があります。

10. トランザクション

トランザクションは、複数のデータベース操作を1つの論理的な単位として扱うために使用されます。トランザクションを使用すると、すべての操作が成功するか、いずれかの操作が失敗した場合にすべての変更がロールバックされることを保証できます。

トランザクションを開始するには、BEGIN TRANSACTIONステートメントを使用します。トランザクションをコミットするには、COMMITステートメントを使用します。トランザクションをロールバックするには、ROLLBACKステートメントを使用します。

“`sqlite
BEGIN TRANSACTION;

— 複数のデータベース操作を実行

COMMIT; — すべての操作をコミット

— または

ROLLBACK; — すべての変更をロールバック
“`

例:

usersテーブルに新しいユーザーを追加し、ordersテーブルにそのユーザーの注文を追加するトランザクションを作成します。

“`sqlite
BEGIN TRANSACTION;

INSERT INTO users (name, email, age) VALUES (‘New User’, ‘[email protected]’, 28);
INSERT INTO orders (user_id, product, price) VALUES (last_insert_rowid(), ‘Headphones’, 100.00);

COMMIT;
“`

last_insert_rowid()関数は、最後に挿入された行のidを返します。これにより、usersテーブルに追加された新しいユーザーのidを、ordersテーブルのuser_idとして使用できます。

11. その他の便利なコマンド

• .mode [モード]: 出力モードを設定します。column, csv, html, insert, line, list, tabs, tclなどのモードがあります。

  “`sqlite
  sqlite> .mode column
  sqlite> .headers on
  sqlite> SELECT * FROM users;
  id name email age

  ---

  1 John Doe [email protected] 31
  “`

• .separator [区切り文字]: .mode listで使用される区切り文字を設定します。

  sqlite
sqlite> .mode list
sqlite> .separator "|"
sqlite> SELECT * FROM users;
1|John Doe|[email protected]|31

• .dump: データベース全体または特定のテーブルの内容をSQL形式でダンプします。これは、データベースのバックアップや復元に役立ちます。

  sqlite
sqlite> .dump users
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER
);
INSERT INTO users(id,name,email,age) VALUES(1,'John Doe','[email protected]',31);
COMMIT;

• .read [ファイル名]: 指定されたファイルに含まれるSQLコマンドを実行します。これは、複数のSQLコマンドをまとめて実行する場合に便利です。

  sqlite
sqlite> .read schema.sql

12. SQLite3 CLIの利点と注意点

利点:

• 軽量かつ高速: SQLite3は非常に軽量であり、メモリ消費量が少ないため、リソースが限られた環境でも快適に動作します。
• ポータブル: SQLite3データベースは単一のファイルに保存されるため、異なるオペレーティングシステム間で簡単に移動できます。
• 組み込み型: SQLite3は組み込み型のデータベースエンジンであり、別途サーバーを起動する必要がないため、設定が簡単です。
• コマンドラインインターフェース: コマンドラインインターフェースは、データベースの操作をスクリプト化したり、自動化したりするのに便利です。

注意点:

• 同時アクセス: SQLite3は、同時書き込みアクセスには適していません。複数のプロセスが同時にデータベースに書き込もうとすると、ロック競合が発生する可能性があります。
• 大規模データ: SQLite3は、大規模なデータセットの処理には、MySQLやPostgreSQLなどのより強力なデータベースエンジンほど適していません。
• セキュリティ: SQLite3データベースファイルは、ファイルシステムの権限によって保護されます。機密データを扱う場合は、適切なセキュリティ対策を講じる必要があります。

13. まとめ

この記事では、SQLite3のコマンドラインインターフェースを使用して、データベースの作成、テーブルの操作、データの追加、クエリ、削除などの基本的な操作をマスターする方法を詳細に解説しました。SQLite3 CLIは、データベースの操作を学ぶための優れたツールであり、SQLite3の機能を最大限に活用するのに役立ちます。この記事で紹介したコマンドとテクニックを理解することで、SQLite3データベースを効果的に管理し、さまざまなアプリケーションで活用できるようになります。さらに高度な機能や概念については、SQLite3の公式ドキュメントを参照してください。