SQL全部まとめたい | 基礎編

# はじめに
SQLに出会って４年ほど経って、DB設計も学習レベルとプライベートで何回も経験したし
データベースについて考えるのって面白いよね
~~（実務はやったことない素人）~~
<!-- でも、毎回コピペでSQLをなんとなくでしか覚えていないじゃん -->
というわけで、それなりにまとめてみよう。ついでに応用編も勉強したよ

※基本 `MySQL` ですが、たまに `SQL Server`, `Oracle` が混ざっています。

# SQLの概要
|分類|SQL|役割|
|---|---|---|
|データ操作言語（DML）|SELECT<br>INSERT<br>UPDATE<br>DELETE|データ検索<br>データ追加<br>データ更新<br>データ削除|
|データ定義言語（DDL）|CREATE<br>ALTER<br>DROP|オブジェクト作成<br>オブジェクト変更<br>オブジェクト削除|
|データ制御言語（DCL）|GRANT<br>REVOKE<br>COMMIT<br>ROLLBACK|権限を与える<br>権限を取り消す<br>トランザクションをコミット<br>トランザクションを取り消す|

基礎編では、「データ操作言語」と「データ定義言語」を
応用編では、「データ制御言語」をまとめました
SQL以外のDBに関する理論とかはまた別にしました

* SQL応用編はこちら

https://qiita.com/takekota/items/884ae36790f5fa465475

* DB設計はこちら

https://qiita.com/takekota/items/47e57625e15d4f570b95

# 基礎編（データ操作言語・データ定義言語）
全部の説明書いたら大変なのでいい感じに割愛したい。
実際にやってみる人は、《 [`CREATE文`](#create文) > [`ALTER文`](#alter文) > [`INSERT文`](#insert文) > [`SELECT文`](#select文) > [`UPDATE文`](#update文) > [`DELETE文`](#delete文) > [`DROP文`](#drop文) 》の順番で試すべきだと思うよ。

## データ操作言語
ややこしいけど、それぞれの操作の頭文字をとって「**CRUD**」とか言う
### SELECT文
データを取得（READ）する文
```SQL
SELECT * FROM users;
SELECT name + 'さん' AS 名前, mail AS メール FROM users;
SELECT CONCAT(name, 'さん') AS 名前, mail AS メール FROM users;
SELECT id, salary * 1.75 + 100000 AS ボーナス FROM users;
SELECT DISTINCT department_id AS 部門番号 FROM users;
```
`*` ：すべて
`AS` ：列に対する別名をつける
`+` `CONCAT` ：文字列連結演算子
`+` `-` `*` `/` ：算術演算子
`DISTINCT` ：重複排除

#### ORDER BY句
データの並び替え
```sql
SELECT * FROM users
    ORDER BY department_id ASC
```
`ASC` ：昇順（小さい順）
`DESC` ：降順（大きい順）

[SQLの GROUP BY / ORDER BY には数字を指定しよう](https://qiita.com/ssc-ksaitou/items/4587f8ac62b04a009dd4)

#### WHERE句
データの絞り込み
利用できる演算子は以下13種類
* 比較演算子 `=` `<` `>` `<=` `>=` `<>`
* 論理演算子 `AND` `OR` `NOT`
* `BETWEEN`
* `IN`
* `IS NULL`
* `LIKE`
```SQL
SELECT * FROM users
    WHERE salary < 375000
    OR hire_date BETWEEN '2020/04/01' AND '2024-03-31'

SELECT * FROM users
    WHERE name LIKE '_田%'
```
`_` ：任意の１文字
`%` ：ワイルドカード

**[集計関数などは後述してます](#集計関数とグループ化)**

### INSERT文
データを挿入（CREATE）する文
```SQL
-- 基本構文
INSERT INTO users (name,department_id,state)
    VALUES('okano',1,0);
-- 全カラム指定
INSERT INTO users
    VALUES('tanisaki',1,NULL,NULL,NULL,0);
-- 複数行
INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);
```
AUTO_INCREMENTとNULL許容の列は省略可
すべての列を指定する場合は列名の記述を省略可

### UPDATE文
データを更新（UPDATE）する文
```SQL
UPDATE users SET name = 'sakurai' WHERE id = 3;
```

### DELETE文
データを削除（DELETE）する文
WHERE句を省略した場合、すべての行が消えるので注意
```SQL
DELETE FROM users WHERE id = 1;
```

## データ定義言語
### CREATE文
オブジェクト（データベースやテーブル）を作成する文
1. **データベースを作成**
    ```SQL
    CREATE DATABASE sampleDB;
    ```
2. **使用するデータベースに移動**
    MySQL の場合 SHOW が使えて便利
    ```SQL
    SHOW DATABASES;
    USE sampleDB;
    SHOW TABLES;
    ```
3. **テーブルを作成**
    ```SQL
    CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` varchar(255) NOT NULL,
    `department_id` int(11) NOT NULL,
    `salary` int(11),
    `mail` varchar(255),
    `hire_date` date,
    `state` int(11) NOT NULL
    );

    CREATE TABLE `departments` (
    `id` int(11) NOT NULL COMMENT '部署ID' AUTO_INCREMENT PRIMARY KEY,
    `name` varchar(255) NOT NULL COMMENT '部署名',
    `state` int(11) NOT NULL COMMENT '状態(0:通常,1:削除)'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '部署';
    ```
    * バッククォートはいらない
    * コメントがあるとDBMSやデータベースクライアントで見たときにわかりやすい
    * ENGINE や DEFAULT CHARSET はデータベースの設定に依存するため、明示的に指定する必要はない
    * データ型: INT(11) はMySQLにおいて、単に INT としても同じ意味だが、正確性のために表示桁数を明示している
    * [データ型についてはこちら](https://qiita.com/takekota/items/e8385d3c59bc6dc44ed2)
4. **VIEWテーブルの作成**
    ビューとは仮想的な表のこと
    テーブルと同じように利用できる「SELECT文」をデータベース内に定義しておく感じ
    テーブル結合めんどいやつとか、アクセス制限したいやつとかに使う
    [ビューとストアドプロシージャの比較と使い分け](https://ittrip.xyz/sql/sql-view-vs-stored-proc)
    ```SQL
    CREATE VIEW view_show_users AS
    SELECT
    u.id AS user_id,
    u.name AS user_name,
    d.name AS department_name
    FROM users u
    LEFT JOIN departments d
    ON u.department_id = d.id
    WHERE u.state = 0
    ;
    ```
5. **中間テーブル**
    カーディナリティが多対多のとき必要（1対多と多対1にわける）
    ```sql
    CREATE TABLE user_departments (
        user_id INT NOT NULL,
        department_id INT NOT NULL,
        PRIMARY KEY (user_id, department_id),
        FOREIGN KEY (user_id) REFERENCES users(U_id),
        FOREIGN KEY (department_id) REFERENCES departments(D_id)
    ) COMMENT '中間テーブル社員部署';
    ```
6. **外部キー制約**
    1対多のときは多のほうのテーブルにつけます
    1対1の関係では、どちらのテーブルに外部キー制約を設定しても構いませんが、通常は「子」側のテーブルに外部キー制約を設定することが一般的
    ```sql
    CREATE TABLE profiles (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT UNIQUE,
        -- その他のプロフィール情報のカラム
        FOREIGN KEY (user_id) REFERENCES users(id)
    );
    ```
    UNIQUE：指定されたカラムの値が重複しないようにする

### ALTER文
オブジェクト（データベースやテーブル）を変更する文
~~テーブル制約以外あんまりやったことないから適当に書いとこ~~
* 基本構文
    ```
    ALTER TABLE table_name action column_name data_type;
    ```
    `table_name` ：操作対象のテーブル名
    `action` ：おこなう操作（例えばADD、DROP、ALTER、RENAMEなど）
    `column_name` ：操作対象の列名
    `data_type` ：データ型を表す
    ```sql
    ALTER TABLE departments ADD COLUMN created_at date;
    ALTER TABLE departments ALTER COLUMN created_at TYPE int(11);
    ALTER TABLE departments RENAME COLUMN created_at TO color;
    ALTER TABLE departments DROP COLUMN color;
    ```

* users.id をオートインクリメントに変更
    ```sql
    ALTER TABLE users ADD PRIMARY KEY (id);
    ALTER TABLE users CHANGE id id INT AUTO_INCREMENT;
    ```

* テーブル制約をつける
    ```sql
    -- 1対NのNの方のテーブルにキー制約をかける。
    ALTER TABLE `users`
    ADD CONSTRAINT `departments_users` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`);
    ```
    バッククォートはいらない

### DROP文
オブジェクト（データベースやテーブル）を削除する文
中身ごと消え去るので注意
```sql
DROP TABLE users;
DROP DATABASE sampleDB;
DROP VIEW view_show_users;
```

## 集計関数とグループ化
### 集計関数： `SUM` `AVG` `MAX` `MIN` `COUNT`
これでいろんな統計がとれますね
|集計関数|意味|
|---|---|
|SUM|合計|
|AVG|平均|
|MAX|最大値|
|MIN|最小値|
|COUNT|行数のカウント|
```sql
SELECT
    SUM(salary) AS みんなの給与の合計,
    AVG(salary) AS みんなの給与の平均,
    AVG(ISNULL(salary,0)) AS みんなの給与の平均(NULLの人も含める),
    COUNT(*) AS 人数
    COUNT(mail) AS メール登録している人数
    FROM users;
```

### グループ化 `GROUP BY`
グループごとの分析ができちゃう
構文
```
SELECT 列名 1, 列名 2, …
    FROM テーブル名
    WHERE 検索する行の条件
    GROUP BY 列名 1, 列名 2, ･･･
    ［ HAVING 絞込み条件 ］
```
```sql
SELECT AVG(salary), department_id
    FROM users
    GROUP BY department_id
```
集計関数じゃなくて列名指定したら一番上の行の値を取得する
（トランプをまとめたら一番上のカードしか見えないイメージ）
```sql
-- メール登録してる人が５人以上いる部署を取得
SELECT department_id
    FROM users              -- usersデッキを用意
    WHERE mail IS NOT NULL  -- メール登録してないカードを排除
    GROUP BY department_id  -- 部署ごとに分ける
    HAVING COUNT(*) >= 5    -- ５枚未満の部署を排除
    ORDER BY COUNT(*) DESC  -- 多い順に並べる
```

## LIMIT, OFFSET
件数や開始位置の指定
`LIMIT` ：表示件数
`OFFSET` ：スキップ
```sql
-- 1～13 を抽出
SELECT id FROM users LIMIT 13; -- 13行表示
-- 6～9 を抽出
SELECT id FROM users LIMIT 5,4; -- インデックスの5から4行表示
SELECT id FROM users LIMIT 4 OFFSET 5; -- 5行飛ばして4行表示
```

## JOIN（テーブル結合）
条件を先に考慮してからSELECTする。メモリを圧迫しない
* 内部結合 `INNER JOIN`
    条件に合致する行のみ取得
    ```sql
    SELECT 選択リスト
        FROM 表A a
        INNER JOIN 表B b
        ON a.結合キー列 = b.結合キー列
    ```
    下記の書き方もありえる
    ```sql
    SELECT 選択リスト
        FROM 表A a, 表B b
        WHERE a.結合キー列 = b.結合キー列
    ```
* 外部結合 `OUTER JOIN`
    片方の表にしかないデータも取得する。空きは `NULL` で埋められる
    ```sql
    SELECT
        u.id AS user_id,
        u.name AS user_name,
        d.name AS department_name
        FROM users u
        LEFT OUTER JOIN departments d
        ON u.department_id = d.id
        WHERE u.state = 0
    ;
    ```
    「`LEFT`, `RIGHT`, `FULL`」の中から選ぶのじゃ

* `JOIN ... ON ...` を複数書いて無限に結合できる
* 自己結合（同じ表を結合）とかもできる
* `OUTER` は省略可（`LEFT JOIN`, `RIGHT JOIN`, `FULL JOIN`）
* `OUTER JOIN` のみ記述したら、デフォルトの `LEFT JOIN` になる
* `JOIN` のみ記述したら、デフォルトの `INNER JOIN` になる


## SELECT文の基礎まとめ
**SELECT文の基礎句全盛**
記述の順番と処理の順番は違うよ
|記述順|処理順|説明|
|---|---|---|
|SELECT|6|選択|
|FROM|1|テーブル決定|
|JOIN|2|結合|
|WHERE|3|絞り込み|
|GROUP BY|4|グループ化|
|HAVING|5|グループの絞り込み|
|ORDER BY|7|並び替え|
|LIMIT|8|行数制限|

[参考記事](https://qiita.com/k_0120/items/a27ea1fc3b9bddc77fa1)


## サブクエリ（副問い合わせ）
直積（全組み合わせ表）を作った後にSELECTする（重いよ）
```sql
SELECT 選択リスト
    FROM 表A a
    WHERE 列 = (SELECT文)
```

```sql
SELECT *
    FROM 表A a
    WHERE salary = (SELECT MAX(salary) FROM 表B)
```

### JOINとサブクエリの違い
| 項目 | 結合（JOIN） | 副問い合わせ（サブクエリ） |
| --- | --- | --- |
| **処理の流れ** | まず**2つのテーブルを結合**（場合によっては直積）し、その後フィルター | 先に**内側のSELECT（サブクエリ）で条件抽出**してから外側に渡す |
| **直積の可能性** | あり（特に古い書き方では直積を生成してからWHEREで絞る） | 原則なし（条件が先に処理される） |
| **メモリ使用量** | 多い（結合前に全行がメモリ上に展開されることもある） | 少ない（必要なデータのみを先に抽出） |
| **可読性** | JOIN句の方が明示的で可読性が高い | 複雑になるとネストが深くなって読みにくくなることも |
| **最適化の余地（DBMS）** | 最適化しやすい（インデックス活用やJOIN順の最適化が可能） | 一部のRDBMSでは最適化が制限される場合もある |

## UNION（連結）（和集合）
複数のSELECTをまとめて表示する
同じ数の列数じゃないとできないよ
```sql
SELECT文1 UNION [ALL] SELECT文2
```
`UNION` ：重複しているものは一つだけ表示
`UNION ALL` ：重複しているものも全て表示
`UNION [ALL] SELECT文2` を複数書いて無限に連結できる
```sql
SELECT * FROM users WHERE id in (1,3,4)
    UNION ALL
    SELECT * FROM users WHERE salary = 450000
;
```

**INTERSECT（積集合）** や **EXCEPT（差集合）** は結合でできるっぽい
[参考記事](https://qiita.com/Nyokki/items/ab2388789d0a4f440e8c)


## CASE 式
* プログラム言語の Switch みたいな条件分岐ができる
    ```sql
    -- 基本構文
    CASE［ 式 ］
        WHEN 条件 1
            THEN 条件 1 を満たしている場合の値
        WHEN 条件 2
            THEN 条件 2 を満たしている場合の値
        ELSE
            すべての条件を満たしていない場合の値
        END
    ```
    ```sql
    -- サンプル１
    SELECT name, salary,
        CASE
            WHEN salary >= 100000 THEN '管理職'
            WHEN salary >= 50000 THEN 'スタッフ'
            ELSE 'インターン'
        END AS position
    FROM users;
    ```
    ```sql
    -- サンプル２
    SELECT
        id,
        CASE 部門番号
            WHEN 10 THEN '総務部'
            WHEN 20 THEN '営業部'
            ELSE 'その他'
        END,
        name
    FROM users;
    ```

* SQLのCASE式を用いてORDER BY でソート列を作るテクニック
    ```sql
    SELECT key
        FROM Tests
        ORDER BY CASE key
            WHEN 'B' THEN 1
            WHEN 'A' THEN 2
            WHEN 'D' THEN 3
            WHEN 'C' THEN 4
            ELSE NULL
        END;
    ```
    ソート列も出力させたい場合
    ```sql
    SELECT
        key,
        CASE key
            WHEN 'B' THEN 1
            WHEN 'A' THEN 2
            WHEN 'D' THEN 3
            WHEN 'C' THEN 4
            ELSE NULL
        END AS sort_col
    FROM Tests
    ORDER BY sort_col;
    ```
    ~~（インデント適当すぎ）~~

### クロス集計
2つ以上のカテゴリーに関連するデータを交差させ、各組み合わせの集計を行う
**CASE式**を**SUM関数**の中で使用
```sql
-- 男女別どっちなんだい
SELECT
    breakfast_flag
    ,SUM(CASE gender WHEN 'm' THEN 1 ELSE 0 END) AS male
    ,SUM(CASE gender WHEN 'f' THEN 1 ELSE 0 END) AS female
FROM peoples
GROUP BY breakfast_flag
-- ＋
UNION ALL
-- 全体
SELECT
    NULL AS breakfast_flag
    ,SUM(CASE gender WHEN 'm' THEN 1 ELSE 0 END) AS male
    ,SUM(CASE gender WHEN 'f' THEN 1 ELSE 0 END) AS female
FROM peoples
;
```
[参考記事](https://qiita.com/Takayuki_Nakano/items/b28ed4f6539d8576fdcc)


## COALESCE関数
データの欠失値をデフォルト値に置換
```sql
COALESCE(列a,0) -- 列aの値がNULLなら0に置換
```


## OVER句（ウィンドウ関数）
データの順序（ランキング）を取り扱ったり、集計する範囲を指定したりする
集約関数を使いたいが各行の値も取得したい時、`GROUP BY` ではなく `OVER` を使う
グループ化せずに様々な集計が可能となる

```sql
-- 基本構文
関数名(列名) OVER(
    [PARTITION BY(分割する列名リスト)]
    [ORDER BY(整列列名リスト)]
    [フレーム句]
)
```

* `関数名` ：集約関数もしくはウィンドウ関数
    《ウィンドウ関数の種類（一部）》
    * `ROW_NUMBER()` ：各行に一意となる行番号を付与
    * `RANK()` ：ランキング（同率は番号を飛ばす）を付与
    * `DENSE_RANK()` ：ランキング（同率は番号を飛ばさない）を付与
    * `LAG(列名[,n])` ：n 行前を取得（n 省略時は１）
    * `LEAD(列名[,n])` ：n 行後を取得（n 省略時は１）
* `PARTITION BY` ：データをどのようにグループ化するかを指定
* `ORDER BY` ：データをどのような順序で処理するかを指定
* `フレーム句` ：
    * `CURRENT ROW` ：現在の行
    * `n PRECEDING` ：n 行前
    * `n FOLLOWING` ：n 行後


```sql
-- サンプル１
-- 給与のランキングを表示
SELECT 
    id,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM users;

-- サンプル２
-- 各行の前後2つの行に対して平均給与が計算
SELECT 
    id,
    salary,
    AVG(salary) OVER (
        ORDER BY salary
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS avg_salary
FROM users;
```

## ROLLUP,CUBE
階層的な集計を行うことができる
CUBEは多次元データベースの分析
[参考記事](https://zenn.dev/gdtypk/articles/2021-09-22-074320)

## WITH句（一時テーブル、一時ビュー）
副問い合わせに使う仮想テーブルをあらかじめ作る
```sql
WITH [RECURSIVE]
    問合せ名・テーブル名(列名リスト)
    AS(問合せ内容)
```

可読性UP
[参考記事](https://qiita.com/tetocha/items/cb10996b90dcc7aabe25)

## PIVOT
行列変換（縦横を入れ替える）
データの可視化や分析において重要らしい
ピボットテーブルのVIEWを作成すると効率化なる
MySQLでは直接サポートされてない