SQLite ビュー、インデックス、トリガーの例

⚡ スマートサマリー

SQLite ビュー、インデックス、トリガーは、データベースのクエリとメンテナンスを容易にするための管理ツールです。ビューは複雑なクエリを再利用し、インデックスは検索を高速化し、トリガーはデータが変更されるたびに定義済みのアクションを自動的に実行します。

👁️ 閲覧数： ビューとは、SELECT文から構築される論理的なテーブルであり、複雑なクエリを書き直すことなく再利用することを可能にします。
⏳ 一時的な表示: 一時ビューは現在の接続時のみ存在し、その接続が閉じられると自動的に削除されます。
⚡ 索引： 索引は本の索引のように機能し、 SQLite テーブルのすべての行をスキャンするのではなく、一致する行をすばやく見つける。
🎯 インデックスの種類: SQLite 式インデックス、部分インデックス、一意インデックスをサポートし、特定のクエリパターンに合わせてパフォーマンスを微調整できます。
🔔 トリガー： トリガーは、テーブルに対するINSERT、UPDATE、またはDELETEステートメントの前後に、定義済みの操作を自動的に実行します。
🤖 AI 支援: AIテキストtoSQLツールとGitHub Copilotが生成する SQLite 平易な英語のプロンプトから、ビュー、インデックス、トリガーを作成します。

プロフィール

の毎日の使用で SQLite、データベースに対していくつかの管理ツールが必要になります。また、これらを使用して、インデックスを作成してデータベースのクエリをより効率的にしたり、ビューを作成して再利用しやすくしたりすることもできます。

SQLite 表示

ビューはテーブルとよく似ています。ただし、ビューは論理テーブルです。テーブルのように物理的に保存されるわけではありません。ビューは select ステートメントで構成されます。

複雑なクエリのビューを定義して、クエリを再度書き直すのではなく、ビューを直接呼び出すことで、いつでもこれらのクエリを再利用できます。

CREATE VIEW ステートメント

データベース上にビューを作成するには、CREATE VIEW ステートメントの後にビュー名を指定し、その後に必要なクエリを入力します。

例：次の例では、サンプルデータベース「TutorialsSampleDB.db」に「AllStudentsView」という名前のビューを以下のように作成します。

ステップ1） マイコンピュータを開き、「C:\sqlite」ディレクトリに移動して、「sqlite3.exe」を開きます。

ステップ2） 以下のコマンドを使用して、「TutorialsSampleDB.db」データベースを開きます。

ステップ3） 以下はビューを作成するためのsqlite3コマンドの基本構文です。

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

コマンドからは次のような出力が表示されないはずです。

ステップ4） ビューが作成されたことを確認するには、次のコマンドを実行して、データベース内のビューのリストを選択します。

SELECT name FROM sqlite_master WHERE type = 'view';

「AllStudentsView」というビューが返されるはずです。

ステップ5） これでビューが作成されました。次のように通常のテーブルとして使用できます。

SELECT * FROM AllStudentsView;

このコマンドは、次のスクリーンショットに示すように、ビュー「AllStudents」をクエリし、そこからすべての行を選択します。

一時的なビュー

一時ビューは、作成に使用された現在のデータベース接続に対して一時的なものです。その後、データベース接続を閉じると、すべての一時ビューが自動的に削除されます。一時ビューは、次のコマンドのいずれかを使用して作成されます。

一時ビューを作成する、または
一時ビューを作成します。

一時的なビューは、一時的に何らかの操作を実行する必要があり、永続的なビューである必要がない場合に便利です。そのため、一時的なビューを作成し、そのビューを使用して処理を実行します。 Later データベースとの接続を閉じると、データベースは自動的に削除されます。

例：

次の例では、データベース接続を開いて、一時ビューを作成します。

その後、その接続を閉じ、一時ビューがまだ存在するかどうかを確認します。

ステップ1） 前述のとおり、「C:\sqlite」ディレクトリからsqlite3.exeを開いてください。

ステップ2） 以下のコマンドを実行して、「TutorialsSampleDB.db」データベースへの接続を開きます。

.open TutorialsSampleDB.db

ステップ3） 一時ビュー「AllStudentsTempView」を作成する以下のコマンドを記述してください。

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

ステップ4） 以下のコマンドを実行して、一時ビュー「AllStudentsTempView」が作成されていることを確認してください。

SELECT name FROM sqlite_temp_master WHERE type = 'view';

ステップ5） sqlite3.exe を閉じて再度開きます。

ステップ6） 以下のコマンドを使用して、データベース「TutorialsSampleDB.db」への接続を開きます。

.open TutorialsSampleDB.db

ステップ7） 次のコマンドを実行して、データベースに作成された一時ビューのリストを取得します。

SELECT name FROM sqlite_temp_master WHERE type = 'view';

前の手順でデータベース接続を閉じたときに、作成した一時ビューが削除されるため、出力は表示されません。それ以外の場合は、データベースとの接続を開いたままにしておくと、データを含む一時ビューを表示できます。

注意：

ビューに対してステートメント INSERT、DELETE、または UPDATE を使用することはできません。CREATE View の例のステップ 5 に示すように、「ビューから選択」コマンドのみを使用できます。
VIEW を削除するには、「DROP VIEW」ステートメントを使用します。

DROP VIEW AllStudentsView;

ビューが削除されたことを確認するには、次のコマンドを実行して、データベース内のビューのリストを取得します。

SELECT name FROM sqlite_master WHERE type = 'view';

次のように、ビューが削除されたため、ビューは返されません。

ビューで複雑なクエリを再利用することに加えて、 SQLite また、インデックスを使用することで、一致する行をより高速に取得できます。

SQLite 目次

本を持っていて、その本のキーワードを検索したいとします。書籍の索引からそのキーワードを検索します。次に、そのキーワードのページ番号に移動して、そのキーワードに関する詳細情報を確認します。

しかし、その本に索引もページ番号もない場合は、検索するキーワードが見つかるまで本全体を最初から最後までスキャンすることになります。これは、特に索引がある場合には非常に困難で、キーワードを検索するプロセスが非常に遅くなります。

のインデックス SQLite (そして同じ概念が他のものにも当てはまりますデータベース管理システム同様に)、本の後ろにある索引と同じように機能します。

内のいくつかの行を検索すると、 SQLite 検索条件を含むテーブル、 SQLite 検索条件に一致する探している行が見つかるまで、テーブルのすべての行を検索します。そして、テーブルが大きい場合、そのプロセスは非常に遅くなります。

インデックスを使用すると、データの検索クエリが高速化され、テーブルからのデータ取得の実行に役立ちます。インデックスはテーブルの列に定義されます。

インデックスによるパフォーマンスの向上:

インデックスはテーブル上のデータ検索のパフォーマンスを向上させることができます。列にインデックスを作成すると、 SQLite 各フィールド値がその値が属する行全体へのポインタを持つインデックスのデータ構造を作成します。

次に、インデックスの一部である列に検索条件を指定してクエリを実行すると、 SQLite 最初にインデックス上の値を検索します。 SQLite テーブル全体をスキャンすることはありません。次に、テーブル行の値が指す場所を読み取ります。 SQLite その場所で行を見つけて取得します。

ただし、検索する列がインデックスの一部でない場合は、 SQLite 列値のスキャンを実行して、探しているデータを見つけます。インデックスがない場合、通常はプロセスが遅くなります。

索引のない本で、特定の単語を検索する必要があると想像してください。最初のページから最後のページまで本全体に目を通し、その単語を探します。ただし、その本の索引がある場合は、最初にその本の単語を検索します。該当するページ番号を取得して、そのページに移動します。これは、本全体を表紙から表紙までスキャンするよりもはるかに高速です。

SQLite インデックス作成

列にインデックスを作成するには、CREATE INDEX コマンドを使用する必要があります。そして、次のように定義する必要があります。

CREATE INDEX コマンドの後にインデックスの名前を指定する必要があります。
インデックス名の後にキーワード「ON」を入力し、その後にインデックスを作成するテーブル名を入力する必要があります。
次に、インデックスに使用される列名のリスト。
任意の列名の後に「ASC」または「DESC」のいずれかのキーワードを使用して、インデックスデータを並べ替えるために使用される並べ替え順序を指定できます。

例：

次の例では、「Students」データベースのstudentsテーブルに「StudentNameIndex」というインデックスを作成します。

ステップ1） 前述のとおり、「C:\sqlite」フォルダに移動してください。

ステップ2） sqlite3.exe を開きます。

ステップ3） 以下のコマンドを使用して、「TutorialsSampleDB.db」データベースを開きます。

.open TutorialsSampleDB.db

ステップ4） 以下のコマンドを使用して、「StudentNameIndex」という新しいインデックスを作成します。

CREATE INDEX StudentNameIndex ON Students(StudentName);

これに対する出力は表示されません。

ステップ5） インデックスが作成されたことを確認するには、次のクエリを実行します。これにより、Students テーブルに作成されたインデックスのリストが表示されます。

PRAGMA index_list(Students);

作成したばかりのインデックスが返されるのがわかります。

注意：

インデックスは列だけでなく式にも基づいて作成できます。このようなもの：

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

「OrderTotalIndex」は、OrderId 列に基づき、また、Quantity 列の値と Price 列の値の乗算にも基づきます。したがって、「OrderId」と「Quantity*Price」のクエリはインデックスを使用するため効率的になります。

CREATE INDEX ステートメントで WHERE 句を指定した場合、インデックスは部分インデックスになります。この場合、インデックスには WHERE 句の条件に一致する行のエントリのみが含まれます。たとえば、次のインデックスでは次のようになります。
```
CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;
```
( 上の例では、WHERE 句が指定されているため、インデックスは部分インデックスになります。この場合、インデックスは数量値が 10000 より大きい注文にのみ適用されます。このインデックスは部分インデックスと呼ばれることに注意してください。インデックスは、WHERE 句で使用される式ではなく、WHERE 句によって作成されます。ただし、式は通常のインデックスで使用できます。)
CREATE INDEX の代わりに CREATE UNIQUE INDEX ステートメントを使用すると、列の重複エントリを防ぐことができ、インデックス付き列のすべての値が一意になります。
インデックスを削除するには、DROP INDEX コマンドの後に削除するインデックス名を指定します。

インデックスは読み取りを高速化しますが、トリガーは SQLite データが変更されるたびに自動的に対応します。

SQLite トリガー

はじめに SQLite トリガー

トリガーは、データベーステーブルで特定のアクションが発生したときに実行される、事前定義された自動操作です。トリガーは、テーブルで次のいずれかのアクションが発生するたびに実行されるように定義できます。

テーブルに INSERT します。
テーブルから行を削除します。
テーブル列の XNUMX つを更新します。

SQLite FOR EACH ROW トリガーをサポートしているため、トリガー内の事前定義された操作は、テーブルで発生したアクション (挿入、削除、更新など) に関係するすべての行に対して実行されます。

SQLite トリガーの作成

新しい TRIGGER を作成するには、次のように CREATE TRIGGER ステートメントを使用できます。

CREATE TRIGGER の後に、トリガー名を指定する必要があります。
トリガー名の後に、トリガー名をいつ実行するかを指定する必要があります。次の XNUMX つのオプションがあります。
- BEFORE – トリガーは、指定された INSERT、UPDATE、または delete ステートメントの前に実行されます。
- After – トリガーは、指定された INSERT、UPDATE、または delete ステートメントの後に実行されます。
- INSTEAD OF – トリガーを起動した発生したアクションを TRIGGER で指定されたステートメントに置き換えます。 INSTEAD OF トリガーはテーブルには適用できません。ビューにのみ適用できます。
次に、アクションのタイプを指定する必要があります。アクションが発生するとトリガーが起動されます。 DELETE、INSERT、または UPDATE のいずれか。
オプションの列名を選択して、その列でアクションが発生しない限りトリガーが起動しないようにすることができます。
次に、トリガーを作成するテーブル名を指定する必要があります。
トリガーの本体内で、トリガーが起動されたときに行ごとに実行するステートメントを指定する必要があります。

トリガーは、create trigger コマンドで指定されたステートメントのタイプに応じてのみアクティブ化 (起動) されます。例えば：

BEFORE INSERT トリガーは、挿入ステートメントの前にアクティブ化 (起動) されます。
AFTER UPDATE トリガーは、更新ステートメントなどの後にアクティブ化 (起動) されます。

トリガー内では、「new」キーワードを使用して新しく挿入された値を参照できます。また、old キーワードを使用して削除または更新された値を参照することもできます。次のようになります。

INSERT トリガー内 – 新しいキーワードを使用できます。
UPDATE トリガー内 – 新しいキーワードと古いキーワードを使用できます。
DELETE トリガー内 – 古いキーワードを使用できます。

例:

以下では、「学生」テーブルに新しい学生を挿入する前に実行されるトリガーを作成します。

新しく挿入された学生は、「StudentsLog」テーブルに、挿入ステートメントが実行された現在の日時の自動タイムスタンプとともに記録されます。以下にその例を示します。

ステップ1） 「C:\sqlite」ディレクトリに移動し、sqlite3.exeを実行してください。

ステップ2） 以下のコマンドを実行して、「TutorialsSampleDB.db」というデータベースを開きます。

.open TutorialsSampleDB.db

ステップ3） 以下のコマンドを実行して、「InsertIntoStudentTrigger」というトリガーを作成します。

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

関数「datetime()」は、挿入ステートメントが実行された時点の現在の日時タイムスタンプを返します。これにより、挿入トランザクションをログに記録し、各トランザクションに自動的にタイムスタンプを追加することができます。

コマンドは正常に実行されるはずですが、出力は得られません。

トリガー「InsertIntoStudentTrigger」は、学生テーブルに新しい学生を挿入するたびに実行されます。「new」キーワードは、挿入される値を指定します。たとえば、「new.StudentId」は挿入される学生IDになります。

ここで、新しい生徒を挿入したときにトリガーがどのように動作するかをテストします。

ステップ4） 学生テーブルに新しい学生を挿入する次のコマンドを記述します。

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

ステップ5） 「StudentsLog」テーブルからすべての行を選択する以下のコマンドを記述してください。

SELECT * FROM StudentsLog;

挿入したばかりの新しい学生に対して新しい行が返されるのがわかります。

この行は、ID 11 の新しい学生を挿入する前にトリガーによって挿入されました。

この例では、作成したトリガー「InsertIntoStudentTrigger」を使用して、テーブル「StudentsLog」への挿入トランザクションを自動的にログに記録しました。同様の方法で、更新や削除ステートメントもログに記録できます。

トリガーによる意図しない更新の防止:

テーブルで BEFORE UPDATE トリガーを使用すると、式に基づいて列の更新ステートメントを防止できます。

例:

次の例では、Students テーブルの「studentname」列が更新ステートメントによって更新されないようにします。

ステップ1） 「C:\sqlite」ディレクトリに移動し、sqlite3.exeを実行してください。

ステップ2） 以下のコマンドを実行して、「TutorialsSampleDB.db」というデータベースを開きます。

.open TutorialsSampleDB.db

ステップ3） 以下のコマンドを実行して、「Students」テーブルに新しいトリガー「preventUpdateStudentName」を作成します。

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

「RAISE」コマンドは、「学生名を更新できません」というエラーメッセージとともにエラーを発生させ、更新ステートメントの実行を阻止します。

ここで、トリガーが適切に機能し、studentname 列の更新が妨げられることを確認します。

ステップ4） 以下の更新コマンドを実行すると、学生名「Jack」が「Jack1」に更新されます。

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

トリガーで指定したエラーメッセージ「学生名を更新できません」が以下のように表示されるはずです。

ステップ5） 次のコマンドを実行すると、学生テーブルから学生の名前のリストが選択されます。

SELECT StudentName FROM Students;

生徒の名前「Jack」が同じままで、変わっていないことがわかります。

よくあるご質問

テーブルは物理的にディスク上にデータを格納するのに対し、ビューは保存されたSELECT文によって定義される仮想テーブルです。ビュー自体はデータを保持せず、読み込むたびにそのクエリを実行し、基となるテーブルから行を表示します。

SQLite ビューは読み取り専用であるため、INSERT、UPDATE、DELETE 操作を直接実行することはできません。ビューを書き込み可能にするには、操作を基となるベーステーブルの変更に変換する INSTEAD OF トリガーをアタッチします。

WHERE句、JOIN句、またはORDER BY句で頻繁に使用される列、特に重複値が少ない高カーディナリティ列にインデックスを作成してください。インデックスはSELECTクエリの高速化に役立ちますが、検索頻度の低いテーブルや非常に小さなテーブルにインデックスを追加しても効果はほとんどありません。

はい。行が変更されるたびにインデックスを更新する必要があるため、インデックスを追加するごとに書き込みオーバーヘッドとストレージ容量が増加します。頻繁に検索する列にはインデックスを作成するようにしてください。ただし、INSERT、UPDATE、DELETE トラフィックが多いテーブルには過剰なインデックス作成は避けてください。

いいえ。 SQLite CREATE MATERIALIZED VIEW コマンドは存在せず、通常のビューは結果をキャッシュしません。これを模倣するには、実際のテーブルを作成し、ソーステーブルに対して AFTER INSERT、UPDATE、DELETE トリガーを使用して同期を維持します。

sqlite_master は、データベース内のすべてのテーブル、ビュー、インデックス、トリガーを一覧表示する組み込みのスキーマカタログです。たとえば、SELECT name FROM sqlite_master WHERE type = 'view'; のようにクエリを実行して、どのオブジェクトが存在するかを確認できます。

はい。AIテキストtoSQLアシスタントは、平易な英語のリクエストを SQLite CREATE VIEW、CREATE INDEX、およびCREATE TRIGGERステートメント。実際のテーブル名と列名を指定することで精度が向上します。生成されたすべてのステートメントは、本番データで実行する前に確認およびテストする必要があります。

GitHubコパイロット提案する SQLite ビュー、インデックス、トリガーをエディタにインラインで追加する VS Code. スキーマとコメントを近くから読み込むため、補完候補は実際のテーブル名と列名を再利用しますが、それでも各ステートメントを実行する前に確認する必要があります。