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

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

目次

1. リレーショナルデータベースとSQLの概要
2. SQLによるデータ検索
   1. 全件検索
   2. 探索条件
   3. 結合
   4. 集合関数
   5. グループ化
   6. 並び替え
   7. 集合演算
   8. 副問合せ
3. SQLによるデータ追加/更新/削除
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 全件検索

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

構文：

SELECT * FROM テーブル名;

例：

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

SELECT * FROM users;

説明：

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

実行結果のイメージ：

user_id	username	email	created_at
1	alice	[e-mail]	2024-04-01 10:00:00
2	bob	[e-mail]	2024-04-02 11:30:00
...	...	...	...

---

2.2 探索条件

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

構文：

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

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

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

SELECT * FROM users WHERE username = 'alice';

例2：複数条件の指定

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

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で終わるユーザーを取得する。

SELECT * FROM users WHERE email LIKE '[e-mail]';

---

2.3 結合

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

2.3.1 INNER JOIN

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

構文：

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

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

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

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が入ります。

構文：

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

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

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が入ります。

構文：

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

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

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テーブルの総ユーザー数を取得

SELECT COUNT(*) AS total_users FROM users;

説明：

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

2.4.2 SUM

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

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

SELECT SUM(price) AS total_price FROM order_items;

2.4.3 AVG

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

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

SELECT AVG(age) AS average_age FROM employees;

2.4.4 MIN

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

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

SELECT MIN(price) AS lowest_price FROM products;

2.4.5 MAX

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

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

SELECT MAX(price) AS highest_price FROM products;

---

2.5 グループ化

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

構文：

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

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

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）を指定できます。

構文：

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

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

SELECT * FROM users
ORDER BY created_at DESC;

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

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文の結果を結合し、重複を排除します。

構文：

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

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

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

説明：

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

2.7.2 UNION ALL

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

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

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

---

2.8 副問合せ

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

2.8.1 スカラーサブクエリ

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

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

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文を使用して、新しいレコードをテーブルに追加します。

構文：

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

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

INSERT INTO users (username, email, password)
VALUES ('charlie', '[e-mail]', 'securepassword');

下記が出るとusername, emailがUNIQUEになっていないからなので値を変える

説明：

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

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

INSERT INTO users
VALUES (4, 'david', '[e-mail]', 'anotherpassword', '2024-04-03 09:45:00');

注意点：

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

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

INSERT INTO users (username, email, password)
VALUES ('eve', '[e-mail]', 'evepassword');

説明：

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

---

3.2 データの更新（UPDATE）

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

構文：

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

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

UPDATE users
SET email = '[e-mail]'
WHERE username = 'alice';

説明：

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

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

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

説明：

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

注意点：

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

---

3.3 データの削除（DELETE）

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

構文：

DELETE FROM テーブル名
WHERE 条件;

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

DELETE FROM users
WHERE username = 'david';

説明：

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

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

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

説明：

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

注意点：

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

---