MySQL ビューと仮想テーブル

はじめに

この実験 (Lab) では、MySQL のビューの操作方法を学びます。ビューは、SQL ステートメントの結果セットに基づいた仮想テーブルです。ビューは、複雑なクエリを単純化したり、データの複雑さを隠蔽したり、データアクセスに抽象化レベルを提供したりするのに役立ちます。ここでは、MySQL ビューの作成、更新、クエリ、および削除方法を学びます。

まず、MySQL サーバーに接続し、company という名前のデータベースを作成し、employees テーブルでそれを投入します。次に、employees テーブルをフィルタリングして 'Sales' 部門の従業員のみを表示する sales_employees という名前のビューを作成します。ビューの構造を確認し、通常のテーブルのようにクエリする方法を学びます。

MySQL への接続とデータベース/テーブルの作成

このステップでは、MySQL サーバーに接続し、実験に必要なデータベースとテーブルをセットアップします。これには、company という名前のデータベースを作成し、その中に employees テーブルを作成して、いくつかのサンプルデータを投入することが含まれます。

まず、LabEx VM でターミナルを開きます。あなたは既に ~/project ディレクトリにいるはずです。

root ユーザーとして MySQL サーバーに接続します。sudo 権限があり、MySQL の root ユーザーは auth_socket プラグインを使用するように設定されているため、sudo を使用してパスワードなしで接続できます。

sudo mysql -u root

これで MySQL シェルに入りました。プロンプトが mysql> に変わります。

MySQL シェル内で、company という名前のデータベースを作成します。

CREATE DATABASE company;

company データベースに切り替えます。これにより、後続のコマンドはこのデータベース内で実行されます。

USE company;

次に、従業員情報を格納する employees という名前のテーブルを作成します。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

employees テーブルにサンプルデータを挿入します。

INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');

すべての行を選択して、employees テーブルのデータを検証できます。

SELECT * FROM employees;

出力は挿入された従業員データを示すはずです。

+----+------------+-----------+----------+-------------+
| id | first_name | last_name | salary   | department  |
+----+------------+-----------+----------+-------------+
|  1 | John       | Doe       | 60000.00 | Sales       |
|  2 | Jane       | Smith     | 75000.00 | Marketing   |
|  3 | Robert     | Jones     | 50000.00 | Sales       |
|  4 | Emily      | Brown     | 80000.00 | Engineering |
|  5 | Michael    | Davis     | 65000.00 | Marketing   |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)

MySQL への接続、データベースの作成、およびデータを含むテーブルの準備が正常に完了しました。

ビューの作成

このステップでは、employees テーブルに基づいたビューを作成します。ビューは、仮想テーブルのように機能する保存された SQL クエリです。ビュー自体はデータを格納しませんが、1 つ以上の基になるテーブルからデータを提示します。

あなたはまだ MySQL シェルに接続しており、company データベースに接続されているはずです。そうでない場合は、sudo mysql -u root で再接続し、次に USE company; を実行してください。

ここでは、'Sales' 部門で働く従業員のみを表示する sales_employees という名前のビューを作成します。このビューは、employees テーブルから特定の列を選択します。

CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

このステートメントは、sales_employees という名前のビューを定義します。ビューの定義は、AS キーワードに続く SELECT クエリです。このクエリは、employees テーブルから id、first_name、last_name、および salary 列を選択しますが、department 列が 'Sales' と等しい行のみを対象とします。

ビューが作成されたことを確認するために、現在のデータベース内のテーブルとビューを一覧表示できます。

SHOW TABLES;

employees と sales_employees の両方がリストされているはずです。

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| sales_employees   |
+-------------------+
2 rows in set (0.00 sec)

テーブルと同様に、ビューの構造を記述することもできます。

DESCRIBE sales_employees;

これにより、ビューに含まれる列が表示されます。

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

sales_employees という名前のビューを正常に作成しました。

ビューを使用したデータのクエリ

このステップでは、作成した sales_employees ビューを使用してデータをクエリする方法を学びます。ビューは通常のテーブルと同様にクエリでき、特定のデータサブセットへのアクセスを簡素化する方法を提供します。

sales_employees ビューからすべてのデータを取得するには、標準の SELECT ステートメントを使用します。

SELECT * FROM sales_employees;

このクエリは、ビューで定義された基になる SELECT ステートメントを実行し、結果を返します。'Sales' 部門の従業員のみが表示されるはずです。

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
|    3 | Robert     | Jones     | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)

ビューから取得したデータに、さらにフィルタリングや並べ替えを適用することもできます。たとえば、給与が 55000 より大きいセールス担当者を見つけるには、次のようにします。

SELECT * FROM sales_employees WHERE salary > 55000;

これにより、ビューから給与が 55000 より大きい行のみが返されます。

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
+------+------------+-----------+----------+
1 row in set (0.00 sec)

ビューから特定の列を選択することもできます。

SELECT first_name, last_name FROM sales_employees;

これにより、セールス担当者の名と姓のみが表示されます。

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
| Robert     | Jones     |
+------------+-----------+
2 rows in set (0.00 sec)

ビューのクエリは、基本的にテーブルのクエリと同じですが、ビューは基になるデータソースからの事前定義されたフィルタリングと列の選択を提供します。

ALTER VIEW によるビュー定義の更新

このステップでは、ALTER VIEW ステートメントを使用して既存のビューの定義を変更する方法を学びます。これは、ビューに含まれる列やフィルタリング条件を変更する必要がある場合に便利です。

現在、sales_employees ビューには id、first_name、last_name、および salary が含まれています。ビューを department 列も含むように変更しましょう。

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';

このステートメントは、ALTER VIEW の後にビュー名と、ビューを定義する新しい SELECT クエリを使用します。新しいクエリには department 列が含まれるようになりました。

ビューが更新されたことを確認するために、再度記述します。

DESCRIBE sales_employees;

出力に department 列が表示されるはずです。

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
| department | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

更新されたビューをクエリして、新しい列を確認します。

SELECT * FROM sales_employees;

出力には department 列が含まれるようになります。

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
|    3 | Robert     | Jones     | 50000.00 | Sales      |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

sales_employees ビューの定義を正常に更新しました。

ビューの削除とクリーンアップ

この最終ステップでは、ビューを削除（ドロップ）し、この実験中に作成されたデータベースとテーブルをクリーンアップする方法を学びます。

sales_employees ビューを削除するには、DROP VIEW ステートメントを使用します。

DROP VIEW sales_employees;

このコマンドは、company データベースから sales_employees ビューを永続的に削除します。

ビューが削除されたことを確認するには、記述を試みることができます。

DESCRIBE sales_employees;

これにより、ビューが存在しないことを示すエラーメッセージが表示されるはずです。

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

次に、employees テーブルと company データベースをクリーンアップしましょう。

employees テーブルを削除します。

DROP TABLE employees;

company データベースを削除します。

DROP DATABASE company;

MySQL シェルを終了するには、次のように入力します。

exit

ビュー、テーブル、およびデータベースを正常に削除し、この実験で使用されたリソースをクリーンアップしました。

まとめ

この実験では、MySQL のビューの操作方法を学びました。まず、MySQL サーバーに接続し、データベースとテーブルをセットアップしました。次に、employees テーブルのフィルタリングされたビューを提供する sales_employees という名前のビューを作成しました。

SELECT ステートメントを使用してビューをクエリする練習を行い、ビューがデータアクセスをどのように簡素化するかを示しました。また、ALTER VIEW ステートメントを使用して既存のビューの定義を変更し、追加の列を含める方法も学びました。最後に、DROP VIEW ステートメントを使用してビューを削除する方法を学び、データベースとテーブルをクリーンアップしました。

これで、データベース内のデータの管理とアクセスに役立つツールである MySQL ビューの基本的な概念と操作を理解しました。