Chiroru's Diary

日々の学びをちょこちょこメモしていきます

PostgreSQL構文のまとめ

SQLゼロからはじめるデータベース操作』を参考に、PostgreSQLについて学んだので構文についてまとめてみようと思います。

SQLの語彙についてはSQL基本の理解(言葉の整理編)でまとめています。

【目次】


SQLの語彙について:SQL基本の理解(言葉の整理編)

データベース

作成

CREATE DATABASE <データベース名>;

-- 一度psqlを終了し、新しく作ったDBに接続
# \q

postgres@:~$ psql -U <ユーザー名> -d <データベース名> -h localhost

テーブル

作成

CREATE TABLE <テーブル名>
(  
<列名> <データ型> <制約>,  
・・
<テーブルの制約>, ・・  
);

PRIMARY KEY (<列名>)

定義の変更

-- 列を追加
ALTER TABLE <テーブル名> ADD COLUMN <列の定義(※列名・データ型・制約)>;

-- 列を削除
ALTER TABLE <テーブル名> DROP COLUMN <列名>;

-- テーブル名の変更
ALTER TABLE <古テーブル名> RENAME TO <新テーブル名>;

データを登録する

-- トランザクションを開始
BEGIN TRANSACTION

-- 行の追加(※この場合は全カラムに対応した値が必要)
INSERT INTO <テーブル名> VALUES (値);

-- 追加を確定する
COMMIT;

削除

DROP TABLE <テーブル名>;

テーブル関連のpostgresqlコマンド

-- テーブル一覧の表示
\dt;

-- テーブル構造の表示
\d <テーブル名>;

-- テーブル内のデータ一覧表示
SELECT * FROM <テーブル名>;

SELECT文

テーブルからデータを検索し、取り出す(問い合わせ)時に使用する。

-- 列を検索する
SELECT <列名>, <列名>・・
FROM <テーブル名>;

列に別名をつける

SELECT <列名> AS <別名>,
     <列名> AS "<日本語別名>"
FROM <テーブル名>;

重複を除く

SELECT DISTINCT <列名>, <列名>
FROM <テーブル名>;

行の条件指定

SELECT <列名>, <列名>・・
FROM <テーブル名>;
WHERE <行に対する条件>;

集約関数

COUNT(*)を除き、基本的に全ての集約関数はNULLを除外している。

-- テーブルの行数をカウントする
SELECT COUNT(*)
FROM <テーブル名>;

-- NULLを省いた行数をカウントする
SELECT COUNT(列名)
FROM <テーブル名>;

-- 列の合計値を求める
SELECT SUM(列名)
FROM <テーブル名>;

-- 列の平均値を求める
SELECT AVG(列名)
FROM <テーブル名>;        

-- 列の最大値を求める
SELECT MAX(列名)
FROM <テーブル名>;

-- 列の最小値を求める
SELECT MIN(列名)
FROM <テーブル名>;

--重複を除外してカウントする
SELECT COUNT(DISTINCT 列名)
FROM <テーブル名>;

テーブルのグループ分け

SELECT <列名>
FROM <テーブル名>
GROUP BY <列名>;

グループに対する条件指定

SELECT <列名>
FROM <テーブル名>
GROUP BY <列名>
HAVING <グループに対する条件>;

DISTINCTとGROUP BY

どちらも重複を排除し、NULLをひとまとめにし、実行速度もほぼ同じである。この二つの使い分けとしては以下のようにすると良い。

DISTINCT:選択結果から重複を除外したい時
GROUP BY :集約した結果を求めたい時

検索結果の並べ替え

デフォルトでは昇順(ASCキーワード)で、降順にしたければDESCを末尾につける。

SELECT <列名>
FROM <テーブル名>
ORDER BY <並べ替えの基準となる列=ソートキー> DESC;

データ

登録

文字型の値を挿入する時は「' '」で囲うことを忘れないこと。

-- 基本構文(※列に対してDEFAULT制約をつけている場合、値にDEFAULTキーワードの指定が可能)
INSERT INTO <テーブル名> (列, 列・・) VALUES (値, DEFAULT,・・)

-- 複数行INSERT
INSERT INTO <テーブル名> (列1, 列2・・) VALUES (値1・・),
                                          (値2・・);

-- 全列にINSERTする場合は、列リストの省略可
INSERT INTO <テーブル名> VALUES (値, 値・・)

-- NULLの挿入
INSERT INTO <テーブル名> (列, 列,・・) VALUES (値, NULL,・・)

他テーブルのデータをコピーする

-- テーブルBのデータを、テーブルAにコピー
INSERT INTO <テーブルA> (列, 列・・)
SELECT 列, 列・・
FROM <テーブルB>;

削除

-- 基本構文
DELETE FROM <テーブル名>;

-- 一部の行を削除
DELETE FROM <テーブル名>
WHERE <行の条件>;

-- 必ず全行削除(※DELETEとの違いは、ロールバックできないこと)
TRUNCATE <テーブル名>;

更新

-- 基本構文
UPDATE <テーブル名>
SET <対象の列名> = <更新後の値>;

-- 一部の列のみ変更
UPDATE <テーブル名>
SET <対象の列名> = <更新後の値>
WHERE <行の条件>;

トランザクション

「COMMIT」は処理の変更を確定し、「ROLLBACK」は処理の変更を破棄する。

BEGIN TRANSACTION
DML文
DML文
COMMIT / ROLLBACK;

PostgreSQL

ちなみにPostgreSQLは、全てのSQL文をトランザクション内で実行している。どういうことかというと、BEGINを明示的に発行せずとも全てのSQL文はBEGINとCOMMITに囲まれている扱いとなっている。
そして、複数のSQL文のうち一つでもエラーを起こせばその後のものもエラーとなり、コミットは失敗して強制ロールバックされる仕組みになっている。

ビュー

-- ビューの作成
CREATE VIEW <ビューの名前> (ビュー列名, ビュー列名, ・・)
AS SELECT;

-- ビューの削除
DROP VIEW <ビューの名前>;

-- 多段ビューの削除
DROP VIEW <ビューの名前> CASCADE;

関数まとめ

算術

-- 絶対値を求める
ABS(数値)

-- 余剰を求める
MOD(被除数, 除数)

-- 四捨五入
ROUND(対象数, 丸めの桁数)

文字列

-- 小文字に変換する
LOWER(文字列)

-- 大文字に変換する
UPPER(文字列)

-- 文字列の置換
REPLACE(対象文字列, 置換前, 置換後)

-- 文字列切り出し(切り出し開始の位置=左から何番めか)
SUBSTRING(対象文字列 FROM 切り出し開始の位置 FOR 数)

変換

-- 型の変換
CAST(変換前の値 AS 変換するデータ型)

-- 最初にNULLでない値を返す
COALESCE(データ, データ,・・)

日付

-- 現在の日付
CURRENT_DATE

-- 現在の時間  
CURRENT_TIME

-- 日時
CURRENT_TIMESTAMP

-- 日付要素の切り出し
EXTRACT(日付要素 FROM 日付)

述語

戻り値が真理値になる関数のこと。

-- 文字列の部分一致検索
<列> LIKE <文字列>

-- 範囲検索
<列> BETWEEN <値> AND <値>

-- NULL行の選択/NULL行以外の選択
IS NULL
IS NOT NULL

-- 複数のORを省略する(引数にビューの指定可)
<列> IN (値, 値, ・・)

-- 条件に合うレコードの存在を調べる
EXISTS <SELECT * の相関サブクエリ>;

CASE式

SELECT
  CASE
    WHEN <列=値> THEN <真の時の式>
    ELSE <真ではない時の式>
  END
FROM <テーブル名>;

テーブルの集合演算

足し算(行方向に連結)

-- 重複を排除しない場合はUNION ALL
SELECT
UNION 
SELECT;

共通部分を選択する

SELECT
INTERSECT
SELECT;

差分を抽出(引き算)

SELECT  
EXCEPT  
SELECT;

結合

別のテーブルから列を持ってきて、列方向に連結する。

-- 両テーブルに存在する情報のみ出力(内部結合)
SELECT
FROM <テーブル名1> INNER JOIN <テーブル名2>
ON <テーブル名1>.列名 =  <テーブル名2>.列名

-- テーブル情報が欠けることなく出力(外部結合)
SELECT
FROM <テーブル名1> OUTER JOIN <テーブル名2>
ON <テーブル名1>.列名 =  <テーブル名2>.列名

ウィンドウ関数

-- レコードのランキングの算出
SELECT
RANK () OVER (PARTITION BY ランキングの範囲)
FROM <テーブル名>;

-- 累計の算出
SELECT
SUM(列名) OVER (ORDER BY 並びの基準の列名)
FROM <テーブル名>;

GROUPING演算子

合計と小計を同時に求めることができる。

SELECT
FROM <テーブル名>
GROUP BY ROLLUP(列, 列);

参考