ここでは、本を読んでも出ていない、Web を見ても簡単には見つけ出せなかった、初心者用の Tips を集めていく予定です。
簡単なテーブルを作って SQL 言語の練習をしてみましょう。まず、データベースを作ります。
$ createdb mydb
次に psql を起動して mydb を操作できるようにします。
$ psql mydb Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: mydb mydb=>
テーブルの作成は create table 文を使います。
mydb=> create table table_1 ( mydb-> id int4, mydb-> name text); CREATE
テーブルにデータを挿入するには insert 文を使います。
mydb=> insert into table_1 values (3, 'Joe'); INSERT 381578 1
フィールドを指定して挿入することもできます。
mydb=> insert into table_1 (id, name) values (1, 'Ken'); INSERT 381579 1
フィールドの順番を変えて入力することもできます。
mydb=> insert into table_1 (name, id) values ('Mary', 2);
INSERT 381580 1
テーブルの中身を見るときは select 文を使います。
mydb=> select * from table_1; id|name --+---- 3|Joe 1|Ken 2|Mary (3 rows)
id の昇順で並べかえるには order by (asc) を使います。
mydb=> select * from table_1 order by id; id|name --+---- 1|Ken 2|Mary 3|Joe (3 rows)
降順で並べかえるには order by desc を使います。
mydb=> select * from table_1 order by id desc; id|name --+---- 3|Joe 2|Mary 1|Ken (3 rows)
次のようにすればカラムのラベルを変えることができます。
mydb=> select id, name as nickname from table_1; id|nickname --+-------- 3|Joe 1|Ken 2|Mary (3 rows)
カラムに計算式を入れることもできます。
mydb=> select id, id * id as square, name from table_1; id|square|name --+------+---- 3| 9|Joe 1| 1|Ken 2| 4|Mary (3 rows)
条件付きの検索をするときは where をつかいます。
mydb=> select * from table_1 where id <= 2; id|name --+---- 1|Ken 2|Mary (2 rows)
条件の値を複数個設定することができます。
mydb=> select * from table_1 where id in (1, 3); id|name --+---- 3|Joe 1|Ken (2 rows)
カラムのデータの一部しか憶えていないときは like 検索を使います。
mydb=> select * from table_1 where name like '%Ma%'; id|name --+---- 2|Mary (1 row)
検索結果の数を数えることもできます。
mydb=> select count(name) from table_1 where id <= 2;
count
-----
2
カラムのデータの合計を計算することもできます。
mydb=> select sum(id) from table_1 where id <= 2; sum --- 3 (1 row)
検索結果を別の表にしたいときは select into を使います。
mydbo=> select * into table_2 from table_1 where id <= 2; SELECT mydb=> select * from table_2; id|name --+---- 1|Ken 2|Mary (2 rows)
table_2 の検索結果を利用して table_1 の検索をする(サブクエリー)こともできます。
mydb=> select * from table_1 where name in ( select name from table_2 where id = 2); id|name --+---- 2|Mary (1 row)
2つのテーブルをジョインするには from のあとで２つのテーブルの名前を ,(コンマ)で区切って並べます。
mydb=> select * from table_1, table_2; id|name|id|name --+----+--+---- 3|Joe | 1|Ken 1|Ken | 1|Ken 2|Mary| 1|Ken 3|Joe | 2|Mary 1|Ken | 2|Mary 2|Mary| 2|Mary (6 rows)
次のような重複したデータが現れて欲しくないときは、
mydb=> select table_1.id, table_1.name from table_1, table_2; id|name --+---- 3|Joe 1|Ken 2|Mary 3|Joe 1|Ken 2|Mary (6 rows)
select のあとに distinct を置きます。
mydb=> select distinct table_1.id, table_1.name from table_1, table_2; id|name --+---- 1|Ken 2|Mary 3|Joe (3 rows)
データの更新は update で行ないます。
mydb=> update table_2 set id = 0 where name='Ken'; UPDATE 1 mydb=> select * from table_2; id|name --+---- 2|Mary 0|Ken (2 rows)
where 以下の条件を省略するとカラムの値を一度に変更できます。
mydb=> update table_2 set id = 0; UPDATE 2 mydb=> select * from table_2; id|name --+---- 0|Mary 0|Ken (2 rows)
次のようにすると table_2 の id の値を table_1 の id と name の対応と同じものにすることができます。
mydb=> update W2 set id = W1.id from table_1 W1, table_2 W2 where W1.name=W2.name; UPDATE 2 mydb=> select * from table_2; id|name --+---- 1|Ken 2|Mary (2 rows)
上の W1 とか W2 とかは、テーブル名のエイリアスです。from 以下でテーブル名のあとにスペースで区切って置いておくと、テーブル名の代わりに使うことができます。別に、W1 でなくても自由な名前を使えます。
行を削除するには delete を使います。
mydb=> delete from table_2 where name='Ken'; DELETE 1 mydb=> select * from table_2; id|name --+---- 2|Mary (1 row)
テーブルを削除するには drop table を使います。
mydb=> drop table table_2; DROP
データベースを削除するには psql を \q で終了した後 destroydb コマンドを使います。
mydb=> \q $ destroydb mydb
一般ユーザのデータベースのバックアップは pg_dump でできます。具体的には次のようにします。
$ pg_dump dbname > dbname.out
作成されたバックアップファイルはテキストファイルなので、less などで中身を覗くことができます。中身は、先頭に psql と SQL コマンドからなる数行のバッチファイルがあり、後半がテキスト形式のデータになっています。バックアップファイルからデータベースへデータを戻すには
$ psql -e dbname < dbname.out
を実行します。テーブルのデータは保存しないで、テーブルの構造だけを保存したいときは -s オプションを使います。例えば次のようにします。
$ psql -s mydb > mydb.out
出来上がったバックアップファイル mydb.out の中身は次のようになっています。
\connect - mydb CREATE TABLE table_1 (id int4, name text); \connect - mydb CREATE TABLE table_2 (id int4, name text); \connect - mydb CREATE FUNCTION one ( ) RETURNS int4 AS 'select 1 as result' LANGUAGE 'SQL'; \connect - mydb
中身は psqlのコマンド と SQLの文になっています。テーブルの構造だけでなく、ユーザー定義関数もバックアップできるようです。これを次のように psql にリダイレクトすると SQL 文が実行されてテーブルが作成されます。
$ psql -e mydb < mydb.out
データベース全体(サイトと呼ばれます)をバックアップするには pg_dumpall を使います。詳しくは pg_dumpall の man ページを参照してください。
だれもデータをいちいち insert 文を使って入力したいとは思わないでしょう。次のように各カラムのデータをタブキーで区切って並べたテキストファイル data.txt (ファイル名は別に data でなくても構いません。) を作って、
101 Ken 102 Mary 103 Joe
次のように psql から \copy コマンドを発行してデータをテーブルに取り込みます。
$ psql mydb mydb=> \copy mytable from data.txt Successfully copied mydb=>
テーブルの内容をテキストファイルにしたいときは、
mydb=> \copy mytable to mytable.txt
psql を使っているときファイルのリストを取るために ls を使いたかったら \! コマンドを使います。
mydb=> \!ls
テーブル作成の SQL 文は psql で直接入力するより mytable.crt などと適当な名前をつけた SQL 文のテキストファイルをつくって、(例えば)
create table mytable (
id int4,
name text
);
つぎに、psql から \i コマンドで mytbl.crt を実行させる方が何かと便利です。これも次のように \! コマンドを活用すると psql から出ずに実行することができます。
mydb=> \!vi mytbl.crt (編集作業をする) mydb=> \i mytbl.crt
select 文で検索した結果の行数が多いと、最初の方のデーターは画面から消えて行ってしまいます。\o コマンドを使って出力先をファイルに変更すると、検索結果をファイルに書き込むことができます。
mydb=> \o output.txt mydb=> select * from mydb where id < 3; mydb=> \!less output.txt mydb=> \o
ファイルへの出力が終ったら \o とだけ入力して、出力先を標準出力へ戻しておきます。
検索の結果をファイルに書き込みたくないときは、次のようにすれば less で見ることができます。
mydb=> select * from mydb where id < 100; mydb=> \g | less
手順の説明をするとこうなります。まず select 文で検索をします。結果が画面を流れて行くのはそのままにしておきます。次に \g | less を実行して検索結果を less で見えるようにします。また、| less の代わりに ファイル名を指定すると、検索結果がファイルに書き込まれます。\g | lpr とするとプリンターに結果を打ち出せます。
前に入力した検索コマンドなどを再利用したいときがあると思います。そのときは、Ctrl + p を押すと、以前に入力したコマンドを呼び出すことができます。Ctrl + p を押すたびに、遡って行きます。Ctrl + n で Ctr + p とは反対の方向に移動できます。Emacs が使えるひとは、psql のコマンドラインを Emacs の要領で編集できます。
psql の \ コマンドのヘルプは \? で表示します。
mydb=> \?
SQL コマンドのヘルプは \h 'コマンド名' で表示します。
mydb=> \h select
psql で接続中のデータベースの中のテーブルのリストを取るには \d コマンドを使います。
mydb=> \d Database = mydb +------------------+----------------------------------+----------+ | Owner | Relation | Type | +------------------+----------------------------------+----------+ | user_id | table_1 | table | | user_id | table_2 | table | +------------------+----------------------------------+----------+
各々のテーブルのカラムの設定をみるには \d 'テーブル名' とします。
mydb=> \d table_1 Table = table_1 +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id | int4 | 4 | | name | text | var | +----------------------------------+----------------------------------+-------+
テーブルの名前を変更するには alter table 命令を使います。psql から \h alter table でヘルプを表示させると、構文は次のようになります。
mydb=> \h alter table
Command: alter table
Description: add/rename attributes, rename tables
Syntax:
alter table <class_name> [*] add column <attr> <type>;
alter table <class_name> [*] rename [column] <attr1> to <attr2>;
alter table <class_name1> rename to <class_name2>
したがって table_2 を table_3 と言う名前に変更するには
mydb=> alter table table_2 rename to table_3;
とします。