• HOME
• データベース
• SQLiteを使いこなす！基本操作マスター

SQLiteを使いこなす！基本操作マスター

Tweet

SQLiteはシンプルで軽量なスタンドアローンデータベースです。データをファイルに保存するため、専用のサーバーやプロセスが不要で、簡単に導入・使用することができます。

本記事では、SQLiteの導入方法や基本的な使い方について解説します。

Windowsでの利用方法

Windowsでの利用方法です。

SQLiteのインストール

SQLiteより最新のSQLiteをダウンロードします。

1. Download
2. Precompiled Binaries for Windows>sqlite-tools-win32-x86-3390200.zip (2022.07.26時点)をダウンロード

ダウンロードしたファイルを解凍して任意のフォルダに格納します。
下記はC:\sqlite3に格納した場合。

C:\sqlite3
    sqldiff.exe
    sqlite3.exe
    sqlite3_analyzer.exe

SQLiteツールは実行ファイルだけなので、これで利用可能となります。

データベース操作

データベースの操作には、コマンドラインツールsqlite3.exeを使用します。

データベースを作成する

次のコマンドでデータベースを作成します。 <データベース名>はデータベースのファイル名になります。(e.g. sample.db)

sqlite3.exe <データベース名>

この時点でファイルは作成されません。テーブルを追加すると、ファイルが作成されます。

データベースに接続する

データベースに接続する場合も作成時と同じです。<データベース名>に既存のファイルを指定します。

sqlite3.exe <データベース名>

データベースを抜ける

> .exit

SQLファイルを実行する

SQL文を書いたファイル(.sql)をデータベースに適用したい場合は、次のようにコマンドを実行します。

sqlite3.exe <データベース名> < <.sqlファイル>

Ubuntuでの利用方法

Ubuntuでの利用方法です。

SQLiteのインストール

sudo apt update
sudo apt install sqlite3

データベース操作

データベースを作成する

作成と同時にデータベースに接続します。

sqlite3 <データベース名>

データベースに接続する

作成時と同じコマンドです。

sqlite3 <データベース名>

データベースを抜ける

.exit

データベース接続後の操作はWindows、Ubuntu共通です。

テーブル操作

テーブルの作成

次のようなSQL文でテーブルを作成します。

create table LOGS (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
  , registered_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
  , update_at TEXT
  , details TEXT NOT NULL);

テーブルの削除

> drop table <テーブル名>;

テーブル一覧を表示

> .tables

create文を表示する

> .schema <テーブル名>

AUTOINCREMENTについて

AUTOINCREMENTを指定する場合はINTEGERかつPRIMARY KEYを指定する必要があります。また、この場合はINTの指定は不可で、INTEGERとしなければなりません。

データ型

指定できる型は次のとおりです。

BOOLEANなどの指定も可能です。BOOLEANの場合、実際にはINTEGERで格納されます。trueを設定すれば1、falseを設定すれば0が格納されます。
日時データもTEXTとして格納されます。

データの格納について

定義した型と異なる値を格納してもエラーとなりません。その場合、行毎に型が異なります。

create table SAMPLE (id INT);
insert into SAMPLE(id) values ('ABC');
insert into SAMPLE(id) values ('1');
insert into SAMPLE(id) values (2);

select id, typeof(id) from SAMPLE;
ABC|text
1|integer
2|integer

トリガー

更新日時などを自動で挿入したい場合は、トリガーを使用します。
下記はLOGSテーブル更新後に、更新日時カラム(update_at)を設定するトリガーです。

CREATE TRIGGER LOGS_TRIGGER AFTER UPDATE ON LOGS
BEGIN
    UPDATE LOGS SET update_at = DATETIME('now', 'localtime') WHERE id = OLD.id;
END;

次のようなクエリを実行すると、update_atに現在の日時が自動で設定されます。

UPDATE LOGS SET details='moge' WHERE id=2;

AFTERの代わりにBEFOREを使うとトリガーをイベント実行前に実行します。

トリガー一覧の表示

SELECT * FROM sqlite_master WHERE type = 'trigger';

トリガーの削除

drop trigger <トリガー名>;

データベースの最適化

SQLiteデータベースは、DELETEやUPDATEなどによって空いたスペースを自動的に解放しないことがあります。そのため、レコードを削除したのにファイルサイズが小さくならない状況が発生します。

VACUUMコマンドは、これらの空いたスペースを解放し、データベースファイルを最適化します。

VACUUM;

Tweet

前の記事

UbuntuへのMariaDBインストール手順と設定

次の記事

SQLiteで日時データを扱う