20210227・28_アウトプット(データベース・SQL)
2/27行ったこと 7時間40分
- スッキリわかるSQL入門 第1章〜第5章まで
👉実際に手を動かしながら理解を深める。
2/28行ったこと 13時間
スッキリわかるSQL入門 第6章〜第12章まで(一番最後の章) ...10時間
👉実際に手を動かしながら理解を深める。アウトプット…3時間
👉あやふやだったところの理解が進む(特に演算子とサブクエリ)
新しい発見(昨日の本との差分)
第1章 初めてのSQL
- データベース管理システム(DBMS:Database Management System)
第2章 基本文法と4大命令
CHAR
(固定長)とVARCHAR
(可変長)CHAR
(固定長)については、指定に満たない部分は文字列の右側に空白が追加されぴったりに調整されてから格納されるのを知らなかった。(郵便番号や社員番号に向いている)AS
を使うと、列やテーブルに別名を定義できる。UPDATE
文を使うときは「どれを更新したいのか」選択することがほとんど。(WHERE
で条件をつける。)DELETE
文は「行」を削除するもの。(「列」を削除するものではない。)なのでDELETE FROM テーブル名 (WHERE 装飾)
と列名を指定する必要がない。WHERE
のないDELETE
文は全件削除になる。(こわ!)INSERT
文は、列の指定順番が重要。(指定した列名と値は同じ順番にする。列名を指定しない場合には、カラム名と同じ順番となる。)4大命令共通で、テーブルを必ず指定する。(列名は「削除」以外、基本つける)
「検索形」(
SELECT
のみ)or「更新形」
👉だから、SELECT
には、いろいろ選ぶときのオプションつけられる。「既存形」or「新規形」(
INSERT
のみ)
👉だから、「既存形」にはWHERE
つけて条件で探す。
第3章 操作する行の絞り込み
比較演算子について「左右の値が正しくない」場合には
<>
使う😲(!=
じゃないんだ)NULL
の扱いは「未定義」になる。(真と偽ではない!!)そのため、
=
・<>
は使えないので、必ずIS NULL
や、IS NOT NULL
を使って条件式を作らないといけない。BETWEEN演算子は「〇〇以上」「〇〇以下」(
=
も含まれると理解)IN演算子
やNOT IN 演算子
は複数値との比較ができる!(=
だと一つだけ🌱)同じように複数値検索できるものでANY演算子
やALL演算子
もある。
複数値を探せる演算子 | 役割 | 内容 |
---|---|---|
IN演算子(=ANY と同じ意味) |
一致の判定 | どれかと一致 |
NOT IN 演算子(<>ALL と同じ意味) |
一致の判定 | どれとも一致してない |
ANY演算子 | 大小の比較 | どれかより大きい(小さい) |
ALL演算子 | 大小の比較 | 全てより大きい(小さい) |
優先順位は①NOT、②AND、③ORの順と理解。(異なる場合はカッコを使おう!)
時間含む日付の判定について。DATE型を条件式に使うとき、DBMS製品によって時間のズレが生じることがある。例えば、3/31までのデータを取りたい場合には、「3/31まで(
<=
)」と検索しないで、「4/1より前(<
)」で検索しよう。問題文で「より」だと
=
は含まない🙂(「以上」や「以下」は含む)ほー!と思ったのが、
UPDATE
文で【例:成績が〇〇の人は「A」を、●●の人は「B」を、△△の人は「C」の総合成績結果をいれる】という命令があったときに、条件の最後にAND 総合成績 IS NULL
と入れないとすでに入れ終わってた成績が上書きされてしまうこと!😲
第4章 検索結果の加工
重複を消すことのできる
DISTINCT装飾子
だけ、SELECT文
の最初に記述する。(他のものはSELECT文以降に記述することが多い😄)地味に間違える🙂
並び替え | オプション |
---|---|
ASC | だんだん大きくなる(階段をこれから昇るよー⬆️) |
DESC | だんだん小さくなる(階段をこれから降りるよー⬇️) |
OFFSET
は位置を基準点から表した値で、FETCH
は呼んでくると理解。(OFFSET 先頭から除外する行数 ROWS
・FETCH NEXT 取得行数 ROWS ONLY
)FETCH
は省略可能(指定しない場合には全ての行が抽出)UNION
が使えるのはテーブルの列数とデータ型がぴったり一致しているときだけ!(和集合で、下に足すから!)数が合わないSELECT文で、どうしても和集合を使いたいときは、足りない方の選択リストに
NULL
を追加すると出来る(ほー!)
集合演算子のその他 | 内容 | こんなときに使える(例) |
---|---|---|
EXCEPT(エクセプト)あるいはMINUS(マイナス) | 2つのSELECT文の差 | 先月にはないけど今月にはあるデータ |
INTERSECT(インターセクト) | 共通項 | 先月にも今月にもあるデータ |
第5章 式と関数
SELECT
文の列選択で計算式を使うときは可読性のためにAS
使おう。(列名が「支出額+100」等となってしまうため)関数の呼び出しは「各行」ごとに行われる。
COALESCE
(コアレス)関数は、NULL
に代替値を入れるのに使える🐨
第6章 集計とグループ化
集計関数の結果は必ず1行になる(計算結果だから)
結果表が凸凹するとエラーになるので、
ORDER BY句
を使って「〇〇ごと」を表現。集計関数が使える場所 は2箇所のみ!(なぜなら、検索結果に使うから)
(1)SELECT文
(2)その他装飾(ORDER BY句
・HAVING句
のみ)COUNT
関数は、行数を取得する関数なので、列を指定しないでSELECT
文を使える。
ちょっとした違い | 内容 |
---|---|
count(*) | NULLも入れて全行 |
count(列) | NULLは入れない。 |
- 集計関数の行われる順番が面白い。(
WHERE
の部分に「1円以上(0円は計算入れないように)」としても、(3)の前なので意味なくなっちゃう。そのためのハビング句!)
集計関数の実行される順番 | 内容 | 備考 |
---|---|---|
(1) | WHERE句による絞り込み | |
(2) | グループ化 | 費目ごとに計算する、都市ごとの最高気温を計算する |
(3) | 集計 | 合計・平均・最高値・最小値など計算! |
(4) | HAVING句 | 1円以上の行(0円は計算入れないように)計算したい・最高気温が40度以上の都市を探したい |
- 非常に膨大な行の集計テーブルを毎回作成するのは負荷がかかる。そのために、こまめに「集計テーブル」を作っておき、その「集計テーブル」等を集計関数で計算すると負荷が減ることを知った。(ただし、更新作業も必須!)
第7章 副問い合わせ
- 内側から外側の順番で処理が実行される。
パターン(副問い合わせの結果) | よく使われる場所 | 備考 |
---|---|---|
1:単一(1行1列) | SELECT文の選択列リスト・FROM句・UPDATEのSET句・WHERE句の1つの値としての条件式 | |
2:複数行(N行1列) | WHERE句の条件式・SELECT文のFROM句 | 複数の値を列挙するときに使う。IN ・ANY ・ALL 演算子と使うことが多い。 |
3:表形式(N行M列) | SELECT文のFROM句・INSERT文 | 副問い合わせで使用する表にAS で別名をつけて利用する。 |
NOT IN
または<>ALL
で(どれとも一致しない)判定する問い合わせの時に、NULL
が含まれていると結果もNULL
になってしまうためコアレスで対処しよう。🐨(またはIS NOT NULL
)
第8章 複数テーブルの結合
SELECT 選択列リスト FROM テーブルA JOIN テーブルB(結合する他の表を指定) ON 両テーブルの結合条件(例 A.id = B.id)
NULLで結合相手がいないときは、結合結果から消えることを知った😱(内部結合)
なので
LEFT JOIN
(結合元のNULLは消さないで)やRIGHT JOIN
(結合先のNULLは消さないで)やFULL JOIN
(両方のNULLは消さないで)があるのか!!(納得)結合と共に副問い合わせを使う場合には
AS
による別名指定が必須。
第9章 トランザクション
複数の利用者による副作用 | 内容 |
---|---|
ダーティーリード | 未確定の変更を他の人が読めてしまう👀 |
反復不能読み取り | SELECT文を実行直後に、他の人がUPDATEをすると、検索結果が異なってしまう |
ファントムリード | SELECT文を実行直後に、他の人がINSERTをすると、検索結果が異なってしまう👻 |
第10章 テーブルの作成
データ制御。権限の付与は
GRANT
(グラント)で、剥奪はREVOKE
(リヴォーク)テーブルの作成。
CREATE TABLE
テーブルの削除。
DROP TABLE テーブル名
テーブル定義の変更。
ALTER(オルター) TABLE テーブル名 ADD 列名 型 制約
👉列の追加テーブル定義の変更。
ALTER(オルター) TABLE テーブル名 DROP 列名 型 制約
👉列の削除
第11章 様々な支援機能
①WHERE句に頻繁に登場する列②ORDER BY句に頻繁に登場する列③JOINの結合条件に頻繁に登場する外部キーの列はインデックスを設定すると検索が早くなる。
ただし、ディスク容量の消費や、本の内容を変えたら索引のページも変わるように、UPDATEやINSERTした時にインデックスにも書き換えが発生するので乱用はNG❎
CREATE INDEX インデックス名 ON テーブル名(列名)
DROP INDEX インデックス名
よく使う検索結果はビューにできる(お客様との表示を別にしたい時にも使える)。中身の実体としては「名前をつけたSELECT文」なので、仮想的なテーブルであることに留意。
CREATE VIEW ビュー名 AS SELECT文
DROP VIEW ビュー名
採番の仕組み(道具)の一つとして「シーケンス」があるのかと理解。
バックアップだけでなく、ロールフォワードをするためにログも大事。
第12章 テーブルの設計
オリジナルアプリの時に苦しんだため、内容がよくわかった。
現実世界の物事(お客様の課題・悩み等)を、パッとER図に落とし込めるよう鍛錬したい。
感想
実際に、手を動かしてみると忘れがちな命令文に気づくことができた。第4章あたりから基本構文については手が慣れてきた。
この本、英語に振り仮名ふってあるのが嬉しかったなあ(いつも読めなくてググっているため)
構造化して意味を理解できて良い本だった!最後の練習問題まで行きたかったけれど時間切れ。明日やろう。
抽出する際に、日本語を読み間違えて、ミスすることも多々あった。言葉の裏にある意図を拾えるようになりたい。
基本的な部分はシンプルなのに、組み合わさって高度にもできるSQL。面白いなあ。関数やサブクエリから一気に難しくなった。紐解いてみると、シンプルだけど奥が深い。
想像していたよりも、各DBMSによる違いがいろいろありそう。今やっていることは共通した命令文の部分だけれど、差分もわかって対応できる力を身につけていきたい。