はじめに
この実験 (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 データベースを効果的に管理するために不可欠です。
