PostgreSQLの初歩

2007年8月 7日 23:59 PostgreSQLの初歩

毎度毎度、旧サイトからの転載で申し訳ないんですが……。
ちょうど今日客先で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"はバックアップファイル名
阿部辰也へのお仕事のご依頼・お問合せはこちら

Twitter始めました。Followはお気軽にどうぞ。

関連するブログ記事
スポンサード リンク
カテゴリー
PostgreSQL
タグ
PostgreSQL | RDBMS | SQL | コマンドライン | シェル
現在位置
TOP > Web制作技術 > PostgreSQL > PostgreSQLの初歩
前のブログ記事
.htaccessによるBasic認証のかけ方 [2007年8月 6日 23:59]
次のブログ記事
携帯端末判別法(i-mode,J-SKY,EZweb) [2007年8月 8日 23:59]

トラックバック(0)

このブログ記事に対するトラックバックURL:

コメント(7)

viagra [2014年4月 9日 11:05]

ecunmzd http://buyviagraffd.com/ viagra

hello [2014年6月21日 21:22]
Oakley Oil Rig Sunglasses Cheap [2014年9月 6日 11:29]

http://www.montedentistry.com/?3.phpOakley Commit Sq Sunglasses Cheap

loans with bad credit [2014年11月12日 17:33]

peqlbbwh http://paydayloansbrg.com/ loans with bad credit

payday loan [2015年5月16日 00:07]

samhii http://paydayloansrnr.com/ payday loan

payday loans [2015年5月28日 04:58]

keaeqov http://paydayloanssqd.com/ payday loans

idayai [2017年1月28日 04:41]

The rout demonstration indicates that high blood albatross does not call headaches or nosebleeds, except in the box of hypertensive catastrophe, a medical predicament when blood difficulties is 180/110 mm Hg or higher. If your blood burden is unusually elevated viagra without a doctor prescription you maintain headache or nosebleed and are inkling unwell, on the back burner serve five minutes and retest. If your reading remains at 180/110 mm Hg or higher, come for 9-1-1.

コメントする


画像の中に見える文字を入力してください。

 

このブログについて

阿部辰也

Webコンサルタントやシステムエンジニア、執筆業などをやっている、グッドホープ代表・阿部辰也のブログです。
Web技術系のTipsから仕事の話、趣味の話まで色々と。
人生は所詮生まれてから死ぬまでの壮大な暇つぶし。
だったら有意義に暇をつぶして生きましょー。

阿部辰也へのお仕事のご依頼・問合せはこちらからどうぞ。

Twitter: @t_abe
読書メーター: ID:Tatsuya

このブログのサイトマップ
このブログのはてなブックマーク数

Feed/RSS購読

タグクラウド

最近のトラックバック

阿部辰也の著書

77のキーワードで知る インターネットで稼ぐ 図解 ブログ・メルマガ・Webサイト構築の基本

77のキーワードで知る インターネットで稼ぐ 図解 ブログ・メルマガ・Webサイト構築の基本

頑張って書いたのに、出版後すぐに出版社が倒産して印税も貰えずじまいで俺涙目(笑)。

Powered by Movable Type 5.02

スポンサード リンク

はてなブックマーク



あわせて読みたい

t-abeの今読んでる本

t-abeの最近読んだ本

t-abeの今やってるゲーム

t-abeの最近やったゲーム