はじめに
以下の書籍を読みました。
備忘録です。
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...）が割り当てられる。
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
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
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
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
- WHERE句による絞り込み
- GROUP BY句によるグループ化
- HAVING句による絞り込み
サブクエリ（副問合せ）
INSERT文のVALUES句を、副問合せのSELECT文を書くこともできる。
INSERT INTO my_emp(empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno
FROM emp
WHERE sal >=2000;
また、サブクエリでは、
基本的に、サブクエリ内はサブクエリ外から独立しているが、
EXISTSを記述することで
主問い合わせの内部から表や列を利用することができる。
トランザクション
一連のSQLコマンドを1つのまとまりにできる。
BEGIN;
INSERT ...;
UPDATE ....;
COMMIT;
トランザクションによる副作用
◯ダーティーリード
未コミットの変更を、他の人が読めてしまう。
◯反復不能読み取り
複数回SELECT文実行時、途中でデータの内容が変わってしまうこと。
◯ファントムリード
複数回SELECT文実行時、途中で他の人がINSERTしてしまうこと。
分離レベル
| 分離レベル | 説明 | ダーティリード | ノンリピータブルリード | ファントムリード |
|---|---|---|---|---|
| READ UNCOMMITTED | 他のトランザクションがコミットしていないデータも読み取ることができる。 | 可能 | 可能 | 可能 |
| READ COMMITTED | コミットされたデータのみを読み取る。 | 不可能 | 可能 | 可能 |
| REPEATABLE READ | トランザクション開始時点のデータのみを読み取る。 | 不可能 | 不可能 | 可能 |
| SERIALIZABLE | 完全に隔離されたトランザクション。他のトランザクションの影響を受けない。 | 不可能 | 不可能 | 不可能 |
行ロック
SELECT ~ FOR UPDATE (NOWAIT)
表ロック
LOCK TABLE TABLE_NAME IN MODE_NAME MODE (NOWAIT)
データベースロック
制約
CHECK
CREATE TABLE children (
id int,
name text,
type int CHECK(type > 0)
);
TRUNCATE
テーブル初期化。
EXPLAIN
高速化の効果を測定。