PostgreSQLのCRUD

バージョンの確認

$ psql --version
$ psql -V

ログインする

• -U ユーザ名
• -p ポート番号

$ psql -U postgres -p 5432

$ psql -U postgres -p 5433

ログアウトする

postgres=# \q

データベースを作成

ログイン前に作成

$ createdb blogapp -U postgres -p 5433

ログイン後に作成

postgres=# create database blogapp;

※ SQL文の終わりには「;」を付けないとSQL文が終わらないので注意しましょう。

作成したデータベースに接続してログイン

$ psql blogapp -U postgres -p 5433

ログイン後にデータベースの切り替え

postgres=# \c blogapp;

データベースの一覧を見る

ログイン前

$ psql -l

ポート番号が5432でインストールしない場合
ログイン前に実行するPostgreSQLのコマンドは
オプションにユーザとポートを指定する必要があります

$ psql -l -U postgres -p 5433 

ログイン後

blogapp=# \l

データベースの削除

ログイン前に削除

$ dropdb blogapp -U postgres -p 5433

ログイン後に削除

postgres=# drop database blogapp;

ヘルプを見る

ログイン前

$ psql -?
$ psql --help

ログイン後

blogapp=# help
blogapp=# \?

テーブルの作成

blogapp=# create table posts(title varchar(255), body text);

テーブルの確認

blogapp=# \dt

テーブルの構造を確認する

blogapp=# \d posts

テーブルの名前を変更する

blogapp=# alter table posts rename to myposts;

テーブルを削除する

blogapp=# drop table myposts;

外部ファイル読み込んでを実行

commands.sql

create table posts(title varchar(255), body text);

$ psql blogapp -U postgres -p 5433

blogapp=# \i commands.sql

SQL文中のコメント

-- 一行コメント

/*
複数行コメント
*/

いろいろなデータ型（data type）

ドキュメント
http://www.postgresql.org/docs/9.3/interactive/datatype.html

create table posts(
    title varchar(255), -- 可変長型で255文字まで
    body text -- 文字数制限なし
);

数値

integer(int): 整数
real: 実数
serial: 連番

文字

char(5): 固定長5文字
varchar(255): 可変長255文字まで
text: 文字数制限なし

真偽

boolean

指定できる値

• TRUE
• FALSE
• t
• f

日付

date: 日付のみ
time: 時間のみ
timestamp: 日付、時間両方

フィールドの制約（constraints）

ドキュメント
http://www.postgresql.org/docs/9.3/interactive/ddl-constraints.html

create table posts(
    id serial primary key,
    title varchar(255) not null,
    body text check(length(body) > 5),
    is_draft boolean default TRUE,
    created timestamp default 'now'
);

• not null: nullデータを受け付けない
• unique: 一意でなければならない
• check: 入力内容をチェックする
• default: nullデータの場合、初期値を代入
• primary key (not null, qnique): 主キーにする

insert文

まずテーブルを再構築しておきましょう。

外部ファイル「poststable.sql」を作成

poststable.sql

create table posts(
    id serial primary key,
    title varchar(255) not null,
    body text check(length(body) > 5),
    is_draft boolean default TRUE,
    created timestamp default 'now'
);

blogappに接続

$ psql blogapp -U postgres -p 5433

postgres=# \c blogapp;

postsテーブルを削除

blogapp=# drop table posts;

テールを再構築

blogapp=# \i commands.sql

確認

blogapp=# \dt
blogapp=# \d posts

データを挿入

blogapp=# insert into posts (title, body) values ('title1', 'body11111');

データを確認

blogapp=# select * from posts;

データを挿入

blogapp=# insert into posts (title, body) values ('title2', 'body22222');

データを確認

blogapp=# select * from posts;

制約によりbodyが5文字以下だと挿入できない

blogapp=# insert into posts (title, body) values ('title3', 'b3');

select文

全件の表示

blogapp=# select * from posts;

新たにテーブルを定義

create table users (
    id serial primary key,
    name varchar(255),
    score real,
    team varchar(255)
);

数件のデータを挿入

insert into users (name, score, team) values
('yamada', 5.5, 'red'),
('sato', 8.3, 'blue'),
('saito', 6.4, 'blue'),
('takahashi', 2.2, 'blue'),
('suzuki', 5.0, 'green'),
('nomura', 4.6, 'red'),
('imai', 4.7, 'green'),
('yamazaki', 7.1, 'red'),
('yamasaki', 3.9, 'green');

データの確認

blogapp=# \d users

blogapp=# select * from users;

拡張表示をONにする

件数が多い時に縦に並べてデータを見やすくする

blogapp=# \x

blogapp=# select * from users;

拡張表示をOFFにする

再度実行

blogapp=# \x

一部のフィールドのみ取得

blogapp=# select name, score from users;

where句

scoreが4.0以上のデータを取得

blogapp=# select * from users where score > 4.0;

比較に使われる演算子

• > : より大きい
• < : より小さい
• >= : 以上
• >= : 以下
• = : 等しい
• <> : 異なる
• != : 異なる

scoreが5のデータを取得

blogapp=# select * from users where score = 5;

scoreが5ではないデータを取得

blogapp=# select * from users where score != 5;

nameが'yamada'のデータを取得

blogapp=# select * from users where name = 'yamada';

あいまい検索に使われる演算子

• like
• % : 任意の文字
• - : 任意の一文字

nameの最後の文字がiで終わるデータを取得

blogapp=# select * from users where name like '%i';

nameがyamazakiとyamasakiのデータを取得

blogapp=# select * from users where name like 'yama_aki';

nameがsatoとsaitoのデータを取得

blogapp=# select * from users where name like 'sa%to';

orderby, limit, offset

scoreを昇順にソートして取得

blogapp=# select * from users order by score;

scoreを降順にソートして取得

blogapp=# select * from users order by score desc;

teamを降順にソートして取得

blogapp=# select * from users order by team;

teamを降順、scoreを降順にソートして取得

blogapp=# select * from users order by team, score desc;

teamもscoreも降順にソートして取得

blogapp=# select * from users order by team desc, score desc;

件数を制限してデータを取得

blogapp=# select * from users limit 3;

4件目から3件分のデータを取得

インデックスは0からスタートします。

blogapp=# select * from users limit 3 offset 3;

scoreの上位3人分のデータを取得

blogapp=# select * from users order by score desc limit 3;

レコードの集計

usersテーブルの件数を取得する

blogapp=# select count(*) from users;

teamフィールドのデータを一意にして取得する

データの中から重複を削除して取得できます。

blogapp=# select distinct team from users;

scoreの合計を取得する

blogapp=# select sum(score) from users;

scoreの最大値を取得する

blogapp=# select max(score) from users;

scoreの最小値を取得する

blogapp=# select min(score) from users;

scoreの平均を取得する

blogapp=# select avg(score) from users;

teamごとの合計を取得する

blogapp=# select team, sum(score) from users group by team;

条件を指定して集計する

blogapp=# select team, sum(score) from users group by team having sum(score) > 10.0;

関数

ドキュメント
http://www.postgresql.org/docs/9.3/static/functions.html

nameの文字数を取得

blogapp=# select name, length(name) from users;

文字列を連結する

blogapp=# select concat(name, ' (', team, ') ') from users;

フィールド名を変えて取得する

blogapp=# select concat(name, ' (', team, ') ') as namelabel from users;

文字列の1文字目だけ取得する

blogapp=# select substring(team, 1, 1) from users;

blogapp=# select substring(team, 1, 1) as teaminitial from users;

乱数を取得する

0から1までの乱数を生成します。

blogapp=# select random();

ランダムに1件取得する

blogapp=# select * from users order by random() limit 1;

レコードの更新（update）, 削除（delete）

yamadaのscoreを更新する

where句を指定しないと全件更新されるので注意。

blogapp=# update users set score = 5.8 where name = 'yamada';

teamがredのみscoreを1繰り上げる

blogapp=# update users set score = score + 1 where team = 'red';

where句を複数指定する

blogapp=# update users set score = score + 1 where team = 'red' or team = 'green';

scoreが3.0未満のデータを削除

blogapp=# delete from users where score < 3.0;

テーブル構造の変更（alter）

テーブルにフィールドを追加する

blogapp=# alter table users add fullname varchar(255);

確認

blogapp=# \d users;

テーブルからフィールドを削除する

blogapp=# alter table users drop fullname;

確認

blogapp=# \d users;

フィールド名を変える

blogapp=# alter table users rename name to myname;

確認

blogapp=# \d users;

フィールドのデータ型を変える

blogapp=# alter table users alter myname type varchar(32);

確認

blogapp=# \d users;

インデックスを追加する

検索は速くなりますが、更新、追加は遅くなります。

blogapp=# create index team_index on users(team);

確認

blogapp=# \d users;

indexを削除する

blogapp=# drop index team_index;

確認

blogapp=# \d users;

テーブルの結合

テーブルを作り直す

drop table users;
drop table posts;
create table posts(
    id serial primary key,
    user_id int not null,
    title varchar(255) not null,
    body text not null
);
insert into posts (user_id, title, body) values
(1, 'title1', 'body1'),
(1, 'title2', 'body2'),
(2, 'title3', 'body3'),
(5, 'title4', 'body4'),
(4, 'title5', 'body5');
create table users (
    id serial primary key,
    name varchar(255),
    score real,
    team varchar(255)
);
insert into users (name, score, team) values
('yamada', 5.5, 'red'),
('sato', 8.3, 'blue'),
('saito', 6.4, 'blue'),
('takahashi', 2.2, 'blue'),
('suzuki', 5.0, 'green'),
('nomura', 4.6, 'red'),
('imai', 4.7, 'green'),
('yamazaki', 7.1, 'red'),
('yamasaki', 3.9, 'green');

確認

blogapp=# select * from users;
blogapp=# select * from posts;

複数のテーブルからデータを取得する

blogapp=# select users.name, posts.title from users, posts where users.id = posts.user_id;

テーブル名を短くして結合

blogapp=# select u.name, p.title from users u, posts p where u.id = p.user_id;

条件を追加する

blogapp=# select u.name, p.title from users u, posts p where u.id = p.user_id and u.id = 1;

ビュー

ビューを作成

blogapp=# create view yamada_posts as select u.name, p.title from users u, posts p where u.id = p.user_id and u.id = 1;

ビューの確認

blogapp=# \dv

ビューからデータを取得

blogapp=# select * from yamada_posts;

ビューの削除

blogapp=# drop view yamada_posts;

確認

blogapp=# \dv

トランザクション

トランザクションの開始

blogapp=# begin;

yamadaのscoreから1引く

blogapp=# update users set score = score - 1.0 where name = 'yamada';

satoのscoreに1足す

blogapp=# update users set score = score + 1.0 where name = 'sato';

確認

blogapp=# select * from users;

反映する

blogapp=# commit;

確認

blogapp=# select * from users;

トランザクションの開始

blogapp=# begin;

yamadaのscoreから1引く

blogapp=# update users set score = score - 1.0 where name = 'yamada';

satoのscoreに1足す

blogapp=# update users set score = score + 1.0 where name = 'sato';

確認

blogapp=# select * from users;

取り消す

blogapp=# rollback;

確認

blogapp=# select * from users;