5.検索を使い様々な角度からデータを分析する

この記事では、弊社の新人エンジニア研修の参考にMySQLを解説します。

今回は、MySQL Workbenchを使ってテーブルを検索する方法を解説します。

CRUDとは？

MySQLのようなデータベース管理システムにおいては、データを作成して、検索して、更新して、削除するという機能が必須になるのでした。それぞれ、【Create:新規作成と追加】、【Read:読み出しと検索】、【Update:更新】、【Delete:削除】の頭文字をとってCRUDと呼ぶのでしたね。ここからは複数の章で【Read】について学びます。

1. 検索の基本

SELECT文はテーブルからデータを抽出するための文です。

データベースが何のためにあるのかといえば、それはデータを取り出して活用するためです。そのため、データベースのDML【Data Manipulation Language:データ操作言語】の中でもSELECT文は最重要です。

先に用意した下図5.1のようなcars表に対してSELECT文を実行してみましょう。

SELECT文の構文

SELECT 列名1,列名2, ... FROM 表名;

どの表からどんな列（フィールド）を取得するのかという文になっているので直感的にも分かりやすいと思います。また、この操作はリレーショナル・データベースの3種類の表操作のうち射影【projection】にあたります。

SELECT文の例文

SELECT name, price FROM sip_a.cars;

＜実行結果＞

このようにSQL文の文末にはセミコロン(;)を付けます。このことはJavaを学んだ新人エンジニアの皆さんですと同じですので馴染みやすいでしょう。

「sip_a.cars」という表記は「スキーマ名.テーブル名」という意味です。スキーマを選択済みであればテーブル名だけにして「cars」と書いても同じことでしたね。

また、以下5_2.sqlのようにキーワードの途中でなければ改行を入れて見やすくすることも可能です。

改行を入れて見やすくした例

SELECT
　name, price
FROM
　sip_a.cars;

上記の結果はMySQL WorkbenchのBeautify Queryという機能で実現しました。下図5.3のホウキのようなアイコンをクリック、または、ショートカットキー「Ctrl + B」でできますので積極的に活用しましょう。

図5.3

全て小文字にした例

Javaとは違いSQLは大文字と小文字を区別しません。したがって以下5_3.sqlのように書いても同じ結果が得られます。しかし、すべて小文字は見にくくなりますので「SELECT」、「FROM」といったキーワードは大文字の方が良いでしょう。

select
　name, price
from
　sip_a.cars;

また、以下5_4.sqlのように2つ目以降のフィールド名の先頭にカンマ（ , ）をつける書き方を好む人もいます。この書き方は、最後のフィールド名（下記5_4.sql でいえば「end_of_life_dates」）の後ろにカンマを付けてしまうミスを防ぐのに役立ちます。

select
　car_id
　,name
　,price
　,end_of_life_dates
from
　sip_a.cars;

フィールド名等をスペルミスなく入力するにはNetBeansのときと同様に「Ctrl + Space」でコード補完が使えます。

さらに、下図5.4のColumnsの下からダブルクリックまたはドラッグ・アンド・ドロップでフィールド名やテーブル名を挿入できることを知っておくと良いでしょう。

列名やテーブル名を挿入できる — 図5.4 フィールド名やテーブル名を挿入できる

コメントを入れてわかりやすくした例

なお、下図5.5のようにコメントを入れることでSQLを分かりやすく説明することができます。

一行コメントは「--」、複数行コメントはJavaと同じ「/* */」です。

一行コメントのショートカットキーもJavaと同じですので講師に尋ねてください。

*を使って全てのフィールドを指定した例

最後に、よく使われる*（アスタリスク、英語圏では略称でスターとも呼ばれる）を使って全てのフィールドを指定する方法を5_5.sqlで見てみます。

SELECT
　*
FROM
　sip_a.cars;

結果はご自身で確認ください。

このあと、Javaプログラムの中にSQL文を記述するも学ぶ予定です。その際、
SELECT * FROM テーブル名
とプログラムの中に書くことは一般的ではありません。
その理由は以下の2点です。
①テーブルのフィールド構造が変化する可能性がある
②余分なデータまで取得することになり、速度が低下する
したがって、SELECTの後にはできるだけフィールド名を個別に記述すべきです。
ただし、この記事ではそのとき解説している論点を明確にしたいがために *を使っている場面があることを了解ください。

1.1. 検索の基本の例題

例題1

cars表から、全てのレコードのcar_idとpriceを取得しなさい。

例題2

cars表から、全てのレコードのnameとend_of_life_datesを取得しなさい。

2. 条件付き検索

先のSELECT文では全てのレコードを抽出しました。そこにWHERE句を使うことでレコードの抽出に条件をつけることができます。英語の【where】に「～の場合には」という意味があります。（例.Where there's a will, there's a way.）

抽出条件はJavaでも学んだ条件式です。条件式は結果がTRUE又はFALSEに評価できるものでなければならなかったことを思い出してください。

なお、この操作はリレーショナル・データベースの3種類の表操作のうち選択【selection】にあたります。

条件付きSELECT文の構文

SELECT 列名1,列名2, ... FROM 表名 WHERE 条件式;

2.1. 条件式の書き方

まずは、＝を使って条件と等しいレコードを抽出してみます。（Javaとは異なり=は1つです）

5_8.sqlの通り数値の場合はクォーテーションマークで囲うことなく、そのまま書きます。

SELECT 
    name, end_of_life_dates
FROM
    cars
WHERE
    id = 4;

＜実行結果＞

以下の5_9.sql、5_10.sqlのように文字列や日時の場合はシングルクォート（'）で囲みます。

SELECT 
    name, end_of_life_dates
FROM
    cars
WHERE
   name = 'クーペ';

＜実行結果＞

SELECT 
    name, end_of_life_dates
FROM
    cars
WHERE
   end_of_life_dates = '2021-01-01';

2.2. 算術演算子

Javaと同様の下表5.1の算術演算子が使えます。

Javaと異なる点は、割り算の際、4つの式の例はいずれも 0.5000 となるところです。

算術演算子	説明	式の例	例の結果
+	数値同士の加算	SELECT 1 + 2	3
-	数値同士の減算	SELECT 1 - 2	-1
*	数値同士の掛け算	SELECT 1 * 2	2
/	数値同士の割り算	SELECT 1 / 2 SELECT 1 / 2.0 SELECT 1.0 / 2 SELECT 1.0 / 2.0	0.5000

表5.1

以下5_11.sqlは、cars表からname,price,price*1.1を税込価格として全てのレコードを抽出します。

SELECT 
    name
    ,price
    ,price * 1.1
FROM
    cars;

＜実行結果＞

なお、以下5.12.sqlのようにprice * 1.1は税込価格という別名を付けて表示するとなお分りやすくなります。

別名をつける際にはAS句を使います。英語の【as】にはもともと「等しい」という意味があります。

SELECT 
    name
    ,price
    ,price * 1.1 as '税込価格'
FROM
    cars;

＜実行結果＞

例題3

cars表から、name、price、price*0.9をセール価格という別名で全てのレコードを取得しなさい。

2.3. 関係（比較）演算子1

Javaと同じように下表5.2のような演算子が使えます。

関係演算子	説明	式の例	例の結果
=	左辺と右辺が等しい	SELECT 1 = 1	1 (TRUE)
!= 　または　<>	左辺と右辺が等しくない	SELECT 1 != 2	1 (TRUE)
>	左辺が右辺より大きい	SELECT 1 > 2	0 (FALSE)
<	左辺が右辺より小さい	SELECT 1 < 2	1 (TRUE)
>=	左辺が右辺以上である	SELECT 1 >= 2	0 (FALSE)
<=	左辺が右辺以下である	SELECT 1 <= 2	1 (TRUE)

表5.2

注意点としては、等しいがイコール1つであること。（Javaは==でした）等しくないは、 <> も使えることです(Javaは!=だけでした)。

例えば、以下の5_14.sqlは、 cars表からpriceが100万円のnameとpriceを抽出します。

SELECT 
    name, price
FROM
    cars
WHERE
    price = 1000000;

＜実行結果＞

例題4

cars表から、car_idが6以上のnameとpriceを取得しなさい。

例題5

cars表から、priceが200万未満のidとname、priceを取得しなさい。

2.4. 関係（比較）演算子2

ここでは下表5.3を使ってJavaには存在しないSQL特有の関係演算子を見ていきます。

関係演算子	説明	句の例
BETWEEN　~　AND	値が値の範囲内に含まれているかどうかを確認します	WHERE price BETWEEN 1000000 AND 2000000;
IN	ある値が値セット内に含まれているかどうかを確認します	WHERE price IN (1000000 , 1980000);
IS NULL	NULLであるかどうかを確認します	WHERE end_of_life_dates IS NULL;
LIKE	文字列のパターン一致（あいまい検索）をします。	WHERE name LIKE '%車';

表5.3

なお、上記関係演算子はNOTをつけることで否定の意味になります。

具体的にはそれぞれ、「NOT BETWEEN ~ AND」,「NOT IN」 ,「IS NOT NULL」 ,「NOT LIKE」 です。

BETWEEN　~　AND を使ったSELECT文

以下の5_17.sqlは、cars表からpriceが100万円以上200万円以下のnameとpriceを抽出します。

SELECT 
    car_id, name, price
FROM
    cars
WHERE
    price BETWEEN 1000000 AND 2000000;

＜実行結果＞

このとき100万の軽自動車が含まれていることに留意ください。もしも、未満や超過という抽出条件を記述したい場合には"<"や">"記号を使うしかありません。

以下5_18.sqlのようにNOTを使うとpriceが100万円以上200万円以下でないnameとpriceを抽出します。

SELECT 
    car_id, name, price
FROM
    cars
WHERE
    price NOT BETWEEN 1000000 AND 2000000;

＜実行結果＞

INを使ったSELECT文

次にINを使ってリストにある場合にデータを抽出する例を見ていきます。

以下5_19.sqlのSELECT文は、cars表からpriceが100万円または 198万円のレコードを抽出します。

SELECT 
    *
FROM
    cars
WHERE
    price IN (1000000 , 1980000);

＜実行結果＞

上記SQLは論理演算子のORを使って書き換えることもできますのでやってみてください。

あなたの答え：

しかし、条件が2つならまだしも、それ以上に増えてくるとあなたはこのINを使った書き方に感謝するでしょう。

IS NULLを使ったSELECT文

次に、IS NULLを使って空欄のあるデータだけを抽出してみます。

NULLとは未定義を意味する特別な値です。数値のゼロ（0）や文字列の空白とは違います。

NULLが存在するメリットとしては、例えば銀行口座を開設したばかりで残高が未定義なのか、それとも0円なのかを区別することができます。

以下の5_20.sqlは、cars表からend_of_life_datesがNULL、すなわち販売中の車のname,とend_of_life_datesを抽出します。

SELECT 
    name, end_of_life_dates
FROM
    cars
WHERE
    end_of_life_dates IS NULL;

＜実行結果＞

このとき初学者が犯しがちなミスは

WHERE end_of_life_dates = NULL;

としてしまうことです。

NULL値と等しい値はありませんし NULL値との大小比較も無意味です。例えば、「SELECT NULL = NULL;」という文を実行すると結果はNULLとなり無意味です。

以下5_21.sqlは、cars表からend_of_life_datesがNULLでない、すなわち販売終了している車のname,とend_of_life_datesを抽出します。

SELECT 
    name, end_of_life_dates
FROM
    cars
WHERE
    end_of_life_dates IS NOT NULL;

＜実行結果＞

LIKEを使ったSELECT文

最後にこれも大変利用シーンの多いあいまい検索を解説します。

あいまい検索には以下のワイルドカードを使います。

「%」：任意の文字列 (ゼロ文字を含む)

「_」：任意の単一の文字

を意味します。

ワイルドカードとはその名の通りトランプのゲームでジョーカーが他のどのようなカードとも読み替えられることを考えるとイメージしやすいでしょう。

下表5.4に一覧にまとめます。

文字列	意味	該当する例	該当しない例
a%	「a」から始まる文字列	art, autumn, Australia（※）	idea
%a%	「a」を含む文字列	art, car, idea	book
%a	「a」で終わる文字列	Australia, idea	art
a_	「a」から始まる2文字	an	car
_a_	2文字目に「a」がある3文字	car	art
__a	「a」で終わる3文字	era	art, car

表5.4

とくに、 「xxx」を含む文字列 として %xxx% という表現は弊社の新人エンジニア研修でもよく使いますから押さえてください。

以下の5_22.sqlは、cars表から「車」で終わるレコードを抽出します。=ではなくLIKEを使うということも間違えやすいので気をつけましょう。英語でも【She is like a flower.】のような表現がありましたね。

なお、今回指定している照合順序の「utf8mb4_unicode_520_ci」では、英字の大文字と小文字が区別されません。

SELECT 
    car_id, name, price
FROM
    cars
WHERE
    name LIKE '%車';

＜実行結果＞

また、以下5_23.sqlのようにすると全てのレコードを抽出します。

この後、皆さんが作成するWebアプリケーションでも

%%の間にキーワードを入れて検索したら「あいまい検索」
%%の間にキーワードを入れずに検索したら「全件検索」

という場合によく使われますのでここでも解説しました。

SELECT 
    *
FROM
    cars
WHERE
    name LIKE '%%';

例題6

cars表から、以下のようにnameに「ー」が含まれるレコードのcar_id、name、priceを取得しなさい。

例題7

cars表から、以下のようにend_of_life_datesが2月のレコードのnameとprice、end_of_life_datesを抽出しなさい。

図5.24

ワイルドカードの考え方はSQL以外の情報処理の世界でも使われるものです。新人エンジニアの皆さんは、ぜひ、この機会にマスターしてください。

2.5. 論理演算子

Java同様、SQLでも論理演算子を使うと複雑な条件式を組み合わせることができます。SQLの論理演算子はANDとORとNOTです。NOTはすでに見たのでANDとORについて確認しましょう。一般にAND条件は対象を絞り込むのに対して、OR条件は対象を広げましたね。

下表5.5を使いしばらく遠ざかっているJavaの復習もしておきましょう。

演算子	式がtrueになる条件	使用例	Javaの場合(復習)
AND	左辺と右辺の両方がtrue	WHERE name LIKE '%ン%' AND end_of_life_dates IS NULL;	&&
OR	すくなくとも左辺と右辺のどちらかがtrue	WHERE name LIKE '%ン%' OR end_of_life_dates IS NULL;	\|\|

表5.5

具体例を以下の5_26.sqlと5_27.sqlで見てみます。

SELECT 
    name, price, end_of_life_dates
FROM
    cars
WHERE
    name LIKE '%ン%' AND end_of_life_dates IS NULL;

＜結果を表示する＞

SELECT 
    name, price, end_of_life_dates
FROM
    cars
WHERE
    name LIKE '%ン%' OR end_of_life_dates IS NULL;

＜実行結果＞

上記例では2つの条件の組み合わせでしたが、3つ以上の条件を組み合わせることもできます。

例題を解いてみましょう。

例題8

cars表から、以下のように販売中でかつpriceに1.1を掛けた値が230万円以上かつnameに「ン」が含まれるレコードのすべてのフィールドを抽出しなさい。

3つ以上の条件をつなげた際、AND条件はOR条件に優先します。だだし、丸括弧（）で優先順位を変えられるのはJavaで学んだとおりです。

今回みたWHERE句を使った条件指定はUPDATE文やDELETE文でも使用します。SELECT文だけで使うわけではありません。更新するレコードや削除するレコードを特定するためです。逆に言えば、 WHERE句のない UPDATE文やDELETE文は全てのレコードを対象にしますから一般的にはほとんど使わないと思っておきましょう。この点はまた触れていきます。

＜まとめ：隣の人に正しく説明できたらチェックを付けましょう＞

□ SELECT文の作り方3ステップ

例：cars表から税込価格が230万円以上で名前に「ン」を含み、販売中である車の名前と金額を抽出する

テーブルを選ぶ

STEP

フィールドを選ぶ

STEP

抽出条件を記述して完成

STEP

□ Javaに似た関係演算子として、「=, != または<>, >, <, >=, <=」が使用できる

□ Javaにはない関係演算子として、「BETWEEN　~　AND, IN, IS NULL, LIKE」が使用できる

□ NULL値と等しい値はなく、NULL値との大小比較も無意味である

□ 論理演算子は「AND, OR, NOT」である

以上、検索（SELECT文）について見てきました。

次は検索結果のソート、グループ化と集約関数について見ていきましょう。

新人エンジニア研修向けデータベース解説の目次に戻る

研修のカリキュラムを見てみる

CRUDとは？

1. 検索の基本

SELECT文の構文

SELECT文の例文

改行を入れて見やすくした例

全て小文字にした例

*を使って全てのフィールドを指定した例

1.1. 検索の基本の例題

例題1

例題2

2. 条件付き検索

条件付きSELECT文の構文

2.1. 条件式の書き方

2.2. 算術演算子

例題3

2.3. 関係（比較）演算子1

例題4

例題5

2.4. 関係（比較）演算子2

BETWEEN ~ AND を使ったSELECT文

INを使ったSELECT文

IS NULLを使ったSELECT文

LIKEを使ったSELECT文

例題6

例題7

2.5. 論理演算子

例題8

＜まとめ：隣の人に正しく説明できたらチェックを付けましょう＞

テーブルを選ぶ

フィールドを選ぶ

抽出条件を記述して完成

BETWEEN　~　AND を使ったSELECT文