SQLのお作法まとめ


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

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

## SELECT文
### 1-1 SELECT① 特定フィールドの抽出
* ```SELECT```：どの項目(列)のデータを取得するかを指定
* ```FROM```：どのテーブルを参照するかを指定
* コメントアウト
    * ```/* (コメント) */```：複数行
    * ```-- (コメント)```：一行
* ```LIMIT```：取得するデータ件数を制限

```sql
/*
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の機能として特別な意味を持つため、そのまま列名として使用できない
    * 列名として使用する場合は、 ```'```(バッククォート)で囲む
```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：降順
```sql
/*
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, ...)```：値リストと比較して、**すべて**真なら真

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

## GROUP BY
### ① 集計関数
| 集計関数 | 意味 | 
|:-----------|:------------|
| COUNT | レコード数 |
| SUM | 合計値 | 
| AVG | 平均値 |
| MAX | 最大値 |
| MIN | 最小値 |
| VARIANCE | 分散 |
| STDDEV | 標準偏差 |
* グループ関数においてSELECTで指定できるもの
    1. GROUP BYで指定されている基準列
    2. 集計関数の集計対象

```sql
/*
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```は、**集計結果に対して**条件抽出を指定する

```sql
/*
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``` の違い
    * <font color="Red">WHERE</font>
        * 元の表に対する絞り込み条件
        * グループ化する前の抽出条件
    * <font color="Red">HAVING</font>
        * 集計結果に対する絞り込み条件
        * グループ化した後の抽出条件


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


## 条件式
### ① ```CASE```
* 条件分岐で値を変換する
```sql
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型の変換
```sql
SELECT 
    '20180402' AS v_date,
    CAST('20180402' AS DATE) AS d_date;
```


## サブクエリ
### サブクエリとは
* 他のSQLの一部分として登場するSELECT文のこと(別名：副問い合わせ)
```sql
-- 例
SELECT name
FROM table_b
WHERE
    price = (SELECT MAX(price) FROM table_b)  -- この部分をサブクエリと呼ぶ
;
```

### 複数行サブクエリ
* 検索結果が n行1列 となるサブクエリ
* IN演算子やANY・ALL演算子を用いる
```sql
SELECT id, COUNT(id)
FROM table_a
WHERE 
    id IN (SELECT DISTINCT user_id 
           FROM table_b 
           WHERE name = 'ビール')
-- (以下省略)
```

### 表形式の値を返す
* 検索結果が n行m列 の表となるサブクエリ
```sql
/*
レシート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 ...;```

```sql
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(内部結合)
* 内部結合
    * 共通項目をキーとし、一致するレコードのみを取り出す
* 基本構文
```sql
FROM テーブルA (左表)
INNER JOIN テーブルB (右表)
ON 結合条件
```
* .(ドット)の意味
    * 日本語の助詞「〜の」と解釈すると分かりやすい
    * 例) ```u.id```：「u(user)テーブルのidフィールド」
```sql
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(外部結合)
* 左外部結合
    * 対応するレコードがない場合でもレコードが削除されない結合
* 基本構文
```sql
FROM テーブルA (左表)
LEFT JOIN テーブルB (右表)
ON 結合条件
```
* 対応するレコードがない場合
    * すべてNULLになる