SQLの備忘録


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

## コメント
```sql
-- コメント
/*
   複数行
　 コメント
*/
```
## 条件分岐
### 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
和集合

- 重複を削除する場合

```sql
/* BLUE + RED */

SELECT * FROM BLUE
UNION
SELECT * FROM RED ;
```

- 重複を削除しない場合 

```sql
/* BLUE + RED */

SELECT * FROM BLUE
UNION ALL
SELECT * FROM RED ;
```

### EXPECT
差集合

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

SELECT * FROM BLUE
EXCEPT
SELECT * FROM RED ;
```


### INTERSECT
積集合

```sql
/* BLUE * RED */
 
SELECT * FROM BLUE
INTERSECT
SELECT * FROM RED ;
```



## 集計関数

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

## 例

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

```sql

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


```

```sql
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

```

```sql
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
```

```sql
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 | チャーリー・ブラウン | <NULL> |
| 00002 | ペパーミント・パティ | <NULL> |
| 00003 | ルシール・ヴァン・ぺルト | 00001 |
| 00004 | ライナス・ヴァン・ぺルト | 00003 |
| 00005 | シュローダー | 00001 |
| 00006 | マーシー | 00002 |

```sql 
SELECT
     部下.従業員コード
     ,部下.従業員名
     ,上司.従業員名 AS 上司の名前
FROM
     従業員 AS 部下
JOIN 
     従業員 AS 上司
ON
     部下.上司コード = 上司.従業員コード
ORDER BY
    部下.従業員コード
```


| 従業員コード | 従業員名 | 上司の名前名 |
|:--|:--|:--|
| 00003 | ルシール・ヴァン・ぺルト | チャーリー・ブラウン |
| 00004 | ライナス・ヴァン・ぺルト | ルシール・ヴァン・ぺルト |
| 00005 | シュローダー | チャーリー・ブラウン |
| 00006 | マーシー | ペパーミント・パティ |


## Gist

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

```sql

/*
  　パターン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)の値をもつレコードを取得し、その中に既読がないもののカウント

```sql
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/