ゼロからはじめるデータベース操作

サンプルコードをダウンロードした

SQL

M1 Macに PostgreSQLをinstallする

ログインする

$ psql -h localhost -p 5432 -U （ユーザーネーム） -d postgres

tabキーで補完される

CREATE TABLEでテーブルを作成する

DROP TABLEでテーブルを削除する

ALTER TABLEでテーブルを変更する、カラムを加える

INSERTでデータをテーブルに登録する

SELECT文の基本

大体知ってるのでskip

WHERE

DESTINCT

AND, OR

集約と並び替え

COUNT

GROUP BY

GROUP BYを丁寧に考えてみる

HAVING

WHEREでも書ける場合はWHEREが良い

whereはソートの前に行数を絞り込むの

ソート対象の行数を減らせる

havingはソートが終わってグループ化された後に実行される

where句で条件指定するよりソート行数が多くなる

データの更新

INSERTでデータを登録する

DROP TABLEでテーブルを削除する

DELETEでテーブルの中身を削除する

探索型DELETE

TRUNCATEの方が高速

OracleではROLLBACKできないので注意

UPDATEでデータを更新する

探索型UPDATE

code:sql

UPDATE <テーブル名>

SET <列名> = <式>

WHERE <条件>;

TRANSACTION

複雑な問い合わせ

VIEWで仮想的なテーブルをを作成する

VIEWはSELECT文を保存する

データを保存しないため、記憶装置の容量を節約できる

いちいちSELECT文を書く必要がない

VIEWの作成はCREATE VIEW

code:sql

CREATE VIEW <ビュー名> (<ビューの列名1>, <ビューの列名2>, .....>

<SELECT文>

多段VIEWは可能だが、パフォーマンス低下を招くので避ける

ORDER BYは使用できない

PostgreSQLでは可能だが避ける

VIEWの更新

集約なしのVIEWであれば更新可能

INSERTやUPDATEが使える

VIEWの削除はDROP VIEW

サブクエリで使い捨てのVIEWを作る

VIEW定義のSELECT文をそのままFROM句の中に入れただけ

内側から最初に実行される

スカラ・サブクエリ

scaler subquery

スカラは「単一の」を意味する

スカラ・サブクエリは「必ず一行一列だけの戻り値を返す」という制限をつけたサブクエリのこと

比較演算子の入力に利用できるようになる

WHERE, GROUP BY, HAVING, ORDER BYで可能

相関サブクエリ

要は集合

結合条件は必ずサブクエリの中に書くこと

テーブルの別名（AS）のスコープを考えるとわかる

関数、述語、CASE式

関数の種類

算術関数

SQLの文字列関数

SQLの日付関数

変換関数

集約関数

CASE式

単純CASE式と検索CASE式がある

検索CASE式は単純CASE式の機能をすべて含む

code:検索CASE式

CASE WHEN <評価式> THEN <式>

WHEN <評価式> THEN <式>

...

ELSE <式>

END

評価式は戻り値が真理値

ELSEは省略できるが、わかりやすさのために明示するのがbetter

ENDは省略不可で忘れがちなので注意

集合演算

テーブルの足し算と引き算（縦結合）

集合関数の注意事項

演算対象となるレコードの列数は同じである

足し算の対象となるレコードの列のデータ型が一致している

SELECT文はどんなものを指定しても良い．ただしORDER BY句は最後に1つだけ

UNIONでテーブルの足し算

UNIONは和集合

重複業は排除される

ALLオプションで重複行を残せる

INTERSECTでテーブルの共通部分を選択

ALLオプションで重複行を残せる

横結合

内部結合 INNER JOIN

テーブルの内部だけから情報を持ってくる

通常NULLはない

外部結合 OUTER JOIN

テーブルの外部からだけから情報を持ってくる

通常NULLの列が生まれる

クロス結合 CROSS JOIN

ほとんど使わない

テーブルの直積

13列のテーブルと5列のテーブルのCROSS JOINの結果は85

（割り算はHAVING句を使う）

高度な処理

（あくまでSQLで行うには高度な処理）

ウィンドウ関数

GROUPING演算子

psycopg2でpostgreSQLに繋ぐ

1章

鉄則

RDBでは行単位でデータを読み書きする

1つのセルの中には1つのデータしか入れられない

標準SQLを覚えれば様々なRDBMSでSQLが書ける

SQLは機能により3種類に分けられる。もっとも使われるのはDML

SQLはセミコロンで終わる

キーワードの大文字・小文字は区別されない

文字列と日付の定数はシングルクォーテーションで囲む

`2010-01-26'のように

単語の間を半角スペース、または改行で区切る

データベースやテーブル、列などの名前に使って良い文字は3種類

半角のアルファベット

半角の数字

アンダーバー

名前の最初の文字は「半角のアルファベット」

名前を重複してはならない

削除したテーブルは復活できない！DROP TABLEは実行前に要確認

テーブル定義を変更したら元に戻せない！ALTER TABLEは実行前に要確認

2章

鉄則

アスタリスク（*）は全ての列を意味する

日本語の別名をつけるときは、ダブルクォーテーション（"）で囲む

結果から重複行を省く場合には、SELECT句にDISTINCTをつける

WHERE句はFROM句の直後に置く

コメントはSQL文に説明や注意事項などを付記するもの

1行コメントと複数行コメントの2種類がある

SELECT句には定数も式も書ける

比較演算子では、必ず不等号が左、イコールが右

文字列型の順序の原則は辞書式。数値の大小順序と混同してはいけない

NULLである行を選択したい時には、条件式にIS NULL演算子を使う。NULLではい行を選択したい時には、条件式にiS NOT NULL演算子を使う。

条件を否定するのはNOT演算子。しかし無理に否定する必要はない

複数の検索条件を組み合わせるときは、AND演算子とOR演算子を使う

ベン図は便利

ANDをORより強い。ORを優先するときはカッコで囲む

複雑な条件も真理表を書けば理解しやすい

3章

鉄則

COUNT関数は引数によって動作が異なる。COUNT(*)はNULLを含む行数を、COUNT(＜列名＞)はNULLを除外した行数を数える。

集約関数はNULLを除外する。ただし「COUNT(*)」は例外的にNULLを除外しない。

MAX/MIN関数はほとんど全てのデータ型に適用できる。SUM/AVG関数は数値のみしか使えない。

値の種類を数えたいときは、COUNT関数の引数にDISTINCTをつける。

重複値を除外して集約するには、集約関数の引数にDISTINCTをつける。

GROUP BY句はテーブルをカットするナイフである。

SQLにおいて句の記述順は不変。入れ替えは不可！

SELECT→FROM→WHERE→GROUP BY

集約キーにNULLが含まれる場合、結果にも「不明」行（空行）として現れる

GROUP BY句を使うときは、SELECT句に集約キー以外の列名を書けない。

GROUP BY句にSELECT句でつけた別名は使えない。

GROUP BY句を使っても結果の表示順序はソートされない。

集約関数を書ける場所はSELECT句とHAVING句（とORDER BY句だけ）

HAVING句はGROUP BY句の後ろに置く

集約キーに対する上演は、HAVING句ではなくWHERE句に置く

ORDER BY句は常にSELECTの最後尾に書く

ORDER BY句で並び順を指定しないと暗黙に昇順扱いになる