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
```
### ログイン後に作成

```sql:
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
```

### ログイン後に削除

```sql:
postgres=# drop database blogapp;
```


## ヘルプを見る

### ログイン前

```
$ psql -?
$ psql --help
```
### ログイン後

```
blogapp=# help
blogapp=# \?
```

## テーブルの作成

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

## テーブルの確認

```
blogapp=# \dt
```

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

```
blogapp=# \d posts
```


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

```sql:
blogapp=# alter table posts rename to myposts;
```

## テーブルを削除する

```sql:
blogapp=# drop table myposts;

```

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

```sql:commands.sql
create table posts(title varchar(255), body text);
```
```
$ psql blogapp -U postgres -p 5433
```

```
blogapp=# \i commands.sql
```

## SQL文中のコメント

```sql:
-- 一行コメント
```

```sql:
/*
複数行コメント
*/
```

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

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

```sql:
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

```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'
);
```

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


## insert文

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

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

```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テーブルを削除

```sql:
blogapp=# drop table posts;
```

### テールを再構築

```
blogapp=# \i commands.sql
```

### 確認

```
blogapp=# \dt
blogapp=# \d posts
```

### データを挿入

```sql:
blogapp=# insert into posts (title, body) values ('title1', 'body11111');
```
### データを確認

```sql:
blogapp=# select * from posts;
```
### データを挿入

```sql:
blogapp=# insert into posts (title, body) values ('title2', 'body22222');
```
### データを確認

```sql:
blogapp=# select * from posts;
```

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

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

## select文

### 全件の表示

```sql:
blogapp=# select * from posts;
```

### 新たにテーブルを定義

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

### 数件のデータを挿入

```sql:
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
```

```sql:
blogapp=# select * from users;
```

### 拡張表示をONにする

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

```
blogapp=# \x
```

```sql:
blogapp=# select * from users;
```

### 拡張表示をOFFにする

再度実行

```
blogapp=# \x
```

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

```sql:
blogapp=# select name, score from users;
```

## where句

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

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

### 比較に使われる演算子

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

### scoreが5のデータを取得

```sql:
blogapp=# select * from users where score = 5;
```

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

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

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

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

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

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

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

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

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

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

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

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


## orderby, limit, offset

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

```sql:
blogapp=# select * from users order by score;
```

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

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

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

```sql:
blogapp=# select * from users order by team;
```

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

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

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

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

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

```sql:
blogapp=# select * from users limit 3;
```

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

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

```sql:
blogapp=# select * from users limit 3 offset 3;
```

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

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

## レコードの集計

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

```sql:
blogapp=# select count(*) from users;
```

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

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

```sql:
blogapp=# select distinct team from users;
```

### scoreの合計を取得する

```sql:
blogapp=# select sum(score) from users;
```

### scoreの最大値を取得する

```sql:
blogapp=# select max(score) from users;
```

### scoreの最小値を取得する

```sql:
blogapp=# select min(score) from users;
```

### scoreの平均を取得する

```sql:
blogapp=# select avg(score) from users;
```

### teamごとの合計を取得する

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

### 条件を指定して集計する

```sql:
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の文字数を取得

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

### 文字列を連結する

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

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

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

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

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

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

### 乱数を取得する

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

```sql:
blogapp=# select random();
```
### ランダムに1件取得する

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


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

### yamadaのscoreを更新する

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

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

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

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

### where句を複数指定する

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


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

```sql:
blogapp=# delete from users where score < 3.0;
```


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

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

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

### 確認

```
blogapp=# \d users;
```

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

```sql:
blogapp=# alter table users drop fullname;
```

### 確認

```
blogapp=# \d users;
```

### フィールド名を変える

```sql:
blogapp=# alter table users rename name to myname;
```

### 確認

```
blogapp=# \d users;
```

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

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

### 確認

```
blogapp=# \d users;
```

### インデックスを追加する

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

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

### 確認

```
blogapp=# \d users;
```

### indexを削除する

```sql:
blogapp=# drop index team_index;
```

### 確認

```
blogapp=# \d users;
```

## テーブルの結合

### テーブルを作り直す

```sql:
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');
```

### 確認

```sql:
blogapp=# select * from users;
blogapp=# select * from posts;
```

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

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

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

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

### 条件を追加する

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

## ビュー

### ビューを作成

```sql:
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
```

### ビューからデータを取得

```sql:
blogapp=# select * from yamada_posts;
```

### ビューの削除

```sql:
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;
```