SQL ビューとサブクエリ

ビューとは仮装のテーブル よく使うSELECT文を保存できる。 そして、仮想的なテーブルを作る。

ビューを作る

# as の後にselect文を記載する
create view soft_max (category, price_max)
as select category, MAX(price) from soft group by category; 

作成したビューを取得する

# fromのテーブルの名前の位置にビューの名前を指定する。
select * from soft_max;

# ビューに条件式を使う。
select * from soft_max where category = 'IDE';

ビューを一覧表示する

select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false));

ビューを削除する

drop view soft_max;

※ビューはテーブルの最新データを取得する。逆にビューを更新するとテーブルのデータも更新される。 ビュー定義にはorderbyは使用できない。

サブクエリ

# fromにselect文をいれる
select  category, max_price
from (select category, MAX(price) as max_price from soft group by category) as soft_max; 


# 階層が深いサブクエリ
select  category, max_price
from (select * from (select category, MAX(price) as max_price from soft group by category) 
as soft_max where max_price > 1000) as soft_max_filter; 

スカラサブクエリ

戻り値が単一になる

# where句によく使う
# (select ~)をスカラサブクエリという
select id, name, price from soft 
where price > (select avg(price) from soft);

# 定数としても使える
select id, name , price , (select min(price) from soft) as min_price from soft;

※複数行を返さないようにする

相関サブクエリ

スカラサブクエリでは一行しか返せないが、複数行返すことができる

# 各categoryごとの平均を超えるpriceをもつ行を返す
select id, name, price, category from soft as s1 
where price > (select avg(price) from soft 
as s2 where s1.category = s2.category group by category);