PostgreSQL入門

# はじめに
本記事では、サンプルを用いて、**PostgreSQL**の基礎的な構文一覧を、視覚的かつ簡単に学ぶことを目的とします。**PostgreSQL**は**RDBMS**(**Relational Database Management System**)の一つで、以下の分類順で、構文を解説していきます。

- ①データ定義言語 DDL (Data Definition Language)
- ②データ操作言語 DML (Data Manipulation Language)
- ③トランザクション制御言語 TCL (Transaction Control Language)
- ④データ制御言語 DCL (Data Control Language)

本記事の説明では[PostgreSQL-Sample](https://github.com/Ryosuke1025/PostgreSQL-Sample)を使用しており、ローカルホストで**PostgreSQL**に接続して実行しています。

# 用語定義
**カラム**・・・テーブルの列(属性)

**レコード**・・・テーブルの行(データ1件)

**フィールド**・・・テーブルの列と行が交差する箇所(値)

**候補キー**・・・レコードを一意に識別するためのカラム、またはカラムの組み合わせ

**主キー**・・・候補キーのうちの1つ

**外部キー**・・・別テーブルの主キーを参照することで、テーブル間の関連付けができるカラム

**クエリ**・・・データベースに対する質問・要求（SQL文）

**トランザクション**・・・データベースなどのシステムにおける一連の処理の単位

# SQLの基礎的な構文一覧
## ①データ定義言語（DDL）
まずはじめに、**主キー**を`id`とした顧客テーブルを作成します。

### CREATE TABLE（テーブル作成）
```sql
CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,         -- 顧客ID
    name TEXT NOT NULL,            -- 顧客名
    email TEXT NOT NULL UNIQUE,    -- メールアドレス
    phone TEXT,                    -- 電話番号
    address TEXT                   -- 住所
);

```

`SERIAL`は自動的に連番（1, 2, 3, ...）を生成する整数型カラムを作るための型です。このテーブルだと、`id`が**主キー**、`email`が**候補キー**になります。また、`name`, `email`は必ず値が入ります。

### ALTER TABLE（テーブル変更）
```sql
ALTER TABLE customers ADD COLUMN age INTEGER;
```

これで`age`(年齢)という新たなカラムを追加できます。

### DROP TABLE（テーブル削除）
```sql
DROP TABLE IF EXISTS customers;
```

### CHECK（制約）
```sql
CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,         -- 顧客ID
    name TEXT NOT NULL,            -- 顧客名
    email TEXT NOT NULL UNIQUE,    -- メールアドレス
    phone TEXT CHECK (             -- 電話番号
        phone IS NULL OR (
            LENGTH(phone) >= 10 AND 
            LENGTH(phone) <= 15
        )
    ),                    
    address TEXT                   -- 住所
);
```
このように、`CHECK`を使うと、特定のカラムに値の制約を加えることができます。今回の場合だと、10文字以上, 15文字以下という制約を加えており、これを満たさないレコードを挿入しようとすると、実行時にエラーになります。

## ②データ操作言語（DML）
### SELECT（基本的なデータ取得）
```sql
SELECT * FROM customers;
```
`*`を付けることで、全てのレコードに対するカラムの値を取得できます。ただし、以下のように、明示的に取得するカラムを書くこともできます。

```sql
SELECT id, name, email, phone, address FROM customers;
```

### INSERT（データ挿入）
```sql
INSERT INTO customers (name, email, phone, address) VALUES
  ('山田太郎', 'taro.yamada@example.com', '03-1234-5678', '東京都新宿区新宿1-1-1'),
  ('佐藤花子', 'hanako.sato@example.com', '06-8765-4321', '大阪府大阪市北区梅田2-2-2'),
  ('鈴木一郎', 'ichiro.suzuki@example.com', '045-123-4567', '神奈川県横浜市西区みなとみらい3-3-3'),
  ('田中美香', 'mika.tanaka@example.com', '052-987-6543', '愛知県名古屋市中区栄4-4-4'),
  ('高橋健太', 'kenta.takahashi@example.com', '092-345-6789', '福岡県福岡市博多区博多5-5-5');
```
![スクリーンショット 2025-05-20 0.15.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/3b8e973f-5195-4a95-8a33-591b5d6dd16c.png)

### WHERE（条件指定）
```sql
SELECT * FROM customers
WHERE address = '愛知県名古屋市中区栄4-4-4';
```
![スクリーンショット 2025-05-22 1.10.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/254b2d8b-5f9f-48b3-81d0-6beccaa2aea5.png)

このクエリは単一の条件でフィルタリングしていますが、以下のように`AND`演算を使って、複数の条件でフィルタリングすることもできます。

```sql
SELECT * FROM customers
WHERE address = '愛知県名古屋市中区栄4-4-4'
AND phone = '052-987-6543';
```

### IN（値リスト検索）

```sql
SELECT * FROM customers
WHERE name IN ('佐藤花子', '鈴木一郎');
```

![スクリーンショット 2025-05-26 23.18.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/0fe4bc09-c22c-40f2-b396-2b207693cfb3.png)

`WHERE`のみで表現する場合以下のように冗長になってしまいます。

```sql
SELECT * FROM customers
WHERE name = '佐藤花子' OR 
name = '鈴木一郎';
```

このように、いずれかの値に一致していれば良いという場合に、簡潔に書けるのが利点です。

### LIKE（パターンマッチング）
```sql
SELECT * FROM customers
WHERE address LIKE '愛知県%';
```

これで、`WHERE`の例と同じレコードが取り出せます。
`LIKE`は部分一致演算で、完全一致していなくても取り出すことができます。

### UPDATE（データ更新）
```sql
UPDATE customers
SET phone = '03-9876-5432', address = '東京都渋谷区渋谷1-2-3'
WHERE name = '山田太郎';
SELECT * FROM customers
```
![スクリーンショット 2025-05-21 23.40.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/fae5fcfa-6545-4705-a6f6-25589fa67347.png)

### DELETE（データ削除）
```sql
DELETE FROM customers
WHERE name = '高橋健太';
SELECT * FROM customers
```
![スクリーンショット 2025-05-21 23.47.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/05992369-a5d3-4882-88b8-3b49116f22f7.png)

### ORDER BY（並べ替え）
```sql
SELECT * FROM customers
ORDER BY phone;
```

![スクリーンショット 2025-05-21 23.59.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/87e68288-3689-49c4-9718-d1ccffc23974.png)

これは昇順に並べていますが、以下のように`DESC`を付与すると、降順に並べられます。

```sql
SELECT * FROM customers
ORDER BY phone DESC;
```

### GROUP BY（グループ化）
```sql
SELECT 
    substring(address from '^([^都道府県]+[都道府県])') as prefecture,
    COUNT(*) as customer_count
FROM customers
GROUP BY prefecture;
```

![スクリーンショット 2025-05-22 0.17.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/8596dfbb-510f-4c9f-b2ba-ca633fab08ef.png)

正規表現について、本記事では取り上げないですが、このように書くと、あるカラムの値ごとにグループ化し、それぞれのグループに含まれるレコード数を取り出すことができます。

### HAVING（グループに対する条件指定）
```sql
SELECT 
    substring(address from '^([^都道府県]+[都道府県])') as prefecture,
    COUNT(*) as customer_count
FROM customers
GROUP BY prefecture
HAVING substring(address from '^([^都道府県]+[都道府県])') LIKE '%県';
```

![スクリーンショット 2025-05-22 1.04.34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/f00b5d2e-fe89-4475-9207-b6a277141799.png)

### LIMIT/OFFSET（結果件数の制限）
``` sql
SELECT * FROM customers ORDER BY id LIMIT 3;
```
![スクリーンショット 2025-05-25 23.43.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/b5ff8d32-17a1-48e2-9712-060ce069ff07.png)

また、`OFFSET`を使うことで、特定の件数をスキップできます。以下の例だと、最初の3件スキップして、2件取得しています。
 ```sql
SELECT * FROM customers
ORDER BY id
LIMIT 2 OFFSET 3;
```

---

ここで、**主キー**を`order_id`, **外部キー**を`customer_id`とした**注文テーブル**を新たに作成します。
```sql
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  price INTEGER NOT NULL,
  order_date TEXT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);
```

そして、以下のように任意のレコードを追加します。

```sql
INSERT INTO orders (order_id, customer_id, product_name, price, order_date) VALUES
  (101, 1, 'ノートパソコン', 120000, '2023-05-15'),
  (102, 3, 'スマートフォン', 85000, '2023-05-16'),
  (103, 1, 'ワイヤレスイヤホン', 25000, '2023-05-20'),
  (104, 2, 'タブレット', 60000, '2023-05-22'),
  (105, 4, 'スマートウォッチ', 35000, '2023-05-25');
```

![スクリーンショット 2025-05-23 0.21.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/718b9fc9-24f3-4a8b-98fa-e5cc07ead101.png)

また、`customers`に1件レコードを追加します。

```sql
INSERT INTO customers (name, email, phone, address) VALUES
  ('高橋健太', 'kenta.takahashi@example.com', '092-345-6789', '福岡県福岡市博多区博多5-5-5');
```

![スクリーンショット 2025-05-24 0.50.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/c215c870-9444-463c-9b34-553797a0a938.png)

---
### CASE式（条件分岐）
```sql
SELECT 
  product_name,
  price,
  CASE 
    WHEN price >= 100000 THEN '高額商品'
    WHEN price >= 50000 THEN '中額商品'
    WHEN price >= 20000 THEN '低額商品'
    ELSE '格安商品'
  END AS price_category
FROM orders
ORDER BY price DESC;
```

![スクリーンショット 2025-05-25 23.16.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/ffb345cd-9480-451f-a43c-234e60f63c00.png)

### INNER JOIN（内部結合）
```sql
SELECT c.name, c.email, o.product_name, o.price, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
```

![スクリーンショット 2025-05-23 0.49.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/ce1e8d97-b6bf-4a44-8bda-23311b5c8c2a.png)

このように、外部キーによって、2つのテーブルを結合することもできます。**INNER JOIN**では、2つのテーブルで一致しているレコードのみ取り出して、結合します。



### LEFT JOIN, RIGHT JOIN, FULL JOIN（外部結合）

```sql
SELECT c.name, c.email, c.address, o.product_name, o.price, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
```
![スクリーンショット 2025-05-24 0.46.16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/b8a0d6db-032d-4e8c-8a99-5ae32f22a2dc.png)

**LEFT JOIN**は、左テーブルの全レコードを取り出して、埋まっていないフィールドを**NULL**として結合します。同様に、**RIGHT JOIN**なら右テーブルのレコード、**FULL JOIN**なら両方のテーブル全てのレコードを取り出して結合します。今回は、左テーブルが`customers`, 右テーブルが`orders`となります。

### BETWEEN（範囲検索）
```sql
SELECT * FROM orders
WHERE price BETWEEN 20000 AND 50000
```

![スクリーンショット 2025-05-25 23.37.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/b58ec0d8-b127-40d0-9c69-3038ab8ed830.png)

--- 


ここで、1人の電話番号を`NULL`にしてみます。

```sql
UPDATE customers
SET phone = NULL
WHERE name = '田中美香';
```

![スクリーンショット 2025-05-26 22.44.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/abecffcf-9cd3-41ae-9373-ada885fbebcb.png)

---

### IS NULL, IS NOT NULL（NULL値の判定）
```sql
SELECT name, email, phone
FROM customers
WHERE phone IS NULL;
```

![スクリーンショット 2025-05-26 22.46.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/cbdc31d7-61f9-4527-8182-140ce810acb8.png)

`IS NULL`で、`NULL`かどうかを判定できます。逆に`IS NOT NULL`で、`NULL`ではないかどうかを判定できます。

### COUNT, SUM, AVG, MAX, MIN（集計関数）

```sql
SELECT COUNT(*) AS total_customers FROM customers;
```

![スクリーンショット 2025-05-26 23.25.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/a168d1d2-348e-4276-ab13-dc1befb175c0.png)

```sql
SELECT SUM(price) AS total_sales FROM orders;
```

![スクリーンショット 2025-05-26 23.26.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/664a535e-ff7f-4e8c-ad45-82c5197a6635.png)

`COUNT`は指定したレコードの数、`SUM`は合計を算出します。同様に、`AVG`は平均、`MAX`は最大値、`MIN`は最小値を算出します。

### DISTINCT（重複除去）

```sql
SELECT DISTINCT customer_id FROM orders;
```

![スクリーンショット 2025-05-27 23.07.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/43ed1c77-da3c-4016-9738-ca9a49010947.png)

---

ここで新たに、従業員テーブルを作成して、任意のデータを挿入します。

```sql
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    address TEXT
);
```

```sql
INSERT INTO employees (employee_id, name, email, phone, address) VALUES
  (11, '山田太郎', 'taro.yamada@company.com', '03-1111-2222', '東京都千代田区丸の内1-1-1'),
  (12, '佐藤花子', 'hanako.sato@company.com', '06-2222-3333', '大阪府大阪市中央区本町2-2-2'),
  (13, '田中一郎', 'ichiro.tanaka@company.com', '052-3333-4444', '愛知県名古屋市東区東桜3-3-3'),
  (14, '高橋健太', 'kenta.takahashi@company.com', '092-4444-5555', '福岡県福岡市中央区天神4-4-4');
```

![スクリーンショット 2025-05-27 23.32.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/e309aba0-a3cd-4a72-9cca-b66e7af37900.png)

---

### UNION, UNION ALL, INTERSECT, EXCEPT（集合演算）

```sql
SELECT name FROM customers
UNION
SELECT name FROM employees;
```

![スクリーンショット 2025-05-28 1.46.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/908efa66-4c8a-412f-ac65-fdea8463f9f6.png)

`UNION`は、2つのクエリ結果の和(重複を許容しない)を抽出します。`UNION ALL`は和(重複を許容する)、同様に、`INTERSECT`は積、`EXCEPT`は差(1つ目のクエリ結果にあって、2つ目のクエリ結果にないもの)を抽出します。

### サブクエリ
```sql
SELECT order_id, customer_id, product_name, price
FROM orders
WHERE price > (SELECT AVG(price) FROM orders);
```

今回の例では、`orders`内の`price`の平均値を取り出し、その値よりも大きい価格を持つレコードを取り出しています。つまり、サブクエリとは**クエリの中のクエリ**です。

## ③トランザクション制御言語（TCL）
トランザクション制御言語を学ぶ前に、**トランザクションの4つの特性**(**ACID 特性**)から学びましょう。以下のようなシナリオを考えます。

ユーザーAの口座残高：10万円
ユーザーBの口座残高：5万円
ユーザーCの口座残高：7万5千円
ユーザーA → ユーザーBに 1万円を送金する

|特性|用語定義|例を用いた説明|
|---|---|---|
|**原子性（Atomicity）**|トランザクション内の処理は全て成功するか、全て失敗して何も起きなかったことになる。|Aの残高が1万円減り、Bの残高が1万円増える。途中でエラーが発生したらどちらの口座も変更されない。|
|**一貫性(Consistency)**|トランザクション前後で、システム全体の整合性が保たれる。|処理後のAとBの合計残高は 15万円で変わらない。Bだけが増えたり、Aが減っただけになることはない。|
|**独立性（Isolation）**|他のトランザクションの影響を受けずに処理される（同時実行でも矛盾しない）。|同時にCがAに振込を行っていても、Aの残高が衝突しないように分離されて処理される。|
|**永続性（Durability）**|トランザクションが完了したら、結果は確実に保存され、システム障害でも失われない。|振込完了後にサーバーが落ちても、Aの減額とBの増額は記録として残る。ログやディスクへの書き込みで保証される。|

上記の例を元に、以下のような口座アカウントテーブルを作成する。

```sql
CREATE TABLE bank_accounts (
    account_id SERIAL PRIMARY KEY,
    account_name VARCHAR(50) NOT NULL,
    balance INTEGER NOT NULL
);

INSERT INTO bank_accounts (account_name, balance) VALUES
('山田太郎', 100000),
('佐藤花子', 50000),
('田中次郎', 75000);
```

![スクリーンショット 2025-06-01 15.46.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/3eb8e417-8a5f-448c-aeda-a7bc0d7b28db.png)

### BEGIN, COMMIT(正常終了)
```sql
-- BEGINでトランザクションを開始
BEGIN;

-- 山田太郎から佐藤花子に10000円送金
UPDATE bank_accounts SET balance = balance - 10000 WHERE account_name = '山田太郎';
UPDATE bank_accounts SET balance = balance + 10000 WHERE account_name = '佐藤花子';

-- 変更を確認
SELECT * FROM bank_accounts;

-- COMMITで変更を確定
COMMIT;

-- 確定後の状態
SELECT * FROM bank_accounts
ORDER BY account_id;
```

コミット前後のテーブルが以下の通りです。

![スクリーンショット 2025-06-01 16.32.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/2af7f77f-241a-42a5-8be0-2693f2396120.png)

### BEGIN, ROLLBACK(異常終了)

```sql
-- 新しいトランザクション開始
BEGIN;

-- 田中次郎から山田太郎に10000円送金
UPDATE bank_accounts SET balance = balance - 10000 WHERE account_name = '田中次郎';
UPDATE bank_accounts SET balance = balance + 10000 WHERE account_name = '山田太郎';

-- 変更を確認
SELECT * FROM bank_accounts ORDER BY account_id;
```

`COMMIT`前は以下のように、仮の変更後のデータが見えるになっていますが、実際は書き換えられていません。

![スクリーンショット 2025-06-01 16.36.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/9f9a55dd-786b-40cd-97f1-96184c09dbfa.png)

```sql
-- ROLLBACKで変更を取り消し
ROLLBACK;

-- 取り消し後の状態（元に戻る）
SELECT * FROM bank_accounts ORDER BY account_id;
```

`ROLLBACK`後、データが元に戻されます。
![スクリーンショット 2025-06-01 16.37.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638200/06af3023-440d-465f-bb1a-8fa1699e2761.png)

## ④データ制御言語（DCL）

### CREATE USER, DROP USER（ユーザー管理）
```sql
CREATE USER user1;
```
上記のよう記述するとユーザーを作成できます。

```sql
DROP USER user1;
```

逆に、このように記述するとユーザーを削除できます。

### GRANT, REVOKE（権限管理）

```sql
GRANT SELECT ON customers TO user1;
```
これで、ユーザー1に対して`customers`テーブルへの`SELECT`操作の権限を与えることができます。

```sql
REVOKE SELECT ON customers FROM user1;
```
逆に、上記のように記述すると、ユーザー1に対して`customers`テーブルへの`SELECT`操作の権限を剥奪することができます。

```sql
GRANT SELECT, INSERT, UPDATE ON customers TO user1;
```

上記のように、複数の操作に対して一括で権限を付与(剥奪)することもできます。

```sql
GRANT ALL PRIVILEGES ON customers TO user1;
```

また、上記のように記述すると、全ての操作に対して一括で権限を付与(剥奪)することもできます

なお、本番環境では管理者のみが実施し、設定したパスワードを使って権限を与えます。

# 今回取り扱っていない、やや難易度の高い重要な構文
## ①データ定義言語（DDL）
- **CREATE INDEX（インデックス作成）**
- **CREATE VIEW（ビュー作成）**
- **TRUNCATE TABLE（テーブルデータ全削除）**

## ②データ操作言語（DML）
- **EXISTS/NOT EXISTS（存在判定・非存在判定）**
- **WITH（共通テーブル式・一時的な名前付きクエリ）**
- **SELF JOIN（自己結合）**
- **COALESCE（NULL値の置換）**
- **RETURNING句（INSERT/UPDATE後の値取得）**
- **ON CONFLICT（UPSERT操作）**
- **ILIKE（大文字小文字を無視したLIKE）**
- **EXPLAIN（実行計画表示）**
- **文字列関数（SUBSTRING, LENGTH, CONCAT等）**
- **日付関数（CURRENT_DATE, EXTRACT等）**
- **数値関数（ROUND, CEILING, FLOOR等）**
- **型変換関数（CAST, CONVERT等）**
- **Window関数（ROW_NUMBER, RANK等）**
- **相関サブクエリ（外側クエリと関連するサブクエリ）**

## ③トランザクション制御言語（TCL）
- **SET TRANSACTION ISOLATION LEVEL（分離レベル）**

## ④データ制御言語（DCL）
- **CREATE ROLE, DROP ROLE（ロール管理）**

# おわりに
本記事では、できる限りシンプルな例を用いて書く構文を説明させて頂きました。ただし、実務ではもっと複雑なクエリを扱うと思います。また、データベースを学ぶ上で、データベース操作だけではなく、データベース設計も学ぶ必要があります。データベース設計に関しては、また今度記事を書こうと思っています。