PostgreSQLの使い方

データベース知識の復習ついでにPostgreSQLに触っています。基礎知識と基本操作をメモしておきます。なお、操作説明に焦点を当てているため、正規化などの前提知識については解説を行いません。

PostgreSQLをインストールする

CentOSにPostgreSQLのインストールを行います。yumを使用します。

rootユーザーに変更します。

su -

PostgreSQLをインストールします。

yum install postgresql-server

インストールが終わったら、PostgreSQLが使用するファイル(データベースの本体)を作成します。

postgresql-setup initdb

問題なく初期化が行えたらPostgreSQLサーバを有効にします。

systemctl enable postgresql

最後にPostgreSQLサーバを起動します。

systemctl start postgresql

PostgreSQLの基本操作

データベースを操作するにはデータベース操作用に用意されている専用ユーザーpostgresに切り替える必要があります。
postgresユーザーになるには一度rootユーザーに切り替えた後、もう一度suコマンドを使用することで切り替えを行います。

su -
su - postgres

データベースを操作するにはpsqlコマンドを利用します。

psql

これでデータベースを操作できる状態になりました。
もし、psqlコマンドから抜けたい場合は\qと入力します。

\q

データベースを作成する

データベースを作成するにはCREATE DATABASEコマンドを実行します。
今回、データベース名はa09001とします。

CREATE DATABASE a09001;

最後のセミコロン「;」を忘れずに入力してください。もし忘れてしまった場合はセミコロンだけ後から入力すればSQLが実行されます。

最後のセミコロン「;」はSQLの文ではなく、psqlへの指示で、「今書いたSQLを実行してください」という意味になります。(SQLの途中で改行してもその時点でSQLが実行されるわけではないため、長いSQLを一行で入力する必要はありません)

データベースが作成したかを確認するには\listコマンドを使用します。

\list

今作成したデータベースが一覧に表示されます。
続いて、データベースへの接続方法を確認するので、\qでpsqlから抜けてください。

データベースへ接続する

データベースを作成したので、次に作成したデータベースに接続します。psqlコマンドの引数としてデータベース名を与えると、指定したデータベースに接続されます。

psql a09001

また、psqlコマンドから抜けずにデータベースに接続する場合は\connectコマンドを使用します。また、短縮形の\cを使用することもできます。

\c a09001

データベースを削除する

作成したデータベースを削除するにはDROP DATABASEコマンドを実行します。
適当なデータベースを作り、削除してみましょう。(接続中のデータベースは削除できないため、a09001を削除しようとしてもエラーになります)

CREATE DATABASE hello;
\list
DROP DATABASE hello;
\list

テーブルの基礎知識

これからデータベースにテーブルを作成しますが、ここではテーブル作成時の前提知識を勉強します。PostgreSQLでテーブルを作成するとき、以下のようなデータ型が利用可能です。

データ型 データの内容
INTEGER -2147483648から+2147483647までの整数値
NUMERIC 任意の精度の数値データ型
CHAR 固定長の文字列型。定義時に最大文字数を指定
VARCHAR 可変長の文字列型。定義時に最大文字数を指定
DATE 日付
TIMESTAMP 日付と時刻

NUMERICは「NUMERIC(6,2)」のように指定すると、有効桁が6桁で小数点以下が2桁という指定になります。
有効桁6桁というのは整数部分、小数部分合わせた桁数であることに注意してください。この場合、整数部分が4桁以上の数値は格納できません。例えば、「1234.56」という値はOKですが、「12345.6」はエラーになります。また、「123.456」という数値はエラーになりませんが、小数部分は2桁なので「123.46」と四捨五入して格納されます。

CHARは、「CHAR(100)」のように最大文字数を指定します。この場合、実際には「abc」のように3文字しか格納しなくてもデータベースには100文字分の領域が確保されます。
一方、VARCHARでは実際の文字列に応じてデータベースに確保される領域が変動します。そのため、通常はVARCHARが利用されます。

また、オプションを設定することができます。オプションには列のデフォルト値や制約を指定できます。デフォルト値では、「DEFAULT デフォルト値」と指定することにより、列の値が指定されたなかった場合のデフォルト値を指定します。

制約にはその列が満たすべき条件を指定します。
制約には以下のような種類があります。

制約 意味
PRIMARY KEY 列は主キーである
UNIQUE 列の値は一意である(NULLの重複は許可)
NOT NULL 列にNULLを許可しない
CHECK 列に対して満たすべき条件式を指定
REFERENCES 参照整合性制約(外部キー)を指定

テーブルを作成する

テーブルを作成するにはCREATE TABLEコマンドを実行します。
今回は以下のように書籍テーブルbookを作成します。

列名 意味 データ型 オプション
id ID INTEGER PRIMARY KEY
title 書名 VARCHAR(80) NOT NULL
author 著者 VARCHAR(80)
price 価格 INTEGER CHECK (price > 0)
published 出版日 DATE

CHECK制約では上記のように、列に対して条件式を記述します。この場合、price列は0より大きい値のみ格納できるようになります。(NOT NULL制約を定義していないので、NULLは許可されます)

テーブルの定義に対して入力するSQLを組み立てます。下記のように一データ項目一行ずつ入力していき、最後にセミコロン「;」を忘れずに入力してください(a09001=#は入力しなくて大丈夫です)。途中でSQLを書き間違えてしまった場合はCtrl + Cキーで現在記述中のSQLをキャンセルすることができます。

a09001=# CREATE TABLE book (
a09001(# id INTEGER PRIMARY KEY,
a09001(# title VARCHAR(80) NOT NULL,
a09001(# author VARCHAR(80),
a09001(# price INTEGER CHECK (price > 0),
a09001(# published DATE);

テーブル一覧を確認するには\dと入力します。

\d

テーブルの詳細情報を確認するには\d テーブル名と入力します。

\d book

練習で以下のような顧客情報テーブルも作ってみましょう。

a09001=# CREATE TABLE customer (
a09001(# id INTEGER PRIMARY KEY,
a09001(# name VARCHAR(80) NOT NULL,
a09001(# address VARCHAR(200) NOT NULL);

テーブルを削除する

作成したテーブルを削除するにはDROP TABLEコマンドを実行します。
適当なテーブルを作り、削除してみましょう。

CREATE TABLE hello(id INTEGER PRIMARY KEY);
\d
DROP TABLE hello;
\d

外部キーの基礎知識

例えば、Amazonのような本のオンラインストアを作る場合を考えてみます。顧客から注文を受け付けるには、「顧客テーブル」「書籍テーブル」「注文テーブル」「注文詳細テーブル」の4つが必要です。

実際に、「注文テーブル」と「注文詳細テーブル」の構造は以下のようになっています

注文テーブル

注文ID 顧客ID 注文日

注文詳細テーブル

注文ID 書籍ID 注文量

注文テーブルの「顧客ID」は顧客テーブル中にある一行を参照しています。また、注文詳細テーブルの「書籍ID」は書籍テーブル中の一行、「注文ID」は注文テーブル中の一行を参照します。このように別テーブルの行を参照している列を外部キーと言います。

外部キーが参照している先の行が削除されてしまうと不具合が生じます。
例えば、注文詳細テーブルに書籍ID1111に関する注文があるのに、書籍テーブルにIDが1111の行がなければどの書籍に対する注文なのかがわからなくなってしまいます。
このように参照先が必ず存在するという制約を参照整合性制約と言います。

SQLでは、CREATE TABLEコマンドで列を定義する際にREFERENCES制約を付けることにより、参照整合性を設定できます。

書式は以下のようになります。

REFERENCES 参照テーブル名(参照列名) [ON DELETE オプション] [ON UPDATE オプション]

オプションでは、参照先が削除、または、更新されるときにどのような動作を行うかを設定します。

オプション 意味
NO ACTION 何もしない(デフォルト)
通常、参照整合性制約エラーが起きる
RESTRICT 参照している行があると削除/変更できない
CASCADE 参照している行が合わせて削除される、更新される
SET NULL NULLにする
SET DEFAULT デフォルト値にする(参照整合性制約を満たす必要がある)

外部キーを含むテーブルを作成する

それでは実際に外部キー(参照整合性制約)を含むテーブルを作成してみましょう。今回は以下のように注文テーブルorder_を作成します。練習で作成したcustomerテーブルのid列を参照するように設定します。
※ORDERはSQLの予約語のためテーブル名に使えません。そういう場合、アンダースコア(_)を付けるなど予約語を回避した名前を付けることになります。

列名 意味 データ型 オプション
id ID INTEGER PRIMARY KEY
customer_id 顧客ID INTEGER REFERENCES customer(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
date 注文日 DATE

以下のSQLを入力してください。

a09001=# CREATE TABLE order_ (
a09001(# id INTEGER PRIMARY KEY,
a09001(# customer_id INTEGER REFERENCES customer(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
a09001(# date DATE);

作成ができたら\dコマンドを使ってテーブル定義を確認してください。

\d order_

練習で以下のような注文詳細テーブルも作ってみましょう。
これは以下のようなテーブルです。

列名 意味 データ型 オプション
order_id 注文ID INTEGER REFERENCES order_(id)
ON DELETE CASCADE
ON UPDATE CASCADE
book_id 書籍ID INTEGER REFERENCES book(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
amount 数量 INTEGER CHECK (amount > 0)

注文IDにはCASCADEを指定しています。これは、注文が削除(取り消し)されたら、関連する注文詳細も削除するためです。一方、書籍については注文がある限り削除できないようにRESTRICTを指定しています。

なお、order_detailは他のテーブルとは異なり、注文IDと書籍IDの組み合わせが主キーとなります。このような複数列の組み合わせを主キーとする場合、SQLでは列定義の後にテーブル全体の制約という形で以下のように記述を行います。

CREATE TABLE order_detail (
列定義...,
PRIMARY KEY (order_id, book_id)
);

以下のSQLを入力してください。

a09001=# CREATE TABLE order_detail (
a09001(# order_id INTEGER REFERENCES order_(id) ON DELETE CASCADE ON UPDATE CASCADE,
a09001(# book_id INTEGER REFERENCES book(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
a09001(# amount INTEGER CHECK (amount > 0),
a09001(# PRIMARY KEY (order_id, book_id)
a09001(# );

インデックスの基礎知識

以前の項でbookテーブルを作成した際、以下のように表示されたかと思います。

NOTICE:  CREATE TABLE / PRIMARY KEYはテーブル"book"に暗黙的なインデックス"book_pkey"を作成します

これは主キーは各行を識別するものなので、主キーを指定してデータを検索された場合に使う"book_pkey"というインデックスが作成されたということです。

インデックスは自動的に作成されるもの以外に「よく検索に利用される」列に対して手動で作成することも可能です。

インデックスを作成する

インデックスの作成にはCREATE INDEXコマンドを使用します。
書式は以下のようになります。

CREATE INDEX インデックス名 ON テーブル名 (列名)

それではbookテーブルのtitle列に対してインデックスを作成します。

CREATE INDEX book_title ON book(title);

インデックスが作成されたかを確認するには\diコマンドを実行します。

\di

インデックスを削除する

作成したインデックスを削除するにはDROP INDEXコマンドを実行します。
適当なインデックスを作り、削除してみましょう。

CREATE INDEX hello_index ON book(autor);
\di
DROP INDEX hello_index;
\di

データを作成する

作成したテーブルにデータを格納するにはINSERTコマンドを使用します。

書式は以下のようになります。

INSERT INTO テーブル名 VALUES(データ, ...)

それでは実際にデータを格納してみましょう。

INSERT INTO book VALUES(1234, 'はじめてのデータベース', '明石 太郎', 2000, '2012-1-23');

入力内容が正しくデータベースに格納されたか確認してみましょう。以下のSQLコマンドを入力してください。SELECTコマンドについてはあとで説明します。

SELECT * FROM book;

練習

ここまででbook, customer, order_, order_detailの4つのテーブルを作成してきました。今回はこれらのテーブルにデータを作成してみましょう。

  1. bookテーブル
INSERT INTO book VALUES(2222, 'SQLレシピブック', '藤井 隆史', 3000, '2010-1-10');
INSERT INTO book VALUES(3456, '入門PHP', '江上 保', 2000, '2012-2-9');
INSERT INTO book VALUES(4444, '実践PHP', '江上 保', 3600, '2012-4-10');
INSERT INTO book VALUES(5678, 'よくわかるJava', '川田 一郎', 2800, '2008-6-20');
INSERT INTO book VALUES(6666, '1週間でわかるPostgreSQL', '明石 太郎', 2400, '2012-5-12');
INSERT INTO book VALUES(7890, 'MySQL入門', '明石 太郎', 2400, '2009-10-3');
INSERT INTO book VALUES(8888, 'インデックスマニアックス', '酒井 章吾', 3200, '2014-8-26');
  1. customerテーブル
INSERT INTO customer VALUES(1111, '鈴木 一郎', '東京都新宿区');
INSERT INTO customer VALUES(2222, '田中 花子', '神奈川県横浜市');
INSERT INTO customer VALUES(3333, '織田 家康', '愛知県清須市');
  1. order_テーブル
INSERT INTO order_ VALUES(111111, 1111, '2015-1-20');
INSERT INTO order_ VALUES(111112, 1111, '2015-2-25');
INSERT INTO order_ VALUES(222222, 2222, '2016-4-3');
INSERT INTO order_ VALUES(222223, 2222, '2016-5-12');

customer_id列は参照整合性制約が設定されているため、customerテーブルにないIDを挿入しようとするとエラーになります。

  1. order_detailテーブル
INSERT INTO order_detail VALUES(111111, 3456, 1);
INSERT INTO order_detail VALUES(111111, 4444, 1);
INSERT INTO order_detail VALUES(111112, 5678, 1);
INSERT INTO order_detail VALUES(222222, 1234, 5);
INSERT INTO order_detail VALUES(222222, 3456, 5);
INSERT INTO order_detail VALUES(222222, 6666, 5);
INSERT INTO order_detail VALUES(222223, 5678, 3);
INSERT INTO order_detail VALUES(222223, 7890, 3);

このテーブルではorder_id、book_idに対して参照整合性制約が設定されているため、対応するテーブルにないIDを設定しようとするとエラーになります。

また、このテーブルではorder_idとbook_idの組み合わせをPRIMARY KEYとしているので上記のデータを入れた後で同じorder_idとbook_idの組み合わせの行を格納しようとするとエラーになります。

制約を満たさないデータの挿入

制約を満たさないデータを格納してみましょう。次のSQLを入力してください。

INSERT INTO book VALUES(NULL, 'タイトル未定', '加藤 卓', NULL, NULL);

エラーメッセージが表示されます。IDが決まっていないからとIDをNULLにしてテーブルに格納しようとしても制約に違反するので格納できないことがわかります。

SQLを実行した場合、2種類のエラーが起こる可能性があります。

  1. 構文エラー
    入力したSQLがSQLの文法上正しくない場合に発生します。例えば、「INSERT book VALUES(1234, 'はじめてのデータベース', '明石 太郎', 2000, '2012-1-23')」と書いたような場合です。(INTOがないので構文エラー)
  2. 制約エラー
    PRIMARY KEYに指定した列にNULLを指定するなど、テーブルの制約を満たさない場合に発生するエラーです。

エラーが起きた場合、構文エラーなのか制約エラーなのかを表示されたメッセージをよく確認し、SQLの文法を修正する必要があるのか、格納するデータを修正する必要があるのかを考えてください。

列を指定した挿入

INSERTコマンドでは値を格納する列を指定することもできます。指定する列の順番はテーブルの定義順でなくても構いません。指定されていない列に挿入される値はNULLになります。値を指定しなかった列にNOT NULL制約などの制約が設定されているとエラーになり、データを格納することはできません。

書式は以下のようになります。

INSERT INTO テーブル名(列, ...) VALUES(データ, ...)

それでは実際に列を指定してデータを格納してみましょう。次のSQLを入力してください。

INSERT INTO book(id, title, author) VALUES(2223, 'SQLレシピブック第2版', '藤井 隆史');

データを検索する

データベースを利用する際に最も重要なのはデータの検索です。
SQLでデータを検索する際に利用するSELECTコマンドも絞り込み条件の指定、検索結果の並び替え、集計(該当データの数を数えるなど)、表をまたいだ検索など非常に様々な検索の方法を利用することができます。

まずはテーブルのデータを単純に表示するところから始めましょう。書式は以下のようになります。

SELECT 列指定 FROM テーブル名

列指定は「*」もしくは表示したい列名をカンマ(,)区切りで指定します。
この列指定は第5章で学習した射影演算に該当します。

まず、「*」を指定して全列を表示してみましょう。

SELECT * FROM book;

次に表示される列を限定してみましょう。
なお、列の順番はテーブル定義の順番である必要はありません。

SELECT title, price FROM book;

列に別名を付ける AS

指定した列にASで別名を付けることができます。(ASは省略可能です)
別名に空白を含む場合はダブルクォーテーション(")で囲みます。

次のSQLを入力してください。

SELECT title AS 書名, price AS 価格 FROM book;

重複行を取り除く DISTINCT

著者の一覧を取得することを考えます。author列を表示してみましょう。

SELECT author FROM book;

9行表示されますが、「明石 太郎」さんなどは複数の本を書いているため、重複して出力されています。列指定の前にDISTINCTを付けることで重複を除いた行が返されるようになります。

SELECT DISTINCT author FROM book;

条件を指定して検索

データを検索する際に検索されるデータ(行)を絞り込むにはWHERE句を指定します。

書式は以下のようになります。

SELECT 列指定 FROM テーブル名 WHERE 条件式

WHERE句の条件式の中では条件演算子を使用することができます。

条件演算子 意味
= 指定値と等しい
<> 指定値と等しくない(指定と異なる)
> 指定値より大きい(指定値を含まない)
>= 指定値以上(指定値を含む)
< 指定値より小さい(指定値を含まない)
<= 指定値以下(指定値を含む)

実際にやってみましょう。下記のSQLを実行してみてください。
「書名が『SQLレシピブック』である本のデータ」を検索するには以下のようにします。

SELECT * FROM book WHERE title = 'SQLレシピブック';

次に不等号の使用例です。
「価格が3000円未満である本のデータ」を検索するには以下のようにします。

SELECT * FROM book WHERE price < 3000;

なお、不等号はpublishedのような日付列に使用することも可能でその場合、「指定された日付より前(<)」「指定された日付以降(>=」のデータが検索されます。

NULLを検索する IS NULL

NULLの扱いは特別です。NULLの行を取り出そうとして、以下のようなSQL文を書いても取り出すことはできません。

SELECT * FROM book WHERE published = NULL;

値がNULL(何もない)かを確認するには、IS NULLを使用する必要があります。

SELECT * FROM book WHERE published IS NULL;

逆に、「NULLでない行」を取り出すには、IS NOT NULLを使用します。

SELECT * FROM book WHERE published IS NOT NULL;

ところで、NULLの列を不等号比較するとどうなるでしょうか。例えば、以下のようなSQL文を実行した場合です。

SELECT * FROM book WHERE price >= 3000;

答えは、価格がNULLの行は未満でも以上でも抽出されません。列の値がNULLだと比較演算は指定された値より大きいか小さいか不明(UNKNOWN)という結果を返します。UNKNOWNとなった場合、DBMSは条件にマッチしないとして結果に含めません。

範囲指定 BETWEEN

SELECTでは「AからBの間の値(A以上B以下)」という指定することも可能です。その場合、BETWEENを使用します。

利用してみましょう。以下のSQLを入力してください。このように入力することで「2012年に発売された書籍一覧」を検索することが可能です。

SELECT * FROM book WHERE published BETWEEN '2012-1-1' AND '2012-12-31';

また、これは以下のような条件演算子で検索することと同じ意味です。

SELECT * FROM book WHERE published >= '2012-1-1' AND published <= '2012-12-31';

いずれかを含む IN

「『明石 太郎』か『江上 保』が書いた本を調べたい」という検索を行うことがあります。この場合、INを使用します。

それでは先ほどの条件でbookテーブルを検索してみましょう。以下のSQLを入力してください。

SELECT * FROM book WHERE author IN ('明石 太郎', '江上 保');

また、これは以下のような条件演算子で比較することと同じです。

SELECT * FROM book WHERE author = '明石 太郎' OR author = '江上 保';

あいまい検索 LIKE

「書名に『PHP』を含んでいる本」を検索する場合、LIKEを使用します。

条件には「%」(0文字以上)と「_」(任意の1文字)を指定できます。
いくつか例を示します。

  • "%PHP":書名がPHPで終わる
    「入門PHP」、「よくわかるPHP」はマッチする。「PHP基礎」、「PHPリファレンスブック」はマッチしない
  • "PHP%":書名がPHPで始まる
    「PHP基礎」、「PHPリファレンスブック」はマッチする。「入門PHP」、「よくわかるPHP」はマッチしない
  • "%PHP%":書名のどこかにPHPを含む
    「入門PHP」、「よくわかるPHP」 、「PHP基礎」、「PHPリファレンスブック」いずれもマッチする
  • "__PHP":書名が○○PHP
    「入門PHP」はマッチする(「PHP」の前が2文字のため)が「よくわかるPHP」はマッチしない(「PHP」の前が5文字のため)

それでは実際に「書名に『PHP』を含んでいる本」を検索してみましょう。

SELECT * FROM book WHERE title LIKE '%PHP%';

さらに練習で「titleが『入門PHP』か『実践PHP』の本」を検索してみましょう。

SELECT * FROM book WHERE title LIKE '__PHP';

複合条件 AND, OR, NOT

データを検索する場合、「『明石 太郎』さんが書いた最近の本」のように複数のデータ項目を指定することがあります。SQLではそのような場合、複数の条件式をANDやORで接続することで複雑な条件を指定することが可能です。

条件演算子 意味
AND 条件式1と条件式2をともに満たす
OR 条件式1と条件式2のどちらかを満たす
NOT 条件式で指定されているもの以外

それでは実際に「『明石 太郎』さんが書いた最近の本(2012-1-1以降に発売された本)」を検索してみましょう。

SELECT * FROM book WHERE author = '明石 太郎' AND published >= '2012-1-1';

検索結果のソート ORDER BY

SELECTコマンドにORDER BY句を指定することで検索結果をソートすることが可能です。(書式ではWHEREを省略していますが、WHEREで行を絞り込んだうえでソートをするということももちろん可能です)

書式は以下のようになります。

SELECT 列指定 FROM テーブル名 ORDER BY ソート対象列 [ASC | DESC]

ソート対象列は列名、もしくは列番号で指定します。ASCと指定すると昇順(小さいものが先)、DESCと指定すると降順(大きいものが先)になります。なお、ソート対象列はコンマ(,)で区切ることで複数指定することも可能です。(その場合、第1列でソートした後、第2列でソートするという動きになります)

それでは実際に入力してみましょう。「『SQL』を書名に含むデータを検索し、発売日の降順(新しい順)に表示する」というSQLを入力します。

SELECT * FROM book WHERE title LIKE '%SQL%' ORDER BY published DESC;

DBMSによりNULLを最も小さいとするか最も大きいとするかが違います。PostgreslではNULLが最も大きい値とみなされるので、今回のような降順の場合、一番上にpublishedがNULLの行が優先されて表示されます。

データのグループ化 GROUP BY

SELECTコマンドでは行をグループ化することが可能です。どのような場合に利用するかというと、「各著者が何冊本を書いているか」というような検索をしたい場合に利用します。

行をグループ化するにはGROUP BY句を指定します。なお、GROUP BYを指定する場合、列指定は「*」を指定することはできずGROUP BYで指定した列のみが指定可能になります。

書式は以下のようになります。

SELECT 列指定 FROM テーブル名 GROUP BY グループ化する列

確認してみましょう。以下のSQLを入力してください。

SELECT author FROM book GROUP BY author;

GROUP BY句を使わない場合、「藤井 隆史、江上 保、江上 保、川田 一郎、明石 太郎、明石 太郎...」のように別々に表示されますが、GROUP BY句を使うと「川田 一郎、酒井 章吾、明石 太郎、藤井 隆史、江上 保」のようにグループ化が行われます。

集計関数

GROUP BYを指定することで著者一覧を取得することはできました。次に「各著者が何冊本を書いているか」を取得しましょう。

SQLには「何冊」のように集計を行うための集計関数(グループ関数)が用意されています。
集計関数はグループ化しない状態でも使用可能ですが、グループ化されている場合はグループごとに集計値を計算します。

関数 処理内容
COUNT 指定列の数を返す
列名の代わりに*を指定することも可能
SUM 指定列の合計を返す
AVG 指定列の平均を返す
MIN 指定列の最小値を返す
MAX 指定列の最大値を返す

集計関数は列指定のところで通常の列と合わせて指定します。
実際にやってみましょう。以下のSQLを実行してみてください。

SELECT author, COUNT(*) AS "number of books" FROM book GROUP BY author;

「各著者が何冊本を書いているか」を得ることができました。
なお、「COUNT(*)」にASを使用して別名を設定しました。別名を付けない場合、DBMSにより「count」のような名前が付けられます。

集計関数の結果を条件に絞り込みを行う HAVING

集計関数の結果を条件に絞り込みを行うにはにはHAVING句を指定します。HAVING句はGROUP BY句でグループ化した結果に対する条件指定をする場合に使用します。あくまでもGROUP BYした結果に対する絞り込みだということに気を付けてください。

SELECT 列指定 FROM テーブル名 GROUP BY グループ化する列 HAVING 条件式

例えば「2冊以上本を書いている著者」という条件は以下のようになります。

SELECT author FROM book GROUP BY author HAVING COUNT(*) >= 2;

練習で以下のようなSQLを書いてみましょう。

  1. 各著者が書いている本を全部買うといくらになるか(値段の合計)を出す
SELECT author, SUM(price) FROM book GROUP BY author;
  1. 書いてる本の値段の平均値が3000円以下の著者を一覧表示する
SELECT author, AVG(price) FROM book GROUP BY author HAVING AVG(price) <= 3000;

副問い合わせ

SELECTでは検索結果の値を利用して検索を行う副問い合わせという機能があります。WHERE句ではHAVING句のように集計関数は使えませんが、副問い合わせで同じような効果を得ることができます。

以下のSQLを入力してください。

SELECT * FROM book WHERE price > (SELECT AVG(price) FROM book);

()内のSELECT AVG(price) FROM bookが副問い合わせです。先に副問い合わせが評価され、左側のWHERE句の条件の一部に埋め込まれます。
このように1つのSQL文で「価格が平均値以上の本一覧」を出すことができます。

SELECT文の評価順序

SQLを正しく理解するためにSELECT文の評価順序を頭に入れておいてください。

1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.HAVING
7.SELECT
8.DISTINCT
9.ORDER BY
10.TOP(LIMIT)

特に注意したいのは、4.WHEREによって条件式が作られた後に、5.GROUP BYによってグループ化が行われ、その結果に対して6.HAVINGで絞り込みを行ない、その後、7.SELECTが評価されます。

GROUP BY句でグループ化を行うと、SELECTに列名を書かなくてはいけないのは上記の仕様によるためです。

データを更新する

データの更新を行うにはUPDATEコマンドを使用します。WHEREにどの行を更新対象とするかを指定します。

書式は以下のようになります。

UPDATE テーブル名 SET 列名1 = 値1, ... WHERE 条件式

実際に実行してみましょう。以下のSQLを入力してください。

UPDATE book SET price = 3300 WHERE id = 8888;

問題なく実行できたらSELECTコマンドでテーブル内のデータを見てみましょう。id=8888のpriceが変更されていることがわかります。

SELECT * FROM book;

なお、更新時に指定する値として、演算を指定することで現在値からの変化量を指定することも可能です。

UPDATE book SET price = price+100 WHERE id = 8888;

UPDATEコマンドの実行ではWHEREでどの行を更新するかを指定しました。このWHEREは必須ではなく、WHEREを付けずにUPDATEコマンドを実行することも可能です。その場合、全ての行が更新対象になってしまうので、注意してください。

それでは練習でデータを更新してみましょう。

  1. bookテーブルのid=1234のデータのpriceを2100に変更してください。
UPDATE book SET price = 2100 WHERE id = 1234;
  1. order_detailテーブルのorder_id=111111, book_id=3456のデータのamountをを1足してください。
UPDATE order_detail SET amount = amount + 1 WHERE order_id = 111111 AND book_id = 3456;

データを削除する

データの削除を行うにはDELETEコマンドを使用します。

書式は以下のようになります。

DELETE FROM テーブル名 WHERE 条件式

DELETEUPDATE同様、WHEREを指定しないと全ての行を削除するという動作になるので注意してください。(テーブル内のデータを一括削除する際に使用されることはあります)

実際に使用してみましょう。以下のSQLを入力してください。

DELETE FROM book WHERE id = 8888;

SELECTコマンドを実行してみましょう。id=8888の行が削除されていることがわかります。

SELECT * FROM book;

次にid=1234の行を消してみましょう。

DELETE FROM book WHERE id = 1234;

これは以下のようなエラーになります。

ERROR:  テーブル"book"の更新または削除は、テーブル"order_detail"の外部キー制約"order_detail_book_id_fkey"に違反します
DETAIL:  キー(id)=(1234)はまだテーブル"order_detail"から参照されています

出力にあるようにid=1234は別テーブルから参照されているため、削除することができません。(そうしないと注文情報だけ残り、何が注文されたかがわからないということになってしまいます)

上記は参照整合性制約のため、エラーになっています。order_detailテーブルを作成する際に以下の参照整合性制約を設定しました。

列名 意味 データ型 オプション
order_id 注文ID INTEGER REFERENCES order_(id)
ON DELETE CASCADE
ON UPDATE CASCADE
book_id 書籍ID INTEGER REFERENCES book(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
amount 数量 INTEGER CHECK (amount > 0)

RESTRICTは削除しようとしている行が他から参照されている場合に削除できないという指定です。
一方で、CASCADEは削除しようとしている行が他から参照されている場合、参照している行も合わせて削除します。

テーブルを結合する(内部結合)

ここまでは1つのテーブルについてだけデータ操作を行ってきました。本節では複数のテーブルをまたいだ検索について見ていきます。
複数のテーブルを特定の条件で連結することを結合と言います。

テーブルを結合するにはJOIN句を使用します。
書式は以下のようになります。

SELECT 列指定 FROM テーブル1 JOIN テーブル2 ON テーブル1.主キー = テーブル2.外部キー

試してみましょう。以下のSQLを入力してください。

SELECT * FROM order_detail JOIN book ON order_detail.book_id = book.id;

order_detailテーブルのbook_idに対応したbookテーブルの行が結合されます。
結合のイメージとしては以下のようになります。

65101

テーブルに別名を付ける

SELECTコマンドでは結合に用いるテーブル名の後ろに名前を記述することでに別名を付けることができます。付けた別名は列指定やJOIN句などで利用することができます。

書式は以下のようになります。

FROM テーブル1 テーブル1別名 JOIN テーブル2 テーブル2別名 ON テーブル1別名.主キー = テーブル2別名.外部キー

1文字の別名を付けることにより、記述を簡単にすることができます。

表示結果を整理する

単純にテーブルを結合しただけだとわかりにくいので列指定をして表示結果を整えましょう。テーブル別名も利用します。なお、2つのテーブルで同じ名前の列がある場合、どちらの列を表示するのか指定しないとエラーになります。

SELECT d.order_id, d.book_id, b.title, b.price, d.amount FROM order_detail d JOIN book b ON d.book_id = b.id;

結合されたテーブルに対しては第2節で学習したようにWHERE句で表示行を絞り込んだり、ORDER BY句でソートをかけたりすることが可能です。

SELECT d.order_id, d.book_id, b.title, b.price, d.amount FROM order_detail d JOIN book b ON d.book_id = b.id WHERE b.title like '%PHP%' ORDER BY d.order_id, d.book_id;

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

JOINを複数回書くことにより2テーブルだけではなく、3つ以上のテーブルを結合することも可能です。

SELECT d.order_id, d.book_id, b.title, b.price, d.amount FROM order_ o JOIN order_detail d ON o.id = d.order_id JOIN book b ON d.book_id = b.id;

JOINが2つあって混乱するかもしれませんが、1つずつ整理していけばそれほど難しくはありません。
まず、order_テーブルとorder_detailテーブルの結合が行われます。
その結果作成されたテーブルとbookテーブルの結合が行われ、最終的な結合結果が作成されます。

65102

結合のための別の方法

テーブルの結合を行うにはJOINを利用することを学習しましたが、古いSQLでは以下のように書かれていることがあります。

SELECT d.order_id, d.book_id, b.title, b.price, d.amount FROM order_detail d, book b WHERE d.book_id = b.id;

結果は同じですが、FROM句以降の書き方が異なります。SQLでJOIN句が定められたのはSQLが普及してから少し後だったため、今でもこのような書き方がされることがあります。特に、情報処理技術者試験ではこの書き方がされるため、問題を見てどのような意味かがわかるようにしておきましょう。

テーブルを結合する(外部結合)

前項ではorder_detailテーブルとbookテーブルを結合して注文に対する書籍情報を取得しました。
ところで、注文がない書籍を調べるにはどうすればいいでしょうか。結合では、order_detailテーブルのbook_idに対応するbookテーブルの行が連結されるため、order_detailテーブルにないbook_idの書籍は結合結果には表れなくなってしまいます。

マッチする注文がない書籍も検索結果に出すには外部結合を利用します。

左外部結合の書式は以下のようになります。

SELECT 列指定 FROM テーブル1 LEFT JOIN テーブル2 ON テーブル1.主キー = テーブル2.外部キー

右外部結合の書式は以下になります。

SELECT 列指定 FROM テーブル1 RIGHT JOIN テーブル2 ON テーブル1.主キー = テーブル2.外部キー

LEFT JOINでは、テーブル2にマッチしないテーブル1の行がすべて表示されます。言い換えると、テーブル1(左側に記述されたテーブル)を基準にしてテーブルの結合を行うということです。
逆に、RIGHT JOINでは、テーブル1にマッチしないテーブル2の行がすべて表示されます。言い換えると、テーブル2(右側に記述されたテーブル)を基準にしてテーブルの結合を行うということです。

今回は左側のbookテーブルについて、右側のorder_detailテーブルに対応行がある、ないを出したいのでLEFT JOINを利用します。

65301

以下のSQLを実行してください。

SELECT b.id, b.title, b.author, b.price, b.published, d.order_id FROM book b LEFT JOIN order_detail d ON b.id = d.book_id;

出力は以下のようになります。

id title author price published order_id
4444 実践PHP 江上 保 3600 2012-04-10 111111
5678 よくわかるJava 川田 一郎 2800 2008-06-20 111112
1234 はじめてのデータベース 明石 太郎 2100 2012-01-23 222222
3456 入門PHP 江上 保 2000 2012-02-09 222222
6666 1週間でわかるPostgreSQL 明石 太郎 2400 2012-05-12 222222
3456 入門PHP 江上 保 2000 2012-02-09 111111
7890 MySQL入門 明石 太郎 2400 2009-10-03
2223 SQLレシピブック第2版 藤井 隆史
2222 SQLレシピブック 藤井 隆史 3000 2010-01-10

idが2222, 7890, 2223というbookテーブルには存在するが、order_detailテーブルには対応行がない行も出力に含まれていることが分かります。
対応する注文がない書籍については、order_idはNULLになります。
これで、一冊も注文のない書籍の情報を取得することができました。

JOINの別名

JOINの書き方には、JOIN / INNER JOIN / LEFT JOIN / RIGHT JOIN / LEFT OUTER JOIN / RIGHT OUTER JOIN など様々な書き方があります。しかし、この中で覚えれば良いのは、JOIN / LEFT JOIN / RIGHT JOIN の3つだけです。

なぜなら、あとはすべて、この3つの別名に過ぎないからです。

  • INNER JOIN(内部結合)は1種類だけです。
    JOINが内部結合にあたります。
  • OUTER JOIN(外部結合)は2種類あります。
    LEFT JOIN
    RIGHT JOINが外部結合にあたります。

JOINが「内部結合」です。「結合できなかった行」は返しません。
本当の名前はINNER JOINといいます。

LEFT JOIN / RIGHT JOINが外部結合です。「結合できなかった行」がある場合も、無理やり表示するので、一部のレコードはNULLになってしまうことがあります。
本当の名前はLEFT OUTER JOIN / RIGHT OUTER JOINです。

副問い合わせを利用した検索

前項では外部結合を利用して注文がない書籍、注文がない顧客を検索しました。

このような検索結果を得るには外部結合とは別の方法を用いることも可能です。本項では相関副問い合わせを利用する方法を説明します。
相関副問い合わせは主問い合わせの各行を副問い合わせに渡して問い合わせを実行するというものです。

具体的なSQLを見てみましょう。以下のSQLを入力してください。

SELECT * FROM book b WHERE NOT EXISTS (SELECT * FROM order_detail d WHERE d.book_id = b.id);

このSQL文は以下のような結果を出力します。

id title author price published
2222 SQLレシピブック 藤井 隆史 3000 2010-01-10
7890 MySQL入門 明石 太郎 2400 2009-10-03
2223 SQLレシピブック第2版 藤井 隆史

EXISTSは、副問い合わせの検索結果がある場合に主問い合わせのWHERE句が真になるというものです。NOTを付けているので、検索結果がない場合に主問い合わせのWHERE句が真になり、結果として注文がない書籍の一覧が得られます。

つまり、EXISTSは注文があった本の情報を返します。
逆にNOT EXISTSは注文がなかった本の情報を返します。

ビューを作成する

注文情報を見る場合に毎回、結合を用いたSQLを書いていると入力ミスする可能性もあり非効率です。また、注文情報を見たい人にとっては、テーブルが実際にはどのように分割されているかはあまり関心がありません。

データベースを利用する人は外部スキーマを介してデータベースに格納されている値を参照します。
一方、正規化を行い、複数のテーブルに分割した状態は内部スキーマに当たります。
そこで、内部スキーマと外部スキーマをつなぐための仕組みが必要になります。それがビュー(VIEW)です。

12101

ビューを作成するにはCREATE VIEWコマンドを実行します。

書式は以下のようになります。

CREATE VIEW ビュー名 AS SELECTコマンド

実際にビューを作成してみましょう。以下のSQLを実行してください。

CREATE VIEW order_list AS
SELECT d.order_id, c.name, d.book_id, b.title, b.price, d.amount 
FROM order_ o
JOIN order_detail d ON o.id = d.order_id
JOIN book b ON d.book_id = b.id
JOIN customer c ON o.customer_id = c.id;

これでビューが作成されました。
なお今回は単純にテーブルを結合しただけですが、WHERE句を指定して表示される内容を絞り込むといったことももちろん可能です。

作成したビューは以下のコマンドで確認することができます。

\dv

ビューを作成したら、テーブルと同様、SELECTコマンドを使うことで内容を表示できます。

SELECT * FROM order_list;

このようにビューはテーブルと同じ感覚で扱うことが可能です。ビューを作成することにより、複雑な結合の指定を裏に隠してシンプルな仮想のテーブルを利用者に提供することができます。これはどういうことかというと、利用者がビューを参照する際に、裏ではビューを作成する際に指定したSELECTコマンドが実行されているということです。

例えば、order_テーブルとorder_detailテーブルにデータを追加してもSELECT * FROM order_list;を実行すれば、追加したデータを含めた結果が返されます。

ビュー定義を確認する

ビューを作成する際に使用したSQLを確認するにはpg_viewsというテーブルを利用します。
「pg_」で始まるテーブルはPostgreSQLが使用しているテーブルでデータベースに関する様々な情報を参照することができます。このようなシステムが利用するテーブルはもちろん、DBMSが変わると別の名前になり、格納されている情報も変わります。

以下のSQLを実行してください。ビューの定義に利用したSQLが表示されます。

SELECT definition FROM pg_views WHERE viewname = 'order_list';

ビューを削除する

ビューを削除するにはDROP VIEWコマンドを実行します。
適当なビューを作り、削除してみましょう。

CREATE VIEW hello AS SELECT * FROM book;
\d
DROP VIEW hello;
\d

SQLのパフォーマンスを分析する

ここまでで様々なSQLを実行してきました。今まではパフォーマンスについてあまり意識してきませんでしたが、データベースを実際に運用する際にはパフォーマンスが非常に重要になります。そこで、ここではパフォーマンスについて学習します。

SQLを入力し、Enterキーを押すとそれがDBMSに送信され、指定された条件を満たす行が取り出されます。
さて、DBMSが指定された条件の行を探すにはどうすればいいでしょうか。
単純に考えると、全ての行を1行ずつ取り出し、条件を満たすか確認する方法が考えられます。

100万行のデータが登録されており、条件として主キーが指定された場合を考えましょう。この場合、条件を満たす1行以外を調べるのは無駄です。(主キーなので、同じ値の行はありえません)

主キー以外の場合、例えば、100万行から著者が「田中 二郎」さんの書籍を探す場合を考えましょう。
この場合は1行「田中 二郎」さんが見つかったからといってそこで打ち切ってはいけません。「田中 二郎」さんは複数本を書いている可能性があるからです。

全てのデータを確認するのは負荷がかかるので、インデックスを作成し、インデックス → テーブルと検索することで効率的に検索が行えることは既にお話ししました。インデックスを使うことで著者が「田中 二郎」さんの行がテーブルのどこにあるか調べ、その行だけ取り出すことが可能になります。

ところで本当に常にインデックスを使う方が効率がいいのでしょうか。

例えば、テーブルに数百行程度しか登録されていない場合、インデックス→テーブルと読み込みを行うと逆に手間がかかってしまいます。
数百行程度のデータなら一度のI/Oでディスクからメモリに読み込めるため、インデックスを使わずにテーブルを全部読み込んで調べた方が速い可能性があります。(インデックスを使うとインデックスを読み込む分の余計なI/Oがかかります。通常、メモリアクセスに対してディスクアクセスは非常に低速です)

実行計画を確認する

実行計画は標準SQLで定められているものではないため、実行計画を得る方法もDBMSにより異なります。
PostgreSQLではEXPLAINコマンドを実行することで実行計画を表示することができます。

実際に実行計画を見てみましょう。以下のコマンドを入力してください。

EXPLAIN SELECT * FROM book WHERE id = 1234;

実行すると以下のような結果が表示されます。

                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on book  (cost=0.00..1.10 rows=1 width=48)
   Filter: (id = 1234)
(2 行)

Seq Scanはテーブルのデータを一行一行調べるという意味です。これをシーケンシャルスキャンと言います。(「テーブルスキャン」とか「全スキャン」と言われることもあります)
costは右側の数値がこの方法で検索を行った場合のコスト、 rowsは出力される行です。値はいずれも推定値です。

idはPRIMARY KEYなのでインデックスが設定されています。しかし、bookテーブルには8行しかデータがないため、インデックスを使わずにテーブルを全部調べた方が速いという判断がされたようです。

どの程度データが登録されているとインデックスを使う方が速いと判断されるかは場合によりますが、試しにbookテーブルに700行程度登録したらインデックスが使われるようになりました。

a09001=# EXPLAIN SELECT * FROM book WHERE id = 1234;
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using book_pkey on book  (cost=0.00..8.27 rows=1 width=26)
   Index Cond: (id = 1234)
(2 行)

Index Scanはまずインデックスからbookテーブル中のidが1234である行の位置情報を読み取り、次にその位置情報を用いてbookテーブルの行を読み込むという意味です。costはデータ量が少ない場合に比べて大きくなっていますが、テーブル全行を読み込むよりは速いと判断され、インデックスが使用されます。

インデックスが使われる場合、使われない場合の確認

ここでは、テスト用にデータ量を増やしたテーブルでの出力結果をお見せします。データ量が少ない場合、Index Scanではなく、Seq Scanと表示される可能性があります。

書名(title)で検索を行った場合の実行計画を確認します。title列にはインデックスを設定しているのでIndex Scanが行われます。

a09001=# EXPLAIN SELECT * FROM book WHERE title = 'はじめてのデータベース';
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using book_title on book  (cost=0.00..8.27 rows=1 width=26)
   Index Cond: ((title)::text = 'はじめてのデータベース'::text)
(2 行)

著者(author)列にはインデックスが設定してありません。この場合、データ量が多くてもSeq Scanが行われ、実行時間がかかります。(titleで検索した場合に比べて、costが大きくなっています)

a09001=# EXPLAIN SELECT * FROM book WHERE author = '明石 太郎';
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on book  (cost=0.00..14.85 rows=3 width=26)
   Filter: ((author)::text = '明石 太郎'::text)
(2 行)

title列を検索条件とする場合でも必ずインデックスが使われるわけではありません。
通常、LIKEを用いて、指定した単語を含む行、という検索を行うとインデックスが使われません。

a09001=# EXPLAIN SELECT * FROM book WHERE title LIKE '%データベース%';
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on book  (cost=0.00..14.85 rows=1 width=26)
   Filter: ((title)::text ~~ '%データベース%'::text)
(2 行)

結合に対する実行計画の確認

JOINを用いて複数のテーブルを結合する場合の実行計画も取得することが可能です。

以下のコマンドを入力してください。SELECTコマンドはorder_detailテーブルとbookテーブルを結合して注文に対する書籍情報を表示するというものです。

a09001=# EXPLAIN SELECT d.order_id, d.book_id, b.title, b.price, d.amount
FROM order_detail d JOIN book b ON d.book_id = b.id;
                               QUERY PLAN
-------------------------------------------------------------------------
 Hash Join  (cost=1.18..38.63 rows=78 width=37)
   Hash Cond: (d.book_id = b.id)
   ->  Seq Scan on order_detail d  (cost=0.00..29.40 rows=1940 width=12)
   ->  Hash  (cost=1.08..1.08 rows=8 width=29)
         ->  Seq Scan on book b  (cost=0.00..1.08 rows=8 width=29)
(5 行)

実行計画の見方としては、bookテーブルをシーケンシャルスキャンし、ハッシュテーブルという作業用の領域に格納、次にorder_detailテーブルをシーケンシャルスキャンし、order_detailテーブルのbook_id列に対応する書籍情報をハッシュテーブルから取り出し、結合を行っています。

結合は実行方法により非常に時間がかかってしまいます。
例えば、100万行の注文詳細と1万行の書籍情報を結合する場合、単純にやろうとすると注文詳細の各行について毎回1万行の書籍テーブルをチェックすることになります。この場合、比較回数は100億回になります。インデックスを使う場合でもテーブルをシーケンシャルスキャンするよりは速くなりますが毎回インデックスを読み込むとI/O負荷がかかります。
それよりもメモリ上に一時的にハッシュテーブルを作成するのが一番速いと判断されるとこのような実行計画になります。

トランザクションの基礎知識

今までは1度のSQLコマンドで1行を操作してきました。しかし、実際にデータベースを用いたアプリケーションを作る場合、関連する複数の行を合わせて操作するということがあります。この際に利用されるのがトランザクションという機能です。

例として「Aさんの銀行口座からBさんの銀行口座に振り込みを行う」という処理を考えます。Aさんの口座から預金額を減らした後、Bさんの口座の預金額を増やす操作を行います。

81101

Aさんの預金額を10万減らしたところで停電が発生してデータベースを動かしているコンピュータが落ちてしまうとどうなるでしょうか。答えは、Aさんの預金額だけが減った状態になってしまいます。

81102

このような場合、

(1) Aさんの口座の預金額を減らす
(2) Bさんの口座の預金額を増やす

の2つの操作は分けることのできない一連の処理になります。この処理単位のことをトランザクションと言います。

2つの操作が無事成功した場合のみ、データベースの変更を確定することができます。この変更内容を確定することをコミットと言います。

一方、停電、あるいは処理中に何らかのエラーが発生した場合(例えば、Bさんの口座がない)などは一部の変更、Aさんの預金データを減らした操作をなかったことにする必要があります。これをロールバックと言います。

排他制御

トランザクションは自動的に排他制御を行います。
複数の人が同時にひとつのデータを更新することを考えます。例として在庫管理を考えます。
Aさんが在庫を更新し、次に、Bさんが在庫を更新しています。この場合は問題ありません。

82101

AさんとBさんがほぼ同時に処理を開始した場合を考えましょう。

82102

Bさんが在庫数を取り出した時点ではまだ10あったので、そこから3減らした在庫数7を書き込んでいます。すると、実際には2減らし、3減らしたので在庫数は5のはずなのにデータベース上は7あるというおかしな状態(不整合な状態)になってしまいます。

これを解決するためには、Aさんが在庫数の更新処理を開始したら、それが完了するまで他の人は処理を待たされる、ようにする必要があります。このように不整合が起こらないようにデータへのアクセスを制限する機能を排他制御と言います。

排他制御を利用する場合、在庫更新処理は以下のようになります。(結果として、最初に示したAさんが処理を終えてしばらくしてからBさんが処理を行うという時系列になります)

82103

ロックとアンロック

排他制御では、あるデータを他から操作させないようにすることをロックする(lock)と言います。
ロックしたデータは用が済んだら必ずアンロックする(unlock)必要があります。しかし、これもトランザクションが自動でやってくれるので概念だけを覚えましょう。

すでに誰かがロックしているデータを別の人がロックしようとすると、後からロックしようとした人は待たされることになります。ロックした人がアンロックを行うと、ロックが解除されるのを待っていた人がロックを獲得し、処理が進められるようになります。

以上がロックの基本ですが、システムによっては共有ロックと占有ロックに分かれていることがあります。
占有ロックは今までお話ししてきたロックです。占有ロックがかけられると他の人は占有ロックも共有ロックもかけられなくなります。(待たされます)

一方、共有ロックはデータの参照は許可するロックです。
あるデータを参照するが、参照中にデータを書きかえられたくないという場合があります。その場合に共有ロックをかけると、他の人が更新しようとして占有ロックをかけた場合、占有ロックをかけようとした人が待たされます。それに対し、他の人が共有ロックをかけた場合は待たされることはありません。

デッドロック

ロックを行う際に注意が必要なのがデッドロックです。
デッドロックは複数のデータを、それぞれが異なる順序でロックした場合、互いにロックの解除待ちをすることになり、結果、どちらも動くことができなくなってしまうことを言います。

具体的な例を見てみましょう。
Aさんはデータ1を先にロックし、次にデータ2をロックします。
Bさんはデータ2を先にロックし、次にデータ1をロックします。

この場合、タイミングによってはAさんはデータ2のロックが解除されるのを待ち、Bさんはデータ1のロックが解除されるのを待つことになってしまいます。こうなると、AさんBさんどちらも動けなくなってしまいます。

82104

デッドロックを回避するには非常に有効な方法があります。AさんBさんともに同じ順番でロックを行えばいいのです。この場合、BさんはAさんが両方のロックを解除するまで待つことになります。

82105

トランザクションを使用する

それではトランザクションを実現するSQLを見ていきましょう。
トランザクションでは、BEGINCOMMITROLLBACKの3つのコマンドを使用します。

  • BEGIN:トランザクションを開始する。
  • COMMIT:トランザクションを終了する。変更をデータベースに反映する。
  • ROLLBACK:トランザクションを終了する。変更は破棄する。

実際に使用してみましょう。まず、トランザクションのロールバックを試してみます。

BEGIN;
UPDATE book SET price = 2200 WHERE id = 1234;

SELECTコマンドを実行してテーブルの内容を見てみましょう。

SELECT * FROM book WHERE id = 1234;

priceが2200に変更されていますが、実はまだこの変更はデータベースには書き込まれていません。

ROLLBACKコマンドを実行し、もう一度SELECTコマンドを実行してみましょう。

ROLLBACK;
SELECT * FROM book WHERE id = 1234;

ロールバックしたため、price列の値が元の2100に戻りました。
次にコミットを試してみましょう。

BEGIN;
UPDATE book SET price = 2200 WHERE id = 1234;
COMMIT;
SELECT * FROM book WHERE id = 1234;

COMMITを行ったため、price列を2200にする変更が永続化されました。

ロックを確認する

これまでに同じデータを更新する際の問題とその対応策としての排他制御、ロックについて学習しました。
DBMSは複数人から利用されることを考慮し、データへのアクセスが行われると自動的にロックが行われるようになっています。

具体的に見てみましょう。まずは2つのターミナル(端末)を起動してください。
ターミナルの片方でトランザクションを開始し、UPDATEまで実行してください。

BEGIN;
UPDATE book SET price=2400 WHERE id=1234;

次にもうひとつの端末で同じ行に対してUPDATEコマンドを実行してください。(BEGINは必要ありません)

UPDATE book SET price=2500 WHERE id=1234;

すると、「UPDATE 1」と表示されずに止まります。

初めにUPDATEコマンドを実行した端末でCOMMIT;を実行してください。もう片方の端末に「UPDATE 1」と表示されます。また、price列の値は最終的に後で実行されたUPDATEコマンドで指定した値になります。

これがロックの動作です。今体験したように、ある行を更新しようとした場合に誰かがすでに更新中だったら更新が完了するまでUPDATEの実行が待機状態になります。

デッドロックを確認する

続いて、デッドロックが発生することを確認しましょう。デッドロックは複数のデータを、それぞれが異なる順序でロックした場合に発生します。データベースで言うと例えば、2つのトランザクションが2つの行を互い違いに更新する際に発生します。

今回も2つのターミナル(端末)を使用します。2つの端末で以下の順番でSQLを実行してください。

端末A. BEGIN;
端末A. UPDATE book SET price = 2200 WHERE id = 3456;
端末B. BEGIN;
端末B. UPDATE book SET price = 3400 WHERE id = 4444;
端末A. UPDATE book SET price = 3800 WHERE id = 4444; → ロックする
端末B. UPDATE book SET price = 1800 WHERE id = 3456; → ロックする

これで両方のトランザクションが互いにロックをかけた状態になり、デッドロックが発生しました。これは互いに相手がコミットするのを待っている状態ということです。

端末BでUPDATEコマンドを実行すると数秒後にエラーになり、一方端末AではUPDATEの待機状態が解除されます。実はPostgreSQLなどのDBMSにはデッドロックを検出する機能があり、デッドロックを検出すると片方のトランザクションをキャンセルします。

端末A、BでROLLBACK;を実行し、それぞれトランザクションをキャンセルしてください。

参考資料
スクールの教科書
SQL—分かりにくい JOIN / INNER JOIN / OUTER JOIN / LEFT JOIN / RIGHT JOIN の違い - Qiita