PostgreSQLの基本部分についてまとめた

# はじめに
こちらの記事の続きで書いてます。

https://qiita.com/Nats72/items/4ac07b6138007eb596b1

DBは自分で触って覚えてくのが一番だと聞いたので、
おうちクラウドのVMにDBコンテナを立てて、触りながら学習した記録です。

# DB操作

## DB接続
DBサーバーから接続する場合は、`-h`オプションは省略可能
```
# psql -U {DBユーザー名} -d {DB名} -h {DBサーバーのホスト名}
```

## DB作成
```
# CREATE DATABASE {DB名};
```

# TABLE操作
```
# CREATE TABLE Shohin(shohin_id CHAR(4) NOT NULL,
  shohin_mei VARCHAR(100) NOT NULL,
  shohin_bunrui VARCHAR(32) NOT NULL,
  hanbai_tanka INTEGER,
  shiire_tanka INTEGER,
  torokubi DATE,
  PRIMARY KEY (shohin_id));
CREATE TABLE
```

## データ型の種類
| 型 | 型種別 | 備考 |
|:-:|:-:|:-:|
| INTEGER | 数値型 | 整数のみ |
| CAHR | 文字列型 | 固定長の文字列型。CHAR(10)のように、カッコ内に文字列の最大長を入れる。CHAR(10)に'abc'という文字を入れた場合、残りの7枠は半角スペースで埋められる。 |
| VARCHAR | 文字列型 | 可変長文字列型。VARCHAR(10)に'abc'という文字を入れた場合でも、半角スペースで埋められない。 |
| DATE | 日付型 | 年月日を入れる |

## 制約
| 制約 | 備考 |
|:-:|:-:|
| NOT NULL | 空データはNG |
| PRIMARY KEY | 主キーに設定。PRIMARY KEY({カラム名})で、指定するカラム名を主キーに設定する。 |

## テーブル削除
```
# DROP TABLE {テーブル名};
```
## 列を追加する
```
# ALTER TABLE {テーブル名} ADD COLUMN {列の定義};
```
## 列を削除する
```
# ALTER TABLE {テーブル名} DROP COLUMN {列名};
```
## テーブルへのデータ入力
トランザクションを開始する。
`COMMIT`もしくは`ROLLBACK`が行われるまで、入力を制限する。
```
# BEGIN TRANSACTION;
BEGIN
```
データ(レコード)を登録する。
```
# INSERT INTO Shohin VALUES ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20');
INSERT 0 1
```
登録を確定してトランザクションを解除する。
```
# COMMIT;
COMMIT
```
登録を確定しない場合で、トランザクションを解除する場合。
```
# ROLLBACK
ROLLBACK
```

# テーブル検索
## SELECT文
テーブル内のデータを検索するときは、**SELECT文**を使います。
SELECT句やFROM句は、大文字で記載してますが、小文字でも動きます。
`SELECT * FROM {テーブル名};`で検索します。
```
# SELECT * FROM shohin;
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi  
-----------+----------------+---------------+--------------+--------------+------------
 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ   | 事務用品      |          500 |          320 | 2009-09-11
 0003      | カッターシャツ | 衣服          |         4000 |         2800 | 
```
*の箇所にカラム名を入れることで、必要なカラムだけを取り出すことができます。
カラム名は、カンマ区切りで複数指定できます。
カラムの順番は自由に変えられます。
```
# SELECT shohin_mei, shohin_bunrui FROM shohin;
   shohin_mei   | shohin_bunrui 
----------------+---------------
 Tシャツ        | 衣服
 穴あけパンチ   | 事務用品
 カッターシャツ | 衣服

# SELECT shohin_bunrui, shohin_mei FROM shohin;
 shohin_bunrui |   shohin_mei   
---------------+----------------
 衣服          | Tシャツ
 事務用品      | 穴あけパンチ
```
カラムに別名をつけることもできる。
日本語を使う場合は、ダブルクォーテーションで囲う。
```
# SELECT shohin_mei AS name, shohin_bunrui AS "分類" FROM shohin;
      name      |     分類     
----------------+--------------
 Tシャツ        | 衣服
 穴あけパンチ   | 事務用品
 カッターシャツ | 衣服
```

### DISTINCT句
重複削除した検索をすることができます。
複数カラムを選択することもできます。
```
# SELECT DISTINCT shohin_bunrui FROM shohin;
 shohin_bunrui 
---------------
 キッチン用品
 衣服
 事務用品
(3 rows)
```
### WHERE句
条件式を書くことができます。
`SELECT ~ FROM ~ WHERE {条件式};`
以下の例は、商品分類カラムが「衣服」のデータのみに絞って検索しています。
```
# SELECT * FROM shohin WHERE shohin_bunrui='衣服';
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi  
-----------+----------------+---------------+--------------+--------------+------------
 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20
 0003      | カッターシャツ | 衣服          |         4000 |         2800 | 
(2 rows)
```
#### 比較演算子
| 比較演算子| 内容　|
|:-:|:-:|
| = | 等しい |
| <>  | 等しくない |
| >= | 以上 |
| <= | 以下 |
| > | より大きい |
| < | より小さい |

#### 論理演算子
WHERE句の条件を否定したり複数合致させたい場合などで使います。
```
# SELECT * FROM shohin WHERE shohin_bunrui='衣服' AND hanbai_tanka > 1000;
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi 
-----------+----------------+---------------+--------------+--------------+----------
 0003      | カッターシャツ | 衣服          |         4000 |         2800 | 
(1 row)
```
| 論理演算子 | 内容 |
|:-:|:-:|
| AND | 複数条件に合致する場合 |
| OR | いずれかの条件が合致する場合 |
| NOT | 条件に合致しない場合 | 
# 参考書籍
こちらの書籍で勉強させていただきました。
PostgreSQLメインで書かれてる＆初学者向けの私にちょうどいい書籍でした。

## データの集約
**COUNT関数**でレコード数を数える。
カッコの中でカラムを指定すると、指定したカラムのデータがNULLのものは除外される。
```
# SELECT COUNT(*) FROM shohin;
 count 
-------
     8
(1 row)

# SELECT COUNT(shiire_tanka) FROM shohin;
 count 
-------
     6
(1 row)
```
**SUM関数**で合計値を出す。
```
# SELECT SUM(shiire_tanka) FROM shohin;
  sum  
-------
 12210
(1 row)
```
その他にも数値計算する関数があります。
| 関数 | 内容 |
|:-:|:-:|
| COUNT | レコード数 |
| SUM | 合計 |
| AVG | 平均 |
| MAX | 最大 |
| MIN | 最小 |

### 分類ごとにカウントする
**GROUP BY句**を使うことで分類ごとに集約できます。
```
# SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui;
 shohin_bunrui | count 
---------------+-------
 キッチン用品  |     4
 衣服          |     2
 事務用品      |     2
(3 rows)
```
### ソート(並べ替え)する
**ORDER BY句**で並び替えをすることができます。
```
# SELECT * FROM shohin ORDER BY hanbai_tanka DESC;
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi  
-----------+----------------+---------------+--------------+--------------+------------
 0006      | 圧力鍋         | キッチン用品  |         6800 |         5000 | 2009-09-20
 0003      | カッターシャツ | 衣服          |         4000 |         2800 | 
 0004      | 包丁           | キッチン用品  |         3000 |         2800 | 2009-01-15
```
| ソートキー | 内容 |
|:-:|:-:|
| ASC | 昇順で並べる。ソートキーを省略した場合は昇順となる。 |
| DESC | 降順で並べる。 |

https://www.shoeisha.co.jp/book/detail/9784798144450