More than 1 year has passed since last update.

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

Posted at 2023-11-12

はじめに

start_dateの降順で自動で数値（1,2,3,4...）が割り当てられる。

SELECT *,
    ROW_NUMBER() OVER (ORDER BY start_date DESC) AS RN
FROM money_table

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

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

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

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

↑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

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

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

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

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)

CREATE TABLE children (
    id int,
    name text,
    type int CHECK(type > 0)
);

テーブル初期化。

高速化の効果を測定。