「ゼロからはじめるデータベース操作」を読む
第1章
命名ルール
データベースやテーブル、列といった名前に使える文字は、半角文字のアルファベット、数字、アンダーバーに限られる。
型
- CHAR型(キャラ型)
固定長文字列。列に入れる文字列の長さが最大長にみたないばあい、文字列が最大長になるまで、空きを半角スペースで埋める。 - VARCHAR(バーキャラ型)
可変長文字列。半角スペースで埋めたりしない。
DISTINCT句はNULLも1種類のデータとして扱われる。
NULLを含んだ計算はすべてNULLになる
以下はすべてNULLになる。
- 5 + NULL
- 1 * ULL
- NULL / 9
NULLを含んだ演算や関数の結果がNULLになることを防ぐには、COALESCE関数を使う。
コアレスまたはコォアリースと読む。
WHERE hoge = NULL
では一行も表示できない。
NULLで絞りたいのなら、WHERE hoge IS NULL
NULLを含めた真理値は「不明(UNKNOWN)」という値になる。
つまりSQLにおける真理値は、「真」「偽」「不明」の3値論理。
(NOT NULL制約つけれるんだったら絶対つけたほうが良いな、、)
文字列型の1,2,3,10,11,222 に対してwhere hoge > '2'をすると、2と222が取得できる。
文字列順の大きさが基準になる。
SUM、AVG、MIN、MAXなどの集計関数は、勝手にNULLを排除してくれる。
SUM、AVGは数値飲みに使える。MIN、MAXはほとんどすべてのデータ型に使える。
タイプの種類を数えたい場合。
以下は間違い。数を集計した後に、重複を除くことになる。
SELECT DISTINCT COUNT(type) FROM hoge
()の中に入れることで、重複を除いた後に集計する。
SELECT COUNT(DISTINCT type) FROM hoge
GROUP BY とWHEREを併用するときの実行順序。
FROM → WHERE → GROUP BY → SELECT
SQLにおいて見た目の句の並び順と内部の実行順序は一緒にならない。
FROM句
↓
JOIN句
↓
WHERE句
↓
GROUP BY句
↓
HAVING句
↓
SELECT句
↓
ORDER BY句
↓
LIMIT句
GROUP BYを使う時、集約キー以外のカラム名を指定できるのはMySQLだけ。その他のDBMSではエラーになる。
ただ、複数ある候補の中から適当なルールで抜き出している。
SELECT count(*), name FROM MyTraining.exercises
GROUP BY id;
GROUP BYのカラム名に別名を指定することはできない。なぜなら、SELECTよりGROUP BYのほうが先に実行されるため、別名を知らないから。
(PostgreSQLでは実行可)
GROUP BYの結果に対して、絞り込みを行う場合はHAVING句を使う。
実行順序は、FROM → WHERE → GROUP BY → SELECT → HAVING。
INSERT ~ SELECT文はテーブルからデータをコピーできる。
以下は、idが467以上のレコードをコピーするSQL(idは採番)
INSERT INTO ocr.estates (info, file_id, status_code, created_at, updated_at)
SELECT info, file_id, status_code, created_at, updated_at FROM ocr.estates
WHERE id >= 467;
トランザクションとは、まとまりで処理されるべき、1つ以上の更新処理の集まりのこと。
DBMSのトランザクションには守らなくちゃいけない約束事がある。これらをACID特性と呼ぶ。
原子性(Atomicity)
トランザクションが終わった時、そこに含まれていた更新処理はすべて実行されるか、すべて実行されない状態で終わる性質。オール・オア・ナッシングともいう。
一貫性(Consistency)
トランザクションの処理は、データベースに予め設定された制約を満たすという性質。
MySQL で利用可能な制約は以下の4つ
- PRIMARY KEY (値が一意かつ NOT NULL)
- FOREIGN KEY (指定した他のテーブルのカラムに含まれる値のみを格納可能)
- 無効データ (INT UNSIGNED にマイナスの値を入れる、NOT NULL に NULL を入れる等を禁止)
- ENUM および SET (あらかじめ指定した値以外を格納禁止)
https://hogetech.info/2021/02/17/2054/#consistency
トランザクション処理で制約を違反した場合、違反した SQL 文に対して ROLLBACK が発生します。
↑要実験、、、、、。
独立性(Isolation)
トランザクション同士が干渉しあわない性質。
トランザクションが終了するまで、他のトランザクションからは変更されたレコードが見えない。
永続性(Durability)
トランザクションが終了したら、その変更状態の保存を保証するという性質
例えば、トランザクションの実行ログをディスクなどに保存しておいて、有事のときに復旧するというほう方法など。
MySQLにおいて
InnoDBは自動コミットモードがデフォルトで有効。
トランザクションはSQLを発行するたびにBEGIN/COMMITで完了し、ロールバックできません。
複数SQLを束ねて1つのトランザクションにしたいときに、BEGINを宣言すると、COMMIT/ROLLBACKするまでの間、AutoCommitがOFFな状態になります。
トランザクションの開始タイミング。
MySQLでは、BEGIN(START TRANSACTION。長いので、以下、特筆すべき場合以外は「BEGIN」で)を宣言しても、内部的にはまだトランザクションを開始してません。
SQLを投げたタイミングで、トランザクション開始になります。
このとき、更新のない、FOR UPDATEもないSELECT文でも、トランザクションが開始されます。
サブクエリはビューを基本とした技術。いわば使い捨てのビュー。
SELECT DISTINCT exercise_id FROM MyTraining.menus WHERE id IN
(SELECT DISTINCT menu_id FROM MyTraining.workouts
WHERE is_max = 1);
スカラサブクエリは必ず一行一列だけの結果を返すサブクエリ。
SELECT * FROM MyTraining.workouts
where rep >= (SELECT AVG(rep) FROM MyTraining.workouts)
;
相関サブクエリは小分けにしたグループ内での比較をするときに使う。
-- 種目ごとの平均重量を超えたワークアウトのみを取得するSQL
SELECT a1.id, a1.weight FROM MyTraining.workouts AS a1
INNER JOIN MyTraining.menus AS a2 ON a2.id = a1.menu_id
INNER JOIN MyTraining.exercises AS a3 ON a3.id = a2.exercise_id
WHERE a1.weight >
(SELECT AVG(weight) FROM MyTraining.workouts AS b1
INNER JOIN MyTraining.menus AS b2 ON b2.id = b1.menu_id
INNER JOIN MyTraining.exercises AS b3 ON b3.id = b2.exercise_id
WHERE a2.exercise_id = b2.exercise_id
GROUP BY b2.exercise_id);
同じテーブルが別名を使って区別する。
WHERE a2.exercise_id = b2.exercise_id
が結合条件。exercise_idで結合している。
結合条件はサブクエリの中に書かないとエラーになる。
EXISTS述語 = ある条件に合致するレコードの存在有無を調べること。常に相関サブクエリを引数とする。
EXISTSのサブクエリでSELECT *
で、*
はどんな値を書いても良い。けど慣習として*
を使う。
whereで結合する。
分岐したいならCASE。
集合演算。
ALLをつけると重複行を排除しない。
UNION
重複業を排除する集合演算子。
演算対象雨のレコードの列数は同じで各レコード対象のデータ型が一致していなくちゃいけない
INTERSECT(MySQLには存在しない)
2つのレコード集合の共通部分を選択する。
EXCEPT(MySQLには存在しない)
引き算を行う集合演算子。
MySQLで集合の加減をするなら工夫が必要。
ウィンドウ関数は、ランキング、連番生成などの通常の集約関数ではでない高度な操作を実現する。
MySQLでは、バージョン8.0以降でサポート。
結果のレコードに対して使う関数なので、SELECT句で使う。
ここからは本の内容じゃない
トランザクショはネストできない。SAVEPOINTを使う。
https://qiita.com/natsu_no_yuki/items/e1db2a132cbff740896d
トランザクションをネストすることはできません。
デッドロックについて
さまざまなトランザクションが進行できない状況 (それぞれが、他方が必要とするロックを保持しているため)。リソースが利用可能になるまで両方のトランザクションが待機しているため、どちらもそれが保持しているロックを解放しません。
デッドロックが発生した場合、InnoDB は状況を検出し、いずれかのトランザクション (デッドロック対象) をロールバックします。したがって、アプリケーションロジックが完全に正しい場合でも、トランザクションを再試行する必要があるケースを扱う必要があります