SQLってSelectとInsertとJoinくらいでしょwww

:::note info
この記事はDeNA [24 新卒 Advent Calendar 2023](https://qiita.com/advent-calendar/2023/dena-24-newgrad)の22日目の記事です．
:::

# はじめに

タイトルのことをちょっとでも考えたことある人、手あげてーー！！(*´▽`)ﾉ

わかります、2ヶ月前まで僕はこう考えていました。 (以下2ヶ月前の発言内容)

- 「**SQLってFromでテーブル選んで、Selectでカラム選んで、Whereで絞ったらいいんでしょ？**」
- 「**InsertもUpdateも構文のまんまやるだけだし。**」
- 「**InnerJoinとかOuterJoinとかよくわかんねぇけど、とりあえず結合するんでしょww**」

...。
今思うと浅はかでした。
そしてこの2ヶ月で、 **SQLの奥深さ** の一端を知りました。

今日はMySQL中心に便利だと思ったコマンドについてちょっと話してみたいと思います。

# その前に

この記事はあくまで「こんなコマンドあって、便利だと思うねん！」に重きを置いています。
なので書いているのは、

- 想定ユースケース
- 簡単な説明
- 例

の3つだけです。
詳しくは各自調べてみてくださいbb

# テーブル作成

テーブル作成(`CREATE`)するときに便利なコマンドです

## CHECK制約

### 想定ユースケース

「テーブルへのデータ挿入時に、いちいちアプリケーション側で判定するのがめんどくさい」

### 簡単な説明

型などでテーブルのカラムごとに制約をかけられることは知っていました。
しかしCHECK制約でより詳細に制約をかけることができます。

不等号・等号はもちろん、case文や正規表現などの表現も可能です。

### 例文

以下の場合だと「18歳以上の整数で、年収は0以上の整数」という制約がかかり、これに反するデータはエラーが返ります。

```sql
CREATE TABLE users (
    age INT CHECK (age >= 18),
    salary INT CHECK (salary >= 0)
);
```

## IF NOT EXISTS

### 想定ユースケース

「もし同名のテーブルがなかったら作成して、あったとてもエラーは返さないでほしい」

### 簡単な説明

もし同名のテーブルを作ろうとしたら、本来エラーが発生します。
しかし場合によってはUPSERTのようなテーブル作成をしてほしいケースがあります。

その時に`IF NOT EXISTS`を使うことで、簡単にやりたいことを達成することができます。

### 例文

以下の文ですと、`users`という名前のテーブルが同DBになかったら作成されます。
あくまで「同名かどうか」を確認するだけであり、カラム情報までは見ていないので注意です。

```sql
CREATE TABLE IF NOT EXISTS users (
    age INT CHECK (age >= 18),
    salary INT CHECK (salary >= 0)
);
```

## ON DELETE CASCADE

### 想定ユースケース

「もし外部キーで紐づいているテーブルのレコードが削除されたら、連動して当該のレコードも削除したい」

### 簡単な説明

`FOREIGN KEY`で外部キー制約をつけることはあると思いますが、参照先が消えたら参照元も同時に消えたいというニーズがあると思います。
そんな時、外部キー制約に「`ON DELETE CASCADE`」を付けることで、やりたいことを達成することができます。

またもし「参照先のキー名が更新されたら、こちらのキー名も更新したい」なんてときは「`ON DELETE UPDATE`」を使ってください。

### 例

以下の文ですと、「parentsテーブル内のid＝2のデータが削除されたとき、それに紐づくchildrenテーブルのレコードが自動で削除される」設定が可能です。

```sql
CREATE TABLE parents (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- 子テーブルの作成
CREATE TABLE children (
    id INT PRIMARY KEY,
    parent_id INT,
    child_name VARCHAR(255),
    FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE
);
```

## FULLTEXT

### 想定ユースケース

「テーブルに保存されている複数のカラムを横断してキーワード検索したい、だけど処理が重い。。」

### 簡単な説明

キーワード検索って、アプリケーションでよく実装されがちだと思うのですが、テーブル設計は難しいです。
しかしMySQLではキーワード検索をサポートしてくれています。

カラムの追加設定で、`FULLTEXT`と追加すると設定完了です。

あとは`MATCH AGAINST`という構文を使って`SELECT`すると簡単に取ってくることができます。

### 例

以下のテーブルを定義して、`SELECT * FROM users　WHERE MATCH (search_words) AGAINST ('佐藤');`などと検索すると、usersテーブルのnameとdescriptionに`佐藤`が含まれているデータが取得できます。

```sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT,
    description TEXT
    FULLTEXT KEY `search_words` (name, description)
);
```

# データ取得

テーブルからデータを取得(`SELECT`)するときに便利なメソッドです

## SUBSTRING

### 想定ユースケース

「取得したテキストを分割したい」

### 簡単な説明

取得したカラム名に`SUBSTRING`を使うことで、意図した場所でtextを分割することができます。
構文としては、
`SUBSTRING(カラム名, 何文字目以降か, 何文字目までか)`
となっています。

各言語のstring型にはありそうなメソッドですが、SQLにもついています。

### 例

以下のコマンドでは、usersテーブルのnameカラムの4~10文字目を取得します。

```sql
SELECT SUBSTRING(name, 3, 10) FROM users;
```

## NULLS FIRST/LAST

### 想定ユースケース

「`ORDER BY`でソートしたデータで、先頭にNULLが来ちゃう、末尾にしたい」

### 簡単な説明

`ORDER BY`でソートするとDBによってはNULLが先頭に来たり、末尾に来たりします。
この順番を制御します

### 例

以下だとnameがNULLのものは末尾に移動します。

```sql
SELECT * FROM users ORDER BY name NULLS LAST;
```

## LAG / LEAD

### 想定ユースケース

「点数のランキングを作って、一つ上の順位の人との点数の差分を取得したい」

### 簡単な説明

LEAD： 「後のレコードを参照する関数」
LAG： 「前のレコードを参照する関数」
です。これを使って、前後のレコードとの差分を取ったりできます。

~~(もうここまでくると、アプリケーションで実装しろよって思ってきますね)~~

### 例

以下の文で、testのスコアが高い順で並べて、スコアと上の順位との差分を取得します

```sql
CREATE TABLE tests (
    user_id INT,
    score INT
);

SELECT score, LAG(score, 1) - score FROM users ORDER BY score DESC;
```

# おわりに

おわりーーーー！！！
SQLって案外いろいろできるんだね、ってお話でした。