第3章 MySQL チュートリアル/アダルトレンタルサーバーのサパネットワークス

第3章 MySQL チュートリアル
前のページ?	?	?次のページ

第3章 MySQL チュートリアル

この章は、mysql クライアントプログラムを使用して簡単なデータベースを作成し、使用する方法について説明する MySQL チュートリアルです。 mysql （``ターミナルモニタ'' または単に ``モニタ'' とも呼ばれる）は、MySQL サーバへの接続、クエリの実行、および結果の表示の各機能を持つ対話式プログラムです。mysql は、バッチモードでも使用できます。クエリを記述したファイルをあらかじめ作成しておき、そのファイルの内容を実行するように mysql に指示します。この章では、mysql のこれら 2 つの使用方法について説明します。

--help オプションを指定して mysql を呼び出すと、そのオプションの一覧が表示されます。

shell> mysql --help

この章では、マシンに mysql がインストールされていること、および MySQL サーバに接続できる環境にあることを前提としています。この前提が成立しない場合は、MySQL 管理者にお問い合わせください。ご自分が MySQL 管理者である場合、このマニュアルの別のセクションを参照する必要があります。

この章では、データベースを設定して使用するまでの過程について、最初から最後まで説明します。既存のデータベースに接続する方法に関心がある場合は、データベースおよびそこに含まれるテーブルの作成方法について説明しているセクションは飛ばしてもかまいません。

この章は基本的にチュートリアルとして記述されているので、詳細な説明の多くは省略されています。この章で説明している内容の詳細については、このマニュアルの関連セクションを参照してください。

3.1. サーバへの接続およびサーバからの切断

サーバに接続するには、通常 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> プロンプトが表示されている場合は、サーバに接続していることを示します。

3.2. クエリの入力

前のセクションで説明したように、サーバに接続していることを確認します。サーバに接続していることを確認するだけでは使用するデータベースの選択は行われませんが、ここではそれで十分です。今の時点では、テーブルの作成、テーブルへのデータロード、およびテーブルからのデータ取得を実行する方法よりも、クエリを発行する方法を学ぶことが重要です。このセクションでは、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 についてのさまざまなことがわかります。

通常、コマンドは SQL ステートメントの後にセミコロンを記述する形式で入力する（セミコロンが不要な場合もあり、たとえば前述の 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 に無視されます。このような状態になると、特に現在のコマンドをキャンセルする前に文字列の終わりを示す引用符を入力する必要があることを知らない場合は、非常に混乱します。

3.3. データベースの作成および使用

3.3.1. データベースの作成および選択
3.3.2. テーブルの作成
3.3.3. テーブルへのデータのロード
3.3.4. テーブルからの情報の取得

コマンドの入力方法がわかったところで、次はデータベースにアクセスします。

自宅でさまざまなペットを飼っていて、それぞれについてさまざまな情報を記録することを考えます。データを格納するためのテーブルを作成し、必要な情報をロードすることで、それを実現できます。テーブルからデータを取得することで、ペットに関するさまざまな質問に答えることもできます。ここでは、以下の作業手順について説明します。

データベースを作成する。
テーブルを作成する。
テーブルにデータをロードする。
さまざまな方法でテーブルからデータを取得する。
複数のテーブルを使用する。

ペットデータベースは単純ですが（意図的）、同じような種類のデータベースが実際に使用される状況はいくらでも考えられます。たとえば、農家が家畜を管理したり、獣医が病歴を記録する際に、同じようなデータベースを使用できます。いくつかのクエリとサンプルデータを含むペットデータベースのディストリビューションは、MySQL Web サイトから入手できます。ディストリビューションは、圧縮 tar 形式（http://downloads.mysql.com/docs/menagerie-db.tar.gz）または Zip 形式（http://downloads.mysql.com/docs/menagerie-db.zip）で提供されています。

SHOW ステートメントを実行すると、現在サーバ上に存在するデータベースの一覧を表示できます。

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

多くの場合、表示されるデータベースの一覧は上の例と実際のマシンでは異なりますが、mysql および test の 2 つのデータベースはどちらにも含まれていると思われます。mysql データベースは、ユーザ特権の定義に使用するので必須です。test データベースは、ユーザがテストに使用する作業場所として提供されています。

注意: SHOW DATABASES 特権がない場合はすべてのデータベースは表示できません。 See 項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 はサーバに接続する際に使用するホストです。

3.3.1. データベースの作成および選択

管理者が、権限を設定する際にデータベースも作成した場合は、それをすぐに使い始めることができます。そうでなければ、自分でデータベースを作成する必要があります。

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 と指定する）。ただし、パスワードをコマンドラインに記述すると、同じマシンにログインしているほかのユーザからそのパスワードが参照可能な状態になるので、そのような指定方法は推奨できません。

3.3.2. テーブルの作成

データベースの作成は簡単です。ただし、以下の 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 は、テーブルのカラムの名前やそのデータ型を忘れた場合など、いつでも使用できます。

3.3.3. テーブルへのデータのロード

テーブルを作成した後は、そこにデータを追加する必要があります。データを追加するには、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 ステートメントを使用するよりもはるかに多くの文字を入力する必要があるということがわかります。

3.3.4. テーブルからの情報の取得

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 に指定します。

3.3.4.1. 全データの SELECT

以下に示す 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 つの方法があります。

テキストファイル pet.txt を編集して誤りを修正し、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 によって該当レコードだけが変更され、テーブルを再ロードする必要はない。

3.3.4.2. 特定のレコードの SELECT

前のセクションで示したように、テーブル全体を読み取るのは簡単です。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  |
+-------+--------+---------+------+------------+-------+

3.3.4.3. 特定のカラムの SELECT

テーブルのレコード全体を表示する必要がない場合、表示するカラムだけをカンマで区切って指定します。たとえば、ペットの誕生日だけを取得するには、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 |
+--------+---------+------------+

3.3.4.4. レコードのソート

これまでの例では、クエリから返されたレコードは順不同で表示されています。レコードの表示順序に何らかの意味があれば、クエリの出力を調べるのが簡単になる場合がよくあります。クエリの結果をソートするには、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 カラムのソート順序には影響しません。

3.3.4.5. 日付計算

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 が特別な値であり、通常の比較演算子を使用して比較することができないためです。これについては、後で説明します。 See 項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）になります。

3.3.4.6. `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 値が正しく処理されていないため、ソートが昇順か降順かに関係なく、常に先頭に表示されます。

3.3.4.7. パターンマッチ

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）を使用します。

拡張正規表現の特徴の一部を以下に示します。

‘.’ は任意の 1 文字に一致する。
文字クラス '[...]' は、角かっこ内の任意の文字に一致する。たとえば、'[abc]' は ‘a’、‘b’、または ‘c’ に一致する。文字の範囲を指定するには、ダッシュを使用する。'[a-z]' は任意のアルファベットに一致し、'[0-9]' は任意の数字に一致する。
‘*’ は、その前に記述された 1 文字分の表現の 0 個以上の繰り返しと一致する。たとえば、'x*' は任意の数の ‘x’ と一致し、'[0-9]*' は任意の数の数字と一致する。'.*' は任意の数の任意の文字と一致する。
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  |
+-------+--------+---------+------+------------+-------+

3.3.4.8. レコードのカウント

データベースは ``ある種のデータはテーブルに何回出現するか'' という質問に答えるためによく使用されます。たとえば、自分の飼っているペットの数や、飼い主ごとに飼っているペットの数を調べたり、ペットに関するさまざまな個体数調査を実行することができます。

飼っているペットの総数をカウントするのは、``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 |
+---------+------+----------+

3.3.4.9. 複数テーブルの使用

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         |
+--------+------+-----------------------------+

このクエリについては、注目すべき点がたくさんあります。

このクエリは 2 つのテーブルから情報を取得する必要があるので、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     |
+--------+------+--------+------+---------+

このクエリでは、カラムを参照するために、テーブル名にエイリアスを指定して、それぞれのカラムの参照が、どちらのテーブルインスタンスと関連付けられているかをはっきりわかるようにしています。

3.4. データベースおよびテーブルに関する情報の取得

データベースやテーブルの名前を忘れた場合、または特定のテーブルの構造（カラム名など）を忘れた場合、どうすればよいでしょうか。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 を実行するとその情報を取得できます。

3.5. バッチモードでの `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 にそれを実行するように指示するだけでよい。
大量のデータを出力するクエリがある場合、画面上でデータが一気にスクロールアップするのを見るのではなく、出力をページ表示コマンドに渡して 1 ページ単位で表示させることができる。
```
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;

3.6. 一般的なクエリの例

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 の使用

ここでは、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. カラムの最大値

``品番が最も大きい物品は ?''

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

3.6.2. 特定のカラムの最大値が格納されているレコード

``最も高価な物品の数、業者、および価格は ?''

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 |
+------------+

上のクエリで表示された値 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 つしか取得できません。

3.6.3. グループごとのカラムの最大値

``物品ごとの最高値は ?''

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 |
+---------+-------+

3.6.4. 特定のフィールドのグループごとの最大値が格納されているレコード

``物品ごとに最高値を付けている業者（複数可）は ?''

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 |
+---------+--------+-------+

この方法は、クライアント側で連結したカラムの分割を実行すると、多少効率良くできます。

3.6.5. ユーザ変数の使用

MySQL ユーザ変数を使用すると、クライアント側で一時変数を使用せずに結果を記憶することができます。 See 項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 |
+---------+--------+-------+

3.6.6. 外部キーの使用

MySQL バージョン 3.23.44 以降では、InnoDB テーブルで外部キーの制約のチェックをサポートしています。See 項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 |
+----+-------+--------+-------+

3.6.7. 2 つのキーを使用した検索

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 を使用することで、この問題を効率的に解決できます。See 項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 と同じです。

3.6.8. 日ごとの訪問数の計算

ビットグループ関数を使用して、あるユーザが 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 |
+------+-------+------+

このクエリでは、年月の組み合わせに対して異なる日付が何回出現するかを、自動的に重複データを除去することによって計算しています。

3.6.9. `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 |
+--------+----+---------+

注意: この場合（AUTO_INCREMENT カラムが複合インデックスの一部として使用されている）、グループ内で最大の AUTO_INCREMENT 値を持つレコードを削除すると、そのグループで同じ AUTO_INCREMENT 値が再使用されることになります。これは MyISAM テーブルの場合にも発生する可能性があります（通常は AUTO_INCREMENT が再使用されることはない）。

3.7. 双生児研究プロジェクトのクエリ

3.7.1. 未訪問のすべての双生児の検索
3.7.2. 双生児の組のステータスをまとめた表の作成

Analytikerna および Lentus で、当社は大規模な研究プロジェクトのシステム業務および実地業務を担当しています。このプロジェクトは、Institute of Environmental Medicine at Karolinska Institutet Stockholm および Section on Clinical Research in Aging and Psychology at the University of Southern California の協同作業として行われています。

このプロジェクトでは、スウェーデン国内の 66 歳以上のすべての双生児に電話でインタビューする予備審査を行います。一定の条件を満足した双生児は、次のステージに進みます。このステージでは、プロジェクトに参加を希望する双生児を、医者と看護師からなるチームが訪問します。そこで、身体検査、神経心理学的検査、臨床試験、神経画像、精神状態評価、および家系収集などの作業が行われます。さらに、医学的および環境的な危険因子に関するデータも収集されます。

双生児研究の詳細については、http://www.mep.ki.se/twinreg/index_en.html を参照してください。

プロジェクトの後半部分は Perl および MySQL を使用して作成した Web インタフェースによって管理されています。

インタビューから得られたデータはすべて、毎晩 MySQL データベースに格納されます。

3.7.1. 未訪問のすべての双生児の検索

以下のクエリは、プロジェクトの後半部分に進む双生児を決定するために使用します。

SELECT
        CONCAT(p1.id, p1.tvab) + 0 AS tvid,
        CONCAT(p1.christian_name, " ", p1.surname) AS Name,
        p1.postal_code AS Code,
        p1.city AS City,
        pg.abrev AS Area,
        IF(td.participation = "Aborted", "A", " ") AS A,
        p1.dead AS dead1,
        l.event AS event1,
        td.suspect AS tsuspect1,
        id.suspect AS isuspect1,
        td.severe AS tsevere1,
        id.severe AS isevere1,
        p2.dead AS dead2,
        l2.event AS event2,
        h2.nurse AS nurse2,
        h2.doctor AS doctor2,
        td2.suspect AS tsuspect2,
        id2.suspect AS isuspect2,
        td2.severe AS tsevere2,
        id2.severe AS isevere2,
        l.finish_date
FROM
        twin_project AS tp
        /* For Twin 1 */
        LEFT JOIN twin_data AS td ON tp.id = td.id
                  AND tp.tvab = td.tvab
        LEFT JOIN informant_data AS id ON tp.id = id.id
                  AND tp.tvab = id.tvab
        LEFT JOIN harmony AS h ON tp.id = h.id
                  AND tp.tvab = h.tvab
        LEFT JOIN lentus AS l ON tp.id = l.id
                  AND tp.tvab = l.tvab
        /* For Twin 2 */
        LEFT JOIN twin_data AS td2 ON p2.id = td2.id
                  AND p2.tvab = td2.tvab
        LEFT JOIN informant_data AS id2 ON p2.id = id2.id
                  AND p2.tvab = id2.tvab
        LEFT JOIN harmony AS h2 ON p2.id = h2.id
                  AND p2.tvab = h2.tvab
        LEFT JOIN lentus AS l2 ON p2.id = l2.id
                  AND p2.tvab = l2.tvab,
        person_data AS p1,
        person_data AS p2,
        postal_groups AS pg
WHERE
        /* p1 gets main twin and p2 gets his/her twin. */
        /* ptvab is a field inverted from tvab */
        p1.id = tp.id AND p1.tvab = tp.tvab AND
        p2.id = p1.id AND p2.ptvab = p1.tvab AND
        /* Just the sceening survey */
        tp.survey_no = 5 AND
        /* Skip if partner died before 65 but allow emigration (dead=9) */
        (p2.dead = 0 OR p2.dead = 9 OR
         (p2.dead = 1 AND
          (p2.death_date = 0 OR
           (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
            >= 65))))
        AND
        (
        /* Twin is suspect */
        (td.future_contact = 'Yes' AND td.suspect = 2) OR
        /* Twin is suspect - Informant is Blessed */
        (td.future_contact = 'Yes' AND td.suspect = 1
                                   AND id.suspect = 1) OR
        /* No twin - Informant is Blessed */
        (ISNULL(td.suspect) AND id.suspect = 1
                            AND id.future_contact = 'Yes') OR
        /* Twin broken off - Informant is Blessed */
        (td.participation = 'Aborted'
         AND id.suspect = 1 AND id.future_contact = 'Yes') OR
        /* Twin broken off - No inform - Have partner */
        (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                      AND p2.dead = 0))
        AND
        l.event = 'Finished'
        /* Get at area code */
        AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
        /* Not already distributed */
        AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
        /* Has not refused or been aborted */
        AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
        OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
        tvid;

クエリの一部について簡単に説明します。

CONCAT(p1.id, p1.tvab) + 0 AS tvid
id と tvab を連結した値を、数値としての比較によってソートする。連結した結果に 0 を加算することで、MySQL はその結果を数値として扱う。
カラム id
双生児の組を識別する。これはすべてのテーブルでキーとして使用される。
カラム tvab
双生児の組の中の個人を識別する。この値は 1 または 2 のどちらかになる。
カラム ptvab
tvab の逆の値。tvab が 1 の場合、この値は 2 であり、その逆も同様である。このカラムは、入力量を少なくするためと、MySQL がクエリを最適化しやすくするために定義されている。

このクエリは、特に、同じテーブルを使用した結合（p1 と p2）でテーブルを参照する方法を示しています。この例では、双生児の一方が 65 歳以前に死亡しているかどうかの確認に使用されています。死亡している場合、そのレコードは返されません。

上で説明したカラムは、双生児関連のすべてのテーブルで定義されています。クエリの実行速度を向上するために、id,tvab の両方（すべてのテーブル）および id,ptvab （person_data）をキーとしています。

当社の実稼働マシン（200MHz UltraSPARC）では、このクエリは 150 ? 200 件のレコードを返し、所要時間は 1 秒未満です。

上記のクエリで使用されている各テーブルの現在のレコード数を以下に示します。

テーブル	レコード数
`person_data`	71074
`lentus`	5291
`twin_project`	5286
`twin_data`	2012
`informant_data`	663
`harmony`	381
`postal_groups`	100

3.7.2. 双生児の組のステータスをまとめた表の作成

インタビューが終わると、event と呼ばれるステータスコードを割り当てます。以下のクエリは、すべての双生児をイベントでグループ化した表を表示します。この表には、双生児の両方にインタビューが終わった組の数、一方にインタビューが終わったがもう一方には断られた数、などが示されます。

SELECT
        t1.event,
        t2.event,
        COUNT(*)
FROM
        lentus AS t1,
        lentus AS t2,
        twin_project AS tp
WHERE
        /* We are looking at one pair at a time */
        t1.id = tp.id
        AND t1.tvab=tp.tvab
        AND t1.id = t2.id
        /* Just the sceening survey */
        AND tp.survey_no = 5
        /* This makes each pair only appear once */
        AND t1.tvab='1' AND t2.tvab='2'
GROUP BY
        t1.event, t2.event;

3.8. Apache での MySQL の使用

MySQL データベースを使用してユーザを認証し、ログファイルを MySQL のテーブルに書き込むプログラムがあります。

Apache 設定ファイルに以下の設定を追加することで、MySQL に簡単に読み込めるように Apache のログ形式を変更することができます。

LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

このフォーマットのログファイルを MySQL にロードするには、以下のようなステートメントを使用します。

LOAD DATA INFILE '/local/access_log' INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

LogFormat 行がログファイルに書き込むデータに対応して、指定するテーブルのカラムを作成する必要があります。

前のページ?	?	?次のページ
第2章 MySQL のインストール?	ホーム	?第4章データベース管理