むらじゅん風呂具

ITエンジニアとたまに歌手と司会などで活動する村中淳のブログ

【メモ】OSS-DB Exam Silver 技術解説無料セミナーを受けてきた

受けてきたので気になったことを書き出し。

今回、受けて強く思ったのが、

事前にある程度動かして調べた知識があると講義の内容もスッと入る。
これないとだいぶぼやける、というかそのあと手を動かさないとすぐに忘れる。

セミナー受講時には意識していきたいと思った。


PostgreSQL 最新版ドキュメント(日本語版)

  • 2019/10/19 現在、商用データベースとして多く活用されているのは、PostgreSQL 10以上
  • データベースごとに文字のエンコーディングが可能

インスタンス

  • データベースを構成するファイルや共有メモリ、プロセスなどをあわせたもの
  • PostgreSQLサーバの起動単位

pgプロセス

アプリケーション、ログを作るプロセスなどが存在する

GUIの管理ツール

pgAdmin Ⅲ

テンプレートデータベース(template0、template1)

初期化の際に一緒に作成される
template0 は書き込み不可
template1 は書き込み可能(CAREATE DATABASEのコピー元、これがないとDBが作れない)
→初期設定を入れておけば、以後クリエイトと当時に入れられる

PostgreSQL 起動と停止

停止、緊急停止と即時停止
→ 緊急停止(あとで矛盾が起きないよう処理をしてくれる)と即時停止(動いていようが問答無用で止める)の違い
→ 即時停止後に再開する場合は多少のリカバリーを行う場合がある

プロンプト

=# スーパーユーザ
=> 一般ユーザ

psqlのメタコマンド

\x テーブルの表示モード変更
横表示から縦表示に変更できる   →もう一度\xとやると元に戻る

サンプルDBを提供している
※日本語訳はなし

パラメータ変更の反映

リロードじゃ反映しないパラメータがあり、その場合はサービス再起動を実施する

リモート接続の許可

postgresql.confの listen_address = 'localhost'
クライアント接続監視する、リモート接続可能にする場合は、listen_address = '*'

データベースアクセス制限

pg_hba.conf
- Trust認証 パスワードをかけずに信頼する設定
- Peer認証方式 カーネルからクライアント上のオペレーティングシステムのユーザ名を取得し、
それをデータベースユーザ名(オプションのユーザ名マップとともに)として使用することにより動作する

SQLの種類

  • DDL: Data Definition Language(データ定義言語)
    テーブルやインデックスの作成・変更・削除など - CREATE, ALTER, DROP

  • DML: Data Manipulation Language(データ操作言語)
    データの追加・検索・更新・削除など - INSERT, SELECT, UPDATE, DELETE

  • DCL: Data Control Language(データ制御言語)
    データのアクセス権設定・トランザクション制御など

  • GRANT, REVOKE
  • START TRANSACTION, COMMIT, ROLLBACK

SELECT文 SELECT文は以下の順番でないといけない

SELECT 句 FROM 句
... 何を出力するのか ... どのテーブルから出力するのか ... 行の選択条件
WHERE 句 GROUP BY 句
... グループのキー HAVING 句 ... グループの選択条件
UNION | EXCEPT | INTERSECT
... テーブルの合併|差|積

SELECT 句
... 何を出力するのか ...
ORDER BY 句 LIMIT 句 OFFSET 句
... 結果のソート ... 表示する行数 ... スキップする行数

テーブルの結合

内部結合、外部結合 (片方にデータがない場合など活用、お店データとレビューデータの結合など)

  • 和結合、差結合、積結合
    列数が一緒じゃないと使えない、列の名前は違っててよし
    -- UNION Aテーブル、Bテーブルともにあるもの -- EXCEPT AテーブルにあってBテーブルにないもの
    -- INTERSECT 両方に共通してあるもの

  • 副照会
    条件付きで照合する、いろんなSELECT文で照合が可能

  • 算術演算 @(絶対値)とは??
    絶対値とは何か?誰でも簡単に理解できる絶対値の解説!5つの計算問題付き

  • 型変換
    :: → PostgreSQLの"::"はキャスト、PostgreSQL特有のもの

  • 集約関数
    引数で指定しているもので結果が変わる
    DISTINCTがついているものは重複を外す
    COUNT NULLは数に入れない

  • トランザクション
    複数の手順を単一の「すべてかなしか」の操作にまとめること
    → 銀行の振込、自分の口座でのやりとりと相手の口座のやりとりをひとまとめに考えるとわかりやすい

トランザクションの特性

  • A 原子性 (Atomicity) トランザクション処理がすべて完了かまったく実行されていないかで終了すること

  • C 一貫性、同時性 (Concurrency)
    処理の順番に関わらず結果が同じになる

  • I 分離性、隔離性、独 立性 (Isolation)
    中間結果は、ほかのトランザクションの処理内容に何の影響も与えない

  • D 耐久性 (Durability) いったんトランザクションが完結したら障害が発 生してもデータの状態が変化しない

分離レベル

  • ダーティ・リード
    他のトランザクションによる未コミットの挿入/更新/削除結果が見える
  • ファジー・リード (反復不能読み取り 、ノン・リピータブル・リード)
    他のトランザクションによるコミット済みの更新/削除結果が見える
  • ファントム・リード
    他のトランザクションによるコミット済みの挿入結果が見える

簡単に言うと

ダーティリード
→ コミットする前に変更後のデータが見えること

ファジーリード
→ commit後のupdateが見える

ファントリード
→ commit後のINSERTが見える

データベースオブジェクト

  • インデックス
    データの検索を高速にする仕組み
  • トリガー
    テーブルに対してイベントが 発生したときに指定した関数 を実行するしくみ
  • シーケンス
    処理するたんびに数列を生成する
    トランザクションをロールバックしても一度使用した値は戻らないので、完全な連番にはならない