【SQL】ビュー：SELECT文だけを保存する
ビューとは
SELECT文だけを保存するので、記憶装置の容量を節約できる- 頻繁に使う
SELECT文を保存できるので、使い回しがきく - ビューが含むデータは自動的に最新の状態に更新される（「ビューを参照 ＝
SELECT文を実行」のため）
| 保存されるもの | |
|---|---|
| データベース | データ（PC内の記憶装置） |
| ビュー | データではなく、SELECT文だけが保存 |
CREATE VIEW
CREATE VIEW ShohinSum (
/* ビューの列名 */
shohin_bunrui, cnt_shohin)
AS
/* ビュー定義の本体(ただのSELECT文) */
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui;
ビューを使う
/* FROM句のあとにビューを指定する */
SELECT shohin_bunrui, cnt_shohin FROM ShohinSum;
shohin_bunrui | cnt_shohin
---------------+------------
キッチン用品 | 4
衣服 | 2
事務用品 | 2
ビューに対する検索
2段階を踏む
- ビューに定義された
SELECT文が実行される - 1.の結果に対して、ビューを
FROM句に指定したSELECT文が実行される
ビューを元にさらにビューを作る
- 通称「多段ビュー」は、DBMSに負荷がかかりパフォーマンスの低下を招く
- 依存関係が絡まり、削除や更新がしにくくなる
ビューの上にビューを重ねることは「なるべく」しないこと
/* ShohinSumビューから、
ShohinSumJimビューを作ってみる */
CREATE VIEW ShohinSumJim (
shohin_bunrui, cnt_shohin ) AS
SELECT shohin_bunrui, cnt_shohin
/* ビューからビューを作る */
FROM ShohinSum
WHERE shohin_bunrui = '事務用品';
SELECT shohin_bunrui, cnt_shohin
FROM ShohinSumJim;
shohin_bunrui | cnt_shohin
--------------+------------
事務用品 | 2
ORDER BYは使えない
CREATE VIEWするとき、どんなSELECT文でも書けるが、ORDER BY句だけは使えない- 作成したビューを使うときに
ORDER BY句で並び替えること
/* NG ビューを作るときに並べる */
CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin) AS
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui
ORDER BY COUNT(*) DESC; /* ← ここには書かない */
/* OK ビューを呼ぶときに並べる */
SELECT * FROM ShohinSum
ORDER BY cnt_shohin DESC; /* ← こっちに書く */
CREATE文を書くときにはつけないSELECT文には付けたほうが見やすい
ビューに対する更新
ビュー定義のSELECT文において、いくつかの条件を満たしている場合、ビューに対する更新（INSERT, DELETE, UPDATE）が可能
SELECT句にDISTINCTが含まれていないFROM句に含まれているテーブルが1つだけであるGROUP BY句を使用していないHAVING句を使用していない
集約は一方通行
/* NG ShohinSumにINSERTしてみる */
INSERT INTO ShohinSum VALUES('電化製品', 5);
/* エラーになる */
ERROR: cannot insert into view "shohinsum"
DETAIL: Views containing GROUP BY are not automatically updatable.
HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger
or an unconditional ON INSERT DO INSTEAD rule.
/* エラー内容
ビュー "shohinsum" に挿入できません
詳細:GROUP BY を含むビューは、自動更新可能ではありません
ヒント:このビューへの挿入を可能にするには、INSTEAD OF INSERT トリガー、または無条件の ON INSERT DO INSTEAD ルールを用意してください */
-
GROUP BYで1行にまとめたビューを作る -
ビューを更新しようとしても、元のテーブルが更新できない
「電化製品が5件」と言われても…
→ 元のShohinテーブルに、どんな行を5つ作ればいいの？
→ 商品IDは？商品名は？販売単価は？
→ 判断できない！ -
ビューと元のテーブルの整合性がとれてないとダメ（ビューとテーブルの更新は連動して行われるもの）
-
更新できないので上記の
INSERT文はエラーになる
集約なしなら更新できる
CREATE VIEW ShohinJim (
shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi
)
AS
SELECT * FROM Shohin
WHERE shohin_bunrui = '事務用品';
SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi
FROM ShohinJim;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+--------------+---------------+--------------+--------------+------------
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
フィルタで'事務用品'だけ抜き出していて、集約がされていないビュー
INSERT INTO ShohinJim VALUES (
'0009', '印鑑', '事務用品', 95, 10, '2009-11-30'
);
SELECT * FROM ShohinJim;
/* エラーなく更新できた */
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+--------------+---------------+--------------+--------------+------------
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
0009 | 印鑑 | 事務用品 | 95 | 10 | 2009-11-30
元のテーブルも確認してみる↓
SELECT * FROM Shohin ORDER BY shohin_id ASC;
/* 元のテーブルにも'0009'が追加されている */
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
0004 | 包丁 | キッチン用品 | 3000 | 2800 | 2009-09-20
0005 | 圧力鍋 | キッチン用品 | 6800 | 5000 | 2009-01-15
0006 | フォーク | キッチン用品 | 500 | | 2009-09-20
0007 | おろしがね | キッチン用品 | 880 | 790 | 2008-04-28
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
0009 | 印鑑 | 事務用品 | 95 | 10 | 2009-11-30
DROP VIEW
ビューを削除する
DROP VIEW ShohinSum;
/* ここで、多段ビューになっているためエラーが出る */
ERROR: cannot drop view shohinsum because other objects depend on it
DETAIL: view shohinsumjim depends on view shohinsum
HINT: Use DROP ... CASCADE to drop the dependent objects too.
/* エラー内容
ビュー shohinsum を削除できません。他のオブジェクトがそれに依存しているためです
詳細:ビュー shohinsumjim が、ビュー shohinsum に依存しています
ヒント:依存しているオブジェクトも一緒に削除するには、DROP ... CASCADE を使ってください */
先に、依存関係になっている別のビューを削除する
/* 依存関係になっているビュー */
DROP VIEW ShohinSumJim;
/* 残りのビューも削除できた */
DROP VIEW ShohinSum;
DROP VIEW ShohinJim;
【要注意】CASCADEで一気に削除できるけど・・・
CASCADEは自分が知らない依存関係まで巻き込んで削除する
削除後、なにが消えたか教えてくれる（事後報告）
依存関係を調べてひとつずつ慎重に削除していくこと
【最後に】追加した行を削除しておく
/* 追加した'0009'を削除する */
DELETE FROM Shohin WHERE shohin_id = '0009';
SELECT * FROM Shohin ORDER BY shohin_id ASC;
/* テーブルが元通りになった */
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
0004 | 包丁 | キッチン用品 | 3000 | 2800 | 2009-09-20
0005 | 圧力鍋 | キッチン用品 | 6800 | 5000 | 2009-01-15
0006 | フォーク | キッチン用品 | 500 | | 2009-09-20
0007 | おろしがね | キッチン用品 | 880 | 790 | 2008-04-28
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
関連記事：【SQL】サブクエリ：使い捨てのビュー
参考書籍：ミック『SQL 第2版 ゼロからはじめるデータベース操作』（翔泳社）