MySQLビューの基本と実践ガイド

MySQLビューの基本概念

ビューは、1つまたは複数のテーブル（または他のビュー）から派生した仮想的なテーブルです。データベースの利用者がデータをどのように見るかを定義するもので、物理的なデータベース上にユーザーの観点から必要なデータ構造を定義します。このユーザー観点に基づいて定義されたデータ構造がビューとなります。

ビューと通常のテーブル（基本テーブル）は異なり、ビューは仮想テーブルです。つまり、ビューに対応するデータは実際には格納されず、データベースにはビューの定義のみが保存されます。ビューのデータを操作する際、システムはビューの定義に基づいて関連する基本テーブルを操作します。

ビューは一度定義されると、テーブルと同様にクエリ、変更、削除、更新が可能です。

ビューを使用する利点

データを集中させ、ユーザーのデータ検索と処理を簡素化します。必要なデータが複数のテーブルに分散している場合、ビューを定義することでそれらを一箇所に集約できます。
データベースの複雑性を隠蔽します。ユーザーは複雑なデータベースのテーブル構造を理解する必要がなく、データベーステーブルの変更もユーザーのデータベース使用に影響しません。
ユーザー権限の管理を簡素化します。ユーザーにテーブルの特定の列のみを使用する権限を付与する代わりに、ビューを使用する権限を付与するだけで済み、セキュリティも向上します。
データ共有を容易にします。各ユーザーが独自に必要なデータを定義・保存する必要がなく、同じデータを一度だけ保存して共有できます。
データを再編成して他のアプリケーションに出力しやすくなります。

CREATE VIEW文を使用したビューの作成

ビューを作成するための基本構文は以下の通りです：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW ビュー名[(カラムリスト)]
AS SELECT文 
[WITH [CASCADED | LOCAL] CHECK OPTION]

例：student_dbデータベースにstudent_viewというビューを作成する場合：

CREATE VIEW student_view AS SELECT * FROM students;

別のデータベースのテーブルからビューを作成する場合：

CREATE VIEW current_db.student_view AS SELECT * FROM other_db.students;

構文要素の説明

カラムリスト：ビューのカラムに明示的な名前を定義する場合に使用します。カンマで区切られたカラム名をリストします。カラムリストの名前の数は、SELECT文が検索するカラム数と等しくなければなりません。ソーステーブルやビューと同じカラム名を使用する場合は、カラムリストを省略できます。
OR REPLACE：この句を指定すると、既存の同名ビューを置き換えることができます。
ALGORITHM句：標準SQLに対するMySQLの拡張機能で、MySQLがビューを処理する方法を指定します。ALGORITHMには3つの値を指定できます：MERGE、TEMPTABLE、またはUNDEFINED。ALGORITHM句がない場合、デフォルトのアルゴリズムはUNDEFINEDです。MERGEを指定すると、ビューを参照するステートメントのテキストがビュー定義とマージされ、ビュー定義の一部がステートメントの対応する部分を置き換えます。MERGEアルゴリズムは、ビュー内の行と基本テーブル内の行に一対一の関係があることを要求します。この関係がない場合は、一時テーブルを使用する必要があります。TEMPTABLEを指定すると、ビューの結果が一時テーブルに格納され、そのテーブルを使用してステートメントが実行されます。

SELECT文の制限

ビューを作成するSELECT文には以下の制限があります：

ビューを定義するユーザーは、参照するテーブルやビューに対してクエリ（SELECT文の実行）権限を持っている必要があります。
FROM句にサブクエリを含めることはできません。
システム変数やユーザー変数を参照することはできません。
プリペアドステートメントのパラメータを参照することはできません。
定義で参照するテーブルやビューは存在している必要があります。
現在のデータベースではないテーブルやビューを参照する場合は、テーブル名やビュー名の前にデータベース名を付ける必要があります。
ビュー定義ではORDER BYの使用が許可されていますが、特定のビューから選択し、そのビューが独自のORDER BYを持つステートメントを使用している場合、ビュー定義のORDER BYは無視されます。
SELECT文の他のオプションや句について、ビューにも同じオプションが含まれている場合、その効果は未定義です。例えば、ビュー定義にLIMIT句が含まれており、SELECT文が独自のLIMIT句を使用している場合、MySQLがどちらのLIMITを使用するかは未定義です。

WITH CHECK OPTION

WITH CHECK OPTIONは、更新可能なビューに対して行われる変更が、SELECT文で指定された制限条件に適合することを保証します。これにより、データを変更した後でも、そのデータをビューを通じて確認できるようになります。ビューが別のビューに基づいて定義されている場合、WITH CHECK OPTIONは2つのパラメータを提供します：LOCALとCASCADEDです。これらはチェックテストの範囲を決定します。LOCALキーワードはCHECK OPTIONを定義されたビューのみに適用し、CASCADEDはすべてのビューにチェックを適用します。いずれのキーワードも指定されていない場合、デフォルト値はCASCADEDです。

ビュー使用時の注意点

デフォルトでは、新しいビューは現在のデータベースに作成されます。特定のデータベースに明示的にビューを作成する場合は、作成時に名前をdb_name.view_nameとして指定する必要があります。
ビューの命名は識別子の命名規則に従う必要があり、テーブルと同名にすることはできません。また、各ユーザーに対してビュー名は一意でなければなりません。つまり、異なるユーザーであっても、同じ定義のビューを作成する場合は異なる名前を使用する必要があります。
ルール、デフォルト値、またはトリガーをビューに関連付けることはできません。
ビュー上にインデックス（全文インデックスを含む）を作成することはできません。

実践例

例1：学科別学生ビューの作成

現在のデータベースがTESTであると仮定し、EDUデータベースにcs_studentsビューを作成します。このビューには、コンピュータサイエンス学科の各学生の学籍番号、選択した科目コード、および成績が含まれます。このビューに対する変更が「学科名がコンピュータサイエンス」という条件に適合することを保証します。

CREATE OR REPLACE VIEW EDU.cs_students
AS SELECT s.student_id, c.course_code, g.grade
FROM EDU.students s, EDU.grades g, EDU.courses c
WHERE s.student_id = g.student_id 
AND g.course_code = c.course_code 
AND s.department = 'コンピュータサイエンス'
WITH CHECK OPTION;

例2：平均成績ビューの作成とクエリ

平均成績が80点以上の学生の学籍番号と平均成績を検索します。まず、student_avg_gradesビューを作成します。このビューには学籍番号（ビューではidという列名）と平均成績（ビューではavg_gradeという列名）が含まれます。

CREATE VIEW student_avg_grades(id, avg_grade)
AS SELECT student_id, AVG(grade)
FROM grades
GROUP BY student_id;

次に、student_avg_gradesビューをクエリします。

SELECT * FROM student_avg_grades WHERE avg_grade >= 80;

これらの例からわかるように、ビューを作成することで、最終ユーザーから複雑なテーブル結合を隠蔽し、ユーザーのSQLプログラミングを簡素化できます。

注意：ビューを使用してクエリを行う場合、関連する基本テーブルに新しいフィールドが追加されても、ビューにはその新しいフィールドは含まれません。例えば、cs_studentsビューの列がstudentsテーブルのすべての列に関連付けられている場合、studentsテーブルに「出身地」フィールドが追加されても、cs_studentsビューでは「出身地」フィールドのデータをクエリできません。また、ビューに関連付けられたテーブルやビューが削除された場合、そのビューは使用できなくなります。

更新可能なビュー

ビューを通じて基本テーブルのデータを更新するには、そのビューが更新可能なビューである必要があります。つまり、INSERT、UPDATE、またはDELETEなどのステートメントで使用できる必要があります。更新可能なビューの場合、ビュー内の行と基本テーブル内の行の間には一対一の関係が存在する必要があります。また、ビューを更新不可能にする特定の構造もあります。

ビューが以下の構造のいずれかを含む場合、それは更新不可能です：

集計関数（SUM、AVG、COUNT、MAX、MINなど）
DISTINCTキーワード
GROUP BY句
ORDER BY句
HAVING句
UNION演算子
選択リスト内のサブクエリ
FROM句に複数のテーブルが含まれる場合
SELECT文で更新不可能なビューを参照している場合
WHERE句内のサブクエリでFROM句のテーブルを参照している場合
ALGORITHMオプションがTEMPTABLEに指定されている場合（一時テーブルを使用すると常にビューは更新不可能になります）

ビューを通じたデータ操作

データの挿入

INSERT文を使用して、ビューを通じて基本テーブルにデータを挿入できます。

例：cs_students_viewビューを作成し、このビューにレコードを挿入します：（'S2023001', '田中太郎', 'コンピュータサイエンス', 1, '2002-05-15', 48, NULL, NULL）

まず、cs_students_viewビューを作成します（以下の削除、変更の例でもこのビューを使用します）：

CREATE OR REPLACE VIEW cs_students_view
AS SELECT * FROM students
WHERE department = 'コンピュータサイエンス'
WITH CHECK OPTION;

注意：ビューを作成する際にWITH CHECK OPTION句を追加するのは、この句がデータを更新する際に、新しいデータがビュー定義のWHERE句の条件に適合するかどうかをチェックするためです。WITH CHECK OPTION句は更新可能なビューと一緒にのみ使用できます。

次に、レコードを挿入します：

INSERT INTO cs_students_view 
VALUES('S2023001', '田中太郎', 'コンピュータサイエンス', 1, '2002-05-15', 48, NULL, NULL);

ここで、レコードを挿入する際の学科名は「コンピュータサイエンス」でなければなりません。これはWITH CHECK OPTION句によって強制されます。

この時点で、SELECT文を使用してcs_students_viewビューと基本テーブルstudentsをクエリすると、studentsテーブルにそのレコードが追加されていることがわかります。

ビューが依存する基本テーブルが複数ある場合、そのビューにデータを挿入することはできません。これは複数の基本テーブルに影響を与えるためです。例えば、cs_courses_viewビュー（複数の基本テーブルに依存）にデータを挿入することはできません。

INSERT文にはもう一つの制限があります：SELECT文にはFROM句で指定されたテーブルのすべてのNULL不可の列が含まれている必要があります。例えば、cs_students_viewビューを定義する際に「氏名」フィールドを含めない場合、データを挿入する際にエラーが発生します。

データの変更

UPDATE文を使用して、ビューを通じて基本テーブルのデータを変更できます。

例：cs_students_viewビュー内のすべての学生の総単位数を8増加させます。

UPDATE cs_students_view 
SET total_credits = total_credits + 8;

このステートメントは、実際にはcs_students_viewビューが依存する基本テーブルstudentsのすべてのレコードのtotal_creditsフィールドの値を元の値より8増加させます。

ビューが複数の基本テーブルに依存する場合、そのビューを一度に変更できる基本テーブルは1つだけです。

データの削除

DELETE文を使用して、ビューを通じて基本テーブルのデータを削除できます。

例：cs_students_viewから女子学生のレコードを削除します。

DELETE FROM cs_students_view 
WHERE gender = 0;

注意：複数の基本テーブルに依存するビューに対しては、DELETE文を使用できません。例えば、cs_courses_viewビュー（複数の基本テーブルに依存）に対してDELETE文を実行して、関連する基本テーブルのデータを削除することはできません。

ALTER文を使用したビュー定義の変更

既存のビューの定義を変更するには、ALTER VIEW文を使用します。

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW ビュー名 [(カラムリスト)] 
AS SELECT文
[WITH [CASCADED | LOCAL] CHECK OPTION]

ALTER VIEW文の構文はCREATE VIEW文と似ています。

DROP文を使用したビューの削除

ビューを削除するには、DROP VIEW文を使用します。

DROP VIEW [IF EXISTS] ビュー名1 [,ビュー名2]...
[RESTRICT | CASCADE]

IF EXISTSを宣言すると、ビューが存在しない場合でもエラーメッセージは表示されません。RESTRICTとCASCADEを宣言することもできますが、これらは特に影響を与えません。

DROP VIEWを使用すると、一度に複数のビューを削除できます。例えば：

DROP VIEW cs_courses_view, cs_students_view;

このステートメントは、cs_courses_viewとcs_students_viewの両方のビューを削除します。

タグ: MySQL ビューデータベース SQL データ管理

7月31日 20:16 投稿

異端開発室