PostgreSQL

PostgreSQL
http://www.ns.kogakuin.ac.jp/~ct13140/Prog.2009/
PostgreSQL-1
PostgreSQL
• RDBMSの実装の1個．
• オープンソースである．
– C言語で記述され，ソースコードが公開されている．
よって，中身を確認することができる．一般ユーザが
改造できる．
無料である．
現在，無料のRDBMSで最もユーザ数が多い．
• 公式ページ
http://www.postgresql.org/
日本PostgreSQLユーザ会
http://www.postgresql.jp/
PostgreSQL-2
表とデータベースと
データベースクラスタ
• 複数の「列」が集まったものが「表」．
– 実際は，列の数が0でもよい．空でもよい．
• 複数の「表」が集まったものが「データベー
ス」
– 実際は，表の数が0でもよい．空でもよい．
• 複数のデータベースが集まったものが
「データベースクラスタ」
– 実際は，データベースの数が0でもよい．
PostgreSQL-3
データベースクラスタ
• データベースクラスタの中にデータベース
を格納する．
• これは，PostgreSQL管理者が管理する
ものなので，本講義では扱わない．
PostgreSQL-4
データベース
• データベースの中に複数の表を格納でき
る．
• データベースには「データベース名」がある．
• 各自，自分用のデータベースを作成しその
中に表を作成することとする．
• データベース名は「学籍番号db」とする．
– 例：学籍番号がct13140なら，
「ct13140db」
PostgreSQL-5
表
• 各自，自分用のデータベースの中に表を
自由に作成してよい．
• 表には，「表名」がある．
「データベースクラスタ」の中に「データベース」が，
「データベース」の中に「表」がある．
PostgreSQL-6
全体の流れ
• PuTTYでgreen.ns.kogakuin.ac.jp
にloginする．
• 自分用データベースを作成する．
– 通常，これは初回に1回だけ行う．
• 自分用データベースに接続する．
• 自分用データベースの中に表を作成する．
• 表操作を行う．
• データベースとの接続を切る．
PostgreSQL-7
操作
• 以下，自分の学籍番号が「ct13140」であ
ると仮定して話を進める．
• 赤字がユーザの入力である．
• 以下のような表を作るとする．
id
name
email
0
fukuda
[email protected]
1
abe
[email protected]
↑
Integer型
↑
Varchar(100)型
↑
Varchar(100)型
PostgreSQL-8
復習 (SQL文) 0/6
• データベースの作成
– データベースにつないでいない状態で
createdb データベース名
– これはPostgreSQL特有のコマンド．
• データベースの削除
– データベースにつないでいない状態で
dropdb データベース名
– データベース内の表が全て消えてしまう．
– これはPostgreSQL特有のコマンド．
PostgreSQL-9
復習 (SQL文) 1/6
• データベースに接続する．
– データベースにつないでいない状態で
psql データベース名
– これはPostgreSQL特有のコマンド．
• データベースとの接続を切断する．
– データベースにつないでいる状態で
\q
– これは，PostgreSQL特有のコマンド．
PostgreSQL-10
復習 (SQL文) 2/6
• 存在する表の一覧を表示
– データベースにつないでいる状態で
\d
– これは，PostgreSQL特有のコマンド．
PostgreSQL-11
復習 (SQL文) 3/6
• 表の作成
– データベースにつないでいる状態で
CREATE TABLE...;
– これはRDBMS一般のコマンド．
• 表の削除
– データベースにつないでいる状態で
DROP TABLE テーブル名;
– 表内のデータが全て消えてしまう．
– これはRDBMS一般のコマンド．
PostgreSQL-12
復習 (SQL文) 4/6
• 表内のデータを得る
– データベースにつないでいる状態で
SELECT 列名 FROM 表名...;
– これはRDBMS一般のコマンド．
• 表にデータを挿入する．
– データベースにつないでいる状態で
INSERT INTO 表名 VALUES (...);
– これはRDBMS一般のコマンド．
PostgreSQL-13
復習 (SQL文) 5/6
• 表内のデータを変更する
– データベースにつないでいる状態で
UPDATE 表名 SET 列名=値 WHERE 条
件;
– これはRDBMS一般のコマンド．
• 表内の行を削除する
– データベースにつないでいる状態で
DELETE FROM 表名 WHERE 条件;
– これはRDBMS一般のコマンド．
PostgreSQL-14
復習 (型)
• Integer型
整数型．
• VARCHAR型
文字列型．
最大文字数を指定する．
VARCHAR(100)なら，最大100文字．
PostgreSQL-15
操作 0/22
• PuTTYでgreen.ns.kogakuin.ac.jpに
loginする．
– スタート → プログラム → Internet → PuTTY →
PuTTY でPuTTYを起動
– 「セッション一覧」中の「green」を選択し，「読込」をク
リックし，「開く」をクリック．
– 「セキュリティ警告」が表示されたら「はい」を押す．
– 「login as」に対してユーザ名を，「password」に
対してパスワードを入力．
• 注意：ここで使用するユーザ名とパスワードは，工学院大学
計算機(Windows)にlogonする時にしようするものです．
PostgreSQL-16
操作 1/22
• 自分用データベースを作成する．
/usr/local/pgsql/bin/createdb ct13140db
Password:
ここでPostgreSQL用
CREATE DATABASE
• 注意：ここで使用する
パスワードは，講義中に配布した
「PostgreSQL用」
のものです．
パスワードを打ち込み
「Enter」を押す．
画面には何も表示され
ないの注意．
このように
表示されれば成功．
全て大文字で無ければ失敗
PostgreSQL-17
操作 2/22
• 作成した自分用データベースに接続する．
/usr/local/pgsql/bin/psql ct13140db
Password: ←ここでパスワードを入力し「Enter」を押す
Welcome to psql 8.2.5, the PostgreSQL interactive
terminal.
Type:
\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute
query
\q to quit
ct13140db=> ←このように表されればデータベースに接続中．
PostgreSQL-18
操作 3/22
• データベースとの接続を切断する．
ct13140db=> \q
ct13140@green[103]:
表示が ct13140db=> でなくなれば，切断成功．
上の例では，表示が ct13140@green[103]: に代わっている．
PostgreSQL-19
操作 4/22
• 再度自分用データベースに接続する．
/usr/local/pgsql/bin/psql ct13140db
Password: ←ここでパスワードを入力し「Enter」を押す
Welcome to psql 8.2.5, the PostgreSQL interactive
(略)
ct13140db=> ←このように表されればデータベースに接続中．
PostgreSQL-20
操作 5/22
• 存在する表を調べる(データベース接続
中)．
ct13140db=> \d
No relations found.
ct13140db=>
このように
表示されれば成功.
「relation」とは
「表」のこと．
「表はない」との返
事が得られた．
PostgreSQL-21
操作 6/22
• 表usertblを作る(データベース接続中)．
ct13140db=> CREATE TABLE usertbl (
ct13140db(> id integer,
ct13140db(> name varchar(100),
ct13140db(> email varchar(100)
ct13140db(> );
CREATE TABLE
ct13140db=>
このように
表示されれば成功.
使用したSQL文は
CREATE TABLE usertbl (id integer, name varchar(100),
email varchar(100));
途中の改行はあってもなくてもよい．
最後の；(セミコロン)を忘れない様に．
PostgreSQL-22
操作 7/22
• 再度，存在する表を調べる(データベース
接続中)．
ct13140db=> \d
List of relations
Schema | Name
| Type | Owner
--------+---------+-------+--------public | usertbl | table | ct13140
(1 row)
ct13140db=>
「操作7」で作成し
た表が表示されれ
れば成功．
PostgreSQL-23
操作 8/22
• 表にSELECTを実行する(データベース接
続中)．
– 現在，表は空のはずである．
ct13140db=> SELECT * FROM usertbl;
id | name | email
----+------+------(0 rows)
ct13140db=>
この様に表示され
れれば成功．
PostgreSQL-24
操作 9/22
• 表にデータを入力する(データベース接続
中)．
ct13140db=> INSERT INTO usertbl VALUES (0,
'fukuda', '[email protected]');
INSERT 0 1
ct13140db=> SELECT * FROM usertbl;
これがデータ
id | name |
email
の挿入命令．
----+--------+--------------------表示は特に
0 | fukuda | [email protected]
変化なし．
(1 row)
ct13140db=>
SELECTを実行して，
表の中身を確認し
てみる．
途中改行は
あってもなく
てもよい．
PostgreSQL-25
操作 10/22
• 表にデータを入力する(データベース接続中)．
ct13140db=> INSERT INTO usertbl VALUES (1, 'abe',
'[email protected]');
INSERT 0 1
ct13140db=> INSERT INTO usertbl VALUES (2,
'koizumi', '[email protected]');
INSERT 0 1
ct13140db=> SELECT * FROM usertbl;
id | name
|
email
----+---------+---------------------0 | fukuda | [email protected]
1 | abe
| [email protected]
2 | koizumi | [email protected]
(3 rows)
ct13140db=>
PostgreSQL-26
操作 11/22
• 表diarytblを作る(データベース接続中).
ct13140db=> CREATE TABLE diarytbl (diaryid
Integer, userid Integer, year Integer, month
Integer, day Integer, txt varchar(1000));
CREATE TABLE
ct13140db=> \d
List of relations
Schema |
Name
| Type | Owner
--------+----------+-------+--------public | diarytbl | table | ct13140
public | usertbl | table | ct13140
(2 rows)
日記記事1個ごとに1行の表とする．
diaryid:日記記事の通し番号
ct13140db=>
Userid:日記を書いたユーザのID
year,month,day:日記の日付
txt:日記本文
PostgreSQL-27
操作 12/22
• 表diarytblにデータを挿入する(データベース接続中)．
ct13140db=> INSERT INTO diarytbl VALUES (0, 0, 2007, 1, 1,
'gantan deshita');
INSERT 0 1
ct13140db=> INSERT INTO diarytbl VALUES (1, 0, 2007, 7, 29,
'senkyo deshita');
INSERT 0 1
ct13140db=> INSERT INTO diarytbl VALUES (2, 2, 2005, 8, 8,
'kaisan shita');
INSERT 0 1
ct13140db=> SELECT * FROM diarytbl;
diaryid | userid | year | month | day |
txt
---------+--------+------+-------+-----+---------------0 |
0 | 2007 |
1 |
1 | gantan deshita
1 |
0 | 2007 |
7 | 29 | senkyo deshita
2 |
2 | 2005 |
8 |
8 | kaisan shita
(3 rows)
ct13140db=>
PostgreSQL-28
操作 13/22
• 現状の確認(データベース接続中).
ct13140db=> SELECT * FROM usertbl;
id | name
|
email
----+---------+---------------------0 | fukuda | [email protected]
1 | abe
| [email protected]
2 | koizumi | [email protected]
(3 rows)
ct13140db=> SELECT * FROM diarytbl;
diaryid | userid | year | month | day |
txt
---------+--------+------+-------+-----+---------------0 |
0 | 2007 |
1 |
1 | gantan deshita
1 |
0 | 2007 |
7 | 29 | senkyo deshita
2 |
2 | 2005 |
8 |
8 | kaisan shita
(3 rows)
ct13140db=>
PostgreSQL-29
操作 14/22
• 現状の確認(データベース接続中).
ct13140db=> SELECT * FROM diarytbl WHERE userid=0;
diaryid | userid | year | month | day |
txt
---------+--------+------+-------+-----+---------------0 |
0 | 2007 |
1 |
1 | gantan deshita
1 |
0 | 2007 |
7 | 29 | senkyo deshita
(2 rows)
ct13140db=>
PostgreSQL-30
操作 15/22
• 現状の確認(データベース接続中).
ct13140db=> UPDATE diarytbl SET txt='ganjitu deshita'
WHERE diaryid=0;
UPDATE 1
ct13140db=> SELECT * FROM diarytbl;
diaryid | userid | year | month | day |
txt
---------+--------+------+-------+-----+----------------1 |
0 | 2007 |
7 | 29 | senkyo deshita
2 |
2 | 2005 |
8 |
8 | kaisan shita
0 |
0 | 2007 |
1 |
1 | ganjitu deshita
(3 rows)
ct13140db=>
表内の列の順番は全く保証されていないので，
RDBMSが勝手に決めてくる．
PostgreSQL-31
操作 16/22
• 内部結合(データベース接続中).
ct13140db=> SELECT * FROM usertbl INNER JOIN diarytbl ON usertbl.id = diarytbl.userid;
id | name
|
email
| diaryid | userid | year | month | day |
txt
----+---------+----------------------+---------+--------+------+-------+-----+----------------0 | fukuda | [email protected] |
0 |
0 | 2007 |
1 |
1 | ganjitu deshita
0 | fukuda | [email protected] |
1 |
0 | 2007 |
7 | 29 | senkyo deshita
2 | koizumi | [email protected] |
2 |
2 | 2005 |
8 |
8 | kaisan shita
(3 rows)
ct13140db=>
PostgreSQL-32
操作 17/22
• 表内のデータの削除(データベース接続中).
ct13140db=> DELETE FROM diarytbl WHERE diaryid=0;
DELETE 1
ct13140db=> SELECT * FROM diarytbl;
diaryid | userid | year | month | day |
txt
---------+--------+------+-------+-----+---------------1 |
0 | 2007 |
7 | 29 | senkyo deshita
2 |
2 | 2005 |
8 |
8 | kaisan shita
(2 rows)
ct13140db=>
PostgreSQL-33
操作 18/22
• 表の削除(データベース接続中).
ct13140db=> DROP TABLE diarytbl;
DROP TABLE
ct13140db=> \d
List of relations
Schema | Name
| Type | Owner
--------+---------+-------+--------public | usertbl | table | ct13140
(1 row)
ct13140db=>
表を削除すると，
表内のデータは全て消失するので注意．
PostgreSQL-34
操作 19/22
• データベースとの接続を切断(データベース接続
中).
ct13140db=> \q
ct13140@green[106]:
PostgreSQL-35
操作 20/22
• データベースの削除.
ct13140@green[106]:/usr/local/pgsql/bin/dropdb ct13140db
Password: ←ここでパスワードを入力し「Enter」を押す
DROP DATABASE
ct13140@green[107]:
データベースを削除すると，
データベース内の全てのデータが失われるので注意！！
頻繁に行う作業ではありません．
PostgreSQL-36
操作 21/22
• 自分用データベースを再度作成する．
/usr/local/pgsql/bin/createdb ct13140db
Password: ←ここでパスワードを入力し「Enter」を押す
CREATE DATABASE
• 自分用データベースに再度接続．
/usr/local/pgsql/bin/psql ct13140db
Password: ←ここでパスワードを入力し「Enter」を押す
Welcome to psql 8.2.5, the PostgreSQL interactive
terminal.
(略)
ct13140db=> \d
No relations found.
ct13140db=>
空の(表が1個もない)データベースが
作成された．
PostgreSQL-37
注意事項
•コマンドラインでは
ct13140@green[102]:tcsh
と1回入力すると使いやすくなる．
BackSpaceが使える，矢印でカーソルを移
動できる，↑(上矢印)で前の入力が出てくる
など．
PostgreSQL-38
注意事項
• 成功時のメッセージは全て大文字のはずで
す．例えば「CREATE DATABASE」など．
• 小文字であったら失敗です．
PostgreSQL-39
注意事項
• RDBMSに接続中は，
ct13140db=>
と表示されるのが正常な状態です．
• ct13140db(>
ct13140db'>
などと表示されたのなら「コマンド入力途中
」という変な状態です．これを正常に戻す
には CTRL+Cを入力する(CTRLを押しな
がらCを押す)．
PostgreSQL-40
注意事項
• RDBMSに接続中は，
ct13140db=>
と表示されるのが正常な状態です．
• ct13140db(>
ct13140db'>
などと表示されたのなら「コマンド入力途中
」という変な状態です．これを正常に戻す
には CTRL+Cを入力する(CTRLを押しな
がらCを押す)．
PostgreSQL-41
注意事項
• createdbで作成したデータベースは，ユーザが
(dropdbで)消さない限り残り続けます．
– PuTTYの再接続やPCの再起動で消えるようなものではありま
せん．
• 既に存在するデータベースと同名のデータベースは作
成できません．
• createdbを何度やっても失敗する場合は，その名前
のデータベースが既に存在しているのかもしれません．
確認のためにそのデータベースへの接続を行ってみま
しょう．接続コマンドはP18(操作 2/22)を参照．
PostgreSQL-42
注意事項
• PuTTYのlogin時や，PostgreSQLコマ
ンド実行時にパスワードを尋ねられるので
入力する必要があるが，
このときは何を入力しても画面には表示さ
れません．
• 表示されないだけで入力は行われていま
す．正しく打ち込んでEnterを押してくださ
い．
PostgreSQL-43