7つのデータベース 7つの世界 2章 PostgreSQL 1日目


## テーブル作成

```sql
CREATE TABLE countries (
  country_code char(2) PRIMARY KEY,
  country_name text UNIQUE
);
```

## データ保存

```sql
INSERT INTO countries (country_code, country_name)
VALUES ('us', 'Unitate States'), ('mx', 'Mexico'), ('au', 'Australia'), ('gb', 'United Kingdom'), ('de', 'Germany'),('ll', 'Loompaland');
```

## データ削除

```sql
DELETE FROM countries WHERE country_code='ll';
```

## テーブル作成（外部キー制約）

```sql
CREATE TABLE cities (
  name text NOT NULL,
  postal_code varchar(9) CHECK (postal_code <> ''),
  country_code char(2) REFERENCES countries,
  PRIMARY KEY (country_code, postal_code)
);
```

## 保存と更新

```sql
INSERT INTO cities VALUES ('Portland', '87200', 'us');
UPDATE cities SET postal_code='97205' WHERE name = 'Portland';
```

## 結合参照

```sql
SELECT cities.*, country_name
 FROM cities INNER JOIN countries
 ON cities.country_code = countries.country_code;
```
```
   name   | postal_code | country_code |  country_name  
----------+-------------+--------------+----------------
 Portland | 97205       | us           | Unitate States
(1 row)
```

### 複数の外部キーがある場合

```sql
CREATE TABLE venues (
  venue_id SERIAL PRIMARY KEY,
  name varchar(255),
  street_address text,
  type char(7) CHECK (type in ('public', 'private')) DEFAULT 'public',
  postal_code varchar(9),
  country_code char(2),
  FOREIGN KEY(country_code, postal_code)
    REFERENCES cities (country_code, postal_code) MATCH FULL
);
INSERT INTO venues (name, postal_code, country_code)
 VALUES ('Crystal Ballroom', '97205', 'us');
```

```sql
SELECT v.venue_id, v.name, c.name 
FROM venues v INNER JOIN cities c
  ON v.postal_code = c.postal_code AND v.country_code = c.country_code;
```

```
 venue_id |       name       |   name   
----------+------------------+----------
        1 | Crystal Ballroom | Portland
(1 row)

```

#### RETURNING で主キー確認

```sql
INSERT INTO venues (name, postal_code, country_code) VALUES ('Voodoo Donuts', '97205', 'us') RETURNING venue_id;
```
```
 venue_id 
----------
        2
(1 row)

INSERT 0 1
```

### 外部結合

```sql
CREATE TABLE events (
  event_id SERIAL PRIMARY KEY,
  title varchar(255),
  starts timestamp,
  ends timestamp,
  venue_id integer REFERENCES venues 
);
```

```sql
INSERT INTO events (title, starts, ends, venue_id)
 VALUES ('LARP Club', '2012-02-15 17:30:00', '2012-02-15 19:30:00', 2),
  ('April Fools Day', '2012-04-01 00:00:00', '2012-04-01 23:59:00', NULL),
  ('Christmas Day', '2012-12-25 00:00:00', '2012-12-25 23:59:00', NULL);
```

#### join

```sql
SELECT e.title, v.name 
FROM events e JOIN venues v
  ON e.venue_id = v.venue_id;
```

```
   title   |     name      
-----------+---------------
 LARP Club | Voodoo Donuts
(1 row)
```

#### left outer join

```sql
SELECT e.title, v.name 
FROM events e LEFT JOIN venues v
  ON e.venue_id = v.venue_id;
```

```
      title      |     name      
-----------------+---------------
 LARP Club       | Voodoo Donuts
 April Fools Day | 
 Christmas Day   | 
(3 rows)
```

## インデックスで高速化

```sql
CREATE INDEX events_title ON events USING hash (title);
CREATE INDEX events_start ON events USING btree(starts);
```

### スキーマのインデックス確認

```sql
\di
```

```
                         List of relations
 Schema |            Name            | Type  |  Owner   |   Table   
--------+----------------------------+-------+----------+-----------
 public | cities_pkey                | index | postgres | cities
 public | countries_country_name_key | index | postgres | countries
 public | countries_pkey             | index | postgres | countries
 public | events_pkey                | index | postgres | events
 public | events_start               | index | postgres | events
 public | events_title               | index | postgres | events
 public | venues_pkey                | index | postgres | venues
(7 rows)
```

## 1日目の宿題

### 1. 作成したテーブルをpg_class から選択

- 参考: [43.9. pg_class](http://www.postgresql.jp/document/7.4/html/catalog-pg-class.html)

これまでに作った```events``` を選んでみる．

```sql
SELECT * FROM pg_class WHERE relname = 'events';
```

### 2. LARP Club というイベントが開催される国を探す

events -> venues -> countries
と外部キーを辿って結合する．

```sql
SELECT c.country_name, e.title 
FROM events e
INNER JOIN venues v ON e.venue_id = v.venue_id
INNER JOIN countries c ON v.country_code = c.country_code;
```

### 3. venues テーブルにavtive というboolean型の列を追加

```ALTER TABLE table_name ADD COLUMN``` を使う

```sql
ALTER TABLE venues ADD COLUMN active boolean DEFAULT TRUE;
SELECT * FROM venues;
```