【技術書まとめ】『SQL 第2版 ゼロからはじめるデータベース操作』を読んだまとめ

データベース操作を基礎からしっかりと身に付けたいと思ったので手に取った書籍です。
## 対象

データベース初心者や非エンジニア。

## 第０章　イントロダクション

書籍では windows 用の設定だけだったので mac 用にqiita記事を参考にしました。
https://qiita.com/ipepi/items/58dedbc0434fa9ea3b71


## 第１章　データベースとSQL

- データベースとはそもそも何のために使われるのか
    - 表計算ソフトとどう違うのか
- 種類と具体例
- SQLとは何か

実際にSQLを書いてみる

- `create database`
- `create table`
- `drop table`
- `alter table`

練習問題でサクッと`drop table`させるところが面白いなと思いました。練習問題ながら、サクッとテーブルを削除するとどうなるのか身をもって辛さを体験できます。


## 第２章　検索の基本

- `select`
    - `as`でわかりやすい名前で出力する
    - `from`なしで出力することもできる
        - 中身が何でもいいとき
- `'商品' as mojiretsu`で定数を出力する
- `select distinct`で重複を省く
- `where`で絞り込む
    - 必ず`from`の直後に書く
- `--`や`/* */`でコメントを書く
- `select`文中で計算もできる
    - `NULL`が入るときに気をつける
- 文字列型の順序の原則は辞書式
    - 数値の大小とは違う
- `NULL`を判別するには`is null`や`is not null`を使う
- `and`、`or`
    - ベン図は便利
    - `or`より`and`の方が優先される
        - `()`で調整する
    - `NULL`は`不明(UNKNOWN)`となる
        - SQLだけは３値論理となる
            - `NOT NULL制約`で対策


## 第３章　集約と並べ替え

- 集約関数とは
    - `count`
        - 列名を引数にすると`NULL`を除外した数になる
            - count関数だけの特徴
    - `sum`
    - `avg`
    - `max`と`min`
        - どんなデータ型の列にも適用できる

- 集約関数は最初に`NULL`を除外する
    - `count`だけは例外
- 重複値を除きたいときは`distinct`
    - `()`の中に書く
    - `select count(distinct shohin_bunrui) from shohin;`
    - 集計関数ならどれにでも使える
- `group by`
    - ケーキのように切り分ける
        - 商品分類ごとなど
    - 必ず`from`の後ろにおく
    - `NULL`は1つのグループとなる
    - `where`も一緒に使える
        - `where`で先に絞られたレコードから集約される

- よくある間違い
    1. `select`に集約キー以外の列名を書く
        - 1行でグループ化したなら結果も１行になる
        - 書けるもの
            - 定数
            - 集約関数
            - `group by`で指定した列名
    2. `group by`してるときに`as`を使う
        - SQL内部では`group by`が`select`より先になる
            - 別名を知らない
    3. `group by`はソートしてくれる？
        - してくれない
    4. `where`に`count`などを書く
        - 集約関数が書けるのは`select`、`having`、`order by`だけ

- `having`
    - `group by`でグループ分けし、さらに条件を指定して絞りこむ
    - `group by`の後ろに書く
    - 一度集約が終わった段階のテーブルを出発点にしている
    - 行に対する条件は`where`で書いた方がいい
        - `where shohin_bunrui = '衣服'`
        - `where`で絞り込んでからソートした方が速い
        - `where`はインデックスが使える
- `order by`
    - `select`の最後に書く
    - 同順位のデータの並び順を決定するには
        - 複数のソートキーを指定する
    - `NULL`はまとめられる
    - `as`を使える
        - `order by`は`select`より後で実行されるから
    - 列番号は使ってはいけない


## 第４章　データの更新

- `insert`
    - `default`値は明示的な方が良い
- `insert …… select`でデータをコピーできる
    - バックアップに使える
    - `insert`内の`select`ではどんなSQL文でも使える
- `drop table`
- `delete`
    - `delete from shohin;`
    - 使えるのは`where`だけ
- `update`
    - 列を`NULL`で更新する
        - `NULLクリア`
    - 複数更新は`set`列を増やす
- `transaction`
    - ワンセットで行われるべき更新の集合
    - `SQL Server`、`PostgreSQL`では
        - `begin transaction`
    - `MySQL`では
        - `start transaction`
    - `Oracle`、`DB2`では
        - ない
            - 接続時点でトランザクションが始まっているから
- `commit`
    - ファイルでいうところの上書き保存
- `rollback`
    - 保存せずに終了
- ACID特性
    - 原子性(Atomicity)
        - オール・オア・ナッシング
            - `commit`されるか`rollback`されるかどちらかしかない
    - 一貫性(Consistency)
        - 制約違反はエラーになってロールバックされる
            - `NOT NULL制約`
        - 整合性
    - 独立性(Isolation)
        - トランザクション同士は独立している
    - 永続性(Durability)
        - 復旧手段がある
            - 実行ログ


## 第５章　複雑な問い合わせ

- ビュー
    - 実際のデータを保存していないテーブル
        - 保存領域の容量節約できる
        - 使い回しが効く
    - よく使う`select`をビューにして使いまわす
- `create view`
    - `as`を省略しない
    - `order by`は使わない
- サブクエリ
    - 使い捨てのビュー
        - ビュー定義の`select`を`from`に持ち込んだもの
- スカラ・サブクエリ
    - 必ず1行１列だけの戻り値を返すサブクエリ
    - 単一の値が書ける所にはどこでも書ける
        - 集約関数を`where`に書けないため重宝する
    - 絶対にサブクエリが複数行を返さないようにする

```sql
SELECT shohin_id, shohin_mei, hanbai_tanka
  FROM Shohin
 WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
                         FROM Shohin);
```

- 相関サブクエリ
    - 小分けにしたグループ内で比較する
    - `where`で使う戻り値が複数
        - 対応する列ごとに比較したいときに使う
    - **縛る**、**制限する**と呼ばれる
        - 「商品分類で縛って」
    - 結合条件は必ずサブクエリの中に書く
        - 相関名のスコープ
            - `S2`は消える

```sql
--SQL Server、DB2、PostgreSQL、MySQL
SELECT shohin_bunrui, shohin_mei, hanbai_tanka
  FROM Shohin AS S1
 WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
                         FROM Shohin AS S2
                        WHERE S1.shohin_bunrui = S2.shohin_bunrui
                        GROUP BY shohin_bunrui);
```


## 第６章　関数、述語、CASE文

### 算術関数

- `abs`
    - **絶対値**
- `mod`
    - **剰余**
    - SQL SERVER では使えない
- `round`
    - **四捨五入**


### 文字列関数

- `||`
    - **連結**
    - SQL Server と MySQL では使えない
    - MySQ は `concat`を使う
- `length`
    - **文字列長**
    - SQL Server では使えない
    - DBMS によっては1文字を長さ2以上と数えるLENGTH関数もある
- `lower`
    - 小文字化
- `replace`
    - 置き換え
- `substring`
    - 文字列の切り出し
    - 利用できるのは PostgreSQL と MySQL だけ
- `upper`
    - 大文字化

### 日付関数

- `current_date`
    - 実行した日付が出力される
- `current_time`
- `current_timestamp`
    - 主要DBMS全てで使える
- `extract`
    - 日付要素の抜き出し


### 変数関数

- `cast`
    - 型変換
- `coalesce`
    - `NULL`を変換して使いたいときに使用する

```sql
SELECT COALESCE(TS.tenpo_id, '不明')  AS tenpo_id, 
       COALESCE(TS.tenpo_mei, '不明') AS tenpo_mei,
       S.shohin_id, 
       S.shohin_mei, 
       S.hanbai_tanka
  FROM TenpoShohin TS RIGHT OUTER JOIN Shohin S
    ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;
```


### 述語

- `like`
    - 部分一致
- `between`
    - 範囲検索
- `is null`と`is not null`
- `in`と`not in`
    - `in`の引数にはサブクエリが使える
    - `in`の中に`null`を入れると常に空っぽになってしまう
        - 戻り値にも`null`が含まれないように気をつける

```sql
--「大阪店に置いてある商品の販売単価」を求める
SELECT shohin_mei, hanbai_tanka
  FROM Shohin
 WHERE shohin_id IN (SELECT shohin_id 
                       FROM TenpoShohin
                      WHERE tenpo_id = '000C');
```

- `exists`
    - ある条件に合致するレコードがあるかどうか
        - `true`、`false` 
    - 引数は常にサブクエリ
        - `select *`と書く
            - 合致するかどうかだけなので列名は気にしない
       
```sql
--EXISTSで「大阪店に置いてある商品の販売単価」を求める
SELECT shohin_mei, hanbai_tanka
  FROM Shohin AS S
 WHERE EXISTS (SELECT *
                 FROM TenpoShohin AS TS
                WHERE TS.tenpo_id = '000C'
                  AND TS.shohin_id = S.shohin_id);
```

### `case`式

- `case`
    - `else`は省略しない
    - 式を書ける場所ならどこでも書ける
        - `select`を柔軟に組み替えられる

```sql
--商品分類ごとに販売単価を合計した結果を行列変換する
SELECT SUM(CASE WHEN shohin_bunrui = '衣服'         THEN hanbai_tanka ELSE 0 END) AS sum_tanka_ihuku,
       SUM(CASE WHEN shohin_bunrui = 'キッチン用品' THEN hanbai_tanka ELSE 0 END) AS sum_tanka_kitchen,
       SUM(CASE WHEN shohin_bunrui = '事務用品'     THEN hanbai_tanka ELSE 0 END) AS sum_tanka_jimu
  FROM Shohin;
```


## 第７章　集合演算

- 集合演算の注意事項
    1. 演算対象のレコードの列数は同じであること
    2. 足し算の対象となるレコードの列のデータ型が一致していること
    3. `select`はどんなものを指定してもいい。だが`order by`は最後に１つだけ


### テーブルの足し算と引き算

- `union`
    - 和集合
- `union all`
    - 重複行を排除しない
- `intersect`
    - 共通するレコードを選択する
- `except`
    - 引いた残りが選択される
    - どちらから引くかで結果が異なる

### 結合

- 結合とは
    - 別テーブルから列を持ってきて増やす
        - `where`などで絞り込むこともできる
- `inner join`
    1. `from`に複数のテーブルを書く
    2. `on`の後に結合キーを指定する
    3. `select`では`TS.tenpo_id`などのように書く

```sql
--SQL Server、DB2、PostgreSQL、MySQL
SELECT TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei, S.hanbai_tanka
  FROM TenpoShohin AS TS INNER JOIN Shohin AS S
    ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;
```

- `outer join`
    1. 片方のテーブルの情報がすべて出力される
        - 行数固定の定型帳票を作る場合に使われる
    2. どちらのテーブルをマスタにするか
        - `left`、`right`で決める

```sql
SELECT TS.tenpo_id, TS.tenpo_mei, S.shohin_id, S.shohin_mei, S.hanbai_tanka
  FROM TenpoShohin TS RIGHT OUTER JOIN Shohin S
    ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;
```

- `corss join`
    - 実務ではまず使われない
    - `inner join`と`outer join`のベースとなる


## 第８章　SQLで高度な処理を行なう

### ウィンドウ関数

OLAP関数とも呼ばれます。例えば`rank`関数の場合だと、`partition by`がテーブルを横方向にカットします。この区切られた部分が**ウィンドウ**という範囲です。それを`order by`が縦方向に順序づけします。

またウィンドウ関数は引数をとらないため`()`内は常に空っぽです。

- `rank`
    - ランキングを算出する
        - 同順位なら後続の順位が飛ぶ
            - 1位、2位、2位、4位……
- `dense_rank`
    - 後続順位が飛ばない
        - 1位、2位、2位、３位……
- `row_number`
    - 一意な連番づけをする

```sql
--Oracle、DB2、SQL Server、PostgreSQL
SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
       RANK () OVER (PARTITION BY shohin_bunrui
                         ORDER BY hanbai_tanka) AS ranking
  FROM Shohin;
```

- `sum`をウィンドウ関数として使って**累計**を出す

```sql
--Oracle、DB2、SQL Server、PostgreSQL
SELECT shohin_id, shohin_mei, hanbai_tanka,
       SUM (hanbai_tanka) OVER (ORDER BY shohin_id) AS current_sum
  FROM Shohin;
```

- `avg`を使って**移動平均**を出す

```sql
--Oracle、DB2、SQL Server、PostgreSQL
SELECT shohin_id, shohin_mei, hanbai_tanka,
       AVG (hanbai_tanka) OVER (ORDER BY shohin_id
                                 ROWS BETWEEN 1 PRECEDING AND 
                                              1 FOLLOWING) AS moving_avg
  FROM Shohin;
```

- レコードをきちんとした順序に並べるには最後に`order by`を入れる
    - `over`内の`order by`は計算順序を決める役割しかない

```sql
--Oracle、DB2、SQL Server、PostgreSQL
SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
       RANK () OVER (ORDER BY hanbai_tanka) AS ranking
  FROM Shohin
 ORDER BY ranking;
```

### `GROUPING`演算子

- `rollup`
    - 合計・小計値も`group by`と一緒に出したい時に使う
    - `group by ()`と`group by (shohin_bunrui)`を同時に計算している

```sql
--Oracle、DB2、SQL Server、PostgreSQL
SELECT shohin_bunrui, SUM(hanbai_tanka) AS sum_tanka
  FROM Shohin
 GROUP BY ROLLUP(shohin_bunrui);
```

- `grouping`
    - 合計部分に文字列を埋め込める
- `cube`
    - `group by`句のキーのすべての組み合わせを表示する
- `grouping sets`
    - 条件を個別指定して抜き出す