PostgreSQL構文のまとめ
『SQLゼロからはじめるデータベース操作』を参考に、PostgreSQLについて学んだので構文についてまとめてみようと思います。
※SQLの語彙についてはSQL基本の理解(言葉の整理編)でまとめています。
【目次】
- データベース
- テーブル
- テーブル関連のpostgresqlコマンド
- SELECT文
- 集約関数
- テーブルのグループ分け
- 検索結果の並べ替え
- データ
- トランザクション
- ビュー
- 関数まとめ
- 述語
- CASE式
- テーブルの集合演算
- 結合
- ウィンドウ関数
- GROUPING演算子
- 参考
※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(列, 列);