OSS-DB Silverチートシート

# 概要
OSS-DB Silverに合格するためのチートシート

https://oss-db.jp/outline/silver

試験概要
- バージョン：3.0
- 問題数：50問
- 試験時間：90分
- 合格点：64点（100点満点）

出題分野（数字は重要度を示す）
- S1 一般知識（16％）
  - S1.1 OSS-DBの一般的特徴　【重要度：4】
  - S1.2 リレーショナルデータベースに関する一般知識　【重要度：4】
- S2 運用管理（52％）
  - S2.1 インストール方法　【重要度：2】
  - S2.2 標準付属ツールの使い方 【重要度：5】
  - S2.3 設定ファイル 【重要度：5】
  - S2.4 バックアップ方法 【重要度：7】
  - S2.5 基本的な運用管理作業 【重要度：7】
- S3 開発/SQL（32％）
  - S3.1 SQL コマンド 【重要度： 13】
  - S3.2 組み込み関数 【重要度：2】
  - S3.3 トランザクションの概念 【重要度：1】


# S1 一般知識（16％）
## S1.1 OSS-DBの一般的特徴　【重要度：4】
用語集
- カリフォルニア大学のバークレイ校で開発されたPOSTGRES(1986年)が起源
- PGDG(PostgreSQL Global Development Group)：開発コミュニティ
    - メーリングリストの運営
    - 世界各地でイベント開催
    - バグの修正に対して義務は負わない
- BSDライセンス：PostgreSQLのベースとなるライセンス
    - 無償利用，再配布，ソースコードの改変可能
    - ソースコードの公開義務なし
    - 再配布はの際は，著作権とライセンス条文，無保証であることをドキュメントに記載する

PostgresSQLの特徴
- オープンソース
- 多言語対応
- マルチプラットフォーム
- レプリケーション機能

バージョンについて
- 「x.y」のように2要素で表記
  - メジャーバージョン(x)：機能の追加や仕様の変更
      - データベースを構成するファイルに互換性がなくなる場合あり
      - 1年に1回程度実施
      - 5年間のサポート
  - マイナーバージョン(y)：不具合やセキュリティ対策
      - 2,3ヶ月に1回程度実施

## S1.2 リレーショナルデータベースに関する一般知識　【重要度：4】
DBMS（デーがベース管理システム）の機能
- データ管理
- 整合性の維持
- トランザクション管理
- 同時実行制御
- セキュリティ機能
- 障害回復機能

データベースの種類
- リレーショナルモデル（関係モデル）
    - 表構造で表現，集合論をベース，分離性が高くプログラムへの影響が少ない
    - 「1対多」「多対1」の関係
- 階層モデル
    - 親データと子データが「1対多」になる
- ネットワークモデル
    - 親データと子データが「多対多」になる

SQLの分類
- DDL（データ定義言語）
    - CREATE，DROP，ALTER，TRUNCATE
- DML（データ操作言語）
    - SLECT，INSERT，UPDATE，DELETE
- DCL（データ制御言語）
    - GRANT，REVOKE，BEGIN，COMMIT，ROLLBACK

正規化
- 第1正規形
    - 主キーを設定
        - ユニーク制約とNOT NULL制約がある
    - 繰り返しがない
    - 導出項目(他の属性から算出できる項目)がない
- 第2正規形
    - 部分関数従属性がない（YがAに部分関数従属する＝「{A, B} → Y」かつ「A → Y」）
- 第3正規形
    - 推移関数従属性がない（ZがXに推移関数従属する＝「Y → X」は不成立で「X → Y」かつ「Y → Z」）

# S2 運用管理（52％）
## S2.1 インストール方法　【重要度：2】
用語
- データベースクラスタ：データベースのファイルを格納する領域
    - ファイルシステム上の1つのディレクトリ
    - PostgreSQLサーバと1対1の関係になる
    - initdbコマンドで作成する
    - root以外のOSの一般ユーザで作成する（OSのユーザ＝DBの管理者ユーザになる）
    - デフォルトで3つのデータベースが作成される
        - template0：新規のデータベース作成時のテンプレートに指定できる（変更不可）
        - template1：新規のデータベース作成時のテンプレートになる（変更可）
        - postgres：通常のデータベース同様に使える
- エンコーディング：UTF8, EUC_JP, SJISが日本語のエンコーディング（SJISはデータベース側で使用できない）

`initdb`コマンド：データベースクラスタを作成するコマンド``
- `-D, --pddata=ディレクトリ名`：ディレクトリを指定（指定しない場合は環境変数`$PGDATA`になる）
- `-E, --encoding=エンコーディング`：エンコーディングを指定（未指定の場合はOSのロケールから自動的に設定）
- `--locale=ロケール`：ロケールを指定する（未指定の場合はOSのロケールを使用）
- `--no-locale`：ロケールを無効（`--locale=C`でも可）
- `-U, --username=ユーザ名`：管理者ユーザ名を指定（未指定の場合OSユーザ名）
- `-k, --data-checksums`：データベースのチェックサム（データ破損を検出する仕組み）を有効
- `-X, --waldir=ディレクトリ名`：WAL（データベースへの変更ログ）を格納するディレクトリを指定

## S2.2 標準付属ツールの使い方 【重要度：5】
pg_ctlコマンド：データベースクラスタの管理コマンド（管理者ユーザのみがローカルホストから実行できる）
- `pg_ctl initdb(init)`：新規データベースクラスタを作成する
    - `-D ディレクトリ名 | --pgdata=ディレクトリ名`：格納先ディレクトリを指定．未指定は$PGDATA
- `pg_ctl start`：バックグラウンドでPostgreSQLサーバを起動する
    - `-D データベースクラスタ | --pgdata=データベースクラスタ`：対象のクラスタを指定．未指定は$PGDATA
    - `-W`：起動の完了を待たずにコマンド発行元に制御を戻す（デフォルトは最大60秒待ち起動完了メッセージを表示）
    - `-t 最大待ち時間`：処理が完了するまでの待ち時間を指定（未指定は60秒）
- `pg_ctl stop`：シャットダウンする
    - `-m シャットダウンモード`：3つの異なるシャットダウン方式を指定
        - `f | fast`：高速シャットダウン．実行中の処理を中断しクライアント接続を強制的に切断．トランザクションはロールバック（デフォルト）
        - `i | immediate`：即時シャットダウン．実行中の処理を強制終了しクライアント接続を強制的に切断．次回起動時に回復処理を実行
        - `s | smart`：スマートシャットダウン．全てのクライアント接続が切断されてからシャットダウン．実行中の処理はシャットダウンの前に正常に完了される
- `pg_ctl restart`：再起動する
- `pg_ctl reload`：設定ファイルを再読み込みする
- `pg_ctl status`：PostgreSQLサーバの稼働状態を確認数する
- `pg_ctl kill`：プロセスにシグナルを送信する
    - `INT`：高速シャットダウン
    - `QUIT`：即時シャットダウン
    - `TERM`：スマートシャットダウン
    - `HUP`：設定ファイル再読み込み（reloadと同じ）

psqlコマンド：PostgreSQLに接続するコマンド（ex: `psql -h 192.168.0.10 -p 9999 -U user1`）
- `-h, --host=ホスト名`：ホスト名を指定（デフォルトは$PGHOST）
- `-p, --port=ポート番号`：ポート番号を指定（デフォルトは5432）
- `-U, --username=ユーザ名`：ユーザを指定（デフォルトは$PGUSER）
- 他オプション
    - `-l | --list`：すべてのデータベースのリスト
    - `-c | --command`：指定したコマンドの実行結果を表示 
    - `-f | --file`：指定したファイルを読み込み実行結果を表示
    - `-s | --single-step`：各コマンド毎に実行するかを確認 
    - `-1 | --single-transaction`：複数のコマンドを1つのトランザクションで実行

ユーザ管理コマンド
- `createuser`：ユーザを作成する
    - `-p | --pwprompt`：パスワードを設定
    - `-s | --superuser`：新しいユーザをすーぱユーザとして作成
    - `-d | --createdb`：データベースの作成を許可
    - `-r | --createrole`：新しいユーザの作成を許可
    - `-l | --login`：ログインを許可（デフォルト）
    - `-S | --no-superuser`：新しいユーザをスーパーユーザにしない（デフォルト）
    - `-D | --no-createdb`：データベースの作成を禁止（デフォルト）
    - `-R | --no-createrole`：新しいユーザの作成を禁止（デフォルト）
    - `-L | --no-login`：ログインを禁止
- `dropuser`：ユーザを削除する
    - `-i | --interactive`：削除前に確認

データベース管理コマンド
- `createdb`：データベースを作成する
    - `-E | --endording`：エンコーディングを指定
    - `-O | --owner`：所有ユーザを指定
    - `-l | --locale`：ロケールを指定
    - `-T | --template`：データベーステンプレートを指定
- `dropdb`：データベースを削除する
    - `-i | --interactive`：削除前に確認

その他コマンド
- `pg_config`：PostgreSQLの設定情報を表示する
- `pg_controldata`：制御情報を表示する
- `pg_isready`：接続状態を確認する
- `pg_resetwal`：WALを消去して制御情報を初期化する
    - `-D | --pgdata`：対象のデータベースクラスタを指定
    - `-n | --dry-run`：実際に処理は行わず変更内容の出力のみ事項
    - `-f | --force`：制御情報が読み取れない場合でも強制的に実行

メタコマンド：データベース内の情報を取得するコマンド
- `\l`：データベースの一覧
- `\d`：テーブル，ビュー，シーケンス一覧
- `\dt`：テーブル一覧
- `\du`：ユーザ一覧
- `\?`：メタコマンド一覧

## S2.3 設定ファイル 【重要度：5】
パラメータ設定に関するコマンド
- `SHOW パラメータ名;`：パラーメータの値を表示（`ALL`を指定するとすべて表示）
- `SET パラメータ名 TO {設定値 | DEFAULT};`：パラメータの設定を変更
- `SET パラメータ名 = {設定値 | DEFAULT};`：上に同じ
    - SETの後に、`SESSION`(現在のセッションのみ有効，デフォルト)と`LOCAL`(現在のトランザクションのみ有効)を指定できる

`postgresql.conf`：PostgreSQL全体の動作を制御する設定ファイル
- パラメータは1行につき1つ
- 大文字，小文字の区別はない
- #から行末まではコメント
- パラメータの値は、boolean型、整数型、浮動小数点、文字列の4つ
- boolean値はon, off, true, false, yes, no, 1, 0（t や f などの省略形も可）
- メモリの単位：B(バイト)～TB(テラバイト)
- 時間の単位：us(マイクロ秒)～d(日)
- 以下主なパラメータ
```
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'    # 接続要求を受け付けるPostgreSQLサーバ自身のIPアドレス
#port = 5432              # 接続を待ち受けるポート番号
max_connections = 100     # PostgreSQLに同時に接続可能な最大数

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
#search_path = '"$user", public'        # デフォルトで使用するスキーマ名
#default_transaction_isolation = 'read committed'  # 新しいトランザクションのデフォルトの分離レベル
#client_encoding = sql_ascii            # クライアントのエンコーディング

#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
#log_destination = 'stderr'             # ログの出力先                                   
#logging_collector = off                # 標準エラーをファイルに出力するか
#log_directory = 'log'                  # ログファイルを格納するディレクトリ
#log_filename = '%Y-%m-%d_%H%M%S.log'   # ログを書き込むファイルのファイル名
#log_min_messages = warning             # ログを書き込むログのレベル
#log_connections = off                  # クライアント情報をサーバログに出力するか
#log_line_prefix = '%m [%p] '           # サーバログメッセージの行頭の書式文字列
#log_statement = 'none'                 # サーバログに書き込むSQL分の種類
```

`pg_hba.conf`：クライアント認証を設定するファイル
- 認証方式：trust(常に許可), reject(常に拒否), md5(暗号化パスワード), password(平文パスワード)
```
local データベース名 ユーザ名 認証方式
host データベース名 ユーザ名 IPアドレス サブネットマスク 認証方式
host データベース名 ユーザ名 CIDRアドレス 認証方式
```


## S2.4 バックアップ方法 【重要度：7】
dumpコマンド
- `pg_dump`：データベースをバックアップする（ex: `pg_dump -Fc examdb -f examdb.dump`）
    - `-Fp`：平文(plain)で出力する（未指定の場合のデフォルト）
    - `-Fc`：カスタム形式(custom)で出力する
    - `-Ft`：tar形式で出力する
    - `-f`：バック先のファイル名を指定（省略した場合標準出力）
- `pg_dumpall`：データベースクラスタ全体をバックアップ（ex: `pg_dumpall -f db.sql`）
- `pg_restore`：平文以外のバックアップからリストアする（ex: `pg_restore -d examdb examdb.dump`）
    - `-d`：リストア先のデータベース名を指定
    - `-f`：リストアに必要なSQLコマンドを出力
- `psql`：平文形式のバックアップからリストアする（ex: `psql -f examdb.sql examdb`）

copyコマンド
- COPY：サーバ側のファイルとテーブル間のデータをコピーするSQLコマンド
    - `COPY テーブル名 TO 絶対パスのファイル名;`：テーブルのデータをファイルに出力
    - `COPY テーブル名 FROM 絶対パスのファイル名;`：ファイルのデータをテーブルに挿入
- `\copy`：クライアント側のファイルとテーブル間のデータをコピーする
    - `\copy テーブル名 to ファイル名`：テーブルのデータをファイルに出力
    - `\copy テーブル名 from ファイル名`：ファイルのデータをテーブルに挿入

PITR(Point In Time Recovery)：データベース全体のバックアップとWALファイルを使用して、データベースの復旧を行う


## S2.5 基本的な運用管理作業 【重要度：7】
データベースロールを管理するSQLコマンド
- `CREATE ROLE/USER ロール名 [WITH] [属性];`：データベースロールを作成
- `ALTER ROLE/USER ロール名 [WITH] [属性]`;：属性を設定
- `ALTER ROLE/USER 現在のロール名 RENAME TO 新しいロール名`;：ロール名を変更する
- `DROP ROLE/USER ロール名;`：データベースロールを削除

属性一覧
- SUPERUSER/NOSUPERUSER：スーパーユーザ権限
- CREATEDB/NOCREATEDB：データベース作成権限
- CREATEROLE/NOCREATEROLE：レプリケーション作成権限
- LOGIN/NOLOGIN：ログイン権限
- REPLICATION/NOREPLICATION：レプリケーション権限
- PASSWORD 'パスワード'：パスワードを設定または変更
- VALID UNTIL '日付'：パスワードの有効期限

不要領域や統計情報を整備するSQLコマンド
- VACUUM：不要領域を回収する
- ANALYZE：統計情報を収集して更新する

権限管理コマンド
- GRANT：権限を付与
- REVOKE：権限を削除
```
書式
・GRANT 権限 ON テーブル名 TO {ユーザ名 | PUBLIC};
・GRANT 権限 ON DATABASE データベース名 TO {ユーザ名 | PUBLIC};
・GRANT 権限 ON SCHEMA スキーマ名 TO {ユーザ名 | PUBLIC};

権限一覧
・SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CONNECT, CREATE, ALL
```

システム情報関数
- `version()`：PostgreSQLのバージョン
- `current_database()`：データベース情報
- `current_user | user`：ユーザ情報（`()`が付かない点に注意）

# S3 開発/SQL（32％）
## S3.1 SQL コマンド 【重要度： 13】
データ取得SQL
- `SELECT`：データ取得
- `ORDER BY ソート対象 [ASC | DESC]`：ソート
- `LIMIT 件数 OFFSET 位置`：取得件数と先頭からスキップする位置を指定
- `DISTINCT [ON 重複除去対象列名] 列名`：重複を除去する
- `GROUP BY 列名 HAVING 条件`：指定した列でグループ化して条件で絞り込む
- `BETWEEN A and B`：AからBの範囲を取得
- `EXISTS / NOT EXISTS`：副問合せの結果を条件に取得
- `UNION [ALL]`：重複を除外してテーブルを縦に結合（ALLを指定した場合重複も含める）
- `EXCEPT [ALL]`：除外したい行を右側に指定
- `INTERSECT`：取得したい行を右側に指定

テーブル結合SQL
- INNER JOIN（内部結合）：結合キーの値が等しいデータのみを取得
- OUTER JOIN（外部結合）：結合キーの値が等しいデータに加えて、条件に一致しなかったデータも取得
- CROSS JOIN (交差結合)：対象テーブルの全データの組み合わせを取得

データ型
- 数値型
- 文字列型
- バイナリ列データ型
- 論理値型
- 日付・時刻型
- 連番型
- OID型
- 配列型
- JSON型

制約
- 主キー制約：重複，NULLの格納を禁止．1つのテーブルに1つだけ作成可能
- ユニーク制約：重複地の格納を禁止
- NOT NULL制約：NULLの格納を禁止
- 外部キー制約：参照先の列に無い値の格納を禁止
- チェック制約：ユーザが定義した値以外の格納を禁止
- ドメイン製薬：ユーザが定義した値以外の格納を禁止
- 識別子制約：自動で払い出した連番を列に設定

用語
- パーティション：テーブルをある範囲に区切ったもの
- シーケンス：自動で連番を払い出すオブジェクト
- ビュー：SELECT文の結果を定義したもの
- インデックス：辞書の索引の役割を果たすもの
- トリガ：特定のテーブルに対して操作が行われた場合に，既定のユーザ定義関数を発動させる機能
- スキーマ：テーブルや関数などのオブジェクトをまとめる単位
- レプリケーション：データを複製し高可用性や負荷分散を実現すること

## S3.2 組み込み関数 【重要度：2】
組み込み関数一覧
- 集約関数：複数の数値データに対して処理を行った結果を返す
    - `count()`：NULL以外の件数
    - `sum()`：NULL以外の合計値
- 文字列関数：文字列に対して処理を行った結果を返す
    - `trim()`：指定した文字列から文字列を除去する
    - `lpad()`：指定した文字数になるように文字を左側に埋め込む
- 算術関数：数値に対して処理を行った結果を返す
    - `floor()`：指定した数値より小さい最大の整数を取得
    - `ceil()`：指定した数値より大きい最小の整数を取得
    - `random()`：0以上1未満のランダムな値を返す


## S3.3 トランザクションの概念 【重要度：1】
ACID特性
- Atomicity(原子性)：一連の処理が完全な状態でのみ実行される
- Consistency(整合性)：実行前後でデータの整合性が保たれる
- Isolation(分離性)：他のトランザクション処理に影響を与えたり受けたりしない
- Durability(持続性)：コミットにより変更された内容が確実に保存される


コマンド
- `BEGIN / START TRANSACTION`：トランザクション開始
- `COMMIT / END`：トランザクションを終了し処理を確定
- `ROLLBACK / ABORT`：トランザクションを停止し処理を取り消す
- `SAVEPOINT セーブポイント名`：セーブポイントを設定
- `ROLLBACK TO セーブポイント名`：セーブポイントまで処理を戻す
- `RELEASE SAVEPOINT セーブポイント名`：セーブポイントを削除