SQLコマンド集：基本から応用

はじめに

こんにちは、シルです。
よく使うSQLコマンドを忘れないように、いつでも見ることができるようにメモしておきます。
DBは、MySQLを想定してます。

説明：SQLコマンドの説明、使い方などを記載
サンプル：使用の一例を記載
メモ：サンプルの説明や扱う際の注意点などを記載

テーブル操作

CREATE – テーブルの作成

説明

• CREATE コマンドを使って新しいテーブルを作成
• 各列にデータ型を指定し、必要に応じて制約（例えば PRIMARY KEY や NOT NULL）を設定

サンプル

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE,
    Salary DECIMAL(10, 2)
);

メモ

• テーブルの定義において、PRIMARY KEY は一意の識別子を指定し、VARCHAR は可変長の文字列型を表す
• DECIMAL は、小数点を含む数値データ型

CREATE DATABASE – データベースの作成

説明

• CREATE DATABASE コマンドを使用して、新しいデータベースを作成

サンプル

CREATE DATABASE CompanyDB;

メモ

• CREATE DATABASE の後にデータベース名を指定
• データベースは新しい情報を保存するためのコンテナとして機能

DROP DATABASE – データベースの削除

説明

• DROP DATABASE コマンドでデータベースを完全に削除
• この操作はデータをすべて消去するため、注意が必要

サンプル

DROP DATABASE CompanyDB;

メモ

• DROP DATABASE は、指定したデータベースを削除し、すべてのテーブルやデータも一緒に消去される
• 復元はできないため、実行前にバックアップを取ることが推奨される

DROP TABLE – テーブルの削除

説明

• DROP TABLE コマンドを使ってテーブルを削除
• 削除されたテーブルのデータは復元できない

サンプル

DROP TABLE Employees;

メモ

• DROP TABLE を使うと、指定したテーブルが削除され、すべてのデータが消去される

ALTER TABLE – テーブル構造の変更

説明

• ALTER TABLE コマンドを使って、既存のテーブルに新しい列を追加したり、列のデータ型を変更したりする

サンプル（列の追加）

ALTER TABLE Employees
ADD COLUMN Email VARCHAR(100);

サンプル（列の削除）

ALTER TABLE Employees
DROP COLUMN Email;

メモ

• ADD COLUMN を使うと、新しい列をテーブルに追加できる
• DROP COLUMN で不要な列を削除することができる

RENAME TABLE – テーブル名の変更

説明

• RENAME TABLE コマンドを使用して、既存のテーブル名を変更

サンプル

RENAME TABLE Employees TO Staff;

メモ

• RENAME TABLE を使って、テーブル名を簡単に変更できる
• 名前の変更は、他の参照するクエリやコードに影響する可能性があるため、実行前に確認が必要

MySQLにログインする

説明

• MySQLサーバーにアクセスし、ユーザーとしてログインするためのコマンド
• 適切な認証情報が必要
• root はユーザー名で、-p はパスワード入力を求めるオプション

サンプル

mysql -u root -p

メモ

• ログイン後に、指定されたユーザーアカウントに関連する権限で操作を行うことができる
• 例えば、root ユーザーは通常すべての操作が許可されている

データベースを選択する

説明

• MySQLにログイン後、操作対象のデータベースを選択するためのコマンド
• 選択したデータベースに対してクエリや操作が実行される
• このコマンドは CompanyDB という名前のデータベースを選択する

サンプル

USE CompanyDB;

メモ

• USE コマンドは、データベースに対する操作の対象を指定する
• 一度データベースを選択すると、その後のクエリはすべてそのデータベースに対して実行される

データベースの権限を確認する

説明

• 現在のユーザーにどのような権限があるのかを確認するためのコマンド
• セキュリティや権限管理に重要

サンプル

SHOW GRANTS FOR 'username'@'localhost';

メモ

• SHOW GRANTS コマンドは、指定されたユーザーに対して割り当てられた権限の一覧を表示する
• 特定のユーザーの操作権限を確認し、セキュリティ上のリスクを管理するために使用する

ユーザー作成と権限付与

説明

• MySQL内で新しいユーザーを作成し、特定のデータベースに対して権限を付与するコマンド
• セキュリティを考慮したユーザー管理に使用する

サンプル

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON CompanyDB.* TO 'newuser'@'localhost';

メモ

• CREATE USER コマンドで新しいユーザーを作成し、GRANT コマンドで特定のデータベースに対して権限を付与する
• 権限は必要最低限に抑えることで、システムの安全性を保つことができる

ユーザーのパスワード変更

説明

• 既存ユーザーのパスワードを変更するためのコマンド
• セキュリティ強化のため、定期的にパスワードを更新することが推奨される

サンプル

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';

メモ

• ALTER USER コマンドを使用してユーザーのパスワードを変更する
• パスワードポリシー（長さ、複雑さなど）を設定することも推奨される

テーブルの構造を確認する

説明

• 既存のテーブルの構造（カラム名、データ型、制約など）を確認するためのコマンド
• デバッグやメンテナンス時に役立つ

サンプル

DESCRIBE Employees;

メモ

• DESCRIBE コマンドでテーブルの構造を確認できる
• 各列のデータ型や制約を確認し、データベース設計の見直しや最適化に役立てる

ユーザー削除

説明

• 不要になったユーザーを削除するためのコマンド
• セキュリティリスクを減らすために、不要なユーザーは削除することが推奨される

サンプル

DROP USER 'username'@'localhost';

メモ

• DROP USER コマンドは、データベースにアクセスできる不要なユーザーを削除する
• セキュリティ管理の一環として、不要なアカウントを定期的に削除することが推奨される

データベースやテーブルのサイズ確認

説明

• データベースやテーブルのサイズを確認することで、リソース管理やパフォーマンス最適化を行う

サンプル

SELECT table_schema AS 'Database', 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' 
FROM information_schema.tables 
GROUP BY table_schema;

メモ

• information_schema.tables を使うことで、データベース全体やテーブルのサイズを簡単に確認できる
• データベースのパフォーマンスを改善するため、定期的に確認することが重要

SHOW DATABASES – 利用可能なデータベースの表示

説明

• 現在の MySQL サーバー上に存在するすべてのデータベースを表示するコマンド

サンプル

SHOW DATABASES;

メモ

• これにより、サーバーに存在するすべてのデータベースのリストが表示される
• 現在アクセス可能なデータベースを確認する際に役立つ

SHOW TABLES – データベース内のテーブルを表示

説明

• 現在選択されているデータベースに含まれるすべてのテーブルを表示するコマンド

サンプル

SHOW TABLES;

メモ

• SHOW TABLES は、選択されたデータベース内にどのテーブルが存在するかを確認できる
• テーブル名の確認に使用する

SHOW COLUMNS – テーブルの列（カラム）を表示

説明

• 特定のテーブルに含まれる列（カラム）の情報（名前、データ型、制約など）を表示するコマンド

サンプル

SHOW COLUMNS FROM Employees;

メモ

• SHOW COLUMNS コマンドは、テーブル内の列名やデータ型、NULL 許可状況、デフォルト値などを確認するのに役立つ
• これは、DESCRIBE コマンドと同様の機能

SHOW STATUS – サーバーステータスを表示

説明

• MySQL サーバーのステータスや稼働状況に関する情報を表示する
• 接続数、クエリ数などのサーバーのパフォーマンスに関する情報が含まれる

サンプル

SHOW STATUS;

メモ

• サーバーの状態を確認して、パフォーマンスの監視やトラブルシューティングを行う際に使用する
• 特定のステータス項目を確認するには、以下のように指定する

SHOW STATUS LIKE 'Threads_connected';

SHOW INDEX – テーブルのインデックスを表示

説明

• 特定のテーブルに設定されているインデックスの情報を表示するコマンド
• インデックスの最適化やパフォーマンス改善に使用される

サンプル

SHOW INDEX FROM Employees;

メモ

• インデックスに関する情報（インデックス名、ユニークかどうか、カラムなど）を確認することができる
• インデックスを正しく管理し、クエリのパフォーマンスを向上させるために有用

SHOW PROCESSLIST – 実行中のクエリを表示

説明

• 現在実行中のクエリや接続に関する情報を表示する
• トラブルシューティングやデバッグに使用される

サンプル

SHOW PROCESSLIST;

メモ

• 現在実行中のクエリや、各接続の状態を確認することができる
• デッドロックやクエリの長時間実行などの問題が発生している場合に役立つ

SHOW VARIABLES – サーバー設定の確認

説明

• MySQL サーバーの設定に関する変数を表示する
• 設定の詳細（バッファサイズ、タイムアウト時間など）を確認したい場合に使用される

サンプル

SHOW VARIABLES;

メモ

• MySQL サーバーの設定変数を確認できる
• 特定の変数を確認するには、以下のように LIKE 句を使って検索できる

SHOW VARIABLES LIKE 'max_connections';

SHOW CREATE TABLE – テーブル作成文を表示

説明

• 既存のテーブルの作成時の SQL 文を表示するコマンド
• テーブル定義を確認したり、同じテーブルを他の場所で再作成するために使用する

サンプル

SHOW CREATE TABLE Employees;

メモ

• テーブル定義の完全な SQL 文（CREATE TABLE 文）を表示する
• データベースのバックアップやテーブルの複製を行う際に役立つ

SHOW CREATE DATABASE – データベース作成文を表示

説明

• 既存のデータベースの作成 SQL 文を表示するコマンド
• データベース定義を確認するのに使用される

サンプル

SHOW CREATE DATABASE CompanyDB;

メモ

• SHOW CREATE DATABASE を使うことで、指定したデータベースの作成 SQL 文を取得できる
• これにより、データベース定義を再利用したり、他の環境で同様のデータベースを作成できる

SHOW ENGINE STATUS – ストレージエンジンのステータスを表示

説明

• MySQL ストレージエンジン（InnoDB など）の状態を表示する
• トランザクション管理やロック管理に役立つ情報が含まれる

サンプル

SHOW ENGINE INNODB STATUS;

メモ

• ストレージエンジンに関連する詳細な情報が提供され、特にトランザクションやデッドロックのトラブルシューティングに役立つ

データ操作

INSERT – データの挿入

説明

• INSERT コマンドを使用して、テーブルに新しいデータを追加

サンプル

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, Salary)
VALUES (1, 'John', 'Doe', '2022-01-01', 50000.00);

メモ

• 列名を指定し、その順に値を入力
• VALUES 句の中で値を設定

SELECT – データの取得

説明

• SELECT コマンドを使用して、テーブル内のデータを取得
• 条件付きで特定のデータを取得するために WHERE 句も使用

サンプル

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 40000
ORDER BY Salary DESC;

メモ

• SELECT は取得したい列を指定し、WHERE 句で条件を絞り込み
• ORDER BY で並び替えを行い、DESC は降順を意味

UPDATE – データの更新

説明

• UPDATE コマンドで、既存のデータを変更
• WHERE 句を使用して特定の行のみを更新

サンプル

UPDATE Employees
SET Salary = Salary * 1.10
WHERE EmployeeID = 1;

メモ

• SET 句を使って列の値を更新
• WHERE 句がないと、テーブル全体のデータが変更されるので注意が必要

DELETE – データの削除

説明

• DELETE コマンドを使用して、テーブルからデータを削除
• WHERE 句で削除対象を指定

サンプル

DELETE FROM Employees
WHERE EmployeeID = 1;

メモ

• WHERE 句を使用して、特定の行を削除
• WHERE 句を指定しないとテーブルの全データが削除される点に注意

副問い合わせ – サブクエリの利用

説明

• 副問い合わせ（サブクエリ）は、メインクエリ内で別のクエリを実行するために使用
• 複雑な条件やデータのフィルタリングに役立つ

サンプル

SELECT FirstName, LastName
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

メモ

• サブクエリを WHERE 句で使用し、全従業員の平均給与よりも高い給与を持つ従業員を抽出

TRUNCATE – テーブルのデータを一括削除

説明

• TRUNCATE コマンドを使用して、テーブル内のすべてのデータを削除しますが、テーブル自体は残る
• DELETE と違い、TRUNCATE は高速で、AUTO_INCREMENT の値もリセットされる

サンプル

TRUNCATE TABLE Employees;

メモ

• TRUNCATE はすべてのデータを削除しますが、テーブル構造はそのままとなる
• また、AUTO_INCREMENT も初期値にリセットされるため、新しいデータ挿入時に ID が 1 から再開される

ALTER TABLE AUTO_INCREMENT – AUTO_INCREMENT のリセット

説明

• AUTO_INCREMENT は通常、データ挿入ごとに自動的にインクリメントされるが、特定の状況でリセットしたい場合に使用する

サンプル

ALTER TABLE Employees AUTO_INCREMENT = 1;

メモ

• テーブルの AUTO_INCREMENT をリセットし、次に挿入される行の ID を指定した数値に変更する
• 新しい挿入はこのリセット値から始まる

LIKE – 部分一致検索

説明

• LIKE 演算子は、文字列データを検索する際に使用され、指定したパターンに一致するデータをフィルタリングする
• % や _ などのワイルドカードを使って部分一致を表現できる
• このクエリは、FirstName が “J” で始まるすべての従業員を検索する

サンプル

SELECT FirstName, LastName
FROM Employees
WHERE FirstName LIKE 'J%';

メモ

• % は0文字以上の任意の文字列に一致します（例: ‘J%’ は “J” で始まる任意の文字列）。
• _ は1文字に一致します（例: ‘J_n’ は “Jan”, “Jen” など）。
• LIKE 演算子は部分一致検索やパターンマッチングに適しており、名前や住所などの曖昧な検索でよく使用されます。

NOT LIKE – パターン除外検索

説明

• NOT LIKE 演算子は、指定されたパターンに一致しないデータをフィルタリングする
• あいまい検索の否定条件を使いたい場合に便利
• このクエリは、LastName が “S” で始まらない従業員を検索する

サンプル

SELECT FirstName, LastName
FROM Employees
WHERE LastName NOT LIKE 'S%';

メモ

• NOT LIKE を使用することで、特定のパターンに一致しないデータを除外できる
• パターンの否定条件を使った検索に役立つ

INSTR() – 部分文字列の位置を検索

説明

• INSTR() 関数は、文字列内で部分文字列が最初に出現する位置を返す
• 検索条件として、文字列の特定の位置に関する情報が必要な場合に使用する

サンプル

SELECT FirstName, LastName
FROM Employees
WHERE INSTR(FirstName, 'an') > 0;

メモ

• INSTR() 関数は、文字列内に部分文字列が存在するかを確認するために使用できる
• 返り値が 0 より大きい場合、その文字列に部分文字列が存在する

REGEXP – 正規表現によるあいまい検索

説明

• REGEXP 演算子は、正規表現に基づくパターン検索を行う
• 複雑なパターンマッチングが必要な場合に非常に便利
• このクエリは、FirstName が “J” で始まり、“n” で終わるすべての従業員を検索する（例: “John”, “Jason” など）

サンプル

SELECT FirstName, LastName
FROM Employees
WHERE FirstName REGEXP '^J.*n$';

メモ

• 正規表現を使用することで、さらに柔軟なパターン検索が可能
• ^ は文字列の先頭を、$ は文字列の末尾を表します。.* は0文字以上の任意の文字列に一致する
• 正規表現は、より高度な検索条件を作成するのに非常に役立ちますが、パフォーマンスに影響を与える可能性があるため、必要に応じて使用する

CONCAT() と LIKE の組み合わせ – 動的パターン検索

説明

• CONCAT() 関数を使用して、動的に検索パターンを生成し、LIKE 演算子であいまい検索を行う
• 複数の条件を動的に組み合わせる際に役立つ

サンプル

SELECT FirstName, LastName
FROM Employees
WHERE FirstName LIKE CONCAT('%', 'John', '%');

メモ

• CONCAT() 関数で複数の文字列を結合し、LIKE 演算子と組み合わせることで、動的に検索パターンを作成できます。
• 動的検索やユーザー入力に基づく検索に便利です。

FIND_IN_SET() – カンマ区切りの文字列検索

説明

• FIND_IN_SET() 関数は、カンマで区切られた文字列の中に特定の値が含まれているかを検索するために使用される
• カテゴリーやタグのように複数の値を持つフィールドを検索する際に便利
• このクエリは、Roles 列に “Manager” を含む従業員を検索する

サンプル

SELECT FirstName, LastName
FROM Employees
WHERE FIND_IN_SET('Manager', Roles);

メモ

• カンマ区切りのフィールドを持つデータに対して検索を行う場合、FIND_IN_SET() が役立つ
• 複数の選択肢があるフィールド（カテゴリーやタグ）でよく使用される

ウィンドウ関数

ROW_NUMBER() – 順位付け

説明

• ROW_NUMBER() 関数は、指定された順序でデータに対して一意の番号を割り振る
• データセット内で行ごとに異なる番号を付与するために使用される

サンプル

SELECT EmployeeID, FirstName, Salary, 
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber
FROM Employees;

メモ

• ROW_NUMBER() は、指定された列に基づいて各行にユニークな番号を付与する
• 結果セットの順序付けに役立ちますが、重複した順位が発生しない点が RANK() と異なる

RANK() – 順位付け（同順位あり）

説明

• RANK() 関数は、指定された列に基づいてデータに順位を付ける
• 同じ値のデータには同じ順位が割り振られ、その次の順位は同順位のデータ数に応じてスキップされる
  （ギャップが生じる）

サンプル

SELECT EmployeeID, FirstName, Salary, 
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

メモ

• RANK() 関数は、同じ値を持つ行には同じ順位を割り当て、次の順位はその数に応じて飛ばされる
  たとえば、2位が2人いる場合、次の行は4位となる
  同順位が必要な場合や順位にギャップを作りたい場合に使用する
• RANK() と ROW_NUMBER() の違い
  ROW_NUMBER() は、同じ値があっても一意の番号を付けるのに対し、RANK() は同順位を付け、その次の順位にギャップが生じる点で異なります。

LEAD() – 次の行の値を取得

説明

• LEAD() 関数は、現在の行から指定されたオフセットに位置する行の値を取得する
• データ分析で次の行の値を参照したい場合に使用される

サンプル

SELECT EmployeeID, FirstName, Salary,
LEAD(Salary, 1) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;

メモ

• LEAD() 関数を使うことで、現在の行の次に続く行の値を参照できる
• 例えば、従業員の現在の給与と次の従業員の給与を比較する場合に便利

LAG() – 前の行の値を取得

説明

• LAG() 関数は、現在の行から指定されたオフセットに位置する前の行の値を取得する
• 前の行のデータを基にした計算を行いたい場合に役立つ

サンプル

SELECT EmployeeID, FirstName, Salary,
LAG(Salary, 1) OVER (ORDER BY Salary) AS PreviousSalary
FROM Employees;

メモ

• LAG() 関数を使うと、現在の行と前の行を比較したり、前の行の値を使用して計算を行うことができる

NTILE() – グループ分け

説明

• NTILE() 関数は、結果セットを指定したグループ数に均等に分割する
• 例えば、全データを 4 つのグループに分けて、それぞれの順位を確認する際に使用する

サンプル

SELECT EmployeeID, FirstName, Salary,
NTILE(4) OVER (ORDER BY Salary DESC) AS SalaryGroup
FROM Employees;

メモ

• NTILE() 関数を使うと、データセットを均等なグループに分割し、各グループに番号を割り当てることができる
• 例えば、トップ25％、次の25％といった順位分けを簡単に実行できる

集計・分析

GROUP BY – グループ化

説明

• GROUP BY コマンドを使用して、特定の列でデータをグループ化し、集計関数を使った分析を行う

サンプル

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 40000;

メモ

• GROUP BY を使うことで、AVG や SUM などの集計関数を列ごとに計算できる
• HAVING 句は、集計後の結果に条件を付けるために使用

INNER JOIN – 内部結合

説明

• INNER JOIN は、両方のテーブルで一致するデータのみを取得する結合
• 最も一般的な結合の形式

サンプル

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

メモ

• 両方のテーブルで一致する行のみを取得するため、非一致のデータは除外される

LEFT JOIN (LEFT OUTER JOIN) – 左外部結合

説明

• LEFT JOIN は、左側のテーブルにあるすべてのデータを取得し、右側のテーブルで一致するデータがない場合は NULL を返す

サンプル

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

メモ

• 左側のテーブル（Employees）のすべての行が返され、対応するデータが Departments に存在しない場合は NULL が表示される。

RIGHT JOIN (RIGHT OUTER JOIN) – 右外部結合

説明

• RIGHT JOIN は、右側のテーブルにあるすべてのデータを取得し、左側のテーブルで一致するデータがない場合は NULL を返す。

サンプル

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

メモ

• 右側のテーブル（Departments）のすべての行が返され、対応するデータが Employees に存在しない場合は NULL が表示される

FULL OUTER JOIN – 全外部結合

説明

• FULL OUTER JOIN は、両方のテーブルから一致しないデータも含めてすべてのデータを取得し、一致しない部分には NULL を返す

サンプル

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

メモ

• 両テーブルのすべてのデータを取得し、いずれかのテーブルでデータが存在しない場合は NULL を返す

CROSS JOIN – クロス結合

説明

• CROSS JOIN は、両方のテーブルのすべての組み合わせを返す
• テーブルの直積（Cartesian Product）を作成する

サンプル

SELECT e.FirstName, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

メモ

• すべての従業員とすべての部署の組み合わせを返す
• テーブルに存在するすべての行が他のテーブルのすべての行と組み合わせられる

SELF JOIN – 自己結合

説明

• SELF JOIN は、同じテーブルを2回参照してデータを結合する
• 例えば、従業員テーブル内で上司と部下の関係を表現する場合に使用される

サンプル

SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

メモ

• 自己結合は、同じテーブル内の異なる行を結合するために使用される
• 上司と部下の関係などの階層構造を扱うのに便利

NATURAL JOIN – 自然結合

説明

• NATURAL JOIN は、共通の列名を自動的に基準として結合する操作
• ON 句は不要

サンプル

SELECT *
FROM Employees
NATURAL JOIN Departments;

メモ

• 自動的に同名の列を基準に結合するため、手動で ON 句を指定する必要はない
• 列名が異なる場合は使用できない点に注意

COUNT() – レコード数を取得

説明

• COUNT() 関数は、特定の列に含まれるレコード数を数える
• NULL の値はカウントされない

サンプル

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

メモ

• COUNT() 関数は、指定された列の中で NULL 以外の値をカウントする
• すべての行をカウントしたい場合は COUNT(*) を使用する

SUM() – 合計値を取得

説明

• SUM() 関数は、指定した列の合計値を計算する
• 数値データに対して使用され、全レコードの合計を算出する

サンプル

SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID;

メモ

• SUM() 関数は数値の合計を計算するために使用され、特定の条件に一致するレコードの合計を簡単に取得できる

AVG() – 平均値を取得

説明

• AVG() 関数は、数値データの平均値を計算する
• 特定の列の平均を取得する際に便利

サンプル

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;

メモ

• AVG() 関数は、指定された列の数値データの平均を計算する
• 例えば、従業員の平均給与を知りたい場合に使用される

MAX() – 最大値を取得

説明

• MAX() 関数は、特定の列から最大値を返す
• データセット内の最も大きい値を取得したい場合に使用する

サンプル

SELECT MAX(Salary) AS HighestSalary
FROM Employees;

メモ

• MAX() 関数を使うと、列内の最大値を取得できる
• 例えば、従業員の中で最も高い給与を持つ従業員の給与を知りたい場合に便利

MIN() – 最小値を取得

説明

• MIN() 関数は、特定の列から最小値を返す
• データセット内の最も小さい値を取得したい場合に使用する

サンプル

SELECT MIN(Salary) AS LowestSalary
FROM Employees;

メモ

• MIN() 関数を使うと、列内の最小値を取得できる
• 例えば、従業員の中で最も低い給与を持つ従業員の給与を知りたい場合に使用される

インデックス操作

CREATE INDEX – インデックスの作成

説明

• CREATE INDEX を使って、テーブルにインデックスを作成し、検索クエリのパフォーマンスを向上させる

サンプル

CREATE INDEX idx_salary ON Employees(Salary);

メモ

• インデックスを作成することで、WHERE 句で指定した列の検索速度を向上
• 特に大規模なデータを扱う場合に効果的

DROP INDEX – インデックスの削除

説明

• 不要になったインデックスを削除する場合は、DROP INDEX を使用

サンプル

DROP INDEX idx_salary ON Employees;

メモ

• パフォーマンス向上のために作成したインデックスが不要になった場合、DROP INDEX で削除する
• インデックスの管理はパフォーマンスに大きく影響を与えるため、適切なインデックスを維持する

UNIQUE INDEX – ユニークインデックスの作成

説明

• UNIQUE INDEX は、列の値が一意であることを保証するインデックス
• 重複する値の挿入を防ぐ

サンプル

CREATE UNIQUE INDEX idx_unique_email ON Employees(Email);

メモ

• UNIQUE キーワードを使うことで、列の値が重複しないように強制する
• 特に、メールアドレスやユーザーIDのように重複が許されないデータに使う

COMPOSITE INDEX – 複合インデックスの作成

説明

• 複数の列に対してインデックスを作成する COMPOSITE INDEX（複合インデックス）は、複数の列を使った検索を高速化する際に役立つ

サンプル

CREATE INDEX idx_emp_name_salary ON Employees(LastName, Salary);

メモ

• 複数の列をインデックスにすることで、複合的な検索条件でのパフォーマンスを向上させる
• 例えば、従業員の姓と給与の両方を使った検索が高速になる

SHOW INDEX – インデックスの確認

説明

• テーブルに作成されたインデックスを確認するためには、SHOW INDEX コマンドを使用する
• これにより、インデックスの詳細情報が表示される

サンプル

SHOW INDEX FROM Employees;

メモ

• SHOW INDEX はテーブルに存在するすべてのインデックス情報を表示する
• インデックスの名前、作成対象の列、ユニーク制約の有無などが確認できる

ALTER TABLE … ADD INDEX – 既存テーブルへのインデックス追加

説明

• すでに存在しているテーブルに後からインデックスを追加したい場合は、ALTER TABLE コマンドを使用してインデックスを作成する

サンプル

ALTER TABLE Employees
ADD INDEX idx_lastname (LastName);

メモ

• 既存のテーブルにインデックスを追加する際に使用
• 特に、パフォーマンスの改善が必要なクエリを特定した後に、このコマンドを使用してインデックスを追加する

ALTER TABLE … DROP INDEX – インデックスの削除

説明

• 不要になったインデックスを削除したい場合は、ALTER TABLE コマンドを使用してインデックスを削除する

サンプル

ALTER TABLE Employees
DROP INDEX idx_lastname;

メモ

• 不要なインデックスはパフォーマンスに悪影響を与えることがあるため、定期的に整理することが大切
• ALTER TABLE を使ってインデックスを個別に削除する

FULLTEXT INDEX – フルテキストインデックスの作成

説明

• FULLTEXT INDEX は、大量のテキストデータに対する検索を効率的に行うためのインデックス
• 特に、テキスト検索のパフォーマンス向上に有効

サンプル

CREATE FULLTEXT INDEX idx_fulltext_name ON Employees(FirstName, LastName);

メモ

• FULLTEXT INDEX を使うと、特定のテキストフィールド内で部分一致や全文検索を高速化できる
• 主に、検索エンジン機能や大量のテキストデータを扱うアプリケーションで利用される

SPATIAL INDEX – 空間インデックスの作成

説明

• SPATIAL INDEX は、空間データ（地理情報や座標など）に対する検索を高速化するために使われる
• GIS（地理情報システム）などで利用される

サンプル

CREATE SPATIAL INDEX idx_location ON Locations(geo_point);

メモ

• 空間インデックスは、地理的な座標データを含む列に対して作成する
• 座標データを扱うクエリのパフォーマンスを大幅に改善できる

ANALYZE TABLE – インデックスの統計情報更新

説明

• ANALYZE TABLE コマンドを使って、インデックスの統計情報を更新
• これにより、クエリオプティマイザが最適な実行計画を立てられるようになる

サンプル

ANALYZE TABLE Employees;

メモ

• ANALYZE TABLE を定期的に実行することで、データの変更に伴ってインデックス統計を更新し、クエリパフォーマンスの最適化を図る

OPTIMIZE TABLE – インデックスの最適化

説明

• OPTIMIZE TABLE は、インデックスやデータの断片化を解消し、テーブルのパフォーマンスを最適化する

サンプル

OPTIMIZE TABLE Employees;

メモ

• OPTIMIZE TABLE を定期的に実行することで、テーブルの断片化を解消し、インデックスやテーブル全体のパフォーマンスを向上させる
• 特に頻繁にデータの挿入・更新・削除が行われるテーブルで効果的

バックアップとリカバリ

mysqldump コマンドでのデータベースバックアップ

説明

• mysqldump ツールを使用して、データベース全体のバックアップを取得する
• MySQLのコマンドラインツール

サンプル

mysqldump -u root -p mydatabase > backup.sql

メモ

• mysqldump はデータベースのバックアップを作成するために使用される
• バックアップファイルは .sql 形式で保存され、再度インポート可能

テーブル単位のバックアップ

説明

• 特定のテーブルのみをバックアップする場合は、mysqldump にテーブル名を指定する

サンプル

mysqldump -u root -p mydatabase Employees > employees_backup.sql

メモ

• 特定のテーブルのバックアップを取得する際に、データベース全体ではなく必要なテーブルのみを指定することができる

バックアップファイルからのリストア

説明

• バックアップファイル（.sql）からデータベースを復元する

サンプル

mysql -u root -p mydatabase < backup.sql

メモ

• 以前に取得したバックアップファイルを使用して、データベースの状態を復元する

SELECT INTO OUTFILE – データのエクスポート

説明

• 特定のテーブルやクエリ結果を .csv ファイルなどにエクスポートする

サンプル

SELECT * FROM Employees
INTO OUTFILE '/tmp/employees.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

メモ

• テーブルのデータをファイルとしてエクスポートすることができる
• INTO OUTFILE を使用して、特定の形式でファイルに出力する

LOAD DATA INFILE – データのインポート

説明

• LOAD DATA INFILE コマンドを使用して、外部ファイルからテーブルにデータをインポートする

サンプル

LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE Employees
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

メモ

• ファイルからテーブルにデータをインポートするために使用する
• 大量のデータを一度に挿入する際に便利

SHOW MASTER STATUS – バイナリログの確認

説明

• レプリケーション環境やバックアップ用途で使用されるバイナリログの状態を確認する
• バイナリログはデータベース操作の履歴を記録するため、データの復旧に使用される

サンプル

SHOW MASTER STATUS;

メモ

• バイナリログを使用したデータベースのバックアップやリカバリのために、現在のバイナリログファイルとポジションを確認する

バイナリログからのバックアップ

説明

• バイナリログを使用して、ポイントインタイムリカバリ（特定時点の復旧）を行うために、ログを適用する

サンプル

mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -u root -p mydatabase

メモ

• バイナリログファイルを利用して、バックアップ後に行われた変更を再現するために使用する

テーブルのクローン作成

説明

• テーブルのデータと構造を複製し、新しいテーブルを作成する

サンプル

CREATE TABLE Employees_backup AS
SELECT * FROM Employees;

メモ

• テーブルの完全なコピーを作成し、バックアップや一時的なデータ保存に使用する
• テーブルのクローンを作成して、検証やテストにも利用可能