【OSS-DB Silver】PostgreSQL SQLとオブジェクト テーブルの作成や検索方法の実践

OSS-DB　Silver

OSS-DBは色々なものがありますが、商用データベースとの連携に優れ、エンタープライズシステムでも多くの導入実績があるのが「PostgreSQL」です。
OSS-DB Silverは「PostgreSQL　11」に関する能力を問う資格です。

試験で問われる範囲は以下の通りです。

分類 説明
一般知識(16%) OSS-DBの一般的特徴、リレーショナルデータベースに関する一般知識
運用管理(52%) インストール、ツールの使い方、設定ファイル、バックアップ方法、運用管理作業
開発/SQL (32%) SQLコマンド、組み込み関数、トランザクションの概念

これらから50題出題され、65%程度の正答率で合格となります。

今回は開発/SQLの中からSQLとオブジェクトについて説明します。

Sec 8. SQLとオブジェクト

データの参照

  • 基本的に大文字と小文字を区別しない（基本小文字になる）
    →テーブル名に大文字を使いたい場合などはダブルクォーテーションで囲む

  • 文字や日付情報はシングルクォーテーションで囲む

  • 基本的な選択

    # 基本
SELECT 列名 FROM テーブル名 WHERE 検索条件;
# 列名に別の名前をつける場合
SELECT 列名 [AS] 別名 FROM テーブル名 WHERE 検索条件;
# 複数のテーブルで同じ名前の列がある場合
SELECT テーブル名.列名 FROM テーブル名 WHERE 検索条件;

  • 関数や、基本的な演算も可能

    # 現在時刻を呼び出す関数
SELECT now();
# 演算
SELECT 1+1;

  • ORDER BY : 並べ替え

    SELECT 列名 FROM テーブル名 WHERE 検索条件 ORDER BY ソート対象 [ASC|DESC];
    • ソート対象はSELECTで指定した列名や、列の番号を指定する。

  • LIMIT, OFFSET : SELECTで取り出す件数の上限や範囲を指定

    • LIMIT : 上限となる件数
    • OFFSET : 先頭から何件をスキップするか
    • 基本的にORDER BYでソートした結果に対して使用することが多い

  • DISTINCT : 重複を除去する場合に使用する

    SELECT DISTINCT [ON (重複対象の列名)] 列名 FROM テーブル名 WHERE 検索条件;
    • DISTINCT ONでは最初に指定した列に対してソートを行う。

  • GROUP BY, HAVING : 特定の列をグループ化したい場合に行う

    SELECT 列名 [集約関数(列名)] FROM テーブル名 WHERE 検索条件 GROUP BY 対象列名;
    • 基本的にはGROUP BYを使用する場合は、SELECTできる列名はGROUP BYで指定したもののみ。
      集約関数を使用している場合は別。
    SELECT 列名 [集約関数(列名)] FROM テーブル名 WHERE 検索条件 GROUP BY 対象列名 HAVING 条件;
    • HAVINGはGROUP BYでグループ化した結果に対しての条件指定を行う。

  • 副問合せ : SELECTの結果をテーブルや条件として使いたい場合に使用する。

    • 使用する場合は副問合せ部分をかっこで括る
    • 副問合せとテーブルとして扱う場合は、エイリアス（別名）が必要となる

  • IN, NOT IN : 列に対して　複数の値で条件を指定したい場合に使用

    SELECT 列名 FROM テーブル名 WHERE 列名 [NOT] IN (値);

  • ANY : INと同様

    • 副問合せの結果が必ず一つである
    • 値を指定する場合は配列の形式とする
    • 演算子を使用可能
    SELECT 列名 FROM テーブル名 WHERE 列名 演算子 ANY(副問合せ);

  • BETWEEN : 列に対してある範囲の条件を指定したい場合に使用

    SELECT 列名 FROM テーブル名 WHERE 列名 BETWEEN 値 AND 値;

  • 結合 : 複数のテーブルについて、特定の列の値が等しいものを条件にして結びつける。

    • INNER JOIN : 結合キーの条件に合致する行だけを結びつけて出力したい場合に使用
    # 列の値の条件による指定
SELECT 列名 FROM テーブル名1 [INNER] JOIN テーブル名2 ON テーブル名1.列名 = テーブル名2.列名 WHERE 条件;
# 列名の指定
SELECT 列名 FROM テーブル名1 [INNER] JOIN テーブル名2 USING (列名) WHERE 条件;
# 結合対象のテーブルにある同じ名前の列全てを結合キーとする
SELECT 列名 FROM テーブル名1 NATURAL [INNER] JOIN テーブル名2 WHERE 条件;
    • CROSS JOIN : 結合対象の全ての行を結びつけて出力する
    SELECT 列名 FROM テーブル名1 CROSS JOIN テーブル名2 WHERE 条件;
SELECT 列名 FROM テーブル名1, テーブル名2 WHERE 条件;

    • 結合キーの指定はなく、各行を結びつけるため、条件を指定しない場合は結合対象の二つのテーブルの行数をかけた行が出力される。

    • OUTER JOIN : 結合キーの条件に合致する行に加えて、合致しなかったいずれか片方のテーブルの行を出力したい場合に使用する

    SELECT 列名 FROM テーブル名1 {LEFT | RIGHT | FULL} [OUTER] JOIN テーブル名2 ON テーブル名1.列名 = テーブル名2.列名 WHERE 条件;
SELECT 列名 FROM テーブル名1 {LEFT | RIGHT | FULL} [OUTER] JOIN テーブル名2 USING (列名) WHERE 条件;
SELECT 列名 FROM テーブル名1 NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN テーブル名2 WHERE 条件;
    • LEFTを選択した場合、JOINの左側に位置するテーブルの結合キーに合致しなかった右側のテーブルの値をNULLとして左側のテーブルの行を出力する。
    • RIGHTはLEFTの逆。
    • FULLはLEFTとRIGHTを合わせた出力。

  • EXISTS,NOT EXISTS : 副問合せを使用して、ある条件に合致するものを検索したい場合に使用する。

    SELECT 列名 FROM テーブル名 WHERE EXISTS(副問合せ);
    • 副問合せの結果が一行以上返却された場合にSELECTの実行結果が出力される。
    • 副問合せにテーブル名に関する条件が指定された場合には、その条件に合致する行のみテーブル名から取得される。

  • UNION, EXCEPT, INTERSECT : 2つのSELECTの結果の集合を出力する場合に使用する

    • UNION : 和集合
    SELECT文 UNION [ALL] SELECT文;

    • ALLを省略すると、重複する行は自動的に除去される

    • EXCEPT : 右側のSELECT文の結果にはなく、左側のSELECTの結果にある行(左から右をひく）

    SELECT文A EXCEPT [ALL] SELECT文B;
    • INTERSECT : 積集合。双方のSELECT文の結果にある行だけを出力する
    SELECT文 INTERSECT [ALL] SELECT文;
    • 優先順位はINTERSECTが一番高く、UNIONとEXCEPTは同じ

INSERT/ UPDATE/ DELETE

  • INSERT : データの新規挿入

    INSERT INTO テーブル名 [(列名)] VALUES (挿入データ);
    • 複数行をまとめて挿入する場合は、挿入データをカッコで括り、カンマで区切って記載
    # SELECTの結果をINSERTに利用する場合
INSERT INTO テーブル名 [(列名)] SELECT文;

  • UPDATE : データの更新

    UPDATE テーブル名 SET 列名 = 更新データ WHERE 条件;

  • DELETE : 行の削除

    DELETE FROM テーブル名 WHERE 条件;
# すべての行を削除する場合
TRUNCATE テーブル名;

データ型

  • バイナリ型には、byteaが使用される（PostgreSQLでの形式）

  • 日付型にはtimestamp, date, time,intervalなどの種類がある

  • 論理値はbooleanのみ

    • 真値には’t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’, TRUEがある（TRUEのみシングルクォートで囲まない）
      擬値も同様。

  • 連番：smallserial, serial, bigserialの三種類

    • 例えば、列「id」の型をserialとした場合、INSERT時にユーザが省略した場合は連番を記入されるが、ユーザが指定した値と重複する可能性があるなど注意は必要。

  • OID : オブジェクトの識別に使用されるID（データベースクラスタ全体で一意の値をとる）

    • テーブルやインデックスに割り当てられているOIDは、pg_classなどのシステムテーブルに対するSQLで確認可能
    SELECT oid, relname FROM pg_class limit 3;
  oid  |       relname
-------+----------------------
 16391 | pg_toast_16388
 16392 | pg_toast_16388_index
 16388 | member
(3 rows)

  • 配列：全てのデータ型に対して配列型を使用可能

    CREATE TABLE array_tbl (
c1 text[],
c2 int[],
c3 timestamp[]
);

  • NULL : 不定

    • 通常の演算子で比較などができない
    • NULLかどうかを調べるには列名 IS [NOT] NULLを使用する。
    • NULLを含む列のソートはNULLS FIRST, NULLS LASTを使用する。
    • 通常はNULLは非NULL値よりも大きい値として扱われるため、通常の昇順では非NULLの後に出力される。

  • キャスト : あるデータ型を別のデータ型に変換する処理

    データ::変換後のデータ型
CAST (データ AS 変換後のデータ型)

テーブルの定義

  • テーブルの作成と削除

    CREATE TABLE テーブル名 (列名 データ型 [デフォルト値] [, ...]);
DROP TABLE テーブル名;
    • 作成したテーブルの各列の定義については、psql上で\d テーブル名を実行することで確認ができる。

  • 制約 : テーブルの列に対して特定の条件を満たす値のみを許可する場合に使用する。

    • 主キー制約：重複値・NULLの禁止。一つのテーブルに対して一つのみ。
    CREATE TABLE テーブル名 (列名 データ型 PRIMARY KEY);
ALTER TABLE テーブル名 ADD PRIMARY KEY (列名);
ALTER TABLE テーブル名 ADD CONSTRAINT 主キー名 PRIMARY KEY(列名);
    • ユニーク制約：重複値の格納の禁止
    CREATE TABLE テーブル名 (列名 データ型 UNIQUE);
ALTER TABLE テーブル名 ADD UNIQUE (列名);
ALTER TABLE テーブル名 ADD CONSTRAINT ユニークキー名 UNIQUE(列名);

    • NULLの格納は許可される

    • NOT NULL制約：NULLの格納の禁止

    CREATE TABLE テーブル名 (列名 データ型 NOT NULL);
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET NOT NULL;
    • 外部キー制約：参照先の列にない値の格納を禁止
    CREATE TABLE テーブル名 (列名 データ型 REFERENCES 参照先テーブル名(列名));
CREATE TABLE テーブル名 (列名 データ型, FOREIGN KEY(列名) REFERENCES 参照先テーブル名(列名));
ALTER TABLE テーブル名 ADD CONSTRAINT 外部キー名 FOREIGN KEY (列名) REFERENCES 参照先テーブル名(列名);

    • 外部キーによって参照されているテーブルの列を更新・削除すると、制約に違反することになる。この場合にエラーにする場合と、参照しているデータも更新・削除する場合の2つを選択可能。

      CREATE TABLE テーブル名 (列名 データ型 REFERENCES 参照先テーブル名(列名) [ON DELETE CASCADE] [ON UPDATE CASCADE]);
CREATE TABLE テーブル名 (列名 データ型, FOREIGN KEY(列名) REFERENCES 参照先テーブル名(列名) [ON DELETE CASCADE] [ON UPDATE CASCADE]);

    • チェック制約：ユーザが定義した値以外の格納を禁止

    CREATE TABLE テーブル名 (列名 データ型 CHECK(条件式));
ALTER TABLE テーブル名 ADD CONSTRAINT チェック制約名 CHECK (条件式);
    • ドメイン制約：ユーザが定義した値以外の格納を禁止(チェック制約を伴った独自のデータ型を作り、チェック制約の定義を楽にする)
    CREATE DOMAIN ドメイン名 AS データ型 [NULL | NOT NULL | CHECK(条件式)];

パーティション

  • テーブルをある範囲ごとに区切ったもの

    • 削除やメンテナンスの粒度を細かくすることが可能
    • キャッシュヒットの向上

  • 書式

    CREATE TABLE 親テーブル名 (列名 データ型) PARTITION BY RANGE (列名);
CREATE TABLE パーティションテーブル名 (列名 データ型) PARTITION OF 親テーブル名 FOR VALUES 条件;
    # 親テーブルと独立したテーブルとして作成しておき、後から親テーブルへ追加する場合
ALTER TABLE 親テーブル名 ATTACH PARTITION パーティションテーブル名 FOR VALUES 条件;
# パーティションテーブルを切り離して、親テーブルとは別のテーブルとして独立させる
ALTER TABLE 親テーブル名 DETACH PARTITION パーティションテーブル名;

シーケンス

  • 自動で連番を払い出してくれるオブジェクト

    CREATE SEQUENCE シーケンス名 [オプション];
    • INCREMENT 増加値 : デフォルトは1
    • START 開始値 : デフォルトは1
    • [NO]CYCLE : シーケンスが最大値に達した場合の振る舞い。CYCLEを指定すると開始値に戻る。

  • シーケンスの現在値の確認にはcurrval()、新しい値の取り出しにはnextval()を使用する。

    • シーケンスの作成後は、まずnextval()で値を払い出してから出ないとcurrval()で確認ができない。
    • 特定の値を設定する場合はsetval()を使用する。

ビュー

  • あるSELECT文の結果を定義したもの

    CREATE VIEW ビュー名 [(列名)] AS SELECT文;

インデックス

  • PostgreSQLでは主なインデックス

    • B-Treeインデックス：スカラー値に対する一致や不等号の検索に用いる
    • GiST : 空間情報の検索に用いたれるインデックス。二次元以上の空間に対する一致や包括の検索に用いる
    • GIN : 全文検索に用いられる転置インデックス。ある要素がどの列にあるかを検索するのに用いる
    • ハッシュ : 値の一致検索のみをサポートするハッシュインデックス

  • インデックスの作成

    CREATE INDEX インデックス名 ON テーブル名 [USING インデックス種別] (列名);
    • インデックスは複数の列に対して作成可能（マルチカラムインデックス）
      補足：上の主なインデックスではbtree, gist, ginではマルチカラムインデックスの指定が可能。

  • 関数インデックス/式インデックス

    • 関数や式の結果を検索条件している場合、インデックス化することで検索条件を向上させることが可能。
    • 式インデックスは二重のカッコで囲む必要がある

  • 部分インデックス

    • テーブルの特定の範囲のデータのみにインデックスを作成する場合に使用する。
    • 検索にとって有意義な部分のみにインデックスを作成することで、領域を無駄に消費数のを防ぐ

トリガ

  • 特定のテーブルに対して、挿入/更新/削除が行われた場合に、規定のユーザ定義関数を発動させる機能

    • 行ごと、SQLごとの指定が可能
    • 更新対象の列の指定及び、実際に更新されたがどうか、更新の前後どちらで発動するかを指定可能

  • 作成

    CREATE TRIGGER トリガ名 {BEFORE| AFTER} {UPDATE | INSERT | DELETE | TRUNCATE} 
ON テーブル名 [FOR [EACH] {ROW | STATEMENT}] 
EXECUTE PROCEDURE 関数名;

ルール

  • ある条件のSQLが発行された場合に、それを別のSQLで置き換えたり、別の処理を付け加えたりしたい場合に使用する。

  • 作成

    CREATE RULE ルール名 AS ON イベント名 TO テーブル名 DO [INSTEAD | ALSO] SQL文;

    • イベント名には「INSERT/ SELECT/ UPDATE/ DELETE」のいずれかを指定する

    • INSERT/ UPDATEを指定時に、元のSQLでの新規の値を用いる場合はNEWというキーワードを使用可能。
      →ログのテーブルにも同じ値を追記する場合などに使用する。

    • 処理を置き換える場合はDO INSTEAD、処理を追加する場合はDO ALSOとする

スキーマ

  • いわゆる名前空間
    →DB内に複数のスキーマが存在し、各スキーマの中にテーブルやインデックスが含まれる

  • 作成

    CREATE SCHEMA スキーマ名 [AUTHORIZATION データベースユーザ名];
CREATE SCHEMA AUTHORIZATION データベースユーザ名;
    • AUTHORIZATIONの指定で、スキーマの所有者を指定することが可能

  • スキーマへのテーブルの作成とスキーマ検索パス

    # スキーマの中にある「tbl」というテーブルを定義
CREATE TABLE スキーマ名.tbl (列名 データ型, ...);
    • スキーマ検索パスを指定すると、どのスキーマのオブジェクトに優先的にアクセスするかを指定することが可能
    SET search_path TO スキーマ名;

関数とプロシージャ

  • ストアドプロシージャ：DBに関数を定義すること

    • 関数は戻り値を指定できるが、プロシージャはできない
    • 関数はSELECTで呼び出すが、プロシージャはCALLで呼び出す
    • 関数は定義内でトランザクションのCOMMITやROLLBACKができないが、プロシージャはできる

  • 関数定義

    # 関数
CREATE [OR REPLACE] FUNCTION 関数名(引数) RETURNS [SETOF] 戻り値 AS $$
関数の記述
$$ LANGUAGE 使用言語;
# プロシージャ
CREATE [OR REPLACE] PROCEDURE プロシージャ名(引数) AS $$
関数の記述
$$ LANGUAGE 使用言語;

  • PL/pgSQLによる関数の構造

    DECLARE
変数の宣言
BEGIN
関数の処理記述
END;
# 変数の宣言
変数名 [CONSTANT] データ型 [NOT NULL] [{DEFAULT | :=} 値など];
# コメントは
-- 一行分のコメント
/* 範囲のコメント */
# が許可されている

テーブルスペース

  • データベースのデータは、物理的にはテーブルスペースに格納される
    →ユーザがテーブルスペースを用意して、そこへテーブルを作成することが可能

    CREATE TABLESPACE テーブルスペース名 LOCATION 'パス名';
    • 高速なストレージを用意した場合などに使用

マテリアライズドビュー

  • 実体を持つビュー

    • インデックスが作成可能
    • 即時性を求めないデータで、結果の取得に時間がかかる場合などに使用
    CREATE MATERIALIZED VIEW マテリアライズドビュー名 AS SELECT文;
# 格納されているデータは常に最新ではないので、更新する必要がある
REFRESH MATERIALIZED VIEW マテリアライズドビュー名;

コメント

タイトルとURLをコピーしました