1人で0からWebアプリを作成できるようになる入門記事(2) PostgresQL・SQL入門

## 2章 PostgreSQL・SQL入門

目次
- [2.1 リレーショナルデータベースとは](#21-リレーショナルデータベースとは)
- [2.2 SQLとは](#22-sqlとは)
- [2.3 PostgreSQLとは](#23-postgresqlとは)
- [2.4 PostgreSQLの環境準備](#24-postgresqlの環境準備)
- [2.5 データベースクライアントツールの導入](#25-データベースクライアントツールの導入)
- [2.6 データベースの作成とテーブルの定義](#26-データベースの作成とテーブルの定義)
- [2.7 SQLの基本的な構文](#27-sqlの基本的な構文)
- [2.8 GROUP BYと集約関数](#28-group-byと集約関数)
- [2.9 結合](#29-結合)
- [2.10 トランザクション](#210-トランザクション)

### 2.1 リレーショナルデータベースとは
 **リレーショナルデータベース(RDB)** はデータ構造の一種であり、表のような形でデータを管理するシステムです。例えば以下ようなものです。

|学籍番号 |学科      |氏名                |
|--------|----------|------------------- |
|241004  |化学       |ヒソカ モロウ       |
|242021  |生物科学   |ゴン フリークス      |
|241015  |電気電子   |キルア ゾルディック  |

この表では、学籍番号に対して、学科や氏名というデータが結び付けられています。このように、データとデータに関係が定義されているのが、リレーショナルデータベース(RDB)です。

RDBを構成する基本的な概念として、 **テーブル、レコード(行)、カラム(列)、フィールド** があります

![qiita_2_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/b4f81e25-4077-ae6d-af8b-f9aa9c5c55fd.png)

- **テーブル**
データベースにおける構成要素で、何らかのデータのまとまり。
上の例では表全体のことを指し、学籍番号、学科、氏名をひとまとめにしたデータの集まりである。
- **レコード(行)**
表における横方向の値のまとまり。特定のデータ。

- **カラム(列)**
表における縦方向の値のまとまり。データの属性。

- **フィールド**
表における特定の値。特定のカラムの特定のレコード。

### 2.2 SQLとは
RDBを使う上で欠かせないのが **SQL** です。
SQLはRDB上のデータを取得、作成、変更、削除するための言語です。
SQLを使用することで、様々なデータ処理を実現できます。

### 2.3 PostgreSQLとは
RDBを作成し、管理・更新するためのシステムが　**リレーショナル データベース管理システム** **(RDBMS)** です。これまで様々なRDBMSが開発されており、その一つが **PostgreSQL** です
>PostgreSQLは強力なオープンソースのオブジェクトリレーショナルデータベースシステムで、35年以上にわたる活発な開発により、信頼性、機能の堅牢性、パフォーマンスにおいて高い評価を得ています。
>
>
>PostgreSQLのインストール方法や使用方法については、公式ドキュメントに豊富な情報があります。オープンソースコミュニティは、PostgreSQLに精通し、どのように動作するかを発見し、キャリアの機会を見つけるのに役立つ多くの場所を提供しています。コミュニティに参加する方法についてはこちらを参照してください。

PostgreSQL(https://www.postgresql.org/)

### 2.4 PostgreSQLの環境準備
[前回の記事](https://qiita.com/minoruishihara/items/a76b53c779099d217566)で紹介したDockerを使って開発環境を構築します。前回の記事を読んでいない人も、手順に従えば環境構築が可能ですし、ついでにDockerの簡単な使い方もつかめると思います。あらかじめインストールだけ行ってください。([インストール手順](https://qiita.com/minoruishihara/items/a76b53c779099d217566#13-dockerのインストール))

#### 1.Dockerfileの作成
新たにのフォルダを作成し、その中に以下の2つのファイルを作成します。

```dockerfile:Dockerfile
FROM postgres:16.0-bullseye
```

```yaml:docker-compose.yaml
version: "3.8"
services:
  postgres:
    container_name: postgres
    build:
      context: .
      dockerfile: Dockerfile
    image: postgres
    volumes:
      - ./data:/var/lib/postgresql/data
    ports:
      - 5432:5432
    tty: true
    stdin_open: true
    env_file:
      - ./.env
```

#### 2.設定ファイルの作成
PostgreSQLの設定のために、.envファイルをdockerから読み込んで環境変数を設定します。
ユーザー名とパスワードは適宜変更してください。

```.env
PGHOST=0.0.0.0 #すべてのネットワークインタフェースにバインドする
POSTGRES_USER=test_user #データベースにアクセスするためのユーザー名
POSTGRES_PASSWORD=test_password #データベースにアクセスするためのパスワード
PGPORT=5432
```

#### 3.コンテナの立ち上げ
```bash:docker-composeによるコンテナの起動
docker-compose up
```

### 2.5 データベースクライアントツールの導入
DBeaverの公式サイトからインストーラダウンロードして、セットアップガイドに沿ってインストールしてください。

https://dbeaver.io/download/

インストールして初めてDBeaverを起動すると、以下のような画面が出ると思います。この画面でPostgreSQLをクリックして、新しい接続を作成してください。

![スクリーンショット 2024-07-02 225339.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/0be968a4-565a-c09e-5d26-c69f70a544f1.png)

接続の設定画面が出ます。
`Host`: localhost, `port`: 5432
`Database`: test_user
`ユーザー名`: test_uset(設定ファイルで設定したユーザー名)
`パスワード`: test_password(設定ファイルで設定したパスワード) 
と設定して、左下のテスト接続ボタンをクリックしてください。

![スクリーンショット 2024-07-02 225454.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/bac7f51a-64a9-deb7-dc2c-a53fa4189c1f.png)

以下の表示が出れば接続成功です。
OKをクリックして右下の終了から設定画面を閉じてください。

![スクリーンショット 2024-07-02 225506.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/6601ae35-574e-1747-d7db-4b678acbfe33.png)

接続を完了した後の画面がこちらになります。
左側のパネルに接続したホストの一覧が表示されます。

![スクリーンショット 2024-07-02 225532.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/1d68ed2d-24e1-7193-9f6f-eb9856b41379.png)

### 2.6 データベースの作成とテーブルの定義

メニュバーから `SQLエディタ` -> `SQLコンソールを開く` をクリックします

![スクリーンショット 2024-07-02 234023.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/61ab6253-aeb1-e4f6-1605-1e0ab158bca5.png)

SQLコンソールが開きます。
ここにSQLを入力し、実行できます。

![スクリーンショット 2024-07-02 234040.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/f7dc9f98-7fe6-84de-a45f-3b6bb7b96073.png)

以下のSQL文をコピー&ペーストしてください

```sql:データベースの作成
create database test_db with owner='test_user' encoding='utf-8';
```

SQLコンソール左上の `SQL文を実行する` をクリックしてください

![スクリーンショット 2024-07-02 234329.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/91a55ba3-53d8-844f-477e-47e879750396.png)

右側のパネルの項目 `データベース` を右クリックして一番下の `更新` をクリックします

![スクリーンショット 2024-07-03 001017.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/bce75d9f-3691-07a3-d108-750bdd7920ae.png)

新しくデータベース `test_db` が作成されています

![スクリーンショット 2024-07-03 001240.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/204bbbab-a94d-c6ad-fe3a-9a86f98feef5.png)

同じ要領で以下のSQLを実行し、テーブルを作成します。

```sql:テーブルの作成
CREATE TABLE test_db.public.test_table (
	id int8 NOT NULL,
	"name" varchar NOT NULL,
	age int8,
	CONSTRAINT test_table_pk PRIMARY KEY (id)
);
```

`テーブル` を右クリックして `更新` からテーブルの表示を更新します。

![スクリーンショット 2024-07-02 232659.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/768217ed-2496-c650-7890-8653bf9add89.png)

新しくテーブルが作成されています

![スクリーンショット 2024-07-02 232719.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/5e2246fd-70f7-25fa-986a-ea69d6a9d271.png)

#### テスト用データの読み込み
1. 以下のリンクを 右クリック -> 名前を付けてリンク先を保存 をクリックしてCSVファイルをダウンロードします
[ダウンロードリンク](https://raw.githubusercontent.com/MinoruIshihara/intro-webapp/main/psql-intro/test_table.csv)
2. `test_table` を右クリック -> データのインポート をクリックします
![SnapCrab_NoName_2024-7-3_3-44-39_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/d9e24973-88ca-2cd9-43a6-c362d41083d8.png)

3. source typeからCSVを選択して、次へを押してダウンロードしたCSVファイルを選択します。
![SnapCrab_No-0000.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/a2fa96b1-3e67-e745-f647-9fd0b2e10ea3.png)

4. そのまま次へ -> 次へ -> 続行と押していくと、このようにデータが読み込まれます
![SnapCrab_NoName_2024-7-3_3-45-51_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/c75e5a10-1288-3327-972d-6d0f81a02e7f.png)

### 2.7 SQLの基本的な構文
#### 取得
データの取得にはSELECT文を使います

```sql
SELECT [カラム名1], [カラム名2], ... FROM [テーブル名]
```

先程と同じく、メニュバーから `SQLエディタ` -> `SQLコンソールを開く` をクリックしてSQLコンソールを開き、それぞれ実行して結果を確認してみてください。

```sql:SELECT文(全抽出)
SELECT * FROM test_table;
```

```sql:SELECT文(カラム指定)
SELECT name, age FROM test_table;
```
また、`WHERE`句を用いて取得するデータの条件を検索することができます

```sql
SELECT [カラム名1], [カラム名2], ... FROM [テーブル名] WHERE [条件]
```
条件には`=` `<` `<=` `>` `>=` `<>` `!=`の比較演算子、`AND` `OR` `NOT`の論理演算子が使えます。 

```sql:WHERE句による条件指定
SELECT * FROM test_table WHERE age >= 20;
```

```sql:WHERE句による条件指定(AND)
SELECT * FROM test_table WHERE age >= 20 AND age < 30;
```

値が `NULL` である/でないレコードを抽出するには、`IS NULL`/`IS NOT NULL`を使います。

```sql:NULL抽出
SELECT * FROM test_table WHERE age IS NULL;
```

```sql:NULL除外
SELECT * FROM test_table WHERE age IS NOT NULL;
```

`ORDER BY`を使えば、結果をソートすることができます
末尾に`desc`を指定すれば降順に、未指定もしくは`asc`で昇順になります。

```sql:ソート(昇順)
SELECT * FROM test_table WHERE age IS NOT NULL ORDER BY age;
```

```sql:ソート(降順)
SELECT * FROM test_table WHERE age IS NOT NULL ORDER BY age desc;
```

#### 追加

新しいデータをテーブルに追加するには `INSERT` 文を使います。

```sql
INSERT INTO [テーブル名] ([カラム名1], [カラム名2], ...) VALUES ([値1], [値2], ...);
```

```sql:INSERTによる追加
INSERT INTO test_table (id, name, age) VALUES (29, '夏油傑', 27);
```

左側のパネルからテーブルをダブルクリックしてテーブルを確認してください。

![SnapCrab_No-0001.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/e76f83da-b84c-b6ec-7944-94e771add776.png)


#### 更新

既存のレコード、フィールドを更新するには `UPDATE` 文を使います。`WHERE` 句で更新対象を絞り込むことができます。

```sql
UPDATE [テーブル名] SET [カラム名1] = [値1], [カラム名2] = [値2], ... WHERE [条件];
```

```sql:UPDATE文の実行例
UPDATE test_table SET name = '究極 メカ丸' WHERE name = '与 幸吉';
```

#### 削除

データを削除するには `DELETE` 文を使います。こちらも `WHERE` 句で削除対象を絞り込むことができます。

```sql
DELETE FROM [テーブル名] WHERE [条件];
```

```sql:DELETE文による削除
DELETE FROM test_table WHERE id = 29;
```

### 2.8 GROUP BYと集約関数

`GROUP BY` 句は、指定したカラムの値でカラムをグループ化し、集約関数と共に使用することで、グループごとの集計結果を得ることができます。

```sql
SELECT [カラム名], [集約関数]([カラム名]) FROM [テーブル名] GROUP BY [カラム名];
```

PostgreSQLで利用できる代表的な集約関数は以下の通りです。

*   `COUNT`: レコード数
*   `SUM`: 合計値
*   `AVG`: 平均値
*   `MAX`: 最大値
*   `MIN`: 最小値

```sql:GROUP BY句と集約関数の実行例
SELECT age, COUNT(id) FROM test_table GROUP BY age;
```

### 2.9 結合
SQLでは、複数のテーブルを結合してデータを取得することができます。

まず、新しいテーブルを作成します。
```sql
CREATE TABLE public.char_organization (
	char_id int8 NOT NULL,
	organization varchar NOT NULL,
	CONSTRAINT char_organization_unique UNIQUE (char_id,organization),
	CONSTRAINT char_organization_test_table_fk FOREIGN KEY (char_id) REFERENCES public.test_table(id) ON DELETE CASCADE ON UPDATE CASCADE
);
```

以下のリンクからCSVをダウンロードし、新しいテーブルにデータをインポートしてください。
[ダウンロードリンク](https://raw.githubusercontent.com/MinoruIshihara/intro-webapp/main/psql-intro/char_organization.csv)

結合を行うには、`JOIN` を使用します。

```sql
SELECT [カラム名] FROM [テーブル名1] JOIN [テーブル名2] ON [テーブル名1].[カラム名] = [テーブル名2].[カラム名];
```

実際に先ほど作成したテーブルと結合を行ってみましょう

```sql:テーブルの結合
SELECT * FROM test_table JOIN char_organization ON test_table.ID = char_organization.char_id;
```

`JOIN` では指定したキー(ここでは`test_table`の`ID`と`char_organization`の`char_id`)が一致するレコード同士が結合されて抽出されます。

PostgreSQLの `JOIN` は正確には `INNER JOIN` であり、SQLには以下の4つの結合方法があります。

*   `INNER JOIN`: 両方のテーブルに一致するレコードのみを取得
*   `LEFT JOIN`: 左側のテーブルのすべてのレコードと、それに一致する右側のレコードを結合したものを取得
*   `RIGHT JOIN`: 右側のテーブルのすべてのレコードと、それに一致する左側のレコードを取得
*   `FULL JOIN`: 両方のテーブルのすべてのレコードを取得
*   `CROSS JOIN`: 一致不一致にかかわらずすべての組み合わせを取得

詳細はこちらを参考

https://www.postgresql.jp/document/7.2/tutorial/tutorial-join.html

### 2.10 トランザクション

トランザクションとは、複数のSQL文をひとまとまりの処理として実行する仕組みです。実行結果は失敗か成功のどちらかで、処理の途中で終了することはありません。
トランザクションを利用することで、データの整合性を保つことができます。

わかりやすい解説↓

https://wa3.i-3-i.info/word142.html

```sql:トランザクションによる複数クエリの実行
BEGIN;
INSERT INTO test_table (id, name, age) VALUES (30, '禪院直毘人', 71);
INSERT INTO char_organization (char_id, organization) VALUES (30, '禪院家');
COMMIT;
```
左側のSQLスクリプトを実行するをクリックして、SQL全体を実行してください

![SnapCrab_No-0002.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/263665/1e7022a9-7b6c-b4d1-8dc3-51e28ad588a6.png)

トランザクション中に問題が発生した場合は、`ROLLBACK` で処理をキャンセルすることができます。

```sql
ROLLBACK;
```

### 参考文献・引用元
 - [1] PostgreSQL Global Development Group.
PostgreSQL: The World's Most Advanced Open Source Relational Database. PostgreSQL. https://www.postgresql.org/

 - [2] 日本テレビ「HUNTER×HUNTER｜日本テレビ」(最終閲覧: 2024/07/03)
https://www.ntv.co.jp/hunterhunter/character/index.html

 - [3] 呪術部「呪術廻戦年齢一覧！誕生日も見てみる」(最終閲覧: 2024/07/03)
https://jujutsu-bu.com/character-age-birthday/

 - [4] 日本PostgreSQLユーザ会「PostgreSQL 16.0文書」(最終閲覧: 2024/07/03)
https://www.postgresql.jp/document/16/html/

 - [5] 「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典「トランザクションとは」(最終閲覧: 2024/07/03)
 https://wa3.i-3-i.info/word142.html