スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリわかる入門シリーズ)
感想
この1冊でSQLとDB設計の基本を学べます。
最初にSQLの全体像が説明されているので、今何を学んでいるのか振り返るのに役立ちました。
章末にまとめと練習問題があり、さっと内容を思い出しながらアウトプットができるので便利です。
また、本の最後にはSQLとテーブル設計の練習ドリルがあり、問題数も多いのでかなり力がつくと思います。
練習ドリルは最初の方は簡単ですが、問題が進むにつれて複雑なSQLを書かなければならない問題もあり、かなり難しかったです。
SQLの理解だけでなく、どの命令を使うのか、どう絞り込むのか、どう並び替えるのかなどを問題から読み取る読解力が必要でした。
ただ、解答を別でダウンロードしなければならないので面倒なのと、問題の解説がないのでそこは難点でした。
この本をこなすだけでも後半の内容は難しかったので、実務に入れたとしても絶望しそうで心配です。
複雑な副問い合わせ、テーブル結合が特に難しかったので、もう一度復習しようと思います。
新しく学んだこと
以下に新しく学んだことを備忘録のため書いていきます。
2章 基本文法と4大命令
- SQLの分類
- 検索系と更新系
- 検索系 - SELECT（検索結果が返ってくる）
- 更新系 - INSERT、DELETE、UPDATE（SQLが成功したか失敗したかが帰ってくる）
- 既存系と新規系
- 既存系 - SELECT、UPDATE、DELETE
- 新規系 - INSERT（WHERE句を使えない）
- 検索系と更新系
3章 操作する行の絞り込み
- <> - 左右の値が等しくなければtrueを返す
- nullは比較演算子を使えないので、IS NULLかIS NOT NULLを使う
- IN/NOT IN
- IN - ()内に列挙した値が式に合致するものを取得する
- NOT IN - ()内に列挙した値が式に当てはまらないものを取得する
ANY/ALL
複数の列を1つの種キーとして扱うものを複合主キーという
4章 検索結果の加工
OFFSET - FETCH
- OFFSET 除外行数 ROWS FETCH 取得行数 NEXT ROWS ONLY
- 先頭から数件だけ取得する
- OFFSETには先頭から除外したい行数を指定する（1件目から取得したい場合は0を指定）
- FETCHには取得したい行数を指定する（省略すると全ての行が取得される）
- LIMITでも代用可
集合演算子
- UNION
- 2つのSELECT文の結果を足し合わせる
- 選択列のリストとデータ型が一致していなければならない
- ORDER BY句は最後のSELECT文に記述する
- UNION (ALL)とすると重複した行をまとめずに取得する
- EXCEPT
- 2つのSELECT文の結果の差を取得する
- INTERSECT
- 2つのSELECT文に共通する結果を取得する
- UNION
5章 式と関数
- 選択列リストで計算を行ったものを取得できる
- 計算した結果にはASを使って別名をつける
- INSERTやUPDATEで値そのものではなく式を指定することができる
DBMSは処理を行うとき、テーブル内の各行を1つずつ順番に処理していく
- 例えば、UPDATEで複数の行を更新するとき、1度に行が更新されるのではなく、更新したい複数の行が1行ずつ更新されていく
||で文字列を連結できる
CASE式
- 列の値や条件式を評価して、その結果に応じて値を変換できる
- if文やSwitch文のようなもの
- 書き方は2種類ある
- CASE 評価する列や式 WHEN 値1 THEN 値1の時に返す値 .. (ELSE デフォルト値) END
- CASE WHEN 条件1 THEN 条件1のときに返す値 .. (ELSE デフォルト値) END
関数はDBMSによって構文が異なるので注意
- 関数
- TRIM - 左右の文字列の空白を除去した値を取得する
- REPLACE - 文字列を変換する
- SUBSTRING - 文字列の一部を切り出す
- CONCAT - 文字列を連結する
- ROUND - 指定した位置で四捨五入する
- TRUNC - 指定した位置で切り捨てる
- POWER - べき乗を計算する
- CURRENT_DATE - 現在の日付を得る
- CURRENT_TIME - 現在の時刻を得る
- CAST - データ型を変換する
- COALESCE
- 最初に登場するNULL出ない値を返す
- NULLを別の値で表示できる
6章 集計とグループ化
- 集計関数は必ず1行になる
- COUNT(*)とCOUNT(列)の違い
- COUNT(*) - NULLの行も含めてカウントする
- COUNT(列) - 指定列の値がNULLである行を無視してカウントする
- 集計関数はWHERE句の中では使えない
- 集計結果に対して絞り込む時はHAVINGを使う
- 毎回SQLを実行して集計を行うのではなく、集計結果を保存した集計テーブルを使うこともある
- 集計結果が最新のものではなくなる可能性があるので更新作業が必要
7章 副問い合せ
- SQLの内部に別のSELECT文を記述するのが副問い合わせ（サブクエリ）
- 副問い合わせによって1つの文で2つ以上の処理を行うことができる
- 副問い合わせは別で使いたいSELECT文を()でくくって書く
()でくくったSELECT文が先に実行される
副問い合わせのパターン
- 検索結果が1行1列の1つの値になる（単一行副問い合わせ）
- 主にWHERE句や選択列リストで使う
- 検索結果がn行1列の値になる（複数行副問い合わせ）
- IN、ANY、ALLなどの複数の値を判定する時に使う
- 検索結果がn行n列の表形式の値になる（表形式）
- FROM句で使う
- 副問い合わせの結果をテーブルとして使える
- INSERT文で使う（厳密には副問い合わせではなくINSERTの特殊構文）
- 1度のINSERTで複数の行を登録できる
- ()でくくらない
- FROM句で使う
- 検索結果が1行1列の1つの値になる（単一行副問い合わせ）
副問い合わせでNULLが含まれると全体の結果もNULLになる
- NULLの対策
- IS NOT NULLを使ってNULLを省く
- COALESCEを使ってNULLを別の値に置き換える
- NULLの対策
FROM句で副問い合わせを使う場合はASで別名をつける
8章 複数テーブルの結合
- 結合するテーブル同士は対等な関係ではなく、FROM句で指定したテーブルが主役になる
- 結合する相手が複数行の場合
- 複数行に合わせて行を複製する
結合する相手の行がない場合や、結合条件の列がNULLの場合
- 結合結果が消滅する
LEFT JOINとRIGHT JOINとFULL JOINの違い
- LEFT JOIN - 左表の結合相手が見つからなくても、NULLであっても出力する
- RIGHT JOIN - 右表の全行を出力する
- FULL JOIN - 左右の表の全行を出力する
外部結合と内部結合の違い
- 外部結合 - 結果から消滅してしまう行も強制的に出力する結合
- 内部結合 - 結合する行が見つからない場合は行が消滅する結合
さまざまな結合
- 3テーブル以上を結合する - JOINを足していく
- 副問い合わせの結果との結合 - JOINの後ろに()を書いて結合する
- FROM句で指定したテーブル（自分自身）と結合する - 同じテーブルに別名をつける
9章 トランザクション
-
- 処理が中断されないようにする
- 他の人の処理が割り込めないようにする（分離）
一連の処理を確定させるのがコミット、処理を中断して無かったことにするのがロールバック
原子性 - すべてのSQL文が実行されているか、1つも実行されていないかのどちらかの状態になるようにする
書き方
- BEGIN - トランザクション開始
- COMMIT - 異常がなければこの指示までの変更を確定する
- ROLLBACK - 異常があれば変更を取り消す
副作用 - データベースには大量のSQLが送られ、DBMSはそれを同時に処理しようとするので、データがおかしくなる可能性がある
3つの副作用
分離性 - トランザクション同士の影響を受けないように分離する
ロック
分離レベル
- READ UNCOMMITTED - 全ての副作用の恐れあり
- READ COMMITTED - ダーティリードだけ防ぎ、大抵の場合はこれで十分
- REPEATABLE READ - ファントムリードの恐れあり
- SERIALIZABLE - 最も厳しくロックするが速度は落ちる
分離レベルの指定
- SET TRANSACTION ISOLATION LEVEL 分離レベル
- SET CURRENT ISOLATION 分離レベル
明示的なロック
- ロックの厳しさ
- 排他ロック - 他からのロックを許可しない
- 共有ロック - 他からの共有ロックを許可する
- 種類
- 行ロック - SELECT ~ FOR UPDATE
- FOR UPDATEをつけることで共有ロックから排他ロックに変わる
- 表ロック - LOOK TABLE テーブル名 IN モード名 MODE
- MODEがEXCLUSIVE - 排他ロック
- MODEがSHARE - 共有ロック
- 行ロック - SELECT ~ FOR UPDATE
- ロックの厳しさ
-
- お互いがロックしようとして解除を待っている状態
- トランザクションの処理が途中で永久に止まってしまう
10章 テーブルの作成
4つの命令
TRUNCATE TABLE テーブル名
- テーブルを初期化する（テーブルを1度削除して同じものを作成する）
- ロールバックできない
- DELETEで全行削除するより高速
CHECK制約
- ある列に格納される値が妥当なものかどうか判定する
- CHECKの後ろの()内に記述した条件式が真となる値だけ格納される
11章 さまざまな支援機能
- インデックスは列ごとに作られる
- CREATE INDEX インデックス名 ON テーブル名(列名) - インデックス追加
DROP INDEX インデックス名 - インデックス削除
インデックスによって高速化するパターン
- WHERE句による、完全一致検索と、前方一致検索（部分一致、後方一致ではインデックスを利用できない）
- ORDER BYによる並び替え
- JOINによる結合の条件
インデックスによるデメリット
- ディスク容量を消費する
- データの変更があるとインデックスも更新しなければならないので、INSERT、UPDATE、DELETEのオーバーヘッドが増える
ビューを使うことでSQL文を使いまわせる
- CREATE VIEW ビュー名 AS SELECT文 - ビューを作成
- DROP VIEW ビュー名 - ビューを削除
- ビューの実態は単なる名前をつけたSELECT文
ビューによって処理が重くなる場合もある
採番 - 追加する行に独自の番号を振るために、適切な番号を取得すること
採番を振る方法
- 採番した番号を専用のテーブルに記録する
- 連番を振る列を指定する
- 連番を管理する専用の道具であるシーケンスを使う
- CREATE SEQUENCE シーケンス名
- DROP SEQUENCE シーケンス名
バックアップ方式
- オフラインバックアップ - データベースを停止して行うバックアップ
- オンラインバックアップ - 稼働しながら行うバックアップ
2つのファイルをバックアップする
12章 テーブルの設計
データベース設計の順番
- 概念設計 - 扱うデータと、データの関係を決める
- 論理設計 - テーブルと列を整理して決める
- 物理設計 - 実際にテーブルを作成する
データの概念
- エンティティ - テーブル
- 属性 - 列
- 関連 - リレーションシップ
ER図
- 概念設計をまとめる図
- エンティティ、属性、リレーションシップを俯瞰して見ることができる
- IEとIDEF1Xという2つの書き方がある
- エンティティ同士の数量的な関係を多重度やカーディナリティという
リレーショナルデータベースは多対多の関係を扱えないので、中間テーブルを作成し、2つの1対多の関係に変換する
正規化
- データの更新や検索が行いやすいようにテーブルを分割していく作業
- 正規化を行わないと、データが重複したり、複数の箇所を更新しなければならなくなる
- テーブル設計の原則は「1つの事実は1箇所に」
- 基本的に第3正規形まで覚えておけばいい
- 正規化が行われていない状態を非正規化といい、パフォーマンスを考慮して最後の手段として使う場合もある
正規化の手順
- 非正規形→第1正規形
- 概念設計をしっかりやっておくと非正規形になっている可能性はほとんどない
- テーブルのすべての行のすべての列に1つずつ値が入るようにする
- 繰り返しの列とセルの結合がない状態
- 第1正規形→第2正規形
- 部分関数従属（複合主キーのどちらかだけに関数従属している）がない状態にする
- 複合主キーを持たないテーブルは部分関数従属はない
- 第2正規形→第3正規形
- 推移関数従属（間接的に関数従属している）がない状態にする
- 非正規形→第1正規形
物理設計の手順
- テーブル名、列名を決める
- 列の型を決める
- 制約、デフォルト値を決める
- インデックスを決める
- その他（ビューの作成や非正規化など
ITの世界では正規化で整合性を保つ形にして、人間の世界では結合でわかりやすい形にする
