毎度毎度、旧サイトからの転載で申し訳ないんですが……。
ちょうど今日客先でWebアプリ構築の仕事をやっていて、単純なSQL文すら忘れていることに愕然としたんですよ。
最近、PostgreSQLを全然触ってなかったからなぁ……。
こんな程度を忘れるのはさすがに自分でも酷いと思うけど。
結局、客先でWayBack Machineから自分の旧サイトをひっぱり出して、これを調べる始末。
なので次に同じことが起きたときのために、今のうちにこっちに移しておきます。
というわけで、以下は旧サイトからの転載です。
データベースを作る
シェル上で createdb コマンドを実行。
% createdb Amethyst # "Amethyst"はデータベース名
CREATE DATABASE # レスポンス
これでデータベース作成完了。
レンタルサーバーなんかだと、最初から自分のアカウント名のデータベースを用意してくれてることが多いみたい。
そういうところは大抵自分で新たにデータベースを作成することはできないと思う。
そんな場合はデータベース作成は飛ばして次へ。
psqlの起動
シェル上で psql コマンドを実行する。引数としてデータベース名を指定。
この際、引数を指定しない場合は自動的に自分のアカウント名が引数にセットされる。
% psql Amethyst
# 以下、レスポンス
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
# ここまで
Amethyst=>
psqlを起動すると、プロンプトは「データベース名=>」になる。
この際、設定によってはユーザー名やパスワードを要求されることもある。
psql起動中は、SQL文の他に「internal slash commands」(個人的に「\コマンド」と呼んでる)とやらが使える。
個人的によく使うのは以下。
- \q psqlの終了
- \d データベース内のテーブルを一覧表示
- \h SQL文のヘルプを表示
- \? \コマンドの一覧表示
テーブルの作成
テーブルの作成は
「create table テーブル名 ( フィールド(列)名 データ型 オプション , フィールド名 データ型 オプション , ……);」
というSQL文で行う。
create table に限らず全てのSQL文のラストには、セミコロン「;」をつけなければならない。
逆に言うと、セミコロンをつけるまで、一つのSQL文を何行にも渡って記述することも可能。
というか、そうしないとやってられない。
ID,Name,Password,Email という四つのフィールド持つ UserList というテーブルを作るには下記のようにする。
Amethyst=> create table UserList (
# 以下、括弧内の部分を記述しているということが一目でわかるよう
# プロンプトが「(>」に変わるところが便利
Amethyst(> ID text PRIMARY KEY,
Amethyst(> Name text NOT NULL,
Amethyst(> Password text NOT NULL,
Amethyst(> Email text
Amethyst(> );
# 以下、レスポンス
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'userlist_pkey' for table 'userlist'
CREATE
フィールド「ID」に「PRIMARY KEY」のオプションをつけたので、自動的に「userlist_pkey」というindexが作成される。
indexが何なのか理屈はよく知らないけど、とりあえずこれでIDをキーに検索するときは効率が良くなるらしい。
データ型
個人的によく使う、フィールドのデータ型は下記。
- numeric
- バイト数可変の1000桁までの正数と小数。
数値を格納するフィールドはよっぽど特殊な場合以外はこれでOKだと思う。
- text
- バイト数可変の文字列。
上に同じく文字列を格納するフィールドはよっぽど特殊な場合以外はこれでOKだと思う。
- timestamp
- 日付・時刻。
入力・取り出しは「2003-04-20 17:05:06」の形式が使いやすいと思う。
- inet
- IPアドレス。
ネットワーク系の処理のために使うのでなければtext型でも充分だけど、せっかく専用のデータ型が用意されてるので使ってるだけ……。
オプション
個人的によく使う、フィールドのオプション(という呼び方は正しくないような気もするけど)は下記。
- PRIMARY KEY
- ひとつのテーブルにつきひとつのフィールドにしか指定できない。
indexが作成されるので、このフィールドをキーにした検索は効率が良いらしい。
フィールドが一意で、かつ、NULLではないという条件がある。
- UNIQUE
- ひとつのテーブル内の複数のフィールドに指定できること以外は PRIMARY KEY と基本的に同じだと思う。
PRIMARY KEY に指定したフィールドは、自動的に UNIQUE も指定されている。
- NOT NULL
- NULL値を認めないフィールドに指定。
PRIMARY KEY や UNIQUE に指定したフィールドは、自動的に NOT NULL も指定されている。
- DEFAULT value
- 新たな行が追加された時に、自動的に挿入されるデフォルト値(value)を指定できる。
フィールドの追加
既存のテーブルにフィールドを追加する時は
「alter table テーブル名 add フィールド名 データ型 オプション;」
というSQL文で行う。
先程作ったテーブル UserList に BirthDay というフィールドを追加する場合は下記のようにする。
Amethyst=> alter table UserList add BirthDay timestamp;
ALTER TABLE # レスポンス
numeric型のフィールドを追加する時は、デフォルト値として 0 をセットしといた方が良いと思う。
そうしないと、フィールドを追加する前に存在した行の該当フィールドの値は全てNULLになるため後々不便(0 と NULL は別物なので)。
perlからデータベースを使う場合は特に。
既存フィールドのデフォルト値の設定
既存のフィールドの DEFAULT value の設定・変更は
「alter table テーブル名 alter フィールド名 set default デフォルト値;」
というSQL文で行う。
先程作ったテーブル UserList の Email フィールドにデフォルト値 anonymous を設定する場合は下記のようにする。
Amethyst=> alter table UserList alter Email set default 'anonymous';
ALTER TABLE # レスポンス
なお、デフォルト値の設定を破棄する場合は「set default デフォルト値」の代わりに「drop default」を使う。
Amethyst=> alter table UserList alter Email drop default;
ALTER TABLE # レスポンス
2003.04.26 追記
データの挿入
テーブルにデータを挿入するときは
「insert into テーブル名 (フィールド名1,フィールド名2,……) values (値1,値2,……);」
というSQL文で行う。
先程作ったテーブル UserList にデータを挿入する場合は下記のようにする。
Amethyst=> insert into UserList(
Amethyst(> ID,Name,Password,Email,BirthDay)
Amethyst-> values ( # SQL文の途中、かつ括弧内でない場合のプロンプトは「->」になる
Amethyst(> '00001',
Amethyst(> 'Amethyst',
Amethyst(> '01234567',
Amethyst(> 'amethyst@amethyst-web.org',
Amethyst(> '1976-05-06'
Amethyst(> );
INSERT 2910628 1 # レスポンス
値を入力するとき、数値以外はシングルクォーテーション「'」で囲むこと。
データの表示
テーブル内のデータを表示するときは
「select フィールド名1,フィールド名2,…… from テーブル名 条件 (ソート方法);」
というSQL文で行う。
先程作ったテーブル UserList から、「Emailが空でないデータのみ、NameとEmailを表示」する場合は下記のようにする。
Amethyst=> select Name,Email from UserList where Email != '';
# 以下、レスポンス
name | email
----------+---------------------------
Amethyst | amethyst@amethyst-web.org
(1 row) # マッチした件数(行数)が表示される
全てのフィールドを表示したい時は、フィールド名をアスタリスク「*」一文字にするだけでOK。
「select * from テーブル名 条件 (ソート方法)」
where句以下の条件を指定しなければ、テーブル内のデータ全てが表示される。
特定のフィールドの値を元に昇順にソートして表示したい場合は、select文末尾に「order by フィールド名」
降順にソートして表示したい場合は「order by フィールド名 desc」
データの検索
前項の例のように、データの検索には
「where 条件」
という句をSQL文に追加して実行する。
単純にフィールドが条件と合致しているものを検索するには「where フィールド名 = 条件」。
複数の条件で検索するには「where フィールド名 in(条件1,条件2,……)」
逆に条件と合致していないものを検索するには「where フィールド名 != 条件」。
値の一部が合致しているものを検索するには「where フィールド名 like 条件」。
「%」が「0文字以上の何か」を表すので、「Aから始まる文字列」の検索は「where フィールド名 like 'A%'」という感じ。
numeric型やtimestamp型では「> < <= >=」もよく使う。
and や or を使って、複数の条件を指定するすることも可能。
別のテーブルの検索結果を条件に指定することも可能。
「where フィールド名 in (select文)」
データの更新
既存のデータの値を更新する時は
「update テーブル名 set フィールド名1=値1,フィールド名2=値2,…… 条件(where句)」
というSQL文で行う。
先程挿入したデータのPassword「01234567」を「abcdefgh」に変更する場合は下記のようにする。
Amethyst=> update UserList set Password='abcdefgh' where ID = '00001';
UPDATE 1 # レスポンス
where句以下の条件を指定せずに実行すると、テーブル内のデータ全てが更新されるので一括更新がしたいときは楽。
ただし、一括更新するつもりがない時はwhere句の指定を忘れないよう注意が必要。
データの削除
既存のテーブルからデータを削除する時は
「delete from テーブル名 条件(where句)」
というSQL文で行う。
先程挿入したデータを削除する場合は下記のようにする。
Amethyst=> delete from UserList where ID = '00001';
DELETE 1 # レスポンス
where句以下の条件を指定せずに実行するとテーブル内のデータは綺麗さっぱりなくなってしまうので要注意。
テーブルの削除
既存のテーブルそのものを削除する時は
「drop table テーブル名」
というSQL文で行う。
先程作成したテーブル UserList を削除する場合は下記のようにする。
Amethyst=> drop table UserList;
DROP TABLE # レスポンス
前項のdelete文で全てのデータを削除してもテーブル自体は残っているので、テーブルそのものを削除する時はこちらを使う。
データベースのバックアップ
データベースのバックアップを取るときは、シェル上で pg_dump コマンドを実行。
% pg_dump Amethyst > AmethystDB.bak
# "Amethyst"はデータベース名。"AmethystDB.bak"はバックアップファイル名
これで"AmethystDB.bak"という名前のバックアップファイルが作成される。
中身はSQL文とかが沢山書かれたテキストファイル。
バックアップファイルをもとにデータベースを再作成するときは psql コマンドに -e オプションをつけて実行する。
% psql -e Amethyst < AmethystDB.bak
# "Amethyst"はデータベース名。"AmethystDB.bak"はバックアップファイル名
つぶやく
