個人的な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