ゼロからはじめるデータベース操作を読んだ

背景・モチベーション

いままでRDBにはあまり関わってこなかったため理解が浅い。DBとなるとKVSを触る機会の方が多かった。RDBが裏側にいたとしてもデータ部隊が別に存在しSQLを書いたりDBの設計に関わることはなかった。SQLを書くとしても、redashでデータを見る時程度で、しかもクエリはコピペで済ませてしまっていた。
さすがにやらないとまずいな、ということで昨年末にようやく読んだ。

SQL 第2版ゼロからはじめるデータベース操作

作者:ミック
発売日: 2016/07/11
メディア: Kindle版

現状の知識

以前にredashでクエリを作る時に、合計数を出すクエリが書けないことがあった。そこに別チームの同僚が通りすがり、「countも知らんの！？！？？！？！？！？？？！？！？？」と言われた。あの時の顔を思い出すと夜しか眠れない。

環境

テキトーにdockerイメージをpull。

$ docker pull postgresql
$ docker run -e POSTGRES_PASSWORD=password postgresql
$ docker exec -it $(containerのID) bash
$ root@container_id> psql -U postgres

読んだ箇所のおおまかな内容

DBって何だ

これはさすがに理解していたので流し読み。DBMSにSQLのパーサーがいて、DBに対して実行し、結果をDBMS経由で返すという流れ。これ以上の深い理解はしていないけれど。

SQLって何だ

SQLに歴史ありという感じだった。SQLはISOで規格を定められた標準SQLというものがあり、普段目にしているSQLはこれだという。また、クエリの種類にもDDL（データ定義言語）・DML（データ操作言語）・DCL（データ制御言語）と分けることができる。それぞれ CREATE 、 SELECT 、 COMMIT などが該当する。詳しく調べるほどの興味が出なかったけど、トリビアとして面白かった。ここに興味が持てると、クエリを高度に抽象化した次元で理解できそう。たぶん。

本書ではクエリはキーワードを大文字、テーブル名を頭文字だけ大文字、あとは小文字としているが、スタイルは議論の余地があるらしい。 techlife.cookpad.com

基本的なCRUD

基本的なので見覚えはあるけど構文を雰囲気で記憶していたので改めて確認すると真新しい感覚になった。

DBの作成

create <DB名>;

テーブルの作成

create table <テーブル名>
(
    <カラム名> <データ型> <制約>,
    <カラム名> <データ型> <制約>,
        .
        .
        .
    <テーブルの制約1>, <テーブルの制約2>, ..., <テーブルの制約N>
);

データ型と制約についてはうっすら理解していたけれど、それだとまた勉強することになりそうだったので公式のリファレンスを眺めた。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11 データ型
 MySQL :: MySQL 5.6 リファレンスマニュアル :: 1.7.3 MySQL における制約の処理

定義の変更

alter table <テーブル名> add column <列名> <データ型> <制約>;
alter table <テーブル名> drop column <列名>;

変更する際は新しいカラムをaddしてから古いカラムをdropする。

データの選択

select <列名>, ..., <列名N> from <テーブル名>;
select distinct <列名>, ..., <列名N> from <テーブル名>;

distinctは複数列にも書けるが、その際は積がselectされる。

NULLの操作

NULLを含む演算をするとNULLになる。特別にIS NULLとIS NOT NULLが用意されている。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.3.4.6 NULL 値の操作
 MySQL :: MySQL 5.6 リファレンスマニュアル :: B.5.5.3 NULL 値に関する問題
 MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.8 IS NULL の最適化

NOT NULLのカラムに対してIS NULLを行うと最適化されるとリファレンスにあった。そもそもNOT NULLのカラムに対してIS NULLつけないだろと思ったけど、ORMとかで人間が組み立てない場合はロジック次第でそういうクエリは生まれそうだなと思った。

集約関数

count・sum・avg・max・minなど。集約関数はNULLを除外して行われるというのが意外。先ほどまでNULL値を含めた演算はNULLになるという理屈はどこへいったのやら。
特に気をつけたいのはavgだ。NULLが除外されるため、分母がNULLを除いた数になる。

集約関数ではないが、他にはgroup byやhaving、order byなど。これらの動作は理解していたけれど、いざ実際に自分で0から書いてみると割と難しい。集約関数と組み合わせて色んなことができる。

更新

insert・delete・drop・updateなど。これは大体雰囲気でわかっていたけれど、テーブルのコピーについて初見の知識があった。特定のテーブルを別のテーブルにコピーする際は下記のように書くことができる。

insert into dst (a, b, c) select a, b, c from src;

複雑な問い合わせ

VIEW

VIEWという存在を初めて知った。これは集合を返却する関数のようなもので、from句に続いてテーブルのように使うことができる。
ただし、あくまで関数のようなものなのでVIEWにデータは存在しない。VIEWが評価されるとVIEWの定義であるクエリが実行される。

スカラ・サブクエリ

サブクエリは知っていたが、スカラ・サブクエリという単語は初見だった。これはサブクエリの結果としてスカラ値を返却するものだ。where句では集約関数を使うことができないが、スカラ・サブクエリを利用することで使うことができる。

相関サブクエリ

理解できた気がするけど0から自分で組み立てる自信はないのできっと理解できていない。相関サブクエリはクエリとサブクエリの関係性を紐付けて実行するものだと理解している。

select shohin_bunrui, shohin_mei, hanbai_tanka
from shohin as s1
where hanbai_tanka > (
    select avg(hanbai_tanka)
    from shohin as s2
    where s1.shohin_bunrui = s2.shohin_bunrui
    group by shohin_bunrui
);

これにより各商品分類ごとに分類の平均を超えた商品を選び出すことができる。

集合演算

union・intersect・exceptがそれぞれ和・積・差に対応する。これらは同じカラムについてレコードが増減する操作だ。

一方、カラムが増減する結合方法がjoinだ。結合方法によってレコード数も変わることはあるけれど、テーブルを横にくっつけるイメージがある。
inner joinはあるテーブルとあるテーブルに共通するカラムを用いて、それをキーとしたレコードを作成する。外部キーを使った結合とかでイメージがしやすかった。outer joinはキーとしているカラムがテーブルで共通していなくても無理やりくっつけてしまう結合方法。3つ以上のテーブルの結合もfrom句に同じ記述を繰り返すことで実現可能である。
最後にcross joinがある。これはテーブル同士の直積をとる。なのでレコード数が膨大になる。実務でどう使うと嬉しいケースがあるのかさっぱりわからない。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9.2 JOIN 構文