📝

【学習記録】ゼロからはじめるデータベース操作読んでみた

2024/08/27に公開

SQL

Database

idea

前提

データベースの操作を実際に行ったのは新人研修の時。
その後はAI開発の案件・システム構想の工程のみ経験していたため、DBを使っていなかった。
2年目になり大規模PJの受入試験工程を担当することになり、その際にDBを触る機会があったためSQLを基礎から学びなおそうと学習に着手いたしました。
著書「ゼロからはじめるデータベース操作」の内容をアウトプットしていきたいと思います。
個人的に抑えておきたいポイントに絞って記載していきます。

第0章イントロ

環境構築のため省略

第1章 DBとSQL　テーブルの作成

DBMSの種類
- Oracle Database：Oracle社のRDBMS
- SQL Server：Microsoft社のRDBMS
- DB2：IBM社のRDBMS
- PostgreSQL：オープンソースのRDBMS
- MySQL：オープンソースのRDBMS(2010年からOracle社が開発元）

SQLの文とその種類
- DDL (Data Definition Language)
  - CREATE,DROP,ALTER
- DML (Data Manipulation Language)
  - SELECT,INSERT,UPDATE,DELETE
- DCL (Data Control Language)
  - COMMIT,ROLLBACK,GRANT,REVOKE

これらのうち、実際に使われるSQLの90%はDMLからなる。
以下、実際のSQL文(主にDDL)についてまとめる。

データベースの作成

CREATE DATABASE <データベース名>;

テーブルの作成

CREATE TABLE <テーブル名>
(<列名1> <データ型> <この列の制約>,
<列名2> <データ型> <この列の制約>,
<列名3> <データ型> <この列の制約>,
.
.
.
<このテーブルの制約1>, <このテーブルの制約2>, ...
);

※列の制約にDEFAULT制約を付けることが出来る。

テーブルの削除

DROP TABLE <テーブル名>;

テーブル定義の変更

追加する場合

ALTER TABLE <テーブル名> ADD COLUMN <列の定義>;

削除する場合

ALTER TABLE <テーブル名> DROP COLUMN <列名>;

第2章検索の基本

where句による条件付きの検索

SELECT <列名>, ...
FROM <テーブル名>
WHERE <条件式>;

NULLを含む演算の場合は全ての結果がNULLになる
比較演算子
- = 　等しい
- <> 等しくない
- <,>,<=,>= それぞれ各比較
NULLを用いた比較をする際は、以下のように記載
- ×　= NULL
- 〇 IS NULL
NOTやANDやORなどの論理演算子についてもNULLを特別視する必要がある。
- NULLを論理演算子で条件式に照らしたときの真理値は、真でも偽でもなく、「不明(UNKNOWN)」という第三の値になる。
- 通常の論理演算が2値論理と呼ばれるのに対して、SQLだけは3値論理と呼ばれる。

第3章集約と並び替え

集約関数

ひとまず次の5つを抑えておく

COUNT
SUM
AVG
MAX
MIN

NULLを0とみなして計算する方が望ましいケースは第6章で取り上げる。

テーブルをグループ分け

SELECT <列名1>, <列名2>,...
FROM <テーブル名>
WHERE <条件式>
GROUP BY <列名1>, <列名2>....

GROUP BY句は必ずWHERE句よりも後ろに記載する。

集約した結果に条件を指定する

COUNT関数などを使ってデータ集約した場合、その結果に対する条件指定はWHERE句ではなくHAVING句を使って行う。
HAVING句はGRUOP BY句の後ろに書く。

SELECT <列名1>, <列名2>,...
FROM <テーブル名>
GROUP BY <列名1>, <列名2>,..
HAVING <グループの値に対する条件>

行に対する条件指定⇒WHERE句
グループに対する条件指定⇒HAVING句

検索結果の並び替え

ORDER BY句によって並び替えが可能。

ソートキーにNULLが含まれていた場合、先頭か末尾にまとめられる。
ORDER BY句では、SELECT句に含まれていない列や集約関数も使える。

第4章データの更新

原則として、INSERT文は1回の実行で1行を挿入とする。（しかし、多くのRDBMSでは1回で複数行をINSERTすることも可能）

INSERT INTO <テーブル名> (列1, 列2,..) VALUES (値1, 値2,..);

※テーブルの全列に対してINSERTを行う場合、列リストは省略することが出来る。

他のテーブルからデータをコピーする

INSERT INTO <コピー先テーブル> (列1, 列2,..)
SELECT <列1>, <列2>..
FROM <コピー元テーブル>

データの削除

データの中身を空っぽにする

DELETE FROM <テーブル名>;

削除対象を制限したDELETE文

DELETE FROM <テーブル名>
WHERE <条件>;

SELECT文と違い、DELETE文で使えるのはWHERE句のみ。

データの更新

データの更新はUPDATEを用いる。

UPDATE <テーブル名>
SET <列名> = <式>
WHERE <条件>;

SET句はカンマ区切りで並べたり、()でまとめるなどの方法で、1つのUPDATE文にまとめることが出来る。

トランザクション

トランザクションを作る時は、以下の書き方

START TRANSACTION;
...
COMMIT;

第5章複雑な問い合わせ

ビュー

テーブルは、データを記憶装置に保存することによってデータを保管している。しかし、ビューの場合はデータを記憶装置に保存していない。ではどこにデータを保存しているのかというと、どこにも保存しておらず、ビューが保存しているのは「SELECT文」そのもの。
よく使うSELECT文はビューにして使いまわすのが良い。

CREATE VIEW ビュー名 (<ビューの列名1>, <ビューの列名2>, ...)
AS
<SELECT文>

ビューの制限事項

ORDER BY句は使えない
ビューとテーブルの更新は連動して行われる。したがって集約されたビューは更新不可能。

ビューの削除

DROP VIEW <ビュー名>

サブクエリ

サブクエリとは、使い捨てのビュー（SELECT文）のようなもの。
サブクエリには名前を付ける必要がある。

SELECT shohin_bunrui, cnt_shohin
FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin
        FROM Shohin
        GROUP BY shohin_bunrui) AS ShohinSum;

上記のFROM句の中身がサブクエリ。
※OracleではFROM句にASを入れることが出来ないので、ASを無くした形で記載する。

スカラ・サブクエリ

スカラとは「単一の」という意味の言葉。必ず一行一列だけの戻り値を返すという制限をつけたサブクエリのことを、スカラ・サブクエリという。
例えば、以下のSQLはWHERE句の中に集約関数が含まれているため、エラーとなってしまう。そこで、スカラ・サブクエリを用いることで、比較演算子の入力として値を出力することが出来るようになる。

SELECT shohin_id, shohin_mei, hanbai_tanka
    FROM Shohin
    WHERE hanbai_tanka > AVG(hanbai_tanka);

↓これを正しい形にすると、

SELECT shohin_id, shohin_mei, hanbai_tanka
    FROM Shohin
    WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin);

スカラ・サブクエリは、定数や列名を書くことができる全ての場所で記載ができる。
絶対にサブクエリが複数行を返さないようにすることだけ、気を付ける必要がある。

相関サブクエリ

相関サブクエリは、小分けにしたグループ内での比較をする際に用いる。
例えば、商品分類ごとに、平均販売単価よりも高い商品のみを抽出するSQLを書きたい時、以下のように書く。

SELECT shohin_id, shohin_mei, hanbai_tanka
FROM Shohin AS S1
WHERE hanbai_tanka > (SELECT ANG(hanbai_tanka)
                        FROM Shohin AS S2
                        **WHERE S1.shohin_bunrui = S2.shohin_bunrui**
                        <!-- 下記はなくてもOK  -->
                        GROUP BY shohin_bunrui);

結合条件は必ずサブクエリの中に書くことに注意する。

第6章関数、述語、CASE式

関数の種類

算術関数（数値の計算）
文字列関数（文字列を操作）
日付関数（日付を操作）
変換関数（データ型や値を変換）
集約関数（データの集計を行う）

算術関数

加減乗除
絶対値
- ABS(数値)
MOD
- MOD(被除数, 除数)
四捨五入
- ROUND(対象数, 丸めの桁数)

文字列関数

連結
- 文字列1 || 文字列2
文字列長
- LENGTH(文字列)
小文字化
- LOWER(文字列)
文字列の置換
- REPLACE(対象文字列, 置換前の文字列, 置換後の文字列)
文字列の切り出し
- SUBSTRING(対象文字列 FROM 切り出し開始位置 FOR 切り出す文字数)
大文字化
- UPPER(文字列)

日付関数

日付関数はDBMSによって扱いが異なることが多い。標準SQLで定められており、ほとんどのDBMSで使えるものを以下に挙げる。

現在の日付
- CURRENT_DATE
現在の時間
- CURRENT_TIME
現在の日次
- CURRENT_TIMESTAMP
日付要素の切り出し
- EXTRACT(日付要素 FROM 日付)
- 日付要素：YEAR,MONTH,DAY,HOUR,MINUTE,SECOND

変換関数

型変換
- CAST(変換前の値 AS 変換するデータ型)
NULLを値へ変換
- COALESCE(データ1, データ2, ...)
- 可変個の引数を取り、左から順にみていき、最初にNULLでない値を返す。

述語

述語とは、戻り値が真理値になる関数のこと。具体的な述語を以下にあげる。

文字列の部分一致検索
- LIKE '%ddd'　など　%は任意の0文字を意味する
- %の代わりに _ を使うと、「任意の一文字」を意味する
範囲検索
- BETWEENを用いる。
- 両端の値も含む。
- SELECT * FROM Shohin WHERE hanbai_tanka BETWEEN 100 AND 1000;
NULLか非NULLかの判定
- IS NULL, IS NOT NULL
OR の便利な省略
- IN述語
- IN述語の引数にサブクエリを指定することが出来る。
- NOT IN の引数にNULLを含んではいけない！
EXISTS述語
- EXISTSは他の述語とは使い方が異なる
- 構文を直感的に理解することが難しい
- EXISTSを用いなくても、INでほぼ代用が可能
- ⇒以上のことから、ここでは言及しない。

CASE式

CASE式には単純CASE式と検索CASE式の2種類があるが、検索CASE式は単純CASE式の機能を全て含むため、ここでは検索CASE式についてのみ記載する

CASE WHEN <評価式> THEN <式>
    WHEN <評価式> THEN <式>
    ...
    ELSE <式>
END

CASE式のELSEは省略可能だが、コードの可動性のためにも省略はしないこと
CASE式のENDは省略不可
CASEは、式がかけるところならどこでも記載が可能

第7章集合演算

集合演算とは、レコードの集合を意味する。

テーブルの足し算

UNIONによるテーブルの足し算

SELECT shohin_id, shohin_mei
FROM Shohin
UNION
SELECT shohin_id, shohin_mei
FROM SHohin2

※UNIONに限らず、集合演算子は、通常は重複行は排除される。
⇒重複を許したい時は、UNION ALLを使う。

2つのレコードの共通部分を選択する時
- INTERSECT

テーブルの引き算

EXCEPTを用いると、テーブルの引き算を実行できる。

結合

UNION,INTERSECT,EXCEPTはテーブルの行方向に作用していたが、列方向に繋げたい時はJOINを用いる。

内部結合

例えば、2つのテーブルを内部結合する時は、以下のようにSQLを実行する。

SELECT TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei, S.hanbai_tanka
FROM TenpoShohin AS TS INNER JOIN Shohin AS S
ON TS.shohin_id = S.shohin_id;

内部結合のポイント
- FROM句に2つのテーブルを書く
- ON句には結合条件を記載する。内部結合を行う場合は記述が必須。書く場所は、FROMとWHERE句の間。
- SELECT句では、どのテーブルのカラム名を指定しているのか分かるように、テーブル名.列名と記載する。（カラム名が重複しているとエラーになる。）

外部結合

内部結合にて記載したSQLを、外部結合仕様にして記述すると、以下になる。

SELECT TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei, S.hanbai_tanka
FROM TenpoShohin AS TS RIGHT OUTER JOIN Shohin AS S
ON TS.shohin_id = S.shohin_id;

外部結合のポイント
- 片方のテーブルの情報が全て出力される。内部結合では、2つのテーブルの両方に存在している情報だけを選択するが、外部結合でどちらか一方は必ず全て出力する。
- どちらのテーブルをマスタとみなすかを指定するのが、「RIGHT」「LEFT」だ。

3つ以上のテーブルを使った場合

INNER JOINを繋げていくことで3つ以上のテーブルを結合することも可能。

クロス結合 - CROSS JOIN

クロス結合では、内部結合や外部結合で用いた ON句は指定することが出来ない。なぜなら、2つのテーブルのレコードについて、全ての組み合わせを作る結合方法であるから。

第8章高度な処理

ウィンドウ関数

ウィンドウ関数は、別名OLAP(オーラップ)関数と呼ばれる。
OLAPとは、OnLine Analytical Processingの略で、リアルタイムデータ分析処理のこと。
以下がウィンドウ関数の構文

<ウィンドウ関数> OVER (PARTITION BY <列リスト>
                        ORDER BY <ソート用列リスト>)

ウィンドウ関数として使える関数
- 集約関数(SUM AVG COUNT MAX MIN)をウィンドウ関数として使う
- RANK,DENSE_RANK,ROW_NUMBER　などのウィンドウ専用関数
  - RANK：ランキングを算出し、同順位が複数レコード存在した場合は、後続の順位が飛ぶ。
  - DENSE_RANK：ランキングを出力し、同順位が複数レコード存在しても後続順位が飛ばない。
  - ROW_NUMBER：一意な連番を付与する。
ウィンドウ関数は原則SELECT句のみで使える。（ランキングなどは基本的に結果出力の最終段階で出されるため）

構文の基本的な使い方　-RANK関数

商品分類別に、販売単価の安い順で並べたランキング表を作る

SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
RANK () OVER (PARTITION BY shohin_bunrui
                ORDER BY hanbai_tanka) AS ranking
FROM Shohin;

PRTITION BYは、順位を付ける対象の範囲を指定している。（これはなくてもOK）
ORDER BYは、その列をどんな順序で順位を付けるかを指定している。

集約関数をウィンドウ関数として使う

SELECT shohin_id, shohin_mei, hanbai_tanka,
SUM () OVER (ORDER BY shohin_id) AS current_sum
FROM Shohin;

上記は所謂「累計」を表している。

移動平均を算出する

「フレーム」と呼ばれる集計範囲に対して、範囲指定のキーワードを仕様することで、移動平均を求めることが可能になる。

SELECT shohin_id, shohin_mei, hanbai_tanka,
AVG (hanbai_tanka) OVER (ORDER BY shohin_id ROWS 2 PRECEDING) AS moving_avg
FROM Shohin;

上記のSQLの場合、自分、自分より1行前、自分より2行前の3行分のレコードの平均を取る。

また、PRECEDINGの代わりにFOLLOWINGというキーワードを使うと、～行後までというフレーム指定も可能。
カレントレコードの前後の行を集計対象に含める場合は、以下のように表現する
- ORDER BY shohin_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
OVER句内のORDER BYは、あくまでウィンドウ関数がどのような順序で計算するかを決めるための役割しか持っていないので、出力するレコードの順序を指定したい場合はSQL文末にORDER BYを指定する必要がある。

GROUPING演算子

shohin_bunrui	sum
合計	16780
衣服	5000
事務用品	600
キッチン用品	11180
上記の表をSELECTで表現したい時は、以下のようなSQLを書くことが一般的。

SELECT '合計' AS shohin_bunrui, SUM(hanbai_tanka)
FROM Shohin
UNION ALL
SELECT shohin_bunrui, SUM(hanbai_tanka)
FROM Shohin
GROUP BY shohin_bunrui;

しかし、上記方法だと処理コストも高く、見た目も冗長。そこで導入されたのがGROUPING演算子。
GROUPING演算子は3種類ある。（ROLLUP, CUBE, GROUPING SETS)

ROLLUP

ROLLUP(shohin_bunrui)　⇒

GROUP BY ()
GROUP BY (shohin_bunrui)
ということ。
1.では、集約キーがなく、合計行を表す。（この合計行のことを超集合行という）

ROLLUP(shohin_bunrui, torokubi)　⇒

GROUP BY ()
GROUP BY (shohin_bunrui)
GROUP BY (shohin_bunrui, torokubi)
となり、1.と2.が超集合行となる。

超集合行で小計などを求めると、NULLが入ってしまう。これが値としてのNULLなのか、超集合行のNULLなのかを区別するのがGROUPING関数。
- GROUPING(引数)の場合、引数にとった列の値が超集合行のために生じたNULLの場合は1を、それ以外の値なら0を返す。

CUBE - データで積み木を作る

構文自体はROLLUPと同じ。
CUBE(shohin_bunrui, torokubi) ⇒

GROUP BY ()
GROUP BY (shohin_bunrui)
GROUP BY (torokubi) ★追加
GROUP BY (shohin_bunrui, torokubi)

つまり、CUBEは集約キーで切り分けたブロックを積み上げて立方体を作るイメージ。

GROUPING SET

ROLLUPやCUBEによって得た結果の中から条件を個別指定して抜き出すことが出来る。
変則的な結果が得られるため、使用頻度は低い。

第9章アプリからDB接続

省略