MYSQL VIEWの確認

by


CREATE VIEW V_CITYN_COUNTRYN (CityName, CountryName)
AS SELECT City.Name, Country.Name FROM City, Country
WHERE City.CountryCode = Country.Code
AND City.CountryCode = 'JPN';

view_update_test


update V_CITYN_COUNTRYN SET CityName = 'Yokohama'
where CityName = 'Jokohama [Yokohama]';

update_view


update V_CITYN_COUNTRYN SET CityName = 'TOKYO',
CountryName = 'JAPAN'
where CityName = 'Tokyo'
AND CountryName = 'Japan';

上記UPDATE文は二つのテーブルからなるVIEWにて同時にそれぞれのテーブルを
更新しようとしている為エラーになっている。

ERROR 1393 (HY000): Can not modify more than one base table through a join view
tables_two

update_view_multiple

insert into V_CITYN_COUNTRYN values('Toukyou','Japan');
insert into V_CITYN_COUNTRYN(CityName,CountryName) values('Toukyou','Japan');

上記INSERTも2つのテーブルを同時にINSERTしようとしてエラーになっている。
(the view is insertable only if a single table is affected)

ERROR 1393 (HY000): Can not modify more than one base table through a join view ‘STUDY.V_CITYN_COUNTRYN’

view_insert_table

VIEW経由で一つのテーブルに対してINSERTしてみるとINSERTは出来たが、
VIEWは２つのテーブルをJOINして成り立っているのでVIEWをSELECTしてもINSERTしたデータは出てこない。

view_insert_single_table

VIEWは便利ですが、色々な制限があるので色々を参考になるサイトを読んでテストしてみた方がいいですね。
GROUP BY,COUNT,列+1などの関数を利用しているVIEWは他のRDBMS同様にINSERT、UPDATE出来ません。

以下のいずれかを含んでいるとビューは更新可能となりません。
——————————————————————————————
* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
*DISTINCT
*GROUP BY
*HAVING
*UNION もしくはUNION ALL
*選択リスト中のサブ・クエリ
その他………………………..
——————————————————————————————

(例）以下のVIEWは集計ファンクションやGroup byを使用しているので、更新処理は出来ません。
The view is not updatable because it uses aggregate functions and GROUP BY.


Create View V_Country_Area
(Continent,Total_Surface,Average_Surface)
as
select Continent,sum(SurfaceArea),avg(SurfaceArea)
from Country group by Continent;

view_group_by

またMYSQL5.1では以下のような固有の制限もあるようです。
ビューの FROM 句でサブクエリを使用することはできません。

    この制限はいずれ取り除かれる予定です。

参考サイト
D.4. ビューの規制
20.2. CREATE VIEW 構文

MYSQL異なったサブクエリーと同じ結果

by

以下のサブクエリーは異なったクエリーを実行しているが、結果は同じになります。

※　ともにそれぞれの大陸で1番大きい国を選んでます

例1)
以下の例は、同じテーブルを利用している訳ではなく
外部、サブクエリーの依存関係はありません。
サブクエリーだけでも成り立ちます。
しかしサブクエリーは独立していて、且つ複数の結果を
返すため　”=”だけでは成り立たない為、”= ANY”
を付けて複数のサブクエリーが返す結果に対応しています。


select Continent,Name,SurfaceArea from Country
where surfaceArea = ANY
(select max(SurfaceArea)
from Country group by Continent);

サブクエリーだけでも成り立ちます。しかし複数列を返すのでANYを利用しています。
mysql> select max(SurfaceArea) from Country group by Continent;
+——————+
| max(SurfaceArea) |
+——————+
| 9572900.00 |
| 17075400.00 |
| 9970610.00 |
| 2505813.00 |
| 7741220.00 |
| 13120000.00 |
| 8547403.00 |
+——————+
7 rows in set (0.00 sec)

mysql>

ANYを利用しないと以下のようエラーになります。

mysql> select Continent,Name,SurfaceArea from Country
-> where surfaceArea =
-> (select max(SurfaceArea)
-> from Country group by Continent);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql>

————————————————————————————————————

例2)
自己結合を用いている。
サブクエリーだけでは成り立たず、外部のQueryと結合して初めて答えが出る。

SELECT C.Continent, C.Name, C.SurfaceArea
FROM Country C
WHERE SurfaceArea =(
SELECT MAX(SurfaceArea)
FROM Country C2
WHERE C2.Continent = C.Continent);

サブクエリーだけだと以下のようにエラーになります。
mysql> SELECT MAX(SurfaceArea)
-> FROM Country C2
-> WHERE C2.Continent = C.Continent;
ERROR 1054 (42S22): Unknown column 'C.Continent' in 'where clause'
mysql>

sub_same

※　ともにそれぞれのCityテーブルから、町田を抽出しています。


SELECT Name,Population,District FROM City
WHERE (Name, District, CountryCode) = ('Machida', 'Tokyo-to', 'JPN');


SELECT Name,Population,District FROM City
WHERE CountryCode = 'JPN' and District ='Tokyo-to' and Name ='Machida';

subquery_same

実行プランはどちらも同じです。

execution_plan1

参考サイト
MYSQL サブクエリー(row constructors)

MYSQL Update View(集合関数、Group by)

by

大陸の名前、面積が入っているテーブルをベースに大陸毎の面積や各大陸に存在する
国の平均面積を算出するViewを作成。

下記2つの内容は同じViewになります。


Create View V_Surface
(NAME, SUM_Surface, AVG_Suface) AS
select continent, sum(SurfaceArea),avg(SurfaceArea)
from Country Group by Continent;


Create View V_Surface2 AS
select continent as NAME, sum(SurfaceArea) as SUM_Surface,
avg(SurfaceArea) as AVG_Suface from Country Group by Continent;

create_view_surface

上記のViewはSUMなどのAggregate funcation(集合関数)を利用していることGroup byを
利用しているので、更新することは出来ない。

view_update_fail

——————————————————————-
・データベース構造を意識させない。
・正規化によって分解された表を、Viewにて組み合わせて一つのテーブルのように操作可能。
・Tableへ直接の権限を付与しないでViewにて権限を制限する事でｾｷｭﾘﾃｨ強化
・複数の表からなるビューは更新できない。
・集合関数や演算を使用して作成したViewはUpdateできない。

GROUP BY ( WITH ROLLUP )

by

GROUP BY 句に WITH ROLLUP 修飾子を加えると、
クエリがすべてのデータにわたる総合計の値を示す行を生成します :

ROLLUP を使用する場合、ORDER BY 句を同時に使用して結果をソートすることはできません。
つまり、ROLLUP と ORDER BY は互いに排し合うということになります。
しかし、ソートの順番をいくらかコントロールすることは可能です。
MySQL の GROUP BY が結果をソートし、そして 明示的な ASC および DESC キーワードを
GROUP BY 内で名付けられたカラムと使用し、各カラムのソート順を指定することができます。
( しかし、ROLLUP によって加えられた高レベルな要約行は、ソート順に関わらず、
それらが計算された行の後に現れます。)

LIMIT はクライアントに戻される行の数を限定するのに使用できます。LIMIT は ROLLUP
の後に適用され、それによって ROLLUP によって追加された行に対しての制限が適用されます。


mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
+--------+-------+
4 rows in set (0.01 sec)

mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE WITH ROLLUP;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
| NULL | 168 |
+--------+-------+
5 rows in set (0.04 sec)

roll_up

11.11.2. GROUP BY 修飾子