MySQL ビューと仮想テーブル

はじめに

この実験では、SQL 文の結果セットに基づく仮想テーブルである MySQL ビューの使い方を学びます。この実験では、SELECT クエリからビューを作成し、ALTER VIEW を使用してビュー定義を更新し、ビューを通じてデータをクエリし、最後にビューを削除して整理する方法をカバーします。

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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/AdvancedFeaturesGroup(["Advanced Features"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_database("Database Deletion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_table("Table Removal") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/AdvancedFeaturesGroup -.-> mysql/views("View Management") subgraph Lab Skills mysql/use_database -.-> lab-550920{{"MySQL ビューと仮想テーブル"}} mysql/create_database -.-> lab-550920{{"MySQL ビューと仮想テーブル"}} mysql/drop_database -.-> lab-550920{{"MySQL ビューと仮想テーブル"}} mysql/create_table -.-> lab-550920{{"MySQL ビューと仮想テーブル"}} mysql/drop_table -.-> lab-550920{{"MySQL ビューと仮想テーブル"}} mysql/select -.-> lab-550920{{"MySQL ビューと仮想テーブル"}} mysql/insert -.-> lab-550920{{"MySQL ビューと仮想テーブル"}} mysql/views -.-> lab-550920{{"MySQL ビューと仮想テーブル"}} end

SELECT クエリからビューを作成する

このステップでは、SELECT クエリから MySQL でビューを作成する方法を学びます。ビューは、SQL 文の結果セットに基づく仮想テーブルです。ビューは、複雑なクエリを簡素化し、データの複雑さを隠し、データアクセスに抽象化のレベルを提供するのに役立ちます。

まず、MySQL サーバーに接続しましょう。LabEx VM でターミナルを開きます。すでに ~/project ディレクトリにいるはずです。

mysql -u root -p

パスワードの入力を求められたら、root パスワードを入力します。root パスワードを設定していない場合は、Enter キーを押してください。

次に、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)
);

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');

次に、'Sales' 部門の従業員のみを表示する sales_employees という名前のビューを作成しましょう。

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

この文は、sales_employees という名前のビューを作成します。このビューは、employees テーブルから id、first_name、last_name、salary 列を選択しますが、department が 'Sales' の行のみを対象とします。

ビューが作成されたことを確認するには、以下のように記述できます。

DESCRIBE sales_employees;

このコマンドは、テーブルを記述するのと同様に、sales_employees ビューの構造を表示します。

また、通常のテーブルのようにビューをクエリすることもできます。

SELECT * FROM sales_employees;

これにより、employees テーブルのデータに基づいて、Sales 部門のすべての従業員が表示されます。

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

これで、SELECT クエリからビューを正常に作成しました。

ALTER VIEW でビュー定義を更新する

このステップでは、MySQL の ALTER VIEW 文を使用して既存のビューの定義を更新する方法を学びます。これは、選択する列、フィルタリング条件、またはビューの基になるクエリの他の側面を変更する必要がある場合に便利です。

前のステップから続けると、まだ MySQL サーバーに接続され、company データベースを使用しているはずです。そうでない場合は、以下を使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら入力し、次に以下を実行します。

USE company;

前に 'Sales' 部門の従業員を表示する sales_employees という名前のビューを作成したことを思い出してください。このビューに department 列を追加したいとしましょう。ALTER VIEW 文を使用してビュー定義を変更することができます。

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

この文は、sales_employees ビューを変更して、結果セットに department 列を含めるようにします。ALTER VIEW 文は CREATE VIEW 文に非常に似ています。単に CREATE を ALTER に置き換えるだけです。

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

DESCRIBE sales_employees;

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

また、更新されたビューをクエリすることもできます。

SELECT * FROM sales_employees;

これにより、Sales 部門の従業員の他の列とともに 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 部門で給与が 55000 を超える従業員を含めるようにビューを再度変更しましょう。

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

ここで、更新されたビューをクエリします。

SELECT * FROM sales_employees;

これにより、Sales 部門で給与が 55000 を超える従業員が表示されます。

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

これで、ALTER VIEW 文を使用してビューの定義を正常に更新しました。

ビューを使用してデータをクエリする

このステップでは、前のステップで作成および変更したビューを使用してデータをクエリする方法を学びます。ビューは通常のテーブルと同じようにクエリでき、ビューの定義に基づいて特定のデータを取得することができます。

mysql -u root -p

パスワードの入力を求められたら入力し、次に以下を実行します。

USE company;

現在、'Sales' 部門で給与が 55000 を超える従業員を表示する sales_employees という名前のビューがあることを思い出してください。

sales_employees ビューからすべてのデータを取得するには、単純な SELECT 文を使用できます。

SELECT * FROM sales_employees;

これにより、ビューの定義を満たすすべての列と行が表示されます。前のステップに基づくと、Sales 部門で給与が 55000 を超える従業員のみが表示されるはずです。

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

また、WHERE 句を使用して、ビューから取得するデータをさらにフィルタリングすることもできます。たとえば、sales_employees ビューから id = 1 の従業員を見つけるには、以下のようにします。

SELECT * FROM sales_employees WHERE id = 1;

これにより、id が 1 の行のみが返されますが、その従業員がビューの定義（Sales 部門で給与が 55000 を超える）も満たしている場合に限ります。

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

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

SELECT first_name, last_name FROM sales_employees;

これにより、sales_employees ビューの従業員の first_name 列と last_name 列のみが表示されます。

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

ビューで集計関数を使用することもできます。たとえば、sales_employees ビューの従業員の平均給与を求めるには、以下のようにします。

SELECT AVG(salary) FROM sales_employees;

これにより、平均給与が計算されて表示されます。

+-------------+
| AVG(salary) |
+-------------+
|  60000.0000 |
+-------------+
1 row in set (0.00 sec)

ビューをクエリすることで、実質的には基になる employees テーブルをクエリしていますが、ビューで定義された制約が適用されます。これにより、複雑なクエリが簡素化され、抽象化のレベルが提供されます。

ビューを削除してクリーンアップする

このステップでは、前のステップで作成したビューを削除する方法を学びます。ビューを削除すると、そのビューはデータベースから削除されます。不要になったリソースをクリーンアップすることは重要です。

mysql -u root -p

パスワードの入力を求められたら入力し、次に以下を実行します。

USE company;

これまでに作成し、変更した sales_employees という名前のビューがあります。このビューを削除するには、DROP VIEW 文を使用します。

DROP VIEW sales_employees;

この文により、company データベースから sales_employees ビューが削除されます。

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

DESCRIBE sales_employees;

これは、テーブル（またはビュー）が存在しないことを示すエラーメッセージを返すはずです。

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

あるいは、ビューから選択しようとすることもできます。

SELECT * FROM sales_employees;

これも、テーブル（またはビュー）が存在しないことを示すエラーメッセージを返します。

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

最後に、完全にクリーンアップするために、employees テーブルと company データベースを削除しましょう。

DROP TABLE employees;
DROP DATABASE company;

これで、ビューを正常に削除し、データベースをクリーンアップしました。

まとめ

この実験では、SELECT クエリから MySQL でビューを作成する方法を学びました。これには、MySQL サーバーに接続し、データベースとサンプルデータを持つ employees という名前のテーブルを作成することが含まれていました。その後、'Sales' 部門の従業員のみを表示するように employees テーブルをフィルタリングする sales_employees という名前のビューを作成しました。

この実験では、CREATE VIEW 文を使用してビューを定義し、含める列とフィルタリング条件を指定する方法を示しました。また、DESCRIBE を使用してビューの作成を確認する方法と、SELECT を使用して通常のテーブルのようにビューをクエリする方法も学びました。