SQLでワイルドカードを使用する方法

投稿日: 2022-04-20 2022-04-20
タグ: Databases, Interactive, sql

序章

多くのコンピュータ言語と同様に、SQLではさまざまなワイルドカード文字を使用できます。 ワイルドカードは、1つ以上の他の文字または値を表すことができる特別なプレースホルダー文字です。これはSQLの便利な機能であり、データベース内に保持されている正確な値を知らなくても、データベースでデータを検索できます。

このガイドでは、SQLで指定されたワイルドカードを使用してデータをクエリする方法について説明します。

前提条件

このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム（RDBMS）を実行しているコンピューターが必要です。このガイドの手順と例は、次の環境を使用して検証されています。

Ubuntu 20.04 の初期サーバーセットアップガイドで説明されているように、管理者権限を持つ非rootユーザーとUFWで構成されたファイアウォールを備えたUbuntu20.04を実行しているサーバー。
Ubuntu 20.04にMySQLをインストールする方法で概説されているように、MySQLがサーバーにインストールされて保護されています。このガイドは、ステップ3で説明されているプロセスを使用して作成された非ルートMySQLユーザーで検証されました。

注：多くのRDBMSは、独自のSQL実装を使用していることに注意してください。このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。

また、ワイルドカードを使用して練習できるサンプルデータがロードされたデータベースとテーブルも必要です。これがない場合は、次の MySQLへの接続とサンプルデータベースのセットアップセクションを読んで、このガイドで例全体で使用するデータベースとテーブルを作成する方法の詳細を確認してください。

このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。次のLaunch an Interactive Terminal!ボタンをクリックして開始します。

MySQLへの接続とサンプルデータベースの設定

SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。

ssh sammy@your_server_ip

次に、MySQLサーバープロンプトを開き、sammyをMySQLユーザーアカウントの名前に置き換えます。このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。

mysql -u sammy -p

wildcardsDBという名前のデータベースを作成します。

CREATE DATABASE wildcardsDB;

データベースが正常に作成されると、次のような出力が表示されます。

OutputQuery OK, 1 row affected (0.01 sec)

wildcardsDBデータベースを選択するには、次のUSEステートメントを実行します。

USE wildcardsDB;

OutputDatabase changed

データベースを選択したら、次のコマンドを使用してデータベース内にテーブルを作成できます。例として、アプリケーションのユーザーのプロファイル情報を保持するuser_profilesという名前のテーブルを作成するとします。このテーブルには、次の5つの列が含まれます。

user_id：この列は、intデータ型の値を保持します。これはテーブルの主キーとしても機能し、各値はそれぞれの行の一意の識別子として機能します
name：最大30文字のvarcharデータ型を使用して表された各ユーザーの名前
email：この列には、ユーザーのメールアドレスが保持されます。これもvarcharデータ型を使用して表現されますが、最大40文字です。
birthdate：dateデータ型を使用して、この列には各ユーザーの生年月日が表示されます
quote：各ユーザーのお気に入りの引用。引用符に適切な文字数を提供するために、この列もvarcharデータ型を使用しますが、最大300文字です。

次のコマンドを実行して、このサンプルテーブルを作成します。

CREATE TABLE user_profiles (
user_id int,
name varchar(30),
email varchar(40),
birthdate date,
quote varchar(300),
PRIMARY KEY (user_id)
);

OutputDatabase changed

次に、いくつかのサンプルデータを空のテーブルに挿入します。

INSERT INTO user_profiles
VALUES
(1, 'Kim', '[email protected]', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'),
(2, 'Ann', '[email protected]', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'),
(3, 'Phoebe', '[email protected]', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'),
(4, 'Jim', '[email protected]', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'), 
(5, 'Timi', '[email protected]', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'),
(6, 'Taeko', '[email protected]', '1953-11-28', '"You miss 100% of the shots you don\'t take.
" -Wayne Gretzky'),
(7, 'Irma', '[email protected]', '1941-02-18', '"
You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose.
" -Dr. Seuss'), 
(8, 'Iris', '[email protected]', '1961-01-05', '"
You will face many defeats in life, but never let yourself be defeated." -Maya Angelou');

OutputQuery OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

これで、ガイドの残りの部分に従い、ワイルドカードを使用してSQLでデータをクエリする方法について学習する準備が整いました。

ワイルドカードを使用したデータのクエリ

はじめにで述べたように、ワイルドカードは、1つ以上の他の文字または値を表すことができる特別なプレースホルダー文字です。

SQLでは、定義されているワイルドカード文字は2つだけです。

_：ワイルドカードとして使用する場合、アンダースコアは1文字を表します。たとえば、s_mmyは、sammy、sbmmy、またはsxmmyと一致します。
%：パーセント記号のワイルドカードは0文字以上を表します。たとえば、s%mmyは、sammy、saaaaaammy、またはsmmyと一致します。

これらのワイルドカードは、LIKEまたはNOT LIKE演算子を使用したクエリのWHERE句でのみ使用されます。

前提条件セクションのサンプルデータの使用を説明するために、user_profilesテーブルにリストされているユーザーの少なくとも1人が、3文字の長さで、「im」で終わる名前を持っていることを知っているとしましょう。彼らが誰であるかを確認してください。これらのユーザーの名前の最初の文字がわからないため、_ワイルドカードを使用して次のクエリを実行し、ユーザーが誰であるかを見つけることができます。

SELECT * FROM user_profiles WHERE name LIKE '_im';

Output+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
| user_id | name | email               | birthdate  | quote                                                                           |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
|       1 | Kim  | [email protected] | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
|       4 | Jim  | [email protected]   | 1940-08-13 | "Whoever is happy will make others happy too." -Anne Frank                      |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

注：この例では、SELECTの後にアスタリスク（*）が続きます。これはSQLの省略形であり、「すべての列」を表します。

特定のアプリケーションやプログラミング言語、およびSQLの一部の実装では、この例で使用されているパーセント記号のように、アスタリスクが0個以上の文字を表すワイルドカード文字として使用されます。ただし、上記の例のアスタリスクは、1つ以上の不明な文字ではなく、特定の何か（つまり、user_profilesテーブルのすべての列）を表すため、ワイルドカードではありません。

NOT LIKE演算子には、LIKEとは逆の効果があります。ワイルドカードパターンに一致するすべてのレコードを返すのではなく、パターンに一致しないすべての行を返します。説明のために、前のクエリを再度実行しますが、LIKEをNOT LIKEに置き換えます。

SELECT * FROM user_profiles WHERE name NOT LIKE '_im';

今回は、name列の値が_imと一致するすべての行が結果セットから省略されます。

Output+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
| user_id | name   | email                      | birthdate  | quote                                                                                                                    |
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
|       2 | Ann    | [email protected]  | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt                              |
|       3 | Phoebe | [email protected]     | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin                                               |
|       5 | Timi   | big_voice@example.com      | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville                                     |
|       6 | Taeko  | [email protected]      | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky                                                              |
|       7 | Irma   | [email protected] | 1941-02-18 | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
|       8 | Iris   | [email protected]       | 1961-01-05 | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou                                  |
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

別の例として、データベースにリストされているユーザーの何人かが「I」で始まる名前を持っているが、すべてを覚えているわけではないことを知っているとします。次のクエリに示すように、%ワイルドカードを使用して、それらすべてを一覧表示できます。

SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'I%';

Output+---------+------+----------------------------+
| user_id | name | email                      |
+---------+------+----------------------------+
|       7 | Irma | [email protected] |
|       8 | Iris | [email protected]       |
+---------+------+----------------------------+
2 rows in set (0.00 sec)

MySQLでは、デフォルトでは、LIKEおよびNOT LIKE演算子は大文字と小文字を区別しないことに注意してください。これは、ワイルドカードパターンで「I」を大文字にしない場合でも、前のクエリが同じ結果を返すことを意味します。

SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'i%';

Output+---------+------+----------------------------+
| user_id | name | email                      |
+---------+------+----------------------------+
|       7 | Irma | [email protected] |
|       8 | Iris | [email protected]       |
+---------+------+----------------------------+
2 rows in set (0.00 sec)

ワイルドカードは正規表現とは異なることに注意してください。一般に、ワイルドカードはグロブスタイルのパターンマッチングで使用される文字を指しますが、正規表現は正規言語に依存して文字列パターンをマッチングします。

ワイルドカード文字のエスケープ

SQLのワイルドカード文字の1つを含むデータエントリを検索したい場合があります。このような場合、エスケープ文字を使用して、%または_のワイルドカード関数を無視し、代わりにプレーンテキストとして解釈するようにSQLに指示できます。

たとえば、データベースにリストされているユーザーの少なくとも2人が、パーセント記号を含むお気に入りの引用符を持っているが、彼らが誰であるかわからないとします。

次のクエリを実行してみてください。

SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';

ただし、このクエリはあまり役に立ちません。パーセント記号は、任意の長さの任意の文字列の代用として機能するため、テーブル内のすべての行を返します。

Output+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
| user_id | name   | quote                                                                                                                    |
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
|       1 | Kim    | "Never let the fear of striking out keep you from playing the game." -Babe Ruth                                          |
|       2 | Ann    | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt                              |
|       3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin                                               |
|       4 | Jim    | "Whoever is happy will make others happy too." -Anne Frank                                                               |
|       5 | Timi   | "It is better to fail in originality than to succeed in imitation." -Herman Melville                                     |
|       6 | Taeko  | "You miss 100% of the shots you don't take." -Wayne Gretzky                                                              |
|       7 | Irma   | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
|       8 | Iris   | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou                                  |
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

パーセント記号をエスケープするには、MySQLのデフォルトのエスケープ文字であるバックスラッシュ（\）を前に付けることができます。

SELECT * FROM user_profiles WHERE quote LIKE '\%';

ただし、このクエリは、quote列の内容がのみでパーセント記号で構成されている必要があることを指定しているため、役に立ちません。したがって、結果セットは空になります。

OutputEmpty set (0.00 sec)

これを修正するには、LIKE演算子に続く検索パターンの最初と最後に、パーセント記号のワイルドカードを含める必要があります。

SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%\%%';

Output+---------+--------+----------------------------------------------------------------------------+
| user_id | name   | quote                                                                      |
+---------+--------+----------------------------------------------------------------------------+
|       3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin |
|       6 | Taeko  | "You miss 100% of the shots you don't take." -Wayne Gretzky                |
+---------+--------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

このクエリでは、バックスラッシュは2番目のパーセント記号のみをエスケープしますが、1番目と3番目の記号は引き続きワイルドカードとして機能します。したがって、このクエリは、quote列に少なくとも1つのパーセント記号が含まれているすべての行を返します。

次の例のように、ESCAPE句を使用してカスタムエスケープ文字を定義することもできることに注意してください。

SELECT user_id, name, email FROM user_profiles WHERE email LIKE '%@_%' ESCAPE '@';

Output+---------+--------+----------------------------+
| user_id | name   | email                      |
+---------+--------+----------------------------+
|       1 | Kim    | [email protected]        |
|       3 | Phoebe | [email protected]     |
|       4 | Jim    | [email protected]          |
|       5 | Timi   | [email protected]      |
|       7 | Irma   | [email protected] |
+---------+--------+----------------------------+
5 rows in set (0.00 sec)

このクエリは、@記号をエスケープ文字として定義し、email列に少なくとも1つのアンダースコアが含まれているすべての行を返します。 ESCAPE句を削除すると、各行に少なくとも1つの@記号が含まれているため、クエリはテーブルからすべての行を返します。

結論

このガイドを読むことで、ワイルドカードSQLベースのデータベースを使用およびエスケープする方法を学びました。ここで概説するコマンドは、SQLを使用するすべてのデータベース管理システムで機能するはずです。すべてのSQLデータベースは独自の言語実装を使用しているため、各コマンドとそのオプションの完全なセットの詳細については、DBMSの公式ドキュメントを参照してください。

SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。

getdocs

13036