この記事はDeNA 24 新卒 Advent Calendar 2023の22日目の記事です．

はじめに

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

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

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

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

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

その前に

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

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

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

テーブル作成

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

CHECK制約

想定ユースケース

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

簡単な説明

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

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

例文

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

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

IF NOT EXISTS

想定ユースケース

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

簡単な説明

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

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

例文

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

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テーブルのレコードが自動で削除される」設定が可能です。

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に佐藤が含まれているデータが取得できます。

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文字目を取得します。

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

NULLS FIRST/LAST

想定ユースケース

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

簡単な説明

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

例

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

SELECT * FROM users ORDER BY name NULLS LAST;

LAG / LEAD

想定ユースケース

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

簡単な説明

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

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

例

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

CREATE TABLE tests (
    user_id INT,
    score INT
);

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

おわりに

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