SQLite 3の主要機能を紹介する.
【目次】
Windows でのSQLite 3のインストールは別の Web ページ で説明している.
SQLite 3 について: 別ページ »にまとめ
このページでは,データベースの生成を行うので, 生成するデータベースのデータベース名を決めておくこと. このページでは,次のように書く.
データベース名は,自由に決めてよいが,半角文字(つまり英字と英記号)を使い,スペースを含まないこと,
自由に決めてよいが,半角文字(つまり英字と英記号)を使い,スペースを含まないこと,
リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name, unit_price, qty)
SQL 文:
create table order_records ( id INTEGER PRIMARY KEY autoincrement not null, year INTEGER not null CHECK ( year > 2008 ), month INTEGER not null CHECK ( month >= 1 AND month <= 12 ), day INTEGER not null CHECK ( day >= 1 AND day <= 31 ), customer_name TEXT not null, product_name TEXT not null, unit_price REAL not null CHECK ( unit_price > 0 ), qty INTEGER not null DEFAULT 1 CHECK ( qty > 0 ), created_at DATETIME not null, updated_at DATETIME, CHECK ( ( unit_price * qty ) < 200000 ) );
begin transaction; insert into order_records values( 1, 2019, 10, 26, 'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime'), NULL ); insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26, 'miyamoto', 'Apple M', 2.5, 2, datetime('now', 'localtime') ); insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 27, 'kaneko', 'orange B', 1.2, 8, datetime('now', 'localtime') ); insert into order_records (year, month, day, customer_name, product_name, unit_price, created_at) values( 2019, 10, 28, 'miyamoto', 'Apple L', 3, datetime('now', 'localtime') ); commit;
「now」は,現在の日時を取得する SQLite の関数 「datetime」は,日時のデータを「YYYY-MM-DD HH:MM:SS」形式の文字列に変換する SQLite の関数
SELECT * FROM order_records; SELECT * FROM order_records WHERE day = 26; SELECT * FROM order_records WHERE customer_name = 'kaneko'; SELECT * FROM order_records WHERE unit_price > 2;
「
「DELETE FROM ... WHERE ...」は行の削除.ここには 1つの SQL 文を書き, 「begin transaction」と「commit」で囲む. ("DELETE FROM ... WHERE ..." means deletion of rows).
begin transaction;
UPDATE order_records SET qty=12, updated_at=datetime('now', 'localtime')
WHERE id = 1;
commit;
begin transaction;
DELETE FROM order_records
WHERE id = 2;
commit;
以下の手順で,新規の SQLite 3 のデータベースファイル「C:\SQLite\mydb」を作成する.
SQLite 3 のデータベース・ディレクトリ C:\SQLite に移る.
C: cd C:\SQLite
データベースを新規作成したいので, sqlite3.exe の起動で, 新しいデータベースファイル名を指定する(まだ存在しないファイル名を付ける).
データベースファイル名は自由に付けてよいが、アルファベットのみを使うのが良い..
例えば,データベースファイル名 mydb のデータベースファイルを扱いたい場合は次のような操作になる.
.\sqlite3.exe mydb
上記の操作で,mydb というデータベースファイル名を持つファイルが無いので,自動的にデータベースファイルが生成される.
※ データベースファイルが生成されるタイミングは,テーブルを定義するなど,データベースの更新を行ったときなので,sqlite3 の起動時点で,データベースファイルが出来ていなくても慌てないこと.
以下の手順で,新規の SQLite 3 のデータベースファイル「/tmp/mydb」を作成する.
SQLite 3 のデータベース・ディレクトリ /tmp に移る.
cd /tmp
データベースを新規作成したいので, sqlite3 の起動で, 新しいデータベースファイル名を指定する(まだ存在しないファイル名を付ける).
データベースファイル名は自由に付けてよいが、アルファベットのみを使うのが良い..
例えば,データベースファイル名 mydb のデータベースファイルを扱いたい場合は次のような操作になる.
sqlite3 /tmp/mydb
上記の操作で,mydb というデータベースファイル名を持つファイルが無いので,自動的にデータベースファイルが生成される.
※ データベースファイルが生成されるタイミングは,テーブルを定義するなど,データベースの更新を行ったときなので,sqlite3 の起動時点で,データベースファイルが出来ていなくても慌てないこと.
「.help」で,ヘルプが表示される.
現在使用中のデータベースについての,文字のエンコーディングの確認
「PRAGMA encoding;」で,エンコーディングが表示される.
データベースを消したいときは,対応するデータベースファイルを消すだけです.
「.exit」で終了.
以下の手順で,既存のデータベースファイル「C:\SQLite\mydb」を開く.
SQLite 3 のデータベース・ディレクトリ C:\SQLite に移る.
C: cd C:\SQLite
sqlite3.exe の起動時に,使用したいデータベースファイルのファイル名を指定する.例えば,mydbというファイル名のデータベースがすでに生成済みで,これを使いたい場合には,「.\sqlite3 mydb」のようになる.
.\sqlite3.exe mydb
SQL を用いて,order_records テーブルを定義し,一貫性制約を記述する.
リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name, unit_price, qty)
create table order_records ( id INTEGER PRIMARY KEY autoincrement not null, year INTEGER not null CHECK ( year > 2008 ), month INTEGER not null CHECK ( month >= 1 AND month <= 12 ), day INTEGER not null CHECK ( day >= 1 AND day <= 31 ), customer_name TEXT not null, product_name TEXT not null, unit_price REAL not null CHECK ( unit_price > 0 ), qty INTEGER not null DEFAULT 1 CHECK ( qty > 0 ), created_at DATETIME not null, updated_at DATETIME, CHECK ( ( unit_price * qty ) < 200000 ) );
このとき C:\SQLite に,データベースファイル mydb ができる.
※ SQLite では,データベースが始めて使うときに,自動的にデータベースファイルが生成される.データベースファイル名は,データベース名と同じになる.
※ データベースファイルが生成されるのは,テーブルを定義するなど,データベースの更新を行ったときなので,最初,sqlite3 を起動したとき,データベースファイルが無くてもあわてないこと.
次のような order_records テーブルを作る.
以下の手順で,SQL を用いてorder records テーブルへの行の挿入を行う
「insert into ...」は行の挿入.ここには 4つの SQL 文を書き, 「begin transaction」と「commit」で囲む.
※ つまり, 挿入の前に begin transaction; を実行し,一連の挿入が終わったら commit; を実行する.
begin transaction; insert into order_records values( 1, 2019, 10, 26, 'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime'), NULL ); insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26, 'miyamoto', 'Apple M', 2.5, 2, datetime('now', 'localtime') ); insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 27, 'kaneko', 'orange B', 1.2, 8, datetime('now', 'localtime') ); insert into order_records (year, month, day, customer_name, product_name, unit_price, created_at) values( 2019, 10, 28, 'miyamoto', 'Apple L', 3, datetime('now', 'localtime') ); commit;
datetime('now', 'localtime') は現在日時の取得.DATETIME型は、YYYY-MM-DD HH:MM:SS形式.
insert into には 2つの方法がある.
■ 属性の値を,テーブル定義の順に全て並べる
insert into order_records values( 1, 2019, 10, 26, 'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime'), NULL );
■ 属性の値の並び方を,属性名を使って明示的に指定する
このとき,属性値を省略すると,テーブル定義のときに指定されたデフォルト値が使われる
insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26, 'miyamoto', 'Apple M', 2.5, 2, datetime('now', 'localtime') );
ここでは,一貫性制約に違反するような更新を試みる.データベース管理システムソフトウェアが一貫性を維持するので, 一貫性制約に違反するような更新はできない.
begin transaction; insert into order_records values( 3, 2019, 10, 30, 'kaneko', 'banana', 10, 3, datetime('now', 'localtime'), NULL ); ROLLBACK;
※ すでに属性 id には 3 という値がある. 主キー制約「PRIMARY KEY」に違反.
begin transaction; insert into order_records values( 3, 2019, 10, 30, NULL, 'melon', 10, 3, datetime('now', 'localtime'), NULL ); ROLLBACK;
※ 非空制約「not null」. 属性 customer_name には NULL を入れることができない.
一貫性制約に違反する例
begin transaction; insert into order_records values( 6, 1009, 10, 30, 'kaneko', 'melon', 10, 3, datetime('now', 'localtime'), NULL ); ROLLBACK;
※ 制約「CHECK ( year > 2008 )」に違反
一貫性制約に違反する例
begin transaction; insert into order_records values( 7, 2019, 10, 31, 'kaneko', 'strawberry', 4.6, 100000, datetime('now', 'localtime'), NULL ); ROLLBACK;
※ 制約「CHECK ( ( unit_price * qty ) < 200000 ) );」に違反
ここでは,SQL を用いた問い合わせの実行例を示す. SQL 問い合わせの詳細については,別の Web ページで説明する.ここでは,テーブルの中身を確認して欲しい.
テーブルの全ての行の表示
SELECT * FROM order_records;
条件を満足する行のみの表示
SELECT * FROM order_records WHERE product_name = 'orange A';
SELECT * FROM order_records WHERE product_name LIKE 'orange%';
LIKE は文字列のパターンマッチ
SELECT * FROM order_records WHERE unit_price > 2;
SELECT * FROM order_records WHERE qty > 9 AND customer_name = 'kaneko';
SQL を用いたデータの更新 (update)の実行例を示す. 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は,データの更新である. この SQL 文を 「begin transaction」と「commit」で囲む.
「UPDATE ... SET ...」は更新.ここには 1つの SQL 文を書き, 「begin transaction」と「commit」で囲む.
begin transaction; UPDATE order_records SET qty=12, updated_at=datetime('now', 'localtime') WHERE id = 1; commit;
SQL を用いた行の削除 (delete row(s))の実行例を示す.
「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である. この SQL 文を 「begin transaction」と「commit」で囲む.
begin transaction; DELETE FROM order_records WHERE id = 2; commit;
.mode csv .output order_records.csv SELECT * FROM order_records;
.output stdout
主な出力モードは次の通りです
SQL の実行結果をファイルに出力するには,「.output」を使う.
SQL 文の前に「explain」を付けると,SQL は実行されずに,実行計画が表示される. これと別に「.explain」というコマンドがあります. 「.explain on」と「.explain off」により,表示形式が変わります(表示形式が変わるだけです).
.read <ファイル名>
データベース内のテーブル一覧を表示するには,sqlite_master, sqlite_temp_masterという名前が付いた特別なテーブルを使う.
データベーススキーマを見たいときは,次のような操作を行う.
SELECT * FROM sqlite_master; SELECT * FROM sqlite_temp_master;
※ sqlite_master, sqlite_temp_master に,DROP TABLE, UPDATE, INSERT, DELETE 操作を行うことは許されていません.
SQLite 3の「.indices」コマンドを使い,インデックス名を表示できる.
CREATE INDEX idx1 ON order_records(customer_name); .indices
.dump を使い,データベースのダンプ.特定のテーブルだけをダンプしたいときは,「.dump commodity」のようにする
単にダンプを実行すると,スクリーンにダンプ結果が出力される.
ダンプ結果をファイルに保存したいときは,先に「.output <ファイル名>」を実行する.
ファイルに保存されたダンプ結果をリストアしたいときは,「.read ファイル名」を実行する.