SQLデータベース操作の完全マスターガイド

-- 基本的なデータ型の例
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT,
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- テーブルの作成
CREATE TABLE employees (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE
);
-- データの挿入
INSERT INTO employees (name, department, salary, hire_date) 
VALUES ('田中太郎', '営業部', 450000, '2023-04-01');
-- 複数行の一括挿入
INSERT INTO employees (name, department, salary, hire_date) 
VALUES 
    ('佐藤花子', '開発部', 550000, '2023-03-15'),
    ('鈴木一郎', '人事部', 400000, '2023-05-10');

-- 基本的なデータ取得
SELECT * FROM employees;
-- 特定のカラムのみ取得
SELECT name, department FROM employees;
-- 条件付き検索
SELECT * FROM employees WHERE department = '開発部';
-- 並び替え
SELECT * FROM employees ORDER BY salary DESC;

-- 単一レコードの更新
UPDATE employees 
SET salary = 500000 
WHERE name = '田中太郎';
-- 複数条件での更新
UPDATE employees 
SET department = 'マーケティング部' 
WHERE department = '営業部' AND salary > 400000;

-- 条件付き削除
DELETE FROM employees WHERE id = 3;
-- 複数条件での削除
DELETE FROM employees 
WHERE department = '人事部' AND salary < 350000;
-- 全データの削除（注意が必要）
DELETE FROM employees;

-- COUNT関数：レコード数を数える
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department = '開発部';
-- SUM関数：数値の合計を取得
SELECT SUM(salary) FROM employees;
SELECT SUM(salary) FROM employees WHERE department = '営業部';
-- AVG関数：平均値を計算
SELECT AVG(salary) FROM employees;
SELECT AVG(salary) FROM employees WHERE department = '開発部';
-- MAX/MIN関数：最大値・最小値を取得
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary), MIN(salary) FROM employees;

-- 部署別の平均給与
SELECT department, AVG(salary) as avg_salary
FROM employees 
GROUP BY department;
-- 部署別の従業員数と平均給与
SELECT 
    department, 
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary
FROM employees 
GROUP BY department
ORDER BY avg_salary DESC;

-- 平均給与が450000以上の部署のみ表示
SELECT department, AVG(salary) as avg_salary
FROM employees 
GROUP BY department
HAVING AVG(salary) > 450000;

-- 内部結合（INNER JOIN）
SELECT e.name, e.department, p.project_name
FROM employees e
INNER JOIN projects p ON e.id = p.employee_id;
-- 左外部結合（LEFT JOIN）
SELECT e.name, e.department, p.project_name
FROM employees e
LEFT JOIN projects p ON e.id = p.employee_id;
-- 右外部結合（RIGHT JOIN）
SELECT e.name, e.department, p.project_name
FROM employees e
RIGHT JOIN projects p ON e.id = p.employee_id;

-- 平均給与以上の給与を受け取る従業員
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 部署別の最高給与者
SELECT name, department, salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

-- 前方一致検索
SELECT * FROM employees WHERE name LIKE '田中%';
-- 後方一致検索
SELECT * FROM employees WHERE name LIKE '%太郎';
-- 部分一致検索
SELECT * FROM employees WHERE name LIKE '%田%';
-- 特定の文字数の検索
SELECT * FROM employees WHERE name LIKE '田_太郎';
-- 複数のパターン検索
SELECT * FROM employees 
WHERE name LIKE '田中%' OR name LIKE '佐藤%';

-- 特定の部署の従業員を検索
SELECT * FROM employees 
WHERE department IN ('開発部', '営業部', 'マーケティング部');
-- 特定のIDの従業員を検索
SELECT * FROM employees 
WHERE id IN (1, 3, 5, 7);

-- 給与範囲での検索
SELECT * FROM employees 
WHERE salary BETWEEN 400000 AND 600000;
-- 日付範囲での検索
SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 給与レベルの分類
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 600000 THEN '高給与'
        WHEN salary >= 450000 THEN '中給与'
        ELSE '標準給与'
    END as salary_level
FROM employees;
-- 部署の分類
SELECT 
    name,
    department,
    CASE department
        WHEN '開発部' THEN '技術系'
        WHEN '営業部' THEN '営業系'
        WHEN '人事部' THEN '管理系'
        ELSE 'その他'
    END as department_type
FROM employees;

-- 文字列の長さを取得
SELECT name, LENGTH(name) as name_length FROM employees;
-- 文字列の結合
SELECT CONCAT(name, ' (', department, ')') as full_info FROM employees;
-- 文字列の置換
SELECT name, REPLACE(department, '部', '課') as new_department FROM employees;
-- 大文字・小文字の変換
SELECT UPPER(name) as upper_name, LOWER(department) as lower_dept FROM employees;

-- 現在の日付を取得
SELECT NOW(), CURDATE(), CURTIME();
-- 日付の計算
SELECT name, hire_date, 
       DATEDIFF(CURDATE(), hire_date) as days_employed
FROM employees;
-- 年、月、日の抽出
SELECT name, hire_date,
       YEAR(hire_date) as hire_year,
       MONTH(hire_date) as hire_month,
       DAY(hire_date) as hire_day
FROM employees;

-- インデックスの作成
CREATE INDEX idx_employee_department ON employees(department);
CREATE INDEX idx_employee_salary ON employees(salary);
-- 複合インデックスの作成
CREATE INDEX idx_employee_dept_salary ON employees(department, salary);
-- インデックスの確認
SHOW INDEX FROM employees;

-- 非効率
SELECT * FROM employees WHERE department = '開発部';
-- 効率的
SELECT name, salary FROM employees WHERE department = '開発部';

-- インデックスを活用する条件
SELECT * FROM employees WHERE department = '開発部' AND salary > 500000;

-- トランザクションの開始
START TRANSACTION;
-- 複数の操作を実行
UPDATE employees SET salary = salary * 1.1 WHERE department = '開発部';
INSERT INTO salary_history (employee_id, old_salary, new_salary, change_date)
SELECT id, salary/1.1, salary, NOW() FROM employees WHERE department = '開発部';
-- コミット（確定）またはロールバック（取り消し）
COMMIT;
-- または
-- ROLLBACK;

-- プレースホルダーの使用（推奨）
PREPARE stmt FROM 'SELECT * FROM employees WHERE name = ?';
SET @name = '田中太郎';
EXECUTE stmt USING @name;
-- 入力値の検証とサニタイゼーション
-- アプリケーション側での適切な処理が必要

-- クエリの実行時間を測定
SET profiling = 1;
SELECT * FROM employees WHERE department = '開発部';
SHOW PROFILES;
-- 実行計画の確認
EXPLAIN SELECT * FROM employees WHERE department = '開発部';

-- 月別売上集計
SELECT 
    YEAR(sale_date) as year,
    MONTH(sale_date) as month,
    SUM(amount) as total_sales,
    COUNT(*) as transaction_count,
    AVG(amount) as average_sale
FROM sales 
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY year, month;
-- 上位売上顧客の特定
SELECT 
    customer_name,
    SUM(amount) as total_purchase,
    COUNT(*) as purchase_count,
    AVG(amount) as average_purchase
FROM sales 
GROUP BY customer_name
HAVING SUM(amount) > 100000
ORDER BY total_purchase DESC
LIMIT 10;

-- 前年同月比の計算
SELECT 
    current_month.month,
    current_month.sales as current_sales,
    previous_month.sales as previous_sales,
    ((current_month.sales - previous_month.sales) / previous_month.sales * 100) as growth_rate
FROM (
    SELECT MONTH(sale_date) as month, SUM(amount) as sales
    FROM sales 
    WHERE YEAR(sale_date) = 2024
    GROUP BY MONTH(sale_date)
) current_month
LEFT JOIN (
    SELECT MONTH(sale_date) as month, SUM(amount) as sales
    FROM sales 
    WHERE YEAR(sale_date) = 2023
    GROUP BY MONTH(sale_date)
) previous_month ON current_month.month = previous_month.month;

-- ランキング分析
SELECT 
    product_name,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) as sales_rank,
    PERCENT_RANK() OVER (ORDER BY sales_amount DESC) as percentile_rank
FROM (
    SELECT product_name, SUM(amount) as sales_amount
    FROM sales s
    JOIN products p ON s.product_id = p.id
    GROUP BY product_name
) product_sales;
-- 移動平均の計算
SELECT 
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_average_7days
FROM (
    SELECT sale_date, SUM(amount) as daily_sales
    FROM sales
    GROUP BY sale_date
    ORDER BY sale_date
) daily_summary;

-- 重複データの特定と削除
-- 重複レコードの特定
SELECT name, email, COUNT(*) as duplicate_count
FROM users 
GROUP BY name, email
HAVING COUNT(*) > 1;
-- 重複データの削除（最新のもの以外）
DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id < u2.id 
AND u1.name = u2.name 
AND u1.email = u2.email;
-- データの標準化
UPDATE users 
SET email = LOWER(TRIM(email)),
    name = CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2)))
WHERE email IS NOT NULL;

-- 総合的なビジネスレポート
SELECT 
    '総売上' as metric,
    SUM(amount) as value,
    COUNT(*) as count
FROM sales
UNION ALL
SELECT 
    '平均売上',
    AVG(amount),
    COUNT(*)
FROM sales
UNION ALL
SELECT 
    '顧客数',
    COUNT(DISTINCT customer_id),
    COUNT(*)
FROM sales;
-- 部署別パフォーマンスレポート
SELECT 
    d.department_name,
    COUNT(e.id) as employee_count,
    AVG(e.salary) as avg_salary,
    SUM(s.amount) as total_sales,
    AVG(s.amount) as avg_sale_per_employee
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
LEFT JOIN sales s ON e.id = s.employee_id
GROUP BY d.id, d.department_name
ORDER BY total_sales DESC;