1週間で身に付くMySQL #3

Pocket

おはようございます、未経験からITエンジニア転職を目指すいずみです。

前回に引き続き、MySQLのちょっと複雑な検索について、アウトプットしたいと思います。

一週間で身につくMySQL|トップページ～データベースの初心者でも、気軽にSQLが学習できるサイトです。

Contents

1 データの下準備
2 ちょっと複雑な検索 -WHERE-
3 サブクエリ(副問い合わせ)
4 まとめ

データの下準備

いろんな検索をかけるために、テーブルにデータを追記します。

まず、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)とイコールのレコードを検索する
結果が出力される

まとめ

サブクエリの理解に時間がかかりましたが、楽しく学習できました。

明日は並べ替えと集計に挑戦してみます。