-- ビューを作成：従業員情報ビュー
CREATE VIEW v_employee_info AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    e.salary,
    d.department_name AS department,
    d.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- ビューをクエリ
SELECT * FROM v_employee_info WHERE salary > 10000;

CREATE VIEW view_name AS
SELECT statement;

-- 部門統計ビューを作成
CREATE VIEW v_department_stats AS
SELECT 
    d.department_id,
    d.department_name AS department,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 方法1：CREATE OR REPLACE（推奨）
CREATE OR REPLACE VIEW v_employee_info AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    e.salary,
    e.hire_date,
    d.department_name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 方法2：ALTER VIEW
ALTER VIEW v_employee_info AS
SELECT employee_id, first_name, last_name, salary FROM employees;

DROP VIEW IF EXISTS v_employee_info;

-- インデックスなし：フルテーブルスキャン（行ごとに検索）
SELECT * FROM employees WHERE last_name = 'Zhang';

-- インデックスあり：直接ルックアップ（インデックス経由）
CREATE INDEX idx_last_name ON employees(last_name);
SELECT * FROM employees WHERE last_name = 'Zhang';

-- 通常インデックス（重複値を許可）
CREATE INDEX idx_column ON table_name(column_name);

-- ユニークインデックス（重複値を許可しない）
CREATE UNIQUE INDEX idx_column ON table_name(column_name);

-- フルテキストインデックス（全文検索用）
CREATE FULLTEXT INDEX idx_column ON table_name(column_name);

-- 複合インデックス（複数カラムの組み合わせ）
CREATE INDEX idx_columns ON table_name(column1, column2);

-- MySQL構文
DROP INDEX idx_column ON table_name;

-- 標準構文
ALTER TABLE table_name DROP INDEX idx_column;

-- 複合インデックスを作成
CREATE INDEX idx_name_age ON employees(last_name, first_name, salary);

-- ✅ インデックスを使用可能（最左カラムに一致）
SELECT * FROM employees WHERE last_name = 'Zhang';
SELECT * FROM employees WHERE last_name = 'Zhang' AND first_name = 'Wei';

-- ❌ インデックスを使用不可（最左カラムをスキップ）
SELECT * FROM employees WHERE first_name = 'Wei';
SELECT * FROM employees WHERE salary > 10000;

-- ビューを作成
CREATE [OR REPLACE] VIEW view_name AS SELECT statement;

-- ビューをクエリ（通常のテーブルと同じ）
SELECT * FROM view_name WHERE condition;

-- ビューを削除
DROP VIEW [IF EXISTS] view_name;

-- インデックスを作成
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(column1, column2, ...);

-- インデックスを削除
DROP INDEX index_name ON table_name;

-- テーブルのインデックスを表示
SHOW INDEX FROM table_name;

-- 注文詳細ビューを作成
CREATE OR REPLACE VIEW v_order_detail AS
SELECT 
    o.order_id,
    CONCAT(e.first_name, e.last_name) AS owner,
    d.department_name AS department,
    o.order_date,
    o.total_amount AS amount,
    o.status
FROM orders o
LEFT JOIN employees e ON o.employee_id = e.employee_id
LEFT JOIN departments d ON e.department_id = d.department_id;

-- ビューを使用してクエリ
SELECT * FROM v_order_detail WHERE status = 'completed';

order_id | owner      | department | order_date | amount   | status
---------+------------+------------+------------+----------+-----------
    1001 | WangQiang  | Sales      | 2026-05-10 | 2500.00  | completed
    1002 | WangQiang  | Sales      | 2026-05-15 | 1800.00  | completed
    1005 | ZhaoMin    | Sales      | 2026-06-10 | 4100.00  | completed

-- ビューを使用して部門別の注文金額を集約
SELECT department, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM v_order_detail
GROUP BY department;

-- 現在のクエリの実行計画を表示
EXPLAIN SELECT * FROM employees WHERE last_name = 'Zhang';

+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+

-- インデックスを作成
CREATE INDEX idx_last_name ON employees(last_name);

-- 実行計画を再表示
EXPLAIN SELECT * FROM employees WHERE last_name = 'Zhang';

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key           | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE      | employees | ref  | idx_last_name | idx_last_name | 152     | const |    1 |       |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+

-- 複合インデックスを作成
CREATE INDEX idx_status_date ON orders(status, order_date);

-- インデックスを使用できるクエリ
EXPLAIN SELECT * FROM orders WHERE status = 'completed' AND order_date > '2026-06-01';

-- テーブルのすべてのインデックスを表示
SHOW INDEX FROM employees;

-- 機密情報を含まないビューを作成
CREATE OR REPLACE VIEW v_employee_public AS
SELECT 
    employee_id,
    CONCAT(first_name, last_name) AS name,
    d.department_name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 一般ユーザーはこのビューのみアクセスでき、給与やその他の機密情報にはアクセスできない
SELECT * FROM v_employee_public;

-- 月次売上レポートビューを作成
CREATE OR REPLACE VIEW v_monthly_sales AS
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_amount,
    COUNT(DISTINCT customer_id) AS customer_count
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- レポートをクエリ
SELECT * FROM v_monthly_sales ORDER BY month;

month   | order_count | total_sales | avg_order_amount | customer_count
--------+-------------+-------------+------------------+---------------
2026-05 |           2 |     4300.00 |          2150.00 |              2
2026-06 |           3 |     8250.00 |          2750.00 |              3