初めに
本記事では、PostgreSQL固有の機能・命令とは言わずSQLの基本的な命令・使い方について記述していきます。記事の前半はPostgreSQLの特徴をつらつらと述べておりますのでSQLでのデータ操作を閲覧したい方につきましては、こちらから参照してください！
PostgreSQLの読み方は「ポストグレスキューエル」だそうです。
PostgreSQL公式ロゴに採用されているこの像の名前はSlonik（スロニク）といい、ロシア語で「小さな像」を意味しているそうです。
基本的な情報
PostgreSQLは、オープンソースの関係データベース管理システム(RDBMS)です。UNIX系のOSやWindowsOSにも対応しています。
SQLでは、DML（データ操作言語）、DDL（データ定義言語）、DCL（データ制御言語）の３つの要素から成り立っている言語であり本記事では、データの検索、新規登録、更新、削除を行うための言語であるDMLについてまとめていきたいと思います！
PostgreSQLの特徴
以下の記事を参考に、要約する形で記載させていただきました。
詳細について気になる項目があればぜひこの記事を参考にしてみてください！
自動的に更新可能なビュー
PostgreSQLでは、トランザクションがビューを提供するデータを更新した時に、問い合わせの結果のビューが自動的に更新される機能を提供します。
マテアライズドビュー
指定されたクエリの結果を含むデータ・オブジェクトのことを指します。
通常のビューは、物理的にデータを保存しないのに対してマテリアライズドビューでは、クエリを実行した結果をディスクに保存します。これにより高速なクエリ応答や負荷の分散が期待されています。
トリガー
テーブルやビューに対するトランザクションやその他の変更に応じてコードを実行します。
データの整合性を保つためや、ログの記録、自動化された業務ロジックの実行など、さまざまな目的で使用されます。
ストアドプロシージャ
一般的に使用されるデータ検証、データ操作、アクセス制御、その他のメソッドへのアクセスをアプリケーションに提供します。
ストアド・プロシージャはユーザーによって定義され、必要なときに実行されます。
パブ / サブ論理レプリケーション
PostgreSQLインスタンスはアプリケーションとイベントコンシューマの間でイベントキャッシュとして動作します。？？
書いてはいるものの理解できておりません。(どなたか教えてください)
データ取り込みの変更
以前のデータベース変更を巻き戻して再生することができるそうです。
テキスト検索
PostgreSQLは、テキスト内容に基づいて列の値を検索するSQL問い合わせをサポートすることができます。
再帰クエリ
再帰クエリを使用することで、親子関係を持つデータやツリー構造を効率的に検索できます。
サポートするための関数が14個提供されているそうです。
json言語のサポート
json言語をサポートしており、json関数を使用することで、その内容を解析したり操作したりすることができます。
同時実行
複数のロックを用いて変更を直列化することで並行ACIDトランザクションをサポートします。
信頼性と災害復旧
フルページイメージをディスクに書き込む前PostgreSQLは、ライトアヘッドログ(WAL)に定期的に書き込みます。これにより、PostgreSQLはログへの書き込みを再生することでクラッシュから回復することができます。
セキュリティ
-
サーババックエンドを起動する際に特定の操作を行わない限り、クライアント接続はデフォルトでローカルの Unix ソケット経由でのみ許可されます
-
PostgreSQLの各ユーザにはユーザ名とパスワードが必要となります。またデータの管理権限を調節することが出来ます
-
特定の列、データパーティション、ネットワーク上での暗号化など、様々な暗号化オプションを提供しています
-
サーバなりすまし防止は、再配置された脆弱なソケットファイルへのシンボリックリンクを作成することで機能します
拡張性
-
カタログ駆動型を採用しており、システムカタログがデータベース自体、セル、列、データ型、関数、アクセスメソッドなどに関する情報を保持します。これはテーブルであり、ユーザが問い合わせ可能で変更可能となっております
-
ユーザはデータベースを直接変更することができ、動的にロード可能なコード変更やユーザ定義関数（UDF）を追加することができる
ここについてもいまいちメリットを理解することが出来ませんでした。使いこなしていない証拠なのでこれから頑張ります！
データ操作
ここからSQLのデータ操作に関する命令についてまとめていきたいと思います。
最初から解説していない言葉が続きますがまずは前提となる書き方について記載します。
【SQLを書く順番】
SELECT → カラムの指定
FROM → テーブル情報
JOIN → 複数のテーブル情報の結合
WHERE → 絞り込み条件指定
GROUP BY → グループ化する条件
HAVING → グループ化してさらに絞り込み条件の指定
ORDER BY → ソート条件指定
LIMIT → 取得する行数指定
【SQLが実行される順番】
FROM
JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
データの抽出
まずは SELECT 文を用いたデータの抽出です。
SELECT カラム名 FROM テーブル名 WHERE 条件式;
次にSELECT文の中で利用される関数を紹介します。
最大値・最小値を求める
最小値には MIN()、最大値は MAX() を列名に対して適用します。
SELECT MIN(カラム名) FROM テーブル名;
SELECT MAX(カラム名) FROM テーブル名;
合計値を求める
何かの合計値を求めるには SUM を用います。
SELECT SUM(カラム名) FROM テーブル名 WHERE 条件;
対象の数を数える
対象となる列を数える場合 COUNT を用います。
SELECT COUNT(*) FROM テーブル名 WHERE 条件;
もう少しテクニカルな使い方があった気がする...
平均を求める
対象の列の平均値を求めるには AVG を用います。
SELECT AVG(カラム名) FROM テーブル名 WHERE 条件;
カラムごとに集計(〇〇)する
集計対象をグループ化してデータを抽出するときは GROUP BY を用います。
SELECT カラム名, COUNT(*) FROM テーブル名 GROUP BY カラム名;
グループ化してさらに絞り込む条件を指定する
GROUP BYの後に抽出するデータを絞る場合 HAVING を用います。
SELECT カラム名, COUNT(*) FROM テーブル名 WHERE 条件
GROUP BY カラム名 HAVING 条件;
ソート条件指定
抽出されたデータを最後に並び替えるには ORDER BY を用います。
昇順(低い順)にしたいときはASC
降順(高い順)にしたいときはDESCを指定します
SELECT カラム名 FROM テーブル名 ORDER BY カラム名 ASC;
SELECT カラム名 FROM テーブル名 ORDER BY カラム名 DESC;
取得する行数指定
結果を取得するデータ数を制限する場合 LIMIT を用います。
指定時の数字の分だけ取得でき、〇,〇と指定すると〇番目から〇番目という分にデータを取得できます
SELECT カラム名 FROM テーブル名 LIMIT 数字;
SELECT カラム名 FROM テーブル名 LIMIT 数字,数字;
サブクエリ
サブクエリ（副問合せ）とは、SQL文の中で別のSQL文を内包するクエリのことです。サブクエリは、SELECT、INSERT、UPDATE、DELETE文の一部として使用され、主にデータのフィルタリングや集計などに使用されます。
SELECT カラム名, カラム名
FROM テーブル１
WHERE カラム名 = (SELECT カラム名 FROM テーブル２ WHERE 条件);
データの登録
データの登録には INSERT を用います。
INTOで登録するテーブルの指定を行いVALUEで登録する値を指定します。
INSERT INTO テーブル名(カラム名１, カラム名２)
VALUES(値１,値２,値３);
データの削除
テーブルの中のデータの削除には DELETE を用います。
WHEREで削除するレコード(行)の条件を指定します。
DELETE FROM テーブル名 WHERE 削除するレコードの条件;
データの更新
テーブル上のレコードの情報に変更を加えるには UPDATE を用います。
UPDATE テーブル名 SET 変更するカラム名 ＝ 値 WHERE 変更するレコードの条件;
UPDATE テーブル名 SET 変更するカラム名１,変更するカラム名２
＝ 値 WHERE 変更するレコードの条件;
複数の結果を同じビューに結合する
抽出してきた複数の結果を同じビューとして結合するには UNION を用います。
UNIONは、各SELECTクエリの結果セットから重複する行を自動的に除外します。重複する行も含めたい場合は、UNION ALLを使用します。
- UNIONの使用例
SELECT カラム名 FROM テーブル名
UNION
SELECT カラム名 FROM テーブル名;
- UNION ALLの使用例
SELECT カラム名 FROM テーブル名
UNION ALL
SELECT カラム名 FROM テーブル名;
とても使い勝手がよく、SQL初心者の私としてはおすすめの演算子です。
比較演算子一覧
ここではWHERE句などでよく使われている比較演算子を紹介します。
等しい
等しいかを比べるときは'='を用います。
SELECT * FROM テーブル名 WHERE カラム名 = 30;
等しくない
等しくないかを比べるときは'!='もしくは'<>'を用います。
SELECT * FROM テーブル名 WHERE カラム名 != 30;
SELECT * FROM テーブル名 WHERE カラム名 <> 30;
より大きくない・より小さい
二つの値の大小を比べるときは'<'もしくは'>'を用います。
SELECT * FROM テーブル名 WHERE カラム名 < 30;
SELECT * FROM テーブル名 WHERE カラム名 > 30;
以上・以下
二つの値の大小（以上、以下）を比べるときは'<='もしくは'>='を用います。
SELECT * FROM テーブル名 WHERE カラム名 <= 30;
SELECT * FROM テーブル名 WHERE カラム名 >= 30;
範囲内かどうか
その値が範囲内にあるか調べるときは'BETWEEN'と'AND'を用います。
SELECT * FROM テーブル名 WHERE カラム名 BETWEEN 30 AND 50;
文字列パターンの一致
その文字列があるパターンに一致するか調べるときは'LIKE'を用います。
SELECT * FROM テーブル名 WHERE カラム名 LIKE 'a%';
ワルドカードの'%'以外にも'_'があります（ほかにもあったかも）
NULLかどうか
その値がNULLなのか調べるときは'IS NULL'もしくは'IS NOT NULL'を用います。
SELECT * FROM テーブル名 WHERE カラム名 IS NULL;
SELECT * FROM テーブル名 WHERE カラム名 IS NOT NULL;
文字列の連結
ある文字列同士を連結するには'||' を用います。
SELECT first_name || ' ' || last_name AS full_name FROM テーブル名;
データ型の変更
あるデータのデータ型を変更したい場合には'CAST'を用います。
SELECT CAST(カラム名 AS 変換先のデータ型) FROM テーブル名;
テーブルの結合について
テーブルの結合は、複数のテーブルからデータを組み合わせるために使用されます。
データを効果的に取り扱うための基本的な操作の一つです。
- 基本的な使い方は以下の通りです
SELECT カラム名
FROM テーブル名
結合条件 テーブル名
ON 結合するカラム = 結合するカラム
以下に結合方法を紹介します。
内部結合
内部結合では両方のテーブルで条件に一致する行を返します。
SELECT カラム名
FROM テーブル名
INNER JOIN テーブル名
ON 結合するカラム = 結合するカラム
外部結合
外部結合では両方のテーブルで条件に一致する行だけでなく一致しない行もNULLで補填することにより、結果を返します。
左外部結合では、指定したテーブル（左側のテーブル）の全ての行を含み、キーに基づいて結合条件を満たす行がある場合にはそれを結合します。
SELECT カラム名
FROM テーブル名
LEFT OUTER JOIN テーブル名
ON 結合するカラム = 結合するカラム
右外部結合では、指定したテーブル（右側のテーブル）の全ての行を含み、キーに基づいて結合条件を満たす行がある場合にはそれを結合します。
SELECT カラム名
FROM テーブル名
RIGHT OUTER JOIN テーブル名
ON 結合するカラム = 結合するカラム
完全外部結合では、指定した2つのテーブルの全ての行を含み、キーに基づいて結合条件を満たす行がある場合にはそれを結合します。
SELECT カラム名
FROM テーブル名
FULL JOIN テーブル名
ON 結合するカラム = 結合するカラム
交差結合
交差結合では、２つのテーブルの取り得る組合せを全て取得します。
SELECT カラム名
FROM テーブル名
CROSS JOIN テーブル名
さいごに
簡単だと思い込み、取り組んでみると思っていたより分量が多く、いいタイピングの練習になりました。SQLのチートシートとして使うにはまだまだ情報量が少ないですが、誰かの参考になればなと思います。