SQLite ビューの作成 (SQLite View Creation)

はじめに

この実験 (lab) では、SQLite ビューの作成について探求します。複雑なビューの構築、ビューを通じたクエリ、更新可能なビューによる更新、そして不要になったビューの削除に焦点を当てます。

まず、ビューを SQL ステートメントの結果セットに基づく仮想テーブルとして理解します。これは、クエリを簡素化し、データアクセスを制御するのに役立ちます。この実験 (lab) では、employees テーブルのような単一のテーブルから単純なビューを作成することから始め、employees テーブルと departments テーブルを結合するなど、テーブル間の結合を含むより複雑なビューへと進みます。これらのビューを通常のテーブルであるかのようにクエリする方法を学び、更新可能なビューを通じてデータを更新する可能性を探り、不要になったビューを適切に削除する方法を学びます。

テーブルの作成：employees と departments

このステップでは、employees と departments の 2 つのテーブルを作成し、いくつかのサンプルデータを挿入します。これらのテーブルは、以降のステップでビューを作成し、クエリするために使用されます。

まず、ターミナルで次のコマンドを実行して、SQLite シェルを開きます。

sqlite3 /home/labex/project/employees.db

このコマンドは SQLite シェルを開き、employees.db データベースに接続します。データベースファイルが存在しない場合、SQLite はそれを作成します。

次に、次の SQL ステートメントを使用して employees テーブルを作成します。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary REAL
);

この SQL ステートメントは、employees という名前のテーブルを、id、first_name、last_name、department、および salary の 5 つのカラムで作成します。id カラムは、テーブルの主キー (primary key) です。

次に、いくつかのサンプルデータを employees テーブルに挿入します。

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Smith', 'Marketing', 60000.00),
('Peter', 'Jones', 'Sales', 55000.00),
('Mary', 'Brown', 'IT', 70000.00);

この SQL ステートメントは、employees テーブルに 4 つの行を挿入します。

次に、次の SQL ステートメントを使用して departments テーブルを作成します。

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    location TEXT
);

この SQL ステートメントは、departments という名前のテーブルを、id、name、および location の 3 つのカラムで作成します。id カラムは、テーブルの主キーです。

次に、いくつかのサンプルデータを departments テーブルに挿入します。

INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Marketing', 'London'),
('IT', 'San Francisco');

この SQL ステートメントは、departments テーブルに 3 つの行を挿入します。

テーブルの作成とデータの挿入を確認するには、テーブルにクエリを実行します。

SELECT * FROM employees;
SELECT * FROM departments;

これらのコマンドは、それぞれ employees テーブルと departments テーブルの内容を表示します。

シンプルなビューの作成

このステップでは、employees テーブルから特定のカラムを選択する employee_info という名前の単純なビューを作成します。

ビューとは、SQL ステートメントの結果セットに基づく仮想テーブルです。複雑なクエリを簡素化し、抽象化 (abstraction) のレベルを提供します。

employee_info ビューを作成するには、SQLite シェルで次の SQL ステートメントを実行します。

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

この SQL ステートメントは、employees テーブルから id、first_name、last_name、および department カラムを選択する employee_info という名前のビューを作成します。

ビューはテーブルであるかのようにクエリできます。

SELECT * FROM employee_info;

このコマンドは、employee_info ビューの内容を表示します。これは、employees テーブルのカラムのサブセット (subset) です。

結合を使用した複雑なビューの作成

このステップでは、employees テーブルと departments テーブルを JOIN する、employee_department_info という名前のより複雑なビューを作成します。

テーブルを JOIN すると、関連するカラムに基づいて複数のテーブルからデータを結合できます。

employee_department_info ビューを作成するには、SQLite シェルで次の SQL ステートメントを実行します。

CREATE VIEW employee_department_info AS
SELECT
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department = d.name;

この SQL ステートメントは、department カラムで employees テーブルと departments テーブルを JOIN する employee_department_info という名前のビューを作成します。従業員のファーストネーム (first name)、ラストネーム (last name)、部署 (department)、および部署の所在地 (location) を選択します。

ビューにクエリを実行して、結合されたデータを表示できます。

SELECT * FROM employee_department_info;

このコマンドは、employee_department_info ビューの内容を表示し、従業員の情報と部署の所在地を合わせて表示します。

集計関数を使用したビューの作成

このステップでは、集計関数 (aggregate functions) を使用して各部署の平均給与を計算する department_salary_stats という名前のビューを作成します。

集計関数は、一連の値に対して計算を実行し、単一の結果を返します。

department_salary_stats ビューを作成するには、SQLite シェルで次の SQL ステートメントを実行します。

CREATE VIEW department_salary_stats AS
SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
GROUP BY
    department;

この SQL ステートメントは、AVG 関数を使用して各部署の平均給与を計算し、GROUP BY 句を使用して結果を部署ごとにグループ化する department_salary_stats という名前のビューを作成します。

ビューにクエリを実行して、各部署の平均給与を表示できます。

SELECT * FROM department_salary_stats;

このコマンドは、department_salary_stats ビューの内容を表示し、各部署の平均給与を示します。

更新可能なビューを通じたデータ更新

このステップでは、SQLite ビューの制限事項と、INSTEAD OF トリガーを使用して真に更新可能なビューを作成する方法について学びます。

重要： デフォルトでは、SQLite ビューは読み取り専用です。ビューを通じてデータを直接 UPDATE、INSERT、または DELETE することはできません。ただし、INSTEAD OF トリガーを使用することで、更新可能なビューを作成できます。

まず、既存のビューで直接更新を試みることで、その制限事項を理解しましょう。

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

これは、SQLite ビューがデフォルトで読み取り専用であるため、エラーで失敗します。

真に更新可能なビューを作成するには、INSTEAD OF トリガーを使用する必要があります。employee_info ビューを再作成し、更新用の INSTEAD OF トリガーを追加しましょう。

DROP VIEW IF EXISTS employee_info;

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

次に、UPDATE 操作を処理する INSTEAD OF トリガーを作成します。

CREATE TRIGGER update_employee_info
INSTEAD OF UPDATE ON employee_info
BEGIN
    UPDATE employees
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        department = NEW.department
    WHERE id = OLD.id;
END;

これで、ビューを通じてデータを更新できます。

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

この UPDATE ステートメントは、INSTEAD OF トリガーが更新を基になる employees テーブルにリダイレクトするため、これで機能するようになります。

employees テーブルを直接クエリすることで、更新を確認できます。

SELECT * FROM employees WHERE id = 1;

このコマンドは、id = 1 の行を employees テーブルに表示し、department カラムが 'HR' に更新されていることが確認できるはずです。

古くなったビューとトリガーの削除

このステップでは、前のステップで作成したビューとトリガーを削除します。

データベースが進化するにつれて、一部のビューとトリガーは古くなったり、不要になったりすることがあります。クリーンで効率的なデータベーススキーマを維持するためには、これらのオブジェクトを削除することが重要です。

まず、INSTEAD OF トリガーを削除します。

DROP TRIGGER IF EXISTS update_employee_info;

次に、ビューを削除します。ビューを削除するには、DROP VIEW ステートメントを使用します。たとえば、employee_info ビューを削除するには、次のコマンドを実行します。

DROP VIEW IF EXISTS employee_info;

IF EXISTS 句は、ビューが存在しない場合にエラーが発生するのを防ぎます。

他のビューも同様に削除します。

DROP VIEW IF EXISTS employee_department_info;
DROP VIEW IF EXISTS department_salary_stats;

ビューが削除されたことを確認するには、sqlite_master テーブルをクエリします。

SELECT name FROM sqlite_master WHERE type='view';

このコマンドは空の結果を返すはずです。これは、データベースにビューがないことを示しています。

最後に、SQLite シェルを終了します。

.exit

このコマンドは、employees.db データベースへの接続を閉じ、Linux ターミナルに戻ります。

まとめ

この実験（Lab）では、SQLite でビューを作成、クエリ、更新、削除する方法を学びました。単一のテーブルに基づいたシンプルなビューの作成から始め、結合（join）や集計関数を含むより複雑なビューへと進みました。SQLite のビューはデフォルトで読み取り専用であること、そして INSTEAD OF トリガーを使用して真に更新可能なビューを作成する方法を学びました。また、不要になったビューやトリガーを適切に削除して、データベーススキーマをクリーンに保つ方法も学びました。これらのスキルは、複雑なクエリを簡素化し、データアクセスを制御し、SQLite データベースを効果的に管理するために不可欠です。