【備忘録 #5】SQL入門書？いやいや、そんなん分かっt...。

## はじめに
以下の書籍を読みました。
備忘録です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2512411/a98c3156-69c0-8b15-2b5b-3075209dc5bb.png)
https://www.amazon.co.jp/gp/product/4295013390/ref=ppx_yo_dt_b_search_asin_title?ie=UTF8&psc=1



### ■関数
#### ROW_NUMBER()
start_dateの降順で自動で数値（1,2,3,4...）が割り当てられる。
```sql
SELECT *,
    ROW_NUMBER() OVER (ORDER BY start_date DESC) AS RN
FROM money_table
```

#### COALESCE
引数に指定した値のうち、左から順番に確認して、
一番最初にNULLではないと判定した値を返す。


### ■集合
#### UNION
和集合。
UNION ALLは重複行がある場合も、そのまま足すが、
UNIONの場合、重複行は1つになる。

#### EXCEPT
差集合。
前の結果から、後ろの結果を引いた結果が出る。


#### INTERSECT
積集合。
前の結果と後ろの結果で、重複がある行をまとめた結果が出る。


### ■外部結合

|id |name|
|:-:|:--:|
|1  |太郎|
|2  |花子|
|3  |テスト|
|4 |ジーピーティー|

↑children


| child_id | start_date | end_date   | type |
|----------|------------|------------|------|
| 1        | 2023-10-11 | 2023-12-30 | 5    |
| 1        | 2023-05-01 | 2023-10-10 | 7    |
| 1        | 2023-01-01 | 2023-05-01 | 2    |
↑child_history



#### LEFT OUTER JOIN

```sql
SELECT *
FROM children
LEFT OUTER JOIN child_history
ON children.id = child_history.child_id;
```
| id | name          | child_id | start_date | end_date   | type |
|----|---------------|----------|------------|------------|------|
| 1  | 太郎           | 1        | 2023-10-11 | 2023-12-30 | 5    |
| 1  | 太郎           | 1        | 2023-05-01 | 2023-10-10 | 7    |
| 1  | 太郎           | 1        | 2023-01-01 | 2023-05-01 | 2    |
| 2  | 花子           | NULL     | NULL       | NULL       | NULL |
| 3  | テスト         | NULL     | NULL       | NULL       | NULL |
| 4  | ジーピーティー | NULL     | NULL       | NULL       | NULL |

#### RIGHT OUTER JOIN

```sql
SELECT *
FROM children
RIGHT OUTER JOIN child_history
ON children.id = child_history.child_id;
```

| id | name | child_id | start_date | end_date   | type |
|----|------|----------|------------|------------|------|
| 1  | 太郎  | 1        | 2023-10-11 | 2023-12-30 | 5    |
| 1  | 太郎  | 1        | 2023-05-01 | 2023-10-10 | 7    |
| 1  | 太郎  | 1        | 2023-01-01 | 2023-05-01 | 2    |



#### FULL OUTER JOIN
```sql
SELECT *
FROM children
FULL OUTER JOIN child_history
ON children.id = child_history.child_id;
```

| id | name      | child_id | start_date | end_date   | type |
|----|-----------|----------|------------|------------|------|
| 1  | 太郎       | 1        | 2023-10-11 | 2023-12-30 | 5    |
| 1  | 太郎       | 1        | 2023-05-01 | 2023-10-10 | 7    |
| 1  | 太郎       | 1        | 2023-01-01 | 2023-05-01 | 2    |
| 2  | 花子       | NULL     | NULL       | NULL       | NULL |
| 3  | テスト     | NULL     | NULL       | NULL       | NULL |
| 4  | ジーピーティー | NULL     | NULL       | NULL       | NULL |


### GROUP BY, HAVING
1. WHERE句による絞り込み
2. GROUP BY句によるグループ化
3. HAVING句による絞り込み


### サブクエリ（副問合せ）
INSERT文のVALUES句を、副問合せのSELECT文を書くこともできる。
```sql
INSERT INTO my_emp(empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno
FROM   emp
WHERE  sal >=2000;
```

また、サブクエリでは、
基本的に、サブクエリ内はサブクエリ外から独立しているが、
`EXISTS`を記述することで
主問い合わせの内部から表や列を利用することができる。

### トランザクション
一連のSQLコマンドを1つのまとまりにできる。

```sql
BEGIN;
INSERT ...;
UPDATE ....;
COMMIT;
```

#### トランザクションによる副作用
**◯ダーティーリード**
未コミットの変更を、他の人が読めてしまう。
**◯反復不能読み取り**
複数回SELECT文実行時、途中でデータの内容が変わってしまうこと。
**◯ファントムリード**
複数回SELECT文実行時、途中で他の人がINSERTしてしまうこと。

#### 分離レベル
| 分離レベル         | 説明                                                         | ダーティリード | ノンリピータブルリード | ファントムリード |
|------------------|------------------------------------------------------------|--------------|---------------------|----------------|
| READ UNCOMMITTED | 他のトランザクションがコミットしていないデータも読み取ることができる。 | 可能         | 可能                | 可能           |
| READ COMMITTED   | コミットされたデータのみを読み取る。                         | 不可能       | 可能                | 可能           |
| REPEATABLE READ  | トランザクション開始時点のデータのみを読み取る。                 | 不可能       | 不可能              | 可能           |
| SERIALIZABLE     | 完全に隔離されたトランザクション。他のトランザクションの影響を受けない。 | 不可能       | 不可能              | 不可能         |


#### 行ロック
```sql
SELECT ~ FOR UPDATE (NOWAIT)
```

#### 表ロック
```sql
LOCK TABLE TABLE_NAME IN MODE_NAME MODE (NOWAIT)
```

#### データベースロック


### 制約
#### CHECK
```sql
CREATE TABLE children (
    id int,
    name text,
    type int CHECK(type > 0)
);
```


### TRUNCATE
テーブル初期化。

### EXPLAIN
高速化の効果を測定。