MySQLまとめ

背景

Rails初学者で当初MySQL?といった状態だったのですが、いろいろと触ってRailsに慣れてきた現在、ログを眺めてMySQLの構文にも慣れてきたこと、
また正直RailsがわかればよしなにSQLクエリを叩いてくれるので軽視していましたが、データベースのイメージを掴むことや設計をする際にSQL側の視点からも考えられた方がイメージを掴みやすいだろうと思ったので自分用にまとめます。
同じような境遇の方の助けに少しでもなれば。

データベースとは

プログラミング言語を学んでいて嫌という程出てくる「データベース」。
そもそもデータベースというものは扱うデータの集合体であって、Webアプリケーションを作る際はほぼ出てくるであろうユーザー情報もデータベースに含まれる。

中でもプログラミングをしていて出てくる「データベース」というのはほぼリレーショナルデータベース（RDB）を指すもので
コンソールから眺めたり設計の際に下記の表のようなものは何度も見たことがあるのではないでしょうか。

id	name	email	password
1	user1	[e-mail]	banana
2	user2	[e-mail]	apple
3	user3	[e-mail]	orange

リレーショナルデータベースでは、基本的にこのような関連性を持った表形式で各データを扱い、
いくつも存在する表を結合したりして必要なデータを持ってくるといったイメージです。

また縦1列をカラム、横1行をレコード、1つ1つのセルにあたる要素をフィールドと言います。
このテーブルにデータを書き込んだり、また既に存在するデータを読み込んだりするためにSQLのクエリ（命令文）を用いて操作していきます。
今回は主に使用されているMySQLに基づいてまとめていきます。

MySQLの仕組み

MySQLでは、前述のテーブルがいくつもまとまったものをデータベースと呼びます。
というのも、例えば何らかの商品を売るためのサービスを作成するとして
その際に必要なデータは最低限

• 各商品に割り振られたID
• 名前
• 値段
• 写真
• 商品説明

あたりは必要なんじゃないかと思います。
これをすべて1つのテーブルで管理すると

ID	名前	値段	写真	商品説明
1	product1	100円	1.jpg	おすすめです
2	product2	200円	2.jpg	人気です
3	product3	300円	3.jpg	安いです

のようになるのですが、例えばこのテーブルに関連商品や値下げ情報、謳い文句や商品カテゴリーなどを追加していくとなると
テーブルのカラム数が横に伸びていきどんどん膨大な量になっていき、名前と値段だけを呼び出したい時に無駄に他の膨大な情報も呼びさなければいけないような状態に陥ります（クエリで制限はできます）。
それを避けるために、目的に応じて情報を細分化し、必要な情報だけを必要に応じて呼び出そうというのが今回の狙いです。

命令（クエリ）

当初1つの情報を呼び出すだけなのに、ログを眺めていると膨大なクエリが走っていて本当に　？　状態だったのですが
改めてまとめてみると意外と少ないというか単純でした。
大体下記のクエリがほとんどかなと思います。（もちろんこれだけ覚えればOKというほど単純ではないです）

命令（クエリ）	役割
CREATE	データベースそのものやテーブルを作成する
DROP	データベースやテーブルを削除する
ALTER	テーブルを変更する
INSERT	レコードを追加する
SELECT	レコードを指定して取得する
UPDATE	レコードを更新する
DELETE	レコードを削除する
COMMIT	データベースに更新処理を反映する
ROLLBACK	データベースの更新処理を中止、元に戻す
SAVEPOINT	セーブポイントを設定する

このあたりでしょうか。
大体眺めていて膨大な行のクエリが走っていても結局はよく見るとこれらのクエリの連続だったりします。
Webアプリケーションを作っていて実際に叩くことは少なさそうですが覚えておいて損はなさそう。

データベースを作成する（CREATE）

まず前述のクエリ表の中から、CREATEを使用してデータベースを作成してみます。
新たにデータベースを作成するにはCREATE DATABASEを叩きます。

CREATE DATABASE user;

こちらでuserデータベースが作成されます。
案外あっさり作成されちゃうものですね。
ただデータベースはあらゆる要素の大元の入れ物のようなものなのでこれがないと話が始まりません。

データベースを削除する（DROP）

削除する場合はこれも前述の通りDROPを使用します。
お察しの通りDROP DATABASEです。

DROP DATABASE user;

これで指定のデータベースが削除されました。
ここまで見ていて感じた方もいるかもしれませんが、英語の構文に近いのかな？と思います。

動詞	目的語	〜の
DROP	DATABASE	(of)user

みたいな感じですかね、伝わらなかったらすみません。

あとJavascriptなどでも見られますがMySQLでも文末にセミコロン（；）が必要です。
クエリを叩いても何も起こらないしエンターを押しても永遠に改行されるな、と思ったらプログラム側がずっと；の入力を待っている状態だったということが多々ありました。
何行でも待ってくれるので；を入力すれば無事クエリが走りますので入力してあげましょう。

既存のデータベースを確認する（SHOW）

データベースを操作する際、常に新しいものを作成/削除するとは限りません。
むしろデータベース自体はそれほど乱立することはなく、後述のテーブルの方がよく作成すると思うのですが
例えば既存のアプリケーションやデータベースを操作する際、どんなデータベースが存在しているのかを確認するのがこちらのSHOWになります。
SHOW DATABASES;（複数形）
のように使用すると、現在作成されているデータベースの一覧が表示されます。
データベース名の重複はだめなので先に確認しておきたい場合にも使えますね。

テーブルを作成する（CREATE）

ここで先述したテーブルの話になります。
テーブルとはデータベースの下に定義されているもので、具体的な値や関連性を保持しています。
この記事の最初に記述してあるものもテーブルの一例です。

こいつもデータベース同様CREATEを使用し

CREATE TABLE user(各カラムのオプション);

のような感じです。データベースと一緒ですね。
ただここで各カラムのオプションが登場しました。
というのも、データベースと違って、テーブルにはカラム（IDとか名前とか）とそれぞれに紐づく実際のデータがあるので
例えばIDは数字だ、名前は文字列だ、のようにカラムとその型を指定してあげる必要があります。
これによって例えばIDにあああのような文字が不正に登録されるのを防いだり、文字数を制限することもできるので結構大事だったりします。
すべて説明すると長くなりそう（自分でもまだすべてを理解しきっていない）なので簡単に説明すると
IDは数字、名前は文字列、と指定してテーブルを作成する場合は

CREATE TABLE user(id int, name varchar);

みたいな感じで各カラムに適用する型を指定しながらテーブルを作成します。
ちなみにカラムは作成してあげないと列のないテーブルを作成するようなものなので絶対に必要です。
詳しくはまた別の記事か追記にてまとめたいなと思います。

テーブルを削除する

これもデータベースのときと全く同じでDROPを使用し

DROP TABLE user;

こいつで一発削除できます。

テーブルにデータを挿入する（INSERT）

さて、ここまでは実際に扱うデータを格納するための入れ物を作ったり削除したりするためのクエリでしたが
ここからは実際にプログラミングで扱うデータをその入れ物に登録したりする作業になってきます。

まず最初に登録作業から。
データの登録にはINSERTを使用し、登録するデータベースや入れ物、またその中に入れるデータを指定し登録します。

例えばこれまで述べてきたUSERデータベースの中のUSERテーブルにIDと名前を登録してみましょう。

INSERT INTO user.user(id, name) VALUES(1, test_user);

このような形で、INSERT＋INTO＋格納するデータベースとテーブル＋VALUES＋格納するデータ
と指定してあげます。
実際にプログラミングを行う際には随時使用するデータベースを指定していると思うのですが
現在使用しているデータベースの中のテーブルに登録する場合は

INSERT INTO user.user(id int, name varchar);

このようにデータベース名を省略することができ、また今回のようにすべてのカラムにデータを登録する場合はカラム名の指定を省略し

INSERT INTO user VALUES(1, test_user);

と出来るようです。
こちらのほうがよく見る気がします。
こちらのクエリを叩いた結果が

id	name
1	test_user

こうなります。（実際にはidは自動で割り振ることがほとんどですよね）
他のフレームワークでユーザー登録などをした際に裏側のクエリで流れているのはこういった命令ってことですね。

テーブルからデータを取得する（SELECT）

テーブルへのデータの登録も完了しました。
それでは実際にそのデータを呼び出してみましょう。
登録されているデータを呼び出すにはSELECTを使用します。

SELECT * FROM user;

こちらでuserテーブルに登録されているすべてのデータを取得することができます。

ちなみにSELECTとFROMの間の*ですが、こちらがすべてのカラムを指定するということで
例えばここをnameなんかにすると

SELECT name FROM user;

userテーブルの中からnameカラムのデータだけを抽出することができます。
実際には名前だけ知りたいのに他のデータもすべて呼び出して名前のみ使う、なんてことはないとおもうので
こういった指定方法も知っておいて損はなさそうです。

条件式を指定して必要なデータのみを抽出する

id	name	email	age	job
1	ken	[e-mail]	20	student
2	hana	[e-mail]	17	student
3	aki	[e-mail]	28	teacher
4	toshi	[e-mail]	40	teacher

さて、仮にこのようなテーブルがあるとします。
この中から例えば生徒のデータだけを参照したい場合、4人だけだと簡単ですがより膨大な量になるといちいちすべてを照らし合わせていくのが大変ですね。
そんなときにjobがstudentの情報だけを指定して出力するのがWHEREになります。

実際に使用する際、前述のSELECTと組み合わせて使用するのですが

SELECT * FROM user WHERE job='student';

このように単純な取得クエリのあとに条件として繋げてあげます。
この場合、job='student'を満たすuserテーブル内のデータを取得するといった意味合いになりますね。
もちろん*ではなく取得カラムをnameにするなどしてもっと必要な情報を絞り込むこともできます。
こいつめっちゃ使います。

さらにさらに条件式を指定する

WHEREの指定は複数条件を指定することができ、ANDやOR、＞や＜などの不等号を使用し、さらに条件を絞り込むことができます。

SELECT * FROM user WHERE job='student' AND job='teacher';

例えばこうするとjob='student'かつjob='teacher'のデータを取得することができ

SELECT * FROM user WHERE age > 20;

こうすると20歳以上のデータを取得することができます。
また年齢などの数値については範囲指定をすることもでき

SELECT * FROM user WHERE age BETWEEN 15 AND 20;

こちらでageカラムが15から20のデータのみを指定することができます。

取得したデータを並べ替えて表示する（ORDER BY）

これまでのようにSELECTで取得したデータは、基本的にはデータが格納された際に割り振られたid順に出力されると思います。
ただ例えば先程取得したageカラムなど、年齢順に並べて取得したいなといった際に使うのがこちらのORDER BYです。
基本的にはこいつもオプションのようなものなので、検索クエリの後ろに指定してあげて

SELECT * FROM user ORDER BY age DESC;

こうしてあげるとuserテーブルから取得したすべてのデータを、ageカラムでDESC（降順）に並び替えて取得してくれます。
ちなみにDESCの反対でASC（昇順）もあるのですが、特別指定しない限りデフォルトは昇順で設定されていることがほとんどだと思います。
その場合は昇順指定はしなくても自動的に昇順に取得してくれます。
またちなみにですが
ASC = Ascending
DESC = Descending
です。

テーブルを結合して取得する

ここからが本番です。（個人的に）
というのもMySQLをちゃんと学ぼうと思ったきっかけがこいつで、Railsを扱っていて複数モデルにまたがるデータを取得する際、テーブルの結合イメージがもっと深まればスムーズに考えられそうだなと思ったからなんです。
ここではまず使用頻度の高い内部結合と外部結合の違いをまとめます。

内部結合（INNER JOIN）

データベースのデータを検索する際、複数のテーブルをまとめて取得したいときがあります。
そんなときに最もよく使うであろうものがこの内部結合です。

内部結合は、複数のデータベースで共通するカラムがある場合、それらをあらかじめ結合した状態で取得してくれます。
といってもいまいちイメージがつきにくいので（少なくとも自分は）

Userテーブル

name	age	class
ken	20	english
tom	23	japanese
kim	19	chinese
hana	19	french

Classテーブル

class	name
japanese	日本語
english	英語
korean	韓国語
chinese	中国語
french	フランス語

例えばこのような2つの要素をそれぞれ別のテーブルで管理してある場合、class名までまとめて取得したいときがあります。
その際に使うのが内部結合で、具体的にはその名の通りINNER JOINというもので指定してあげます。

SELECT * FROM user INNER JOIN class ON user.class = class.class;

このように、データを取得するSELECT文の後に
INNER JOIN＋結合するテーブル名＋テーブル1のカラム＝テーブル2のカラム
を指定してあげます。
そうすると、結果として

name	age	class	class	name
ken	20	english	english	英語
tom	23	japanese	japanese	日本語
kim	19	chinese	chinese	中国語
hana	19	french	french	フランス語

のように、あらかじめ共通するカラムで照らし合わせて結合した状態でデータを取得してくれます。
これが内部結合の仕組みってわけですね。
ここで注意しなければいけないのは、カラムの中身が共通していないkorean（韓国語）を持つデータについては取得されないということです
あくまでも指定したカラムの中で、同じ要素を持つレコードを取得するということに注意ですね。

ただ今回、ともにclassというカラムを指定しており、結果にもclassが2つ含まれていますね。
今回のようにカラム名が共通の場合は、さらにUSINGを使用することによってコードもシンプルに取得できます。

SELECT * FROM user INNER JOIN class ON USING(class);

このように、先程はON＋カラム名＝カラム名としていたところを
USING(共通のカラム名)とシンプルに記述することで、そのカラムを結合し、さらにまとめた状態で取得してくれます。

class	name	age	name
english	ken	20	英語
japanese	tom	23	日本語
chinese	kim	19	中国語
french	hana	19	フランス語

このように、結合したカラムを先頭にいい感じにまとまってくれましたね。
こちらの方がデータとしても見やすくて良さそうです。

ちなみに、先述のWHEREやORDER BYなども使用してさらにデータを上手く絞り込んだり並び替えて見やすくすることも可能です。

外部結合（OUTER JOIN）

さて、先程の内部結合に対して続いては外部結合になるのですが、こちらもお察しかと思いますが名前の通りでOUTER JOINを使用します。
先程の内部結合が、共通するカラムの中身を持つレコードのみを結合して取得するものであったものに対して
こちらの外部結合は、カラムの中身に共通のデータを持っていなくても、指定したカラムを結合して取得するものになります。
これだけだと？？状態だったのですが、さらにやっかいなのがこいつ、結合方法が2つあるんですよね。。

• LEFT OUTER JOIN＝左項（FROMの後）のテーブルを基準に結合する
• RIGHT OUTER JOIN＝右項(JOINの後)のテーブルを基準に結合する

の２つがあるんです。
といってもよくわかんなかったので、先程のテーブルを用いて具体的に取得してみます。

LEFT OUTER JOIN

Userテーブル

name	age	class
ken	20	english
tom	23	japanese
kim	19	chinese
saki	22	german
hana	19	french

Classテーブル

class	name
japanese	日本語
english	英語
korean	韓国語
chinese	中国語
french	フランス語

こいつらを2つの方法で結合してみます。
まず左項を基準に取得するLEFT OUTER JOIN

SELECT * FROM user LEFT OUTER JOIN class ON user.class = class.class;

こんな感じですね。
今回はLEFT OUTER JOINなので左項のuserテーブルを基準に取得します。
すると1つ問題が。
userテーブルに存在するgermanがclassテーブルには存在しません。
先述の内部結合のときはこのように共通でないものは取得されなかったのですが
外部結合はこれも結合して取得されます。

name	age	class	class	name
ken	20	english	english	英語
tom	23	japanese	japanese	日本語
kim	19	chinese	chinese	中国語
saki	22	german	(null)	(null)
hana	19	french	french	フランス語

結果はこのようになりました。
userテーブルとclassテーブルをclassカラムで結合しつつ、そのカラムの中身が共通でない部分に関してはnull（空）として取得されます。
これが外部結合の性質で、要するに中身あるかないか関係ないから引っ付けて取得してしまえーって感じですね。

RIGHT OUTER JOIN

LEFT OUTER JOINに対してRIGHT OUTER JOIN。
なんとなくイメージはついていると思うのですが、こちらは右項のテーブルを基準とするので、左項にないデータがある場合にnullが入ります。

SELECT * FROM user RIGHT OUTER JOIN class ON user.class = class.class;

こいつで返ってくるのが

name	age	class	class	name
ken	20	english	english	英語
tom	23	japanese	japanese	日本語
(null)	(null)	(null)	korean	韓国語
kim	19	chinese	chinese	中国語
hana	19	french	french	フランス語

となります。
classテーブルを基準にuserテーブルに共通するレコードを割り当てて、存在しないデータはnullとなります。
LEFT OUTER JOINもそうですが、どちらのレコードを基準とするかを考えるとわかりやすいです。
またLEFT OUTER JOINとRIGHT OUTER JOINの結果は基準とするテーブルが違うので必然的に結果は変わってきます。（すべて共通のデータだったら同じです）

さらに内部結合のときにも書きましたが、WHEREなどを用いてデータを絞り込むことも可能ですのでいろいろと試してみてください。

おわりに

なんだかんだ基礎的なものをまとめていたら長くなってしまったのですが、Webアプリケーション開発をしていてよく見るなーといったSQLは大体このあたりかな？と思います。
もちろん他にもありますし、結合に結合を重ねて...なんでテクニックもあるので理解が深まったら調べてみてください！

私はまだまだ駆け出しのRailsエンジニアで毎日必死にログを眺めているのですが
今回まとめた部分をしっかりと理解するだけでもあの訳のわかんない怖かったログがかなり優しくなりました。
どころか普段データベースの書き込みや読み込みを単語程度のコードでやっちゃってたのですが
裏側での動きを理解することによって無駄なクエリを走らせないことを意識したりすることが出来るようになったので
ログへの恐怖心がなくなることはもちろん、自分の書くコードへの向き合い方も良い方向に変わったかなと思います。
本当に基礎的なことですがしっかりと理解しておくことをオススメします！！

まだまだ至らない部分もあると思うので、間違いや勘違いなどがあったらご指摘いただけますと大変ありがたいです。
それでは、がんばっていきましょう！