1. データベースとは

データベースは、構造化されたデータを効率的に保存・管理・検索するためのシステムです。

データベースの種類

- リレーショナルデータベース（RDBMS）: テーブル形式でデータを管理（MySQL, PostgreSQL, SQLite）
- NoSQLデータベース: ドキュメント、キー・バリュー、グラフ形式（MongoDB, Redis）
- 列指向データベース: 大量データの分析に適している（Cassandra）

参考リンク： Wikipedia - データベース

2. リレーショナルデータベースの基本概念

テーブル、行、列

リレーショナルデータベースは、テーブル（表）の集合で構成されます。

- テーブル: データを格納する表
- 行（レコード）: 1つのデータエントリ
- 列（カラム）: データの属性

主キーと外部キー

-- ユーザーテーブル
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主キー
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 投稿テーブル
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,  -- 外部キー
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)  -- 外部キー制約
);

3. SQLの基本操作（CRUD）

CREATE - データの作成

-- テーブルの作成
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- データの挿入
INSERT INTO products (name, price, stock, category_id)
VALUES ('ノートPC', 80000, 10, 1);

-- 複数のデータを一度に挿入
INSERT INTO products (name, price, stock, category_id)
VALUES
    ('マウス', 2000, 50, 1),
    ('キーボード', 5000, 30, 1),
    ('デスク', 15000, 20, 2);

READ - データの読み取り

-- すべてのデータを取得
SELECT * FROM products;

-- 特定の列を取得
SELECT name, price FROM products;

-- 条件で絞り込み
SELECT * FROM products WHERE price > 10000;

-- 並び替え
SELECT * FROM products ORDER BY price DESC;

-- 件数を制限
SELECT * FROM products LIMIT 10;

-- 集計関数
SELECT 
    COUNT(*) AS total_products,
    AVG(price) AS average_price,
    MAX(price) AS max_price,
    MIN(price) AS min_price
FROM products;

UPDATE - データの更新

-- 単一レコードの更新
UPDATE products
SET price = 75000, stock = 15
WHERE id = 1;

-- 複数レコードの更新
UPDATE products
SET stock = stock - 1
WHERE category_id = 1 AND stock > 0;

-- 条件付き更新
UPDATE products
SET price = price * 1.1
WHERE category_id = 1;

DELETE - データの削除

-- 特定のレコードを削除
DELETE FROM products WHERE id = 1;

-- 条件に合うレコードを削除
DELETE FROM products WHERE stock = 0;

-- すべてのデータを削除（注意）
DELETE FROM products;

-- テーブルを削除
DROP TABLE products;

4. リレーション（結合）

INNER JOIN

-- ユーザーと投稿を結合
SELECT 
    users.name,
    posts.title,
    posts.created_at
FROM users
INNER JOIN posts ON users.id = posts.user_id;

LEFT JOIN

-- すべてのユーザーとその投稿（投稿がないユーザーも含む）
SELECT 
    users.name,
    posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

RIGHT JOIN

-- すべての投稿とそのユーザー（ユーザーが削除された投稿も含む）
SELECT 
    users.name,
    posts.title
FROM users
RIGHT JOIN posts ON users.id = posts.user_id;

5. 集計とグループ化

-- カテゴリーごとの商品数と平均価格
SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS average_price,
    SUM(stock) AS total_stock
FROM products
GROUP BY category_id;

-- HAVING句でグループ化後の条件を指定
SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS average_price
FROM products
GROUP BY category_id
HAVING COUNT(*) > 5;

6. サブクエリ

-- 平均価格より高い商品を取得
SELECT *
FROM products
WHERE price > (
    SELECT AVG(price) FROM products
);

-- サブクエリで存在チェック
SELECT *
FROM users
WHERE EXISTS (
    SELECT 1 FROM posts WHERE posts.user_id = users.id
);

-- IN句でサブクエリを使用
SELECT *
FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE name LIKE '%電子%'
);

7. インデックスの作成

インデックスは、データの検索速度を向上させるための仕組みです。

-- 単一カラムのインデックス
CREATE INDEX idx_product_name ON products(name);

-- 複合インデックス
CREATE INDEX idx_category_price ON products(category_id, price);

-- ユニークインデックス
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- インデックスの確認
SHOW INDEX FROM products;

-- インデックスの削除
DROP INDEX idx_product_name ON products;

注意点：
- インデックスは検索を高速化するが、更新処理は遅くなる
- 適切なカラムにインデックスを作成することが重要

8. トランザクション

トランザクションは、複数のSQL操作を1つの単位として実行する仕組みです。

-- トランザクションの開始
START TRANSACTION;

-- 複数の操作を実行
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- コミット（確定）
COMMIT;

-- ロールバック（取り消し）
-- ROLLBACK;

ACID特性

- Atomicity（原子性）: すべて成功するか、すべて失敗するか
- Consistency（一貫性）: データの整合性が保たれる
- Isolation（独立性）: トランザクション同士が干渉しない
- Durability（永続性）: コミット後は永続的に保存される

9. データベース設計の基本

正規化

正規化は、データの冗長性を減らし、整合性を保つための設計手法です。

#### 第1正規形（1NF）

各セルに1つの値のみを格納する。

-- ❌ 悪い例
CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_names VARCHAR(255)  -- '商品A, 商品B, 商品C'
);

-- ✅ 良い例
CREATE TABLE orders (
    id INT PRIMARY KEY
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

#### 第2正規形（2NF）

部分関数従属を排除する。

#### 第3正規形（3NF）

推移的関数従属を排除する。

テーブル設計の例

-- ユーザーテーブル
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- カテゴリーテーブル
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 商品テーブル
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 注文テーブル
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 注文明細テーブル
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

10. 実践的なクエリ例

複雑な結合クエリ

-- ユーザーごとの注文総額を取得
SELECT 
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC;

ウィンドウ関数（MySQL 8.0+, PostgreSQL）

-- カテゴリーごとの価格ランキング
SELECT 
    name,
    price,
    category_id,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM products;

11. データベースのベストプラクティス

1. 適切なデータ型を選択: メモリとパフォーマンスを考慮
2. インデックスを適切に使用: 検索頻度の高いカラムに作成
3. 外部キー制約を設定: データの整合性を保証
4. 正規化を適度に: 過度な正規化は避ける
5. バックアップを定期的に: データ損失を防ぐ
6. クエリを最適化: EXPLAINで実行計画を確認

-- クエリの実行計画を確認
EXPLAIN SELECT * FROM products WHERE category_id = 1;

12. ORM（Object-Relational Mapping）

ORMは、オブジェクト指向のコードでデータベースを操作するためのツールです。

Python（SQLAlchemy）の例

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    email = Column(String(255))

# 使用例
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# データの取得
users = session.query(User).filter(User.name == '田中').all()

# データの追加
new_user = User(name='佐藤', email='sato@example.com')
session.add(new_user)
session.commit()

参考リンク： SQLAlchemy公式ドキュメント

参考リンク： MySQL公式ドキュメント | PostgreSQL公式ドキュメント