9. ビュー
ビューとは、CREATE TABLE で定義された実テーブルから作成される仮想的なテーブルのことです。ビューはあくまでも仮想テーブルなので、その中にデータは存在しません。ビューは実テーブルをどのように見るのかを定義したものと言えます。
もうすでに使っていますが、CREATE TABLE で定義された、データの入ったテーブルのことを実テーブル、この実テーブルから作られる仮想的なテーブルをビューと呼ぶことにします。
9.1. ビューのメリット
ビューの実体は SELECT 文によるクエリを定義したものです。ビューにアクセスすると、定義されたクエリが実行され、実テーブルから必要なデータが抽出されます。また、ビューからビューを定義することも可能です。ですから、あらかじめ毎回入力するのが面倒な複雑なクエリを一度ビューとして定義しておけば、ユーザやプログラムは単純なクエリを実行するだけですみます。
このような開発労力の軽減という観点以外に、セキュリティについてもメリットもあります。たとえば、実テーブルのデータの中に一般ユーザには見せたくないものがある場合、実テーブルへのアクセス権限を一般ユーザには与えることはできません。しかし、見せたくないデータを除いたビューを定義することにより、一般ユーザでも実テーブルのデータを利用できるようになります。
9.2. ビューの作成
ビューを作成するには CREATE VIEW 文を用います。基本構文は次のとおりです。
CREATE VIEW ビュー名 [(列名, 列名, .....)] AS SELECT文 [WITH CHECK OPTION];
ビュー名の後には、抽出するデータの列名を記述します。列名を省略した場合、SELECT 文で指定した列の名前がそのまま使用されます。SELECT 文で演算子を使用した場合や、改めて列名を指定したい場合に明示的に記述するものです。
ビュー名と列名の後には、AS に続けて SELECT 文によるクエリを記述します。このクエリの結果がこのビューのデータということになります。[WITH CHECK OPTION] はビューの更新に関連するオプションです。ビューの更新については後の節で説明します。
この章の SQL のサンプルはこれまでの章でも使用してきた 受注表・顧客表・商品表 を実テーブルとして使用します。sample.21-1 では v_受注という名前のビューを定義し、最後にビューの内容を確認しています。
-- sample.21-1 --/* ビューの作成 */ CREATE VIEW v_受注 AS SELECT JJ.受注番号, KK.顧客名, SS.商品名, JJ.受注個数, JJ.納品日 FROM 受注表 JJ, 顧客表 KK, 商品表 SS WHERE JJ.顧客コード = KK.顧客コード AND JJ.商品コード = SS.商品コード ; /* 確認 */ SELECT JJ.受注番号, KK.顧客名, SS.商品名, JJ.受注個数, JJ.納品日 FROM 受注表 JJ, 顧客表 KK, 商品表 SS WHERE JJ.顧客コード = KK.顧客コード AND JJ.商品コード = SS.商品コード ;
確認の実行結果
受注番号 顧客名 商品名 受注個数 納品日 ─────── ──────────── ────── ──────── ─────── 10001 KUROKIYA JUICE 300 20010401 10002 ONSIDE BEER 200 20010401 10003 KUSHINANA BEER 150 20010401 10004 KUSHINANA JUICE 80 20010402 10005 ONSIDE WINE 110 20010402 10006 KUROKIYA TEA 250 20010402 10007 FIRST HOUSE JUICE 45 20010402 10008 KUSHINANA BEER 135 20010403 10009 FIRST HOUSE TEA 65 20010403 10010 KUROKIYA BEER 290 20010403 10011 FIRST HOUSE TEA 60 20010404 10012 KUSHINANA BEER 175 20010404
ビュー確認
SELECT * FROM v_受注 ;
ビュー確認の実行結果
受注番号 顧客名 商品名 受注個数 納品日 ─────── ──────────── ────── ──────── ─────── 10001 KUROKIYA JUICE 300 20010401 10002 ONSIDE BEER 200 20010401 10003 KUSHINANA BEER 150 20010401 10004 KUSHINANA JUICE 80 20010402 10005 ONSIDE WINE 110 20010402 10006 KUROKIYA TEA 250 20010402 10007 FIRST HOUSE JUICE 45 20010402 10008 KUSHINANA BEER 135 20010403 10009 FIRST HOUSE TEA 65 20010403 10010 KUROKIYA BEER 290 20010403 10011 FIRST HOUSE TEA 60 20010404 10012 KUSHINANA BEER 175 20010404
定義したビューから特定の行や列を出力することも出来ます。sample.21-2 は定義したv_受注から、受注個数が 100以上である行を選択し、その顧客名、商品名及び受注個数を出力するものです。
-- sample.21-2 --/* ビューからの抽出 */ SELECT 顧客名,商品名,受注個数 FROM v_受注 WHERE 受注個数 >= 100 ;
実行結果
顧客名 商品名 受注個数 ──────────── ────── ─────── KUROKIYA JUICE 300 ONSIDE BEER 200 KUSHINANA BEER 150 ONSIDE WINE 110 KUROKIYA TEA 250 KUSHINANA BEER 135 KUROKIYA BEER 290 KUSHINANA BEER 175
実習課題 1
8章実習課題 1 で作成した SQL 文をもとに、ビューを作成しなさい。
- ビュー名は MonthCarsListView とする。
select * from MonthCarsListView;としたときに、8章実習課題 1 の結果と同じになるようにすればよい。
実習課題 2
8章の実習課題 2 で作成した SQL 文をもとに、ビューを作成しなさい。
- ビュー名は OfficeCheckView とする。
select * from OfficeCheckView;としたときに、8章の実習課題 2 の結果と同じになるようにすればよい。
実習課題 3
実習課題 1 で作成したビューを利用して、売上台数の最大、最小、平均を表示する SQL 文を 1文で記述しなさい。