H2 Database（H2DB）用のテーブルおよびデータ作成スクリプト

このセクションでは、前述のチュートリアルで使用するためのテーブルおよびサンプルデータを作成するSQLスクリプトを提供します。これらのスクリプトを実行することで、チュートリアル内のクエリや操作を実践的に試すための環境が整います。

目次

テーブルの作成
データの挿入
- 2.1 departments テーブルへのデータ挿入
- 2.2 users テーブルへのデータ挿入
- 2.3 employees テーブルへのデータ挿入
- 2.4 customers テーブルへのデータ挿入
- 2.5 products テーブルへのデータ挿入
- 2.6 orders テーブルへのデータ挿入
- 2.7 order_items テーブルへのデータ挿入
- 2.8 current_users テーブルへのデータ挿入
- 2.9 former_users テーブルへのデータ挿入
スクリプトの実行方法
まとめ

1. テーブルの作成

以下のセクションでは、チュートリアルで使用する各テーブルの作成SQL文を提供します。各テーブルには、適切なデータ型、制約（主キー、外部キー、ユニーク制約など）が設定されています。

1.1 `departments` テーブル

-- departments テーブルの作成
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100)
);

説明:

dept_id: 自動インクリメントされる主キー。
dept_name: 部署名。非NULLかつ一意。
location: 部署の所在地。

1.2 `users` テーブル

-- users テーブルの作成
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

説明:

user_id: 自動インクリメントされる主キー。
username: ユーザー名。非NULLかつ一意。
email: メールアドレス。非NULLかつ一意。
password: パスワード。非NULL。
created_at: レコード作成時のタイムスタンプ。デフォルトで現在時刻が設定される。

1.3 `employees` テーブル

-- employees テーブルの作成
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18),
    salary DECIMAL(10, 2) DEFAULT 0.00,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

説明:

emp_id: 自動インクリメントされる主キー。
name: 従業員の名前。非NULL。
age: 従業員の年齢。18歳以上であることをチェック。
salary: 給与。デフォルト値は0.00。
dept_id: 部署ID。departmentsテーブルのdept_idを参照する外部キー。

1.4 `customers` テーブル

-- customers テーブルの作成
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    address VARCHAR(255)
);

説明:

customer_id: 自動インクリメントされる主キー。
customer_name: 顧客名。非NULL。
email: メールアドレス。一意。
phone: 電話番号。
address: 住所。

1.5 `products` テーブル

-- products テーブルの作成
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL UNIQUE,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

説明:

product_id: 自動インクリメントされる主キー。
product_name: 製品名。非NULLかつ一意。
price: 価格。非NULL。
stock: 在庫数。デフォルト値は0。
created_at: レコード作成時のタイムスタンプ。

1.6 `orders` テーブル

-- orders テーブルの作成
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

説明:

order_id: 自動インクリメントされる主キー。
customer_id: 顧客ID。customersテーブルのcustomer_idを参照する外部キー。
order_date: 注文日。デフォルトで現在の日付。
status: 注文のステータス。デフォルト値はpending。

1.7 `order_items` テーブル

-- order_items テーブルの作成
DROP TABLE IF EXISTS order_items;

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT CHECK (quantity > 0),
    price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

説明:

order_id: 注文ID。ordersテーブルのorder_idを参照する外部キー。
product_id: 製品ID。productsテーブルのproduct_idを参照する外部キー。
quantity: 数量。1以上であることをチェック。
price: 単価。
複合主キーとしてorder_idとproduct_idを設定。

1.8 `current_users` テーブル

-- current_users テーブルの作成
DROP TABLE IF EXISTS current_users;

CREATE TABLE current_users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

説明:

user_id: 自動インクリメントされる主キー。
username: ユーザー名。非NULLかつ一意。
email: メールアドレス。非NULLかつ一意。
last_login: 最終ログイン時刻。デフォルトで現在時刻。

1.9 `former_users` テーブル

-- former_users テーブルの作成
DROP TABLE IF EXISTS former_users;

CREATE TABLE former_users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    deactivated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

説明:

user_id: 自動インクリメントされる主キー。
username: ユーザー名。非NULLかつ一意。
email: メールアドレス。非NULLかつ一意。
deactivated_at: アカウントが無効化された時刻。デフォルトで現在時刻。

2. データの挿入

以下のセクションでは、作成した各テーブルにサンプルデータを挿入するためのSQLスクリプトを提供します。これらのデータは、チュートリアル内のクエリや操作を実践的に試す際に使用します。

2.1 `departments` テーブルへのデータ挿入

-- departments テーブルへのデータ挿入
INSERT INTO departments (dept_name, location) VALUES
('Sales', 'New York'),
('Engineering', 'San Francisco'),
('HR', 'Chicago'),
('Marketing', 'Boston');

2.2 `users` テーブルへのデータ挿入

-- users テーブルへのデータ挿入
INSERT INTO users (username, email, password) VALUES
('alice', 'alice@example.com', 'alicepassword'),
('bob', 'bob@example.com', 'bobpassword'),
('charlie', 'charlie@example.com', 'charliepassword'),
('david', 'david@example.com', 'davidpassword'),
('eve', 'eve@example.com', 'evepassword');

2.3 `employees` テーブルへのデータ挿入

-- employees テーブルへのデータ挿入
INSERT INTO employees (name, age, salary, dept_id) VALUES
('Alice Johnson', 30, 70000.00, 1),
('Bob Smith', 45, 85000.00, 2),
('Charlie Davis', 25, 50000.00, 1),
('David Wilson', 35, 65000.00, 3),
('Eve Thompson', 28, 60000.00, 2);

2.4 `customers` テーブルへのデータ挿入

-- customers テーブルへのデータ挿入
INSERT INTO customers (customer_name, email, phone, address) VALUES
('Frank Miller', 'frank.miller@example.com', '555-1234', '123 Maple Street'),
('Grace Lee', 'grace.lee@example.com', '555-5678', '456 Oak Avenue'),
('Hank Green', 'hank.green@example.com', '555-9012', '789 Pine Road');

2.5 `products` テーブルへのデータ挿入

-- products テーブルへのデータ挿入
INSERT INTO products (product_name, price, stock) VALUES
('Laptop', 1200.00, 10),
('Smartphone', 800.00, 25),
('Headphones', 150.00, 50),
('Monitor', 300.00, 15),
('Keyboard', 75.00, 40);

2.6 `orders` テーブルへのデータ挿入

-- orders テーブルへのデータ挿入
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2024-04-15', 'shipped'),
(2, '2024-04-16', 'pending'),
(1, '2024-04-17', 'delivered'),
(3, '2024-04-18', 'canceled'),
(2, '2024-04-19', 'shipped');

2.7 `order_items` テーブルへのデータ挿入

-- order_items テーブルへのデータ挿入
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 1200.00),
(1, 3, 2, 150.00),
(2, 2, 1, 800.00),
(3, 4, 2, 300.00),
(3, 5, 3, 75.00),
(4, 1, 1, 1200.00),
(5, 2, 2, 800.00),
(5, 5, 1, 75.00);

2.8 `current_users` テーブルへのデータ挿入

-- current_users テーブルへのデータ挿入
INSERT INTO current_users (username, email, last_login) VALUES
('alice', 'alice@example.com', '2024-09-20 10:15:00'),
('bob', 'bob@example.com', '2024-09-21 11:30:00'),
('charlie', 'charlie@example.com', '2024-09-22 09:45:00'),
('david', 'david@example.com', '2024-09-23 14:20:00'),
('eve', 'eve@example.com', '2024-09-24 16:50:00');

2.9 `former_users` テーブルへのデータ挿入

-- former_users テーブルへのデータ挿入
INSERT INTO former_users (username, email, deactivated_at) VALUES
('frank', 'frank.miller@example.com', '2024-05-01 08:00:00'),
('grace', 'grace.lee@example.com', '2024-05-02 09:30:00'),
('hank', 'hank.green@example.com', '2024-05-03 10:45:00');

3. スクリプトの実行方法

以下の手順に従って、提供されたSQLスクリプトをH2コンソールで実行し、テーブルとデータを作成します。

3.1 H2コンソールの起動

H2DBのインストールが完了していることを確認してください。
H2コンソールを起動します。ターミナル（コマンドプロンプト）でH2DBのディレクトリに移動し、以下のコマンドを実行します。
```
java -jar h2*.jar
```
ブラウザが自動的に開き、H2コンソールのログイン画面が表示されます。開かない場合は、ブラウザで以下のURLにアクセスしてください。
```
http://localhost:8082
```
ログイン画面で以下の情報を入力して接続します。
- JDBC URL: jdbc:h2:~/test
- ユーザー名: sa
- パスワード: 空欄（デフォルト設定）
「Connect」ボタンをクリックしてデータベースに接続します。

3.2 SQLスクリプトの実行

接続後、SQLエディタに先ほど提供したテーブル作成スクリプトおよびデータ挿入スクリプトをコピー＆ペーストします。
スクリプト全体を選択し、「Run」ボタン（またはショートカットキー：Ctrl + Enter）をクリックして実行します。
正常に実行されると、各テーブルが作成され、サンプルデータが挿入されます。エラーメッセージが表示された場合は、エラー内容を確認し、スクリプトを修正してください。

注意点:

テーブルの順序: 外部キー制約があるため、テーブルの作成およびデータ挿入は依存関係に基づいて順序正しく実行する必要があります。上記のスクリプトでは、departmentsやcustomersなど、依存先のテーブルを先に作成しています。
データの整合性: 外部キー制約により、参照先のデータが存在しない場合はエラーが発生します。例えば、employeesテーブルにデータを挿入する前にdepartmentsテーブルに対応する部署データが存在する必要があります。

4. まとめ

このセクションでは、H2 Database（H2DB）を使用したSQLチュートリアルの実践に必要なテーブルおよびデータの作成方法を詳述しました。以下のポイントを確認しましょう：

テーブルの作成: 各テーブルの構造を定義し、適切なデータ型と制約を設定。
データの挿入: サンプルデータを挿入し、クエリや操作を試すための基盤を構築。
スクリプトの実行方法: H2コンソールを使用して、提供されたSQLスクリプトを実行し、環境をセットアップ。

これらのテーブルとデータを使用することで、前述のチュートリアル内のクエリや操作を実践的に学ぶことができます。次のステップとして、チュートリアル内の各セクションを参照し、実際にクエリを実行してみてください。

参考リンク

サポート

もしスクリプトの実行中に問題が発生した場合や、追加の質問がある場合は、以下の方法でサポートを受けることができます：

公式フォーラム: H2 Database フォーラム
Stack Overflow: H2 Database タグ
ドキュメント参照: H2 Database 詳細ドキュメント

引き続き、効果的なデータベース管理とSQLクエリの習得を目指して頑張ってください！

DROP TABLE AREA;
DROP TABLE CURRENT_USERS;
DROP TABLE CUSTOMERS;
DROP TABLE DEPARTMENTS;
DROP TABLE EMPLOYEES;
DROP TABLE FORMER_USERS;
DROP TABLE ORDERS;
DROP TABLE ORDER_ITEMS;
DROP TABLE PITCHERRECORD;
DROP TABLE PLAYER;
DROP TABLE POSITION;
DROP TABLE PRODUCTS;
DROP TABLE SPORT;
DROP TABLE TEAM;
DROP TABLE USERS;


DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100)
);

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18),
    salary DECIMAL(10, 2) DEFAULT 0.00,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    address VARCHAR(255)
);

DROP TABLE IF EXISTS products;
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL UNIQUE,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

DROP TABLE IF EXISTS order_items;
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT CHECK (quantity > 0),
    price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

DROP TABLE IF EXISTS current_users;
CREATE TABLE current_users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS former_users;
CREATE TABLE former_users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    deactivated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO departments (dept_name, location) VALUES
('Sales', 'New York'),
('Engineering', 'San Francisco'),
('HR', 'Chicago'),
('Marketing', 'Boston');

INSERT INTO users (username, email, password) VALUES
('alice', 'alice@example.com', 'alicepassword'),
('bob', 'bob@example.com', 'bobpassword'),
('charlie', 'charlie@example.com', 'charliepassword'),
('david', 'david@example.com', 'davidpassword'),
('eve', 'eve@example.com', 'evepassword');

INSERT INTO employees (name, age, salary, dept_id) VALUES
('Alice Johnson', 30, 70000.00, 1),
('Bob Smith', 45, 85000.00, 2),
('Charlie Davis', 25, 50000.00, 1),
('David Wilson', 35, 65000.00, 3),
('Eve Thompson', 28, 60000.00, 2);

INSERT INTO customers (customer_name, email, phone, address) VALUES
('Frank Miller', 'frank.miller@example.com', '555-1234', '123 Maple Street'),
('Grace Lee', 'grace.lee@example.com', '555-5678', '456 Oak Avenue'),
('Hank Green', 'hank.green@example.com', '555-9012', '789 Pine Road');

INSERT INTO products (product_name, price, stock) VALUES
('Laptop', 1200.00, 10),
('Smartphone', 800.00, 25),
('Headphones', 150.00, 50),
('Monitor', 300.00, 15),
('Keyboard', 75.00, 40);

INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2024-04-15', 'shipped'),
(2, '2024-04-16', 'pending'),
(1, '2024-04-17', 'delivered'),
(3, '2024-04-18', 'canceled'),
(2, '2024-04-19', 'shipped');

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 1200.00),
(1, 3, 2, 150.00),
(2, 2, 1, 800.00),
(3, 4, 2, 300.00),
(3, 5, 3, 75.00),
(4, 1, 1, 1200.00),
(5, 2, 2, 800.00),
(5, 5, 1, 75.00);

INSERT INTO current_users (username, email, last_login) VALUES
('alice', 'alice@example.com', '2024-09-20 10:15:00'),
('bob', 'bob@example.com', '2024-09-21 11:30:00'),
('charlie', 'charlie@example.com', '2024-09-22 09:45:00'),
('david', 'david@example.com', '2024-09-23 14:20:00'),
('eve', 'eve@example.com', '2024-09-24 16:50:00');

INSERT INTO former_users (username, email, deactivated_at) VALUES
('frank', 'frank.miller@example.com', '2024-05-01 08:00:00'),
('grace', 'grace.lee@example.com', '2024-05-02 09:30:00'),
('hank', 'hank.green@example.com', '2024-05-03 10:45:00');