SQL ビュー、サブクエリ

こちらの記事では、私が学んだSQLについて記載しております。
自身の忘れないためのメモとしております。
間違っている部分もあるかもしれませんので、ご了承ください。

#ビューとテーブルの違い
#####・テーブル・・・実際のデータを保存
#####・ビュー・・・ビューの中にselect文が保存され、ビュー自体はデータを持たない！！！
メリット・・・データを保存しないので、記憶装置の容量を節約できる。select文の使い回しが可能。

デメリット・・・パフォーマンス低下を招く場合がある。

create view prefecture_user_counts(prefecture_user_countsname,count)
as
select
	p.name name,
    count(*) count
from
	users u
inner join
	prefectures p
	on u.prefecture_id = p.id
group by u.prefecture_id
;

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
***ビューでの出力***
select
	name,
    count
from
	prefecture_user_counts;

#ビューの制限事項
・order byは使用できない！
・更新系　insert,delete.updateに制約がある！
・いくつかの条件を満たす場合のみ、ビューに対する更新が可能！
　ーselectにdistinctが含まれていない
　ーfromに含まれているテーブルは１つだけ
　ーgroup byを使用していない
　ーhavingを使用していない
*ビューとテーブルの更新は連動して行われるため、集約されたビューは更新不可。

#ビューの削除

・記述方法
drop view ビュー名

#サブクエリ
####ある問合せの結果に基づいて、異なる問合せを行う仕組み。複雑な問合せが可能になる。

***not in　演算子***
select 
	id,
    last_name,
    email
from 
	users u
where id not in(     ←サブクエリ
select
	user_id
from
	orders
where
	order_time >= '2017-12-01 00:00:00'
    and order_time < '2018-01-01 00:00:00'
);

---------------------------------------------------------------------
***in　演算子***
select
	id,
    last_name,
    email
from 
	users
where id in (　　　←サブクエリ
select
	user_id
from
	orders
where 
	order_time >= '2017-12-01 00:00:00'
    and order_time < '2018-01-01 00:00:00'
);

#スカラ・サブクエリ
必ず１行１列だけの戻り値を返す、サブクエリのこと
スカラとは、単一のと言う意味。絶対にサブクエリが複数行を返さないようにする

select
*
from
	products
where 
	price > (
    select
		avg(price)
	from 
		products
        )
order by price desc, id;
;
    

#条件式　caseを使用

記述
case
 when 条件式1 then 値１　　　条件式１がTrueなら値１
 [when 条件式２ then 値２]　　条件式２がTrueなら値２
 [eles 値３]　　　　　　　　　どれも当てはまらない値３
end

[]部分は省略可能。最後のendは省略不可。

select
	u.id user_id,
    u.last_name,
    count(*) num,
    case
		when count(*) >= 5 then 'A'
        when count(*) >= 2 then 'B'
        else 'C'
    end as user_rank
from
	users u
inner join
	orders o
on u.id = o.user_id
group by u.id
order by user_rank
;

#取得値nullの場合は、0で置き換える

select
	p.id,
    p.name,
    case
    when sum(od.product_qty) is null then 0
    else sum(od.product_qty)
    end as num
from
	products p
left outer join
	order_details od
	on p.id = od.product_id
group by p.id;

#補足
####date_format　

・記述方法
date_format（カラム,'%Y%m'）　年月でデータを取得する！

select
	date_format(order_time, '%Y%m') 年月,
    round(avg(amount), 0) 平均客単価
from
	orders
group by 
	date_format(order_time, '%Y%m')
;

##データ利用方法
###データをCSVファイルに書き出す
export recordset to an external fileというボタンから作成可能です！
フロッピーのアイコンが目標になります！

###CSVファイルに書き出した後に、「Excel」にCSVファイルを取り込むことが可能になります。
-ExcelにCSVファイルを入れる方法！
Excelを開き、ファイルをクリックします。インポートをクリックし、アップロードから作成したCSVファイルを選択することでExcelにデータを入れることができます。