LoginSignup
2
1

More than 5 years have passed since last update.

@mgoldchild

SQLの備忘録

Last updated at Posted at 2015-09-27

SQLの備忘録
ORMを使っていると忘れるのでRDBのSQLのメモ

構文

指定順序と実行順序

The conceptual order of query processing is:

指定順序 実行順序 句 内容
2 1 FROM(Including JOINs) 実行対象のテーブルを指定する
3 2 WHERE テーブルに対してレコードの抽出条件を指定する
4 3 GROUP BY レコードをグループ化する
5 4 HAVING グループ化した結果に対して抽出条件を指定する
1 5 SELECT 取得（表示）する列を指定する
6 6 ORDER BY 取得した列を並び替える

CRUD

コメント

-- コメント
/*
   複数行
　 コメント
*/

条件分岐

CASE式

まとめ系

GROUP BY句

  • 複数のレコードをグルーピングする
  • データ操作には、FROM句とWHERE句の結果が使用される
  • GROUP BYを使った場合、SELECT句に加工なしで使える項目は、GROUP BY句に使った項目に依存する
  • 要はGROUP BY句で指定していない、重複するカラムをSELECT文に集計関数抜きなどでは指定できない

HAVING句

  • GROPU BY用のWHERE句
  • HAVING句の指定はグループ(複数行のレコード)に対して行われる
  • HAVING構文は、集計関数を含めることができる点を除けば、WHERE 構文と同じ
  • WHERE 検索条件がグループ化操作の前に適用されるのに対し、HAVING 検索条件はグループ化操作の発生後に適用される

エイリアス

fields AS name1
(
) AS name2

サブクエリ

  • 主にSELECT, JOIN, FROM, WHERE, HAVINGなどで括弧と共に使う。
  • 相関サブクエリはエイリアスなどを使って、外側の結果を参照したもの
  • 基本的にサブクエリは一つの関数として読む
タイプ 読み方
(非相関)サブクエリ サブクエリの引数はなく。1度だけ実行される
相関サブクエリ サブクエリの引数があるため、引数が変わったタイミングで実行される

ANY, ALL, EXISTS演算子

  • ANY, ALL, EXISTS演算子はサブクエリの「TRUE」または「FALSE」を評価する

ANY(SOME)

  • 比較演算子と共に使用する
  • 結果セットの値のいずれかが比較演算子の関係を満たす場合にTRUEを返す

ALL

  • 比較演算子と共に使用する
  • 結果セットの値の全てが比較演算子の関係を満たす場合にTRUEを返す

EXISTS

  • サブクエリの生成した値が存在する場合はTRUEを返す
  • 否定はNOT EXISTS

DISTINCT

結合系

UNION

和集合

  • 重複を削除する場合
/* BLUE + RED */

SELECT * FROM BLUE
UNION
SELECT * FROM RED ;
  • 重複を削除しない場合 
/* BLUE + RED */

SELECT * FROM BLUE
UNION ALL
SELECT * FROM RED ;

EXPECT

差集合

/* BLUE - RED(BLUEにはあって、REDにはないもの) */

SELECT * FROM BLUE
EXCEPT
SELECT * FROM RED ;

INTERSECT

積集合

/* BLUE * RED */

SELECT * FROM BLUE
INTERSECT
SELECT * FROM RED ;

集計関数

関数名 機能
COUNT 集計対象の行数を出力
MAX 集計対象のなかで最大のものを出力
MIN 集計対象のなかで最小のものを出力
SUM 集計対象の合計値を出力
AVG 集計対象の平均を出力

例

GROPU BYしてその中の最大の値を含む行を取得する


-- table

id  home  datetime     player   resource
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399 
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
3  | 10  | 03/03/2009 | john   | 300
4  | 11  | 03/03/2009 | juliet | 200
6  | 12  | 03/03/2009 | borat  | 500
7  | 13  | 24/12/2008 | borat  | 600
8  | 13  | 01/01/2009 | borat  | 700

-- wants result

id  home  datetime     player   resource 
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
8  | 13  | 01/01/2009 | borat  | 700



SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

別テーブルに存在するIDの合計値を取得したい

- 全社員の売上の合計を表示したい。

社員マスタ
社員コード 社員名   部署コード 年齢
1   テスト社員1    1   22
2   テスト社員2    2   30
3   テスト社員3    null    20

売上明細
売上NO    社員コード 売上日   売上金額
1   1   20150401    200
2   1   20150402    300
3   2   20150408    100
4   2   20150501    150
5   3   20150505    550


SELECT
   MAX(T1.社員名) AS 社員名
   ,SUM(T2.売上金額) AS 売上金額
FROM
  社員マスタ AS T1
LEFT JOIN
  売上明細 AS T2
ON
  T1.社員コード = T2.社員コード
GROUP BY
  T1.社員コード


--実行結果

社員名   売上金額
テスト社員1    500
テスト社員2    250
テスト社員3    550

別テーブルにIDが存在していたら、元のレコードを取得する

- 売上のある社員のみ表示したい。

社員マスタ
社員コード 社員名   部署コード 年齢
1   テスト社員1    1   22
2   テスト社員2    2   30
3   テスト社員3    null    20
4   テスト社員4    1   30

売上明細
売上NO    社員コード 売上日   売上金額
1   1   20150401    200
2   1   20150402    300
3   2   20150408    100
4   2   20150501    150
5   3   20150505    550

SELECT
     社員名
FROM
     社員マスタ AS T1
WHERE
     EXISTS(
          SELECT
               1
          FROM
               売上明細 AS S1
          WHERE
               T1.社員コード = S1.社員コード
     )


--↓EXISTSを使わない場合
SELECT
     MAX(T1.社員名) AS 社員名
FROM
     社員マスタ AS T1
JOIN
     売上明細 AS T2
ON
     T1.社員コード = T2.社員コード
GROUP BY
     T1.社員コード

自己結合

  • テーブルが再帰的(同一テーブルへのparent_idを持っている)時などに有効
従業員コード 従業員名 上司コード
00001 チャーリー・ブラウン
00002 ペパーミント・パティ
00003 ルシール・ヴァン・ぺルト 00001
00004 ライナス・ヴァン・ぺルト 00003
00005 シュローダー 00001
00006 マーシー 00002 
SELECT
     部下.従業員コード
     ,部下.従業員名
     ,上司.従業員名 AS 上司の名前
FROM
     従業員 AS 部下
JOIN 
     従業員 AS 上司
ON
     部下.上司コード = 上司.従業員コード
ORDER BY
    部下.従業員コード
従業員コード 従業員名 上司の名前名
00003 ルシール・ヴァン・ぺルト チャーリー・ブラウン
00004 ライナス・ヴァン・ぺルト ルシール・ヴァン・ぺルト
00005 シュローダー チャーリー・ブラウン
00006 マーシー ペパーミント・パティ

Gist

  • 外部テーブルとJOINしたい時の例

/*
  　パターン1
*/


SELECT
        i.*,
        c.name AS rank
FROM
        inquiries AS i
INNER JOIN
        constants AS c
ON
        c.key = i.rank
WHERE
        i.id = 1
        AND
        c.field = 'RANK'
\G

/*
  　パターン2
*/

SELECT
        i.*,
        c2.name AS rank
FROM
        inquiries AS i
INNER JOIN (
        SELECT
                *
        FROM
                constants  AS c
        WHERE
                c.field = 'RANK'
) AS c2
ON
        c2.key = i.rank
WHERE
        i.id = 1
\G
  • cd値でGroup byし、最小の行(no)の値をもつレコードを取得し、その中に既読がないもののカウント
SELECT
        COUNT(*) AS unread_count
FROM
        letters AS i
INNER JOIN (
        SELECT
                cd,
                MIN(no) AS first_letter_no
        FROM
                letters
        GROUP BY
                cd
) AS i2
ON
        i.cd = i2.cd AND
        i.no = i2.first_letter_no
WHERE 0 = ANY(
        SELECT
                opend
        FROM
                letters AS i3
        WHERE
                i.cd = i3.cd
)
\G;

Index

アソシエーション

1:多

  • 芸能人と一般人のパターン
  • 多が1を知る必要がある

多:多

  • タグ付けのテーブルなどで使われるパターン
  • 2次元では表現できないので、中間テーブルを用意する

1:1

  • 目的ごとに1つのテーブルを分けるパタン
  • 意味論的と変更可能性を考慮して設計する
    • 例えば、UserテーブルとそのUserのCredentialが合った場合、
    • UserがCredentialのIDを所有する
      • 意味論的にusers.credential_idの方がcredentials.user_idより良いから
      • もし、credentials.user_idにすると、credentailを作る時に、userが必要になるから
    • ただし、今後の変更で1:多になるなら
      • つまり、User:Credential = 1:多になる場合はCredentailがUserを知る必要がある
      • スカラ値は正規化ではRDBでは許容されないから

References

http://write-remember.com/archives/4805/
https://dev.classmethod.jp/server-side/db/difference-where-and-having/
https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order
https://qiita.com/suzukito/items/edcd00e680186f2930a8
http://d.hatena.ne.jp/CAMUS/20060626/1151334328
https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql
https://stackoverflow.com/questions/30749913/which-performs-first-where-clause-or-join-clause
http://omachizura.com/sql/GROUP%20BY%E5%8F%A5%E3%81%A7%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E9%9B%86%E8%A8%88%E3%83%BB%E9%9B%86%E7%B4%84%E3%82%92%E8%A1%8C%E3%81%86.html
http://www.techscore.com/tech/sql/index.html/

2
1
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
2
1