MySQLまとめ

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

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

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

| id | name | email | password |
|:-:|:-:|:-:|:-:|
|1|user1|user1@example.com|banana|
|2|user2|user2@example.com|apple|
|3|user3|user3@example.com|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`を叩きます。

```MySQL
CREATE DATABASE user;
```

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

# データベースを削除する（DROP）
削除する場合はこれも前述の通り**DROP**を使用します。
お察しの通り`DROP DATABASE`です。

```MySQL
DROP DATABASE user;
```

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

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

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

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

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

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

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

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

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

```MySQL
CREATE TABLE user(id int, name varchar);
```

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

# テーブルを削除する
これもデータベースのときと全く同じで**DROP**を使用し

```MySQL
DROP TABLE user;
```

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

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

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

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

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

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

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

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

```MySQL
INSERT INTO user VALUES(1, test_user);
```

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

|id|name|
|:-:|:-:|
|1|test_user|

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

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

```MySQL
SELECT * FROM user;
```

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

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

```MySQL
SELECT name FROM user;
```

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

## 条件式を指定して必要なデータのみを抽出する
|id|name|email|age|job|
|:-:|:-:|:-:|:-:|:-:|
|1|ken|ken@example.com|20|student|
|2|hana|hana@example.com|17|student|
|3|aki|aki@example.com|28|teacher|
|4|toshi|toshi@example.com|40|teacher|

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

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

```MySQL
SELECT * FROM user WHERE job='student';
```

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

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

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

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

```MySQL
SELECT * FROM user WHERE age > 20;
```

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

```MySQL
SELECT * FROM user WHERE age BETWEEN 15 AND 20;
```

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

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

```MySQL
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**というもので指定してあげます。

```MySQL
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**を使用することによってコードもシンプルに取得できます。

```MySQL
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**

```MySQL
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が入ります。

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

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