SQLのお作法まとめ

はじめに

SQLほぼ初心者の私が、1日かけて覚えたお作法について共有します。
なお、テーブル名、変数等につきましては著作権の関係上割愛させていただきます。

SQLとは

SQLとは、データーベースに対する命令を記述するための言語です。
大量のデータに対して、素早く操作することができるため、データベース操作において重宝される言語です。
今回はそんなSQLの一部の操作についてまとめてみました。
なお、SQL文の最後には必ずセミコロン(;) をつける必要があるため、注意しましょう。

SELECT文

1-1 SELECT① 特定フィールドの抽出

• SELECT：どの項目(列)のデータを取得するかを指定
• FROM：どのテーブルを参照するかを指定
• コメントアウト
  • /* (コメント) */：複数行
  • -- (コメント)：一行
• LIMIT：取得するデータ件数を制限

/*
table_a から id, user_id の2つのフィールドに対して5件分のデータを取得する
*/
SELECT 
    id, 
    user_id
FROM
    table_a
LIMIT
    5;

1-2 SELECT② COUNT, DISTNCE, AS

• COUNT：レコード(行)の件数を求める
• DISTINCT：重複行を除外する
• AS
  • 列名やテーブル名に別名をつける
  • [列名orテーブル名] AS [別名]
• 予約語
  • SELECT, FROM, USERなどはSQLの機能として特別な意味を持つため、そのまま列名として使用できない
  • 列名として使用する場合は、 '(バッククォート)で囲む

/*
table_a の user_id フィールドに対して、
「重複を許した場合の件数」、「重複を除いた場合の件数」
をそれぞれ取得する
*/
SELECT  
    COUNT(user_id) AS count_user,
    COUNT(DISTINCT user_id) AS distinct_user
FROM
    table_a;

1-3 SELECT③ ORDER BY, SELECT *

• *：全てのフィールドを選択する
• ORDER BY
  • 指定したフィールドで検索結果を並び替える
  • ORDER BY [フィールド名] ASC(DESC)
  • ASC：昇順、DESC：降順

/*
table_a の 全てのフィールドを5件分、データを取得する
login_day について昇順に並び替えて表示する
*/
SELECT *
FROM
    table_a
ORDER BY
    login_day
LIMIT
    5;

WHERE(条件検索)

① 比較条件

• <, >, <=, >=, =, <> など
• 例) WHERE birthday >= '1999-04-05'：日付の場合「誕生日が1999年4月5日以降」

② 論理条件

• AND, OR, NOT
• AND, ORの優先順位
  • AND → OR の順に処理される

③ パターンマッチングLIKE

• パターン文字%, _(アンダースコア)
• 例) WHERE shop_name LIKE '%コンビニ%' ：shop_nameに「コンビニ」を含む文字列の抽出

パターン文字	意味
%	任意の0文字以上の文字列
_(アンダースコア)	任意の1文字

④ 範囲条件BETWEEN

• 例) WHERE login BETWEEN '2010-01-01' AND '2015-12-31'

⑤ IN条件

• IN (値1, 値2, 値3, ...) ：　ある値が列挙した値のどれかと等しいかを判定
• NOT IN (値1, 値2, 値3, ...) ：ある値が列挙した値のどれとも一致しないことを判定
• ANY (値1, 値2, 値3, ...)：値リストと比較して、いずれかが真なら真
• ALL (値1, 値2, 値3, ...)：値リストと比較して、すべて真なら真

/*
table_b に対して、「条件」に合致するフィールドを取得する
*/
SELECT *
FROM
    table_b
WHERE
    「条件」

GROUP BY

① 集計関数

集計関数	意味
COUNT	レコード数
SUM	合計値
AVG	平均値
MAX	最大値
MIN	最小値
VARIANCE	分散
STDDEV	標準偏差

• グループ関数においてSELECTで指定できるもの
  1. GROUP BYで指定されている基準列
  2. 集計関数の集計対象

/*
table_b で、receipt_idごとにグループ化したレコードに対して、レコード数とpriceの合計・平均・最大を求める。
ただし、idが 100 と 1000 のデータを対象とし、レコード数の昇順に並び替えて表示
*/
SELECT 
    receipt_id,
    COUNT(*) AS 'count',
    SUM(price) AS sum_price,
    AVG(price) AS avg_price,
    MAX(price) AS mas_price
FROM
    table_b
WHERE
    receipt_id IN (100, 1000)
GROUP BY
    receipt_id
ORDER BY
    'count';

② HAVING集計結果

• HAVINGは、集計結果に対して条件抽出を指定する

/*
table_b で、receipt_idごとにグループ化し、priceの平均を求め、
その平均が 500 であるものを5件取得する。
*/
SELECT 
    receipt_id,
    ROUND(AVG(price), 0) AS avg_price
FROM
    table_b
WHERE
    price >= 0
GROUP BY
    receipt_id
HAVING
    AVG(price) = 500
LIMIT 5;

• ROUNDは、指定した桁数に数値を丸める。
  • 例) ROUND(12.15, 0) → 12
• WHERE と HAVING の違い
  • WHERE
    • 元の表に対する絞り込み条件
    • グループ化する前の抽出条件
  • HAVING
    • 集計結果に対する絞り込み条件
    • グループ化した後の抽出条件

SQLの実行順序

1. FROM で処理対象テーブルを選択
2. WHERE による絞り込み
3. GROUP BY によるグループ化
4. HAVING による絞り込み
5. SELECT
6. ORDER BY によるソート
7. LIMIT による絞り込み

条件式

① CASE

• 条件分岐で値を変換する

CASE WHEN 条件1 THEN 返す値
     WHEN 条件2 THEN 返す値
     ELSE 上記に合致しないときに返す値
END (as 別名)

② COALESCE

• NULLとは
  • 何も格納されていない、未定義
  • 0や空白文字とも異なる
• NULL判定
  • IS NULL／IS NOT NULL
  • 比較演算子(=)では、NULLの判定はできない！！
• COALESCE(コアレス)
  • 引数のうち、最初に現れたNULLでない引数を返す

COALESCEの例	返り値
COALESCE(NULL, 1, 2)	1
COALESCE(NULL, 1, NULL)	1
COALESCE(NULL, NULL, 2)	2

型変換CAST

• CAST
  • データ型を変換する
  • CAST(フィールド名 AS 変換後のデータ型)
• 文字列varchar型 → date型の変換

SELECT 
    '20180402' AS v_date,
    CAST('20180402' AS DATE) AS d_date;

サブクエリ

サブクエリとは

• 他のSQLの一部分として登場するSELECT文のこと(別名：副問い合わせ)

-- 例
SELECT name
FROM table_b
WHERE
    price = (SELECT MAX(price) FROM table_b)  -- この部分をサブクエリと呼ぶ
;

複数行サブクエリ

• 検索結果が n行1列 となるサブクエリ
• IN演算子やANY・ALL演算子を用いる

SELECT id, COUNT(id)
FROM table_a
WHERE 
    id IN (SELECT DISTINCT user_id 
           FROM table_b 
           WHERE name = 'ビール')
-- (以下省略)

表形式の値を返す

• 検索結果が n行m列 の表となるサブクエリ

/*
レシートidと、priceの合計を表形式にまとめたtbl
*/
FROM 
    (SELECT receipt_id,
            SUM(price) AS sum_price
    FROM table_b
    GROUP BY receipt_id) 
AS tbl

WITH句 サブクエリの代用

• サブクエリに当たる部分をもとのSQLと切り離す
• WITH句の場合は、構造が分割されるので可読性が高い
• WITH句はカンマで区切ることで複数続けて書くこともできる
  • 例) WITH tblA AS(...), tblB AS(...) SELECT ...;

WITH tbl AS (
    SELECT receipt_id,
           SUM(price) AS sum_price
    FROM table_b
    GROUP BY receipt_id
) 
SELECT 
    AVG(sum_price)
FROM 
    tbl;

テーブルの結合

• 複数のテーブルを、共通項目(キー)を用いて1つにまとめること

INNER JOIN(内部結合)

• 内部結合
  • 共通項目をキーとし、一致するレコードのみを取り出す
• 基本構文

FROM テーブルA (左表)
INNER JOIN テーブルB (右表)
ON 結合条件

• .(ドット)の意味
  • 日本語の助詞「〜の」と解釈すると分かりやすい
  • 例) u.id：「u(user)テーブルのidフィールド」

SELECT
    r.id AS receipt_id,
    u.id AS user_id,
    r.shop_name, u.state_code
FROM 
    receipt_table AS r
INNER JOIN
    user_table AS u
ON r.user_id = u.id
-- (以下省略)

LEFT JOIN(外部結合)

• 左外部結合
  • 対応するレコードがない場合でもレコードが削除されない結合
• 基本構文

FROM テーブルA (左表)
LEFT JOIN テーブルB (右表)
ON 結合条件

• 対応するレコードがない場合
  • すべてNULLになる