スッキリわかるSQL入門 読書メモ

はじめに

今回は、スッキリわかるSQL入門を読んだので、以下に読書録として残そうと思いました

なぜ「スッキリわかるSQL入門」を選んだのか

実務2年目で、SQLを触る機会があるのですが、

「引き出しが少なすぎる...」

「複数テーブルが結合した長文クエリが訳分からん...」

「先輩エンジニのSQL文考える速度が異常にはやい...」

と感じながらも、なんとなく使っている感が否めないので基礎からしっかり理解し直すために、基礎固めとして定評があるこちらの書籍を選びました

また、演習問題ドリルや、環境構築不要なdokoQLも利用できる為、すぐにSQLを実行して動作を確認できるのも魅力ポイントです

学んだこと

3章 操作する行の絞り込み

• WHERE句

  • 対象データの絞り込みに利用
  • SELECT, UPDATE, DELETE文で使うことができる

• 演算子

  • 比較演算子
    • <= : 左辺は右辺の値以下
    • >= : 左辺は右辺の値以上
    • <> : 左右の値が等しくない
    • IS NULL, LIKE, BETWEEN, IN, ANY, ALL
  • 論理演算子
    • AND, OR, NOT
    • 論理演算子は、NOT、AND、ORの順で優先度が高く、先に評価される

• NULL

  • データが格納されておらず、未定義の状態を「NULLが格納されている」と表現する
  • 数字のゼロや空白文字とも異なる
  • NULLは=で判定できない
    • IS NULLもしくはIS NOT NULLで判定

• 主キー

  • 「この値を指定することで、ある1行を完全に特定できる」という役割を担う列のこと
  • プライマリーキー(primary key)ともいう
  • 自然キー(natural key)
    • 自然に登場し、主キーの役割を果たせる列のこと
    • 例) 社員情報を管理する社員テーブルを作ろうとする中で、名前や性別に加えて社員番号という列が自然に思いつく
  • 人工キー(artificial key)もしくは代替キー(surrogate key)
    • 管理目的のためだけに人為的に追加された列のこと
    • 例) 家計簿テーブルの場合、「1回の入出金行為」それぞれに連番で番号を振り、入出金IDのような列として管理する

4章 検索結果の加工

• 検索結果を加工する主なキーワード

  • DISTINCT : 重複行を除外する
  • ORDER BY : 順序を並べ替える
  • LIMIT : 件数を限定して取得する
  • UNION : 検索結果に他の検索結果を足し合わせる (和集合)
  • EXCEPT : 検索結果から他の検索結果を差し引く (差集合)
  • INTERSECT : 検索結果と他の検索結果で重複する部分を取得する (積集合)

• DBMSにとって並び替えは大仕事

  • ORDER BY句はとても便利だが、並び替えという処理は、DBMSにとってかなり負荷のかかる作業であることを頭の片隅に置いておく
  • また、DISTINCTやUNIONも内部的には並び替えを行なっていることがあるため、大量のメモリを消費する場合があるので、乱用は控えておく

5章 式と関数

• COALESCE : 最初に登場するNULLでない値を返す

6章 集計とグループ化

• 集計

  • SUM : 各行の値の合計を求める
  • MAX : 各行の値の最大値を求める
  • MIN : 各行の値の最小値を求める
  • AVG : 各行の値の平均値を求める

• 計数

  • COUNT : 行数をカウントする

• COUNT(*)とCOUNT(列)の違い

  • COUNT(*)は、単純に行数をカウントする(NULLの行も含める)
  • COUNT(列)は、指定列の値がNULLである行を無視してカウントする

• グループ化

  • GROUP BY句にグループ分けの基準となる列を指定することで、グループ別に集計できる
  • GROUP BY句を用いない集計では、検索結果の全件を1つのグループとして扱う

• 集計関数はWHERE句に利用できない

  • WHERE句を処理する段階では、まだ集計が終わっていないため

  • 集計処理を行った後の結果表に対して絞り込みをしたい時は、HAVING句を用いる

    -- 以下のsqlはエラー
    SELECT 費目, SUM(出金額) AS 費目別の出金額合計
    FROM 家計簿
    WHERE SUM(出金額) > 0
    GROUP BY 費目;
    
    -- 以下のようにする
    SELECT 費目, SUM(出金額) AS 費目別の出金額合計
    FROM 家計簿
    GROUP BY 費目
    HAVING SUM(出金額) > 0;
    

7章 副問い合わせ(サブクエリ)

• 使用例 : 家計簿アーカイブテーブルの1月と2月の出金額合計を知りたい場合

SELECT G.タイトル, G.出金額合計
FROM (
    SELECT '合計1月' AS タイトル, SUM(出金額) AS 出金額合計
    FROM 家計簿アーカイブ
    WHERE 日付 >= '2022-01-01'
    AND 日付 <= '2022-01-31'
    UNION
    SELECT '合計12月' AS タイトル, SUM(出金額) AS 出金額合計
    FROM 家計簿アーカイブ
    WHERE 日付 >= '2021-12-01'
    AND 日付 <= '2021-12-31'
) AS G;

• SQLのネスト
  • 副問合せ(サブクエリ) : SQLの中に別のSELECT文を記述すること
  • サブクエリは、より内側にあるものから外側に向かって順に評価される
• サブクエリのパターン
  • 単一行サブクエリ : 結果が1行一列になる
  • 複数行サブクエリ : 結果がn行一列になる
  • 結果がn行m列の表形式になるサブクエリも利用される
• 複数行サブクエリと演算子
  • 複数行サブクエリは、IN, ANY, ALL 演算子と併せてよく用いられる
  • 複数行サブクエリの結果にNULLが含まれると、NOT IN, <>ALL演算子の評価結果もNULLとなる
• サブクエリの結果から確実にNULLを除外する方法
  • サブクエリの絞り込み条件に、IS NOT NULL条件を含める
  • COALESCE関数を使って、NULLを別の値に置き換える

8章 複数テーブルの結合

• 外部キーとリレーションシップ
  • リレーションシップ(relationship) : 「家計簿テーブル」と「費目テーブル」のように、ある2つのテーブルの行に情報として関連がある場合、その関連のことを指す
  • 外部キー(foreign key) : 家計簿テーブルの「費目ID」列のように、他のテーブルの関連行を指すための値を格納することでリレーションシップを結ぶ役割を担っている列のこと
• 外部キー列の役割
  • 他テーブルのある列(主キー列など)の値を格納することによって、その行が他テーブルのどの行と関連しているかを明らかにする
• 複数のテーブルに分けるメリット
  • データを複数のテーブルに分けて格納した方が、安全・確実にデータを管理しやすい
• リレーショナルデータベース(RDB)
  • RDBは、データを複数テーブルで安全・確実に管理しながら、必要に応じて「人間にわかりやすい表」に結合することができる
• 結合
  • 結合を用いることで、複数のテーブルに格納された関連するデータを1つの結果表として取り出すことができる
  • 結合を行う相手テーブルを指定するためにJOIN句を、結合条件を指定するためにON句を記述する
  • 外部結合を用いると、結合相手がない行も結果表に出力させることができる
• テーブルの結合バリエーション
  • INNER JOIN : キーを元に結合して、共通しているレコードのみ表示する
  • LEFT JOIN : 左側のテーブルのデータは全て抽出され、右のテーブルにはないものはNULLが入る
  • RIGHT JOIN : 右側のテーブルのデータは全て抽出され、左のテーブルにないものはNULLが入る
  • OUTER JOIN : どちらかのテーブルにあるレコードが全て返される
  • テーブル名 Aはテーブル名 as Aの省略記法
  • 内部結合 : ONで指定した条件に一致するレコードのみ表示
    • INNER JOINは、内部結合
  • 外部結合 : ONで指定した条件に一致しないレコードも表示
    • LEFT JOIN, RIGHT JOIN, OUTER JOINは、外部結合

9章 トランザクション

• トランザクション : 複数のSQL文を分けることのできない1つの命令として扱うことができる
• DBMSによるトランザクションの制御
  • トランザクションの途中で、処理が中断されないようにする(原子性を保つように制御)
  • トランザクションの途中に、他の人の処理が割り込めないようにする(分離性を保つように制御)
• 原子性(atomicity)
  • トランザクションに含まれる複数のSQL文が、DBMSによって不可分なものとして扱われる性質のこと
  • コミット : トランザクションが終了する際に、「仮の書き換え」を全て確定したことにする動作
    • コミットを行うことで、トランザクション中のすべての処理が確定する
  • ロールバック : トランザクション中に異常が発生して中断したときに、DBMSがそれまで行ったすべての仮の書き換えをキャンセルして「なかったこと」にする動作
    • ロールバックを行うことで、トランザクション中の全ての処理はキャンセルされる
  • 自動コミットモード : DBMSが1つのSQL文が実行されるたびに自動的に裏でコミットを実行する
    • DBMSに付属する多くのSQL汎用ツールは、デフォルトで自動コミットモードになっている
• 分離性(isolation)
  • トランザクションは、同時実行中の他のトランザクションからの影響を受けないよう、分離して実行される性質のこと
  • 3つの代表的な副作用
    • ダーティーリード : まだコミットされていない未確定の変更を、他の人が読めてしまうという副作用
    • 反復不能読み取り : あるテーブルに対してSELECT文を実行したあと、他の人がUPDATE文でデータを書き換えると、次回SELECTした際に検索結果が異なってしまうという副作用
    • ファントムリード : 反復不能読み取りと似ており、2回のSELECTの間に他の人がINSERT文で行を追加すると、検索結果の行数が変わってしまうという副作用
  • トランザクションの分離レベルを選ぶことで、性能と分離の度合いのバランスを選ぶことができる
  • 正確なデータ操作とパフォーマンス(速度)は二律背反の関係にある
• ロック
  • DBMSが、あるトランザクションが現在読み書きしている行に鍵をかけ、他の人のトランザクションからは読み書きできないようにしてしまうこと
  • 行や表、データベース全体に明示的にロックをかけることができる
  • 複数の対象に異なる順番でロックをかけようとすると複数のトランザクションは、デッドロックに陥ることがあるため注意する

10章 テーブルの作成

• データベースを利用する2つの立場
  • 立場1 : データベースにデータの出し入れを指示する立場
  • 立場2 : 立場1の人が、効率よく安全にデータの出し入れができるよう必要なテーブル準備や各種設定を支持する立場
• 4種類のSQL命令

立場1	立場2
DML : Data Manipulation Language データ操作言語 : データの格納や取り出し、更新、削除などの命令 ・SELECT ・INSERT ・UPDATE ・DELETE などの4大命令	DDL : Data Definition Language データ定義言語 : テーブルなどの作成や削除、各種設定などの命令 ・CREATE ・ALTER ・DROP ・TRUNCATE
TCL : Transaction Control Language トランザクション制御言語 : トランザクションの開始や終了の命令 ・COMMIT ・ROLLBACK ・SET TRANSACTION ・SAVEPOINT	DCL : Data Control Language データ制御言語 : DMLやDDLの利用に関する許可や禁止を設定する命令 ・GRANT ・REVOKE

• 制約
  • テーブル作成時に各列に制約を設定し、予期せぬ値が格納されないようにすることができる
  • NOT NULL制約は、NULLの格納を防ぐことができる
  • UNIQUE制約は、重複した値の格納を防ぐことができる
  • CHECK制約は、格納しようとする値が妥当かどうかをチェックできる
  • 主キーとして取り扱いたい列には、主キー制約を設定する
  • データの更新や削除によって外部キーによる参照整合性が崩れることがないように、外部キー制約を設定する

11章 様々な支援機能

• インデックス
  • テーブルの列に対して索引情報を生成することができる
  • インデックスが存在する列に対する検索は、多くの場合高速になる
  • インデックスには名前をつけなければならない
  • すべての検索でインデックスが使われるわけではない
  • インデックスは書き込み性能の低下を招くこともあるため汎用は禁止
• ビュー
  • SELECT分の結果表を仮想的なテーブルとして扱うことができる
  • ビューを使うことでSQL文はシンプルになるが、その実体は単なるSELECT文のため、DBMSの負荷は変わらない
• 採番とシーケンス
  • 連番を生成する列定義やシーケンスを使って、連番を簡単に生成できる
  • MySQLなどの場合、CREATE TABLE文で列を定義する際に、AUTO_INCREMENTを指定するだけで、データが追加されるタイミングで自動的に連番が振られるようになる
  • 数字と記号を組み合わせたような複雑な採番を行う場合は、採番テーブルを作るなどして自力で実装する必要がある
• ACID特性 : データベーストランザクションの正確性と信頼性を保証するための4特性のこと
  • 原子性(atomicity) : 処理が中断しても中途半端な状態にならない
  • 一貫性(consistency) : データの内容が矛盾してはならない
  • 分離性(isolation) : 複数の処理を同時実行しても副作用がない
  • 永続性(durability) : 記録した情報は消滅せずに残り続ける
• バックアップ
  • 正確なデータ処理には、上記のACID特性が求められる
  • 記憶媒体が障害を起こした場合に備え、バックアップを取得する
  • データベースの内容だけでなく、ログファイルもバックアップし、ロールフォワードを行うことで、障害発生直前の状態までデータを復元することができる

12章 テーブルの設計

• データベース設計
  • お客様からヒアリングした要件は、概念設計、論理設計、物理設計を経て、DDLやDBMSの各種設定に落とし込む
  • 概念設計
    • 取り扱うエンティティとその関連を明らかにする
    • データベースやシステムに関することは考えず、要件に登場する情報だけをザックリと把握する
  • 論理設計
    • キー設計や正規化などを行い、RDB用のモデルに変換する
    • 「どのようなテーブルを作り、それぞれのテーブルにどのような列を作るか」まで明らかにすれば十分
    • データの抽出、エンティティの定義、正規化、ER図
  • 物理設計
    • 採用するDBMS製品に依存した詳細な設計に落とし込む
    • 論理設計で明らかになった各テーブルについて、内容を詳しく具体化していく
    • 全てのテーブルの全ての列について、型、インデックス、制約、デフォルト値など、テーブルに必要な全ての要素を確定させる
    • テーブル定義、インデックス設計
• 関数従属性
  • y = f(x) : 入力xに対して出力yが決まる
  • yはxに従属する : {x} -> {y}
  • 1つのセルに1つの値の方針も関数従属性
  • テーブル設計では全ての列が関数従属性を満たすように整理していく
• 論理データモデルと正規化
  • 「多対多」の関係は、中間テーブルを使って「1対多」に変換する
  • 主キーが存在しないテーブルには、人工キーを追加する
  • 第1正規形
    • 「1対多」を形成する概念は別テーブルとして設計する
      • テーブルの全ての行の全ての列に1つずつ値が入っているべきである
      • よって、「繰り返し列」や「セルの結合」が現れてはならない
  • 第2正規形
    • 複合主キーの一部に関数従属する部分を別テーブルに分割する
      • 複合主キーを持つテーブルの場合、比キー列は複合主キーの全体に関数従属すべきである
      • よって、「複合主キーの一部の列に対してのみ関数従属する列」が含まれてはならない
  • 第3正規形
    • 主キーに対して間接的に関数従属する部分を別テーブルに分割する
      • テーブルの比キー列は、主キーに直接、関数従属すべきである
      • よって、「主キーに関数従属する列にさらに関数従属する列」は存在してはならない
• 正規化を覚えるコツ
  • 第2正規形と第3正規形への変形は非常によく似ている(どちらもきたない関数従属を排除する)
  • まずは「繰り返し列」を、次に「きたない関数従属」を排除する、とザックリ捉える

良かったところ

SQLは書いてみないと中々理解しづらいと思うので、dokoQLでクエリの動作確認ができるのはとても良かったです

難しかったこと

テーブルの設計に関しては、概念として難しくこの書籍だけだと少し物足りない感じがしました。ですので、達人に学ぶDB設計徹底指南書などと併せて学習をするのが良いかと思います

※追記 : 以下も併せて読んでいただくと幸いです
達人に学ぶDB設計徹底指南書を本気で要約してみた - Qiita