| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
| 3.1 サーバへの接続およびサーバからの切断 | ||
| 3.2 クエリの入力 | ||
| 3.3 データベースの作成および使用 | ||
| 3.4 データベースおよびテーブルに関する情報の取得 | ||
3.5 バッチモードでの mysql の使用 | ||
| 3.6 一般的なクエリの例 | ||
| 3.7 双生児研究プロジェクトのクエリ | ||
| 3.8 Apache での MySQL の使用 |
この章は、mysql クライアントプログラムを使用して簡単なデータベースを作成し、使用する方法について説明する MySQL チュートリアルです。
mysql （"ターミナルモニタ" または単に "モニタ" とも呼ばれる）は、MySQL サーバへの接続、クエリの実行、および結果の表示の各機能を持つ対話式プログラムです。mysql は、バッチモードでも使用できます。クエリを記述したファイルをあらかじめ作成しておき、そのファイルの内容を実行するように mysql に指示します。この章では、mysql のこれら 2 つの使用方法について説明します。
--help オプションを指定して mysql を呼び出すと、そのオプションの一覧が表示されます。
shell> mysql --help |
この章では、マシンに mysql がインストールされていること、および MySQL サーバに接続できる環境にあることを前提としています。この前提が成立しない場合は、MySQL 管理者にお問い合わせください。ご自分が MySQL 管理者である場合、このマニュアルの別のセクションを参照する必要があります。
この章では、データベースを設定して使用するまでの過程について、最初から最後まで説明します。既存のデータベースに接続する方法に関心がある場合は、データベースおよびそこに含まれるテーブルの作成方法について説明しているセクションは飛ばしてもかまいません。
この章は基本的にチュートリアルとして記述されているので、詳細な説明の多くは省略されています。この章で説明している内容の詳細については、このマニュアルの関連セクションを参照してください。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
サーバに接続するには、通常 mysql を呼び出す際に MySQL ユーザ名および、ほとんどの場合、パスワードを入力する必要があります。ログインするマシンとサーバが動作しているマシンが異なる場合は、ホスト名も入力する必要があります。接続する際に必要な接続パラメータ（使用するホスト名、ユーザ名、およびパスワード）については、管理者にお問い合わせください。正しい接続パラメータがわかったら、以下のようにそれらを指定することで、サーバに接続できます。
shell> mysql -h host -u user -p Enter password: ******** |
******** はパスワードを表します。パスワードは、mysql が Enter password: プロンプトを表示してから入力します。
パスワードが正しければ、mysql> プロンプトの後に以下のようなウェルカムメッセージが表示されます。
shell> mysql -h host -u user -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25338 to server version: 4.0.14-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> |
プロンプトは、mysql がユーザからのコマンド入力を受け付けられる状態であることを示します。
MySQL のインストール方法によっては、ローカルホスト上で動作するサーバに匿名ユーザ（名前のないユーザ）として接続できます。使用しているマシンにそのような方法で MySQL がインストールされている場合、以下のように何もオプションを指定しないで mysql を呼び出しても、MySQL サーバに接続できます。
shell> mysql |
サーバに正常に接続した後は、mysql> プロンプトで QUIT（または \q）と入力することで、いつでもサーバから切断できます。
mysql> QUIT Bye |
Unix 上では、Ctrl-D キーを押してサーバから切断することもできます。
以下のセクションに示す例のほとんどは、サーバに接続した状態であることを前提としています。mysql> プロンプトが表示されている場合は、サーバに接続していることを示します。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
前のセクションで説明したように、サーバに接続していることを確認します。サーバに接続していることを確認するだけでは使用するデータベースの選択は行われませんが、ここではそれで十分です。今の時点では、テーブルの作成、テーブルへのデータロード、およびテーブルからのデータ取得を実行する方法よりも、クエリを発行する方法を学ぶことが重要です。このセクションでは、mysql が動作するメカニズムに慣れるためのさまざまなクエリを使用しながら、コマンド入力の基本的な原則について説明します。
以下のコマンドは、サーバにそのバージョン番号と現在の日付を照会する簡単なコマンドです。mysql> プロンプトの後に以下のとおりに入力し、Enter を押してください。
mysql> SELECT VERSION(), CURRENT_DATE; +--------------+--------------+ | VERSION() | CURRENT_DATE | +--------------+--------------+ | 3.22.20a-log | 1999-03-19 | +--------------+--------------+ 1 row in set (0.01 sec) mysql> |
このクエリによって、mysql についてのさまざまなことがわかります。
QUIT がそれに該当する。ほかの例外については後述する）。
mysql はそれをサーバに送信して実行を依頼し、返された結果を表示する。そして、mysql> プロンプトを再度表示して、次のコマンド入力を受け付けられる状態であることを示す。
mysql はクエリの出力を表形式（行と列）で表示する。先頭行には列のラベルが表示される。それ以降の行にはクエリの結果が表示される。通常は、データベースから取得したカラムの名前が、列のラベルとして表示される。テーブルのカラムではなく、式の値を取得した場合（上述の例の場合）、mysql は式そのものを列のラベルとして使用する。
mysql は、返されたレコード数およびクエリの実行に要した時間を表示する。これらの値からサーバのだいたいのパフォーマンスを知ることができる。ただし、これらの値はクエリの実行を開始してから終了するまでに経過した時間であり（CPU 時間やマシン時間ではない）、サーバの負荷やネットワーク遅延の影響を受けるので、正確な値ではない（以後、この章の例では "rows in set" の行の表示は省略する）。
キーワードは大文字と小文字のどちらでも入力できます。以下のクエリはすべて同等です。
mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE; |
以下に示す例では、mysql を簡単な計算機として使用できることが示されています。
mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+ |
これまでの例で使用したクエリは比較的短い単一行ステートメントでしたが、単一行に複数ステートメントを入力することもできます。この場合も、各ステートメントの末尾にセミコロンを入力するだけです。
mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | VERSION() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 1999-03-19 00:15:33 | +---------------------+ |
コマンドは 1 行に収める必要はなく、長いコマンドを複数行にわたって記述しても問題ありません。mysql は、入力行の終わりではなく、セミコロンをステートメントの終わりと判断して処理します。つまり、mysql への入力はフリーフォーマットであり、入力された行は、セミコロンが読み込まれるまで実行されません。
以下に簡単な複数行ステートメントを示します。
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+--------------------+--------------+
| USER() | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18 |
+--------------------+--------------+
|
この例を見ると、クエリの最初の行を入力した後に、プロンプトが mysql> から -> に変わっています。mysql は、このようにプロンプトを変えることによって、途中まで入力されたステートメントの残りの部分の入力を待っていることを示します。プロンプトには有用な情報が表示されます。この情報に注目することで、mysql が現在何を待っているのかを知ることができます。
途中まで入力したコマンドの実行をキャンセルする場合は、\c を入力します。
mysql> SELECT
-> USER()
-> \c
mysql>
|
この例でもプロンプトに注目してください。\c を入力するとプロンプトは mysql> に切り替わり、mysql が新しいコマンドの入力を待っていることが示されます。
表示される可能性があるプロンプトと、それが意味する mysql の状態について、以下の表に示します。
| プロンプト | 意味 |
mysql> | 新しいコマンドの入力待ち |
-> | 複数行コマンドで次の行の入力待ち |
'> | 単一引用符（`''）で始まる文字列を読み込み中の次の行の入力待ち |
"> | 二重引用符（`"'）で始まる文字列を読み込み中の次の行の入力待ち |
`> | バッククォート記号（``'）で始まる識別子を読み込み中の次の行の入力待ち |
単一行のコマンドを発行するつもりで末尾のセミコロンの入力を忘れたために、複数行ステートメントになってしまう場合がよくあります。この場合、mysql は入力待ちになります。
mysql> SELECT USER()
->
|
この場合（ステートメントの入力を完了したつもりなのに -> プロンプトが表示されるだけ）、たいていは mysql はセミコロンが入力されるのを待っている状態です。表示されるプロンプトの意味に気が付かない場合、対処法がわかるまでしばらくかかるかもしれません。このステートメントを完了するには、セミコロンを入力します。それによって mysql が入力されたステートメントを実行します。
mysql> SELECT USER()
-> ;
+--------------------+
| USER() |
+--------------------+
| joesmith@localhost |
+--------------------+
|
'> および "> の 2 つのプロンプトは、文字列の入力中に表示されます。MySQL では、文字列を `'' または `"' で囲んで表記することができます（'hello'、"goodbye" など）。また、mysql では、複数行にわたる文字列を入力できます。表示されるプロンプトが '> または "> の場合、`'' または `"' のどちらかの引用符で始まった文字列を含む行を入力している途中であり、その文字列の終わりを示す引用符がまだ入力されていないことを意味します。この動作は、実際に複数行にわたる文字列を入力している場合は問題になりませんが、実際にそういうケースは多いのでしょうか。いいえ、それほど多くはありません。むしろ、'> および "> のプロンプトが表示されることで、うっかり引用符を入力し忘れたことに気付かされる場合がほとんどです。例を示します。
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
">
|
このような SELECT ステートメントを入力してから Enter を押して、結果が表示されるのを待っていても、何も表示されません。この場合、このクエリになぜこんなに時間がかかるのかを不思議に思う前に、"> プロンプトの意味に気付く必要があります。このプロンプトは、mysql が入力途中の文字列の残りの部分が入力されるのを待っていることを示します（このステートメントには、"Smith で始まる文字列の終わりを示す引用符がないというエラーがあります）。
この場合、何をすればよいでしょうか。最も簡単な方法はコマンドをキャンセルすることです。ただし、この場合は \c を入力するだけではキャンセルできません。その入力は、現在途中まで入力されている文字列の一部として mysql に解釈されるためです。代わりに、まず文字列の終わりを示す引用符を入力し（これによって mysql が文字列の終わりを認識する）、それから \c を入力します。
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
"> "\c
mysql>
|
プロンプトが mysql> に戻り、mysql が新しいコマンドの入力を待っていることが示されます。
`> プロンプトは、'> および "> の 2 つのプロンプトに似ていますが、バッククォートで囲まれた識別子の入力が完了していないことを示します。
'>、">、および `> の各プロンプトの意味を知ることは重要です。これに気付かないと、文字列の終わりの入力を忘れた場合に、その後入力した内容が、QUIT も含めて、すべて mysql に無視されます。このような状態になると、特に現在のコマンドをキャンセルする前に文字列の終わりを示す引用符を入力する必要があることを知らない場合は、非常に混乱します。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
| 3.3.1 データベースの作成および選択 | ||
| 3.3.2 テーブルの作成 | ||
| 3.3.3 テーブルへのデータのロード | ||
| 3.3.4 テーブルからの情報の取得 |
コマンドの入力方法がわかったところで、次はデータベースにアクセスします。
自宅でさまざまなペットを飼っていて、それぞれについてさまざまな情報を記録することを考えます。データを格納するためのテーブルを作成し、必要な情報をロードすることで、それを実現できます。テーブルからデータを取得することで、ペットに関するさまざまな質問に答えることもできます。ここでは、以下の作業手順について説明します。
ペットデータベースは単純ですが（意図的）、同じような種類のデータベースが実際に使用される状況はいくらでも考えられます。たとえば、農家が家畜を管理したり、獣医が病歴を記録する際に、同じようなデータベースを使用できます。いくつかのクエリとサンプルデータを含むペットデータベースのディストリビューションは、MySQL Web サイトから入手できます。ディストリビューションは、圧縮 tar 形式（http://www.mysql.com/Downloads/Contrib/Examples/menagerie.tar.gz ）または Zip 形式（http://www.mysql.com/Downloads/Contrib/Examples/menagerie.zip ）で提供されています。
SHOW ステートメントを実行すると、現在サーバ上に存在するデータベースの一覧を表示できます。
mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+ |
多くの場合、表示されるデータベースの一覧は上の例と実際のマシンでは異なりますが、mysql および test の 2 つのデータベースはどちらにも含まれていると思われます。mysql データベースは、ユーザ特権の定義に使用するので必須です。test データベースは、ユーザがテストに使用する作業場所として提供されています。
注意: SHOW DATABASES 特権がない場合はすべてのデータベースは表示できません。 「4.4.1 GRANT および REVOKE の構文」 節 参照 。
test データベースが存在している場合、アクセスしてみます。
mysql> USE test Database changed |
注意: USE は、QUIT と同じように、セミコロンを末尾に付ける必要はありません（セミコロンを付けても悪影響はありません）。USE ステートメントは別の意味で特別です。このステートメントは単一行で入力する必要があります。
test データベースを使用して（アクセス権がある場合）、以降の例を試すことができますが、その際にこのデータベースに作成したオブジェクトは、このデータベースにアクセスできるほかのユーザによって削除される可能性があります。そのため、独自のデータベースを使用させてもらえるように MySQL 管理者に依頼する必要があります。ここでは menagerie という名前のデータベースを使用するものとします。管理者は以下のようなコマンドを実行する必要があります。
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host'; |
ここで、your_mysql_name は使用する MySQL ユーザ名、your_client_host はサーバに接続する際に使用するホストです。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
管理者が、権限を設定する際にデータベースも作成した場合は、それをすぐに使い始めることができます。そうでなければ、自分でデータベースを作成する必要があります。
mysql> CREATE DATABASE menagerie; |
Unix の場合、データベース名は（SQL キーワードとは異なり）大文字と小文字が区別されます。したがって、上記のコマンドで作成したデータベースを参照する際は必ず menagerie と指定する必要があります。Menagerie や MENAGERIE などを指定しても参照できません。これは、テーブル名でも同じです（Windows の場合はこの制限は適用されないが、1 つのクエリ内で使用するデータベース名およびテーブル名では、大文字と小文字の指定方法を統一する必要があります）。
データベースを作成しただけでは、そのデータベースの選択は行われません。明示的に選択する必要があります。menagerie をカレントデータベースにするには、以下のコマンドを使用します。
mysql> USE menagerie Database changed |
データベースを作成する必要があるのは 1 回だけですが、データベースの選択は、mysql セッションを開始するたびに実行する必要があります。データベースは、上記の例のように USE ステートメントを発行して選択します。別の方法として、mysql を呼び出すコマンドラインでデータベースを選択することもできます。その場合は、必要な接続パラメータを記述した後にデータベース名を追加するだけです。例を示します。
shell> mysql -h host -u user -p menagerie Enter password: ******** |
注意: 上記の例では menagerie はパスワードではありません。パスワードをコマンドラインの -p オプションの後で指定する場合は、スペースを空けずに記述します（たとえば、-p mypassword ではなく、-pmypassword と指定する）。ただし、パスワードをコマンドラインに記述すると、同じマシンにログインしているほかのユーザからそのパスワードが参照可能な状態になるので、そのような指定方法は推奨できません。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
データベースの作成は簡単です。ただし、以下の SHOW TABLES の結果でわかるように、現在は空のままです。
mysql> SHOW TABLES; Empty set (0.00 sec) |
難しいのは、データベースの構造、すなわち必要なテーブルとそれぞれのカラムを決めることです。
まず、ペットごとのレコードを持つテーブルを作成します。このテーブルには pet という名前を付けて、少なくとも動物の名前だけは格納する必要があります。名前だけではテーブルを作成する意味があまりないので、ほかの情報も格納する必要があります。たとえば、家族の中の複数の人物がペットを飼っている場合、ペットごとの飼い主を知りたい場合があります。また、種類や性別などの基本情報を記録することもできます。
年齢についてはどうでしょうか。年齢も重要な情報ですが、それをデータベースに格納するのは適切ではありません。年齢は時間の経過とともに変化するので、記録を頻繁に更新する必要があるためです。代わりに、誕生日などの固定値を格納するほうが適切です。そうすれば、必要なときに現在の日付と誕生日との差分を計算して年齢を知ることができます。MySQL には日付計算を行うための関数が用意されているので、これは難しいことではありません。年齢の代わりに誕生日を格納することには、ほかの長所もあります。
pet テーブルに格納すると便利な情報をほかにも思いつくかもしれませんが、これまでに挙げた名前、飼い主、種類、性別、誕生日、命日だけで、現在は十分です。
CREATE TABLE ステートメントを使用してテーブルのレイアウトを指定します。
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
|
name、owner、および species の各カラムの値は長さが固定ではないので、データ型としては VARCHAR が適しています。これらのカラムの長さはすべて同じである必要はありません。また、20 文字である必要もありません。1 から 255 の範囲で最適と思われる長さを指定できます（指定した長さが適切ではなく、後からもっと長いフィールドが必要なことがわかった場合のために、MySQL には ALTER TABLE ステートメントが用意されている）。
ペットの記録で性別を表すには、"m" と "f"、または "male" と "female" など、さまざまな表記が考えられます。ここは、"m" と "f" の 1 文字の値を使用するのが最も簡単です。
birth および death の各カラムに DATE データ型を使用するのは自明な選択です。
これでテーブルが作成できました。SHOW TABLES を実行すると、以下の情報が出力されます。
mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | pet | +---------------------+ |
テーブルが想定どおりに作成されたことを確認するために、DESCRIBE ステートメントを実行します。
mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ |
DESCRIBE は、テーブルのカラムの名前やそのデータ型を忘れた場合など、いつでも使用できます。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
テーブルを作成した後は、そこにデータを追加する必要があります。データを追加するには、LOAD DATA ステートメントおよび INSERT ステートメントを使用します。
ペットのレコードが以下のように表せるものとします（MySQL では日付が 'YYYY-MM-DD' 形式であると想定しており、普段使用している形式と異なる場合があります）。
| name | owner | species | sex | birth | death |
| Fluffy | Harold | cat | f | 1993-02-04 | |
| Claws | Gwen | cat | m | 1994-03-17 | |
| Buffy | Harold | dog | f | 1989-05-13 | |
| Fang | Benny | dog | m | 1990-08-27 | |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | |
| Whistler | Gwen | bird | 1997-12-09 | ||
| Slim | Benny | snake | m | 1996-04-29 |
ここでは空のテーブルを使用するので、そこにデータを追加する簡単な方法は、ペットごとの行を記述したテキストファイルを作成し、1 つのステートメントでそのファイルの内容をロードします。
ここでは、CREATE TABLE ステートメントで記述したカラムの順番に合わせて、1 行に 1 レコードの形式でタブで区切った値を記述したテキストファイル `pet.txt' を作成します。値がない場合（性別がわからない場合や生存中のペットの命日など）、代わりに NULL 値を使用できます。テキストファイルでそれを表現するには、\N （バックスラッシュと大文字の N）を使用します。たとえば、鳥の Whistler のレコードは以下のようになります。
| name | owner | species | sex | birth | death |
Whistler | Gwen | bird | \N | 1997-12-09 | \N |
テキストファイル `pet.txt' を pet テーブルにロードするには、以下のコマンドを使用します。
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; |
必要に応じて、カラムの区切り文字および改行コードを、LOAD DATA ステートメントで明示的に指定できます。デフォルトはそれぞれタブと LF です。上記のステートメントでテキストファイル `pet.txt' を正しく読み込むには、デフォルトで十分です。
このステートメントでエラーが発生する場合、使用している MySQL インストールで、LOCAL INFILE 機能がデフォルトで有効になっていないことが考えられます。これを変更する方法については、 「4.3.4 LOAD DATA LOCAL のセキュリティ関連事項」 を参照してください。
新しいレコードを 1 レコードずつ追加する場合は、INSERT ステートメントを使用します。CREATE TABLE ステートメントで記述したカラムの順番に合わせて各カラムの値を指定するのが、最も簡単な形式です。Diane が Puffball という名前のハムスターを新しく飼うことにした場合を考えます。この新しいレコードを追加するには、以下のように INSERT ステートメントを使用します。
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
|
注意: ここでは、日付と文字列の値は引用符で囲んだ文字列として指定されています。また、INSERT ステートメントでは NULL を直接記述して不明な値を表現できます。この場合は LOAD DATA で使用した \N は使用しません。
この例から、空のテーブルにデータを追加するときに複数の INSERT ステートメントを使用すると、1 つの LOAD DATA ステートメントを使用するよりもはるかに多くの文字を入力する必要があるということがわかります。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
| 3.3.4.1 全データの SELECT | ||
| 3.3.4.2 特定のレコードの SELECT | ||
| 3.3.4.3 特定のカラムの SELECT | ||
| 3.3.4.4 レコードのソート | ||
| 3.3.4.5 日付計算 | ||
3.3.4.6 NULL 値の使用 | ||
| 3.3.4.7 パターンマッチ | ||
| 3.3.4.8 レコードのカウント | ||
| 3.3.4.9 複数テーブルの使用 |
テーブルから情報を取得するには、SELECT ステートメントを使用します。このステートメントの一般的な形式を以下に示します。
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy; |
what_to_select は、取得するカラムを示します。ここにはカラムのリストを指定します。* を指定した場合は "すべてのカラム" を表します。which_table は、データの取得元のテーブルを示します。WHERE 節は省略可能です。指定する場合は、取得対象となるレコードの満たすべき条件を conditions_to_satisfy に指定します。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
以下に示す SELECT の最も簡単な形式を実行すると、テーブルの全データを取得します。
mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ |
この形式の SELECT を使用する例として、初期データをテーブルにロードした直後に、テーブルの全データを確認する場合があります。たとえば、Bowser の誕生日が正しくないのではないかと考えたとします。血統書を調べたところ、正しい生まれ年は 1979 年ではなく 1989 年であることがわかりました。
この場合、データを修正するには、少なくとも 2 つの方法があります。
DELETE と LOAD DATA を使用して、テーブルを空にしてから再ロードする。
mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; |
ただし、この方法を実行した場合、Puffball のレコードを再入力する必要がある。
UPDATE ステートメントを使用して、誤りのあるレコードだけを修正する。
mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser"; |
UPDATE によって該当レコードだけが変更され、テーブルを再ロードする必要はない。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
前のセクションで示したように、テーブル全体を読み取るのは簡単です。SELECT ステートメントで WHERE 節を省略すれば、テーブル全体を取得できます。しかし、通常は、特に大規模なテーブルの場合は、テーブル全体を読み取ることはありません。特定の質問に回答するというはっきりした目的があり、そのために必要な情報を取得するための制約を指定するのが普通です。ここで、ペットに関する質問という観点から、その回答を示すいくつかの SELECT クエリについて説明します。
テーブルから特定のレコードだけを選択することができます。たとえば、Bowser の誕生日が変更されたことを確認するには、以下のように Bowser のレコードを選択します。
mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ |
この出力から、生まれ年が 1979 年から 1989 年に正しく変更されたことが確認できます。
文字列の比較では、通常は大文字と小文字は区別されません。したがって、"bowser" や "BOWSER" などを指定しても、クエリから返される結果は同じです。
条件は、name 以外のカラムにも指定できます。たとえば、1998 年以後に生まれたペットを調べるには、birth カラムに条件を指定します。
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1"; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ |
条件は組み合わせることができます。たとえば、メスの犬を調べるには以下のように条件を指定します。
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ |
上記のクエリでは、AND 論理演算子を使用しています。そのほかに、OR 演算子もあります。
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird"; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+-------+---------+------+------------+-------+ |
AND と OR を混合して指定することができます。ただし、AND が OR よりも高い優先度で処理されます。両方の演算子を混合して使用する場合は、条件の関係を明示的に指定するためにかっこを使用することを推奨します。
mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
-> OR (species = "dog" AND sex = "f");
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
|
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
テーブルのレコード全体を表示する必要がない場合、表示するカラムだけをカンマで区切って指定します。たとえば、ペットの誕生日だけを取得するには、name カラムと birth カラムを選択します。
mysql> SELECT name, birth FROM pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Puffball | 1999-03-30 | +----------+------------+ |
ペットの飼い主を取得するには、以下のクエリを使用します。
mysql> SELECT owner FROM pet; +--------+ | owner | +--------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen | | Gwen | | Benny | | Diane | +--------+ |
この場合、クエリが各レコードの owner フィールドの値を単に取得しているので、重複している値があることに注意してください。出力するデータの量を最小限にするには、キーワード DISTINCT を追加して一意なレコードを 1 回だけ取得します。
mysql> SELECT DISTINCT owner FROM pet; +--------+ | owner | +--------+ | Benny | | Diane | | Gwen | | Harold | +--------+ |
WHERE 節を使用してレコードの選択とカラムの選択を組み合わせることができます。たとえば、犬と猫だけの誕生日を取得するには、以下のクエリを使用します。
mysql> SELECT name, species, birth FROM pet
-> WHERE species = "dog" OR species = "cat";
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+
|
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
これまでの例では、クエリから返されたレコードは順不同で表示されています。レコードの表示順序に何らかの意味があれば、クエリの出力を調べるのが簡単になる場合がよくあります。クエリの結果をソートするには、ORDER BY 節を使用します。
ペットの誕生日を日付順で取得するには、以下のクエリを使用します。
mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Bowser | 1989-08-31 | | Fang | 1990-08-27 | | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Chirpy | 1998-09-11 | | Puffball | 1999-03-30 | +----------+------------+ |
文字型のカラムでは、ソートは、ほかの比較演算子と同じように、大文字と小文字を区別しない方法で行われるのが普通です。これは、大文字と小文字の区別でしか違いを識別できないようなデータを含むカラムでは、順序が定義されないことを意味します。BINARY キャストを使用することで、大文字と小文字を区別してカラムのデータをソートするように指定できます。 ORDER BY BINARY col_name。
デフォルトのソート順序は昇順で、小さい値が大きい値よりも先に表示されます。逆の順序（降順）でソートするには、ソートするカラムの名前に DESC キーワードを付けます。
mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Puffball | 1999-03-30 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Fluffy | 1993-02-04 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Buffy | 1989-05-13 | +----------+------------+ |
複数のカラムでソートすることができ、その際、カラムごとに異なるソート順序を指定できます。たとえば、ペットの種類を昇順にソートし、同じ種類の中では誕生日を降順（若いペットから先に表示）でソートするには、以下のクエリを使用します。
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Chirpy | bird | 1998-09-11 | | Whistler | bird | 1997-12-09 | | Claws | cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy | dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +----------+---------+------------+ |
注意: DESC キーワードはその直前に記述されたカラム（birth）にのみ適用されます。species カラムのソート順序には影響しません。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
MySQL では、年齢計算や日付の一部の抽出など、日付計算を実行するためのさまざまな関数が用意されています。
それぞれのペットの年齢を取得するには、現在の日付と誕生日の年の部分の差を求め、現在の日付の月日の部分が誕生日の月日の部分よりも暦年で早い場合は、さらにそこから 1 を引くという計算をします。以下のクエリは、それぞれのペットについて、誕生日、現在の日付、および年齢を返します。
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
+----------+------------+------------+------+
|
ここで、YEAR() は日付の年の部分を抽出します。RIGHT() は日付の MM-DD（暦年）を表す右端の 5 文字を抽出します。MM-DD の値を比較する式は 1 または 0 に評価され、それによって CURDATE() が birth よりも暦年で早いと判断される場合は、年の差からさらに 1 を引きます。出力するカラムのラベルは、式全体だと長すぎるので、エイリアス（age）を使用して、わかりやすくします。
このままでもクエリは動作しますが、結果を何らかの順序でソートすれば、その内容を調べやすくなります。それには、ORDER BY name 節を追加して、名前でソートした結果を出力します。
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet ORDER BY name;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
+----------+------------+------------+------+
|
name ではなく、age を使用して出力をソートするには、別の ORDER BY 節を使用します。
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet ORDER BY age;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
+----------+------------+------------+------+
|
同じようなクエリを使用して、死んだペットの死亡時の年齢を取得できます。死んだペットかどうかは、death カラムの値が NULL かどうかを調べて判断します。それが NULL 以外の値であるペットについて、death カラムと birth カラムの値の差を計算します。
mysql> SELECT name, birth, death,
-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
-> AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+
|
このクエリでは、death <> NULL ではなく、death IS NOT NULL を使用しています。これは、NULL が特別な値であり、通常の比較演算子を使用して比較することができないためです。これについては、後で説明します。 「3.3.4.6 NULL 値の使用」 節 参照 。
来月に誕生日を迎えるペットを取得する場合はどうすればよいでしょうか。この場合の計算では、年と日の部分は必要なく、birth カラムの月の部分だけを抽出する必要があります。MySQL では、YEAR()、MONTH()、および DAYOFMONTH() など、日付の一部を抽出する関数が用意されています。ここでは、MONTH() が適しています。その動作を調べるには、birth および MONTH(birth) の両方の値を表示する簡単なクエリを実行します。
mysql> SELECT name, birth, MONTH(birth) FROM pet; +----------+------------+--------------+ | name | birth | MONTH(birth) | +----------+------------+--------------+ | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Fang | 1990-08-27 | 8 | | Bowser | 1989-08-31 | 8 | | Chirpy | 1998-09-11 | 9 | | Whistler | 1997-12-09 | 12 | | Slim | 1996-04-29 | 4 | | Puffball | 1999-03-30 | 3 | +----------+------------+--------------+ |
来月に誕生日を迎えるペットも、同じように簡単に取得できます。現在が 4 月であるとします。その場合、月の値は 4 であり、以下のように指定して、5 月（5）に生まれたペットを取得します。
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; +-------+------------+ | name | birth | +-------+------------+ | Buffy | 1989-05-13 | +-------+------------+ |
現在が 12 月の場合は多少複雑になります。12 月（12）に単純に 1 を足して翌月（13）に生まれたペットを取得しようとしても、そのような月は存在しないので成功しません。この場合は 1 月（1）に生まれたペットを取得する必要があります。
現在がどの月であっても動作するクエリを作成することもできます。この場合、クエリで特定の月数を指定する必要はありません。DATE_ADD() を使用すると、指定された日付に期間を加算できます。CURDATE() の値に 1 ヵ月を加算し、その結果から MONTH() を使用して月の部分を抽出すると、ペットの誕生日を調べる月がわかります。
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));
|
現在の月（剰余関数（MOD）を使用して現在が 12 月の場合は 0 にする）に 1 を加算する方法でも同じ結果が得られます。
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
|
注意: MONTH は 1 から 12 の範囲の値を返します。また、MOD(something,12) は 0 から 11 の範囲の値を返します。したがって、加算する前に MOD() を実行する必要があります。そうしないと、11 月（11）の次が 1 月（1）になります。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
NULL 値の使用
NULL 値については、慣れるまでは戸惑う点があるかもしれません。概念的には、NULL は何もない値または不明な値を意味し、ほかの値とは多少異なる扱い方をします。NULL かどうかを調べる場合、=、<、または <> などの算術比較演算子は使用できません。これを自分で試すには、以下のクエリを実行します。
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ |
これらの比較から意味のある結果が得られないことは明らかです。この場合は、IS NULL および IS NOT NULL の各演算子を使用します。
mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ |
注意: MySQL では、0 または NULL は false (偽)を意味し、それ以外は true (真)を意味します。ブール値演算では、true を表すデフォルト値は 1 です。
このように NULL は特別な扱い方をするので、前のセクションではペットが死んでいるかどうかを判断する際に death <> NULL ではなく、death IS NOT NULL を使用しました。
GROUP BY では、2 つの NULL 値は等しいとみなされます。
ORDER BY を処理する場合、NULL 値は、ORDER BY ... ASC では先頭に表示され、ORDER BY ... DESC では最後に表示されます。
注意: MySQL 4.0.2 から 4.0.10 では、NULL 値が正しく処理されていないため、ソートが昇順か降順かに関係なく、常に先頭に表示されます。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
MySQL では、標準の SQL のパターンマッチだけでなく、Unix の vi、grep、および sed などのユーティリティで使用されているのと同じような、拡張正規表現に基づくパターンマッチも用意されています。
SQL のパターンマッチでは、任意の 1 文字に一致する `_' や、任意の数（0 文字も含む）の文字に一致する `%' を使用できます。MySQL では、SQL パターンは、デフォルトでは、大文字と小文字が区別されません。ここではいくつかの例を示します。注意: SQL パターンを使用する場合は = または <> ではなく、LIKE または NOT LIKE の比較演算子を使用します。
`b' で始まる名前を取得するには、以下のクエリを実行します。
mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ |
`fy' で終わる名前を取得するには、以下のクエリを実行します。
mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ |
`w' を含む名前を取得するには、以下のクエリを実行します。
mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ |
ちょうど 5 文字の名前を取得するには、`_' パターン文字を 5 回繰り返したパターンを使用します。
mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ |
MySQL が提供するもう 1 つのパターンマッチでは、拡張正規表現を使用します。この種のパターンが一致しているかどうかを調べるには、REGEXP および NOT REGEXP（またはその同義語の RLIKE および NOT RLIKE）を使用します。
拡張正規表現の特徴の一部を以下に示します。
REGEXP パターンマッチは、そのパターンが、照合する値の任意の場所に現れている場合に成功する（LIKE パターンマッチはこれとは違って、そのパターンが、照合する値と完全に一致する場合にのみ成功する）。
拡張正規表現の動作を調べるために、前出の LIKE を使用したクエリを、ここでは REGEXP を使用するように書き換えています。
`b' で始まる名前を取得するには、名前の先頭と一致するかどうかを照合させるために `^' を指定します。
mysql> SELECT * FROM pet WHERE name REGEXP "^b"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ |
MySQL バージョン 3.23.4 より前のバージョンでは、REGEXP では大文字と小文字が区別されるので、上記のクエリは何も結果を返しません。この場合、大文字または小文字の `b' のどちらかと照合するには、以下のクエリを使用します。
mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]"; |
MySQL 3.23.4 以降のバージョンでは、REGEXP の比較で実際に大文字と小文字を区別させるには、BINARY キーワードを使用していずれかの文字列をバイナリ文字列にします。以下のクエリは、小文字の `b' で始まる名前とだけ一致します。
mysql> SELECT * FROM pet WHERE name REGEXP BINARY "^b"; |
`fy' で終わる名前を取得するには、名前の末尾と一致するかどうかを照合させるために `$' を指定します。
mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ |
`w' を含む名前を取得するには、以下のクエリを実行します。
mysql> SELECT * FROM pet WHERE name REGEXP "w"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ |
正規表現パターンは、値の任意の位置にパターンがあれば一致するので、ワイルドカードをパターンの両側に記述して、SQL パターンを使用する場合のようにパターンを値全体と一致させる必要はありません。
ちょうど 5 文字の名前を取得するには、`^' と `$' を使用してそれぞれ名前の先頭と末尾に一致させ、その間に `.' を 5 回繰り返して指定します。
mysql> SELECT * FROM pet WHERE name REGEXP "^.....$"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ |
前のクエリで "n 回繰り返し" 演算子である `{n}' を使用することもできます。
mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
|
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
データベースは "ある種のデータはテーブルに何回出現するか" という質問に答えるためによく使用されます。たとえば、自分の飼っているペットの数や、飼い主ごとに飼っているペットの数を調べたり、ペットに関するさまざまな個体数調査を実行することができます。
飼っているペットの総数をカウントするのは、"pet テーブルには何件のレコードがあるか" という質問と同じです。これは、このテーブルでは 1 匹のペットについて 1 レコードを使用しているためです。COUNT(*) はレコードの数をカウントします。したがって、飼っているペットの総数をカウントするには、以下のようなクエリを使用します。
mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ |
前に、ペットの飼い主の名前を取得するクエリを使用しました。COUNT() を使用すると、飼い主ごとに飼っているペットの数をカウントできます。
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ |
owner ごとのすべてのレコードをグループ化するために GROUP BY を使用していることに注意してください。これを指定しない場合、以下のエラーメッセージが表示されます。
mysql> SELECT owner, COUNT(*) FROM pet; ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause |
COUNT() と GROUP BY を組み合わせると、さまざまな方法でデータを特徴付けることができます。ペットのさまざまな個体数調査を実行する例を以下に示します。
種類ごとのペット数を取得します。
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ | species | COUNT(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ |
性別ごとのペット数を取得します。
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; +------+----------+ | sex | COUNT(*) | +------+----------+ | NULL | 1 | | f | 4 | | m | 4 | +------+----------+ |
（この出力では、NULL は性別が不明であることを示す）
種類および性別の組み合わせごとのペット数を取得します。
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | NULL | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ |
COUNT() を使用する場合、テーブル全体を取得する必要はありません。たとえば、前のクエリで犬と猫だけをカウントする場合、以下のクエリを実行します。
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = "dog" OR species = "cat"
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
|
性別が判明しているペットについて、性別ごとのペット数を取得するには、以下のクエリを実行します。
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
|
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
pet テーブルでは飼っているペットのデータを管理しています。ペットに関するその他の情報、たとえば獣医に通った回数や出産した日など、その一生に発生するイベントなどを記録する場合は、別のテーブルを作成する必要があります。このテーブルはどのような構成でしょうか。このテーブルに必要なカラムを以下に示します。
これらの検討事項を踏まえ、event テーブルの CREATE TABLE ステートメントは以下のようになります。
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
|
pet テーブルの場合と同じように、情報を記述したタブ区切りのテキストファイルを作成して初期レコードをロードするのが最も簡単です。
| name | date | type | remark |
| Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male |
| Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male |
| Buffy | 1994-06-19 | litter | 3 puppies, 3 female |
| Chirpy | 1999-03-21 | vet | needed beak straightened |
| Slim | 1997-08-03 | vet | broken rib |
| Bowser | 1991-10-12 | kennel | |
| Fang | 1991-10-12 | kennel | |
| Fang | 1998-08-28 | birthday | Gave him a new chew toy |
| Claws | 1998-03-17 | birthday | Gave him a new flea collar |
| Whistler | 1998-12-09 | birthday | First birthday |
これらのレコードをロードします。
mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event; |
pet テーブルに対して実行したクエリから学習した内容を参考にすれば、event テーブルからもさまざまなデータを取得できます。基本的な考え方は同じです。ただし、event テーブルだけでは質問に回答できない場合があります。それはどのような場合でしょうか。
それぞれのペットが出産したときの年齢を調べる場合を考えます。すでに 2 つの日付から年齢を計算する方法については説明しました。母親の出産日は event テーブルに格納されていますが、その日の母親の年齢を調べるには母親の誕生日が必要です。この誕生日は pet テーブルに格納されています。このことは、クエリで 2 つのテーブルを参照する必要があることを意味します。
mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
-> remark
-> FROM pet, event
-> WHERE pet.name = event.name AND type = "litter";
+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+--------+------+-----------------------------+
|
このクエリについては、注目すべき点がたくさんあります。
FROM 節にはそれら 2 つのテーブル名を列挙している。
name カラムがあるので、これは簡単に指定できる。このクエリでは、WHERE 節で name の値を使用して 2 つのテーブルのレコードを一致させている。
name カラムはどちらのテーブルにも存在するので、そのカラムを参照する場合はどちらのテーブルのカラムかを指定する必要がある。テーブルを指定するには、カラム名の前にテーブル名を前置する。
結合を実行する場合、必ずしも 2 つの異なるテーブルを使用する必要はありません。テーブルのレコードを同じテーブルのほかのレコードと比較する場合には、テーブルをそれ自体と結合させると便利です。たとえば、ペットの中で繁殖させる組み合わせを探す場合、pet テーブルをそれ自体と結合させて、同種のオスとメスの組み合わせの候補を取得することができます。
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+
|
このクエリでは、カラムを参照するために、テーブル名にエイリアスを指定して、それぞれのカラムの参照が、どちらのテーブルインスタンスと関連付けられているかをはっきりわかるようにしています。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
データベースやテーブルの名前を忘れた場合、または特定のテーブルの構造（カラム名など）を忘れた場合、どうすればよいでしょうか。MySQL では、サポートするデータベースおよびテーブルの情報を提供するステートメントを多数用意することで、このような問題を解決します。
SHOW DATABASES は、すでに説明したとおり、サーバが管理するデータベースの一覧を表示します。現在選択されているデータベースを取得するには、DATABASE() 関数を使用します。
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+ |
まだどのデータベースも選択していない場合は、NULL が返されます（MySQL 4.1.1 より前のバージョンでは空の文字列）。
カレントデータベースに含まれるテーブルを取得するには（テーブル名がわからない場合など）、以下のコマンドを実行します。
mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | event | | pet | +---------------------+ |
テーブルの構造を取得するには、DESCRIBE コマンドを使用します。このコマンドは、テーブルの各カラムに関する情報を表示します。
mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ |
Field はカラム名、Type はカラムのデータ型、NULL はカラムに NULL 値を格納できるかどうか、Key はカラムにインデックスが付加されているかどうか、Default はカラムのデフォルト値を、それぞれ示します。
テーブルにインデックスが付加されている場合、SHOW INDEX FROM tbl_name を実行するとその情報を取得できます。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
mysql の使用
これまでのセクションでは、mysql を対話式に使用して、クエリを入力し、結果を表示させていました。mysql は、バッチモードでも実行できます。バッチモードで実行するには、実行するコマンドをファイルに記述し、そのファイルから入力を読み込むように mysql に指示します。
shell> mysql < batch-file |
Windows 上で mysql を実行していて、ファイルに含まれる特殊文字によって問題が発生する場合、以下のように指定します。
dos> mysql -e "source batch-file" |
コマンドラインで接続パラメータを指定する必要がある場合、以下のようなコマンドを実行します。
shell> mysql -h host -u user -p < batch-file Enter password: ******** |
バッチモードで mysql を実行する場合、まずスクリプトファイルを作成してからそれを実行します。
スクリプトファイルに含まれるステートメントの一部でエラーが発生した場合も、その後の処理を継続させる場合は、--force コマンドラインオプションを指定する必要があります。
スクリプトを使用する理由を以下に示します。
mysql にそれを実行するように指示するだけでよい。
shell> mysql < batch-file | more |
shell> mysql < batch-file > mysql.out |
cron ジョブからクエリを実行する場合など、対話式で処理できない場合がある。この場合は、バッチモードを使用する必要がある。
mysql をバッチモードで実行する場合、その出力形式は、対話式に実行する場合とは異なります（より簡潔に出力される）。たとえば、mysql を対話式に実行する場合、SELECT DISTINCT species FROM pet の出力は以下のようになります。
+---------+ | species | +---------+ | bird | | cat | | dog | | hamster | | snake | +---------+ |
バッチモードで実行した場合は、以下のように出力されます。
species bird cat dog hamster snake |
バッチモードでも対話式と同じ出力形式でデータを取得するには、mysql -t を使用します。実行しているコマンドを出力にエコーするには、mysql -vvv を使用します。
source コマンドを使用すると、mysql プロンプトからでもスクリプトを実行できます。
mysql> source filename; |
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
ここでは、MySQL に関する一般的な問題を解決する方法の例を示します。
一部の例では、テーブル shop を使用します。このテーブルには、業者（ディーラー）の物品（品番）ごとの価格が格納されます。各業者は物品ごとに 1 つの定価を付けているものとし、（article, dealer）がレコードのプライマリキーになります。
コマンドラインツール mysql を呼び出して、データベースを選択します。
shell> mysql your-database-name |
（ほとんどの MySQL インストールで、データベース名 test を使用できます）
以下のステートメントを実行すると、テーブルを作成し、データを追加できます。
mysql> CREATE TABLE shop (
-> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
-> dealer CHAR(20) DEFAULT '' NOT NULL,
-> price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
-> PRIMARY KEY(article, dealer));
mysql> INSERT INTO shop VALUES
-> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
-> (3,'D',1.25),(4,'D',19.95);
|
上記のステートメントを発行した後、テーブルには以下の内容が格納されています。
mysql> SELECT * FROM shop; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+ |
| 3.6.1 カラムの最大値 | ||
| 3.6.2 特定のカラムの最大値が格納されているレコード | ||
| 3.6.3 グループごとのカラムの最大値 | ||
| 3.6.4 特定のフィールドのグループごとの最大値が格納されているレコード | ||
| 3.6.5 ユーザ変数の使用 | ||
| 3.6.6 外部キーの使用 | ||
| 3.6.7 2 つのキーを使用した検索 | ||
| 3.6.8 日ごとの訪問数の計算 | ||
3.6.9 AUTO_INCREMENT の使用 |
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
"品番が最も大きい物品は ?"
SELECT MAX(article) AS article FROM shop; +---------+ | article | +---------+ | 4 | +---------+ |
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
"最も高価な物品の数、業者、および価格は ?"
SQL-99 （および MySQL バージョン 4.1 以上）では、サブクエリを使用すると簡単にできます。
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); |
MySQL バージョン 4.1 より前のバージョンでは、2 段階に分けて実行します。
SELECT ステートメントを使用して、最高値をテーブルから取得する。
mysql> SELECT MAX(price) FROM shop; +------------+ | MAX(price) | +------------+ | 19.95 | +------------+ |
mysql> SELECT article, dealer, price
-> FROM shop
-> WHERE price=19.95;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+
|
これ以外に、すべてのレコードを価格の降順でソートし、MySQL 固有の LIMIT 節を使用して先頭のレコードだけを取得する方法もあります。
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1; |
注意:価格が 19.95 の物品が複数ある場合、LIMIT 節を使用した方法では、その中の 1 つしか取得できません。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
"物品ごとの最高値は ?"
SELECT article, MAX(price) AS price FROM shop GROUP BY article +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+ |
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
"物品ごとに最高値を付けている業者（複数可）は ?"
SQL-99（および MySQL バージョン 4.1 以降）では、以下に示すようなサブクエリを使用してこの問題を解決できます。
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
|
MySQL バージョン 4.1 より前のバージョンでは、複数の段階に分けて実行するのが最適です。
これは、テンポラリテーブルと結合を使用すると、簡単に実行できます。
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES shop READ;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
|
TEMPORARY テーブルを使用しない場合は、tmp テーブルもロックする必要があります。
"これを 1 つのクエリで実行できますか ?"
できます。ただし、MAX と CONCAT を使用した、非効率的な手段を使用する必要があります。
SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
|
この方法は、クライアント側で連結したカラムの分割を実行すると、多少効率良くできます。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
MySQL ユーザ変数を使用すると、クライアント側で一時変数を使用せずに結果を記憶することができます。 「6.1.4 ユーザ変数」 節 参照 。
たとえば、最高値および最安値が付けられている物品を取得するには、以下のクエリを実行します。
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+ |
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
MySQL バージョン 3.23.44 以降では、InnoDB テーブルで外部キーの制約のチェックをサポートしています。 「7.5 InnoDB テーブル」 節 参照 。 「1.8.4.5 外部キー」 も参照してください。
実際には外部キーを使用しなくても 2 つのテーブルを結合できます。InnoDB 以外の種類のテーブルの場合、MySQL が現在実行しないのは、1） CHECK を実行して、使用しているキーがテーブルまたは参照しているテーブルに実際に存在するかどうかを確認すること、2） 外部キーが定義されているテーブルから自動的にレコードを削除すること、の 2 つだけです。キーを使用してテーブルを結合すると、何の問題もなく動作します。
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
|
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
MySQL では、OR で結合された 2 つの異なるキーを使用した検索は、まだ最適化されていません（1 つのキーを複数の OR の部分で使用する検索は非常に最適化が進んでいる）。
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1' |
これは、一般的なケースでそのような検索を効率的に処理する方法を見つけだすだけの時間がないからです（一方、AND の処理は現在では完全に一般化され、極めて効率的に動作しています）。
MySQL 4.0 以降では、2 つの異なる SELECT ステートメントの出力を結合する UNION を使用することで、この問題を効率的に解決できます。 「6.4.1.2 UNION 構文」 節 参照 。 それぞれの SELECT ステートメントを使用した検索では 1 つのキーだけが使用されるので、それを最適化できます。
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' UNION SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; |
MySQL 4.0 より前のバージョンでは、TEMPORARY テーブルおよび異なる SELECT ステートメントを使用することで同じ効果を得られます。このような最適化は、SQL サーバが不適切な順序で最適化を実行してしまうような、非常に複雑なクエリを実行する場合にも最適です。
CREATE TEMPORARY TABLE tmp SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'; INSERT INTO tmp SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; SELECT * from tmp; DROP TABLE tmp; |
この解決方法は、実際には 2 つのクエリの UNION と同じです。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
ビットグループ関数を使用して、あるユーザが Web ページを訪問した月ごとの日数を計算する方法の例を以下に示します。
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);
|
このテーブルには、ユーザがページを訪問した日付を表す年月日が格納されます。月ごとの訪問日数を取得するには、以下のクエリを実行します。
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;
|
以下の結果が表示されます。
+------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 3 | | 2000 | 02 | 2 | +------+-------+------+ |
このクエリでは、年月の組み合わせに対して異なる日付が何回出現するかを、自動的に重複データを除去することによって計算しています。
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top ] | [Contents ] | [Index] | [ ? ] |
AUTO_INCREMENT の使用
AUTO_INCREMENT 属性を使用すると、新しく追加するレコードを識別するための一意な値を生成できます。
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),
("lax"),("whale"),("ostrich");
SELECT * FROM animals;
|
以下の結果が表示されます。
+----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+ |
SQL 関数 LAST_INSERT_ID() または C API 関数 mysql_insert_id() を使用すると、最後に生成した AUTO_INCREMENT の値を取得できます。
注意:複数レコードを同時に挿入する場合、 LAST_INSERT_ID()/mysql_insert_id() は、実際には最初に挿入したレコードの AUTO_INCREMENT の値を返します。これにより、レプリケーション設定の場合に、ほかのサーバでも正しく複数行の挿入を再現できます。
MyISAM テーブルと BDB テーブルでは、複合インデックスの2つめのカラムに AUTO_INCREMENT を指定できます。この場合、AUTO_INCREMENT カラムで生成される値は、MAX(auto_increment_column)+1) WHERE prefix=given-prefix として計算されます。これは、データを順序付きのグループに分割する場合に便利です。
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),
("bird","penguin"),("fish","lax"),("mammal","whale"),
("bird","ostrich");
SELECT * FROM animals ORDER BY grp,id;
|
以下の結果が表示されます。
+--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+ |
注意: こ�