-- リレーション = テーブル
-- タプル     = 行（レコード）
-- 属性       = 列（カラム）
-- ドメイン   = 列が取りうる値の範囲（データ型 + 制約）
 
CREATE TABLE employees (
    employee_id   INTEGER PRIMARY KEY,          -- 主キー（候補キーから選択）
    name          VARCHAR(100) NOT NULL,        -- 属性（NOT NULL制約 = ドメインからNULLを除外）
    email         VARCHAR(255) UNIQUE NOT NULL, -- 属性（UNIQUE制約 = 候補キー）
    department_id INTEGER,                      -- 外部キー（参照整合性）
    salary        DECIMAL(10, 2)
        CHECK (salary >= 0),                    -- CHECK制約 = ドメインの制限
    hired_date    DATE NOT NULL,                -- 属性
    FOREIGN KEY (department_id) REFERENCES departments(id)
);
 
-- 1タプル（行）を挿入
INSERT INTO employees (employee_id, name, email, department_id, salary, hired_date)
VALUES (1, '田中太郎', 'tanaka@example.com', 10, 450000.00, '2020-04-01');
 
-- リレーショナル代数の「選択」演算 = WHERE句
SELECT * FROM employees WHERE salary > 400000;
 
-- リレーショナル代数の「射影」演算 = SELECTで列を指定
SELECT name, salary FROM employees;

-- リレーショナル代数の集合演算をSQLで表現
 
-- 準備: 2つの互換なリレーションを作成
CREATE TABLE employees_tokyo (
    name VARCHAR(100),
    role VARCHAR(50)
);
 
CREATE TABLE employees_osaka (
    name VARCHAR(100),
    role VARCHAR(50)
);
 
INSERT INTO employees_tokyo VALUES ('田中', '開発'), ('鈴木', '営業'), ('佐藤', '開発');
INSERT INTO employees_osaka VALUES ('鈴木', '営業'), ('高橋', '企画'), ('山田', '開発');
 
-- 和（UNION）: 2つの結果セットを結合（重複排除）
SELECT name, role FROM employees_tokyo
UNION
SELECT name, role FROM employees_osaka;
-- → 田中, 鈴木, 佐藤, 高橋, 山田 （鈴木は1回だけ）
 
-- UNION ALL: 重複を排除しない（高速）
SELECT name, role FROM employees_tokyo
UNION ALL
SELECT name, role FROM employees_osaka;
-- → 田中, 鈴木, 佐藤, 鈴木, 高橋, 山田 （鈴木が2回）
 
-- 差（EXCEPT）: 左にのみ存在する行
SELECT name, role FROM employees_tokyo
EXCEPT
SELECT name, role FROM employees_osaka;
-- → 田中, 佐藤 （東京だけにいる社員）
 
-- 積（INTERSECT）: 両方に存在する行
SELECT name, role FROM employees_tokyo
INTERSECT
SELECT name, role FROM employees_osaka;
-- → 鈴木 （両方にいる社員）

-- 非正規化状態（アンチパターン）
-- 1つのテーブルに全情報を詰め込む
CREATE TABLE orders_denormalized (
    order_id      INTEGER,
    customer_name VARCHAR(100),     -- 顧客名が注文ごとに重複
    customer_email VARCHAR(255),    -- メールも重複
    product_name  VARCHAR(100),     -- 商品名も重複
    product_price DECIMAL(10, 2),   -- 価格も重複
    quantity      INTEGER,
    order_date    DATE
);
 
-- 正規化後（第3正規形）
-- 各エンティティを独立したテーブルに分離
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);
 
CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0)
);
 
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE
);
 
CREATE TABLE order_items (
    id         INTEGER PRIMARY KEY,
    order_id   INTEGER NOT NULL REFERENCES orders(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL  -- 注文時点の価格を保存
);

-- PostgreSQL: 高度なデータ型と拡張性
 
-- 配列型: 1カラムに複数値を格納
CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    tags     TEXT[],                    -- 配列型
    metadata JSONB,                     -- JSONBバイナリ型（GINインデックス対応）
    price    NUMRANGE,                  -- 範囲型（価格帯を表現）
    search   TSVECTOR                   -- 全文検索用ベクトル
);
 
-- サンプルデータ挿入
INSERT INTO products (name, tags, metadata, price, search) VALUES (
    'オーガニック味噌',
    ARRAY['organic', 'japanese', 'fermented'],
    '{"category": "food", "origin": "Japan", "weight_g": 500}',
    NUMRANGE(300, 500),
    to_tsvector('japanese', 'オーガニック 味噌 国産 大豆')
);
 
-- 配列の検索: ANY演算子
SELECT * FROM products WHERE 'organic' = ANY(tags);
 
-- 配列の包含: @>演算子
SELECT * FROM products WHERE tags @> ARRAY['organic', 'japanese'];
 
-- JSONB内の検索: @>演算子（包含）
SELECT * FROM products WHERE metadata @> '{"category": "food"}';
 
-- JSONB内の値を取得: ->>演算子
SELECT name, metadata->>'origin' AS origin FROM products;
 
-- 範囲型の検索: @>演算子（値が範囲内か）
SELECT * FROM products WHERE price @> 350;
 
-- 全文検索: @@演算子
SELECT * FROM products WHERE search @@ to_tsquery('japanese', 'オーガニック & 味噌');
 
-- テーブル継承（PostgreSQL固有）
CREATE TABLE employees_2024 () INHERITS (employees);
 
-- GENERATED COLUMNS（計算列）
CREATE TABLE orders_v2 (
    id         SERIAL PRIMARY KEY,
    quantity   INTEGER NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total      DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);
 
-- LATERAL JOIN: FROM句での相関サブクエリ
SELECT d.name, top3.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT e.name, e.salary
    FROM employees e
    WHERE e.department_id = d.id
    ORDER BY e.salary DESC
    LIMIT 3
) top3;

-- MySQL: 広い普及率とシンプルな運用
 
-- AUTO_INCREMENTとエンジン指定
CREATE TABLE articles (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(255) NOT NULL,
    body       LONGTEXT,
    status     ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
    -- MySQLのフルテキストインデックス
    FULLTEXT INDEX ft_body (body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
-- フルテキスト検索（ブーリアンモード）
SELECT * FROM articles
WHERE MATCH(body) AGAINST('データベース +設計 -NoSQL' IN BOOLEAN MODE);
 
-- ON DUPLICATE KEY UPDATE（UPSERT）
INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (1, 'theme', 'dark')
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = NOW();
 
-- REPLACE INTO（存在すれば削除→挿入）
REPLACE INTO cache_table (cache_key, cache_value, expires_at)
VALUES ('user:1:profile', '{"name":"田中"}', DATE_ADD(NOW(), INTERVAL 1 HOUR));
 
-- JSON型（MySQL 5.7+）
CREATE TABLE events (
    id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    data JSON NOT NULL,
    -- 仮想生成列 + インデックス
    event_type VARCHAR(50) GENERATED ALWAYS AS (data->>'$.type') VIRTUAL,
    INDEX idx_event_type (event_type)
);
 
INSERT INTO events (data) VALUES ('{"type": "login", "user_id": 42, "ip": "192.168.1.1"}');
SELECT * FROM events WHERE data->>'$.type' = 'login';
 
-- Window Functions（MySQL 8.0+）
SELECT
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- SQLite: 組み込み型、サーバー不要
-- ファイル1つがデータベース全体（またはインメモリ :memory:）
 
-- 動的型付け（型アフィニティ）
-- SQLiteは列に宣言された型を「ヒント」として使い、実際にはどんな値でも格納可能
CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY              -- どんな型でも格納可能
);
 
INSERT INTO settings VALUES ('max_retry', 3);          -- INTEGER
INSERT INTO settings VALUES ('api_url', 'https://example.com');  -- TEXT
INSERT INTO settings VALUES ('enabled', 1);            -- INTEGER (SQLiteにBOOLEAN型はない)
INSERT INTO settings VALUES ('ratio', 3.14);           -- REAL
 
-- typeof()で実際の型を確認
SELECT key, value, typeof(value) FROM settings;
-- max_retry | 3    | integer
-- api_url   | https://example.com | text
-- enabled   | 1    | integer
-- ratio     | 3.14 | real
 
-- JSON拡張（SQLite 3.38.0+ で組み込み）
CREATE TABLE logs (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    data TEXT NOT NULL  -- JSON文字列として格納
);
 
INSERT INTO logs (data) VALUES ('{"level": "error", "message": "接続失敗", "code": 500}');
 
SELECT
    json_extract(data, '$.level') AS level,
    json_extract(data, '$.message') AS message
FROM logs
WHERE json_extract(data, '$.level') = 'error';
 
-- UPSERT（SQLite 3.24.0+）
INSERT INTO settings (key, value)
VALUES ('max_retry', 5)
ON CONFLICT(key) DO UPDATE SET value = excluded.value;
 
-- WAL（Write-Ahead Logging）モードで並行読み取り性能を向上
-- PRAGMA journal_mode=WAL;

-- SQL Server: エンタープライズ向け、.NETとの統合が深い
 
-- IDENTITY列（自動連番）
CREATE TABLE orders (
    order_id   INT IDENTITY(1,1) PRIMARY KEY,  -- 1から1ずつ増加
    order_date DATETIME2 DEFAULT SYSDATETIME(),
    customer_id INT NOT NULL,
    total_amount DECIMAL(12, 2)
);
 
-- TOP N（SQL Serverのページネーション）
SELECT TOP 10 * FROM orders ORDER BY order_date DESC;
 
-- OFFSET-FETCH（SQL:2008標準に準拠）
SELECT * FROM orders
ORDER BY order_date DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
 
-- MERGE文（UPSERT + DELETE を1文で）
MERGE INTO user_settings AS target
USING (VALUES (1, 'theme', 'dark')) AS source (user_id, setting_key, setting_value)
ON target.user_id = source.user_id AND target.setting_key = source.setting_key
WHEN MATCHED THEN
    UPDATE SET setting_value = source.setting_value
WHEN NOT MATCHED THEN
    INSERT (user_id, setting_key, setting_value)
    VALUES (source.user_id, source.setting_key, source.setting_value);
 
-- STRING_AGG（文字列集約、SQL Server 2017+）
SELECT
    department_id,
    STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS members
FROM employees
GROUP BY department_id;
 
-- IIF（条件式の簡略記法）
SELECT name, salary,
    IIF(salary >= 500000, '高給', '一般') AS grade
FROM employees;

-- 方言差を最小化する書き方のパターン
 
-- (1) ページネーション: OFFSET-FETCH（SQL:2008標準）を使う
-- PostgreSQL, SQL Server, SQLite (3.35.0+) で動作
SELECT employee_id, name, salary
FROM employees
ORDER BY salary DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
 
-- (2) CASE式は全RDBMSで使用可能
SELECT name, salary,
    CASE
        WHEN salary >= 600000 THEN '高給'
        WHEN salary >= 400000 THEN '中給'
        ELSE '標準'
    END AS salary_grade
FROM employees;
 
-- (3) COALESCE: NULL代替（全RDBMSで使用可能）
SELECT name, COALESCE(phone, email, '連絡先不明') AS contact
FROM employees;
 
-- (4) 日付リテラルの標準記法
SELECT * FROM orders
WHERE order_date >= DATE '2024-01-01'
  AND order_date <  DATE '2025-01-01';
 
-- (5) CAST式は全RDBMSで使用可能
SELECT CAST(price AS INTEGER) AS rounded_price FROM products;
 
-- (6) EXISTS/NOT EXISTS は全RDBMSで同一動作
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 方言差をアプリケーション層で吸収する設計パターン
 
-- パターン1: ビューで方言を隠蔽
-- PostgreSQL用
CREATE VIEW v_monthly_sales AS
SELECT
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount) AS total
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);
 
-- MySQL用（同じ論理だが関数が異なる）
-- CREATE VIEW v_monthly_sales AS
-- SELECT
--     DATE_FORMAT(sale_date, '%Y-%m-01') AS month,
--     SUM(amount) AS total
-- FROM sales
-- GROUP BY DATE_FORMAT(sale_date, '%Y-%m-01');
 
-- パターン2: 条件付きINSERT（方言差が大きい）
-- 標準的なアプローチ: アプリケーション側でIF-ELSE
-- PostgreSQL: INSERT ... ON CONFLICT DO UPDATE
-- MySQL:      INSERT ... ON DUPLICATE KEY UPDATE
-- SQL Server: MERGE
-- SQLite:     INSERT ... ON CONFLICT DO UPDATE
 
-- パターン3: ストアドプロシージャで方言を吸収
-- PostgreSQLの例
CREATE OR REPLACE FUNCTION upsert_setting(
    p_user_id INTEGER,
    p_key TEXT,
    p_value TEXT
) RETURNS VOID AS $$
BEGIN
    INSERT INTO user_settings (user_id, key, value)
    VALUES (p_user_id, p_key, p_value)
    ON CONFLICT (user_id, key)
    DO UPDATE SET value = EXCLUDED.value, updated_at = NOW();
END;
$$ LANGUAGE plpgsql;
 
-- 呼び出し側はRDBMSを意識しない
SELECT upsert_setting(1, 'theme', 'dark');

-- DDL: データベースオブジェクトの構造を定義・変更・削除
 
-- CREATE: オブジェクトの作成
CREATE TABLE departments (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE INDEX idx_employees_dept ON employees(department_id);
 
CREATE VIEW v_active_employees AS
SELECT * FROM employees WHERE status = 'active';
 
-- ALTER: オブジェクトの変更
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees ALTER COLUMN name SET NOT NULL;
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME COLUMN name TO full_name;
 
-- DROP: オブジェクトの削除
DROP TABLE IF EXISTS temp_data;
DROP INDEX IF EXISTS idx_old;
DROP VIEW IF EXISTS v_old_view;
 
-- TRUNCATE: テーブルの全行削除（DDL操作、高速）
TRUNCATE TABLE log_entries;
 
-- COMMENT: オブジェクトにコメントを付与（PostgreSQL）
COMMENT ON TABLE employees IS '従業員マスタテーブル';
COMMENT ON COLUMN employees.salary IS '月額基本給（円）';

-- DCL: データアクセス権限の制御
 
-- GRANT: 権限の付与
GRANT SELECT, INSERT ON employees TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_user;
GRANT USAGE ON SEQUENCE employees_id_seq TO app_user;
 
-- REVOKE: 権限の剥奪
REVOKE DELETE ON employees FROM app_user;
REVOKE ALL PRIVILEGES ON employees FROM public;
 
-- ROLEの作成と管理
CREATE ROLE readonly_user LOGIN PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
 
-- TCL: トランザクションの制御
 
-- 基本的なトランザクション
BEGIN;
    UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
    UPDATE accounts SET balance = balance + 10000 WHERE id = 2;
COMMIT;
 
-- SAVEPOINT: 部分的なロールバック
BEGIN;
    INSERT INTO orders (customer_id, total) VALUES (1, 5000);
    SAVEPOINT before_items;
 
    INSERT INTO order_items (order_id, product_id) VALUES (100, 1);
    -- エラーが発生した場合、SAVEPOINTまで戻す
    -- ROLLBACK TO SAVEPOINT before_items;
 
    INSERT INTO order_items (order_id, product_id) VALUES (100, 2);
COMMIT;

-- NG: MySQL固有の構文に依存
SELECT * FROM users LIMIT 10, 20;  -- offset, limitの順序がMySQL固有
-- さらにMySQLのGROUP BY拡張に依存
SELECT department_id, name, MAX(salary)
FROM employees
GROUP BY department_id;
-- → MySQLではnameが不定値で返る（ONLY_FULL_GROUP_BY無効時）
-- → PostgreSQLでは即エラー
 
-- OK: 標準SQLに近い書き方
SELECT * FROM users
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY;  -- SQL:2008標準
 
-- OK: GROUP BYの正しい使用
SELECT department_id, MIN(name), MAX(salary)
FROM employees
GROUP BY department_id;

-- NG: NULLとの比較は常にUNKNOWN
SELECT * FROM employees WHERE department_id = NULL;
-- → 結果は常に0行！（NULL = NULL は UNKNOWN）
 
SELECT * FROM employees WHERE department_id != 10;
-- → department_id が NULL の行は含まれない！
 
-- OK: IS NULL / IS NOT NULL を使用
SELECT * FROM employees WHERE department_id IS NULL;
 
-- OK: NULLを考慮した条件
SELECT * FROM employees
WHERE department_id != 10 OR department_id IS NULL;
 
-- NULLの3値論理:
-- TRUE AND NULL    = NULL (UNKNOWN)
-- FALSE AND NULL   = FALSE
-- TRUE OR NULL     = TRUE
-- FALSE OR NULL    = NULL (UNKNOWN)
-- NOT NULL         = NULL (UNKNOWN)

-- MySQL版
SELECT
    id,
    IF(status = 1, 'active', 'inactive') AS status_label,
    DATE_FORMAT(created_at, '%Y年%m月%d日') AS formatted_date,
    GROUP_CONCAT(tag SEPARATOR ', ') AS tags
FROM articles
WHERE MATCH(body) AGAINST('データベース' IN BOOLEAN MODE)
GROUP BY id
LIMIT 5, 10;