皆様こんにちは、Amiaです。
私はここ何週間かでタイトルにもある通り『スッキリわかるSQL入門』を読んでいました。
そのため今回は上記の書籍について読んだ感想等をまとめいきたいと思います。
よろしければ最後までご覧になっていただければと思います。
⚫︎良かった点
- イラストや図が多く使用されている。
- 巻末に256問という多くの問題が掲載されているため、インプット後の復讐になる。
⚫︎学んだこと
「第2章 基本文法と4第命令」
- SQL文の末尾にセミコロンを付けることで文の終了を表す。
- 予約語は大文字と小文字のどちらで記述してもよい。
- シングルクォートで括らずに記述されたリテラルは数値として扱われる。
- シングルクォートで括られたリテラルは基本的に文字列として扱われる。
- シングルクォートで括られて、
'2022-02-25'のような一定の形式で記述されたリテラルは日付として扱われる。
「第3章 操作する行の絞り込み」
- WHERE句に記述できるものは、結果が必ず真(TRUE)または偽(FALSE)となる条件式のみ。
→1行ずつ順番に条件に合うかどうかチェックするため。
NULLは=や<>では判定できない。必ずIS NULLやIS NOT NULLを使用して条件式を作る。
- パターン文字列の中で、単なる文字として
%や_を使いたい場合は、下記のようにESCAPE句を併用した記述を行う。
SELECT * FROM 家計簿 WHERE メモ LIKE '%100$%' ESCAPE '$'
- IN演算子:括弧内に列挙した複数の値(値リスト)のいずれかにデータが合致するかを判定する演算子。
SELECT * FROM 家計簿 WHERE 費目 IN('食費', ' 交通費')
- NOT IN演算子:括弧内に列挙した値のどれとも合致しないことを判定する。
<> ALLと同じ。
SELECT * FROM 家計簿 WHERE 費目 NOT IN('食費', '交通費')
- ANY演算子:括弧内に列挙した値リストのそれぞれと比較して、いずれかが真なら全て真。
INと同じ。
式 基本比較演算子 ANY(値1, 値2, 値3...)
- ALL演算子:括弧内に列挙した値リストのそれぞれと比較して全て真なら真。
式 基本比較演算子 ALL(値1, 値2, 値3...)
- 主キーとなる列が持つべき特性
- 必ず何らかのデータが格納される。(
NULLではない。)
- 他の行と値が重複しない。
- 一度決めた値は変化しない。
「第4章 検索結果の加工」
- ORDER BY句を付けないSELECT文では、結果表の各行の並び順は実質的に「ランダム」である。
- OFFSET-FETCH句:OFFSET句には先頭から除外したい行数を記述する。除外せずに1件目から取得したい場合には0を指定する。FETCH句には取得したい行数を指定する。FETCH句を省略すると該当するすべての行が抽出される。
SELECT 費目, 出金額 FROM 家計簿 ORDER BY 出金額 DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
- UNION演算子:2つのSELECT文を
UNIONで繋いで記述すると、それぞれの検索結果を足し合わせた和集合の結果が返される。
- EXCEPT演算子:あるSELECT文の検索結果に存在する行から別のSELECT文の検索結果に存在する行を差し引いたさ集合を返す。
- INTERSECT演算子:2つのSELECT文に共通する行を集めた積集合を返す。
「第5章 式と関数」
- DBMSによる処理の原則
- DBMSは、テーブル内の各行を1つずつ順番に処理していく。
- 式の評価等も各行で行われる。
文字列 || 文字列で文字列同士を連結できる。
- CASE演算子:列の値や条件式を評価し、その結果に応じて値を自由に変換する。
SELECT 費目, 出金額,
CASE 費目 WHEN '居住費' THEN '固定費'
WHEN '水道光熱費' THEN '固定費'
ELSE '変動費'
END AS 出費の分類
FROM 家計簿 WHERE 出金額 > 0
- 関数はDBMS製品によって構文や機能が大きく異なるため、詳細は製品マニュアルを参照する必要がある。
- TRIM関数:ある文字列の前後についている余計な空白を除去する。類似する機能を持つ関数として下記のものがある。
- LTRIM関数:左側の空白を除去した文字列を返す。
- RTRIM関数:右側の空白を除去した文字列を返す。
- REPLACE関数:文字列の一部を別の文字列に置換する関数。
- SUBSTRING関数 / SUBSTR関数:文字列の一部分を取り出す。どちらを利用できるかはDBMS製品によって異なる。
- CONCAT関数:文字列を連結する。
- ROUND関数:指定した位置で四捨五入した結果を返す。有効とする桁数に指定する値が正の場合は少数部の桁数、負の場合は整数部の桁数を表す。
- TRUNC関数:指定桁で切り捨てる。有効とする桁数に指定する値が正の場合は少数部の桁数、負の場合は整数部の桁数を表す。
POWER関数:ある値のべき乗を計算する。
現在の日時を得る関数は下記のようなものがある。
- CURRENT_TIMESTAMP関数:現在の日時(年、月、日、時、分、秒)を得る。
- CURRENT_DATE関数:現在の日付(年、月、日)を得る。
- CURRENT_TIME関数:現在の時刻(時、分、秒)を得る。
CAST関数:データ型を変換する。
- COALESCE関数:受け取った引数を左から順番にチェックして、その中から最初に見つかった
NULLでない引数を返す。また下記のように記述することで「NULLの場合の代替値を明示的に表示する」ことができる。
SELECT 日付, 費目,
COALESCE(メモ, '(メモはNULLです)') AS メモ,
入金額, 出金額
FROM 家計簿
「第6章 集計とグループ化」
- 集計関数の結果は必ず1行になる。
COUNT(*)とCOUNT(列)の違い。
COUNT(*)は、単純に行数をカウントする。(NULLの行も含める。)
COUNT(列)は、指定列がNULLである行を無視してカウントする。
- 集計関数はSELECT文の選択列リストかORDER BY句、HAVING句だけに記述できる。
- SQLの結果表について
- 結果表は必ず長方形型になる。
- 結果表が凸凹になるようなSQL文は実行できない。
- GROUP BY句に複数の列をカンマで区切って指定すれば、複数の列を基準にしたグループ化をすることもできる。
- 集計関数はWHERE句に記述できない。
→行を絞り込む段階ではまだ集計が終わっていないため。
- HAVING句:集計処理を行った後の結果表に対して絞り込みを行いたい場合に使用する。
- SELECT文の基本構文
SELECT 選択列リスト
FROM テーブル名
[WHERE 条件式]
[GROUP BY グループ化列名]
[HAVING 集計結果に対する条件式]
[ORDER BY 並び替え列名]
- グループ集計を行うSELECT文の選択列リストに指定する列は、下記のどちらかに当てはまるものでなければならない。
①GROUP BY句にグループ化の基準列として指定されている。
②集計関数による集計の対象となっている。
「第7章 副問い合わせ」
- 副問い合わせの3つのパターン
①単一行副問い合わせ:副問い合わせの検索結果が1行1列の値になるパターン。
②複数行副問い合わせ:副問い合わせの検索結果が複数の行から成る単一列(n行1列)の値になるパターン。
③表形式の結果となる副問い合わせ:副問い合わせの検索結果が複数の行と複数の列から成る表形式(n行m列)の値となるパターン。
- 相関副問い合わせ
- 主問い合わせがテーブルから行を絞り込む過程で各行について抽出の可否を判断するために、繰り返し副問合せを実行する。
- 上記の挙動を行うことから、DBMSの負荷は大幅に増加する。
「第8章 複数テーブルの結合」
- 左外部結合(LEFT JOIN):NULLの行を生み出してでも、左表の全行を必ず出力する。
- 右外部結合(RIGHT JOIN):NULLの行を生み出してでも、右表の全行を必ず出力する。
- 完全外部結合(FULL JOIN):NULLの行を生み出してでも、左右の表の全行を必ず出力する。
- 外部結合:本来結果表から消滅してしまう行も強制的に出力する結合。
- 内部結合:結合すべき相手の行が見つからない場合に行が消滅してしまう結合。
- FULL JOINを利用できないDBMSでは、UNIONを使用して同等の処理を実現できる。
SELECT 選択列リスト FROM 左表の名前
LEFT JOIN 右表の名前
ON 左表の結行条件列 = 右表の結合条件列
UNION
SELECT 選択列リスト FROM 左表の名前
RIGHT JOIN 右表の名前
ON 左表の結合条件列 = 右表の結合条件列
- 非統合結合
- 結合の条件には=以外の演算子を用いた条件式も記述することができる。
- 動作の仕組みは通常の結合と同じだがDBMSにかかる負荷は大きなものとなる。
- DBMSに対して複数の利用者が同時に処理を要求することで発生する副作用には下記の3つが知られている。
①ダーティーリード:まだコミットされていない未確定の変更を他の人が読めてしまう副作用。
②反復不能読み取り:あるテーブルに対してSELECT文を実行した後、他の人がUPDATE文でデータを書き終えると次にSELECTした際に検索結果が異なってしまう副作用。
③ファントムリード:2回のSELECT文の間に他の人がINSERT文で行を追加すると、最初と次のSELECTで取得する結果の行数が変わってしまう副作用。
- 上記の副作用は各トランザクションを分離することで防ぐことができる。
- SQL文を使って指定した対象を明示的にロックすることができる。
- ロックをかける際には制限の強さを指定することができる。
- 排他ロック:他からのロックを一切許可しないため主にデータの更新時に利用される。
- 共有ロック:他からの共有ロックを許す特性があるためデータの読み取り時に多く利用される。
- 通常、SELECT文で選択した行には自動的に共有ロックがかかる。
NOWAITオプションを指定した場合にはDBMSはロックの解除を待機せずにすぐさまロック失敗のエラーを返すため、トランザクションは即時終了する。
- 表全体をロックすることも可能。
- ロックは最小限にすること！
- 明示的にロックする時は、必要最小限の範囲に留める。
- 排他ロックの代わりに共有ロックを使用できないかを検討する。
- デッドロック:データベースで同時にたくさんのトランザクションが実行されると稀に陥る状態。トランザクションの処理が途中で永久に止まってしまう。
- デッドロックを予防する方法
①トランザクションの時間を短くする。
②同じ順番でロックするようにする。
「第10章 テーブルの作成」
- 予期しない値を格納できないように制限をかけることで、人為的ミスによるデータ破壊の可能性を減らすことができる。
- 制約はCREATE TABLE文でテーブルを定義する際に、列定義の後ろに指定することが可能。
①NOT NULL制約:設定された列には、NULLの格納は許可されない。NOT NULL制約はDEFAULT指定と組み合わせて利用されることがほとんど。
②UNIQUE制約:ある列の内容が決して重複してはならない場合に付ける。
③CHECK制約:ある列に格納される値が妥当であるかを細かく判定したい場合に付ける。「CHECK」の後ろに括弧内に記述した条件式が真となるような値だけが格納を許される。
*主キー制約:主キーの役割を担う列に付ける。この制約が付いている列は「NULLも重複も許されない列」ではなく、そのテーブルで管理しているデータを一意に識別する。主キー制約を付ける方法は下記の2つがある。
①主キー制約の指定(単独列)
CREATE TABLE 費目(
ID INTEGER PRIMARY KEY,
名前 VARCHAR(40) UNIQUE
)
②主キー制約の指定(複合主キー)
CREATE TABLE 費目(
ID INTEGER,
名前 VARCHAR(40) UNIQUE,
PRIMARY KEY(ID, 名前)
)
- 外部キー制約:参照整合性が崩れるようなデータ操作をしようとした場合にエラーを発生させて、強制的に処理を中断させる制約。CREATE TABLE文で外部キー制約をかけるには、下記のように指定する。
外部キー制約の指定方法①
CREATE TABLE テーブル名(
列名 型 REFERENCES 参照先テーブル名(参照先列名)
:
)
外部キー制約の指定方法②
CREATE TABLE テーブル名(
:
FOREIGN KEY(参照元列名)
REFERENCES 参照先テーブル名(参照先列名)
)
「第11章 さまざまな支援機能」
- ある列についてインデックスを作成すると、その列に関する検索が高速化する。
- インデックス設定の効果が得られやすい列
- WHERE句に頻繁に登場する列
- 完全一致検索や前方一致検索ではインデックスを利用した高速な検索が行われることがある。
- 部分一致検索や後方一致検索ではインデックスは利用されない。
- ORDER BY句に頻繁に登場する列
- JOINの結合条件に頻繁に登場する列(外部キーの列)
- インデックスを作成することによるデメリット
- 索引情報を保存するためにディスク容量を消費する。
- テーブルのデータが変更されるとインデックスも書き換える必要があるため、INSERT文、UPDATE文、DELETE文のオーバーヘッドが増える。
- DBMSには連番を管理する機能が提供されている。
- ACID特性:データを正確かつ安全に取り扱うためにシステムが備えるべき4つの特性。
- 原子性:処理が中断しても中途半端な状態にならない。
- 一貫性: データの内容が矛盾した状態にならない。
- 分離性:複数の処理を同時実行しても副作用がない。
- 永続性:記憶した情報は消滅せず保持され続ける。
- ロールバック(実行した処理を取り消す):データベースの利用中に実行失敗やデッドロックなどを要因として度々発生する。
- ロールフォワード(まだ実行されていない処理を実行する):障害復旧時に行われる処理であるため滅多に発生しない。
「第12章 テーブルの設計」
- データベース設計の流れ
- 概念設計:取り扱うエンティティとその関連を明らかにする。
- 論理設計:キー設計や正規化等を行いRDB用のモデルに変換する。
- 物理設計:採用するDBMS製品に依存した詳細な設計に落とし込む。
- 主キーが備えるべき3つの特性
- 非NULL性:必ず何らかの値を持っている。
- 一意性:他と重複しない。
- 不変性:一度決定されたら値が変化することがない。(主キーは、一貫して同じ1行を指し示す。)
- 正規化
- 第1正規形:非正規形から繰り返しの列やセルの結合をなくす。
- 繰り返しの列の部分を別の表に切り出す。
- 切り出したテーブルの仮の主キーを決める。
- 主キー列をコピーして複合種キーを構成する。
- 第2正規形:複合主キーの一部の列に対してのみ関数従属する列をなくす。
- 複合主キーの一部に関数従属する列を切り出す。
- 部分関数従属していた列をコピーする。
第3正規形:主キーに関数従属する列にさらに関数従属する列をなくす。
- 間接的に主キーに関数従属する列を切り出す。
- 直接的に関数従属していた列をコピーする。
⚫︎難しく感じた部分
設計の部分が難しく感じました。資料から非正規形にする部分は正解が1つというわけではないため特に難しいと感じました。SQL文についても言えることだと思いますが、とにかく経験を積んで慣れていくことが重要だと思いました。
⚫︎最後に
巻末問題を最後に行いましたが、ボロボロでした😭
もしかしたら各章を終えたごとに問題に取り組んでいったほうがよかったかもしれませんね...。
これから経験を積んで慣れていきたいと思います。
気になった方は是非購入を検討してみてはいかがでしょうか。
