
おはようございます、未経験からITエンジニア転職を目指すいずみです。
前回に引き続き、MySQLのちょっと複雑な検索について、アウトプットしたいと思います。
一週間で身につくMySQL|トップページ~データベースの初心者でも、気軽にSQLが学習できるサイトです。Contents
データの下準備
いろんな検索をかけるために、テーブルにデータを追記します。
まず、ALTER文とUPDATE文で「性別というカラムの追加」を行いました。
# レコーダーを10件登録
INSERT INTO casts (id, name, affiliation, age) VALUES(4, '新垣 結衣', 'Free', 32);
INSERT INTO casts (id, name, affiliation, age) VALUES(5, '菅田 将暉', 'トップコート', 29);
INSERT INTO casts (id, name, affiliation, age) VALUES(6, '山本 耕史', 'ワイツーカンパニー', 45);
INSERT INTO casts (id, name, affiliation, age) VALUES(7, '佐藤 浩市', 'テアトル・ド・ポッシュ',61);
INSERT INTO casts (id, name, affiliation, age) VALUES(8, '坂東 彌十郎', '松竹エンタテイメント',66);
INSERT INTO casts (id, name, affiliation, age) VALUES(9, '宮澤 エマ', 'プロダクション尾木', 32);
INSERT INTO casts (id, name, affiliation, age) VALUES(10, '梶原 善', 'シス・カンパニー',56);
# 性別カラムを追加(先やっておけばよかったです...)
ALTER TABLE casts
ADD sex varchar(10) NULL;
# 既に登録されているレコードの性別カラムはNULLとなっているので、UPDATEする
UPDATE casts SET sex = 'Male' WHERE id = 1;
UPDATE casts SET sex = 'Male' WHERE id = 2;
UPDATE casts SET sex = 'Female' WHERE id = 3;
UPDATE casts SET sex = 'Female' WHERE id = 4;
UPDATE casts SET sex = 'Male' WHERE id = 5;
UPDATE casts SET sex = 'Male' WHERE id = 6;
UPDATE casts SET sex = 'Male' WHERE id = 7;
UPDATE casts SET sex = 'Male' WHERE id = 8;
UPDATE casts SET sex = 'Female' WHERE id = 9;
UPDATE casts SET sex = 'Male' WHERE id = 10;
ALTER文とUPDATE文で「性別というカラムの追加」を行いました。
その後、登録済のレコードの「性別カラム」はNULLとなっているので、UPDATE文で更新をかけています。

これでいろんな検索を試す準備ができました。
ちょっと複雑な検索 -WHERE-
早速ですが、鎌倉殿の「男性の出演陣」のデータが欲しいとき、以下のように書きます。
SELECT * FROM casts WHERE sex = 'Male';

WHERE句の後に、条件式を記述すれば良いのですね。
今回は=(イコール)を用いましたが、WHERE句では演算子を用いて比較できるようです。
それでは、比較演算子を使って「40歳以上の男性俳優」を検索してみましょう。
# 40歳以上の男性俳優
SELECT * FROM casts WHERE sex='Male' AND age >= 40;

渋い俳優陣ですね〜!
条件式にAND演算子を用いて、「40歳以上」かつ「男性」という検索を行なっています。
ざっとWHERE句を触ってみましたが、他にもさまざまなオプションが用意されています。これらを組み合わせて、複雑な検索をかけることができますので、見てみましょう。
WHEREのオプション① -BETWEEN-
英語の意味をそのまま捉えると、数値がある値の範囲にあるレコードを検索できます。
# BETWEEN句
SELECT * FROM テーブル WHERE 検索対象のカラム BETWEEN 数値の下限 AND 数値の上限;
鎌倉殿出演者の中で、同世代(年齢が30歳~40歳の間)の俳優を検索してみます。
# 30歳以上40歳以下の出演者
SELECT * FROM casts WHERE age BETWEEN 30 AND 40;

がっきーと宮澤エマが同世代なんですね。そして、小栗旬は思っていたより若い。
Betweenなので、数値の範囲指定のときに文法的にTOと書いてしまいそうですが、ANDなので注意です。
WHEREのオプション② -IN-
検索対象の値が、指定したカラムの中に存在しているかを検証します。
# IN句
SELECT * FROM テーブル WHERE 検索対象のカラム IN (検索値1, 検索値2, ...);
鎌倉殿出演者の中で、Team NACSと松竹所属の俳優を検索してみましょう。
# Team NACSと松竹所属の俳優
SELECT * FROM casts WHERE affiliation IN ('Team NACS', '松竹エンタテイメント');

坂東彌十郎は、今回の大河が初めての出演作だとか。
WHEREのオプション③ – LIKE-
メタ文字を用いて、文字の検索を行います。
# LIKE句
SELECT * FROM テーブル WHERE 検索対象のカラム LIKE 検索条件;
# メタ文字
## % : 任意の文字数の文字
## _ : 1文字の任意の文字
それぞれのメタ文字を用いた例を挙げてみます。
それでは、所属事務所が「◯◯カンパニー」の俳優を検索してみましょう。
# なんたらカンパニーという所属事務所の俳優
SELECT * FROM casts WHERE affiliation LIKE '%カンパニー';

メタ文字%を使えば、カンパニーの前に何文字入っていても、引っ張ってくれました。
続いて、「大泉さんの下の名前、漢字1文字なんだけど忘れちゃった。なんだっけ?」という検索です。ほとんどありえませんが、、、
# 大泉という苗字を持つ俳優
SELECT * FROM casts WHERE name LIKE '大泉 _';

メタ文字_を使って、大泉という苗字のレコードが引っ張れました。
サブクエリ(副問い合わせ)
サブクエリとは、SELECT文で取得した結果を、さらに他のSELECT文で利用する使い方です。
# サブクエリの文法
SELECT 列名1 FROM テーブル名1 WHERE 列名1 = (SELECT 列名2 FROM テーブル名);
サブクエリのSELECT文は全体を括弧で囲んで記述します。
処理の順番は、まずサブクエリ(括弧内)が実行され、1つのレコードを特定します。
そのサブクエリの結果を元に、外側の条件式(便宜上メインクエリと呼びます)が実行されて、該当するレコードを表示するという仕組みです。
ポイントは、メインクエリとサブクエリを結ぶ比較演算子です。
今回は、”=”を用いているので、「サブクエリが取得した値」が「メインクエリのカラム」と一致するか検証してます。
比較を行うということは、サブクエリの結果は1つでなければならないということです。
(ここをよく理解せずにSQLを叩いてて、めちゃくちゃエラーが出ました)
それでは実例です。
良い例が出てきませんでしたが、、、「宮澤姓の俳優の性別と一致するレコード」を検索します。
# 宮澤姓の俳優の性別と一致するレコード
SELECT * FROM casts WHERE sex=(SELECT sex FROM casts WHERE name LIKE '宮澤%');
処理の流れは以下の通りです。
- サブクエリが実行され、宮澤姓に該当する性別(Female)が取得される
- castsテーブルの性別カラムで、サブクエリの結果(Female)とイコールのレコードを検索する
- 結果が出力される

まとめ
サブクエリの理解に時間がかかりましたが、楽しく学習できました。
明日は並べ替えと集計に挑戦してみます。