自分用SQLチートシート（ 「スッキリわかるSQL入門」まとめ）

書籍を読んだ内容をまとめて、自分用チートシートを作成しました。
元ネタは以下書籍です。
（初心者のため、間違い等ありましたら、お知らせいただけると助かります。）

参考書籍「スッキリわかるSQL入門」

データ型

INTEGER　は数値
VARCHAR　は可変文字列
CHAR(10) は固定文字列 10文字未満の場合は、空欄が追加される。
DECIMAL　小数（他にもある）
TIMESTAMP　日付＋時刻
DATE　日付のみ
TIME　時刻のみ

基本コマンド（コマンドの終わりに　;　を忘れずに。）

SELECT 列名 FROM テーブル名 WHERE 条件;
UPDATE テーブル名 SET 列名=値 WHERE 条件;
DELETE FROM テーブル名 WHERE 条件;
--DELETEの後は列名無し
INSERT INTO テーブル名 (列名) VALUES (値);
--すべての項目がVALUESにあれば列名は省略できる。VALUESは、CHAR型なら’’が必要！

列名に' 'はいらないが、値に文字列を指定するときは' 'が必要。
AS を使うと、列名に「別名」を付けることができる。

WHERE条件

条件A AND 条件B　AかつB　
条件A OR 条件B　AまたはB
--注意！　ANDのほうが先に判定されるので、ORを先に適用するには(A OR B)で囲む。

--比較
<= 以下
>= 以上
<> 一致しない

BETWEEN A AND B　AからBの間（AとB含む。日付の場合使いやすい。）
IS NULL　NULLの値
IS NOT NULL　NULLではない
IN (A,B) AかB

<ANY (サブクエリ)　サブクエリすべてのOR未満
>=ALL (サブクエリ)　サブクエリすべてのAND以上

LIKE '正規表現文字列'　正規表現文字列に該当するか
--正規表現文字列
　% ０文字以上の任意
　_ １文字の任意
　%A Aで終わる。
　%B% Bを含む。

重複・並び替え・TOPランキング

SELECT DISTINCT 列名 FROM テーブル名; 列の重複を削除してリスト化する。
ORDER BY 列名 ASC　昇順
ORDER BY 列名 DESC　降順

OFFSET 数値A ROWS 
FETCH NEXT 数値B ROWS ONLY　--先頭行からA行飛ばして、B行分表示
LIMIT 数値C　--先頭行からC行表示

結果の和集合・差集合・積集合

使い方
・集合を取るときは、列名の数とデータ型が一致しなければならない。
　一致しない場合は、列名にNULLを入れる。
・ORDER BYで並び替えるなら、最後に書く。
　列名は一番上のSELECT文の列名（DBMSによっては、列を左から数えた数値1,2,...で指定しなければならない。）
・ALLを付けると、重複を削除しない。

--和集合 シンプルに両方をまとめる
SELECT 列名 FROM テーブル名 WHERE 条件A
UNION (ALL)
SELECT 列名 FROM テーブル名 WHERE 条件B;

--差集合 Cの表から、Dの表と一致する部分を除外する
SELECT 列名 FROM テーブル名 WHERE 条件C
EXCEPT (ALL)
SELECT 列名 FROM テーブル名 WHERE 条件D;

--積集合 EとFの共通する部分のみ
SELECT 列名 FROM テーブル名 WHERE 条件E
INTERSECT (ALL)
SELECT 列名 FROM テーブル名 WHERE 条件F;

計算

--列名(数値)に100を足して更新する
UPDATE テーブル名 SET 列名=列名 +100;

日付 +5;　5日あとの日付
日付 ―5;　5日まえの日付
日付 -日付;　日付の間の日数

--列名に「です。」を付けて更新する
UPDATE テーブル名 SET 列名=列名 || 'です。';

条件分岐

--書き方１　CASEからEND ASまでを一つの列としてSELECT表示
SELECT　列名,
　CASE 列名 WHEN A THEN C
  　        WHEN B THEN D
    　      ELSE E(デフォルト値)
　END AS 新規でつくる列の別名
FROM テーブル名;

--書き方２　一つの列ではなく、条件式で分岐して表示
SELECT　列名,
　CASE WHEN 条件式A THEN C
  　   WHEN 条件式B THEN D
    　 ELSE E(デフォルト値)
　END AS 新規でつくる列の別名
FROM テーブル名;

関数

--文字数
LENGTH(引数：列名でもよい) AS 新規列名

--空欄削除（CHAR型で余分な空欄を削除）
TRIM(引数：列名でもよい) AS 新規列名

--置換
REPLACE (引数：列名でもよい,'検索文字','置換する文字') AS 新規列名

--一部抽出
SUBSTRING (引数：列名でもよい,抽出を開始する位置,抽出する文字数) AS 新規列名
例：　WHERE SUCSTRING(列名,1,3) LIKE '%A%';　１～３文字目にAを含む

--連結
CONCAT (引数：列名でもよい,連結する文字列) AS 新規列名

--四捨五入（有効とする桁数は、小数点指定なら正の値、整数指定なら負の値）
ROUND (引数：列名でもよい,有効とする桁数) AS 新規列名
例：　ROUND(列名,-3);　100の位で四捨五入

--切り捨て（有効とする桁数は、小数点指定なら正の値、整数指定なら負の値）
TRUNC (引数：列名でもよい,有効とする桁数) AS 新規列名
例：　TRUNC(列名,-2);　10の位で切り捨て

--現在の日時
CURRENT_TIMESTAMP AS 新規列名;　日時
CURRENT_DATE AS 新規列名;　日付
CURRENT_TIME AS 新規列名;　時刻

--型の変換
CAST (引数：列名でもよい AS 新しい型) AS 新規列名

--NULLを代替（明示）して表示する
COALESCE (引数：列名や式) AS 新規列名
例： SELECT 日付,費目,
　　　　　　COALESCE(メモ,'(メモはNULL)') AS メモ,
　　　　　　入金額,出金額,
　　FROM テーブル名

集計関数

--NULLは無視される。全行NULLならNULLとなる。
SUM(列名) AS 列名の合計
AVG(列名) AS 列名の平均
MAX(列名) AS 列名のうち、最大値（文字列は文字コード、日付は最終日）
MIN(列名) AS 列名のうち、最小値（文字列は文字コード、日付は最初の日）

--NULLを０として計算したい場合（COALESCEを使う）
SELECT AVG(COALESCE(列名,0)) AS 列名の平均
FROM テーブル名

--NULLは無視される。全行NULLなら０となる。
COUNT(列名) AS 列名の行数

--NULLは無視されない。
COUNT(*) AS 全行数

SELECT文の基本構文

処理順は、WHERE　➡　GROUP BY　➡　集計関数　➡　HAVING　➡　ORDER BY

※注意　🔲は実行される順番
　--したがって、選択列リストの列は、2⃣ 又は 「選択列リストの集計関数列」と同一でなければならない。
SELECT 選択列リスト（集計関数を書ける）--3⃣　
FROM　テーブル名
WHERE　条件式　　　　　　　--1⃣
GROUP BY グループ化列名　　--2⃣
HAVING 集計関数結果に対する条件式　--4⃣　←ここで指定する列は、1⃣のSELECT選択列の列名とする（ASより前の列名）
ORDER BY 並び替え列名　　　--5⃣

--意訳
SELECT リストを作りたい列名,作ったリストの知りたい集計関数　--3⃣　
FROM　テーブル名
WHERE　行に対する絞り込み条件　　　　　　　--1⃣
GROUP BY リストを作りたい列名「～ごとに」の意味　　--2⃣
HAVING リストに対する絞り込み条件　--4⃣　←ここで指定する列は、1⃣のSELECT選択列の列名とする（ASより前の列名）
ORDER BY 並び替え列名　　　--5⃣

サブクエリ

--INでテーブル2のリスト(NULL無し)をもとにテーブル1を抽出
AllやANYも同様
SELECT * FROM テーブル名1
WHERE 列名 IN (SELECT DISTINCT 列名 FROM テーブル名2) 

--INでテーブル2のリスト(NULLあり)をもとにテーブル1を抽出
SELECT * FROM テーブル名
WHERE 列名 IN (SELECT COALESCE(列名,'NULL代替語') FROM テーブル名2)

--サブクエリから得たテーブルに別名を付ける
SELECT SUM(SUB.列名) AS テーブル名1
FROM (SELECT 列名
      FROM テーブル名2
      UNION
      SELECT 列名
      FROM テーブル名3
      WHERE 条件式) AS SUB;
      
--テーブル2の集計をINSERTして追加する文
--INSERT文のみ、サブクエリの( )は不要
INSERT INTO 集計テーブル名1(列名リスト)
SELECT 列名
FROM テーブル名2
WHERE 条件式
GROUP BY 集計関数のグループ単位

テーブルの結合（JOIN）:

--基本形
SELECT 選択列リスト
FROM テーブルA
JOIN テーブルB
ON 両テーブルの結合条件

注意)
１.右表に該当する行が複数あるとき
左表に対して、右表の該当が複数行あるとき、左表を複製して（増やし て）結合する。
２.右表に結合相手がいないとき（左表の結合条件の列がNULLのとき含む）
結合結果から消える。
３.ON句の複数条件
ONにはWHERE句のように、ANDでさらなるフィルター条件（特定の列が一致、不一致など）を書くことができる。
４.複数テーブル（A,B,C）から同一テーブル（X）を参照と結合する場合
JOIN＋ONは、Xを３回して、それぞれにASで別名を付ける必要がある。（ANDは使えない。）

--副問い合わせの結果と結合するSQL
SELECT 選択列リスト
FROM テーブルA
JOIN (SELECT * FROM テーブルB
       WHERE 条件式) AS テーブルB 
ON 結合条件

--自分自身と結合するSQL（テーブルA内で、列が別の列と結びついているような場合）
--例えば日付と関連日、子と親など
SELECT A.選択列,B.選択列
FROM テーブルA AS A
LEFT JOIN テーブルA AS B 
ON A.選択列 = B.選択列 の結合条件

**注意４の例**
--OK
SELECT S1.社員番号 AS 社員番号, S1.NM AS NM,
       K1.NM AS 本人勤務地, K2.NM AS 上司勤務地
  FROM 社員 AS S1
  JOIN 社員 AS S2
    ON S1.上司ID = S2.社員番号
   AND S1.勤務地ID <> S2.勤務地ID
  JOIN 支店 AS K1　　　　　　　　　←←　１回目
    ON S1.勤務地ID = K1.支店ID　　 ←←　１回目
  JOIN 支店 AS K2　　　　　　　　　←←　２回目
    ON S2.勤務地ID = K2.支店ID;　　←←　２回目

--NG
SELECT S1.社員番号 AS 社員番号, S1.NM AS NM,
       K1.NM AS 本人勤務地, K1.NM AS 上司勤務地
  FROM 社員 AS S1
  JOIN 社員 AS S2
    ON S1.上司ID = S2.社員番号
   AND S1.勤務地ID <> S2.勤務地ID
  JOIN 支店 AS K1
    ON S1.勤務地ID = K1.支店ID　　←←
    AND S2.勤務地ID = K1.支店ID;　←←　

トランザクション

--開始
BEGIN;

--完了
COMMIT;

--BEGINまでキャンセル(ただし，自動コミット機能があると，戻せなくなる)
ROLLBACK;

それぞれの分離レベルで発生する問題　○：発生する　×：発生しない

分離レベル	ダーティリード	ファジーリード	ファントムリード
READ UNCOMMITTED	○	○	○
READ COMMITTED	×	○	○
REPEATABLE READ	×	×	○
SERIALIZABLE	×	×	×

分離レベルを設定

SET TRANSACTION ISOLATION LEVEL 上記の分離レベル
SET CURRENT ISOLATION 上記の分離レベル

ロックはトランザクション内で使う。

--明示的な行ロック
SELECT ... FOR UPDATE NOWAIT
通常は，トランザクション解除まで待つが，NOWAITをつけることで，ロックできなければ即座にエラーとなる。

--テーブルロック
LOCK TABLE テーブル名 IN モード名 MODE NOWAIT;
モード名はEXCLUSIVE(排他ロック),SHARE(共有ロック)

デッドロックを予防する方法

1.トランザクションの時間を短くする

2.行やテーブルを常に同じ順番でロックする

テーブル操作（DDL）

--テーブル作成
CREATE TABLE テーブル名(
	列名０ 型　制約,
	列名１ INTEGER PRIMARY KEY,
	列名２ DATE NOT NULL,
	列名３ VARCHAR(10) DEFAULT '不明' NOT NULL,
	列名４ INTEGER DEFAULT 0 CHECK(列名４>=0),
	列名５ VARCHAR(10) UNIQUE,
	列名６ REFERENCES 外部テーブル名(外部列名),
	PRIMARY KEY(列名１,列名３)　--複合主キーの場合
)

--テーブル列の追加
ALTER TABLE テーブル名 ADD 列名 型
--テーブル列の追加
ALTER TABLE テーブル名 DROP 列名 型

--テーブルの存在を確認してから、操作する
CREATE TABLE IF NOT EXISTS テーブル名(
...)

テーブル権限操作（DCL）

--権限付与
GRANT 権限名 TO ユーザー名

--権限削除
REVOKE 権限名 TO ユーザー名

インデックス

（where句に使用する列で作成することで検索が速くなる。ただし、データ容量を食う。複数行インデックスも可能）

--インデックス作成
CREATE INDEX インデックス名 ON テーブル名(列名)

--インデックス削除
DROP INDEX インデックス名

高速化の効果検証コマンド

EXPLAIN SELECT * FROM テーブル名 WHERE 条件;

ビュー

テーブルから、使用する列・行だけを持った仮想テーブルを別途作成しておける。閲覧権限の設定などに有用。
元のテーブルデータが更新されると、自動で、ビューのデータも更新される。

--ビュー作成（WHEREなどによる条件も追加可能）
CREATE VIEW ビュー名 AS SELECT文

--ビュー削除
DROP VIEW ビュー名

テーブルの自動採番機能

ただし、シーケンスは一部のDBMSのみ。主に、IDに使う。
最近はUUIDを用いるケースも多い。（なお、UUIDは連番ではない。）

INSERT INTO テーブル名（列名）
VALUES (SELECT NEXTVAL('列名シーケンス'))

テーブル設計

概念設計

概念上のエンティティをRDBで使いやすい形のテーブルER図を作成する。

• エンティティ
  テーブルのようなもの
• 属性
  テーブルの列のようなもの
• 関連
  テーブル間の関係

論理設計（正規化）

• 概念テーブルを複数のテーブルに分けて、矛盾しないようにする。

1. 第１正規形
   テーブルのすべての行のすべての列に一つずつ値が入るようにする。（エクセルの碁盤の目に沿うように）
2. 第２正規形
   複合主キー全体へ関数従属させる（複合主キーが一つ決まれば、一つのデータに確定するようにする。複合主キーの一部で決まるものは分ける。）（部分関数従属しないように。）
3. 第３正規形
   主キーのみで、一意に決まるように分ける。主キー以外で一意になるものは分ける。（推移関数従属しないように。）

物理設計

• 主キーや制約やデフォルトなどを設定し、DDLでデータベースを作成する。