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

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

SQLite 表示

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

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

CREATE VIEW ステートメント

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

例：次の例では、「すべての学生を見るサンプルデータベース内の「」チュートリアルSampleDB.db” は以下のようになります。

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

ステップ2） データベースを開きます」チュートリアルSampleDB.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';

ビューが表示されるはずです。すべての学生を見る” が返されます。

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

SELECT * FROM AllStudentsView;

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

一時的なビュー

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

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

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

例：

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

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

ステップ1） ディレクトリ「C:\sqlite」前に説明したとおりです。

ステップ2） データベースへの接続を開きます。チュートリアルSampleDB.db次のコマンドを実行して、「」を実行します。

.open TutorialsSampleDB.db

ステップ3） 次のコマンドを書いて一時ビューを作成します。全学生臨時ビュー"

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） 一時ビューが「全学生臨時ビュー” は、次のコマンドを実行することで作成されます。

SELECT name FROM sqlite_temp_master WHERE type = 'view';

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

ステップ6） データベースへの接続を開きます。チュートリアルSampleDB.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 インデックス作成

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

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

例：

次の例では、インデックスを作成します 「生徒名インデックス」」の生徒のテーブルの上生徒” データベースは次のようになります:

ステップ1） フォルダーに移動します。C:\sqlite」前に説明したとおりです。

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

ステップ3） データベースを開きます」チュートリアルSampleDB.db” を次のコマンドで実行します。

.open TutorialsSampleDB.db

ステップ4） 新しいインデックスを作成します。」学生名インデックス” を次のコマンドで実行します。

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 トリガー

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

テーブルに 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 トリガー内 – 古いキーワードを使用できます。

例

以下では、新しい生徒を「生徒" テーブル。

新しく挿入された学生をテーブルに記録します。」学生ログ” に、挿入ステートメントが発生した現在の日時の自動タイムスタンプが付けられます。次のようになります。

ステップ1） ディレクトリに移動します。C:\sqlite” と入力し、sqlite3.exe を実行します。

ステップ2） データベースを開きます」チュートリアルSampleDB.db次のコマンドを実行して、「」を実行します。

.open TutorialsSampleDB.db

ステップ3） トリガーを作成します」InsertIntoStudentTrigger” 次のコマンドを実行します。

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

関数 "日付時刻（）" 挿入ステートメントが実行されたときの現在の日付タイムスタンプが表示されます。これにより、各トランザクションに自動タイムスタンプが追加されて挿入トランザクションをログに記録できるようになります。

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

引き金 "InsertIntoStudentTrigger」は、学生テーブルに新しい学生を挿入するたびに起動されます。 ”NEW” キーワードは、挿入される値を指します。たとえば、「new.StudentId」が入る学生証となります。

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

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

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

ステップ5） 次のコマンドを書いて、「学生ログ" テーブル：

SELECT * FROM StudentsLog;

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

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

この例では、トリガー「 InsertIntoStudentTrigger ” テーブル内の挿入トランザクションを記録するために作成しました。”学生ログ" 自動的に。同様の方法で、更新または削除ステートメントをログに記録できます。

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

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

例

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

ステップ1） ディレクトリに移動します。C:\sqlite” と入力し、sqlite3.exe を実行します。

ステップ2） データベースを開きます」チュートリアルSampleDB.db次のコマンドを実行して、「」を実行します。

.open TutorialsSampleDB.db

ステップ3） 新しいトリガーを作成します。防止学生名更新「テーブルの上に」生徒” 次のコマンドを実行します

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

「レイズ” コマンドを実行すると、エラーメッセージが表示されてエラーが発生します。 生徒名を更新できません 」と更新すると、更新ステートメントが実行されなくなります。

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

ステップ4） 次の更新コマンドを実行すると、生徒名「ジャック" することが "Jack1"

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

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

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

SELECT StudentName FROM Students;

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

製品概要

ビュー、インデックス、トリガーは、システムを管理するための非常に強力なツールです。 SQLite データベーステーブルでデータ変更操作が発生したときにそれを追跡できます。また、インデックスを作成してデータベースのデータ取得操作を最適化することもできます。

SQLite 表示

関連記事

SQLite 目次

SQLite トリガー

製品概要

ニュースレターに登録する