はじめに
SQLを学びながらboostnoteにまとめていた記事をリファクタリングした。
SQL叩く時はこれを参考にしている
ポイント
元の表がどういうもので、新しくどのような表を作りたいのかイメージする
基本
SELECT カラム名
FROM テーブル名
WHERE 条件
重複なし
SELECT DISTINCT カラム名
条件
条件が複数
WHERE 条件1 AND 条件2
どちらかの条件が当てはまったら
WHERE 条件 1 OR 条件2
-- role = "パイロット" OR role = "作戦部長" 同じカラムの場合INでまとめる
WHERE role IN ('パイロット', '作戦部長')
-- まとめて否定もできる
WHERE role NOT IN ('パイロット', '作戦部長')
指定した範囲内に条件に当てはまったら
WHERE kawaii BETWEEN 6 AND 8
あいまい検索
WHERE name LIKE "プリン%" -- プリンから始まる
WHERE name LIKE "%プリン" -- プリンで終わる
WHERE name LIKE "%プリン%" -- プリンが含まれる
空のレコード
WHERE role IS NULL;
--空じゃないレコード
WHERE role IS NOT NULL;
表示件数を制限
LIMIT 2; --2件だけ表示
LIMIT 10 OFFSET 10 -- 21件目から10件取得
並び替え
ORDER BY 基準のカラム名 並べ方
-- 昇順 ASC
-- 降順 DESC
SELECT *
FROM eva
ORDER BY kawaii ASC
グループ化
ポイントは
軸になるカラムは全てORDER BYで指定する
処理が行われる順番を意識する
GROUP BY 基準のカラム
-- 指定したカラムに存在する値ごとにまとめる
-- 指定できるのはSELECTで指定したカラムか集計関数
テーブル結合
テーブル間で共通するデータを軸に結合させる
処理が行われる順番を意識する
-- テーブルを結合してからSELECTしている
-- 結合した新しいテーブルから何を取得するかをSELECTに記載
SELECT martians.name, histories.planet
FROM martians AS m
INNER JOIN histories AS h
ON m.id = h.martians_id;
-- 結合先のデータが欠損していても削除せずNULLで出力する (結合元のデータが全て残る)
LEFT OUTER JOIN
SELECT martians.name, histories.planet
FROM martians
JOIN histories
ON martians.id = histories.martians_id;
CASE式
分析用の分娩単位で集計したい時に集計関数(SUM)とGROUP BY句と一緒に使うことで大活躍
条件に応じて集計したい時はCASE式
SELECT CASE WHEN 条件 THEN 値 --この条件の時はこの値
WHEN 条件 THEN 値 --この条件の時はこの値
ELSE 値 END --どれにも該当しなかったらこの値 終わり
-- SELECTするカラムが他にもある場合はENDの後に ,カラム名で追加
FROM テーブル名
例
SELECT
CASE WHEN pref_name = IN ('京都', '大阪') THEN "関西"
WHEN pref_name = IN ('福岡', '佐賀') THEN "九州"
ELSE NULL
END AS district, -- pref_nameのカラム名をdistrictに変更
SUM(population) -- populationを集計する
FROM population
GROUP BY -- SELECTと全く同じCASE式を書く
CASE WHEN pref_name = IN ('京都', '大阪') THEN "関西"
WHEN pref_name = IN ('福岡', '佐賀') THEN "九州"
ELSE NULL
END
サブクエリ
クエリ叩いた結果に対してクエリ叩く
実際に実行されているSQL文を書いてみるとイメージできる
-- サブクエリから実行される
SELECT *
FROM items
WHERE price >= (
SELECT AVG(price)
FROM items
)
SELECT *
FROM items AS i1
WHERE price >=
(
SELECT AVG(price)
FROM items AS i2
WHERE i1.category = i2.categrory
-- 上の１文で合致した方の平均価格で比べることができる
-- 相関サブクエリ
GROUP BY category
);