はじめに

前回の記事に引き続いてアウトプットしていきます！
[SQL]すっきりわかるSQL入門のまとめ①はこちら

副問い合わせ

副問い合わせとは、SQL文の中にさらにSQL文をネストすることで、副照会またはサブクエリともいう。

副問合せのパターンとしては以下の3つ

単一の値の代わりとして、副問い合わせの検索結果を用いる。
複数の値の代わりとして、副問い合わせの検索結果を用いる。
表の値の代わりとして、副問い合わせの検索結果を用いる。

単一の値の代わりとして利用

UPDATE 家計簿集計
SET 平均 = (SELECT AVG(出金額)
           FROM 家計簿アーカイブ
           WHERE 出金額 > 0
           AND 費目='食費')
WHERE 費目='食費'

複数の値の代わりとして利用

・INを使った副問合せ

SELECT * FROM 家計簿集計
WHERE 費目 IN (SELECT DISTINCT 費目 FROM  家計簿)

・ANY/ALLを使った副問い合わせ

SELECT * FROM 家計簿
WHERE 費目 = '食費'
 AND 出金額 < ANY (SELECT 出金額 FROM 家計簿アーカイブ　
                  WHERE 費目 = '食費')

・COALESCE関数を使用した副問い合わせ

SELECT * FROM 家計簿アーカイブ
WHERE 費目 IN(SELECT COALESCE(費目,'不明') FROM 家計簿)

表の値の代わり

通常はFROM句などで使用する。
INSERT文で使う場合は、以下のように特殊な記述になる。
(VALUES以降の記述に相当する内容に化けるもの)

INSERT INTO 家計簿集計 SELECT ~~

INSERT INTO 家計簿集計(費目,合計,平均,回数)
SELECT 費目,SUM(出金額),AVG(出金額),0
  FROM 家計簿
 WEHRE 出金額　> 0

テーブルの結合

テーブルの結合にはJOINを使用します。

SELECT 日付,名前 AS 費目,メモ
FROM 家計簿
JOIN 費目
ON 家計簿.費目ID=費目.ID

<ポイント>
・結合相手が重複している場合は、複製した上で結合。
・結合相手の行がない場合は、その行は結合結果から消滅。

ところで、NULLだったら消滅ではなく、NULLとして出したい時があるはずです。そうした場合は、LEFT JOINやRIGHT JOINなどがあります。また、それら両方の特徴をもつ、FULL JOINなどもあります。

トランザクション

1つ以上のSQL文をひとかたまりとしたものを、トランザクションといいます。
トランザクションが終了する際に、いままでの「仮の処理」を確定事項にすることをコミットといい、逆になかったことにすることをロールバックと言います。

トランザクションは以下のように指定します。

BEGIN;

処理

COMMIT;

トランザクションの分離

DBMSに対して複数の利用者が同時に処理を要求することで発生する副作用には次の3つのものが知られています。

ダーティーリード

まだコミットしてない未確定の変更を他の人が読めてしまう副作用。これがあると、まだ決定してない預金残高（後でロールバックするもの）に対して操作してまって整合性がずれてしまうということある。

反復読み取り

あるテーブルに対してSELECT文を実行した後、他の人がUPDATEすると、二度目のSELECT文を実行した際に結果が変わってしまうということ。

ファントムリード

反復読み取りと似ていて、2回目のSELECT文の間に他の人がinsert文で行を追加すると2回目のSELECTで結果行数が変わってしまうという副作用。

以上のような副作用を避けるために、トランザクションの読み書き中にロックをかけます。このロックはDBMSのパフォーマンスを下げるため、正確なデータベースと二律背反の関係です。とはいえ、完全に2分の1の選択をしにといけないわけではなく、トランザクション分離レベルといった形でどの程度厳密に分離するか指定できます。

分離レベル	ダーティーリード	反復不能読み取り	ファントムリード
REA UNCOMMITTED	恐れあり	恐れあり	恐れあり
READ COMMITTED	発生しない	恐れあり	恐れあり
REPEATABLE READ	発生しない	発生しない	恐れあり
SERIALIZABLE	発生しない	発生しない	発生しない

トランザクション分離レベルのん指定は以下のようにします。

SET TRANSACTION ISOLATION LEVEL　分離レベル

ロックの活用

上記でも少し触れたロックについては、対象の一行をロックするものと表そのものをロックする二つ方法があります。

<行のロック>

BEGIN;
SELECT * FROM ~ 
WHERE ~
FOR UPDATE;
--省略

<表のロック>

BEGIN;
LOCK TABLE テーブル名 IN モード名 MODE
--省略

モードについては、EXCLUSIVEとすると排他ロック,SHEREとすると共有ロックとなる。

おわりに

まだまだもっと基礎を叩き込まねば！
練習問題もしっかりやっていきます！

[SQL]すっきりわかるSQL入門のまとめ②