自分用SQLチートシート

タイトル通り自分用のSQLチートシートです。
仕事でたまにSQLを書くことがあり、「たまに」なので忘れていることもあり、自分用のSQLチートシートを作ることにしました。
書き溜めていきます。

DML（Data Manipulation Language）データ操作言語

DML

-- データの検索
SELECT 列名 FROM テーブル名;
// 列名に*, 列名みたいにすることも可能（DB次第？）
// 列名, 列名 + 1のようにすると、変更前後の値がわかる
// 列名1 +  列名2 AS 別列名とかもできる。+はDBによっては||を使う
// 列名にNULLとすることもできる

-- データの更新
UPDATE テーブル名 SET 列名 = 値, 列名 = 値, 列名 = 値;

-- データの追加
INSERT INTO テーブル名(列名, 列名, 列名) VALUES (値, 値, 値);
INSERT INTO テーブル名 VALUES (値, 値); // 列と同じ順で入れれば列名は省略可能
INSERT INTO テーブル名1 SELECT * FROM テーブル名

-- データの削除
DELETE FROM テーブル名 // WHEREで指定しないと全件削除になる

-- DML整理
SELECT 列名 FROM テーブル名;
UPDATE           テーブル名 SET 列名 = 値;
INSERT      INTO テーブル名 (列名) VALUES (値);
DELETE      FROM テーブル名;

C:INSERT(CREATE)
R:SELECT
U:UPDATE
D:DELETE

パターンマッチング

LIKE

SELECT * FROM テーブル名 WHERE LIKE '%値_'

% 任意の0文字以上の文字列
_ 任意の一文字

比較等ごちゃまぜ

-- BETWEEN演算子（範囲）
SELECT * FROM テーブル名 WHERE 列名 BETWEEN 値1 AND 値2;

-- IN演算子（比較）
SELECT * FROM テーブル名 WHERE 列名 IN (値, 値);
SELECT * FROM テーブル名 WHERE 列名 NOT IN (値, 値);

-- ANY / ALL演算子（比較）
SELECT * FROM テーブル名 WHERE 列名 ANY (値, 値); // 値と比較して、いずれかが真なら真
SELECT * FROM テーブル名 WHERE 列名 ALL (値, 値); // 値と比較して、すべてが真なら真

NULLの判定

SELECT * FROM テーブル名 WHERE 値 IS NULL;
SELECT * FROM テーブル名 WHERE 値 IS NOT NULL;

検索結果の加工

DISTINCT

SELECT DISTINCT * FROM テーブル名 

ORDER BY

SELECT * FROM テーブル名
ORDER BY 列名,列名

指定行数の取得ごちゃまぜ

-- OFFSET - FETCH句
-- サポート  :ORACLE
-- サポート外:MySQL
SELECT * FROM テーブル名 ORDER BY 列名,列名
OFFSET 先頭から除外する行数 ROWS
FETCH NEXT 取得行数ROWS ONLY

-- LIMIT
-- サポート外:ORACLE
SELECT * FROM テーブル名 ORDER BY 列名,列名
LIMIT 行数

LIMITは、ORACLE DBではサポートされていないので、ROWNUMを使う。
ROWNUMとORDER BYの併用は副問い合わせを使うこと。
参考

演算子

CASE演算子

-- 検索CASE式（一般的な方）
CASE
    WHEN  列名 = 値1 THEN 値1の時に返す値
    WHEN  列名 = 値2 THEN 値1の時に返す値
    ELSE デフォルト値
END

-- 単純CASE式
CASE 列名
    WHEN  値1 THEN 値1の時に返す値
    WHEN  値2 THEN 値1の時に返す値
    ELSE デフォルト値
END

-- （例）値によって列名の結果が変化する
SELECT
    CASE
        WHEN 列名 = 値1 THEN 返却値1
        WHEN 列名 = 値2 THEN 返却値2
    END AS 返却値
FROM テーブル名

参考：CASE式で条件分岐をSQL文に任せる

関数

LENGTH / LEN （長さ）

SELECT * FROM テーブル名
WHERE LENGTH(列名) 比較演算子 値 // (例) >= 10

空白の除去

-- TRIM(列名)  // 左右の空白を除去
-- LTRIM(列名) // 左の空白を除去
-- RTRIM(列名) // 右の空白を除去

SELECT TRIM(列名) FROM テーブル名

REPLACE

REPLACE(列名, '置換前の文字列', '置換後の文字列')

SELECT REPLACE(列名, '置換前の文字列', '置換後の文字列') FROM テーブル名

文字列内の空白はREPLACEを使う。
REPLACE(列名, ' ', '')

SUBSTRING / SUBSTR

SELECT * FROM テーブル名
WHERE SUBSTRING(列名, 抽出開始位置, 抽出する文字数) = '文字列'

SQLにおけるSUBSTRINGの開始位置は"1"。
つまりSELECT SUBSTRING('123456789', 1, 2);の場合は、12になる。
マイナス値はDBによって扱いが異なる？

COALESCE（コアレス）

-- NULLではない最初の値を返す
COALESCE(1, 2, 3) // 1
COALESCE(NULL, 2, 3) // 2
COALESCE(列名, 0) // NULLが格納されている場合は、0になる

数値の関数

-- ROUND 四捨五入
-- 桁数0の場合は四捨五入
-- 桁数が正の場合は小数
-- 桁数が負の場合は整数
ROUND(列名, 桁数)

-- TRUNC 切り捨て
TRUNC(列名, 桁数)

-- POWER べき乗
POWE(列名, 乗数の値)

日付

-- 現在日付 YYYY-MM-DD
CURRENT_DATE

-- 現在時刻 HH:MM:SS
CURRENT_TIME

データ型

• CHAR型（固定長）
• VARCHAR（可変長）

テーブル結合

SELECT * FROM テーブル名1 AS t1
JOIN テーブル名2 AS t2 ON t1.id = t2.id
AND t1.sub_id <> t2.sub_id