前提条件
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. データベースの中身を確認する
最初から自分で作ることもあるかもしれませんが、たいていは既存のサービスにジョインして既存のデータベースを触ることになると思います。したがって、まずはデータベースの情報を確認するところから始まるのではないでしょうか。
自分自身最初に使ったコマンドは以下の3つでした。
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(型情報)
には大きく分けて数値型、日付と時間型、文字列型の3種類ありますが、それぞれ結構な数のパターンがあって、すべてを一気に覚えるのは難しいです。
主なパターンを記載しておきますが、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…ダミーデータをつくるのに使いました。気軽に本番データなんか触れたもんじゃないので助かります。