90 views

以下に、具体的なデータ例を使ったMySQLのコマンド集を示します。今回は、employeesデータベースに含まれるemployeesテーブルとdepartmentsテーブルを例にします。

データベースとテーブルの作成

データベースの作成
```
CREATE DATABASE employees;
```

テーブルの作成

USE employees;

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50)
);

データの挿入

employeesテーブルのデータ挿入

INSERT INTO employees (first_name, last_name, department_id, salary) VALUES
    ('John', 'Doe', 1, 50000.00),
    ('Jane', 'Smith', 2, 60000.00),
    ('Alice', 'Johnson', 1, 55000.00),
    ('Bob', 'Brown', 3, 65000.00);

departmentsテーブルのデータ挿入

INSERT INTO departments (department_name) VALUES
    ('Sales'),
    ('Engineering'),
    ('Marketing');

データ取得

全データの取得

SELECT * FROM employees;

結果例：

id	first_name	last_name	department_id	salary
1	John	Doe	1	50000.00
2	Jane	Smith	2	60000.00
3	Alice	Johnson	1	55000.00
4	Bob	Brown	3	65000.00

特定の列のみ取得

SELECT first_name, salary FROM employees;

条件付きでデータを取得

SELECT * FROM employees WHERE salary > 55000;

データの並び替え（高い給与順）

SELECT * FROM employees ORDER BY salary DESC;

集計とグループ化

平均給与の計算

SELECT AVG(salary) AS average_salary FROM employees;

部門ごとの平均給与

SELECT department_id, AVG(salary) AS average_salary 
FROM employees 
GROUP BY department_id;

部門ごとに従業員数をカウント

SELECT department_id, COUNT(*) AS num_employees 
FROM employees 
GROUP BY department_id;

結合操作

内部結合（employeesとdepartmentsの情報を結合）

SELECT e.first_name, e.last_name, d.department_name, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

結果例：

first_name	last_name	department_name	salary
John	Doe	Sales	50000.00
Jane	Smith	Engineering	60000.00
Alice	Johnson	Sales	55000.00
Bob	Brown	Marketing	65000.00

左外部結合（全ての従業員とその部門を表示、部門がない場合も含む）

SELECT e.first_name, e.last_name, d.department_name, e.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

インデックス管理

インデックスの作成（first_name列）

CREATE INDEX idx_first_name ON employees (first_name);

インデックスの削除

DROP INDEX idx_first_name ON employees;

ユーザー管理と権限

ユーザー作成と権限付与

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON employees.* TO 'report_user'@'localhost';

ユーザー削除
```
DROP USER 'report_user'@'localhost';
```

トランザクション管理

トランザクションの開始、データ変更、コミット

START TRANSACTION;
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 1;
COMMIT;

ロールバックの利用（変更を破棄）

START TRANSACTION;
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 1;
ROLLBACK;

データベース操作

データベースのバックアップ
MySQLのmysqldumpを使用してデータベース全体をバックアップできます。
```
mysqldump -u username -p employees > employees_backup.sql
```
データベースの復元
取得したバックアップを復元する際は、以下のコマンドを実行します。
```
mysql -u username -p employees < employees_backup.sql
```
テーブルのコピー
既存のテーブルをコピーする場合には、以下のコマンドを使用します。
```
CREATE TABLE employees_copy AS SELECT * FROM employees;
```

高度なデータ操作

LIKEを使用した部分一致検索
名前に「Jo」が含まれる従業員を検索します。
```
SELECT * FROM employees WHERE first_name LIKE '%Jo%';
```
IN句を使用して複数の値を検索
複数の部門に所属する従業員を取得する例。
```
SELECT * FROM employees WHERE department_id IN (1, 2);
```

CASE式で条件分岐
給与に応じてランクを分けて表示します。

SELECT first_name, last_name,
    CASE
        WHEN salary >= 60000 THEN 'High'
        WHEN salary >= 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_rank
FROM employees;

サブクエリの活用

サブクエリで最大給与を持つ従業員を検索
サブクエリを使用して最大給与を持つ従業員を特定します。
```
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
```
サブクエリで複数の条件を設定
部門ごとの平均給与よりも高い給与を持つ従業員を取得します。
```
SELECT * FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
```

集計関数

標準偏差を求める
給与の標準偏差を取得します。
```
SELECT STDDEV(salary) AS salary_stddev FROM employees;
```
中央値を求める
MySQLに直接の中央値関数はないため、以下のような方法で近似します。
```
SELECT salary FROM employees ORDER BY salary LIMIT 1 OFFSET (SELECT COUNT(*) FROM employees) / 2;
```

ロールアップとCUBE

ROLLUPで総計を追加
部門ごとの給与合計と総計を取得します。

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id WITH ROLLUP;

CUBEを使用して複数の集計を行う（MySQL 8.0以降）
すべての組み合わせでの集計を行います。
```
SELECT department_id, first_name, SUM(salary)
FROM employees
GROUP BY department_id, first_name WITH CUBE;
```

ウィンドウ関数

RANK関数を使用して順位をつける
給与が高い順に順位を付けます（MySQL 8.0以降）。

SELECT first_name, last_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

ウィンドウ関数で累積合計を計算
給与の累積合計を計算します。

SELECT first_name, last_name, salary,
       SUM(salary) OVER (ORDER BY salary) AS cumulative_salary
FROM employees;

JSONデータの操作

JSONデータの挿入
JSON型のカラムにデータを挿入します（MySQL 5.7以降）。

CREATE TABLE employee_info (
    id INT PRIMARY KEY,
    info JSON
);

INSERT INTO employee_info (id, info) VALUES
    (1, '{ "first_name": "John", "last_name": "Doe", "skills": ["PHP", "MySQL"] }'),
    (2, '{ "first_name": "Jane", "last_name": "Smith", "skills": ["Python", "Data Analysis"] }');

JSONフィールドからデータを取得
JSON内のデータを取得します。
```
SELECT id, info->"$.first_name" AS first_name FROM employee_info;
```

権限管理の詳細

テーブルごとの権限付与
特定テーブルに対してのみ権限を付与します。
```
GRANT SELECT, INSERT ON employees.employees TO 'user'@'localhost';
```
権限の取り消し
付与された権限を取り消します。
```
REVOKE INSERT ON employees.employees FROM 'user'@'localhost';
```

データベースのパフォーマンス調整

インデックスの確認
特定テーブルのインデックスを確認します。
```
SHOW INDEX FROM employees;
```
インデックスの再構築
インデックスを再構築し、パフォーマンスを向上させます。
```
ALTER TABLE employees ENGINE=InnoDB;
```
クエリのパフォーマンス分析
クエリの実行計画を確認し、パフォーマンスの問題を特定します。
```
EXPLAIN SELECT * FROM employees WHERE department_id = 1;
```

パーティショニング

範囲パーティショニングの設定
給与に基づいてパーティションを設定します。

CREATE TABLE partitioned_employees (
    id INT,
    salary DECIMAL(10,2),
    PRIMARY KEY (id, salary)
)
PARTITION BY RANGE (salary) (
    PARTITION p0 VALUES LESS THAN (30000),
    PARTITION p1 VALUES LESS THAN (60000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

トリガーの作成

データ挿入時のトリガー設定
employeesテーブルにデータが挿入されたときに、別のテーブルにログを記録するトリガー。

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
INSERT INTO employee_logs (employee_id, action, action_time) VALUES (NEW.id, 'INSERT', NOW());

Page 3 of 3.

[添付ファイル]

お問い合わせ

プロフィール

すぺぺぺ

自己紹介

本サイトの作成者。
プログラムは趣味と勉強を兼ねて、のんびり本サイトを作っています。
フレームワークはdjango。
ChatGPTで自動プログラム作成に取り組み中。

サイト/ブログ

https://www.osumoi-stdio.com/novel/

ツイッター

@darkimpact0626

データベース

SQL集