トップ 差分 一覧 Farm ソース 検索 ヘルプ RSS ログイン

PostgreSQL

PostgreSQL

[PostgreSQL]
最終更新時間:2010年08月27日 05時48分58秒

コマンド

 データベース作成

$ createdb [データベース名]

 データベース削除

$ dropdb [データベース名]

 データベース接続

$ psql [データベース名]
psql (8.4.4)
Type "help" for help.

SQLコマンド

 テーブル作成

sampledb=# create table [テーブル名] (
sampledb=#   id      int,
sampledb=#   city    varchar(80),
sampledb=#   data    int
sampledb=# );

 テーブル削除

sampledb=# drop table [テーブル名];

 データ挿入

sampledb=# insert into [テーブル名] values (1, 'hoge', 1);

または

sampledb=# insert into [テーブル名] (id, city, data) values (1, 'hoge', 1);

または

sampledb=# insert into [テーブル名] (id, data) values (1, 1);

※copyコマンドを使えばファイルからもデータ挿入ができる

 テーブル問い合わせ

postgres=# select * from [テーブル名];
AS句を使った計算
postgres=# select id, (id + data)/2 as avg, city, data from [テーブル名];
whereを使った検索
postgres=# select * from [テーブル名] order by id [ASC|DESC];
limit, offsetを使った検索
postgres=# select * from [テーブル名] order by id desc offset 2 limit 4;
重複を取り除く検索
postgres=# select DISTINCT * from [テーブル名];
LIKE句を使った検索

aで始まるものを検索する

postgres=# select * from tbl1 like 'a%';

 テーブル結合

postgres=# select * from tbl1, tbl2;

※この場合はすべてのパターンが出てきてしまうので注意

重複した列名の検索方法
postgres=# select * from tbl1, tbl2 where tbl1.id = tbl2.id;

 集約関数

count(総数)
postgres=# select count(*) from tbl1;
max(最大値)
postgres=# select max(id) from tbl1;
min(最小値)
postgres=# select min(id) from tbl1;
sum(総和)
postgres=# select sum(data) from tbl1;
avg(平均)
postgres=# select avg(data) from tbl1;
副問い合わせを使った検索
postgres=# select city from tbl1 where id = (select max(id) from tbl1);
GROUP BY句を使った検索

cityでグループ化し、同じ都市名が何個あるかを数えている

postgres=# select city, count(city) from tbl1 group by city;

 データ更新

postgres=# update [テーブル名] set data = 2 where id = 1;

 データ削除

postgres=# delete from [テーブル名] where id = 3;

 ビュー作成

ビューを作成することで楽にコマンドを打てるようになる

postgres=# create view [view name] as select [query];

postgres=# create view myview as
             select city, temp_lo, temp_hi, prcp, date, location from weather, cities
             where city = name;

 ビュー削除

postgres=# drop view [view name];

制約

 列制約

列の値を制約する

金額が0円以上であることを指定する
postgres=# create table products (
             product_no int,
             name text,
             price numeric check (price > 0)
           );
名前を付ける
postgres=# create table products (
             product_no int,
             name text,
             price numeric constraint positive_price (price > 0)
           );
複数の列を参照して制約をつける
postgres=# create table products (
             product_no int,
             name text,
             price numeric check (price > 0),
             discounted_price numeric check (discounted_price > 0),
             check (price > discounted_price)
           );

 テーブル制約

列制約ではなく別個にして制約をつけることもできる。これをテーブル制約という。
列制約をテーブル制約として書くことはできるが、逆はできない場合があるらしい。

テーブル制約
postgres=# create table products (
             product_no int,
             name text,
             price numeric,
             check (price > 0),
             discounted_price numeric,
             check (discounted_price > 0),
             check (price > discounted_price)
           );

 非NULL制約

NULL値をとることを否定する。

 CREATE TABLE products (
   product_no integer NOT NULL,
   name text NOT NULL,
   pricenumeric
 );

 一意性制約

ある値が行の中で一意であることを指定する。

 CREATE TABLE (
   product_no integer UNIQUE,
   name text,
   price numeric
 );

または

 CREATE TABLE (
   product_no integer,
   name text,
   price numeric,
   UNIQUE (product_no)
 );
グループで一意制約を指定する

グループで一意制約を指定した場合、グループで一意であればよい。
下記の場合だとaとcの組み合わせで一意であればよい。

 CREATE TABLE (
   a integer,
   b integer,
   c integer,
   UNIQUE (a, c)
 );

※NULL値に関しては一意であることを強制しない。

 プライマリーキー

プライマリーキー制約は単純に一意制約と非NULL制約を組み合わせたもの。

 CREATE TABLE products (
   product_no integer PRIMARY KEY,
   name text,
   price numeric
 );

プライマリーキーでも複数列を指定することができる

 CREATE TABLE example (
   a int,
   b int,
   c int,
   PRIMARY KEY (a, c)
 );

 外部キー

テーブル同士に関係性を持たせることができる。

以下の2つのテーブルに関して説明すると、ordersテーブルのproduct_noはproductsテーブルのproduct_noにある値しか入れることができない。
製品として存在しないものをオーダーできないという考え方。

 CREATE TABLE products (
   product_no int PRIMARY KEY,
   name text,
   price numeric
 ;
 CREATE TABLE orders (
   order_id int PRIMARY KEY,
   product_no int REFERENCES products (product_no),
   quantity int
 );
別の書き方

列名を指定しない場合、そのテーブルのプライマリーキーが選ばれる
この場合はproductsテーブルのproducts_noが選ばれる。

 CREATE TABLE orders (
   order_id int PRIMARY KEY,
   product_no int REFERENCES products,
   quantity int
 );

※外部キーはプライマリーキーか一意制約で指定されたものしかとることができない

外部キーの値を削除する場合に関して

外部キーの値を削除する場合、

  • 参照される製品の削除を許可しない
  • 注文も一緒に削除する

という選択肢がある

参照される製品の削除を許可しない場合
 CREATE TABLE order_items (
   product_no int REFERENCES products ON DELETE RESTRICT,
   order_id int,
   quantity int,
   PRIMARY KEY product_no
 );
注文も一緒に削除する
 CREATE TABLE order_items (
   product_no int,
   order_id int REFERENCES order_items ON DELETE CASCADE,
   quantity int,
   PRIMARY KEY product_no
 );

テーブル結合

以下のようなテーブルがあったとする。

t1テーブル
Column Type Modifiers
id integer not null default nextval('t1_id_seq'::regclass)
name text
t2テーブル
Column Type Modifiers
id integer not null default nextval('t2_id_seq'::regclass)
value text
データ
 sampledb=# select * from t1;
 id | name 
----+------
  1 | hoge
  2 | fuga
  3 | foo
  4 | buzz
 
 sampledb=# select * from t2;
 id | value 
----+-------
  1 | xxx
  2 | yyy
  3 | zzz
テーブル結合
sampledb=# select * from t1, t2
 id | name | id | value 
----+------+----+-------
  1 | hoge |  1 | xxx
  1 | hoge |  2 | yyy
  1 | hoge |  3 | zzz
  2 | fuga |  1 | xxx
  2 | fuga |  2 | yyy
  2 | fuga |  3 | zzz
  3 | foo  |  1 | xxx
  3 | foo  |  2 | yyy
  3 | foo  |  3 | zzz
  4 | buzz |  1 | xxx
  4 | buzz |  2 | yyy
  4 | buzz |  3 | zzz

※以下でも同じ

sampledb=# select * from t1 cross join t2;
INNER JOIN
sampledb=# select * from t1 inner join t2 on t1.id = t2.id;
 id | name | id | value 
----+------+----+-------
  1 | hoge |  1 | xxx
  2 | fuga |  2 | yyy
  3 | foo  |  3 | zzz
sampledb=# select * from t1 inner join t2 using(id);
 id | name | value 
----+------+-------
  1 | hoge | xxx
  2 | fuga | yyy
  3 | foo  | zzz
sample=# select * from t1 natural inner join t2;
 id | name | value 
----+------+-------
  1 | hoge | xxx
  2 | fuga | yyy
  3 | foo  | zzz
LEFT JOIN
sampledb=# select * from t1 left join t2 on t1.id = t2.id;
 id | name | id | value 
----+------+----+-------
  1 | hoge |  1 | xxx
  2 | fuga |  2 | yyy
  3 | foo  |  3 | zzz
  4 | buzz |    | 
sampledb=# select * from t1 left join t2 using(id);
 id | name | value 
----+------+-------
  1 | hoge | xxx
  2 | fuga | yyy
  3 | foo  | zzz
  4 | buzz | 
RIGHT JOIN
sampledb=# select * from t1 right join t2 on t1.id = t2.id;
 id | name | id | value 
----+------+----+-------
  1 | hoge |  1 | xxx
  2 | fuga |  2 | yyy
  3 | foo  |  3 | zzz
sampledb=# select * from t1 right join t2 using(id);
 id | name | value 
----+------+-------
  1 | hoge | xxx
  2 | fuga | yyy
  3 | foo  | zzz
FULL JOIN
sampledb=# select * from t1 full join t2 on t1.id = t2.id;
 id | name | id | value 
----+------+----+-------
  1 | hoge |  1 | xxx
  2 | fuga |  2 | yyy
  3 | foo  |  3 | zzz
  4 | buzz |    | 

GROUP BYとHAVING句

GROUP BYを用いると同じ行内で同一の値のものをグループ化することができる。

サンプルテーブル
 Column |  Type   | Modifiers 
--------+---------+-----------
 x      | text    | 
 y      | integer | 
サンプルデータ
kichon=# select * from test1;
 x | y 
---+---
 a | 3
 c | 2
 b | 5
 a | 1
GROUP BY

xでGROUP BYをする

kichon=# select x from test1 group by x;
 x 
---
 c
 b
 a

※以下のようにするとエラーがでる

kichon=# select * from test1 group by x;

yはグループ化できないため。

集約式でグループ化する

集約式を用いるとyもグループ化することができる

kichon=# select x, sum(y) from test1 group by x;
 x | sum 
---+-----
 c |   2
 b |   5
 a |   4
kichon=# select x, count(y) from test1 group by x;
 x | count 
---+-------
 c |     1
 b |     1
 a |     2
HAVING

HAVINGは通常のSQLのWHERE句のようなもの

kichon=# select x, sum(y) from test1 group by x having sum(y) > 3;
 x | sum 
---+-----
 b |   5
 a |   4
kichon=# select x, sum(y) from test1 group by x having x < 'c';
 x | sum 
---+-----
 b |   5
 a |   4
DISTINCT

DISTINCTを遣うと重複した行を削除することができる。
イメージ的にはGROUP BYと似ている。

kichon=# select distinct x from test1;
 x 
---
 c
 b
 a

 連番型

連番はserial型を使用することで実現できる。
(他のデータベースで言うところのauto_increment)

 kichon=# create table hoge (
            id serial
          );

これは以下と同じこと。

 kichon=# create sequence hoge_id_seq;
 kichon=# create table hoge (
            id integer not null default nextval('hoge_id_seq')
          );
 kichon=# alter sequence hoge_id_seq owned by hoge.id;

 文字列

char型

固定長の文字列型

 kichon=# create table test1(a char(4));
 kichon=# insert into values ('ok');
 kichon=# select a, char_length(a) from test1;
   a   | char_length
 ------+-------------
  ok   |           2

設定ファイル

 最大接続数

postgresql.confのmax_connectionsで設定