『スッキリわかるSQL入門』を読んだ感想とポイント

はじめに

『スッキリわかるSQL入門[第3版]』を読んだので、感想と特に勉強になったポイントを書きます。

スッキリわかるSQL入門 第3版 ドリル256問付き！ スッキリわかるシリーズ

良かったところ

• 各章の終わりに易しい章末問題があり、章の内容が身についたかチェックできる
• 付録としてDBMS製品ごとの互換性のポイントがある
  • 実務でも役に立ちそう
• 本の終わりにドリルが256問ついている
  • 学んだ内容を大量の演習で身につけられる
  • SQL入門の本で設計の問題まであるのは珍しいと思う

悪かったところ

• テーブル名、カラム名が全て日本語であること
  • 初心者にもとっつきやすいように日本語にしていると思われるが、かなと英数の入力切り替えがとても面倒だった
• ドリルの回答がPDFであること
  • 改行がされておらず見にくい
  • コピペしづらい

難しかったこと

• やはり設計が難しかった
  • 特に与えられた資料から非正規形にするのが難しい
  • 絶対的な正解があるわけでもないのが余計ややこしい
  • 経験を積んで慣れていくしかない

学んだこと

今まであまり使ったことのない機能や、普段使用しているMySQLではサポートされていない機能を中心に学んだ。

第2章 基本文法と4大命令

• 文字列や日付はシングルクォーテーションで囲む
  • MySQLではダブルクォーテーションでも良い
  • Oracleではダブルクォーテーションだと引用識別子となる
    • ASで別名をつけるときなどにダブルクォーテーションを使う

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

• WHERE句で= NULLのようにNULLと比較してはいけない理由

  • WHERE句ではTRUEと判定された行だけを返す（処理対象になる）
    • NULLとの比較結果はUNKNOWNになってしまい、処理対象にならない

• 単なる文字として%や_を使いたい場合はESCAPEを設定する

  SELECT * FROM table_name WHERE column_name LIKE '%100$%' ESCAPE '$'

• ANY演算子
  • 値リストのそれぞれと比較し、いずれかが真なら真
  • INと同じ

  column_name = ANY (値1, 値2, 値3)

• ALL演算子
  • 値リストのそれぞれと比較し、すべて真なら真
  • NOT INと<> ALLは同じ

  column_name = ALL (値1, 値2, 値3)

• ANYやALLは計算式や副問い合わせと組み合わせて初めて真価を発揮する

• NOT INや<> ALLで比較する対象にNULLが含まれると、判定結果もNULLになってしまうので注意

  -- 比較結果はすべてNULLになってしまう
  SELECT * FROM table_name
  WHERE column_name NOT IN (A, B, NULL)

• 論理演算子の優先順位
  • 高い順にNOT, AND, OR

第4章 検索結果の加工

• OFFSET - FETCH

  OFFSET 先頭から除外する行数 ROWS
  FETCH NEXT 取得行数 ROWS ONLY

• 今まで使っていたMySQLではサポートされていないので知らなかった

  • MySQLでもLIMIT 取得件数 OFFSET 開始位置のように開始位置を設定できる

• 集合演算子

  • UNION 和集合
  • EXCEPT 差集合
  • INTERSECT 積集合
  • 注意点
    • 使用するテーブルの各カラムの列数とデータ型が一致している必要がある
    • ORDER BYは最後のSELECT文の後ろに書く
    • 各演算子の後ろにALLをつけると重複を排除しない

第5章 式と関数

• 関数はDBMS製品による差異が大きいので、都度調べる必要あり

• 文字列連結は||関数を使う

  • ただし、MySQLではサポートされていないのでCONCAT関数を使う

• 型が違うデータを組み合わせるにはCASTを使用する

• 式や関数はSELECT 式や関数という形式でFROM句なしで実行できる

  • ただし、OracleとDb2ではサポートされていないので、FROM句にダミーのテーブルを指定する

第6章 集計とグループ化

• COUNT(column_name)とすると、カラムがNULLの行を無視してカウントする

第7章 副問い合わせ

• 副問い合わせには検索結果の行数と列数によって以下の種類がある

  • 単一行副問い合わせ（1行）
  • 複数行副問い合わせ（1列n行）
  • 表の結果となる副問い合わせ（n列m行）

• 相関副問い合わせ

  • 「主問い合わせがテーブルから行を絞り込む過程で、各行について抽出の可否を判断するために、繰り返し副問合せを実行する」処理
  • 通常の副問い合わせよりは処理負荷が大幅に増える

  SELECT column_name FROM tableA
  WHERE EXISTS
  (SELECT * FROM tableB WHERE tableA.column = tableB.column)

第8章 複数テーブルの結合

• 完全外部結合は左右の表の全行を必ず出力する

  SELECT ~ FROM left_table
  FULL [OUTER] JOIN right_table
  ON 結合条件

• MySQLやMariaDBにはFULL JOINがないので、LEFT JOINした結果とRIGHT JOINした結果をUNIONをつけて代用する

第9章 トランザクション

• 複数の利用者が同時に処理を実行しようとした時の代表的な副作用

  • ダーティリード
    • まだコミットされていない未確定の変更を他の人が読めてしまう
  • 反復不能読み取り（アンリピータブルリード）
    • あるテーブルに対してSELECTした後、他の人がUPDATEしてしまったため、次にSELECTした時に結果が変わってしまう
  • ファントムリード
    • あるテーブルに対してSELECTした後、他の人がINSERTやDELETEしてしまったため、次にSELECTした時に結果の行数が変わってしまう

• 副作用を発生させないためには、各トランザクションを分離する必要がある

  • ただ、他人の処理が終わるまで絶対に待たせる、とすると処理速度が遅くなってしまう
  • どの程度分離するかを設定するとDBMSが適宜処理してくれる

• SQLで指定した対象を明示的にロックすることもできる

• ロックには2種類の強度がある

  • 共有ロック
    • 他のトランザクションからは参照のみ許可
    • データ読み取りの際に使用する
  • 排他ロック
    • 他のトランザクションからのアクセスは禁止
    • データ更新の際に使用する

• 通常SELECT文を実行した場合は共有ロックが自動的にかかる

• 行に対して排他ロックをかけるには以下を実行する
  • NOWAITオプションを指定すると、アクセスしようとした行がすでにロックがかかった場合、待たずに即エラーを返す

  SELECT ~ FOR UPDATE [NOWAIT]

• 表に対してロックをかけることも可能

第10章 テーブルの作成

• 人的ミスを防ぐために、意図しないデータが追加・更新されないよう制約をかけておく
• CREATE TABLE文で指定する制約
  • NOT NULL制約
    • DEFAULT指定と組み合わせて利用させることが多い
  • UNIQUE制約
  • CHECK制約
  • PRIMARY KEY制約
    • NULLも重複も許されない

  -- 主キー制約の指定（単独列）
  CREATE TABLE table_name (
    ID INTEGER PRIMARY KEY, 
    ...
  )
  -- 主キー制約の指定（複合主キー）
  CREATE TABLE table_name (
    ID INTEGER,
    name VARCHAR(40) UNIQUE,
    PRIMARY KEY (ID, name)
  )

• FOREIGN KEY制約
  • 参照整合性（外部キーが指し示す先にあるべき行が存在しリレーションシップが成立していること）

  -- 外部キー制約の指定方法1
  CREATE TABLE table_name (
  column_name データ型 REFERENCES 参照先テーブル名（参照先列名）
  ...
  )
  
  -- 外部キー制約の指定方法2
  CREATE TABLE table_name (
    ...
    FOREIGN KEY (参照元列名)
     REFERENCES 参照先テーブル名（参照先列名)
  )
  

第11章 さまざまな支援機能

• 検索を高速化するためにインデックスを追加する

  • 高速化が見込まれるパターン
    • WHERE句による絞り込み条件でインデックスを作成した列を指定した場合
      • 完全一致検索や前方一致検索では効果がある
      • 部分一致検索や後方一致検索では効果なし
    • インデックス列をORDER BY句に指定した場合
    • インデックス列をJOINの結合条件に指定した場合
  • インデックスのデメリット
    • 索引情報の保存にディスク容量を消費する
    • テーブルデータの作成・更新・削除時にインデックスも書き換える必要があり、オーバーヘッドが増える

• Oracle、SQL Serverなど一部のDBMSでは、連番を管理してくれる専用の道具（シーケンス）を利用できる

  • シーケンスは裁判した最新の値を常に記憶し、シーケンスに指示すると「現在の値」や「次の値」をすぐ取り出せる
  • 複数のトランザクションからの利用を想定し、シーケンスから値を取り出すと、その操作はすぐに確定し、ロールバックしても元に戻らない

• ACID特性

  • データを正確かつ安全に取り扱うための4つの特性
    • 原始性
      • 処理が中断しても中途半端な状態にならない
      • トランザクションが保証
    • 一貫性
      • データの内容が矛盾した状態にならない
      • 型や制約が保証
    • 分離性
      • 複数の処理を同時実行しても副作用がない
      • 分離レベル、ロックが保証
    • 永続性
      • 記録した情報は消滅せず保持され続ける
      • バックアップが保証

• バックアップファイルの取得には時間がかかる

  • 実行されたすべてのSQL文を保持するログファイルを高頻度でバックアップしておく
  • バックアップファイルとログファイルで復旧できる

第12章　テーブルの設計

• データベース設計の流れ

  • 概念設計
    • ユーザから聞いた要件をエンティティと属性に整理し、ER図を作成する
  • 論理設計
    • エンティティをRDBモデルで扱いやすいテーブルに整理する
      • 主キーの整理、正規化
  • 物理設計
    • DBMS選定後に、サポートする型、制約、インデックス、利用するハードウェアの制約を考慮し、すべてのテーブルの詳細設計を行う
      • 最終的にデータベース内に作られるテーブル名やカラム名を物理名という
      • 論理設計までの段階までに利用してきた名前を論理名という

• 主キーが備えるべき3つの特性

  • 非NULL性
  • 一意性
  • 不変性

• 正規化

  • 第一正規形
    • 非正規形から繰返しの列やセルの結合をなくす
      • 繰り返しの列の部分を別の表に切り出す
      • 新しい表で仮の主キーを決める
      • 元の表の主キーをコピーして複合主キーにする
  • 第二正規形
    • 複合主キーの一部にのみ関数従属する列をなくす
      • 複合主キーの一部に関数従属する列を切り出す
      • 元の表で部分関数従属していた列をコピーする
  • 第三正規形
    • 主キーに関数従属する列に関数従属する列をなくす
      • 間接的に主キーに関数従属している列を切り出す
      • 元の表で直接的に関数従属していた列をコピーする

• トップダウンアプローチで作成したER図を元に、ボトムアップアプローチで作成したER図から得られる情報を適切に取り込むことが大切

  • トップダウンアプローチ
    • 新システム要件を元に概念設計、論理設計を行う
    • これだけでは現状が反映されない
  • ボトムアップアプローチ
    • 現行システムや資料を元に論理設計を行う
    • これだけでは新システムの要望が反映されない

SQLドリルでよく間違えること

• 文字列は'で囲み、ASの名称は"で囲むこと
• CASTしなくても通る場合があるが、実務ではCASTしないとインデックスが使われずパフォーマンスが低下するので、使用するようにすること