MySQL CLI 入門：コマンドラインでデータベースを操作しよう

By /

MySQL CLI 入門：コマンドラインでデータベースを操作しよう

はじめに：なぜMySQL CLIを学ぶのか

リレーショナルデータベース管理システム（RDBMS）の代表格であるMySQLは、Webアプリケーションのバックエンドとして、あるいは企業の基幹システムなどで広く利用されています。MySQLを操作するには、グラフィカルユーザーインターフェイス（GUI）を持つクライアントツール（例: MySQL Workbench, phpMyAdmin）を使う方法と、コマンドラインインターフェイス（CLI）を使う方法があります。

多くの初心者はGUIツールからMySQLの学習を始めるかもしれません。GUIツールは視覚的に分かりやすく、テーブル構造の確認や簡単なデータ操作を手軽に行えるため非常に便利です。しかし、データベースをより深く理解し、効率的に操作するためには、MySQL CLIの習得が不可欠です。

なぜCLIなのでしょうか？主な理由は以下の通りです。

  1. 高速かつ効率的: GUIツールを起動するオーバーヘッドがなく、直接コマンドを入力するため、慣れれば非常に高速に作業できます。
  2. 自動化とスクリプト化: 定期的なメンテナンス作業（バックアップ、データ更新など）や、複数のコマンドを連続して実行する場合、CLIを使えばシェルスクリプトなどで自動化できます。GUIツールでは難しい、あるいは不可能な操作もCLIなら可能です。
  3. リモート操作: SSHなどでサーバーに接続し、そのままCLIからMySQLを操作できます。GUIツールをサーバー上にインストールしたり、ローカルから接続設定を行ったりする手間が省けます。
  4. リソース消費が少ない: GUIツールに比べてメモリやCPUリソースの消費が少ないため、非力なサーバーや開発環境でも軽快に動作します。
  5. バージョンや環境に左右されにくい: CLIの基本的なコマンドはMySQLのバージョンが変わっても大きく変化しません。また、様々なOSや環境で一貫した操作が可能です。
  6. データベースの仕組み理解: SQLクエリを手で入力することで、どのようにデータが取得・操作されるのか、データベースの仕組みをより深く理解できます。
  7. トラブルシューティング: サーバー側のログを確認したり、複雑な設定変更を行ったりする際に、CLIでの操作知識が役立ちます。

この記事では、MySQL CLIを使ったデータベース操作の基本的な手順から、データベースやテーブルの作成、データの操作（CRUD: Create, Read, Update, Delete）、ユーザー管理、バックアップなど、実践的な知識を約5000語にわたり詳細に解説します。この記事を読めば、GUIツールだけでなく、コマンドラインからも自信を持ってMySQLを操作できるようになるでしょう。

1. MySQLのインストールとセットアップ

MySQL CLIを使うには、まずお使いのシステムにMySQLサーバーとクライアントツール（mysql コマンド）がインストールされている必要があります。ここでは主要なOSごとのインストール方法を簡単に紹介します。

注意: 既にMySQLがインストールされている場合は、この章は読み飛ばしてください。

Windowsでのインストール

MySQL公式サイトから「MySQL Installer for Windows」をダウンロードして実行するのが最も簡単です。インストーラーはMySQLサーバー本体だけでなく、MySQL WorkbenchやMySQL Shell、そしてもちろんMySQL CLIクライアント（mysql.exe）を含んでいます。

  1. MySQLの公式ダウンロードページ（https://dev.mysql.com/downloads/installer/）にアクセスします。
  2. 「MySQL Installer for Windows」をダウンロードします。通常、「web-community」バージョンで十分です。
  3. ダウンロードしたインストーラーを実行します。
  4. インストールタイプを選択します。「Developer Default」を選択すれば、MySQLサーバー、クライアント、Workbenchなどが一通りインストールされます。
  5. インストールウィザードに従って進めます。特に「Type and Networking」（サーバータイプやポート番号）、認証方法（「Use Strong Password Encryption」が推奨）、そしてrootユーザーのパスワード設定は重要です。rootパスワードは必ず控えておいてください。
  6. インストール完了後、コマンドプロンプトまたはPowerShellを開き、mysql --version と入力してバージョン情報が表示されればインストールは成功です。パスが通っていない場合は、MySQLインストールディレクトリの bin サブディレクトリに移動するか、環境変数 PATH に追加する必要があります。

macOSでのインストール

macOSではHomebrewというパッケージマネージャーを使うのが最も簡単です。

  1. Homebrewがインストールされていない場合は、公式ページ（https://brew.sh/index_ja）の手順に従ってインストールします。
  2. ターミナルを開き、以下のコマンドを実行します。
    bash
    brew install mysql
  3. インストール完了後、MySQLサーバーを起動します。
    bash
    brew services start mysql
  4. 初期セットアップを行います。セキュリティを高めるための設定です。
    bash
    mysql_secure_installation
    このコマンドを実行すると、rootパスワードの設定、匿名ユーザーの削除、リモートrootログインの禁止、テストデータベースの削除などの設定を対話形式で行えます。指示に従って設定してください。
  5. mysql --version でバージョンを確認できればインストールは成功です。

Linuxでのインストール (Debian/Ubuntu)

aptパッケージマネージャーを使用します。

  1. ターミナルを開き、パッケージリストを更新します。
    bash
    sudo apt update
  2. MySQLサーバーとクライアントをインストールします。
    bash
    sudo apt install mysql-server mysql-client
  3. インストール中にrootパスワードの設定を求められる場合があります。設定してください。
  4. インストール完了後、MySQLサーバーが起動しているか確認します。
    bash
    sudo systemctl status mysql
    起動していない場合は sudo systemctl start mysql で起動します。
  5. macOSと同様に初期セットアップを行います。
    bash
    sudo mysql_secure_installation
    指示に従って設定してください。
  6. mysql --version でバージョンを確認します。

Linuxでのインストール (RHEL/CentOS/Fedora)

yumまたはdnfパッケージマネージャーを使用します。

  1. ターミナルを開き、MySQL Community Repositoryをシステムに追加します。詳細はMySQL公式サイトを参照してください。
  2. リポジトリ設定後、パッケージリストを更新します。
    bash
    sudo yum update # または dnf
  3. MySQLサーバーをインストールします。
    bash
    sudo yum install mysql-community-server # または dnf install mysql-community-server
  4. インストール完了後、MySQLサーバーを起動し、システム起動時に自動起動するように設定します。
    bash
    sudo systemctl start mysqld
    sudo systemctl enable mysqld
  5. 初期rootパスワードを確認します。パスワードはインストールログファイルに一時的に記録されています。
    bash
    sudo grep 'temporary password' /var/log/mysqld.log
    または、バージョンによっては /var/log/mysql/error.log など異なる場合があります。
  6. 初期セットアップを行います。この際に一時パスワードでログインし、新しいrootパスワードを設定します。
    bash
    mysql_secure_installation
    指示に従って設定してください。新しいパスワードは強力なものにし、控えておいてください。パスワードポリシーによって、特定の要件を満たす必要があります。
  7. mysql --version でバージョンを確認します。

MySQLサーバーの起動・停止 (インストール後)

  • systemdを使用するシステム (多くのLinuxディストリビューション):
    bash
    sudo systemctl start mysql # 起動
    sudo systemctl stop mysql # 停止
    sudo systemctl restart mysql # 再起動
    sudo systemctl status mysql # 状態確認
    サービス名はディストリビューションやバージョンによって mysql または mysqld と異なる場合があります。

  • Homebrewを使用するmacOS:
    bash
    brew services start mysql # 起動
    brew services stop mysql # 停止
    brew services restart mysql # 再起動

  • Windows:
    Windowsサービスの管理ツールからMySQLサービスを手動で起動/停止するか、コマンドプロンプト（管理者として実行）から以下のように操作します。
    cmd
    net start MySQL # 起動 (サービス名はバージョンによる)
    net stop MySQL # 停止

2. MySQL CLIへの接続

MySQL CLIクライアントプログラム mysql を使ってMySQLサーバーに接続します。接続コマンドの基本的な形式は以下の通りです。

bash
mysql -u ユーザー名 -p

  • -u ユーザー名: 接続に使用するユーザー名を指定します。多くの場合、最初は root ユーザーを使用します。
  • -p: パスワード入力を促すプロンプトを表示させます。このオプションを付けずにコマンドを実行すると、パスワードなしで接続しようとします（rootユーザーでは通常許可されません）。

例：rootユーザーで接続する場合
bash
mysql -u root -p

このコマンドを実行すると、以下のようにパスワード入力プロンプトが表示されます。

Enter password:

ここで、rootユーザーのパスワードを入力します。入力したパスワードは画面には表示されません。正しいパスワードを入力してEnterキーを押すと、MySQLモニター（CLI環境）に接続されます。

“`
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is …
Server version: … MySQL Community Server – GPL

Copyright (c) … Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
“`

mysql> というプロンプトが表示されれば、接続成功です。これでSQLコマンドやMySQL固有のコマンドを入力できるようになります。

接続オプション

より詳細な接続設定を行うためのオプションもあります。

  • -h ホスト名: 接続先のMySQLサーバーが実行されているホスト名またはIPアドレスを指定します。デフォルトは localhost (127.0.0.1) です。リモートサーバーに接続する場合などに使用します。
    bash
    mysql -u user -p -h example.com
  • -P ポート番号: 接続先のMySQLサーバーが待機しているポート番号を指定します。デフォルトは 3306 です。
    bash
    mysql -u user -p -P 3307
  • -D データベース名: 接続と同時に使用するデータベースを選択します。
    bash
    mysql -u user -p -D mydatabase
    接続後に USE mydatabase; と実行するのと同じです。

パスワード入力に関する注意

mysql -u user -p と実行してパスワード入力を促すプロンプトで入力する方法が推奨されます。

mysql -u user -ppassword のように、-p オプションの直後にスペースを空けずにパスワードを記述する方法もあります。

“`bash

非推奨の方法

mysql -u root -pmypassword
“`

この方法はセキュリティ上のリスクがあるため、公開キーボードを使っている場合や、コマンド履歴が残る環境では避けるべきです。 シェルの履歴ファイルや、ps コマンドなどでパスワードが第三者に見えてしまう可能性があります。特別な理由がない限り、-p オプション単体を使用し、プロンプトでの入力を利用しましょう。

スクリプトなどでパスワードを自動的に渡したい場合は、パスワードファイルを指定する方法や、環境変数 MYSQL_PWD を利用する方法がありますが、これらも取り扱いには十分な注意が必要です。最も安全なのは、パスワードファイルを使用する方法です（--defaults-extra-file= オプション）。

接続エラーのトラブルシューティング

Can't connect to MySQL server on 'hostname' (errno 111) や類似のエラーが表示される場合、以下の原因が考えられます。

  • MySQLサーバーが起動していない: サーバーが実行されているか確認し、起動してください（上記の起動方法を参照）。
  • ホスト名またはIPアドレスが間違っている: -h オプションで正しいホスト名を指定しているか確認します。localhost で試してみるのも有効です。
  • ポート番号が間違っている: MySQLサーバーがデフォルト以外のポートで待機している場合、-P オプションで正しいポート番号を指定します。
  • ユーザー名またはパスワードが間違っている: 入力したユーザー名とパスワードが正しいか確認します。特にrootパスワードは mysql_secure_installation で変更している可能性があります。
  • ネットワークまたはファイアウォールの問題: クライアントからサーバーへの接続が、ファイアウォールによってブロックされていないか確認します。リモート接続の場合、サーバー側のMySQL設定でリモート接続が許可されているか確認が必要です（デフォルトでは bind-address = 127.0.0.1 となっており、localhost以外からの接続を許可しない設定になっていることが多いです）。
  • Unixソケットファイルの問題 (Linux/macOS): ローカル接続の場合、Unixソケットファイルが見つからない、または権限がない場合があります。mysql.sock ファイルの場所と権限を確認します。

3. 基本的なCLIコマンドとSQL文

MySQL CLIに接続後、mysql> プロンプトが表示されます。ここでSQLステートメントやMySQL固有のコマンドを入力してデータベースを操作します。コマンドの末尾にはセミコロン ; を付けるのが一般的です（SQLステートメントの区切り）。CLIコマンドの中には ; が不要なものもあります。

データベース一覧の表示

現在MySQLサーバーに存在するデータベースの一覧を表示します。

sql
SHOW DATABASES;

実行結果例：

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| your_database_name |
+--------------------+
5 rows in set (0.01 sec)

information_schema, mysql, performance_schema, sys はMySQLのシステムデータベースです。これらはMySQLの動作に必要な情報や設定を格納しており、通常は変更しません。

データベースの選択

操作対象とするデータベースを選択します。これ以降のテーブル操作などは、選択したデータベース内で行われます。

sql
USE データベース名;

例：your_database_name というデータベースを選択する場合

sql
USE your_database_name;

成功すると Database changed と表示されます。

現在使用しているデータベースの確認

現在どのデータベースを選択しているかを確認します。

sql
SELECT DATABASE();

実行結果例：

+----------------------+
| DATABASE() |
+----------------------+
| your_database_name |
+----------------------+
1 row in set (0.00 sec)

テーブル一覧の表示

選択中のデータベース内に存在するテーブルの一覧を表示します。

sql
SHOW TABLES;

実行結果例：

+------------------------------+
| Tables_in_your_database_name |
+------------------------------+
| users |
| products |
| orders |
+------------------------------+
3 rows in set (0.00 sec)

テーブル構造の確認

特定のテーブルのカラム構成（カラム名、データ型、NULL可能か、キー、デフォルト値など）を確認します。

sql
DESCRIBE テーブル名;

または短縮形

sql
DESC テーブル名;

例：users テーブルの構造を確認する場合

sql
DESC users;

実行結果例：

+-----------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+-------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | UNI | NULL | |
| email | varchar(100) | YES | | NULL | |
| created_at| datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------+--------------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)

  • Field: カラム名
  • Type: データ型
  • Null: NULLを許可するか (YES または NO)
  • Key: キー情報 (PRIはPRIMARY KEY, UNIはUNIQUE KEY, MULは非UNIQUEインデックスの一部)
  • Default: デフォルト値
  • Extra: 追加情報 (auto_incrementなど)

MySQLサーバー情報の確認

MySQLサーバーのバージョン、接続情報、現在の設定などを詳細に確認できます。

sql
STATUS;

実行結果例（一部）：

“`

mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server – GPL)

Connection id: 12345
Current database: your_database_name
Current user: root@localhost
SSL: Not in use
…
Server version: 8.0.26
Protocol version: 10
…
Character set client: utf8mb4
Character set connection: utf8mb4
Character set server: utf8mb4
…

“`

CLIからの終了

MySQLモニターを終了して、シェルのプロンプトに戻ります。

sql
EXIT;

または

sql
QUIT;

または短縮形

sql
\q

入力中のコマンドのキャンセル

長いコマンドを入力中に間違えた場合など、入力をキャンセルして新しいコマンドを開始したいときに使用します。

sql
\c

または

sql
\C

これにより、入力途中の内容は破棄され、新しい mysql> プロンプトが表示されます。

4. データベースの作成と削除

新しいデータベースを作成したり、不要になったデータベースを削除したりします。

データベースの作成

sql
CREATE DATABASE データベース名;

例：mydatabase というデータベースを作成する場合

sql
CREATE DATABASE mydatabase;

データベース名には、半角英数字、アンダースコア _、ドル記号 $ を使用できます。先頭に数字は使えません。

文字コードと照合順序の指定

データベースを作成する際に、文字コード（CHARACTER SET）と照合順序（COLLATE）を指定することが強く推奨されます。特に日本語を扱う場合は、適切な文字コードを指定しないと文字化けの原因になります。

推奨される文字コードは utf8mb4 です。これは絵文字を含むUnicodeの全ての文字を扱える文字コードです。utf8 は3バイトまでしか対応しておらず、絵文字など4バイト文字に対応できません。

照合順序は、文字の比較やソート順を定義します。utf8mb4_unicode_ci は、Unicodeの国際的な照合ルールに基づいており、大文字・小文字を区別しない (ci は Case Insensitive) 設定です。

sql
CREATE DATABASE mydatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

データベースが既に存在する場合の対応

CREATE DATABASE は、指定したデータベース名が既に存在する場合にエラーとなります。これを避けるには IF NOT EXISTS オプションを使用します。

sql
CREATE DATABASE IF NOT EXISTS mydatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

このコマンドは、mydatabase が存在しない場合のみ作成します。既に存在する場合は何もせず、エラーも発生しません（警告が表示されることはあります）。

データベースの削除

不要になったデータベースを削除します。この操作はデータベース内の全てのテーブルとデータ、権限などを完全に削除するため、非常に危険です。実行には十分注意し、必要に応じてバックアップを取ってください。

sql
DROP DATABASE データベース名;

例：mydatabase というデータベースを削除する場合

sql
DROP DATABASE mydatabase;

データベースが存在しない場合のエラーを回避

DROP DATABASE は、指定したデータベースが存在しない場合にエラーとなります。これを避けるには IF EXISTS オプションを使用します。

sql
DROP DATABASE IF EXISTS mydatabase;

このコマンドは、mydatabase が存在する場合のみ削除します。存在しない場合は何もせず、エラーも発生しません。

データベースを作成・削除する際は、まず SHOW DATABASES; で一覧を確認し、操作対象のデータベース名を正確に把握することが重要です。

5. テーブルの作成と削除

データベースを選択したら、その中にテーブルを作成し、データを格納できるようになります。テーブルは、関連するデータを構造化して保持するオブジェクトです。

テーブルの作成

CREATE TABLE ステートメントを使用してテーブルを作成します。テーブルの構造は、カラム名、各カラムのデータ型、および制約によって定義されます。

基本的な構文：

sql
CREATE TABLE テーブル名 (
カラム名1 データ型1 制約1,
カラム名2 データ型2 制約2,
...
テーブルレベル制約
);

例：簡単な users テーブルを作成する場合

sql
USE mydatabase; -- データベースを選択しておく
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

この例では、users という名前のテーブルを作成しています。

  • id: 整数型 (INT)。AUTO_INCREMENT は新しい行が追加されるたびに自動的に一意な連番が生成される設定です。PRIMARY KEY はこのカラムが主キーであることを示します（後述）。
  • username: 可変長文字列型 (VARCHAR) で最大50文字。NOT NULL はこのカラムにNULL値を格納できないことを示します。UNIQUE はこのカラムの値がテーブル内で重複してはならないことを示します。
  • email: 可変長文字列型 (VARCHAR) で最大100文字。制約がないため、NULL値を格納可能です。
  • created_at: 日時型 (DATETIME)。DEFAULT CURRENT_TIMESTAMP は、挿入時にこのカラムに値が指定されなかった場合に、現在のシステム日時が自動的に設定されることを示します。

主要なデータ型

MySQLには様々なデータ型があります。データの性質に合わせて適切な型を選択することが重要です。

  • 数値型:
    • TINYINT: 非常に小さい整数 (-128 to 127 または 0 to 255, サイズ1バイト)
    • SMALLINT: 小さい整数 (-32768 to 32767 または 0 to 65535, サイズ2バイト)
    • MEDIUMINT: 中くらいの整数 (-8388608 to 8388607 または 0 to 16777215, サイズ3バイト)
    • INT または INTEGER: 標準的な整数 (-2147483648 to 2147483647 または 0 to 4294967295, サイズ4バイト)
    • BIGINT: 大きい整数 (約 -9 quintillion to 9 quintillion, サイズ8バイト)
    • DECIMAL(M, D): 厳密な固定小数点数。Mは全体の桁数、Dは小数点以下の桁数。通貨など、正確な計算が必要な場合に推奨されます。
    • FLOAT: 単精度浮動小数点数。おおよその値。
    • DOUBLE: 倍精度浮動小数点数。おおよその値。
  • 文字列型:
    • VARCHAR(length): 可変長文字列。指定した最大長までの文字列を格納できます。使用した長さ+1バイトを消費します。最もよく使われる文字列型です。
    • CHAR(length): 固定長文字列。指定した長さを常に消費します（短い文字列でも余白が詰められます）。格納/取得が速いですが、スペース効率は悪い場合があります。
    • TEXT: 可変長文字列。VARCHARより長い文字列（最大65535文字）を格納できます。インデックス付けに制限がある場合があります。
    • TINYTEXT, MEDIUMTEXT, LONGTEXT: より長い文字列を格納するためのTEXT派生型。
  • 日付/時間型:
    • DATE: 日付 (YYYY-MM-DD)。
    • TIME: 時刻 (HH:MM:SS)。
    • DATETIME: 日付と時刻 (YYYY-MM-DD HH:MM:SS)。
    • TIMESTAMP: 日付と時刻。UTCで格納され、セッションのタイムゾーンで取得されます。自動更新機能などがあり、よく使われます。DATETIME と似ていますが、値の範囲や内部表現が異なります。
    • YEAR: 年 (YYYY)。
  • バイナリ型:
    • BLOB: 可変長のバイナリデータ。画像や音声など。
    • BINARY(length): 固定長バイナリデータ。
    • VARBINARY(length): 可変長バイナリデータ。
  • その他:
    • BOOLEAN: 真偽値。実際には TINYINT(1) のエイリアスです（0が偽、非ゼロが真）。
    • ENUM('value1', 'value2', ...): 定義済みの値のリストから1つを選択。
    • SET('value1', 'value2', ...): 定義済みの値のリストから0個以上を選択（ビットフラグとして格納）。

データ型を選択する際は、格納するデータの種類、最大長、数値の精度、検索やソートの頻度などを考慮します。

主要な制約

制約は、テーブル内のデータの整合性を保証するためのルールです。

  • PRIMARY KEY: そのカラム（またはカラムの組み合わせ）がテーブル内の各行を一意に識別するためのキーであることを示します。PRIMARY KEYを持つカラムは自動的にNOT NULLとUNIQUEになります。テーブルごとに1つだけ設定できます。
  • AUTO_INCREMENT: 数値型のPRIMARY KEYまたはUNIQUE KEYを持つカラムに設定し、新しい行が挿入されるたびに自動的に値を増加させます。
  • NOT NULL: そのカラムにNULL値を格納できないことを強制します。
  • UNIQUE: そのカラムの値がテーブル内で重複してはならないことを強制します。NULL値は複数格納できます（UNIQUE制約違反にはなりません）。
  • DEFAULT value: 値が指定されずに新しい行が挿入された場合に、自動的に設定されるデフォルト値を指定します。

  • FOREIGN KEY: 他のテーブルのカラム（通常はそのテーブルのPRIMARY KEY）を参照し、テーブル間の関連性を定義します。参照される側の値が存在しないデータを参照する側で登録できなくすることで、データの整合性を保ちます。

    sql
    CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
    );
    この例では、orders テーブルの user_id カラムが users テーブルの id カラムを参照しています。
    ON DELETE CASCADE は、参照元 (users テーブル) の行が削除された場合に、参照している側 (orders テーブル) の関連する行も自動的に削除する設定です。
    ON UPDATE CASCADE は、参照元 (users テーブル) のキー値が更新された場合に、参照している側 (orders テーブル) の関連するキー値も自動的に更新する設定です。
    CASCADE の代わりに SET NULL（参照元削除/更新時、参照側カラムをNULLにする）、RESTRICT（参照元削除/更新時、参照側のデータが存在する場合はエラーとする）、NO ACTION（RESTRICT と似ているが、制約チェックのタイミングが異なる）などが指定できます。

テーブルが既に存在する場合の対応

CREATE TABLE は、指定したテーブル名が既に存在する場合にエラーとなります。これを避けるには IF NOT EXISTS オプションを使用します。

sql
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE
);

テーブルの削除

不要になったテーブルを削除します。この操作はテーブル内の全てのデータと構造を完全に削除するため、非常に危険です。実行には十分注意し、必要に応じてバックアップを取ってください。

sql
DROP TABLE テーブル名;

例：users テーブルを削除する場合

sql
DROP TABLE users;

テーブルが存在しない場合のエラーを回避

DROP TABLE は、指定したテーブルが存在しない場合にエラーとなります。これを避けるには IF EXISTS オプションを使用します。

sql
DROP TABLE IF EXISTS users;

テーブル構造の変更

既存のテーブルにカラムを追加・削除したり、データ型を変更したりする場合は ALTER TABLE ステートメントを使用します。

  • カラムの追加:
    sql
    ALTER TABLE テーブル名 ADD COLUMN カラム名 データ型 制約;
    -- 例: users テーブルに address カラムを追加
    ALTER TABLE users ADD COLUMN address VARCHAR(255);
    -- 例: email カラムの後に phone カラムを追加
    ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
  • カラムの削除:
    sql
    ALTER TABLE テーブル名 DROP COLUMN カラム名;
    -- 例: users テーブルから address カラムを削除
    ALTER TABLE users DROP COLUMN address;
  • カラムのデータ型/制約変更:
    sql
    ALTER TABLE テーブル名 MODIFY COLUMN カラム名 新しいデータ型 新しい制約;
    -- 例: users テーブルの username カラムの型と制約を変更
    ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL UNIQUE;
  • カラム名とデータ型/制約の変更:
    sql
    ALTER TABLE テーブル名 CHANGE COLUMN 古いカラム名 新しいカラム名 新しいデータ型 新しい制約;
    -- 例: users テーブルの email カラム名を contact_email に変更し、NOT NULL制約を追加
    ALTER TABLE users CHANGE COLUMN email contact_email VARCHAR(100) NOT NULL;
  • テーブル名の変更:
    sql
    ALTER TABLE 古いテーブル名 RENAME TO 新しいテーブル名;
    -- 例: users テーブル名を members に変更
    ALTER TABLE users RENAME TO members;

ALTER TABLE は、テーブルのサイズによっては時間がかかり、テーブルがロックされる可能性があるため、運用中のシステムで実行する際は注意が必要です。

6. データの操作 (CRUD)

データベースの主要な操作である、データの作成 (Create)、読み取り (Read)、更新 (Update)、削除 (Delete) は、それぞれ INSERT, SELECT, UPDATE, DELETE というSQLステートメントを使って行います。

ここでは、前述の users テーブルを例に進めます。
sql
USE mydatabase;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE (データの挿入) – INSERT

新しい行をテーブルに挿入します。

基本構文：

sql
INSERT INTO テーブル名 (カラム名1, カラム名2, ...)
VALUES (値1, 値2, ...);

挿入する値は、指定したカラムのデータ型と一致するか、互換性がある必要があります。文字列や日付はシングルクォーテーション ' で囲みます。数値やNULL値はそのまま記述します。

例：1行挿入

sql
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');

id カラムは AUTO_INCREMENT なので、値を指定しなくても自動的に生成されます。created_at カラムは DEFAULT CURRENT_TIMESTAMP なので、値を指定しなくても自動的に現在日時が設定されます。

カラムリストを省略した場合、VALUES の値はテーブル定義のカラム順に全て指定する必要があります。

sql
-- id, username, email, created_at の順に全て指定する必要がある
-- id は AUTO_INCREMENT なので、通常は 0 または NULL を指定して自動生成させる
INSERT INTO users VALUES (0, 'bob', '[email protected]', NULL);

複数行を一度に挿入することもできます。

sql
INSERT INTO users (username, email) VALUES
('charlie', '[email protected]'),
('david', '[email protected]'),
('eve', '[email protected]');

READ (データの読み取り) – SELECT

テーブルからデータを検索し、取得します。SELECT ステートメントは最も頻繁に使用されるSQLコマンドです。

基本構文：

sql
SELECT カラム名1, カラム名2, ...
FROM テーブル名
WHERE 条件
GROUP BY カラム名
HAVING 条件
ORDER BY カラム名 ASC|DESC
LIMIT オフセット, 件数;

各句は省略可能です。FROM句は必須です。

例：テーブルの全てのカラムの全ての行を取得

sql
SELECT * FROM users;

実行結果例：

+----+----------+-----------------------+---------------------+
| id | username | email | created_at |
+----+----------+-----------------------+---------------------+
| 1 | alice | [email protected] | 2023-10-27 10:00:00 |
| 2 | bob | [email protected] | 2023-10-27 10:01:00 |
| 3 | charlie | [email protected] | 2023-10-27 10:02:00 |
| 4 | david | [email protected] | 2023-10-27 10:03:00 |
| 5 | eve | [email protected] | 2023-10-27 10:04:00 |
+----+----------+-----------------------+---------------------+
5 rows in set (0.00 sec)

特定のカラムのみ取得：

sql
SELECT username, email FROM users;

AS を使ってカラムに別名（エイリアス）を付ける：

sql
SELECT username AS user_name, email AS user_email FROM users;

重複しない値を取得：DISTINCT

sql
-- 例: users テーブルに country カラムがあり、値が重複している場合
-- SELECT DISTINCT country FROM users;

WHERE句によるフィルタリング

特定の条件を満たす行のみを取得します。

  • 比較演算子: =, != (<>), >, <, >=, <=
    sql
    SELECT * FROM users WHERE id = 3;
    SELECT * FROM users WHERE created_at > '2023-10-27 10:02:30';
  • 論理演算子: AND, OR, NOT
    sql
    SELECT * FROM users WHERE id > 2 AND username != 'david';
    SELECT * FROM users WHERE username = 'alice' OR username = 'bob';
    SELECT * FROM users WHERE NOT username = 'alice'; -- alice以外
  • LIKE演算子: 文字列の部分一致検索。% は任意の0文字以上の文字列、_ は任意の一文字を表します。
    sql
    SELECT * FROM users WHERE username LIKE 'a%'; -- 'a'で始まる
    SELECT * FROM users WHERE email LIKE '%example.com'; -- '@example.com'で終わる
    SELECT * FROM users WHERE username LIKE '%l%'; -- 'l'を含む
    SELECT * FROM users WHERE username LIKE '____'; -- 4文字のユーザー名
  • IN演算子: リスト内のいずれかの値と一致するかどうか。
    sql
    SELECT * FROM users WHERE username IN ('alice', 'charlie', 'eve');
  • BETWEEN演算子: 値が範囲内にあるかどうか（両端の値を含む）。
    sql
    SELECT * FROM users WHERE id BETWEEN 2 AND 4; -- idが2, 3, 4の行
  • IS NULL / IS NOT NULL: NULL値かどうかを判定します。= NULL や != NULL は使えません。
    sql
    SELECT * FROM users WHERE email IS NULL;
    SELECT * FROM users WHERE email IS NOT NULL;
ORDER BY句によるソート

結果を指定したカラムの値で並べ替えます。デフォルトは昇順 (ASC)。降順は DESC を指定します。

sql
SELECT * FROM users ORDER BY username ASC; -- usernameの昇順
SELECT * FROM users ORDER BY created_at DESC; -- created_atの降順
SELECT * FROM users ORDER BY username ASC, id DESC; -- 複数のカラムでソート

LIMIT句による結果件数の制限

取得する行数を制限します。大量のデータがある場合に、全てを取得せずに済むためパフォーマンス向上に役立ちます。

sql
SELECT * FROM users LIMIT 3; -- 先頭から3件
SELECT * FROM users LIMIT 2, 3; -- 2件目（0からカウント）から3件取得 (オフセット, 件数)

集計関数

複数の行の値を集計して1つの結果を求めます。

  • COUNT(カラム名): 行数をカウントします。COUNT(*) はNULLを含む全ての行数をカウントします。COUNT(カラム名) はNULL以外の行数をカウントします。
    sql
    SELECT COUNT(*) FROM users; -- 全ユーザー数
    SELECT COUNT(email) FROM users; -- emailがNULLでないユーザー数
  • SUM(カラム名): 数値型のカラムの合計値を求めます。
  • AVG(カラム名): 数値型のカラムの平均値を求めます。
  • MIN(カラム名): 最小値を求めます。
  • MAX(カラム名): 最大値を求めます。
GROUP BY句

指定したカラムの値ごとにグループ化し、グループごとに集計関数を適用します。

例：都道府県ごとのユーザー数をカウントする場合 (テーブル構造に prefecture カラムがあると仮定)

sql
-- users テーブルに prefecture VARCHAR(10) があるとする
-- INSERT INTO users (username, email, prefecture) VALUES ('test', '[email protected]', '東京都'), ('test2', '[email protected]', '東京都'), ('test3', '[email protected]', '大阪府');
SELECT prefecture, COUNT(*) FROM users GROUP BY prefecture;

HAVING句

GROUP BY によってグループ化された結果に対してフィルタリングを行います。WHERE 句はグループ化前の個々の行に適用されるのに対し、HAVING 句はグループ化後の集計結果に適用されます。

例：ユーザー数が2人以上の都道府県のみを表示

sql
SELECT prefecture, COUNT(*) FROM users GROUP BY prefecture HAVING COUNT(*) >= 2;

JOINによる複数テーブルの結合

関連のある複数のテーブルからデータを結合して取得します。

例えば、users テーブルと orders テーブル（order_id, user_id, product_name, amount というカラムがあると仮定）があり、orders.user_id が users.id を参照しているとします。

  • INNER JOIN: 両方のテーブルに一致する行が存在する場合にのみ、その行を結合して返します。
    sql
    -- 注文したことのあるユーザーとその注文情報
    SELECT users.username, orders.product_name, orders.amount
    FROM users INNER JOIN orders
    ON users.id = orders.user_id;
    または USING を使って簡潔に（結合条件のカラム名が両テーブルで同じ場合）
    sql
    -- users テーブルの id と orders テーブルの user_id が同じ場合
    SELECT u.username, o.product_name, o.amount
    FROM users AS u INNER JOIN orders AS o
    ON u.id = o.user_id; -- エイリアスを使うと記述が楽になる
  • LEFT JOIN (LEFT OUTER JOIN): 左側のテーブル (FROM 句で指定したテーブル) の全ての行を返し、右側のテーブル (JOIN 句で指定したテーブル) の一致する行を結合します。左側の行に一致する右側の行がない場合、右側のカラムはNULLになります。
    sql
    -- 全てのユーザーと、もしあればその注文情報（注文がないユーザーも含む）
    SELECT u.username, o.product_name, o.amount
    FROM users AS u LEFT JOIN orders AS o
    ON u.id = o.user_id;
  • RIGHT JOIN (RIGHT OUTER JOIN): 左結合の逆で、右側のテーブルの全ての行を返し、左側のテーブルの一致する行を結合します。
    sql
    -- 全ての注文情報と、それに関連するユーザー情報（ユーザーが不明な注文も含む）
    SELECT u.username, o.product_name, o.amount
    FROM users AS u RIGHT JOIN orders AS o
    ON u.id = o.user_id;
    ※通常、RIGHT JOINはLEFT JOINを使って記述できます（テーブルの記述順を逆にすれば良い）。LEFT JOINの方がよく使われます。
サブクエリ

別の SELECT ステートメントの結果を、親となる SELECT、INSERT、UPDATE、DELETE ステートメントの中で使用します。

  • WHERE 句でのサブクエリ（スカラーサブクエリ、行サブクエリ、列サブクエリ）
    “`sql
    — 最新のユーザーのcreated_atよりも後に作成されたユーザー
    SELECT * FROM users
    WHERE created_at > (SELECT MAX(created_at) FROM users); — スカラーサブクエリ (単一の値)

    — 特定の条件を満たす注文をしたユーザー
    SELECT username FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000); — 列サブクエリ (単一の列のリスト)
    - `FROM` 句でのサブクエリ（導出テーブル）sql
    — 各都道府県のユーザー数をカウントし、その結果からユーザー数が5人以上の都道府県を抽出
    SELECT prefecture, user_count
    FROM (SELECT prefecture, COUNT(*) AS user_count FROM users GROUP BY prefecture) AS pref_counts
    WHERE user_count >= 5;
    ``pref_counts` は一時的なテーブル（導出テーブル）であり、その結果に対してさらに操作を行います。

UPDATE (データの更新) – UPDATE

既存のテーブルの行の値を変更します。

基本構文：

sql
UPDATE テーブル名
SET カラム名1 = 新しい値1, カラム名2 = 新しい値2, ...
WHERE 条件;

WHERE 句は更新対象の行を指定します。WHERE 句を省略すると、テーブルの全ての行が更新されます！ これは非常に危険な操作なので、細心の注意を払ってください。

例：ユーザーIDが1のユーザーのメールアドレスを更新

sql
UPDATE users
SET email = '[email protected]'
WHERE id = 1;

複数のカラムを更新：

sql
UPDATE users
SET email = '[email protected]', username = 'bobby'
WHERE id = 2;

WHERE 句を省略した例（全行更新）：

sql
-- ！！注意！！ users テーブルの全ての行の email を NULL にします
UPDATE users
SET email = NULL;

DELETE (データの削除) – DELETE

テーブルから特定の行を削除します。

基本構文：

sql
DELETE FROM テーブル名
WHERE 条件;

WHERE 句は削除対象の行を指定します。WHERE 句を省略すると、テーブルの全ての行が削除されます！ これも非常に危険な操作なので、細心の注意を払ってください。

例：ユーザーIDが3のユーザーを削除

sql
DELETE FROM users
WHERE id = 3;

条件に一致する複数の行を削除：

sql
-- created_at が特定の日時より古いユーザーを削除
DELETE FROM users
WHERE created_at < '2023-10-27 10:02:00';

WHERE 句を省略した例（全行削除）：

sql
-- ！！注意！！ users テーブルの全ての行を削除します
DELETE FROM users;

TRUNCATE TABLEとの違い

TRUNCATE TABLE テーブル名; もテーブルの全ての行を削除するコマンドですが、DELETE FROM テーブル名; とはいくつかの点で異なります。

  • TRUNCATE TABLE はテーブル構造をそのままに、データを全て削除します。これは DELETE FROM (WHERE句なし) と同じように見えますが、内部的にはテーブルを再作成するような処理を行うため、より高速です。
  • TRUNCATE TABLE はトランザクション内で実行した場合でもロールバックできません（またはトランザクションを暗黙的にコミットします）。DELETE FROM はトランザクション内で実行すればロールバック可能です（InnoDBストレージエンジンの場合）。
  • TRUNCATE TABLE を実行すると、AUTO_INCREMENT の値はリセットされます（通常1から再開）。DELETE FROM は通常リセットされません（ただし、テーブルが空になった後にサーバーを再起動するなど特定の条件下ではリセットされることもあります）。
  • TRUNCATE TABLE はDDL (Data Definition Language) コマンドとして扱われ、DELETE FROM はDML (Data Manipulation Language) コマンドとして扱われます。権限管理において違いが出ることがあります（TRUNCATEにはDROP権限が必要）。

テーブルの全てのデータを削除したい場合は、通常 TRUNCATE TABLE が高速で推奨されますが、ロールバックの必要性や AUTO_INCREMENT のリセットが必要かどうかによって使い分けます。

7. トランザクション

トランザクションは、複数のSQLステートメントをひとまとまりの不可分な処理として扱うための仕組みです。これにより、データベースの整合性を保つことができます。例えば、銀行の振り込み処理では、「送金元口座から金額を減らす」操作と「送金先口座に金額を増やす」操作は、両方成功するか、両方失敗するか、どちらか一方だけが実行されてしまうことのないようにする必要があります。このような場合にトランザクションを使用します。

トランザクションは、ACID特性と呼ばれる以下の性質を満たすように設計されています。

  • Atomicity (原子性): トランザクション内の全ての操作は、全て成功するか、全て失敗して元に戻されるか、どちらか一方になります。途中で止まることはありません。
  • Consistency (一貫性): トランザクションの開始時と終了時に、データベースは常に一貫性のある状態を保ちます。定義された制約（PRIMARY KEY、FOREIGN KEYなど）が破られることはありません。
  • Isolation (独立性): 複数のトランザクションが同時に実行されても、それぞれが独立して実行されているかのように見えます。他のトランザクションの影響を受けません。
  • Durability (永続性): コミットされたトランザクションの結果は、システム障害が発生しても失われることなく、永続的にデータベースに保存されます。

MySQLでトランザクションを使用するには、テーブルのストレージエンジンがトランザクションをサポートしている必要があります。InnoDBはトランザクションをサポートする代表的なストレージエンジンです（MyISAMはサポートしません）。MySQL 5.5以降ではInnoDBがデフォルトのストレージエンジンです。

トランザクションの開始、コミット、ロールバック

デフォルトでは、MySQLは「オートコミット」モードで動作します。これは、各SQLステートメントが実行されるたびに、自動的にコミット（変更の確定）が行われる設定です。トランザクションを手動で制御するには、オートコミットをオフにするか、明示的にトランザクションを開始します。

明示的にトランザクションを開始するには、以下のコマンドを使用します。

sql
START TRANSACTION;

または

sql
BEGIN; -- START TRANSACTION と同じ意味

トランザクションが開始されると、以降の INSERT, UPDATE, DELETE などのデータ変更操作は、まだ確定されていません。

トランザクション内の全ての操作が成功し、変更を確定したい場合は、以下のコマンドを実行します。

sql
COMMIT;

COMMIT を実行すると、それまでの変更がデータベースに永続的に保存され、トランザクションは終了します。

トランザクション内の操作の途中でエラーが発生したり、何らかの理由で操作を取り消したい場合は、以下のコマンドを実行します。

sql
ROLLBACK;

ROLLBACK を実行すると、トランザクション開始時点の状態に戻され、それまでの変更は全て破棄されます。トランザクションは終了します。

例：トランザクションを使ったデータ操作

“`sql
— ユーザーテーブルとポイントテーブルがあるとする
— CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
— CREATE TABLE points (user_id INT PRIMARY KEY, points INT, FOREIGN KEY (user_id) REFERENCES users(id));
— INSERT INTO users VALUES (1, ‘Alice’), (2, ‘Bob’);
— INSERT INTO points VALUES (1, 100), (2, 50);

— AliceからBobへポイントを50移動するトランザクション
START TRANSACTION;

— Aliceのポイントを減らす
UPDATE points SET points = points – 50 WHERE user_id = 1;

— Bobのポイントを増やす
UPDATE points SET points = points + 50 WHERE user_id = 2;

— どちらの操作も成功した場合のみ、コミット
COMMIT;

— もし途中で問題が発生した場合（例: Aliceのポイントが足りないなど）は、代わりに ROLLBACK を実行
— ROLLBACK;
“`

この例では、UPDATE ステートメントが2つ実行されます。もし最初の UPDATE は成功したが2番目の UPDATE が失敗した場合、COMMIT は行われず、通常は ROLLBACK が自動または手動で実行されます。これにより、Aliceのポイントだけが減ってBobのポイントが増えない、といったデータの不整合を防ぐことができます。

オートコミットの設定

現在のセッションのオートコミット設定は、以下のコマンドで確認できます。

sql
SELECT @@autocommit;

1 が表示されればオートコミット有効（デフォルト）、0 が表示されればオートコミット無効です。

オートコミットを一時的に無効にするには、以下のコマンドを実行します。

sql
SET autocommit = 0;

オートコミットが無効な場合、全てのSQLデータ変更ステートメントはトランザクション内で行われ、明示的に COMMIT または ROLLBACK を実行するまで変更は確定されません。トランザクションを終了すると、次のデータ変更ステートメントから新しいトランザクションが開始されます。

オートコミットを再度有効にするには：

sql
SET autocommit = 1;

通常はデフォルトのオートコミット有効のままにしておき、トランザクションが必要な処理の直前で START TRANSACTION を実行し、処理の終了時に COMMIT または ROLLBACK を実行するという方法がよく取られます。

8. ユーザーと権限管理

データベースシステムでは、誰がどのような操作（データの読み取り、書き込み、テーブルの作成など）を行えるかを厳密に管理することがセキュリティ上非常に重要です。MySQLでは、ユーザーを作成し、特定のデータベースやテーブルに対して個別に権限を付与することでこれを実現します。

ユーザーの作成

新しいユーザーを作成するには CREATE USER ステートメントを使用します。ユーザーは「ユーザー名」と「接続元ホスト」の組み合わせで識別されます。

sql
CREATE USER 'ユーザー名'@'接続元ホスト' IDENTIFIED BY 'パスワード';

  • 'ユーザー名': 作成するユーザー名。シングルクォーテーションで囲みます。
  • '接続元ホスト': そのユーザーがどのホストから接続できるかを指定します。
    • 'localhost': そのMySQLサーバーが動いているマシンからのみ接続可能。
    • '%': どのホストからでも接続可能（セキュリティリスクが高いため注意）。
    • '192.168.1.%': 特定のサブネットからの接続を許可。
    • 'example.com': 特定のホスト名からの接続を許可（DNS解決に依存）。
  • 'パスワード': そのユーザーのパスワード。強力なパスワードを設定し、シングルクォーテーションで囲みます。

例：ローカルホストからのみ接続可能な app_user というユーザーを作成

sql
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password_for_app';

例：任意のホストから接続可能な remote_user というユーザーを作成（推奨されない場合が多い）

sql
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'another_secure_password';

権限の付与

ユーザーを作成しただけでは、そのユーザーは何の操作もできません。GRANT ステートメントを使って、具体的な権限を付与する必要があります。

sql
GRANT 権限タイプ ON 対象オブジェクト TO 'ユーザー名'@'接続元ホスト';

  • 権限タイプ: 付与する権限の種類。カンマ区切りで複数指定できます。
    • SELECT: データの読み取り (SELECT)
    • INSERT: データの挿入 (INSERT)
    • UPDATE: データの更新 (UPDATE)
    • DELETE: データの削除 (DELETE)
    • CREATE: テーブルやデータベースの作成 (CREATE TABLE, CREATE DATABASE)
    • DROP: テーブルやデータベースの削除 (DROP TABLE, DROP DATABASE)
    • ALTER: テーブル構造の変更 (ALTER TABLE)
    • INDEX: インデックスの作成・削除
    • ALL PRIVILEGES: 全ての権限（GRANT OPTION を除く）
    • GRANT OPTION: 他のユーザーに権限を付与する権限
  • 対象オブジェクト: 権限を付与する対象。
    • *.*: 全てのデータベースの全てのテーブル
    • データベース名.*: 特定のデータベースの全てのテーブル
    • データベース名.テーブル名: 特定のデータベースの特定のテーブル

例：app_user に mydatabase データベースの全てのテーブルに対する読み取り、書き込み、更新、削除権限を付与

sql
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'app_user'@'localhost';

例：remote_user に mydatabase データベースの特定のテーブル orders に対する読み取り権限のみ付与

sql
GRANT SELECT ON mydatabase.orders TO 'remote_user'@'%';

例：admin_user に全てのデータベースに対する全ての権限と、他のユーザーに権限を付与する権限を付与

sql
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'admin_password';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;

権限の確認

特定のユーザーにどのような権限が付与されているかを確認するには SHOW GRANTS ステートメントを使用します。

sql
SHOW GRANTS FOR 'ユーザー名'@'接続元ホスト';

例：app_user の権限を確認

sql
SHOW GRANTS FOR 'app_user'@'localhost';

実行結果例：

+------------------------------------------------------------------------------+
| Grants for app_user@localhost |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app_user'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mydatabase`.* TO 'app_user'@'localhost' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

GRANT USAGE ON *.* は、接続権限があることを示す最も基本的な権限で、データ操作などの権限は何もない状態を表します。

権限の取り消し

付与した権限を取り消すには REVOKE ステートメントを使用します。構文は GRANT と似ています。

sql
REVOKE 権限タイプ ON 対象オブジェクト FROM 'ユーザー名'@'接続元ホスト';

例：app_user から mydatabase データベースの全てのテーブルに対する削除権限を取り消し

sql
REVOKE DELETE ON mydatabase.* FROM 'app_user'@'localhost';

例：admin_user から GRANT OPTION 権限を取り消し

sql
REVOKE GRANT OPTION ON *.* FROM 'admin_user'@'localhost';

ユーザーの削除

不要になったユーザーを削除するには DROP USER ステートメントを使用します。

sql
DROP USER 'ユーザー名'@'接続元ホスト';

例：remote_user を削除

sql
DROP USER 'remote_user'@'%';

ユーザー管理と権限設定は、データベースのセキュリティを維持するために非常に重要な作業です。最小権限の原則に基づき、各ユーザーには必要最低限の権限のみを付与するように心がけましょう。

権限の即時反映

GRANT や REVOKE ステートメントによる権限変更は、通常すぐに反映されます。しかし、古いMySQLバージョンや特定の条件下では、変更が反映されないことがあります。その場合、以下のコマンドで権限キャッシュをリロードすることで即時反映させることができます。

sql
FLUSH PRIVILEGES;

最近のMySQLバージョンでは、多くの場合 FLUSH PRIVILEGES は不要ですが、念のため実行しておくと安全な場合があります。

9. インデックス

インデックスは、データベーステーブルからのデータ検索（SELECT ステートメント）のパフォーマンスを大幅に向上させるために使用される特別なデータ構造です。書籍の索引（インデックス）のようなもので、特定の値を素早く見つけるためのポインターとして機能します。

テーブルにインデックスがない場合、データベースは検索条件に一致する行を見つけるためにテーブルの全ての行を最初から最後までスキャンする必要があります（フルテーブルスキャン）。これはテーブルのサイズが大きくなるにつれて非常に非効率になり、クエリの応答時間が長くなります。

インデックスを作成すると、データベースはインデックスを使って目的のデータがどこにあるかを効率的に特定し、関連する行を直接取得できるようになります。

インデックスの種類

  • PRIMARY KEY: テーブルの主キーに自動的に作成されるインデックスです。一意性とNOT NULLが保証されます。
  • UNIQUE INDEX: カラムの値の一意性を保証するインデックスです。PRIMARY KEYと異なりNULL値を許可します（NULLは複数格納可能）。
  • INDEX (または KEY): 最も一般的なインデックスです。一意性は保証されません。データの検索やソートの効率化に使用されます。
  • FULLTEXT INDEX: テキストデータ内のキーワード検索を高速化するためのインデックスです（MyISAMまたはInnoDB）。
  • SPATIAL INDEX: 空間データ型（地理情報など）を扱うためのインデックスです（MyISAMまたはInnoDB）。

インデックスの作成

CREATE INDEX ステートメントを使用して、既存のテーブルにインデックスを追加できます。

sql
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX インデックス名
ON テーブル名 (カラム名1 [(長さ)], カラム名2 [(長さ)], ...);

  • インデックス名: 作成するインデックスの名前。テーブル内で一意である必要があります。省略可能ですが、指定した方が管理しやすいです。
  • テーブル名: インデックスを作成するテーブルの名前。
  • (カラム名1, ...): インデックスを作成するカラムまたはカラムの組み合わせ。複数のカラムを指定すると「複合インデックス」になります。VARCHAR や TEXT などの文字列型のカラムの場合、インデックスのサイズを抑えるために先頭の数文字のみにインデックスを付けること（プレフィックスインデックス）ができます（例: (column_name(10))）。

例：users テーブルの username カラムにインデックスを作成

sql
CREATE INDEX idx_username ON users (username);

例：orders テーブルの order_date と user_id の複合インデックスを作成

sql
-- 仮に orders テーブルに order_date DATE, user_id INT があるとして
CREATE INDEX idx_order_date_user_id ON orders (order_date, user_id);
複合インデックスは、指定したカラムの順序でソートされます。例えば (A, B) のインデックスは WHERE A = ... や WHERE A = ... AND B = ... のクエリで効果的ですが、WHERE B = ... だけのクエリでは効果が限定的になります。

テーブル作成時にインデックスを指定することも可能です。

sql
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2),
INDEX idx_price (price) -- price カラムにインデックスを作成
);

PRIMARY KEY や UNIQUE 制約を定義すると、対応するインデックスが自動的に作成されます。

テーブルのインデックスの確認

テーブルにどのようなインデックスが存在するかは SHOW INDEX ステートメントで確認できます。

sql
SHOW INDEX FROM テーブル名;

例：users テーブルのインデックスを確認

sql
SHOW INDEX FROM users;

実行結果例：

+-------+----------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+----------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| users | 0 | username | 1 | username | A | 5 | NULL | NULL | | BTREE | | |
| users | 1 | idx_username | 1 | username | A | 5 | NULL | NULL | | BTREE | | |
+-------+----------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

  • Non_unique: 0 ならUNIQUEインデックス、1 なら非UNIQUEインデックス。
  • Key_name: インデックス名。PRIMARY KEYは ‘PRIMARY’。
  • Column_name: インデックスに含まれるカラム名。
  • Cardinality: インデックスに含まれる一意な値の数の推定値。テーブルの行数に近いほど、インデックスが選択的（検索効率が良い）であることを示唆します。
  • Index_type: インデックスのタイプ（BTREEが一般的）。

インデックスの削除

不要になったインデックスは DROP INDEX ステートメントで削除できます。

sql
DROP INDEX インデックス名 ON テーブル名;

例：users テーブルの idx_username インデックスを削除

sql
DROP INDEX idx_username ON users;

※ PRIMARY KEY インデックスを削除する場合は、構文が少し異なります。
sql
ALTER TABLE テーブル名 DROP PRIMARY KEY;

インデックスの利用と注意点

  • 効果的なインデックス: WHERE 句や JOIN 条件、ORDER BY 句で使用されるカラムにインデックスを作成すると効果的です。
  • インデックスのコスト: インデックスは検索を速くしますが、データ挿入 (INSERT)、更新 (UPDATE)、削除 (DELETE) の際にはインデックスも更新する必要があるため、これらの操作が遅くなる可能性があります。また、インデックス自身もディスク容量を消費します。
  • 過剰なインデックスは避ける: 必要以上にインデックスを作成すると、更新性能の低下やディスク容量の圧迫を招きます。本当にパフォーマンス向上に寄与するカラムに絞って作成することが重要です。
  • EXPLAINステートメント: クエリがインデックスを効果的に使用しているかを確認するには EXPLAIN SELECT ...; というステートメントを使用します。
    sql
    EXPLAIN SELECT * FROM users WHERE username = 'alice';
    結果の key カラムに利用されたインデックス名が表示されます。type が ALL となっている場合はフルテーブルスキャンが行われています。

10. バックアップとリストア

データベースのバックアップは、データ損失のリスクに備えるために非常に重要です。MySQL CLIには、データベースをファイルにエクスポート（バックアップ）したり、ファイルからインポート（リストア）したりするための便利なツール mysqldump が付属しています。

mysqldump はシェルコマンドであり、MySQL CLI (mysql) プロンプト内ではなく、システムのターミナルまたはコマンドプロンプトから実行します。

mysqldumpによるバックアップ

mysqldump コマンドは、指定したデータベースの定義（CREATE TABLEなどの構造）とデータ（INSERTステートメント）を含むSQLファイルを生成します。

基本的な構文：

bash
mysqldump -u ユーザー名 -p データベース名 > バックアップファイル名.sql

  • -u ユーザー名: MySQLユーザー名。
  • -p: パスワード入力を促します。
  • データベース名: バックアップしたいデータベース名。
  • > バックアップファイル名.sql: 標準出力を指定したファイルにリダイレクトします。

例：mydatabase というデータベースを mydatabase_backup.sql というファイルにバックアップ

bash
mysqldump -u root -p mydatabase > mydatabase_backup.sql
コマンド実行後、パスワード入力を求められます。

よく使うmysqldumpオプション
  • --all-databases: 全てのデータベースをバックアップします（mysql システムデータベースなども含まれます）。
    bash
    mysqldump -u root -p --all-databases > all_databases_backup.sql
  • --single-transaction: InnoDBテーブルの場合、バックアップ中に一貫性のあるスナップショットを取得します。これにより、バックアップ中のロック時間を最小限に抑えられます。InnoDBを使用している場合は強く推奨されるオプションです。
    bash
    mysqldump -u root -p --single-transaction mydatabase > mydatabase_backup_innodb.sql
  • --routines: ストアドプロシージャとストアドファンクションを含めます。
  • --events: イベントスケジューラで定義されたイベントを含めます。
  • --triggers: トリガーを含めます（通常デフォルトで含まれますが明示的に指定することも）。
  • --no-data: テーブル定義（スキーマ）のみをバックアップし、データは含めません。
    bash
    mysqldump -u root -p --no-data mydatabase > mydatabase_schema.sql
  • --databases db1 db2 ...: 複数の特定のデータベースをバックアップします。
    bash
    mysqldump -u root -p --databases mydatabase another_db > multiple_databases_backup.sql
  • -h ホスト名, -P ポート番号: リモートのMySQLサーバーからバックアップを取得する場合に使用します。

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

mysqldump で作成したSQLファイルを使って、データベースを復元します。これもシェルコマンドで行います。

基本的な構文：

bash
mysql -u ユーザー名 -p データベース名 < バックアップファイル名.sql

  • -u ユーザー名: MySQLユーザー名。
  • -p: パスワード入力を促します。
  • データベース名: リストア先のデータベース名。リストア対象のデータベースは事前に作成しておく必要があります。
  • < バックアップファイル名.sql: 指定したファイルを標準入力としてコマンドに渡します。

例：mydatabase_backup.sql ファイルを mydatabase というデータベースにリストア

bash
mysql -u root -p mydatabase < mydatabase_backup.sql
コマンド実行後、パスワード入力を求められます。

もしリストア先のデータベースが存在しない場合は、事前に CREATE DATABASE mydatabase; のように作成しておく必要があります。

--all-databases で取得したバックアップファイルをリストアする場合：

bash
mysql -u root -p < all_databases_backup.sql
この場合、リストア先のデータベース名を指定しません。バックアップファイル内の CREATE DATABASE および USE ステートメントによって、適切なデータベースが作成または選択され、データが投入されます。

MySQL CLI内からのSOURCEコマンドによるリストア

mysqldump の出力をMySQL CLIに接続した後に SOURCE コマンドで実行することも可能です。

  1. MySQL CLIに接続し、リストアしたいデータベースを選択します。
    bash
    mysql -u root -p
    USE mydatabase;
  2. SOURCE コマンドでSQLファイルを指定します。
    sql
    SOURCE /path/to/your/backup/file/mydatabase_backup.sql;
    ファイルのパスはサーバーからの相対パスではなく、CLIクライアントが実行されているマシンのファイルシステム上のパスを指定します。

SOURCE コマンドはCLIに接続してから操作する場合に便利ですが、大量のデータをリストアする場合はシェルからリダイレクト (<) を使う方が一般的に効率的です。

11. CLIの便利な機能とショートカット

MySQL CLIには、操作を効率化するための便利な機能やショートカットがいくつかあります。

  • コマンド履歴: 上下矢印キー (Up Arrow, Down Arrow) で、過去に入力したコマンドを呼び出すことができます。これにより、同じコマンドを再度入力する手間が省けます。
  • コマンド補完: Tab キーを使うと、コマンド名、データベース名、テーブル名、カラム名などを補完できます。入力中に Tab キーを押すと、入力途中の文字列で始まる候補が表示されるか、一意であれば補完されます。例えば、SE と入力して Tab を押すと SELECT に補完される、SHOW DA で Tab を押すと SHOW DATABASES に補完される、USE mydata で Tab を押すと USE mydatabase に補完される、といった具合です。これは非常に役立ちます。
  • 複数行入力: 長いSQLステートメントを入力する際、Enterキーを押してもセミコロン ; がなければ、プロンプトが -> に変わり、入力の続きを受け付けます。これにより、可読性を保ったまま複数行にわたるクエリを入力できます。セミコロン ; または \g を入力してEnterを押すと、コマンドが実行されます。
  • シェルコマンドの実行: MySQL CLIセッションを終了せずに、一時的にシェルコマンドを実行したい場合は、コマンドの前に \! を付けます。
    sql
    mysql> \! ls -l
    これにより、ls -l コマンドが実行され、その結果が表示された後、再び mysql> プロンプトに戻ります。
  • 外部エディタの利用: 複雑なSQLクエリを作成する場合、CLI上で直接入力するのは大変です。\e または EDIT コマンドを使用すると、デフォルト設定された外部エディタ（環境変数 EDITOR または VISUAL で指定）で現在のバッファ（または入力途中のコマンド）を編集できます。
    sql
    mysql> SELECT * FROM users WHERE \e
    エディタが起動し、WHERE 句以降を入力・編集できます。エディタを保存して閉じると、その内容がCLIに戻り、実行されます。
  • 縦方向表示: SELECT クエリの結果が横に非常に長い場合、各行が見づらくなることがあります。クエリの最後に \G を付けると、結果が縦方向（カラム名が左、値が右）に表示されます。
    sql
    mysql> SELECT * FROM users WHERE id = 1\G
    実行結果例：
    *************************** 1. row ***************************
    id: 1
    username: alice
    email: [email protected]
    created_at: 2023-10-27 10:00:00
    1 row in set (0.00 sec)
  • Pagerの設定: pager コマンドを使うと、SELECT 結果などの出力を外部コマンド（例えば less）にパイプで渡すことができます。大量の結果が表示される際にページングしながら見たい場合に便利です。
    sql
    mysql> pager less -S -- -Sオプションで長い行を折り返さず横スクロール可能にする
    PAGER set to 'less -S'
    mysql> SELECT * FROM some_large_table; -- 結果がlessで表示される
    Pagerを解除するには nopager コマンドを使用します。
    sql
    mysql> nopager
    Pager disabled.
  • ログの記録: tee コマンドを使うと、MySQL CLIでの入力と結果をファイルに記録できます。デバッグや作業ログを残したい場合に便利です。
    sql
    mysql> tee /path/to/your/log/file.log
    Outfile disabled.
    ログ記録を停止するには notee コマンドを使用します。
    sql
    mysql> notee
    Outfile disabled.
    記録されたファイルは、CLIから終了する際に閉じられます。

これらの便利な機能を活用することで、MySQL CLIでの作業効率を大きく向上させることができます。

12. CLIのメリット・デメリットのまとめ

MySQL CLIを学ぶことの重要性を改めて整理し、そのメリットとデメリットをまとめます。

メリット

  1. 高い効率と速度: GUIツールの起動や操作のオーバーヘッドがなく、直接コマンドを実行するため、特に慣れたユーザーにとっては非常に高速に作業を進められます。定型的な操作や大量の操作に適しています。
  2. 自動化とスクリプト化: シェルスクリプトなどと組み合わせることで、定期的・連続的なデータベース操作（バックアップ、データ投入、メンテナンススクリプトの実行など）を完全に自動化できます。これはGUIツールでは難しい、あるいは不可能な領域です。
  3. リモート操作: SSHなどを使ってサーバーに接続し、そのままCLIで操作できるため、GUIツールを別に用意したり、リモート接続設定を行ったりする手間が省けます。管理作業において必須のスキルです。
  4. リソース消費の少なさ: GUIツールに比べてシステムリソース（CPU、メモリ）の消費が非常に少ないため、スペックの低いサーバーや多数のツールを開いている環境でも軽快に動作します。
  5. 環境依存性の低さ: コマンドラインインターフェイスは基本的にテキストベースであり、OSやバージョンによる操作性の違いがGUIツールほど大きくありません。様々な環境で一貫した操作が可能です。
  6. データベースの深い理解: SQLクエリを手で入力し、実行結果をテキストで確認することで、SQLの仕組みやデータベースの内部動作（インデックスの効果、クエリ実行計画など）に対する理解が深まります。
  7. トラブルシューティング能力向上: エラーメッセージが直接表示されるため、問題の特定と解決に役立ちます。低レベルな操作が可能であるため、GUIツールでは手の届かない領域のトラブルシューティングができます。

デメリット

  1. 学習コスト: GUIツールに比べて、コマンドやSQLステートメントの構文を覚える必要があります。初心者にとっては敷居が高いと感じられるかもしれません。
  2. 直感的な操作性の欠如: データベースやテーブルの全体構造を視覚的に把握したり、データをグリッド形式で閲覧・編集したりするのは、GUIツールの方が優れています。テーブル間のリレーションシップ（ER図など）もGUIツールの方が分かりやすいです。
  3. 入力ミス: コマンドを手で入力するため、タイプミスが発生しやすいです。特に複雑なクエリや長文のデータ入力では注意が必要です。
  4. 複雑なクエリの作成・編集: 長くて複雑なSQLクエリを作成したり、既存のクエリを編集したりするのは、GUIツールのSQLエディタの方が便利な場合があります（構文ハイライト、補完、整形など）。ただし、CLIの外部エディタ機能 (\e) を使えばこのデメリットは軽減できます。

これらのメリット・デメリットを理解した上で、作業内容や目的に応じてCLIとGUIツールを使い分けることが重要です。特に、開発や運用の現場では、CLIスキルは不可欠と言えるでしょう。

13. まとめ

この記事では、MySQL CLIの基本的な使い方から、データベースやテーブルの操作、データのCRUD操作、トランザクション、ユーザー管理、インデックス、そしてバックアップ・リストアまで、MySQL CLIを使ったデータベース操作の幅広い知識を詳細に解説しました。

MySQL CLIは、単にGUIの代替ツールというだけでなく、データベース管理者や開発者にとって強力な武器となるツールです。高速な操作、自動化の可能性、リモート環境での作業効率など、多くのメリットを享受できます。

GUIツールはデータベースの全体像を把握したり、一時的なデータ確認や簡単な編集を行ったりするのに非常に便利ですが、より複雑な管理タスクや定型作業の自動化、パフォーマンスチューニング、問題解決においては、CLIの知識が圧倒的に有利になります。

この記事で紹介した内容は、MySQL CLIを使ったデータベース操作の基礎のほんの一部に過ぎません。MySQLにはさらに多くのデータ型、関数、ストレージエンジン、レプリケーション、クラスタリングなど、学ぶべき様々な機能があります。しかし、CLIを使いこなすことで、これらのより高度なトピックへの理解も深まり、習得が容易になるはずです。

まずはこの記事で学んだ基本的なコマンドを実際に手を動かして実行してみてください。日常の開発やデータベース管理業務でCLIを積極的に利用する機会を増やし、その利便性を実感することが、習得への一番の近道です。

コマンドラインからMySQLを自由に操れるようになれば、あなたのデータベーススキルは大きく向上し、できることの幅が格段に広がるでしょう。データベースの世界への扉を開くための強力なツールとして、MySQL CLIをぜひマスターしてください。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール