SQLite 3 の利用

1. データベース環境の構築と前準備

SQLite 3の詳細情報: SQLite 3技術解説 »

データベース設定の基本事項

効率的なデータベース運用を実現するため，以下の基本的な設定を行います．

• データベース名の設定: mydb

  データベース名は，プロジェクトの特性を反映した半角英数字のみを使用することを推奨します．スペースは使用できません．

• データベースの保存場所: C:\SQLite (Windows環境)，/tmp (Linux環境)

  保存場所は管理の容易な場所を選択し，半角英数字のみを使用します．スペースは使用できません．

2. 実践的なデータベース操作ガイド

• SQLite 3データベースの効率的な新規作成手順
• 既存データベースの最適な活用方法
• データベース設計とデータ整合性の実装

  リレーショナルスキーマの定義例: 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」関数は現在時刻を取得し，「datetime」関数は日時データを「YYYY-MM-DD HH:MM:SS」形式に変換します．

• データ整合性の検証方法
• 高度なデータ検索技術

  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;
  

• 安全なデータ更新の実装

  「UPDATE ... SET ...」構文によるデータ更新を実装します．トランザクション管理のため，「begin transaction」と「commit」で処理を囲みます．

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

• データの安全な削除手順

  「DELETE FROM ... WHERE ...」構文を使用したデータ削除を実装します．トランザクション管理のため，「begin transaction」と「commit」で処理を囲みます．

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

• データのバックアップと保護
• クエリ実行の最適化手法
• SQLスクリプトの効率的な実行方法
• データベース構造の解析
• データベースの保守と復元手順

3. 効率的なデータベース作成手順

Windows環境でのSQLite 3起動手順

Windows環境での具体的な操作手順について解説します．

以下の手順に従って，新規の SQLite 3データベース「C:\SQLite\mydb」を作成します．

1. Windowsのコマンドプロンプトで以下の操作を実行します．

   SQLite 3のデータベースディレクトリ C:\SQLite に移動します．

   cd /d C:\SQLite
   

   
2. sqlite3.exeの起動と初期設定

   新規データベースを作成するため， sqlite3.exe起動時に新しいデータベースファイル名を指定します．

   データベースファイル名は，半角英数字のみを使用することを強く推奨します．

   具体例として，データベースファイル名を mydb とする場合の操作手順を示します．

   .\sqlite3.exe mydb
   

   

   この操作により，mydbという名前のデータベースファイルが存在しない場合，自動的に新規作成されます．

   重要な注意事項: データベースファイルは，テーブル定義などのデータベース更新操作時に生成されます．起動直後にファイルが見当たらなくても問題ありません．

Ubuntu環境でのSQLite 3起動手順

Ubuntu環境での具体的な操作手順について解説します．

以下の手順に従って，新規の SQLite 3データベース「/tmp/mydb」を作成します．

1. Ubuntuの端末で以下の操作を実行します．

   SQLite 3のデータベースディレクトリ /tmp に移動します．

   cd /tmp
   

2. SQLite 3の起動と初期設定

   新規データベースを作成するため， sqlite3の起動時に新しいデータベースファイル名を指定します．

   データベースファイル名は，半角英数字のみを使用することを強く推奨します．

   具体例として，データベースファイル名を mydb とする場合の操作手順を示します．

   sqlite3 /tmp/mydb
   

   

この操作により，mydbという名前のデータベースファイルが存在しない場合，自動的に新規作成されます．

重要な注意事項: データベースファイルは，テーブル定義などのデータベース更新操作時に生成されます．起動直後にファイルが見当たらなくても問題ありません．

SQLite 3のヘルプ機能

「.help」コマンドを実行すると，利用可能な全コマンドとその詳細な使用方法が表示されます．

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

現在使用中のデータベースの文字エンコーディングを確認するには，以下の手順を実行します．

「PRAGMA encoding;」コマンドを実行すると，現在の文字エンコーディング設定が表示されます．

データベースの削除手順

データベースを完全に削除する場合は，対応するデータベースファイルを直接削除します．

SQLite 3の終了手順

「.exit」コマンドを実行することで，安全にデータベースを終了できます．

4. 既存データベースの活用方法

以下の手順に従って，既存のデータベースファイル「C:\SQLite\mydb」に安全にアクセスします．

1. Windowsのコマンドプロンプトで以下の操作を実行します．

   SQLite 3のデータベースディレクトリ C:\SQLite に移動します．

   cd /d C:\SQLite
   

   
2. sqlite3.exeの起動とデータベース接続

   sqlite3.exe起動時に，アクセスするデータベースファイル名を明示的に指定します．例えば，mydbという既存のデータベースに接続する場合は，以下のコマンドを実行します．

   .\sqlite3.exe mydb
   

   

5. データベース設計とデータ整合性の確保

SQLを使用して，order_records テーブルを定義し，データの整合性を厳密に確保します．

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

1. テーブル定義用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 が自動的に生成されます．

   

   重要な注意事項: SQLiteでは，初回のデータベース更新操作時に自動的にデータベースファイルが生成されます．ファイル名はデータベース名と同一になります．

   補足説明: データベースファイルの生成はテーブル定義などの更新操作時に実行されます．そのため，起動直後にファイルが存在しなくても問題ありません．

6. データの効率的な登録と管理

以下のような構造を持つ order_records テーブルを作成します．

以下の手順に従って，SQLを使用したデータ登録を実行します．

1. データ登録用SQLの実行

   「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」形式で保存します．

   

   データ登録には，以下の2つの主要な方式があります：

   1. 全カラム指定方式: テーブル定義の順序に従ってすべての値を指定

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

   2. カラム名指定方式: 登録するカラムを明示的に指定

   明示的に指定されていないカラムには，定義されたデフォルト値が自動的に適用されます．

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

7. データ整合性の検証と管理

ここでは，データ整合性制約に違反する更新操作を意図的に試行し，データベース管理システムによる堅牢な整合性保護機能を検証します．

主キー制約の検証 (PRIMARY KEY)

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

重要な注意事項: id列に値 3 が既に存在するため， 主キー制約「PRIMARY KEY」に違反し，エラーが発生します．

非NULL制約の検証 (not null)

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

重要な注意事項: customer_name列には「not null」制約が設定されているため， NULL値の挿入はできません．

データ値制約の検証

制約違反の例1: 年度範囲の制約

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

重要な注意事項: 「CHECK ( year > 2008 )」制約に違反するため，エラーが発生します．

制約違反の例2: 取引金額の制約

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 )」制約に違反するため，エラーが発生します．

8. 高度なデータ検索と分析

ここでは，SQLを使用した効率的なデータ検索手法について具体例を交えて解説します． より詳細な検索手法については，SQLクエリ最適化ガイドを参照してください．

全データの取得と確認

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

9. 安全なデータ更新手法

SQLを使用した安全かつ効率的なデータ更新 (update)の実装方法について解説します． 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」構文を使用し，トランザクション処理でデータ更新を実行します．

1. 更新用SQLの実行

   「UPDATE ... SET ...」構文でデータを更新します．トランザクション管理のため， 「begin transaction」と「commit」で処理全体を囲みます．

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

   
2. 更新結果の確認と検証
   

10. データの安全な削除方法

SQLを使用した安全かつ確実なデータ削除 (delete row(s))の実装方法について解説します．

「DELETE FROM <table-name> WHERE <expression>;」構文を使用し，トランザクション処理でデータ削除を実行します．

1. 削除用SQLの実行

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

   
2. 削除結果の確認と検証
   

11. データのバックアップと保護

1. CSVフォーマットの設定とファイル出力の指定を行い，「SELECT * FROM ...」を実行します．

   .mode csv
   .output order_records.csv
   SELECT * FROM order_records;
   

   
2. データの安全性を確保するため，出力先を標準出力に戻す操作を実行します．

   .output stdout
   

   
3. エクスポートされたデータをMicrosoft Excelで開いて内容を確認します．
   

出力形式の設定と管理

SQLite 3では，以下の主要な出力形式をサポートしています：

• 「.mode csv」: データをカンマ区切り形式で出力
• 「.mode column」: データを左揃えの列形式で出力
• 「.mode list」: カスタム区切り文字を使用してデータを出力（区切り文字は「.separator」コマンドで設定可能）

実行結果のファイル出力設定

SQLの実行結果を外部ファイルに保存する場合は，「.output」コマンドを使用します：

• 「.output ＜ファイル名＞」: 指定したファイルへ出力を保存
• 「.output stdout」: 標準出力（画面表示）へ出力を切り替え

12. クエリ実行計画の最適化

SQLクエリの前に「explain」キーワードを付加することで，クエリの実行計画を詳細に確認できます． また，「.explain」コマンドを使用して表示形式を制御することが可能です． 「.explain on」と「.explain off」コマンドで，実行計画の表示形式を切り替えることができます．

13. SQLスクリプトの実行管理

.read ＜ファイル名＞

14. データベース構造の分析

テーブル構造の確認

データベース内のテーブル構造を詳細に確認するには，システムテーブルsqlite_masterと sqlite_temp_masterを活用します：

• sqlite_master: 永続テーブルの詳細なスキーマ情報を管理
• sqlite_temp_master: 一時テーブルの詳細なスキーマ情報を管理

データベース構造の確認は，以下のコマンドで実行します：

SELECT * FROM sqlite_master;
SELECT * FROM sqlite_temp_master;

重要な注意事項: これらのシステムテーブルは読み取り専用です．DROP TABLE, UPDATE, INSERT, DELETE操作は実行できません．

インデックス情報の管理

SQLite 3の「.indices」コマンドを使用して，インデックス情報を詳細に確認できます．

CREATE INDEX idx1 ON order_records(customer_name);
.indices

15. データベースの保守と復元

.dumpコマンドを使用して，データベースの完全なバックアップを作成します．特定のテーブルのみをバックアップする場合は，「.dump テーブル名」コマンドを使用します．

デフォルトでは，バックアップの内容は標準出力（画面）に表示されます．

バックアップをファイルに保存する場合は，実行前に「.output ファイル名」コマンドを実行します．

バックアップからデータを復元する場合は，「.read ファイル名」コマンドを実行します．