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 | [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 | +---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ 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の使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。
