ひとり勉強ログ

ITエンジニアの勉強したことメモ

MySQL講座#13 ビュー

ビューとテーブル

まず最初に習得する新しい道具は「ビュー」。

このビュートはいったい何か。一言で説明すると「SQLの観点から見ると“テーブルと同じもの”」である。実際、SQL文の中で、テーブルなのかビューなのかを意識する必要はほとんどない。実は更新の場合だけは違いを意識する必要があるが、、これはまた後述する。少なくともSELECT文を組み立てる際には、テーブルとビューの違いは気にしなくて構わない。

では、ビューとテーブルの違いはどこにあるのか。それはただ1つ、「実際のデータを保存しているか否か」である。

通常、テーブルを作り、INSERT文でデータを格納すると、データベースにデータが保存できる。そして、このデータベースのデータが実際に保存されるのはどこかというと、コンピュータ内の記憶装置である。したがって、SELECT文でデータ検索しようとするときは、実際にはこの記憶装置(ハードディスク)からデータを引っ張り出して、いろいろな計算を行い、ユーザに返す、という過程をたどる。

一方、ビューの場合、データを記憶装置に保存しない。ではどこにデータを保存しているかというと、ビューの場合、どこにも保存していない。ビューが保存しているのは「SELECT文そのもの」なのである。ビューからデータを取り出そうとするときに、ビューは内部的にそのSELECT文を実行し、一時的に仮想のテーブルを作る。

ビューのメリット

ビューのメリットは大きく分けて2つ。

1つ目は、データを保存しないため、記憶装置の容量を節約できること。例えば、以前にcategoryごと集約したテーブルを作ったが、このテーブルに含まれるデータも、結局のところ記憶装置に保存されるため、その分、記憶装置のデータ領域を消費する。しかし、これと同じデータをビューとして保存するならば、SELECT文だけを保存すれば良いので、記憶装置のデータ領域を節約できる。

2つ目のメリットは、頻繁に使うSELECT文を、いちいち毎回書かなくても、ビューとして保存しておくことで使いまわしがきくことである。一度ビューを作っておけば、後はそれを呼び出すだけで、簡単にSELECT文の結果を得ることができる。集計や条件が複雑で本体のSELECT文が大きくなればなるほど、ビューによる効率化の恩恵は大きなものとなる。

しかも、ビューが含むデータは、元のテーブルと連動して自動的に最新の状態に更新される。ビューは結局のところ「SELECT文」なので、「ビューを参照する」とは「そのSELECT文を実行する」ということである。なので、最新状態のデータを選択できるのである。これは、データをテーブルとして保存した場合にはない利点である。

ビューの作り方

ビューを作成するには、CREATE VIEW文を使用する。

CREATE VIEW ビュー名 (<ビューの列名1>, <ビューの列名2>,・・・)
AS
<SELECT文>

ASキーワードの後にはSELECT文を記述する。SELECT文の列とビューの列は並び順で一致し、SELECT文の最初の列はビューの1番目の列、SELECT文の2番目の列はビューの2番目の列、になる。ビューの列名は、ビュー名の後ろのリストで定義する。

では試しにビューを作ってみる。

CREATE VIEW CategorySum (category, cnt_chojin)
AS
SELECT category, COUNT(*)
FROM chojin
GROUP BY category;

これでCategorySum(カテゴリー合計)という名前のビューが1つ、データベース内に作られた。2行目のキーワード「AS」は絶対に省略してはいけない。ここでのASは、列名やテーブル名に別名をつけるときに使ったASとは異なる。省略するとエラーになるが、構文でそう決められているので、覚えておく必要がある。

ビューの使い方は、テーブルと同じく、SELECT文のFROM句に書くことができる。

SELECT category, cnt_chojin
FROM CategorySum;

結果

+--------------+------------+
| category     | cnt_chojin |
+--------------+------------+
| 完璧超人     |         22 |
| 悪魔超人     |         13 |
| 正義超人     |          9 |
+--------------+------------+

ビューの制限事項① ビュー定義でORDER BY句は使えない

「ビュー定義にはどんなSELECT文も書くことができる」と説明したが、1つだけ例外があり、ORDER BY句だけは使えない。したがって、次のようなビュー定義文は認められていない。

CREATE VIEW CategorySum (category, cnt_chojin)
AS
SELECT category, COUNT(*)
FROM chojin
GROUP BY category
ORDER BY category;

なぜORDER BY句をつかってはいけないかと言う理由は、テーブルと同様、ビューについても「行には順序がない」と定められているからである。

ビューの制限事項② ビューに対する更新

これまで、SELECT文の中では、ビューをテーブルとまったく同様に扱うことが可能だと述べた。それでは、INSERT、DELETE、UPDATEといった更新系SQLにおいては、どうだろうか。

実は、かなり厳しい制限付きではあるが、ビューに対する更新が可能な場合がある。標準SQLでは、

「ビュー定義のSELECT文において、いくつかの条件を満たしている場合、ビューに対する更新が可能」

と定められている。代表的な条件を挙げると、次の通り。

①SELECT句にDISTINCTが含まれていない
②FROM句に含まれるテーブルが1つだけ
③GROUP BY句を使用していない
④HAVING句を使用していない

①、③、④は集約に関するものである。平たく言ってしまうと、今回のCategorySumのように、ビューが元のテーブルを集約した結果を保持している場合、ビューでの変更を元のテーブルにどう反映すればよいのか判断できないのである。

ビューを削除する

ビューを削除するにはDROP VIEWを使う。

例えば、CategorySumビューを削除するなら、以下のように記述する。

DROP VIEW CategorySum;

ビューの応用

CREATE VIEW ChojinHealth (name, category, height, weight, power)
AS
SELECT name, category, height, weight, power
FROM chojin;

ビューの内容を表示。

SELECT * FROM ChojinHealth;

結果

+--------------------------------+--------------+--------+--------+-------+
| name                           | category     | height | weight | power |
+--------------------------------+--------------+--------+--------+-------+
| キン肉マン                     | 正義超人     |    185 |     90 |    95 |
| テリーマン                     | 正義超人     |    190 |     95 |    95 |
| ロビンマスク                   | 正義超人     |    217 |    155 |    96 |
| ウォーズマン                   | 正義超人     |    210 |    150 |   100 |
| ラーメンマン                   | 正義超人     |    209 |    130 |    97 |
| ブロッケンJr                 | 正義超人     |    195 |     90 |    90 |
| ウルフマン                     | 正義超人     |    190 |    102 |    80 |
| ステカセキング                 | 悪魔超人     |    214 |    700 |   250 |
| ブラックホール                 | 悪魔超人     |    201 |    420 |   200 |
以下省略

catgoryごとに、平均身長、最長身長、最小身長、平均体重、最大体重、最小体重を選択する。

SELECT category,
AVG(height) AS 平均身長, MAX(height) AS 最長身長, MIN(height) AS 最小身長,
AVG(weight) AS 平均体重, MAX(weight) AS 最大体重, MIN(weight) AS 最小体重
FROM ChojinHealth
WHERE height IS NOT NULL
GROUP BY category;

結果

+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| category     | 平均身長     | 最長身長     | 最小身長     | 平均体重     | 最大体重     | 最小体重     |
+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| 完璧超人     |     230.6364 |          302 |          178 |     270.6364 |          880 |           95 |
| 悪魔超人     |     215.4615 |          300 |          104 |     403.8462 |         1000 |          115 |
| 正義超人     |     199.4286 |          217 |          185 |     116.0000 |          155 |           90 |
+--------------+--------------+--------------+--------------+--------------+--------------+--------------+

身長階級表を作成する。

CREATE TABLE heightLevel
(id CHAR(4) NOT NULL,
level VARCHAR(20) NOT NULL,
min INTEGER NOT NULL,
max INTEGER NOT NULL,
PRIMARY KEY (id)
);

階級データを挿入する。

START TRANSACTION;
INSERT INTO heightLevel (id, level, min, max) VALUES('0001', '190cm未満', '0.0', '189');
INSERT INTO heightLevel (id, level, min, max) VALUES('0002', '190cm以上200cm未満', '190', '199');
INSERT INTO heightLevel (id, level, min, max) VALUES('0003', '200cm以上210cm未満', '200', '209');
INSERT INTO heightLevel (id, level, min, max) VALUES('0004', '210cm以上220cm未満', '210', '219');
INSERT INTO heightLevel (id, level, min, max) VALUES('0005', '220cm以上230cm未満', '220', '229');
INSERT INTO heightLevel (id, level, min, max) VALUES('0006', '230cm以上240cm未満', '230', '239');
INSERT INTO heightLevel (id, level, min, max) VALUES('0007', '240cm以上250cm未満', '240', '249');
INSERT INTO heightLevel (id, level, min, max) VALUES('0008', '250cm以上260cm未満', '250', '259');
INSERT INTO heightLevel (id, level, min, max) VALUES('0009', '260cm以上270cm未満', '260', '269');
INSERT INTO heightLevel (id, level, min, max) VALUES('0009', '270cm以上280cm未満', '270', '279');
INSERT INTO heightLevel (id, level, min, max) VALUES('0010', '280cm以上290cm未満', '280', '289');
INSERT INTO heightLevel (id, level, min, max) VALUES('0011', '290cm以上300cm未満', '290', '299');
INSERT INTO heightLevel (id, level, min, max) VALUES('0012', '300cm以上310cm未満', '300', '309');
COMMIT;
select * from heightLevel;

結果

+------+------------------------+-----+-----+
| id   | level                  | min | max |
+------+------------------------+-----+-----+
| 0001 | 190cm未満              |   0 | 189 |
| 0002 | 190cm以上200cm未満     | 190 | 199 |
| 0003 | 200cm以上210cm未満     | 200 | 209 |
| 0004 | 210cm以上220cm未満     | 210 | 219 |
| 0005 | 220cm以上230cm未満     | 220 | 229 |
| 0006 | 230cm以上240cm未満     | 230 | 239 |
| 0007 | 240cm以上250cm未満     | 240 | 249 |
| 0008 | 250cm以上260cm未満     | 250 | 259 |
| 0009 | 260cm以上270cm未満     | 260 | 269 |
| 0010 | 280cm以上290cm未満     | 280 | 289 |
| 0011 | 290cm以上300cm未満     | 290 | 299 |
| 0012 | 300cm以上310cm未満     | 300 | 309 |
+------+------------------------+-----+-----+

身長階級ごとの超人の数をカウントする。

SELECT level, COUNT(*) AS num
FROM ChojinHealth RIGHT OUTER JOIN heightLevel ON
height BETWEEN min AND max
GROUP BY level, min, max
ORDER BY min ASC;

結果

+------------------------+-----+
| level                  | num |
+------------------------+-----+
| 190cm未満              |   3 |
| 190cm以上200cm未満     |   9 |
| 200cm以上210cm未満     |  11 |
| 210cm以上220cm未満     |   5 |
| 220cm以上230cm未満     |   2 |
| 230cm以上240cm未満     |   1 |
| 240cm以上250cm未満     |   1 |
| 250cm以上260cm未満     |   3 |
| 260cm以上270cm未満     |   2 |
| 280cm以上290cm未満     |   2 |
| 290cm以上300cm未満     |   2 |
| 300cm以上310cm未満     |   2 |
+------------------------+-----+

「COUNT(*)」とすると、「230cm以上240cm未満」「240cm以上250cm未満」の超人が存在しないにもかかわらず、heightLevelの「230cm以上240cm未満」「240cm以上250cm未満」がNULLとなっているレコードまで出力され、カウントされてしまっている。

正しくは、COUNT(height)としなければならない。

SELECT level, COUNT(height) AS num
FROM ChojinHealth RIGHT OUTER JOIN heightLevel ON
height BETWEEN min AND max
GROUP BY level, min, max
ORDER BY min ASC;

結果

+------------------------+-----+
| level                  | num |
+------------------------+-----+
| 190cm未満              |   3 |
| 190cm以上200cm未満     |   9 |
| 200cm以上210cm未満     |  11 |
| 210cm以上220cm未満     |   5 |
| 220cm以上230cm未満     |   2 |
| 230cm以上240cm未満     |   0 |
| 240cm以上250cm未満     |   0 |
| 250cm以上260cm未満     |   3 |
| 260cm以上270cm未満     |   2 |
| 280cm以上290cm未満     |   2 |
| 290cm以上300cm未満     |   2 |
| 300cm以上310cm未満     |   2 |
+------------------------+-----+