はじめてのPostgreSQL 9.1.5文書 第一章〜第二章
そろそろRDBのひとつくらいちゃんと扱えるようになりたいなーと思い、日本PostgreSQLユーザ会のPostgreSQL文書を読もうかと。
http://www.postgresql.jp/document/9.1/html/
HTML版がデザイン的にとてもシンプルなので若干の読みにくさがあるのですが、文章はとてもしっかりしてます。
PDF版もあるのでKindleとかに入れたらごろ寝して読めるからいいかも。
最新版は9.2だけど、UbuntuServerでaptから入れられるのが9.1なので9.1版のドキュメントを読むことにしました。
ちゃんと最後まで読み続けられるといいなー。
Ubuntuでaptからインストールすると、微妙にコマンドが違ったりするみたい。
Ubuntu で PostgreSQL を使ってみよう(続編) | Let's Postgres
資料編：Ubuntu特有のPostgreSQL用コマンド | Let's Postgres
ってかここもみた方が良いかも。PostgreSQL文書読んだら見てみよう。
ホーム | Let's Postgres
環境：Ubuntu 日本語版 12.04 LTS 32bit on VirtualBox
1.1 インストール
PostgreSQLをインストールします。1.1から飛べる15章のところにはソースコードからインストールする方法が書いてあるのだけど、なんだかパッケージ推しのようなのでaptからインストールします。
$ sudo apt-get install postgresql
$ sudo apt-cache show postgresql | less
とすると9.1に依存してるのがわかるかと。つまり9.1が入るってことですねー。
ついでに
$ dpkg -l | grep postgresql
とかしても良いかも。
1.3 データベースの作成
createdbコマンドでDBを作成できるみたい。
$ which createdb /usr/bin/createdb $ createdb mydb createdb: could not connect to database postgres: FATAL: role "sasaplus1" does not exist
普通のユーザだと許可されてなくて作成できないみたい……
なのでpostgresユーザで実行してみる。
$ tail /etc/passwd
$ sudo passwd postgres
$ su - postgres
$ createdb mydb
できた！
DBの削除は以下で出来るみたい。
$ dropdb mydb
http://www.postgresql.jp/document/9.1/html/app-createdb.html
http://www.postgresql.jp/document/9.1/html/app-dropdb.html
1.4 データベースへのアクセス
おなじみpsqlでDBへアクセスします。
$ createdb mydb $ psql mydb psql (9.1.9) Type "help" for help. mydb=# SELECT version(); version ------------------------------------------------------------------------------------------------------------ PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit (1 row) mydb=# SELECT current_date; date ------------ 2013-04-25 (1 row) mydb=# SELECT 2 + 2; ?column? ---------- 4 (1 row)
version()やらcurrent_dateやら足し算をしてみたり。
あと\hで内部コマンドの表示、\?で他の内部コマンド、\qでpsqlの終了。
http://www.postgresql.jp/document/9.1/html/app-psql.html
2.3 新しいテーブルの作成
新しいテーブルを作成するSQLを投げます。
mydb=# CREATE TABLE weather ( mydb(# city varchar(80), mydb(# temp_lo int, -- 最低気温 mydb(# temp_hi int, -- 最高気温 mydb(# prcp real, -- 降水量 mydb(# date date mydb(# ); CREATE TABLE
まあこれくらいは普通に書けてるかも。型を広く知らないけど。
セミコロンで終わるまでコマンドとして継続するものと認識することと、
空白、タブ、改行は自由に使えること、ハイフン2つで行コメントになるみたい。
mydb=# CREATE TABLE cities ( mydb(# name varchar(80), mydb(# location point mydb(# ); CREATE TABLE
point型はPostgreSQL独自のデータ型らしい……
他のRDBも使う事があるなら使わない方が良いのかも。
テーブルの削除は以下の通り。
mydb=# DROP TABLE weather; DROP TABLE mydb=# DROP TABLE cities; DROP TABLE
2.4 テーブルに行を挿入
基本的な挿入方法は以下のような感じ。
mydb=# INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
まあ、大体は以下みたいにちゃんと指定するんじゃないかな。あと余計な箇所は省略したりとか。
mydb=# INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);
あとCOPYというのを使うと平文のテキストファイルからロードできるみたい。
mydb=# COPY weather FROM '/home/user/weather.txt';
どういう形式なら大丈夫なんだろう？
って調べてみたらTSVとかCSVとかいろいろ指定できるみたい。
今まで十数万行のINSERT文のSQLファイル出力したりしてたけど、こっちのほうが速いみたいだしこっち使っていきたい。
2.5 テーブルへの問い合わせ
mydb=# SELECT * FROM weather;
全カラムを取得する。psqlからよく書いたりする。
mydb=# SELECT city, (temp_hi + temp_lo) / 2 AS temp_arg, date FROM weather;
任意の式を書いたり、ASでラベル付けしたり。
mydb=# SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
WHEREで特定の条件に一致する行だけを取得したりとか。
mydb=# SELECT * FROM weather ORDER BY city, temp_lo;
ORDER BYで順番を並べ替えたりとか。
mydb=# SELECT DISTINCT city FROM weather;
DISTINCTで重複する行を表示しなくしたりとか。
2.6 テーブル間を結合
mydb=# SELECT * mydb-# FROM weather, cities mydb-# WHERE city = name;
FROMを複数指定した上で、cityとnameが一致する行だけを取得。
なんかこの辺に書いてある結合の意味がわからない…… 難しく考え過ぎなのかな。
この場合は*で指定できるけど、カラム名が重複する場合は列名を修飾する必要があるみたい。
mydb=# SELECT weather.city, cities.location FROM weather, cities WHERE weather.city = cities.name;
結合問い合わせっていうみたい。JOINを使わなくても結合問い合わせなのかー。
mydb=# SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
これで左外部結合。よくわからん。右外部結合、完全外部結合もよくわからん……
結合についてよく調べた方が良いかも。
以下はラベル付けでの問い合わせ。
mydb=# SELECT * mydb-# FROM weather w, cities c mydb-# WHERE w.city = c.name;
2.7 集約関数
mydb=# SELECT max(temp_lo) FROM wather;
temp_loで一番値として高い行だけ取得。
集約関数はWHERE句には使えないので、そのような事をしたい場合は以下のような感じにする。
mydb=# SELECT city FROM weather mydb-# WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
サブクエリを使うわけか。
GROUP BYで集約して、HAVINGで集約した行に対してフィルターをかけたり。
mydb=# SELECT city, max(temp_lo) mydb-# FROM weather mydb-# GROUP BY city mydb-# HAVING max(temp_lo) < 40;
まだよく意味をわかってないのでここも調べたいかも。
WHEREだと集約関数は使えないけど、HAVINGでは常に使う事になる、とある。
2.8 更新
普通にUPDATE文を。
mydb=# UPDATE weather mydb-# SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 mydb-# WHERE date > '1994-11-28';
説明があまりないということは、特筆するような事がないってことなのかな？
2.9 削除
普通にDELETE文を。
mydb=# DELETE FROM weather WHERE city = 'Hayward';
WHERE付けないとえらいことになるので忘れずに。
とりあえずこんなところ？
復習しないといけないところがあるからちゃんと調べないとなー。
