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

はじめに (Introduction)

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

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

Skills Graph

テーブルの作成：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 テーブルの内容を表示します。

単純なビューの作成 (Create a Simple View)

このステップでは、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) です。

JOIN を使用した複雑なビューの作成 (Create a Complex View with Joins)

このステップでは、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 ビューの内容を表示し、従業員の情報と部署の所在地を合わせて表示します。

集計関数を使用したビューの作成 (Create a View with Aggregate Functions)

このステップでは、集計関数 (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 ビューの内容を表示し、各部署の平均給与を示します。

更新可能なビューを介したデータの更新 (Update Data Through an Updatable View)

このステップでは、employee_info ビューを介して employees テーブルのデータを更新します。

すべてのビューが更新可能であるとは限りません。ビューは通常、単一のテーブルから選択し、そのテーブルの主キー (primary key) を含んでいる場合に更新可能です。

まず、employee_info ビューを削除して再作成し、employees テーブルの主キーである id カラムが含まれていることを確認しましょう。

DROP VIEW IF EXISTS employee_info;

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

次に、id = 1 の従業員の部署 (department) を 'HR' に更新します。

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

この SQL ステートメントは、employee_info ビューを介して、id = 1 の従業員の employees テーブルの department カラムを更新します。

employees テーブルに直接クエリを実行して、更新を確認できます。

SELECT * FROM employees WHERE id = 1;

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

不要になったビューの削除 (Remove Obsolete Views)

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

データベースが進化するにつれて、一部のビューが古くなったり、不要になったりする可能性があります。クリーンで効率的なデータベーススキーマ (database schema) を維持するために、これらのビューを削除することが重要です。

ビューを削除するには、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 ターミナルに戻ります。

まとめ (Summary)

この実験 (Lab) では、SQLite でのビューの作成、クエリ、更新、および削除の方法を学びました。単一のテーブルに基づく単純なビューの作成から始め、結合 (join) や集計関数 (aggregate functions) を含むより複雑なビューへと進みました。また、更新可能なビューを介してデータを更新する方法や、クリーンなデータベーススキーマ (database schema) を維持するために不要になったビューを削除する方法も学びました。これらのスキルは、複雑なクエリを簡素化し、データアクセスを制御し、SQLite データベースを効果的に管理するために不可欠です。