【MySQL超初心者向け】これを覚えれば初心者になれる基本コマンドとその意味

前提条件

1. 対象としている方

• エンジニアになったばかりでMySQLを触ったこともない
• Railsアプリの開発に携わっているものの、User.find(params[:id])とかでよしなにデータベースにアクセスしてくれているっぽいけど、正直中身は何やっているのか分からない
• でもMySQLのコマンドそのものを理解しとかないとどこかでつまずくニオイは感知している

という方向けに書いています。
つまり筆者自身と同じ環境に置かれている方向けに書いています。
今までエンジニアじゃなかったけど、就職を機にエンジニアになった新卒の方、もしくは最近ジョブチェンしてエンジニア畑に入られた中途の方なども対象なのかな…と思います。

2. MySQL初心者の定義

　◯ MySQLにログインすることができる
　◯ 特定のデータベースを探すことができる
　◯ 簡単なクエリを生成してレコードを抽出することができる
　✕ サブクエリ、index、outer joinなど含めた複雑な検索はできない
　✕ 膨大なデータからレコードを抽出するとき、どんなクエリがパフォーマンス良いか判断できない

これは自分で勝手に定義したものなので、他にも考慮すべき視点はいろいろあると思いますが、「データベースに何が入ってるかちょっと頑張れば把握できる」くらいが初心者としてのゴールなのかなと個人的に思った次第です。

3. おことわり

「ここちょっと意味違いませんか…？」と思われる部分を見つけましたら、ぜひともコメント／編集リクエストをお願いします。また、基本的なコマンドをまとめるところにフォーカスしたかったので、MySQLをインストールする部分に関しては割愛しています。

まずはMySQLに入るところから

1. rails dbで入る

Railsアプリの作業ディレクトリにいる場合は、

$ rails db
  or
$ bundle exec rails db //アプリ側のgemを指定してコマンドを叩きたい場合はbundle execが必要

でデータベース内に入ることができます。もちろんそのアプリがMySQLを使っていることが前提です。なおrails newで生成したアプリでは、development環境でsqlite3が使われているはずなので、この記事で紹介するコマンドは残念ながらだいたい通用しません。別途MySQLのインストール等が必要です。

2. mysql -u rootで入る

rails dbでなくとも、通常のMySQLのコマンドでもログインすることは可能です。本来はユーザー名やらパスワードがないとログインできないようですが、ローカル環境で入る場合は、いわゆる匿名ユーザーとしてログインすることが可能です。
単にmysqlでログインすることも可能ですが、rootユーザーで入らないとデータベースはいろいろ見れないようです。したがって、

$ mysql -u root

で入るようにしてください。

データベースを操作する

1. データベースの中身を確認する

最初から自分で作ることもあるかもしれませんが、たいていは既存のサービスにジョインして既存のデータベースを触ることになると思います。したがって、まずはデータベースの情報を確認するところから始まるのではないでしょうか。
自分自身最初に使ったコマンドは以下の３つでした。

1.1. データベースを表示する

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| hoge               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.04 sec)

1.2. 特定のデータベースを使用する

mysql> USE database_name;
Database changed

1.3. テーブルを表示する

mysql> SHOW TABLES;

+----------------+
| Tables_in_hoge |
+----------------+
| FUGA_DATA      |
| HOGE_DATA      |
| MOCK_DATA      |
| PIYO_DATA      |
+----------------+
4 rows in set (0.00 sec)

2. データベースを作成／削除する

　2.1. データベースを作成する

mysql> CREATE DATABASE database_name;

　2.2. データベースを削除する

mysql> DROP DATABASE database_name;

※データベースの削除は非常に非常に非常に非常に非常に非常に非常に非常に非常に非常に非常に危険な行為です。慣れないうちは、自分で適当に生成したデータベースで遊び場をつくって試してください。

万が一にも運用に乗っているサービスの顧客データを誤って削除してしまった場合は…どうすればいいんでしょう…？？どのようにして復旧させるのか、それはそれで知っておくべきだとたった今思ったので、後日改めて調べることにします。

3. テーブルを作成／削除する

　3.1. テーブルを作成する

mysql> CREATE TABLE table_name (
    column_name(カラム名) column_definition(型情報) //最低一列以上のカラムは必要。
);

　3.2. テーブルを削除する

mysql> DROP TABLE table_name;

テーブルを作成するとき、最低一列以上のカラムを()内で定義してあげる必要があります。
また、column_definition（型情報）には大きく分けて数値型、日付と時間型、文字列型の３種類ありますが、それぞれ結構な数のパターンがあって、すべてを一気に覚えるのは難しいです。

主なパターンを記載しておきますが、INT、VARCHAR、DATETIMEの3つくらい覚えておいて、後は必要に応じて調べていくのが得策な気がします。

数値型

型 （M:最大表示幅）	範囲（符号あり｜なし）
TINYINT(M)	符号あり => -128~127の整数 符号なし => 0~255の整数 0以外はtrue、0はfalse
INT(M)	符号あり => -2147483648~2147483647の整数 符号なし => 0~4294967295の整数

符号あり／なしはSIGNED | UNSIGNEDで定義することができます。たとえば、int_dataというテーブルをつくるとき、tiny_int_columnフィールドを符号なしに指定するには以下のように書けばOKです。

mysql> CREATE TABLE int_data (
    -> tiny_int_column TINYINT(10) UNSIGNED
    -> );
Query OK, 0 rows affected (0.01 sec)

こうすることで、tiny_int_columnは0以上の数値しか許容しなくなります。負の値を入れようとするとエラーが返ります。

日付と時間型

型	表示方法
DATE	'YYYY-MM-DD' 形式
DATETIME	'YYYY-MM-DD HH:MM:SS' 形式
TIME	'HH:MM:SS' 形式

MySQL5.6.4以降では、時間まで含む型に関して小数点以下6桁までの精度を持つようになったそうです。型情報の末尾()内に桁数を入れればOKですが、ここでは省略しています。いずれも作成するとしたら、以下のようになります。

mysql> create table time_data (
    -> date_column DATE,
    -> datetime_column DATETIME,
    -> time_column TIME
    -> );
Query OK, 0 rows affected (0.02 sec)

文字列型

型 （M:最大表示幅）	範囲	バイト数 （M＝6）の場合
CHAR(M)	0~255文字 固定長	'ab' => 6バイト分を確保
VARCHAR(M)	0~65535文字 可変長	'ab' => 文字数+1=3バイト分を確保

正確には指定した最大表示幅（Mの値）未満の文字列が格納された場合に限りますが、
　・CHAR => 格納する文字列の長さを問わず、指定されたデータ長の領域を確保します。
　・VARCHAR => 格納する文字列の長さに合わせたデータ長+1の領域を確保します。

こうしてみると、一見VARCHARの方が良いのかな？と思うんですが、状況によってパフォーマンスが変わったりする？そうで、ちょっと判断はつきにくいですね。ここは実際にサーバーサイドを触られている方にアドバイスを頂きたいです。

※VARCHARは"CHARACTER VARYING"の略だそうで、「バーチャー」とか「バーキャラ」と読むそうです。なんでCHARVARじゃないかは謎。

4. フィールドを追加／削除する

フィールドとは、テーブル上の列のことです。（ドットインストールより）
他にも、テーブル上の行のことをレコードと呼んだりするそうです。

4.1. フィールドを追加する

mysql> ALTER TABLE table_name ADD column_name(カラム名) column_definition(型情報);

4.2. フィールドを削除する

mysql> ALTER TABLE table_name DROP column_name(カラム名);

特定のテーブルからレコードを抽出する

今回デモとして使うテーブルはMOCK_DATAという名前にします。レコード数は1000件。中身のフィールドは（ちょっと適当ですが）以下のようになっています。

mysql> DESCRIBE MOCK_DATA;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50) | YES  |     | NULL    |                |
| last_name  | varchar(50) | YES  |     | NULL    |                |
| email      | varchar(50) | YES  |     | NULL    |                |
| gender     | varchar(50) | YES  |     | NULL    |                |
| ip_address | varchar(20) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

なお、適当なダミーデータを手軽につくりたい場合には、mockarooというサイトがオススメです。

1. SELECT構文の基本形を覚える

冒頭でもふれたように、データベースを触るようになって最初に行うのは、中身の情報を抽出することかと思います。その際、いろいろな条件を指定して検索をかけたりすることができるのですが、とにもかくにも基本の形をまずは覚えるに越したことはないです。
おおよそは、下記のフォーマットにしたがってコマンドを叩いていきます。

mysql> SELECT [抽出したい情報] FROM [テーブル名] [オプション条件|WHERE, ORDER BY, LIMIT,etc];

例えば、MOCK_DATAテーブルのfirst_nameフィールドの値を表示したいときは以下のように書きます。

mysql> SELECT first_name FROM MOCK_DATA;

ただ、上記のコマンドだと1000件すべて表示してしまいます。これはあまり現実的ではありません。
そこで、通常は以下に挙げるようなオプションを使って、検索条件を絞っていきます。

2. オプションで条件を加える

2.1. LIMIT => 行数を制限する

mysql> SELECT first_name FROM MOCK_DATA LIMIT 10;
+------------+
| first_name |
+------------+
| Craig      |
| Earl       |
| Rebecca    |
| Stephanie  |
| Anthony    |
| Cynthia    |
| Doris      |
| Shawn      |
| Joyce      |
| Janet      |
+------------+
10 rows in set (0.00 sec)

LIMIT 数値を文末に追加することで、表示させる行数を制限させることができます。この場合、10件のみを表示させています。

2.2. WHERE => 検索条件をさらに追加する

mysql> SELECT first_name WHERE gender = 'Male' FROM MOCK_DATA LIMIT 10;
+------------+
| first_name |
+------------+
| Craig      |
| Earl       |
| Anthony    |
| Shawn      |
| John       |
| Joshua     |
| Patrick    |
| Philip     |
| Russell    |
| Matthew    |
+------------+
10 rows in set (0.01 sec)

WHERE以下で詳細な条件を指定することができます。この場合、genderフィールドのMaleという値を持つレコードにさらに限定しています。複数の条件を加えたい場合はANDでつなぐこともできたりします。

2.3. ORDER BY => 検索結果をソートする

mysql> SELECT DISTINCT(first_name) FROM MOCK_DATA ORDER BY first_name ASC LIMIT 10;
+------------+
| first_name |
+------------+
| Aaron      |
| Adam       |
| Alan       |
| Albert     |
| Alice      |
| Amanda     |
| Amy        |
| Andrew     |
| Angela     |
| Ann        |
+------------+
10 rows in set (0.01 sec)

表示結果をソートさせたいときは、ORDER BY [フィールド名] [ASC（昇順） | DESC（降順）]を使います。この場合、first_nameフィールドを基準に昇順で表示させています。
なお、[抽出したい情報]の部分をDISTINCT(first_name)にしているのは、重複している値を除きたかったからです（重複を許すとAaronってヤツばかりで埋まってしまいました）。

2.4. COUNT => 指定したカラムの値の数を数える

mysql> SELECT COUNT(first_name) FROM MOCK_DATA WHERE email LIKE '%.com';
+-------------------+
| COUNT(first_name) |
+-------------------+
|               579 |
+-------------------+
1 row in set (0.00 sec)

[抽出したい情報]の部分がCOUNT(first_name)になっています。()内にカウントしたいフィールド名をいれればOKです。

なお、LIKEを使っているのは、emailフィールドの値に部分一致の検索をかけるためです。この場合、末尾が.comになるようなメールアドレスを指定していますね。

2.5. SUM => 指定のカラムの値を合計する

mysql> SELECT SUM(id) FROM MOCK_DATA;
+---------+
| SUM(id) |
+---------+
|  500500 |
+---------+

これは想像しやすいとおもいますが、数値の合計数を出しています。当然ですが、数値でない文字列などにSUMをかけても0にしかなりません。

2.6. BETWEEN => 特定の範囲に限定する

mysql> SELECT SUM(id) FROM MOCK_DATA WHERE id BETWEEN 1 AND 100;
+---------+
| SUM(id) |
+---------+
|    5050 |
+---------+

「先月の販売個数が知りたい」とか「◯番から◯番までのレコードを抽出したい」という場面も多々あったりします。そのようなときは、BETWEEN ... AND ...をWHERE以下で指定してあげてください。

まとめ

超初心者向けの方（ちょっと前の自分）が、

• MySQLにログインすることができる
• 特定のデータベースを探すことができる
• 簡単なクエリを生成してレコードを抽出することができる

ぐらいになるまでを目指して書いてみました。このレベルになれば、少しMySQLで遊べるくらいにはなるかなと思います。

「まだ知識は浅いんだけど、すぐにMySQL叩けるようにならないと自分が上司にアタマ叩かれる…」なんて方はあまりいないかと思いますが、これから勉強したいと思っていて、まずは基本的なコマンドは打てるようになっておきたい、という方々に少しでも参考になればと思います。

次に覚えるべき項目

蛇足ですが、脱初心者に向けてさらに勉強すべき項目ってなんだろう？と勝手に考えてみました。

• indexの使い方
• データベースにアクセスする系のRailsメソッドの意味と役割
  └ select() joins() sum() where()など
  └ 実際にrails上でこれらがどんなSQLコマンドに変換されているのか
• 他のデータベース（MongoDB、SQliteなど）との違い…etc

本記事が初心者になるための知識を独断と偏見で集めたものだったので、上記項目も同様に独断と偏見なんですが…。

他にも、「ココ勉強をした方が理解がグッと深まるよ！」とか「この本は脱初心者に向けて読むなら良書だよ！」とか、はたまた「初心者でもこのコマンドとか知ってないとやばいんじゃねーの？」などのご指摘をいただければ幸いです。

参考にしたソース

• MySQL公式ドキュメント
  └チュートリアル…全体像をざっくり把握するのに便利です。
  └データ型…型情報を調べるときに使いました。
  　└数値型
  　└日付と時間型
  　└文字列型
  └データ定義ステートメント…データベース／テーブル作成・削除を勉強するのに参考にしました。他にもたくさんステートメントが用意されていて未知の領域がいっぱいです。
  └データ操作ステートメント…ここはSELECT構文のところしか見ていません。

• mockaroo…ダミーデータをつくるのに使いました。気軽に本番データなんか触れたもんじゃないので助かります。