目次
本書について SQLって何だろう? どのように学ぶ?
本書の構成
本書の動作確認環境
本書の補足情報について
第1章 SQL&データベースの基礎知識
SQLって何だろう?
1.1 データベース用の言語「SQL」
まずはどんな姿か見てみよう
- データを管理する「テーブル」
- SELECT文の構造
- 複数のテーブルからデータを取得する
- 新しい列を作って表示する
- なぜ複数のテーブルに分かれているのか
1.2 DBMSの基本機能
データベース管理システムの役割って何?
- データ操作機能
- 同時実行制御
- トランザクション管理
- 機密保護
- 障害回復
1.3 RDBの特徴
リレーショナルデータベースってどんなDB?
- 常に「表」で考える
- 一意性制約と参照制約
- RDBと3層スキーマ
第2章 スタートアップSQL
実際に書いて試してみよう
2.1 標準SQLと基本的な書式
書き方のルールを確認しよう
- 標準SQLの基礎知識 標準SQLと,実装ごとに異なる対応状況や独自機能について
- SQLにおける5つの記述ルール
- ❶各単語は半角スペースまたは改行で区切り,文末には「;」を付ける
- ❷キーワードは大文字でも小文字でもよい
- ❸テーブル名や列名には(原則として)半角英数字と「_」を使う
- ❹文字列や日付は「'〜'」で囲む
- ❺コメントは「--」の後ろか,「/*」と「*/」の間に書く
2.2 テーブルの作成と削除
CREATE TABLE,DROP TABLE
- テーブルの作成 CREATE TABLE
- テーブルの削除 DROP TABLE
2.3 参照制約(外部キー)の設定
FOREIGN KEY,REFERENCES
2.4 データの追加
INSERT INTO
- 外部キーがあるテーブルにデータを登録する
2.5 データの変更
UPDATE
- 更新できないデータが含まれていた場合
2.6 データの削除
DELETE
- 参照されているデータの削除
2.7 データの問い合わせ
SELECT
- テーブルを表示する 問い合わせ❶
- データを並べ替える 問い合わせ❷
- データを集計する 問い合わせ❸
- 集計結果で並べ替える 集計補足❶
- 別の列名で表示する 集計補足❷
- テーブルを結合する 問い合わせ❹
- 結合した結果を絞り込む,並べ替える 結合補足❶
- 結合した結果を集計する 結合補足❷
2.8 ビューの作成と削除
CREATE VIEW/DROP VIEW
- ビューの作成
- ビューの削除
2.9 バッカス記法(BNF)
読めると便利! マニュアルの書式
第3章 CREATE TABLE詳細
[DB設計❶]テーブルではどんなことを定義できるのか
3.1 実表と導出表
SELECTできる2つの「表」
- テーブルとビューの定義
3.2 「列」(カラム)の設定
どんなデータを保存したいか考えよう
- データの「型」とは何か
- 列の初期値 DEFAULT句
- 登録できる値の制限 CHECK制約
- 文字列/数値/日付時刻以外のデータ型
- ドメイン(定義域)とは何か
3.3 特別な値「NULL」
わからない値だって保存したい
- NULLの禁止
3.4 キー(識別子)
PRIMARY KEY,UNIQUE,NOT NULL
- 主キーと候補キー
- 主キーとそのほかの候補キーの宣言
- 複合キーの宣言
3.5 参照制約(外部キー)
FOREIGN KEY,REFERENCES
- 参照制約(外部キー)の宣言
- キーの値を変更したらどうなるか
3.6 テーブル定義の変更
ALTER TABLE
- 列と制約の追加
- 列と制約の変更/削除
- 参照制約(外部キー制約)の追加と削除
3.7 インデックスの作成と削除
CREATE INDEX,DROP INDEX
- インデックスの作成
- インデックスの削除
- インデックスと更新速度
第4章 正規化
[DB設計❷]RDBにとっての「正しい形」とは
4.1 正規化の目的 すべては正しいデータを保つため
4.2 テーブルの構造は列×行のみ 第1正規形と繰り返し項目の排除
4.3 テーブルの列は主キーと主キーで決定する項目のみ
第2正規形,第3正規形,ボイスコッド正規形
- 無損失分解
- 部分関数従属を取り除く 第2正規形
- 推移的関数従属を取り除く 第3正規形
- ボイスコッド正規形 BCNF
- ボイスコッド正規形までのまとめ
4.4 多値従属性と結合従属性
第4正規形,第5正規形
- 多値従属性
- 結合従属性
4.5 このほかの正規形
ドメインキー正規形,第6正規形
- ドメイン制約に着目する「ドメインキー正規形」
- 識別子から決定できる値を1つまでにする「第6正規形」
第5章 ER図
[DB設計❸]「モノ」と「関係」を図にしてみよう
5.1 データモデリング技法「ERモデル」
データベース設計でどう使う?
- ERモデルと関係モデル
- データベースへの「写像」
- エンティティは「テーブル」になる
- リレーションシップは「参照制約」になる
- 参照される側は「1」にする
5.2 ER図
箱と箱を結ぶ線のルール
- さまざまな図法
- ER図を読み解く3つのポイント
- ❶エンティティと属性の表記
- ❷主キーと外部キーの表記
- ❸ カーディナリティの表記
- モデリングツール
5.3 カーディナリティの検討
参照できる形に整えよう
- 1対多の場合
- 1対多は「親子関係」か「参照関係」を表している
- 多対多の場合
- 多対1の場合
- 1対1の場合
- 1対1の意味を考える
- 1対1の外部キー
5.4 識別子(キー)の検討
本当にその識別子で大丈夫?
- 使われていない識別子は存在しないか
- 識別子に複合キーが潜んでいないか
- 存在しない識別子を使っていないか
5.5 スーパータイプとサブタイプ
区分コードを見つけたら考えよう
- is-aの関係になっているか
- orの関係になっているか
- どちらでテーブルを作るか
第6章 データ操作
データを自在にSELECTしよう
6.1 SELECTの基礎構文
必要なデータを取り出す,重複を取り除く
- 列の指定,列の連結と計算,別名 SELECT句,AS,CONCAT,||
- 重複の除去 DISTINCT
- 行の指定(絞り込み) WHERE句
- 条件を組み合わせる AND,OR,NOT
- 並び順を変える ORDER BY
- 件数の指定 LIMIT(MySQL/MariaDB/PostgreSQL),TOP(SQL Server)
- 開始位置と件数の指定 OFFSET~FETCH
6.2 関数と演算子
値の比較,計算,パターンマッチング
- 大小の比較と数値の計算 = <> > < >= <= + - * /
- NULLの判定 IS NULL,IS NOT NULL
- 範囲の指定 BETWEEN
- いずれかの値に当てはまるか IN
- あいまい検索 LIKE
- 正規表現による検索 SIMILAR TO,REGEXP,~演算子
- 文字列の演算子とおもな関数
- 日付時刻の演算子とおもな関数
- NULLの変換 NULLIF,COALESCE
6.3 NULLとUNKNOWN
わからない値をどう扱う?
- TRUE/FALSE/UNKNOWNによる論理演算
- TRUEとFALSEのみで判定する IS演算子
6.4 結合(JOIN)
複数のテーブルを組み合わせる
- クロス結合 CROSS JOIN
- 内部結合 INNER JOIN(JOIN)
- 外部結合 LEFT OUTER JOIN,RIGHT OUTER JOIN,FULL OUTER JOIN
- 自己結合 同じテーブルでの結合
- 等価結合時の列指定 USING()
- 自然結合 NATURAL JOIN
- 2つより多いテーブルの結合
- 複数の列によるJOIN
6.5 テーブルの連結(UNION)
データを「縦」につなげたい
- SELECT結果の連結 UNION,UNION ALL
- 列の数や型を調整するには
6.6 集約関数
データのグループ化と集計
- 集約関数 COUNT,AVG,SUM,MAX,MIN
- 複数の列でグループ化する GROUP BY
- 小計と合計を付ける ROLLUP
- GROUP BY使用時のSELECT句 GROUP BY
- 集約結果で絞り込む HAVING,WHERE
- 列の一部の値で集計する GROUP BY,SUBSTRING()
- 列ごとに異なる条件で集計する CASE,GROUP BY
6.7 CASE式
SELECT文で場所分け(条件分岐)
- 値を置き換える 単純CASE式
- 値ごとに式を書く 検索CASE式
- WHERE句で使用する列の優先順位を決める 検索CASE式(WHERE句)
- 任意の値で区切ってカウントする 検索CASE式(SELECT句)
6.8 サブクエリー
SELECT文とSELECT文を組み合わせる
- SELECT文の結果を使って絞り込む サブクエリー(WHERE句)
- 各行に対してSELECT文を実行する 相関サブクエリー
- 列の値をSELECT文で作る SELECT句のサブクエリー
- 複数の値で絞り込む IN,NOT IN
- 複数の値と比較する ALL,SOME,ANY
- サブクエリーとNULL
- 存在しているかどうかを調べる EXISTS,NOT EXISTS
6.9 テーブルの共通(INTERSECT)と差(EXCEPT)
SELECT文の結果を比べる
6.10 ウィンドウ関数
データを区切って集計,順位付けする
- ウィンドウ関数の基本 無名ウィンドウ,名前付きウィンドウ
- どう区切るのかを決める,連番を付ける
- PARTITION BY,ORDER BY,ROW_NUMBER()
- 全体の集計を行う PARTITION BYを使わないウィンドウ関数
- 区画別の集計とランキング RANK,DENSE_RANK,ORDER BY
- 区画内の行の位置を指定する
- LAG,LEAD,FIRST_VALUE,LAST_VALUE,NTH_VALUE
- 直前の値と同じだったらスペースにする LAG,CASE,名前付きウィンドウ
6.11 データの更新
クエリーを使って更新しよう
- INSERTとSELECTによる一括登録
- ほかのテーブルの値を使って更新対象を指定する
- ほかのテーブルの値を使って更新する
6.12 [補講]実践的な運用の話題
トランザクションの分離レベル,デッドロック,並列処理と分散処理
- トランザクションの分離レベル
- デッドロック
- 並列処理と分散処理
6.13 [補講]関係演算
集合論から見たSQL
- 和 UNION,UNION ALL
- 差 EXCEPT,EXCEPT ALL,NOT EXISTS,OUTER JOIN
- 共通 INTERSECT,INTERSECT ALL,INNER JOIN
- 直積 CROSS JOIN
- 関係演算で追加された演算
- 選択 WHERE
- 射影 SELECT
- 結合 JOIN
- 商 サブクエリー
第7章 ケーススタディー
DB設計&SELECT文の組み立て方
7.1 フクロウ塾のDB設計
ER図,フィールド&書式,DBに持たせるルール
- 氏名フィールドの検討と文字列の書式
- 氏名に常にスペースを入れたい場合のCHECK制約
- マスターを追加すべきか検討する コースの再検討❶
- 複合キーで選択可能なコースを制限する コースの再検討❷
- データベースでルールを管理するかを検討する コースの再検討❸
- データベースで管理する場合 コースの再検討❸-A
- データベースでは管理しない場合 コースの再検討❸-B
7.2 校舎の情報
集約関数,CROSS JOIN,外部結合,DISTINCTの再入門[データ抽出のバリエーション1]
- 校舎別×科目別の人数 COUNT
- 受講人数と平均点 COUNT,AVG,DISTINCT
- 受講していない生徒❶ CROSS JOIN,EXCEPT
- 受講していない生徒❷ CROSS JOIN,LEFT OUTER JOINまたはNOT EXISTS
- メンターの人数❶ JOINとDISTINCT
- メンターの人数❷ JOINとLEFT OUTER JOIN
7.3 受講生ごとの情報の抽出とデータの整形
CASE式の応用[データ抽出のバリエーション2]
- 生徒と受講科目の表示❶ CASE式
- 生徒と受講科目の表示❷ JOIN+CASE式
- SELECT文が自分の意図したとおりに組み立てられているか確認する
- 生徒×受講科目ごとの平均点 LEFT OUTER JOIN,CASE式
7.4 複雑な条件に合致する受講生の抽出
サブクエリーとウィンドウ関数の活用[データ抽出のバリエーション3]
- 最高得点者のリスト サブクエリーの場合
- 最高得点者のリスト ウィンドウ関数の場合
- 内部結合と外部結合の検討 難易度別コースの最高得点❶
- 最高得点の確認 難易度別コースの最高得点❷
- 氏名の取得 難易度別コースの最高得点❸
- 最高得点者のリスト完成 難易度別コースの最高得点❹
- 前回の記録との比較 前回よりも10点以上点数が下がった
- 同じ中学校に通っている生徒
- 複数の子が通っている中学校のリスト
- 同じ中学校で別の校舎に通っている生徒のリスト