個人的なPostgreSQLまとめ

##はじめに
未来電子テクノロジー(https://www.miraidenshi-tech.jp/intern-content/program/)
でインターンをしているrayaと申します。
プログラミング初心者であるため、内容に誤りがあるかもしれません。
もし、誤りがあれば修正するのでどんどん指摘してください。

##今回の内容
Djangoのデータベース設計で使ったPostgreSQLについて学び、頭に残りにくかったものを主にまとめています。
環境はmac、postgresql11.5 です。

##PostgreSQLとは
データベースを管理するためのソフトウェアです。
様々なソフトウェアがある中でフリー利用が可能なものの１つです。
データベースを表の形で管理します。

##PostgreSQLに接続する
``psql -h ホスト -p ポート -U ロール名　-d　データベース名``
ユーザーやグループ名のことをロールという。
接続を終了するには``\q``

##データベース、スキーマ、テーブル
データベース＞スキーマ＞テーブル
データベースの中にスキーマがあり、さらにスキーマの中にテーブル、関数がある。
データベースを作成するとpublicスキーマがデフォルトで作られる。
同じスキーマ内ならば同じテーブル名は設定できない

##オプション、コマンド
``\?`` : 一覧表示
``\i`` : 保存したファイルを読み込む　
``\|`` : データベース一覧を表示
``\c データベース名`` : データベースを変更
``\dn`` : スキーマ一覧を表示
``\dt`` : テーブル一覧を表示
``\d テーブル名``　: テーブルのカラム情報を表示
``\du`` : ロール一覧を表示

##sql文
``;``を打つまでは継続して入力できる。
``\r``で途中キャンセル。

1. データベース
・データベースの設定変更　
``alter database データベース名 設定名``
変えられる設定は、``allow_connections``, ``connection limit``, ``is_template``
・データベースの名前変更　
``alter database データベース名（元） rename to データベース名（新）``
・データベースの所有者変更　
``alter database データベース名 owner to 所有者（新）``
・データベースの削除　
``drop database データベース名``

2. ロール
・ロール作成　
``create role ロール名 with オプション名``
オプションで``superuser/nosuperuser`` スーパーユーザーか否か。デフォルトはnosuperuser
``createdb / nocreatedb`` データベースを作成できるロールか否か
``createrole / nocreaterole`` ロールを作成できるロールか否か
・ロールのオプション変更　
``alter role ロール名 with オプション（新）``
・ある対象（テーブルやスキーマ）に対する権限をロールに追加する 
``grant 権限 on 対象名 to ロール名``
対象名のところがテーブルなら``テーブル名``を、データベースやスキーマなら``database データベース名``、``schema スキーマ名``
・ある対象に対する権限をロールから削除する
``revoke 権限 on 対象名 from ロール名``
あるロールが別のロールに対して権限を与えている場合は、それらを全て削除するために``cascade``を最後に指定する 

3. テーブル
・テーブル作成時の定義を取得する
``pg_dump -U ロール名 -t テーブル名 -s データベース名``
・テーブルを削除する
``drop table テーブル名 オプション名``
オプションには``cascade``と``restrict``がある。デフォルトはrestrict。そのテーブルが他のテーブルやビューと紐づけられている場合は、cascadeを指定しなければ削除できない。
・一時的なテーブルを作る
``create temp テーブル名（カラム名　データ型, ...）``
このテーブルは特殊なスキーマ内に格納されてセッションが終了すると勝手に削除される
・テーブルを継承する
``create table 子テーブル(...) inherits(親テーブル)``
継承では、親と子は連動しているので、親に対する変更は子にも適用される。制約も原則継承される。複数の親からも継承可能。
・テーブルをコピーする
``create table テーブル名(...) like コピー元テーブル名``
継承とは異なり、全く連動しない構造が同じだけのテーブルを作る。オプションで設定すれば制約もコピーできる
・テーブルのスキーマを変更する
``alter table テーブル名 set schema 移動先スキーマ``

4.ビュー
・ビューを作成する
ビューとは特定の条件でテーブル同士を結合した結果を通常のテーブルのように扱えるようにしたもの。
毎回結合して値を取り出すのが面倒な場合に使用する
``create view ビュー名 as select カラム名1,... from テーブル名,... where 条件式``
``select * from ビュー名``



##各種ファイル設定
・postgresql.conf
=>基本設定
・pg_hba.conf
=>接続するクライアント認証
・pg_ident.conf
=>接続するクライアント認証をする上で、そのクライアントをマッピング（クライアントに対して別の名前を割り当てるなど）する

##識別子とキーワード
キーワード：PostgreSQLの文法（selectなど）
識別子：テーブル名などオリジナルの名前
基本的にキーワードは識別子に使用できないが、””で囲えば使用可能

##演算子
累乗　2^3 -> 8
平方根　|/16  -> 4
立方根　||/27 ->3
階乗　5! ->120
絶対値　@-5 ->5 
XがAからBの範囲にある　X between A and B 
Xの値がNULL　X isnull
Xの値がNULLでない　X notnull

##テーブルの制約
テーブル作成時に制約を設定できる。
``カラム名 データ型 制約``とするか最後にまとめて``制約（カラム名, ..）``と設定できる場合がある。
・``default デフォルト値`` : カラムのデフォルト値を設定
・``not null`` : nullを許可しない
・``check 条件式`` : 条件式を満たすデータなら追加できる
・``unique`` : 重複した値を許可しない。nullは重複にならない。カラムごとに設定するのみならず、テーブル全体に指定することも可能。
・``primary key`` : unique + not null + αのような制約。重複した値とnullを格納できなくなる。
primary key制約はテーブルに１つしか設定できない。primary key(col1, col2)とするとcol1,col2の組み合わせの値が重複するのを許可しなくなる。
・``foreign key (制約するカラム名) references テーブル名(参照するカラム名)``: 指定した別のテーブルのデータしか格納できなくなる。
``on delete cascade``、``on delete update``オプションが使用できる

##トランザクション
データベースを更新する複数の手続きを１つにまとめて扱うこと。それらの手続きが全て完結すれば手続き完了でデータベースを更新し保存する。
一方、途中でトラブルが起き、手続きが中断した場合、それらはなかったことになり保存されない。

##関数
・集約関数
複数の行のデータから計算結果を出す。
sum(), avg(), max(), min()など。
``select max(カラム名) from テーブル名``
集約関数はwhereの条件式に直接入れることはできないので、以下のようにする。
``select カラム名 from テーブル名 where カラム名=(select max(カラム名) from テーブル名)``
どのカラムを元に行のデータを計算するかは``group by カラム名``で指定できる。
group byを使用する際、表示する結果に条件を追加したい場合は``having 条件式``を使う。


・ウィンドウ関数
集約関数と似ているが、異なる点はウィンドウ関数の場合１つの行の値だけでグループ化しない点がある。
あるカラムの中の複数の行の値ごとにグループ化して計算ができる。
``select sum(カラム名1) over (partition by カラム名2) from テーブル名``
この場合、カラム1の値の合計をカラム2内の行の値ごとに分けて出力する。
例えばカラム１が社員の給料のデータ、カラム２が会社の各部署名のデータとすると、給料の合計を各部署名ごとにグループ化して計算できる。

・JSON関数
PoatgreSQLは表の形でデータを表すが、複雑なデータの場合は表に収まらない場合がある。
その場合に、少しでも人がデータを読みやすくなるようにデータを表現する方法をJSONという。
JSONのデータ型には大きく以下の型がある。
``json型``:データのコピーを作成して格納。そのコピーを再解析して関数を実行する
``jsonb型``:バイナリ形式に変換してデータを格納。再解析が不要なので処理が早い。データの順序や意味のない空白を無視する。
JSON形式('データ'::json)で記述したデータに対して演算子を用いて操作する。
特定の値がJSONで書かれたデータの中に含まれているかを判断したり、特定の値を取り出すことなどができる。

演算子リスト
https://www.postgresql.jp/document/9.6/html/functions-json.html#functions-json-op-table

##参考
https://www.dbonline.jp/postgresql/
https://www.postgresql.jp/document/9.6/index.html