LoginSignup
3
5

More than 1 year has passed since last update.

@shotarian(T S)

SQLのお作法まとめ

Posted at

はじめに

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になる
3
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
5