PostgreSQL 編6 - ビュー作成、結合ビュー作成、ビュー削除、create view
■ビュー作成
?ビューの使い方で、データベースの使い心地とプログラムでのハンドリング
負荷が大きく変わる。
必要なビューを上手く設計して、プログラムコード負荷が少なくなるように
する。尚、ビューとは、仮想的な表、つまり、仮想テーブル、論理テーブル
みたいなものである。
?create view の as 句以降には、select 文の様々なパターンが指定できる。
たいへん強力である。
?単純ビューで、特定のデータを抜き出す指定は次のようにする。
この例では、testv1 は、キーの先頭が「a」で始まるものだけのテーブル
と同じにアクセスできるようになる。
尚、view に order by 句は指定できない。
create table testm (
keychar(8),
data1int8,
data2int8,
data3int8
);
insert into testm values ('a001', 1, 2, 3);
insert into testm values ('a011', 1, 2, 3);
insert into testm values ('b002', 10, 20, 30);
insert into testm values ('c003', 100, 200, 300);
create view testv1 as
select
key,
data1,
data2,
data3
from testm
where key like 'a%'
;
select * from testv1;
?特定の項目を見せたくないときは次のようにする。
表の分割のようなことにも応用できる。
この例では、testv2 には、data2 と言う項目がなくなる。
create view testv2 as
select
key,
data1,
data3
from testm
;
select * from testv2;
?項目名を変更する場合は、次のようにする。
この例では、testv3 には、data1 と言う項目名が「abc」と言う項目名に
変更されている。
create view testv3 as
select
key,
data1 as abc,
data3
from testm
;
select * from testv3;
?計算項目を作る場合は、次のようにする。
この例では、data1 + data2 の合計が「total」と言う項目名で追加される。
様々な SQL 関数を用いた計算項目が作成できる。
create view testv4 as
select
key,
data1,
data2,
data3,
data1 + data2 as total
from testm
;
select * from testv4;
?文字列の結合項目を作る場合は、次のようにする。
この例では、data1 と data2 の結合文字が「catstr」と言う項目名で追加
される。SQL 関数を用いたフォーマットなども可能である。
create view testv5 as
select
key,
data1,
data2,
data3,
data1 || data2 as catstr
from testm
;
select * from testv5;
■結合ビュー作成
?単純ビューでの様々な指定を含みながら、複数のテーブルやビューをひとつ
のビューに結合することができる。
結合する条件が必要なので、なんでもかんでも結合できるわけではない。
リレーショナルデータベースのゆえんは、この機能にある。
次のテーブルがあったとする。test2m の code1 は、testm の key に対応
しているものとする。
create table test2m (
keychar(8),
code1char(8)
);
insert into test2m values ('abc01', 'a001');
insert into test2m values ('abc02', 'a011');
insert into test2m values ('abc03', 'z999');
create table testm (
keychar(8),
data1int8,
data2int8,
data3int8
);
insert into testm values ('a001', 1, 2, 3);
insert into testm values ('a011', 1, 2, 3);
insert into testm values ('b002', 10, 20, 30);
insert into testm values ('c003', 100, 200, 300);
?test2m の code1 に testm の key を結合(ジョイン join) する場合、
次のようにする。
この例は、クロスジョイン(cross join)である。
create view testv6 as
select
test2m.key as key,
code1as code1,
testm.keyas bkey,
data1as data1,
data2as data2,
data3as data3
from test2m, testm
where test2m.code1 = testm.key
;
select * from testv6;
?たくさんのテーブルやビューを結合する場合、修飾が見にくく難解になって
くる。その場合、次のようにする。
from 句でテーブルやビューの一時名を指定して、それで修飾する。
上手く使うと、すっきりした SQL 文にできる。
「,」で区切るのと「cross join」と書くのは同じである。
create view testv7 as
select
a.key as key,
code1as code1,
b.keyas bkey,
data1as data1,
data2as data2,
data3as data3
from test2m a, testm b
where a.code1 = b.key
;
create view testv8 as
select
a.key as key,
code1as code1,
b.keyas bkey,
data1as data1,
data2as data2,
data3as data3
from test2m a cross join testm b
where a.code1 = b.key
;
select * from testv8;
key|code1 | bkey | data1 | data2 | data3
----------+----------+----------+-------+-------+------
abc01| a001 | a001 | 1 | 2 | 3
abc02| a011 | a011 | 1 | 2 | 3
?実際には、left join を使用する場合が多い。
古い PostgreSQL では、cross join 以外使えないが、最近のバージョンは
問題なく使える。
create view testv9 as
select
a.key as key,
code1as code1,
b.keyas bkey,
data1as data1,
data2as data2,
data3as data3
from test2m a left join testm b on a.code1 = b.key
;
select * from testv9;
key|code1 | bkey | data1 | data2 | data3
----------+----------+----------+-------+-------+------
abc01| a001 | a001 | 1 | 2 | 3
abc02| a011 | a011 | 1 | 2 | 3
abc03| z999 |
| | |
■ビュー削除
?ビューを削除しても、実際のテーブルデータが消えることはない。
drop view testv1;
drop view testv2;
drop view testv3;
drop view testv4;
drop view testv5;
drop view testv6;
drop view testv7;
drop view testv8;
drop view testv9;
|
