「ゼロから始めるデータベース操作」を読んだ。まとめ。

SQLの基本が丁寧に解説されている。1〜2日で読めてわかりやすかった。

Amazon LinuxにpostgreSQLの学習環境を構築

書籍ではwindowsを用いていたが、簡単にお掃除ができるようにEC2上にpostgre学習環境を立てた。

$ sudo yum install -y postgresql

$ sudo yum install -y postgresql-server

$ sudo service postgresql initdb
Initializing database:                                     [  OK  ]
$ sudo service postgresql start
Starting postgresql service:                               [  OK  ]
$ sudo passwd postgres
ユーザー postgres のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。
$ sudo -u postgres psql
could not change directory to "/home/ec2-user"
psql (9.2.24)
Type "help" for help.

postgres=#

SQL

RDBMS操作用の言語。
大文字小文字は区別なし。
命令は;で終わる。改行では続行。
データ定義言語(DDL: Data Definition Langage)
CREATE
DROP
ALTER
データ操作言語(DML: Data Manipulation Langage)
SELECT
INSERT
UPDATE
DELETE
データ制御言語(DCL: Data Control Langage)
BEGIN (トランザクション制御言語 TCLと扱われる場合もある)
COMMIT (トランザクション制御言語 TCLと扱われる場合もある)
ROLLBACK (トランザクション制御言語 TCLと扱われる場合もある)
GRANT
REVOKE

データ定義言語(DDL: Data Definition Langage)

DB作成・削除(CREATE、DROP句)

postgres=# CREATE DATABASE <DB名>;
postgres=# DROP DATABASE <DB名>;

テーブル作成・削除・変更(CREATE、DROP、ALTER句)

-- 作成
postgres=# CREATE TABLE <テーブル名>
postgres-# ( <列名> <データ型> <制約>,
postgres(#  <列名> <データ型> <制約>,
postgres-#     :   
postgres(# <テーブルの制約>, <テーブルの制約>, ・・・);

-- 削除
postgres=# DROP TABLE <テーブル名>;

-- 変更
---- 列の追加
postgres=# ALTER TABLE <テーブル名> ADD COLUMN <列名1> <データ型> <制約>;
---- 列の削除
postgres=# ALTER TABLE <テーブル名> DROP COLUMN <列名>;

データ型

型名	意味
INTEGER	整数。
CHAR	固定長文字列。CHAR(文字数)で指定。
VARCHAR	可変長文字列。CHARと同じく最大長を()で指定。
DATE	日付型。

※ CHARは指定文字数に満たない場合は後ろが半角スペースで埋められる。(VARCHARとの違い)

制約

制約	意味
NOT NULL	NULL不可
DEFAULT <値>	デフォルト値設定

テーブルの制約

プライマリキーの設定(主キー制約)。

postgres-# PRIMARY KEY (<列名>);

プライマリキーは、テーブル内でレコードを一意に特定することができる値。すなわち重複は認めない(主キー制約)。なお、1つの値からはレコードを特定できない場合、複数のキーを並べて指定することができる。

テーブル作成例

postgres=# CREATE TABLE test_table
(id char(4) not null,
name varchar not null,
age integer,
department char(2),
primary key (id));

/*
id        | name     | age     | department    
----------+----------+---------+---------
001       | 佐藤  　  | 39      | A      
002       | 鈴木  　  | 41      | A      
003       | 高橋  　  | 41      | C      
004       | 田中  　  | 28      | B      
005       | 伊藤  　  | 34      | B      
006       | 社長  　  | 58      | NULL   
*/

データ操作言語(DML: Data Manipulation Langage)

データの登録(INSERT句)

postgres=# INSERT INTO <テーブル名> (<列名1>, <列名2>, ・・・) VALUES (<列1の値>, <列2の値>,・・・);

-- 列名の列挙は省略可。
postgres=# INSERT INTO <テーブル名> VALUES (<列1の値>, <列2の値>,・・・);

-- 他のテーブルからコピーする場合
postgres=# INSERT INTO <コピー先テーブル名> (<列名1>, <列名2>, ・・・) 
postgres-# SELECT (<列名1>, <列名2>, ・・・)
postgres-# FROM <コピー元テーブル名>;

データの登録時にDEFAULTを指定するとテーブル設定時に指定したデフォルト値が挿入される。(なお、これは省略することも可能だが、可読性の面から非推奨。)

データのクエリ(SELECT、WHERE、FROM句)

postgres=# SELECT <列名> AS <エイリアス>, <列名>　AS <エイリアス>, <列名>, <列名>,・・・
postgres-# FROM <テーブル名>;
postgres-# WHERE <条件>

テーブルの全ての列を取得するには*を用いる。
オプションだがAS <エイリアス>とすると抽出してきた列名にエイリアス(別名)をつけることができる。列ごとにつけられる。
その列を定数で埋める場合には以下のように書く

定数で埋める

postgres=# SELECT '社員' AS const, id, name
postgres-# FROM  ;
/*
const     | id        | name   
----------+-----------+----------------
社員     　| 001       | 佐藤
社員     　| 002       | 鈴木
社員     　| 003       | 高橋
社員     　| 004       | 田中
社員     　| 005       | 伊藤
社員     　| 006       | 社長
*/

WHERE句による条件付きクエリ

クエリではWHERE句を用いて条件文を指定することができる。用いる演算子は以下。

算術演算子

算術演算子	演算
+	加算
-	減算
*	乗算
/	除算

NULLを含む演算は全てNULL（ゼロ除算もエラーにならずNULLが返る）。

比較演算子

比較演算子	演算
=	等しい
<>	等しくない
>=	以上
<=	以下
>	より大きい
<	より小さい
IS NULL	NULLである
IS NOT NULL	NULLでない

NULLには通常の比較演算子は不可。IS NULLまたはIS NOT NULLを用いる。

論理演算子

論理演算子	演算
AND	論理和
OR	論理積
NOT	否定

複数の演算子を並べてWHERE句に書く場合、AND演算子が優先して評価される。
例条件A AND 条件B OR 条件Cは、条件Aかつ条件Bまたは条件Cと見なされる。
NULLの真理値は真でも偽でもなく不明(UNKNOWN)。
SQLでは一般的なプログラミング言語と異なり、論理演算が3値論理で行われる。

参考

P	Q	P AND Q	P OR Q
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	UNKNOWN	UNKNOWN	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	UNKNOWN	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN
UNKNOWN	FALSE	FALSE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

クエリ結果から重複行を省く DISTINCTキーワード

postgres=# SELECT DISTINCT <列名>
postgres-# FROM <テーブル名>;

DISTINCTキーワードは最初の列名の前にしか書くことができない。
SELECT DISTINCT <列名>, <列名>とすると複数の列名に対してDISTINCTキーワードを付けるのと同じ。

DISTINCTキーワード

postgres=# SELECT DISTINCT age FROM test_table;
/*
 age 
-----
  28
  39
  34
  41
(4 rows)
*/

postgres=# SELECT DISTINCT age, department FROM test_table;
/*
 age | department 
-----+------------
  28 | B 
  41 | A 
  41 | C 
  34 | B 
  39 | A 
(5 rows)
*/

データの削除(DELETE句)

DELETE句はテーブルのレコードを全て削除するため、特定のレコードのみを削除したい場合にはWHERE句で条件を指定する必要がある。

postgres=# DELETE FROM <テーブル名>
postgres-# WHERE <条件>; --探索型DELETE(オプション)

データの更新(UPDATE句)

postgres=# UPDATE <テーブル名>
postgres-# SET <列名1> = <式>,
postgres-#     <列名2> = <式>,
postgres-#     :
postgres-# WHERE <条件>; --探索型UPDATE(オプション)

SELECT文自体を保存するビュー(view)

何度も繰り返し実行するSELECT文はビューという形で保存することができる。また、ビューでは列名を付けることができる。
集約したテーブルなどはオリジナルと重複して作成するとストレージを食うため、ビュー形式でSELECT文のみ保存する手法が有力。

--ビューの作成
postgres=# CREATE VIEW <ビュー名> (<ビューの列名1>, <ビューの列名2>)
postgres-# AS
postgres-# <SELECT文>;
--ビューの表示
postgres-# SELECT <列名>
postgres-# FROM <ビュー名>;
--ビューの削除
postgres-# DROP VIEW <ビュー名>;

ビューからビューを作成する多段ビューを生成することも可能あるが、パフォーマンスの低下を招く。
集約したビューへの更新は不可だが、RAWデータのビューであれば更新可能。

データの集約(集約関数)

集約関数	意味
COUNT	レコード数をカウント(NULLは除外)
SUM	列の合計値(NULLは除外)
AVG	列の平均値(NULLは除外)
MAX	列の最大値(NULLは除外)
MIN	列の最小値(NULLは除外)

集約関数は、以下の3箇所でしか記述することができない。

SELECT句
HAVING句
ORDER BY句

そのため、WHERE句では集約関数を用いた絞り込みができない。（例：平均値以上のみ表示）
→ スカラ・サブクエリの利用へ。

集約関数をWHERE句で使うとエラー

postgres=# SELECT *
postgres-# FROM test_table
postgres-# WHERE age >= AVG(age);
--ERROR:  aggregates not allowed in WHERE clause
--行 3: WHERE age >= AVG(age);
--                   ^

スカラ・サブクエリを利用

postgres=# SELECT *
postgres-# FROM test_table
postgres-# WHERE age >= (SELECT AVG(age) FROM test_table);
/*
  id  | name | age | department 
------+------+-----+------------
 002  | 鈴木 |  41 | A 
 003  | 高橋 |  41 | C 
 006  | 社長 |  58 | 
(3 rows)
*/

テーブルを集約する GROUP BY句

GROUP BYは指定した列名でテーブルを集約（集計）する。この列名を集約キーという。
今回の例ではdepartment列の値のみに基づいてレコードを表示するため、A、B、Cの3行に絞られる（WHERE句でC以外に指定するためここでは表示はAとBのみ）。
なお、SELECT句で付したエイリアスを指定することは不可。

postgres=# SELECT SUM(age), COUNT(*), department
postgres-# FROM test_table
postgres-# WHERE department <> 'C'
postgres-# GROUP BY department;
/*
 sum | count | department 
-----+-------+------------
  62 |     2 | B 
  80 |     2 | A 
(2 rows)
*/

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

GROUP BYで集約した結果に対してWHERE句で条件指定しようとするとエラーになる。
例えば、上記の例では「部門の年齢の合計値が70以上のみ表示」といった絞り込みをしたい場合に以下の式ではエラーする。これは、集約関数はWHERE句に書くことができないため。

集約関数をWHERE句で使うとエラー

postgres=# SELECT SUM(age), department
postgres-# FROM test_table
postgres-# WHERE SUM(age) >= 70
postgres-# GROUP BY department;
--ERROR:  aggregates not allowed in WHERE clause
--行 3: WHERE SUM(age) >= 70
--            ^

こうした集合に対する条件指定にはHAVING句を使う。

集約結果に対する条件指定(HAVING句)

postgres=# SELECT SUM(age), department
postgres-# FROM test_table
postgres-# GROUP BY department
postgres-# HAVING SUM(age) >= 70;
/*
 sum | count | department 
-----+-------+------------
  80 |     2 | A 
(1 row)
*/

HAVING句に記述可能な要素

定数
集約関数
集約キー

ソートする ORDER BY句

ソートキーを指定し、ソートの基準となる列を指定する。複数のソートキーを設定することも可能（1つ目のソートキーが同値の場合に2つ目が用いられる）。なお、NULLは先頭/末尾にまとめて表示される(DBMS依存)。
なお、SELECT句で付したエイリアスを指定することが可(GROUP BY句は不可)。
デフォルトでは昇順だが、以下のキーワードで降順を指定できる。

ASCキーワード→明示的な昇順(ascendent)
DESCキーワード→降順(descendent)

postgres=# SELECT <列名>
postgre-=# FROM <テーブル名>
postgres-# ORDER BY <ソートキー1>, <ソートキー2>, ・・・ <昇降順キーワード>;

サブクエリ/スカラ・サブクエリ/相関サブクエリ

クエリの中でクエリ実行するもの。

postgres=# SELECT <列名>
postgres-# FROM (
postgres(#   SELECT <列名>, ・・・
postgres(#   FROM <テーブル名>
postgres(#   GROUP BY <列名>
postgres(# ) AS <サブクエリ名>

サブクエリのASキーワードは省略可能。サブクエリ名自体は必要なので、命名自体は省略できない。
スカラ・サブクエリは返り値が一行に定まるクエリ。WHERE句の条件式などで指定。

相関サブクエリ

departmentごとにageの平均値を出し、それ以下のageのレコードのみ取得したい場合。departmentごとに平均ageを算出してしまうと複数行の返り値となり、スカラ・サブクエリを用いた条件文では記述できない。

複数返り値のためエラー

postgres=# SELECT *
postgres-# FROM test_table
postgres-# WHERE age > (SELECT AVG(age)
postgres(#              FROM test_table
postgres(#              GROUP BY department);
--ERROR:  more than one row returned by a subquery used as an expression

相関サブクエリはdepartment全てを一度に返さない。(ループ処理みたいなことが行われているのだろうか？)

相関サブクエリ

postgres=# SELECT *
postgres-# FROM test_table AS S1
postgres-# WHERE age <= (SELECT AVG(age)
postgres(#               FROM test_table AS S2
postgres(#               WHERE S1.department = S2.department
postgres(#               GROUP BY department)
postgres-# ORDER BY department;
/*
  id  | name | age | department 
------+------+-----+------------
 001  | 佐藤 |  39 | A 
 004  | 田中 |  28 | B 
 003  | 高橋 |  41 | C 
(3 rows)
*/

データ制御言語(DCL: Data Control Langage)

トランザクション制御言語(TCL: Transaction Control Langage)

トランザクション

更新処理のひとまとめのセット。トランザクション中の一連の処理は失敗した場合、全ての処理が元の状態に戻る。
本来SQLではトランザクションは暗黙的に開始されるため明示的な指定は不要である。そのため、DBMSによって違いがある。

処理の開始

--Postgre, SQL Server
BEGIN TRANSACTION;
--MySQL
START TRANSACTION;

処理の確定/取り消し

-- 確定
COMMIT;
-- 取り消し
ROLLBACK;

ACID特性

トランザクションにおいて守られるべきルール。

原子性(Atomicity)
all or nothing。トランザクションの処理は全て成功か、全て失敗（実行されない）かの二択。
一貫性/整合性(Consistency)
トランザクションのデータはDBにあらかじめ設定された制約を満たす。
独立性(Isolation)
トランザクション中のデータ変更はコミットされるまで別のトランザクションから参照されない。（中途半端な状態のデータを参照されることがない。）
永続性/耐久性(Durability)
トランザクションが終了したと同時にデータは保存されること。トランザクションの実行記録はログとして保存される。

テーブル操作

行方向の集合演算子

書式

postgres=# SELECT <列名>
postgres-# FROM <テーブル名>
postgres-# <集合演算子> <オプション>
postgres-# SELECT <列名>
postgres-# FROM <テーブル名>;

UNION(和)
- 重複行は排除される。
- UNIONの後ろにALLオプションを付けると重複行は排除されない。
TINTERSECT(交差)
- 重複したレコードのみ取り出す。
EXCEPT(差)
- 前者から後者を引いたレコード。

列方向の集合演算子

内部結合 INNER JOIN

postgres=# SELECT <テーブル名/エイリアス名>.<列名>
postgres-# FROM <テーブル名> AS <エイリアス1> INNER JOIN <テーブル名> AS <エイリアス2>
postgres-# ON <エイリアス1>.<結合キーとする列名> = <エイリアス2>.<結合キーとする列名>;

FROM句で結合したい2つのテーブルを指定する。それぞれにエイリアスを付けることが可能。
SELECT句ではどのテーブル（またはエイリアス）の列を表示するかを指定。テーブル名は省略してもエラーにはならないが、記述すること推奨。
ON句はどの列をもとに結合するかの結合キーを指定する。

外部結合 OUTER JOIN

postgres=# SELECT <テーブル名/エイリアス名>.<列名>
postgres-# FROM <テーブル名> AS <エイリアス1> RIGHT OUTER JOIN <テーブル名> AS <エイリアス2>
postgres-# ON <エイリアス1>.<結合キーとする列名> = <エイリアス2>.<結合キーとする列名>;

マスタに指定したテーブルの情報をすべて出力し、他方のテーブルに対応するレコードがない場合にはNULLで埋める。
FROM句で指定する2つのテーブルにおいて、RIGHTまたはLEFTでどちらをマスタとするかを指定する。

3つ以上のテーブルを結合する場合

複数テーブルを結合

postgres=# SELECT <テーブル名/エイリアス名>.<列名>
postgres-#   FROM <テーブル名> AS <エイリアス1> INNER JOIN <テーブル名> AS <エイリアス2>
postgres-#   ON <エイリアス1>.<結合キーとする列名> = <エイリアス2>.<結合キーとする列名>
postgres-#     INNER JOIN <テーブル名> AS <エイリアス3>
postgres-#     ON <エイリアス1>.<結合キーとする列名> = <エイリアス3>.<結合キーとする列名>
postgres-#        :

クロス結合 CROSS JOIN(直積)

クロス結合は全てのパターンの結合結果を返す。ON句は指定不可。

postgres=# SELECT <テーブル名/エイリアス名>.<列名>
postgres-#   FROM <テーブル名> AS <エイリアス1> CROSS JOIN <テーブル名> AS <エイリアス2>

その他

OLAP関数(OnLine Analytical Processing :ウィンドウ関数)

集計用の関数と処理。
パーティションによって区切られたレコードの集合をウィンドウと呼び、これらの範囲で実行される関数のこと。

構文

<ウィンドウ関数> OVER (PARTITION BY <列名> ORDER BY <ソート列名>)
--パーティションは指定しなくても良い。

ウィンドウ関数

集約関数
ウィンドウ専用関数（RANK、DENSE_RANK、ROW_NUMBERなど）
常に引数なし。
SELECT句でのみ使用可能。

postgres=# SELECT *,                                              
postgres-# RANK () OVER (ORDER BY age) AS ranking,
postgres-# DENSE_RANK () OVER (ORDER BY age) AS dense_ranking,
postgres-# ROW_NUMBER () OVER (ORDER BY age) AS row_num
postgres-# FROM test_table;
/*
  id  | name | age | department | ranking | dense_ranking | row_num 
------+------+-----+------------+---------+---------------+---------
 004  | 田中　|  28 | B          |       1 |             1 |       1
 005  | 伊藤　|  34 | B          |       2 |             2 |       2
 001  | 佐藤　|  39 | A          |       3 |             3 |       3
 002  | 鈴木　|  41 | A          |       4 |             4 |       4
 003  | 高橋　|  41 | C          |       4 |             4 |       5
 006  | 社長　|  58 |            |       6 |             5 |       6
(6 rows)
*/

ウィンドウ関数以外を使う

SUMを使った場合。→累計

postgres=# SELECT *,
postgres-# SUM(age) OVER (ORDER BY age) AS current_sum
postgres-# FROM test_table;
/*
  id  | name | age | department | current_sum 
------+------+-----+------------+-------------
 004  | 田中 |  28 | B          |          28
 005  | 伊藤 |  34 | B          |          62
 001  | 佐藤 |  39 | A          |         101
 002  | 鈴木 |  41 | A          |         183
 003  | 高橋 |  41 | C          |         183
 006  | 社長 |  58 |            |         241
(6 rows)
*/

GROUPING演算子、ROLLUP、CUBE

GROUP BYによるグループごとの演算結果と、全体の演算結果を同時に表示したい時。
例：グループ毎の小計と全体の合計を一度に表示したい場合など。

構文

--ROLLUP
postgres=# SELECT *
postgres-# FROM <テーブル名>
postgres-# GROUP BY ROLLUP(<列名1>, <列名2>, ・・・);

/*
以下の各パターンを集約して結果を返す。
GROUP BY ()
GROUP BY (<列名1>)
GROUP BY (<列名1>, <列名2>)
 :
*/

--CUBE
postgres=# SELECT *
postgres-# FROM <テーブル名>
postgres-# GROUP BY CUBE(<列名1>, <列名2>, ・・・);

/*
可能な組み合わせ全パターンを集約して結果を返す。
GROUP BY ()
GROUP BY (<列名1>)
GROUP BY (<列名2>)
GROUP BY (<列名1>, <列名2>)
 :
*/

group       | sum
------------+----------
            | 60          //総計 (超集合行という。)
A           | 10          //以下は各グループの小計
B           | 20
C           | 30

なお、グルーピングの結果返される"合計"はその他の列は空白となるが、これはNULLが入っている訳ではない。
これを明示的に区別するのがGROUPING関数。引数に<列名>を入れることでその列がNULLであれば1を、それ以外なら０を返す。

部分一致検索 LIKE

postgres=# SELECT *
postgres-# FROM <テーブル名>
postgres-# WHERE <列名> LIKE '検索文字列';

%：任意の文字列。
_:任意の一文字。

ORの列挙 IN

postgres=# SELECT *
postgres-# FROM <テーブル名>
postgres-# WHERE <列名> IN (<値1>, <値2>, <値3>);

INの引数にとった値と一致するものを絞り込み。
なお、NULLは検索対象とならない。→IS NULLのみ。

CASE式とWHEN・THEN句

switch文みたいなやつ。

--書式1
postgres=# SELECT *,
postgres-#     CASE WHEN <列名> = <値>
postgres-#          THEN <式> 
postgres-#          WHEN <列名> = <値>
postgres-#          THEN <式> 
postgres-#            :
postgres-#          ELSE <式>
postgres-#     END AS <列名>
postgres-# FROM <テーブル名>;

--書式2
postgres=# SELECT *,
postgres-#     CASE <列名>
postgres-#          WHEN <値> THEN <式> 
postgres-#          WHEN <値> THEN <式> 
postgres-#            :
postgres-#          ELSE <式>
postgres-#     END AS <列名>
postgres-# FROM <テーブル名>;