postgresqlチュートリアル抜粋(v10.4)

##目的
仕事でpostgresqlとpl/pgsql使うことになったので勉強。

oracleのもそうだったけど、公式文書がどうにも読みにくいと感じて仕方がない…

Javadoc的なものが欲しいけど、無いものねだりしても仕方ないので
[チュートリアル](https://www.postgresql.jp/document/10/html/tutorial.html)から自分が欲しいところだけ抜粋。

##DB
・対話環境からではなく、コマンドラインから直接実施
・ユーザー省略時はOSのユーザー名が参照される
###作成

```sh

createdb mydb

# ユーザー指定の例
createdb -U postgres mydb
```

###削除

```sh

dropdb mydb
```

##対話環境
・末尾に;がなければ無視される

###接続

```sh
psql mydb

psql -U postgress mydb
```

###ヘルプ
psql接続後

```sh

\?
```

###一覧系

```sh
# テーブル、ビュー、シーケンス一覧
\d

# DB一覧
\l
```

###切断

```sh

\q
```

##テーブル関連

###テーブル作成

```sql
CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低気温
    temp_hi         int,           -- 最高気温
    prcp            real,          -- 降水量
    date            date
);
```

###テーブル削除

```sql
DROP TABLE weather;
```

##レコード操作

###INSERT

```sql

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
```
※[COPY](https://www.postgresql.jp/document/10/html/sql-copy.html)を使って平文テキストファイルから一括ロードできるらしい

###SELECT

```sql
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

-- DISTINCTとかももちろん使える
SELECT DISTINCT city
    FROM weather
    ORDER BY city;
```

####結合

```sql
SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);

SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;
```

####集約

```sql
-- 集計
SELECT max(temp_lo) FROM weather;

-- 副問合せ
SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

-- GROUP BY
SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;

-- フィルタ
SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

```

###UPDATE

```sql

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';
```

###DELETE

```sql
DELETE FROM weather WHERE city = 'Hayward';

-- 確認？　慈悲はない
DELETE FROM tablename;
```

##高度な機能

###ビュー

```sql

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;
```

###外部キー

```sql
CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

-- エラーになる
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
```

###トランザクション
・BEGINを省略するとオートコミット
・SAVEPOINTを使う事でROLLBACK先を細かく制御できる

```sql
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
COMMIT;
```

```sql

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- ぼうけんのしょ1にキロク
SAVEPOINT my_savepoint;
-- 名義間違い
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- 何も起きなかった、いいね
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;
```

####ウィンドウ関数
・グループ化はしないが、任意の項目で集約できる

```sql
select city, sum(temp_lo) OVER (PARTITION BY temp_hi) from weather;
```

```sql
select * from weather;
     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Kishiwada     |      43 |      57 |    0 | 1994-11-29
 Izumi         |    2345 |      53 |    0 | 1994-11-28
 Izumi         |    9817 |      53 |    0 | 1994-11-28
 Izumisano     |     123 |      53 |    0 | 1994-11-28
 Sennan        |    1321 |      53 |    0 | 1994-11-28
 Izumisano     |   12313 |      53 |    0 | 1994-11-28
 Sennan        |    9786 |      53 |    0 | 1994-11-28

select city, sum(temp_lo) OVER (PARTITION BY city) from weather;
     city      |  sum
---------------+-------
 Izumi         | 12162
 Izumi         | 12162
 Izumisano     | 12436
 Izumisano     | 12436
 Kishiwada     |    43
 San Francisco |    43
 Sennan        | 11107
 Sennan        | 11107
(8 行)


select city, sum(temp_lo) OVER (PARTITION BY temp_hi) from weather;
     city      |  sum
---------------+-------
 Sennan        | 35705
 Sennan        | 35705
 Izumisano     | 35705
 Izumi         | 35705
 Izumi         | 35705
 Izumisano     | 35705
 Kishiwada     |    86
 San Francisco |    86
(8 行)
```

####継承
・親TBL、子TBLを一緒くたに扱える
・SELECT,UPDATE,DELETEとかも対応してる

```sql
CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- （フィート単位）
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);
```

```sql
-- 継承元のテーブルもまとめて検索してくれる
SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

-- ONLYつけたら指定テーブルのみ検索
SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;
```