H2 Database（H2DB）を使用したSQLチュートリアル

# H2 Database（H2DB）を使用したSQLチュートリアル

このチュートリアルでは、**H2 Database（H2DB）**を活用して、リレーショナルデータベースの基本概念から高度なSQLクエリまでを詳細に解説します。以下の内容を網羅的に学ぶことで、H2DBを用いた効果的なデータベース操作が可能となります。

## 目次

1. [リレーショナルデータベースとSQLの概要](#1-リレーショナルデータベースとsqlの概要)
2. [SQLによるデータ検索](#2-sqlによるデータ検索)
   1. [全件検索](#21-全件検索)
   2. [探索条件](#22-探索条件)
   3. [結合](#23-結合)
   4. [集合関数](#24-集合関数)
   5. [グループ化](#25-グループ化)
   6. [並び替え](#26-並び替え)
   7. [集合演算](#27-集合演算)
   8. [副問合せ](#28-副問合せ)
3. [SQLによるデータ追加/更新/削除](#3-sqlによるデータ追加更新削除)
4. [まとめ](#4-まとめ)

---

## 1. リレーショナルデータベースとSQLの概要

### 1.1 リレーショナルデータベースとは

**リレーショナルデータベース**（Relational Database）は、データを**テーブル**（表）形式で管理するデータベースの一種です。各テーブルは**行**（レコード）と**列**（フィールド）から構成され、テーブル間の関係性（リレーション）を定義することで、データの整合性と効率的なアクセスを実現します。

**主な特徴：**

- **テーブル構造**：データは行と列で整理され、各列には特定のデータ型が割り当てられます。
- **主キーと外部キー**：主キーは各レコードを一意に識別し、外部キーは他のテーブルとの関係性を定義します。
- **SQLの使用**：データの操作やクエリには**SQL（Structured Query Language）**が使用されます。

### 1.2 SQLの概要

**SQL（Structured Query Language）**は、リレーショナルデータベースを操作するための標準的な言語です。SQLを使用することで、データの検索、挿入、更新、削除、スキーマの定義などを行うことができます。

**SQLの主なカテゴリ：**

1. **データ定義言語（DDL）**：データベースやテーブルの構造を定義・変更する（例：`CREATE`, `ALTER`, `DROP`）。
2. **データ操作言語（DML）**：データの操作（例：`SELECT`, `INSERT`, `UPDATE`, `DELETE`）。
3. **データ制御言語（DCL）**：アクセス権限の制御（例：`GRANT`, `REVOKE`）。
4. **トランザクション制御言語（TCL）**：トランザクションの管理（例：`COMMIT`, `ROLLBACK`）。

---

## 2. SQLによるデータ検索

データベースから必要な情報を取得するためのクエリを作成する際に、SQLの`SELECT`文が主要な役割を果たします。以下では、`SELECT`文の基本から応用までを段階的に解説します。

### 2.1 全件検索

**全件検索**は、特定のテーブルからすべてのレコードを取得するクエリです。

**構文：**

```sql
SELECT * FROM テーブル名;
```

**例：**

`users`テーブルから全てのユーザー情報を取得する。

```sql
SELECT * FROM users;
```

**説明：**

- `SELECT *`：テーブルのすべての列を選択。
- `FROM users`：`users`テーブルからデータを取得。

**実行結果のイメージ：**

| user_id | username | email              | created_at          |
|---------|----------|--------------------|---------------------|
| 1       | alice    | alice@example.com  | 2024-04-01 10:00:00 |
| 2       | bob      | bob@example.com    | 2024-04-02 11:30:00 |
| ...     | ...      | ...                | ...                 |

---

### 2.2 探索条件

**探索条件**を指定して、特定の条件に一致するレコードのみを取得します。`WHERE`句を使用します。

**構文：**

```sql
SELECT 列名1, 列名2, ... FROM テーブル名 WHERE 条件;
```

**例1：特定のユーザーを取得**

`username`が`alice`であるユーザーを取得する。

```sql
SELECT * FROM users WHERE username = 'alice';
```

**例2：複数条件の指定**

`age`が25歳以上で、`NAME`が`Alice Johnson`の社員を取得する。

```sql
SELECT * FROM employees
WHERE age >= 25 AND NAME  = 'Alice Johnson';
```

**説明：**

- `WHERE`句で条件を指定。
- 複数の条件は`AND`や`OR`で組み合わせることが可能。

**比較演算子の主な種類：**

- `=` : 等しい
- `<>` または `!=` : 等しくない
- `>` : より大きい
- `<` : より小さい
- `>=` : 以上
- `<=` : 以下
- `LIKE` : パターンマッチング
- `IN` : 複数の値の中に含まれる
- `BETWEEN` : 範囲内にある
- `IS NULL` / `IS NOT NULL` : NULL値のチェック

**例3：パターンマッチング**

`email`が`@example.com`で終わるユーザーを取得する。

```sql
SELECT * FROM users WHERE email LIKE '%@example.com';
```

---

### 2.3 結合

**結合（JOIN）**を使用すると、複数のテーブルから関連するデータを組み合わせて取得できます。主な結合の種類には、**INNER JOIN**, **LEFT JOIN**, **RIGHT JOIN**, **FULL OUTER JOIN**などがあります。

#### 2.3.1 INNER JOIN

**INNER JOIN**は、結合条件に一致するレコードのみを返します。

**構文：**

```sql
SELECT 列名1, 列名2, ...
FROM テーブル1
INNER JOIN テーブル2 ON テーブル1.カラム = テーブル2.カラム;
```

**例：社員と部署の情報を結合して取得**

`employees`テーブルと`departments`テーブルを`dept_id`で結合する。

```sql
SELECT employees.emp_id, employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;
```

**説明：**

- `INNER JOIN`を使用して、`employees`と`departments`を`dept_id`で結合。
- 結果には、両方のテーブルに存在する`dept_id`のレコードのみが含まれる。

#### 2.3.2 LEFT JOIN

**LEFT JOIN**は、左側のテーブルのすべてのレコードと、結合条件に一致する右側のテーブルのレコードを返します。右側に一致するレコードがない場合、`NULL`が入ります。

**構文：**

```sql
SELECT 列名1, 列名2, ...
FROM テーブル1
LEFT JOIN テーブル2 ON テーブル1.カラム = テーブル2.カラム;
```

**例：すべての社員と、その所属部署（部署がない場合も含む）を取得**

```sql
SELECT employees.emp_id, employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;
```

#### 2.3.3 RIGHT JOIN

**RIGHT JOIN**は、右側のテーブルのすべてのレコードと、結合条件に一致する左側のテーブルのレコードを返します。左側に一致するレコードがない場合、`NULL`が入ります。

**構文：**

```sql
SELECT 列名1, 列名2, ...
FROM テーブル1
RIGHT JOIN テーブル2 ON テーブル1.カラム = テーブル2.カラム;
```

**例：すべての部署と、その部署に所属する社員（社員がいない場合も含む）を取得**

```sql
SELECT employees.emp_id, employees.name, departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;
```


**注意点：**

- 一部のデータベースシステム（例えば、MySQL）では`FULL OUTER JOIN`がサポートされていない場合があります。その場合は、`UNION`を使用して実現します。

---

### 2.4 集合関数

**集合関数（Aggregate Functions）**は、複数の行から単一の値を計算する関数です。主な集合関数には、`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`などがあります。

#### 2.4.1 COUNT

レコードの数をカウントします。

**例：`users`テーブルの総ユーザー数を取得**

```sql
SELECT COUNT(*) AS total_users FROM users;
```

**説明：**

- `COUNT(*)`はすべてのレコードをカウント。
- `AS total_users`は結果に別名を付ける。

#### 2.4.2 SUM

数値カラムの合計を計算します。

**例：` order_items `テーブルの金額の合計を取得**

```sql
SELECT SUM(price) AS total_price FROM order_items;
```

#### 2.4.3 AVG

数値カラムの平均値を計算します。

**例：`employees`テーブルの平均年齢を取得**

```sql
SELECT AVG(age) AS average_age FROM employees;
```

#### 2.4.4 MIN

カラムの最小値を取得します。

**例：`products`テーブルの最安価格を取得**

```sql
SELECT MIN(price) AS lowest_price FROM products;
```

#### 2.4.5 MAX

カラムの最大値を取得します。

**例：`products`テーブルの最高価格を取得**

```sql
SELECT MAX(price) AS highest_price FROM products;
```

---

### 2.5 グループ化

**グループ化（Grouping）**は、特定のカラムでデータをまとめ、各グループごとに集計関数を適用する操作です。`GROUP BY`句を使用します。

**構文：**

```sql
SELECT カラム1, 集合関数(カラム2)
FROM テーブル名
GROUP BY カラム1;
```

**例：各部署ごとの社員数を取得**

```sql
SELECT departments.dept_name, COUNT(employees.emp_id) AS num_employees
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id
GROUP BY departments.dept_name;
```

**説明：**

- `GROUP BY departments.dept_name`で部署ごとにグループ化。
- `COUNT(employees.emp_id)`で各部署の社員数をカウント。





---

### 2.6 並び替え

**並び替え（Ordering）**は、クエリ結果を特定の順序でソートする操作です。`ORDER BY`句を使用します。昇順（`ASC`）と降順（`DESC`）を指定できます。

**構文：**

```sql
SELECT 列名1, 列名2, ...
FROM テーブル名
ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...;
```

**例1：ユーザーを作成日時の降順で取得**

```sql
SELECT * FROM users
ORDER BY created_at DESC;
```

**例2：社員を名前の昇順、年齢の降順で取得**

```sql
SELECT * FROM employees
ORDER BY name ASC, age DESC;
```

**説明：**

- `ORDER BY created_at DESC`：`created_at`カラムを基準に降順でソート。
- 複数のカラムを指定する場合、左から順に優先順位が高くなります。

---

### 2.7 集合演算

**集合演算（Set Operations）**は、複数の`SELECT`文の結果を組み合わせる操作です。主な集合演算には、`UNION`, `INTERSECT`, `EXCEPT`などがあります。

#### 2.7.1 UNION

`UNION`は、2つの`SELECT`文の結果を結合し、重複を排除します。

**構文：**

```sql
SELECT 列名1, 列名2, ... FROM テーブル1
UNION
SELECT 列名1, 列名2, ... FROM テーブル2;
```

**例：`current_users`と`former_users`の全ユーザーを取得**

```sql
SELECT username, email FROM current_users
UNION
SELECT username, email FROM former_users;
```

**説明：**

- 重複するレコードは一つにまとめられる。
- 各`SELECT`文で選択するカラムの数とデータ型が一致している必要がある。

#### 2.7.2 UNION ALL

`UNION ALL`は、`UNION`と同様に結果を結合しますが、重複を排除しません。

**例：すべてのユーザー（重複あり）を取得**

```sql
SELECT username, email FROM current_users
UNION ALL
SELECT username, email FROM former_users;
```



---

### 2.8 副問合せ

**副問合せ（Subquery）**は、クエリの中に含まれる別のクエリです。副問合せは、主クエリの結果に基づいて動的に値を取得する際に使用します。

#### 2.8.1 スカラーサブクエリ

単一の値を返す副問合せです。

**例：平均年齢以上の社員を取得**

```sql
SELECT * FROM employees
WHERE age >= (SELECT AVG(age) FROM employees);
```

**説明：**

- 副問合せ`(SELECT AVG(age) FROM employees)`が平均年齢を計算。
- 主クエリは、その平均年齢以上の社員を取得。




---

## 3. SQLによるデータ追加/更新/削除

データベース内のデータを操作するためには、`INSERT`, `UPDATE`, `DELETE`文を使用します。これらは**データ操作言語（DML）**の主要なコマンドです。

### 3.1 データの追加（INSERT）

**INSERT**文を使用して、新しいレコードをテーブルに追加します。

**構文：**

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

**例1：新しいユーザーを`users`テーブルに追加**

```sql
INSERT INTO users (username, email, password)
VALUES ('charlie', 'charlie@example.com', 'securepassword');
```

下記が出るとusername, emailがUNIQUEになっていないからなので値を変える
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/576094/0eaa8611-8899-cd7f-47ed-ff38d44480cc.png)

**説明：**

- `users`テーブルの`username`, `email`, `password`カラムに新しい値を挿入。

**例2：全カラムに値を挿入（カラム名を省略）**

```sql
INSERT INTO users
VALUES (4, 'david', 'david@example.com', 'anotherpassword', '2024-04-03 09:45:00');
```

**注意点：**

- カラム名を指定しない場合、すべてのカラムに対して値を提供する必要があります。
- 自動インクリメントされるカラム（例：`user_id`）は、値を省略することが可能です。

**例3：自動インクリメントカラムを省略**

```sql
INSERT INTO users (username, email, password)
VALUES ('eve', 'eve@example.com', 'evepassword');
```

**説明：**

- `user_id`が自動インクリメントの場合、値を指定しなくても自動的に割り当てられます。

---

### 3.2 データの更新（UPDATE）

**UPDATE**文を使用して、既存のレコードを変更します。

**構文：**

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

**例1：特定のユーザーのメールアドレスを更新**

```sql
UPDATE users
SET email = 'alice_new@example.com'
WHERE username = 'alice';
```

**説明：**

- `username`が`alice`であるレコードの`email`カラムを更新。

**例2：複数のカラムを同時に更新**

```sql
UPDATE employees
SET salary = salary * 1.10, age= 55
WHERE NAME  = 'Alice Johnson';
```

**説明：**

- Alice Johnsonの社員の給与を10%増加。
- `age`も更新。

**注意点：**

- `WHERE`句を指定しない場合、テーブル内のすべてのレコードが更新されます。意図しない更新を避けるため、必ず条件を指定しましょう。

---

### 3.3 データの削除（DELETE）

**DELETE**文を使用して、テーブルからレコードを削除します。

**構文：**

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

**例1：特定のユーザーを削除**

```sql
DELETE FROM users
WHERE username = 'david';
```

**説明：**

- `username`が`david`であるレコードを削除。

**例2：条件に一致する複数のレコードを削除**

```sql
DELETE FROM orders
WHERE order_date < '2024-01-01';
```

**説明：**

- `2024-01-01`より前の日付の注文をすべて削除。

**注意点：**

- `WHERE`句を指定しない場合、テーブル内のすべてのレコードが削除されます。必要に応じて、条件を厳密に設定しましょう。
- データの削除は元に戻せないため、実行前にバックアップを取ることを推奨します。

---