SQL　ビューを作成（CREATE VIEW文）

このチュートリアルでは、SQLを使ってビューを作成、更新、削除する方法を学びます。

ビューを作成してテーブルのアクセスを簡素化

ビューはデータベースに定義が保存されている仮想テーブルです。しかし、テーブルとは異なり、ビューには実際にはデータが含まれていません。代わりに、データベース内で常用の複雑なクエリを保存する方法を提供します。ただし、SQL SELECT文ビューのデータにアクセスする方法は、通常のテーブルや基テーブルを使用するのと同じです。

ユーザーがデータベースの全ての基表へのアクセス権ではなく、ビューを通じてデータにアクセスを許可することで、ビューはセキュリティメカニズムとしても使用できます。

文法

CREATE VIEW文を使ってビューを作成します。

CREATE　VIEW　view_name　AS　select_statement;

この点を明確に理解するために、以下を見てみましょう。employeesおよびdepartments表。

+--------+--------------+--------+---------+
|　emp_id　|　emp_name　　　　　|　salary　|　dept_id　|
+--------+--------------+--------+---------+
|　　　　　　1　|　Ethan　Hunt　　　|　　　5000　|　　　　　　　4　|
|　　　　　　2　|　Tony　Montana　|　　　6500　|　　　　　　　1　|
|　　　　　　3　|　Sarah　Connor　|　　　8000　|　　　　　　　5　|
|　　　　　　4　|　Rick　Deckard　|　　　7200　|　　　　　　　3　|
|　　　　　　5　|　Martin　Blank　|　　　5600　|　　　　NULL　|
+--------+--------------+--------+---------+

+---------+------------------+
|　dept_id　|　dept_name　　　　　　　　|
+---------+------------------+
|　　　　　　　1　| Administration |
|　　　　　　　2　| Customer Service |
|　　　　　　　3　| Finance |
|　　　　　　　4　| Human Resources |
|　　　　　　　5　| Sales |
+---------+------------------+

例えば、従業員のIDと名前、および彼らの部署名を取得するには、以下を実行する必要があります：左結合以下の操作を行います：

SELECT　t1.emp_id,　t1.emp_name,　t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

上記のクエリを実行すると、以下のような出力が得られます：

+--------+--------------+-----------------+
| emp_id | emp_name | dept_name |
+--------+--------------+-----------------+
|　　　　　　1　| Ethan Hunt | Human Resources |
|　　　　　　2　| Tony Montana | Administration |
|　　　　　　3　| Sarah Connor | Sales |
|　　　　　　4　| Rick Deckard | Finance |
|　　　　　　5　| Martin Blank | NULL |
+--------+--------------+-----------------+

しかし、この記録にアクセスするたびに、再度全体のクエリを入力する必要があります。このような操作を頻繁に行うと、非常に不便で煩わしくなります。

この場合、以下のようにクエリ結果をアクセスしやすくするためのビューを作成できます：

CREATE VIEW emp_dept_view AS
SELECT　t1.emp_id,　t1.emp_name,　t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

今では、以下のようにemp_dept_viewビューを使用して同じレコードにアクセスできます：

SELECT　*　FROM emp_dept_view;

ご覧の通り、ビュー上でどれだけ時間と労力を節約できるかがわかります。

既存のビューを置き換える

MySQLでは、既存のビューを更新または置き換える場合は、ビューを削除して新しいビューを作成するか、または以下のようにCREATE VIEW文句のOR REPLACE子句を使用することができます：

CREATE OR REPLACE VIEW view_name AS select_statement;

以下のSQL文は、既存のビューを置き換えまたは変更します。emp_dept_viewビューの定義は、新しい列salaryを追加することで行われます。

--　MySQLデータベースの構文
CREATE　OR　REPLACE　VIEW　emp_dept_view　AS
SELECT　t1.emp_id,　t1.emp_name,　t1.salary,　t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

ビューを更新した後、以下の文を実行すると：

SELECT　*　FROM　emp_dept_view　ORDER　BY　emp_id;

結果出力で別の列が見られます。salary、以下のように表示されます：

+--------+--------------+--------+-----------------+
|　emp_id　|　emp_name　　　　　|　salary　|　dept_name　　　　　　　|
+--------+--------------+--------+-----------------+
|　　　　　　1　|　Ethan　Hunt　　　|　　　5000　|　Human　Resources　|
|　　　　　　2　|　Tony　Montana　|　　　6500　|　Administration　　|
|　　　　　　3　|　Sarah　Connor　|　　　8000　|　Sales　　　　　　　　　　　|
|　　　　　　4　|　Rick　Deckard　|　　　7200　|　Finance　　　　　　　　　|
|　　　　　　5　|　Martin　Blank　|　　　5600　|　NULL　　　　　　　　　　　　|
+--------+--------------+--------+-----------------+

ビューを通じてデータを更新することはサポートされていません。

理論的には、以下の以外のSELECT文の外でも、ビューに対して以下の操作を実行できます：INSERT、UPDATEおよびDELETE。しかし、すべてのビューが更新可能でないことに注意してください、つまりベースのソーステーブルのデータを変更できるようにすることはできません。更新可能性にはいくつかの制限があります。

通常、ビューに以下のいずれかが含まれている場合、ビューは更新できません：

• DISTINCT、GROUP BYまたはHAVING句。

• 集計関数、例えばAVG()、COUNT()、SUM()、MIN()、MAX()など。

• UNION、UNION ALL、CROSS JOIN、EXCEPTまたはINTERSECT演算子。

• WHERE句のサブクエリがFROM句のテーブルを参照している。

である従業員の給料を更新します。

以下の文は、emp_idが1の従業員の給料(salary)。

UPDATE emp_dept_view SET salary = '6000'　
WHERE emp_id =　1;

ビューの削除

ビューが不要になった場合、DROP VIEW文を使用してデータベースから削除することができます。以下の文法が示されています：

DROP VIEW view_name;

以下のコマンドは、データベースからビューを削除します。emp_dept_view。

DROP VIEW emp_dept_view;