SQL ゼロから始めるデータベース操作 第5章複雑な問い合わせ VIEWについて
Oracleは放置(オイ)して、SQL本読書に戻ります。
今回はVIEWです。実は一度も作ったことがありません。
というよりクライアント側でVIEWに相当するものを作ってるんですよね。
DBMSのVIEWとはなにかをちゃんと勉強していきます。
今回のキーワード
CREATE VIEW
DROP VIEW
VIEWに対する更新
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2013/08/28
- メディア: Kindle版
- この商品を含むブログ (4件) を見る
サンプルテーブル
今回もこいつを使わせて頂きます。
テーブル名はSampleです。
更新で色々いじったせいでちょっとデータが変わってます。
id | Name | VALUE | Date | Category |
---|---|---|---|---|
0001 | 車 | 2000000 | 2009-11-25 | 移動手段 |
0002 | えんぴつ | 200 | 2009-11-27 | 文房具 |
0003 | キャビネット | 12500 | 2009-11-27 | 家具 |
0004 | 消しゴム | 12500 | 2009-12-11 | 文房具 |
0005 | バイク | 195000 | 2009-12-21 | 移動手段 |
0006 | 椅子 | 8500 | 2010-05-12 | 家具 |
0007 | ボールペン | 17000 | 2010-06-08 | 文房具 |
0008 | クレヨン | 1640 | 2010-06-15 | 文房具 |
0009 | ガレージ | 5282000 | 2010-09-01 | 未設定 |
0010 | MacBookPro | 0 | 2011-02-03 | 未設定 |
VIEWって何?
SELECT文
が保存されたもの。実際のデータは持たない。
プログラミング的に言えば、関数の定義
なのかな。
イメージはこんな感じです。
ACCESSを使ったことがある人なら、クエリのようなものと考えればわかりやすいです。
VIEWについて、本書では以下のように書かれています。
SQLの観点から見ると、ビューは「テーブルと同じもの」
これは、テーブルと同じようにFROM句でビューを指定できることから来ているのかな。
VIEWを使うメリット
毎日のデータチェック等でいちいちSELECT文を打つ手間が省けます。
データそのものは毎日更新されるので、チェックする人はVIEWを使うだけですぐにデータが見れるようになります。
簡単なSQL文ならいいですが、複雑なSQL文となると毎回打つのは手間ですし、打ち間違いで
ウガー!と叫ぶこともなくなります。
また、データをいちいち持つ必要がない(設計的には重複したデータがあっちこっちにあるのはそもそも良くない)ので、
記憶領域を圧迫しなくなります。
VIEWを使う時の注意点
パフォーマンスの低下
VIEWはテーブルと同じものとして考えられるので、
VIEWからVIEWを呼び出す事もできます。
こういうふうに、階層構造になったVIEWを多段VIEW
と呼びます。
VIEWはデータがあるわけではなく、1つ1つのVIEWでSELECT文が動くので、
階層構造が深くなれば、その分処理速度が低下してしまいます。
本書では、鉄則として
ビューにビューを重ねることは(なるべく)しない。
と書かれています。
VIEWの作成と使用・削除方法
VIEWの作成
以下の例では、V_sampleというビューを作成しています。
Categoryが'文房具'のものを表示するようにしています。
CREATE VIEW V_bunbogu ( id, Name, VALUE, Date, Category ) -> SELECT * FROM Sample -> WHERE Category='文房具'; Query OK, 0 rows affected (0.06 sec)
VIEWの名称、表示するフィールドのリストの後にASをつけて後は普段通りのSELECT文です。
ただし、ORDER BY
句は使えないことに注意。(行には順序がないというルールのため)
以下の例は、GROUP BY文を使うVIEWです。
Categoryごとにグループ化し、それぞれのカテゴリ名とVALUEの合計と平均値を表示します。
mysql> CREATE VIEW V_GrCategory ( Category, SUM, AVG ) AS -> SELECT Category, SUM(VALUE), AVG(VALUE) -> FROM Sample -> GROUP BY Category; Query OK, 0 rows affected (0.15 sec)
注意事項として、
VIEWではAS
が絶対に必要です。SELECT句で列の別名に使うAS
とは違うので要注意!
VIEWの使用
Tableと同じような感覚で使えます。
mysql> SELECT * FROM V_sample; +------+-----------------+-------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+-----------------+-------+------------+-----------+ | 0002 | えんぴつ | 200 | 2009-11-27 | 文房具 | | 0004 | 消しゴム | 20000 | 2009-12-11 | 文房具 | | 0007 | ボールペン | 17000 | 2010-06-08 | 文房具 | | 0008 | クレヨン | 1640 | 2010-06-15 | 文房具 | +------+-----------------+-------+------------+-----------+ 4 rows in set (0.02 sec)
カテゴリでグループに分けるVIEWもこの通り。
mysql> SELECT * FROM V_GrCategory; +--------------+---------+--------------+ | Category | SUM | AVG | +--------------+---------+--------------+ | 家具 | 21000 | 10500.0000 | | 文房具 | 38840 | 9710.0000 | | 未設定 | 5282000 | 2641000.0000 | | 移動手段 | 2195000 | 1097500.0000 | +--------------+---------+--------------+ 4 rows in set (0.01 sec)
また、VIEWではORDER BY
が使えないという制約があるので、
使うときにORDER BY
句を使うことで並び替えることになります。
(ORDER BYも含めて全部自動でやってくれるとなると、スクリプトが必要なのかな?)
mysql> SELECT * FROM V_GrCategory ORDER BY SUM DESC, AVG DESC; +--------------+---------+--------------+ | Category | SUM | AVG | +--------------+---------+--------------+ | 未設定 | 5282000 | 2641000.0000 | | 移動手段 | 2195000 | 1097500.0000 | | 文房具 | 38840 | 9710.0000 | | 家具 | 21000 | 10500.0000 | +--------------+---------+--------------+ 4 rows in set (0.01 sec)
VIEWの削除
TABLEと同様、DROPで削除できます。
DROP VIEW V_bunbogu;
VIEWに対する更新
VIEWに対しても、UPDATE
INSERT``DELETE
がかけられます。
ただし、以下の制限があります。
- SELECT句にDISTINCTが含まれていない
- FROM句に含まれるテーブルが1つだけである
- GROUP BY句を使用していない
本書では、HAVING句を使用していないというものもありましたが、
そもそもGROUP BY
を使わなければHAVING
は使えないので、除外しました。
一言でまとめると、データを纏める(集約)しているVIEWに対しては更新不可能ということですね。
できない理由は、集約されてしまうと、更新しようとしても集約された元データのどこを更新すればいいのかDBMSが判断できないからですね。
上の制限にかからないVIEWに対してなら、更新ができます。
例えば、先ほどの文房具だけ表示するV_bunbogu
ビューに対してなら更新できます。
mysql> INSERT INTO V_bunbogu VALUES ('0011', 'マジック', 50, '2012-03-01', '文房具'); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM V_sample; +------+-----------------+-------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+-----------------+-------+------------+-----------+ | 0002 | えんぴつ | 200 | 2009-11-27 | 文房具 | | 0004 | 消しゴム | 20000 | 2009-12-11 | 文房具 | | 0007 | ボールペン | 17000 | 2010-06-08 | 文房具 | | 0008 | クレヨン | 1640 | 2010-06-15 | 文房具 | | 0011 | マジック | 50 | 2012-03-01 | 文房具 | +------+-----------------+-------+------------+-----------+ 5 rows in set (0.00 sec)
ちょっと気になったので、V_bunboguに文房具カテゴリ以外のデータを追加してみます。
mysql> INSERT INTO V_sample VALUES ('0012', '一輪車', 2500, '2013-12-25', '移動手段'); Query OK, 1 row affected (0.01 sec)
できちゃった・・・
条件などは関係なく、VIEWを経由して元のテーブルに更新がかかる感じなのでしょうか。
更新処理後の元のテーブルを確認すると、
mysql> SELECT * FROM Sample; +------+--------------------+---------+------------+--------------+ | id | Name | VALUE | Date | Category | +------+--------------------+---------+------------+--------------+ | 0001 | 車 | 2000000 | 2009-11-25 | 移動手段 | | 0002 | えんぴつ | 200 | 2009-11-27 | 文房具 | | 0003 | キャビネット | 12500 | 2009-11-27 | 家具 | | 0004 | 消しゴム | 20000 | 2009-12-11 | 文房具 | | 0005 | バイク | 195000 | 2009-12-21 | 移動手段 | | 0006 | 椅子 | 8500 | 2010-05-12 | 家具 | | 0007 | ボールペン | 17000 | 2010-06-08 | 文房具 | | 0008 | クレヨン | 1640 | 2010-06-15 | 文房具 | | 0009 | ガレージ | 5282000 | 2010-09-01 | 未設定 | | 0010 | MacBookPro | 0 | 2011-02-03 | 未設定 | | 0011 | マジック | 50 | 2012-03-01 | 文房具 | | 0012 | 一輪車 | 2500 | 2013-12-25 | 移動手段 | +------+--------------------+---------+------------+--------------+
ちゃんとデータが追加されています。
まとめ
VIEWに対して更新というのが間違ってないかと思いました。
元に、PostgreSQL
はデフォルトではVIEWは読み取り専用です。
VIEWに対する更新は、テーブルに対する更新を使う場合に比べてどのようなメリット
があるのか、が気になります。
ググっても更新の説明ばかりでメリットについての説明はなかったので、ほとんどないのでしょうか。
現場で実際にVIEWに対する更新を使うシチュエーションってどんなのがあるんでしょうか。
個人的には、「やろうと思えばできるけどあんまり使わない方がいい」という印象です。
次回は、サブクエリ・相関サブクエリについてやっていきます。