SQLite 3 コマンドライン・クライアントの主要機能

前準備

SQLite 3 のインストール

Windows での SQLite 3 のインストールは， 別ページで説明している．

あらかじめ決めておく事項

この Web ページでは，データベースの生成を行うので， 生成するデータベースのデータベース名を決めておくこと． この Web ページでは，次のように書く．

• データベース名: mydb

  データベース名は，自由に決めてよいが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと，

• データベースを置くディレクトリ: C:\SQLite (Windows の場合)，/tmp (Linux の場合)，

  自由に決めてよいが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと，

この Web ページで行うこと

• SQLite3 コマンドライン・インタフェースで SQLite データベースの新規作成 (Create a new database using SQLite3 command line interface)
• SQLite3 コマンドライン・インタフェースで既存のデータベースを開く (Open an existing SQLite database using SQLite3 command line interface)
• SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constraint specification using SQL)

  リレーショナル・スキーマ (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 ) );
  

• SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

  begin transaction;
  insert into order_records values( 1, 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now'), 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') );
  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') );
  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') );
  commit;
  

  「now」は，現在の日時を取得する SQLitep の関数 (A SQLitep function to get the current datetime). 「datetime」は，日時のデータを「YYYY-MM-DD HH:MM:SS」形式の文字列に変換する SQLitep の関数 (A SQLitep function to convert a datetime data into the string like "YYYY-MM-DD HH:MM:SS").

• 一貫性制約に違反する更新ができないことの確認 (Integrity constraint violation is not permitted when database update)
• SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

  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;
  

• SQL を用いた更新 (Update using SQL)

  「UPDATE ... SET ...」は更新．ここには 1つの SQL 文を書き， 「begin transaction」と「commit」で囲む． ("UPDATE ... SET ..." means database update).

  begin transaction;
  UPDATE order_records SET qty=12, updated_at=datetime('now')
  WHERE id = 1;
  commit;
  

• SQL を用いた行の削除 (Delete row(s) using SQL)

  「DELETE FROM ... WHERE ...」は行の削除．ここには 1つの SQL 文を書き， 「begin transaction」と「commit」で囲む． ("DELETE FROM ... WHERE ..." means deletion of rows).

  begin transaction;
  DELETE FROM order_records
  WHERE id = 2;
  commit;
  

• テーブルの CSV ファイル形式でのエクスポート (Export table data)
• .explain による SQL 実行計画の表示 (Get SQL query plan using 'explain')
• ファイルに入った SQL の実行 (Execute SQL file)
• データベーススキーマの表示 (Get database schema)
• テーブルのテキストファイル形式でのダンプとリストア (dump and restore database)

SQLite 3 コマンドライン・インタフェース で SQLite データベースの新規作成 (Create a new database)

Ubuntu での sqlite3 の起動手順（例）

以下の手順で，新規の SQLite データベースファイル「/tmp/mydb」を作成する． (Create a new SQLite database file '/tmp/mydb' on Ubuntu OS)

1. Ubuntu の端末で，次の操作を行う．(Use a terminal, and do the following).

   SQLite データベース・ディレクトリ /tmp に移る．

   cd /tmp
   

2. sqlite3 の起動 (Start the sqlite3).

   データベースを新規作成したいので， sqlite3 の起動で， 新しいデータベースファイル名を指定する（まだ存在しないファイル名を付ける）．

   データベースファイル名は自由に付けてよいが、アルファベットのみを使うのが良い．．

   例えば，データベースファイル名 mydb のデータベースファイルを扱いたい場合は次のような操作になる．

   sqlite3 /tmp/mydb
   

上記の操作で，mydb というデータベースファイル名を持つファイルが無いので，自動的にデータベースファイルが生成される．

※ データベースファイルが生成されるタイミングは，テーブルを定義するなど，データベースの更新を行ったときなので，sqlite3 の起動時点で，データベースファイルが出来ていなくても慌てないこと．

Windows での sqlite3 の起動手順（例）

以下の手順で，新規の SQLite データベースファイル「C:\SQLite\mydb」を作成する． (Create a new SQLite database file 'C:\SQlite\mydb' on Windows)

1. Windows のコマンドプロンプトで，次の操作を行う．(Use the Windows command prompt, and do the following).

   SQLite データベース・ディレクトリ C:\SQLite に移る．

   C:
   cd C:\SQLite 
   
2. sqlite3.exe の起動 (Start the sqlite3.exe).

   データベースを新規作成したいので， sqlite3.exe の起動で， 新しいデータベースファイル名を指定する（まだ存在しないファイル名を付ける）．

   データベースファイル名は自由に付けてよいが、アルファベットのみを使うのが良い．．

   例えば，データベースファイル名 mydb のデータベースファイルを扱いたい場合は次のような操作になる．

   .\sqlite3.exe mydb
   

上記の操作で，mydb というデータベースファイル名を持つファイルが無いので，自動的にデータベースファイルが生成される．

※ データベースファイルが生成されるタイミングは，テーブルを定義するなど，データベースの更新を行ったときなので，sqlite3 の起動時点で，データベースファイルが出来ていなくても慌てないこと．

ヘルプの表示

「.help」で，ヘルプが表示される．

文字のエンコーディングの確認

現在使用中のデータベースについての，文字のエンコーディングの確認 (encoding)

「PRAGMA encoding;」で，エンコーディングが表示される．

データベースの消去

データベースを消したいときは，対応するデータベースファイルを消すだけです．

SQLite 3 コマンドライン・インタフェースの終了

「.exit」で終了．

SQLite 3 コマンドライン・インタフェースで既存のデータベースを開く (Open an existing database)

以下の手順で，既存のデータベースファイル「C:\SQLite\mydb」を開く． (Open an existing database file 'C:\SQlite\mydb')

1. Windows のコマンドプロンプトで，次の操作を行う．(Use the Windows command prompt, and do the following).

   SQLite データベース・ディレクトリ C:\SQLite に移る．

   C:
   cd C:\SQLite 
   
2. sqlite3.exe の起動 (Start the sqlite3.exe).

   sqlite3.exe の起動時に，使用したいデータベースファイルのファイル名を指定する．例えば，mydbというファイル名のデータベースがすでに生成済みで，これを使いたい場合には，「.\sqlite3 mydb」のようになる．

   .\sqlite3.exe mydb
   

SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constrant specification using SQL)

SQL を用いて，order_records テーブルを定義し，一貫性制約を記述する． (Define 'order_records' table and specify integrity constrants of the table using SQL)

リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name,  unit_price, qty)

1. 次の SQL を入力 (Write the following 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 ) );
   

   このとき C:\SQLite に，データベースファイル mydb ができる．(At the time, database file is generated)

   ※ SQLite では，データベースが始めて使うときに，自動的にデータベースファイルが生成される．データベースファイル名は，データベース名と同じになる．

   ※ データベースファイルが生成されるのは，テーブルを定義するなど，データベースの更新を行ったときなので，最初，sqlite3 を起動したとき，データベースファイルが無くてもあわてないこと．

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような order_records テーブルを作る． (Construct table 'order_records')

以下の手順で，SQL を用いてorder records テーブルへの行の挿入を行う (Insert rows into table 'order_records' using SQL)

1. SQL の実行

   「insert into ...」は行の挿入．ここには 4つの SQL 文を書き， 「begin transaction」と「commit」で囲む． ("insert into ..." means inserting a row into a table. Four SQL statements are wrote).

   ※ つまり， 挿入の前に begin transaction; を実行し，一連の挿入が終わったら commit; を実行する．(Issue "begin transaction" before database update and "commit" after database update).

   begin transaction;
   insert into order_records values( 1, 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now'), 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') );
   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') );
   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') );
   commit;
   

   datetime('now') は現在日時の取得．DATETIME型は、YYYY-MM-DD HH:MM:SS形式．

   insert into には 2つの方法がある．(Two styles of "insert into")

   ■ 属性の値を，テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition)

   insert into order_records values( 1, 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now'), NULL );
   

   ■ 属性の値の並び方を，属性名を使って明示的に指定する (Specify the order of attribute values using attribute name list)

   このとき，属性値を省略すると，テーブル定義のときに指定されたデフォルト値が使われる (defaults values are used)

   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') );
   

一貫性制約に違反する更新ができないことの確認 (Integrity constraint violation is not permitted when database update)

ここでは，一貫性制約に違反するような更新を試みる．データベース管理システムソフトウエアが一貫性を維持するので， 一貫性制約に違反するような更新はできない．

主キー制約 (PRIMARY KEY)

begin transaction;
insert into order_records values( 3, 2019, 10, 30, 'kaneko', 'banana', 10, 3, datetime('now'), NULL );
ROLLBACK;

※ すでに属性 id には 3 という値がある． 主キー制約「PRIMARY KEY」に違反．

非空制約 (not null)

begin transaction;
insert into order_records values( 3, 2019, 10, 30,  NULL, 'melon', 10, 3, datetime('now'), NULL );
ROLLBACK;

※ 非空制約「not null」． 属性 customer_name には NULL を入れることができない．

その他の一貫性制約

一貫性制約に違反する例

begin transaction;
insert into order_records values( 6, 1009, 10, 30,  'kaneko', 'melon', 10, 3, datetime('now'), NULL );
ROLLBACK;

※ 制約「CHECK ( year > 2008 )」に違反

一貫性制約に違反する例

begin transaction;
insert into order_records values( 7, 2019, 10, 31,  'kaneko', 'strawberry', 4.6, 100000, datetime('now'), NULL );
ROLLBACK;

※ 制約「CHECK ( ( unit_price * qty ) < 200000 ) );」に違反

SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

ここでは，SQL を用いた問い合わせの実行例を示す． SQL 問い合わせの詳細については，別の Web ページで説明する．ここでは，テーブルの中身を確認して欲しい．

テーブルの全ての行の表示 (List all rows of a table)

SELECT * FROM order_records;

条件を満足する行のみの表示 (List the rows which satisfy a given condition)

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 using SQL)

SQL を用いたデータの更新 (update)の実行例を示す． 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は，データの更新である． この SQL 文を 「begin transaction」と「commit」で囲む． ("UPDATE ... SET ..." means database update).

1. SQL の実行 (Execute SQL)

   「UPDATE ... SET ...」は更新．ここには 1つの SQL 文を書き， 「begin transaction」と「commit」で囲む． ("UPDATE ... SET ..." means database update.

   begin transaction;
   UPDATE order_records SET qty=12, updated_at=datetime('now')
   WHERE id = 1;
   commit;
   
2. order_records テーブルの中身を確認 (Inspect the table 'order_table')

SQL を用いた行の削除 (Delete row(s) using SQL)

SQL を用いた行の削除 (delete row(s))の実行例を示す．

「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である． この SQL 文を 「begin transaction」と「commit」で囲む． ("DELETE FROM ... WHERE ..." means deletion of rows).

1. SQL の実行 (Execute SQL)

   begin transaction;
   DELETE FROM order_records
   WHERE id = 2;
   commit;
   
2. order_records テーブルの中身を確認 (Inspect the table 'order_table')

テーブルの CSV ファイル形式でのエクスポート (Export table data)

1. 出力モードを CSV に設定し，出力先をファイルに指定した後，「SELECT * FROM ...」を実行

   .mode csv
   .output order_records.csv
   SELECT * FROM order_records;
   
2. 出力先をファイルにしたままでは混乱するので，画面（標準出力）に出力するように戻しておく．

   .output stdout
   
3. 念のため Microsoft Excel で開いて確認してみる

.mode による出力モードの切り替え

主な出力モードは次の通りです

• 「.mode csv」: コンマ区切りデータ (Comma-seperated values)
• 「.mode column」: 左詰め形式
• 「.mode list」: 所定のセパレータで区切る表示（セパレータは，「.seperator」で変更できる）．

.output による実行結果のファイル出力

SQL の実行結果をファイルに出力するには，「.output」を使う．

• 「.output ＜ファイル名＞」: ファイルに出力
• 「.output stdout」: 画面（標準出力）に出力

.explain による SQL 実行計画の表示 (Get SQL query plan using 'explain')

SQL 文の前に「explain」を付けると，SQL は実行されずに，実行計画が表示される． これと別に「.explain」というコマンドがあります． 「.explain on」と「.explain off」により，表示形式が変わります（表示形式が変わるだけです）．

ファイルに入った SQL の実行

.read ＜ファイル名＞

データベーススキーマの表示 (Get database schema)

テーブル一覧の表示 (list of tables)

データベース内のテーブル一覧を表示するには，sqlite_master, sqlite_temp_masterという名前が付いた特別なテーブルを使う．

• sqlite_master: TEMPORARY テーブル以外のデータベーススキーマが格納されている．
• sqlite_temp_master: TEMPORARY テーブルのデータベーススキーマが格納されている．

データベーススキーマを見たいときは，次のような操作を行う．

SELECT * FROM sqlite_master;
SELECT * FROM sqlite_temp_master;

※ sqlite_master, sqlite_temp_master に，DROP TABLE, UPDATE, INSERT, DELETE 操作を行うことは許されていません．

インデックス名の一覧表示 (list of index name)

SQLite コマンドライン・インタフェースの「.indices」コマンドを使い，インデックス名を表示できる．

CREATE INDEX idx1 ON order_records(customer_name);
.indices

テーブルのテキストファイル形式でのダンプとリストア (dump and restore database)

.dump を使い，データベースのダンプ．特定のテーブルだけをダンプしたいときは，「.dump commodity」のようにする

単にダンプを実行すると，スクリーンにダンプ結果が出力される．

ダンプ結果をファイルに保存したいときは，先に「.output ＜ファイル名＞」を実行する．

ファイルに保存されたダンプ結果をリストアしたいときは，「.read ファイル名」を実行する．